JayCheng113 PostgreSQL migrations without downtime
Adding a NOT NULL column to a large table rewrites it under an ACCESS EXCLUSIVE
lock, which blocks every reader and writer for the duration. Split it instead:
- Add the column as nullable with no default.
- Backfill in batches, committing between batches so the lock is never held long.
- Add a
NOT VALIDcheck constraint, thenVALIDATE CONSTRAINTseparately — validation takes only aSHARE UPDATE EXCLUSIVElock.
Dropping a column is metadata-only and safe. Renaming one is not: the old and new name cannot both be live, so deploy a read-both/write-both shim first.
Always set lock_timeout before DDL. Without it a migration that cannot acquire
its lock will queue behind a long transaction and block everything behind it too.