Combining partitioning and FDWs for real time analytics.

Since v9.1 PostgreSQL is steadily improving its Foreign Data Wrapper (FDW in short) capabilities. FDWs are a way to access and manipulate data external to PostgreSQL from a PostgreSQL server. The technology is based on SQL/MED standard, which represents foreign data in the form of relational table called foreign tables. Starting with v10, PostgreSQL will support declarative partitioning, similar to the partitioning support in many popular DBMSes. What’s more interesting is that these partitions can be foreign tables, bringing two great technologies together! Here’s a glimpse of what this confluence do.

Real time analytics scenario

Consider a database recording purchases made by customers in various stores. Since the customers have affinity towards stores in regions they are located, each region has its own server, which records transactions by customers within that region. A region here can be as large as a continent or as small as a province, depending upon the nature of business, volumes of transactions etc. The transactions are required to be analyzed at a central server periodically for deciding promotions, product distribution etc. The traditional method to do so would be to dump-load-analyze. 

With the help of partitioning and FDWs, the central server can create tables partitioned by region to consolidate all the data. The partitions would be foreign tables pointing to their counterparts in regional servers. This saves time spent in dumping data on regional servers and loading it to the central server. Also, the reports on the central server can be generated any time. Regional standbys can be used as foreign servers in case the central server only generates reports from this regional data.

Potential performance

I tried to measure performance gains simulating above scenario on my laptop. Assume two tables in every regional server, a. customers: recording information about the customers, b. orders: recording purchases made by customers. Central server has partitioned table 'all_customers' and 'all_orders'. Customer id in every region has higher two digits corresponding to the region (e.g. 01, 02, 03, ...). Thus partitioning tables by customer id partitions those by region. (Depending upon the other queries, we may choose to create 'region' as a column in all the required tables and then partition the tables in the central server by region.). I tried a query which produces the names and other details of top-10 consumers. I had four partitions each with 1M customers, and for each customer upto 10 purchases. The real data would have lot more customers and transactions per customer than that and accordingly the scale up would be better.

SELECT count(o.prod_id) sale_count, -- sum(price) would be more appropriate
       c.id -- add c.name, c.email etc. here
FROM all_customers c, all_orders o
WHERE c.id = o.cust_id
GROUP BY c.id
ORDER BY sale_count
LIMIT 10;

Without partition-wise joins, it fetched all the data from all the regions, performed a local join and aggregation. The query took 176 seconds to complete.

With partition-wise join and postgres_fdw join pushdown but without partition-wise aggregation, the query took 155 seconds (12% improvement).

With partition-wise join, partition-wise aggregation, postgres_fdw join pushdown and aggregate pushdown it took 62 seconds, (65% improvement). In this case, aggregation produced total 4M rows from each region and each query on the foreign server took approx 15 seconds. Extrapolating that, we can say that a query which takes hours to run, would now require minutes to run.

If we were to have asynchronous query capability in FDWs, each foreign server can be queried in parallel, thus completing the query in approximately 17-20 seconds (88% improvement). If we could push SORT and LIMIT down to the foreign server, we would be fetching 10 rows instead of 4M, thus improving the query further.

If the company requires to make real time decisions about promotions targeting high-profile consumers, or product distribution and so on, it could do so using combination of partitioning and FDWs, as compared to unreal-time dump-then-load-then-analyze approach.

Patches under development

All the query optimisations we talked about are either available in PostgreSQL or are under development and will be available with next few releases. I have proposed partition-wise join optimisation to the community here. It’s being reviewed by Robert Haas. Jeevan Chalke is working on partition-wise aggregation, which will soon be proposed to the community. Asynchronous query is being discussed on hackers here.