Session
BackHypothetical Partitioning for PostgreSQL
Date: 2018-09-07
Time: 16:00 - 16:50
Room: Cyril Magnin
Level: Intermediate
Feedback: Leave feedback
HypoPG is a PostgreSQL extension and supports index design tuning; it allows users to define hypothetical indexes and shows queries' plan/cost with EXPLAIN statement as if they were constructed. As a major feature of next generation of HypoPG, which supports PostgreSQL 11, we are planning to introduce hypothetical partitioning functionality. This functionality will help users to tune partitioning design in the same way as for index design.
Declarative partitioning was introduced in the latest PostgreSQL 10. It will be greatly improved in the next PostgreSQL 11 and more and more, later. Since this is a long-awaited feature, there will be emerging needs to support partitioning design tuning. For users, finding optimal partitioning schemes are not easy tasks. There often would be very many choices on partitioning schemes; and, what is worse, actual partitioning/un-partitioning of tables take a lot of time. The hypothetical partitioning feature allows users to define multiple partitioning schemes hypothetically on real tables and data without spending much time. And then, it outputs queries' plan/cost with EXPLAIN statement using defined hypothetical partitioning schemes. Using this feature, users can quickly check how their queries would be processed if certain tables were partitioned, and try different partitioning schemes in a short time. In this way, users can find optimal partitioning schemes without actually partitioning any tables and moving data to partitions.
We have already created a working prototype. It allows users to simulate range/list/hash partitioning, partition pruning, partition-wise join/aggregation, and N-way join. There are a few limitations right now, though: The size/cost estimates may not be correct in some cases, only plain tables (not partitioned tables) are supported, and multi-level hypothetical partitioning is not possible. We will take away these limitations soon and have better integration with PostgreSQL core program.
In this talk, first, I will provide a brief introduction of HypoPG 1.1.2, the current version of HypoPG. Then, I will show how to extend HypoPG for hypothetical partitioning. Lastly, I will give a demo of hypothetical partitioning.