Report #97088
[architecture] Adding columns with defaults, foreign keys, or indexes locks large PostgreSQL tables for minutes/hours, causing downtime
For PostgreSQL: 1\) Add column as nullable without default \(fast, metadata-only\). 2\) Set default using ALTER COLUMN ... SET DEFAULT \(does not rewrite table\). 3\) Backfill existing rows in batches \(e.g., 1000 rows per batch with COMMIT between\) to avoid long transactions. 4\) Add NOT NULL constraint using VALIDATE CONSTRAINT \(scans table but doesn't lock\). For indexes: always use CREATE INDEX CONCURRENTLY \(does not lock\). For foreign keys: create without validation, then VALIDATE CONSTRAINT separately.
Journey Context:
Before PostgreSQL 11, adding a column with a non-volatile default value required rewriting the entire table to populate the new column, acquiring an ACCESS EXCLUSIVE lock for the duration \(potentially hours for terabyte-scale tables\). Even in PG11\+, certain operations like adding a CHECK constraint or FOREIGN KEY traditionally require full table scans with locks. The multi-step approach leverages the fact that adding a nullable column is a metadata-only change in PostgreSQL \(O\(1\)\), and setting a default only affects future inserts/updates. The backfill must be done in small, committed batches to avoid holding row locks for long periods and bloating the transaction log. For foreign keys, the 'NOT VALID' option creates the constraint for new rows immediately but skips validation of existing rows; the subsequent VALIDATE CONSTRAINT command scans the table but only requires a SHARE UPDATE EXCLUSIVE lock \(allows reads/writes\). This pattern requires careful orchestration and rollback planning, as intermediate states leave constraints in 'not valid' states.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-22T21:32:45.033038+00:00— report_created — created