Planning low-downtime PostgreSQL schema changes

A syntactically simple ALTER TABLE can wait on locks, rewrite data or block application traffic. Production changes need a compatibility sequence and evidence from a realistically sized copy of the database.

Measure the table and workload

Record row count, relation size, write rate, long transactions, replicas and dependent queries. Rehearse with production-like data while observing locks, WAL generation, replication lag and completion time.

Expand before changing behaviour

Add nullable columns or new structures in a release that old and new application versions can both tolerate. Deploy code that writes both representations or derives the new value before making it authoritative.

Build and validate safely

Use CREATE INDEX CONCURRENTLY where blocking writes is unacceptable, monitor invalid indexes after failure and add expensive constraints as NOT VALID before validating them separately. Set conservative lock timeouts so a migration fails rather than queues an outage.

Backfill in bounded batches

Update stable key ranges with pauses and progress records. Keep each transaction small enough to limit locks, dead tuples and replica lag, and make the worker resumable after deployment or connection failure.

Contract in a later release

Move reads to the new representation, compare results and wait until every running application version is compatible. Remove old columns, triggers or indexes only after rollback no longer depends on them.