PostgreSQL Zero Downtime Primary Key Conversion

https://fountain.engineering/2023/05/04/postgresql-partitioning-primary-key/

by jhas78asd • 3 years ago

4 3 3 years ago

jhas78asd2• 3 years ago

In 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.

andygauge• 3 years ago

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?

jhas78asd2• 3 years ago

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.

PostgreSQL Zero Downtime Primary Key Conversion

Related Stories