postgres-impl-zero-downtime-migrations

Quick Reference :

A schema change on a live database is safe only when it never holds a strong lock long enough to block production traffic. Two distinct dangers :

1. The operation itself is slow : a full table rewrite (ALTER COLUMN TYPE, ADD COLUMN with a volatile default) or a plain CREATE INDEX holds a strong lock for the whole duration.
2. The lock queue : even a fast, metadata-only ALTER TABLE requests ACCESS EXCLUSIVE. If a long-running query holds the table, the ALTER waits behind it, and every query arriving after the ALTER waits behind the ALTER. One slow SELECT plus one fast ALTER can freeze the table.

Core rules :