Since v9.1, PostgreSQL has been steadily improving its Foreign Data Wrapper (FDW) capabilities. FDWs are a way to access and manipulate data external to PostgreSQL from a PostgreSQL server. The technology is based on the SQL/MED standard, which represents foreign data in the form of relational tables called foreign tables. Starting with v10, PostgreSQL will support declarative partitioning, similar to the partitioning support in many popular DBMSs. 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 will do.
Real-Time Analytics Scenario
Consider a database recording purchases made by customers in various stores. Since the customers have an 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.
I tried to measure performance gains simulating the above scenario on my laptop. Assume two tables in every regional server: customers, recording information about the customers and orders, recording purchases made by customers. A central server has the partitioned table
all_orders. Customer ID in every region has the 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 that produces the names and other details of top 10 consumers. I had four partitions each with one million customers, and for each customer up to 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 and 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 (a 12% improvement).
With partition-wise join, partition-wise aggregation,
postgres_fdw join pushdown, and aggregate pushdown, it took 62 seconds (a 65% improvement). In this case, aggregation produced total four million rows from each region and each query on the foreign server took approximately 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 an asynchronous query, each foreign server can be queried in parallel, thus completing the query in approximately 17-20 seconds (an 88% improvement). If we could push SORT and LIMIT down to the foreign server, we would be fetching 10 rows instead of 4 million, thus improving the query further.
If the company requires real-time decision making about promotions targeting high-profile consumers, or product distribution and so on, it could do so using a combination of partitioning and FDWs, as compared to non-real-time dump-then-load-then-analyze approach.
Patches Under Development
All the query optimizations 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 optimization 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. An asynchronous query is being discussed on hackers here.