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.