Zero-downtime database changes: expand and contract

Goal

Change the database structure while the service keeps serving requests, with every intermediate state working with both the old and the new code version.

Prerequisites

Migrations versioned together with the code; a deploy process that can ship code and migrations separately; knowledge of which statements take strong locks. The PostgreSQL documentation for ALTER TABLE lists the lock level for each form. Most forms take ACCESS EXCLUSIVE, which blocks all other use of the table. It also states that ADD COLUMN with a non-volatile DEFAULT stores the value in the metadata instead of rewriting the table; that a CHECK or NOT NULL constraint reads the table but does not rewrite it; and that a constraint created with NOT VALID is only checked by VALIDATE CONSTRAINT, which takes only a SHARE UPDATE EXCLUSIVE lock. CREATE INDEX CONCURRENTLY builds an index without locking out inserts, updates and deletes on the table. The lock_timeout parameter aborts a statement that waits longer than the given time for a lock.

Steps

1. Expand: add the new column, table or index in a way that is fast and non-blocking (nullable column or constant default, CREATE INDEX CONCURRENTLY, constraints marked NOT VALID). The old code ignores the new structure.
2. Run every migration with lock_timeout set (a few seconds) and retry if it is aborted, rather than letting a waiting lock request build up a queue of blocked requests behind it.
3. Deploy code that writes both representations and still reads the old one.
4. Backfill existing rows in small batches with pauses between them; watch lock wait times and replication lag. Then run VALIDATE CONSTRAINT.
5. Deploy code that reads the new representation and, for a period, compares it against the old one (log any discrepancies).
6. Stop writing the old representation; deploy.
7. Contract: remove the old column or table in a later release, once rolling back to the previous code version is no longer needed.

Expected result

Each deployment can be reversed on its own; no migration holds a lock long enough to be noticed.

Limits and test basis

The pattern multiplies the number of steps and takes days rather than minutes. A column is renamed as "create, copy, drop" rather than with RENAME when both code versions have to run at the same time. The locking behavior follows the cited documentation; no timings are claimed. Whether lock_timeout combined with retrying reduces the number of incidents is recorded in the wiki as a hypothesis, not as a finding.

Retrying with the blocker in mind

An abort caused by lock_timeout is information, not a cue for an endless loop: the waiting lock request itself already blocks all new access behind it, and every retry causes another short stall. Before the next attempt, pg_blocking_pids(pg_backend_pid()) together with pg_stat_activity (state, xact_start, query) shows who holds the lock. A long-running query or a session stuck idle in transaction will not end on its own; whether to wait, cancel or use pg_terminate_backend is a deliberate decision, and idle_in_transaction_session_timeout prevents the most common kind in advance. The loop needs a pause between attempts and an upper limit. lock_timeout only limits the wait for the lock; the runtime of VALIDATE CONSTRAINT and of the backfill batches is limited by statement_timeout.