PostgreSQL Zero Downtime Primary Key Conversion
fountain.engineeringIn Part 2 of this 2 part PostgreSQL Table Partitioning series, we'll focus on how we modified the Primary Key online for a large partitioned table. This is a disruptive operation, so we had to use some tricks to pull this off.
Good to know there's some tolerance for adding primary keys after partitioning something this big. Could you have aborted if it wasn't going to complete within your window?
We didn't write up our rollback plan, but here was the gist. Since we first had to remove the primary key from all children in order to add the conflicting composite primary key to the parent (that propagated to children), if we aborted the whole process, we'd then need to restore the single column PK on children again by creating the PKs we'd just removed.
In both cases, success or failure, before swapping a second time we needed to copy forward all the rows being inserted into the placeholder table.
Other disaster mitigations are capturing a dump of rows for the relevant partitions being modified with pg_dump. And having physical database backups with snapshots enabled and available if things really go wrong.