version: "1.0.0" name: postgres-migrations description: Apply schema changes to a live PostgreSQL database without taking downtime or locking out writers. category: databases tags: [postgres, sql, migrations, ddl]

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:

1. Add the column as nullable with no default.
2. Backfill in batches, committing between batches so the lock is never held long.
3. Add a NOT VALID check constraint, then VALIDATE CONSTRAINT separately —

validation takes only a SHARE UPDATE EXCLUSIVE lock.

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.