A zero-downtime migration is a compatibility and availability goal, not a guarantee attached to small statements. Old and new application versions may overlap while data is changing. Plan the schema, writer behavior, backfill, reader cutover, and recovery together. Dropping information is not automatically reversible, even when the preceding steps were additive.

Lock acquisition, row conflicts, resource usage, and application assumptions all matter. A metadata-only change can wait behind another transaction; a backfill can exhaust I/O or replication capacity without holding an exclusive table lock.

Expand, backfill, contract

Expand introduces a representation that existing readers and writers can tolerate. Deploy compatible writers before relying on new data, backfill existing rows, verify the invariant, switch readers, and contract only after old binaries, background jobs, and rollback requirements no longer need the old shape. Adding a nullable column is often useful, but still needs a lock and compatibility checks.

Dual writing must define failure and ordering behavior. Two columns updated in one database transaction can remain consistent; separate writes can partially succeed. Old writers that still update only the old column can invalidate an already backfilled value. Retire them or provide a tested synchronization mechanism before declaring convergence. A rollback is safe only while its required representation remains accurate.

Use restartable batches and a stable progress key. Prevent stale backfill data from overwriting a concurrent application update, for example through a transactionally checked version or a suitable conditional update. A null-only predicate is sufficient only for the intended invariant and writer behavior. Recheck skipped or concurrently changed rows and validate after writers converge. Batching limits individual transactions but can add total work; it is not a substitute for correct phase ordering.

Locks are one failure boundary

In PostgreSQL, ACCESS EXCLUSIVE conflicts with ordinary reads and writes. Many ALTER TABLE forms take it even when no table rewrite is needed. In contrast, an ordinary UPDATE takes ROW EXCLUSIVE plus row locks; it does not globally exclude every writer. Conflicts depend on affected rows and other operations, while long transactions retain locks until completion.

A queued strong lock can delay later conflicting requests. Identify actual blockers, transaction boundaries, and wait events rather than blaming whichever transaction is oldest. SQLite rollback-journal and WAL behavior are different engine contracts. The earlier article’s millisecond and page-count figures have no exact benchmark evidence established here and are not PostgreSQL predictions or measurements from this review.

Validate under a weaker lock

For PostgreSQL 17, add an appropriate CHECK ... NOT VALID only once current writers satisfy it: new inserts and updates are checked immediately, while old rows await validation. VALIDATE CONSTRAINT scans under SHARE UPDATE EXCLUSIVE, which permits ordinary DML but conflicts with some maintenance and schema operations. It is not lock-free.

A validated CHECK proving the column cannot be null can let SET NOT NULL skip its scan, provided that check is not dropped in the same command. This does not eliminate the stronger lock needed by the final alteration, and setting NOT NULL does not inherently rewrite the table. PostgreSQL 18 adds not-null constraint capabilities, so consult the deployed major version rather than assuming every release supports identical syntax.

When it breaks

A backfill finishes once, then a surviving old writer makes the new representation stale. Monitor mismatch counts and writer versions before cutting reads over. A destructive contract phase may require restore or forward repair; rehearse that recovery before removing data.

Ordinary CREATE INDEX blocks writes during construction. CREATE INDEX CONCURRENTLY permits normal DML, but performs extra scans and waits, cannot run inside a transaction block, and can leave an invalid index after failure. Such an index may still impose update overhead or enforce uniqueness. Inspect its exact state before an approved recovery operation.

Compatible writers, conditional backfill, validation, reader cutover and delayed contraction are separated from PostgreSQL constraint locks and recovery limits.
Migration compatibility: mechanism and evidence boundaries.

Check it quickly

These retained commands are an operator checklist, not an isolated migration test. Use only an explicitly selected disposable PostgreSQL database where an owned events table exists and note does not. Confirm connection and search_path, suitable catalog visibility, DDL permission, and a separate total statement timeout. The fourth command changes schema; do not paste it into production.

psql -c "SELECT pid, state, now()-xact_start AS age, left(query,50) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;"
psql -c "SELECT pid, mode, granted, left(relation::regclass::text,40) FROM pg_locks WHERE NOT granted;"
psql -c "SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid;"
psql -c "SET lock_timeout='3s'; ALTER TABLE events ADD COLUMN note text;"
psql -c "SELECT conname, convalidated FROM pg_constraint WHERE conrelid='events'::regclass;"

The activity query lists candidates, not proven blockers; pg_blocking_pids supplies blocking relationships. Ungranted locks do not identify every holder. lock_timeout bounds each acquisition wait, not execution time or total impact. convalidated alone does not show which condition was proven. No commands ran here; rollout compatibility, concurrent backfill, constraint transitions, and recovery still need an authorized, version-pinned fixture.

Sources