Upcoming Enhancements to Partitioning & Indexes in PostgreSQL 11
My colleague Alvaro Herrera has been working on a series of connected features for PostgreSQL 11. It’s worth explaining what these are rather than trying to piece together what is happening from reading commit messages.
The overall idea is to allow Partitioned tables to have Referential Integrity, by way of Primary Keys and Foreign Keys, as well as some additional tweaks.
To achieve that, we need to understand the structure of features in PostgreSQL.
Foreign Keys (FKs) are implemented using row Triggers, so we must allow Triggers to be executed on Partitioned Tables. FKs also require Primary Keys (PKs), so we must add those also.
Primary Keys are implemented using Unique Indexes, so we need to add indexes and allow them to be unique. I’ll write about partitioned indexes separately, because they are very cool. The best bit is that they don’t actually exist, its just metadata! That requires some explanations and a longer post.
So that gives us a set of features and an order in which they should be implemented:
- Create Index on Partitioned Tables
- Allow Unique Index on Partitioned Tables
- Create Triggers on Partitioned Tables
- Allow FKs on Partitioned Tables
Taken together, these features are now looking pretty good after lots of work and review. So PostgreSQL 11 is looking like we’ll be able to add referential integrity to partitioned tables.
That is really cool. PostgreSQL is really shining nowadays.
I fear we are going to wait until PG12 or PG13 before we get partitioning really usable.
Partitioning needs to be viable with thousands of partitions, not a dozen or two.
Yes, referential integrity for partitioned tables is great, but if partitioned tables aren’t usable, then also RI won’t be.
“Real life partitioning”™ involves tables with tens and hundreds of billion or rows. Splitting them into a dozen sub-tables won’t help much. It’s rather easy to write software to do “logical partitioning” with some code to choose the right partition!
As of now the weak point in partitioning is the sub-table selection algorithm, not the RI. I mean, RI is important, but I feel it’s way less important than real life partitioning.
So, if I had to work on PG partitioning I’d focus on this area first. But this is just my opinion.
Good points. My colleague David Rowley has been working with Amit Langote on precisely the issues you mention. Hopefully, PG11 will meet your needs.
I concur with Vincenzo’s comments about handling large numbers of partitions. Oracle currently supports thousands of partitions without significant issues, really need the same in PostgreSQL. Chances are that if the tables are big enough to be using partitions there is a good chance it is a data warehouse and would typically not implement declarative FKs due to the overhead involved.
Yes, the developers understand the issue. Efficient partition pruning was not considered an essential part of the first phase for partitioning for PG10 and we were unable to get that working in time for that release. Correcting that issue, we’ve been trying hard with a number of approaches to reduce the planning time. So I’m hopeful the situation will change for PG11.
I disagree quite a bit with the sentiment raised here. There are important use cases for partitions with few tables, i.e. factoring off read-only parts of tables. For those cases, referential integrity is one of the show stoppers. So please, just because it doesn’t fill your niche yet doesn’t make it unusable.
I strongly agree with Jörg here.
My use case for example would be partitioned tables used in a multi-tenant OLTP database partitioned by tenant where statements are executed mostly on a single partition. In this case referential integrity is a must for me.