DDL and schema change from an operations perspective · PostgreSQL

DDL in PostgreSQL is transactional, which is a genuine advantage. It is also where most self-inflicted outages come from, and for a reason that is not the one people expect.

Two questions

1. Does it rewrite the table? A rewrite is proportional to the table’s size in I/O, WAL and space.

2. How long will it wait for its lock? This is the one that causes outages.

Rewrites, and the ones that do not

Statement	Rewrites?
`ADD COLUMN` with no default	No — catalogue only
`ADD COLUMN ... DEFAULT <constant>`	No, since PostgreSQL 11
`ADD COLUMN ... DEFAULT <volatile>`	Yes
`DROP COLUMN`	No — marked dropped, space reclaimed by vacuum
`ALTER COLUMN TYPE` to a binary-coercible type	No
`ALTER COLUMN TYPE` otherwise	Yes
`SET NOT NULL`	No rewrite, but a full scan to verify
`ADD CONSTRAINT ... CHECK`	Full scan to verify
`ADD CONSTRAINT ... CHECK NOT VALID`	No scan
`ADD FOREIGN KEY`	Full scan of both tables
`ADD FOREIGN KEY ... NOT VALID`	No scan
`SET DEFAULT` / `DROP DEFAULT`	No
`RENAME`	No

ADD COLUMN ... DEFAULT <constant> not rewriting is the single most useful of these, and it is recent enough that a great deal of advice still says otherwise.

The lock queue is the real hazard

Service impact possiblea plain SELECT refused, with no conflicting lock granted— Captured on 18.6. lock_timeout was 6s. The only GRANTED lock on the table was another plain SELECT.

$ -- session 1: a long SELECT (holds ACCESS SHARE)
-- session 2: ALTER TABLE ... (waits for ACCESS EXCLUSIVE)
-- session 3: SELECT count(*) FROM t;

ERROR:  canceling statement due to lock timeout

blocked_by = {1622}     <- the WAITING ALTER, which holds nothing

Read that carefully. Session 3 is blocked by session 2 — which holds no lock at all. It is waiting for one, and PostgreSQL queues later requests behind waiting ones to prevent starvation.

So a single ALTER TABLE that cannot get its lock stops every subsequent query on that table, even queries that would not have conflicted with anything currently granted.

`lock_timeout` is the answer

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN promo_code text;
COMMIT;

With lock_timeout set, the ALTER gives up after three seconds instead of holding the queue indefinitely. The migration fails, which is loud, recoverable and infinitely preferable to an outage.

Wrap it in a retry loop and it becomes reliable:

for i in $(seq 1 20); do
  psql -v ON_ERROR_STOP=1 -c "SET lock_timeout='3s'; ALTER TABLE orders ADD COLUMN promo_code text;" \
    && break
  sleep 10
done

Each attempt is cheap and bounded. Eventually one lands in a gap between long queries.

Set lock_timeout for the migration role, not per statement

ALTER ROLE migrator SET lock_timeout = '3s';
ALTER ROLE migrator SET statement_timeout = '30s';

Relying on every migration to remember is relying on the least careful migration anyone writes. Setting it on the role that runs migrations makes it the default, and a migration that genuinely needs longer has to say so explicitly — which is the right way round.

The same applies to idle_in_transaction_session_timeout on application roles: it bounds the long-held ACCESS SHARE locks that migrations end up queuing behind in the first place.

Safe patterns

Adding a column. ADD COLUMN with no default, or a constant default. Both are catalogue-only.

Adding a NOT NULL constraint, without the full-table scan blocking everything:

-- 1. cheap: no scan
ALTER TABLE t ADD CONSTRAINT t_c_not_null CHECK (c IS NOT NULL) NOT VALID;
-- 2. validates with a weaker lock; can run for a long time safely
ALTER TABLE t VALIDATE CONSTRAINT t_c_not_null;

Adding a foreign key, same shape:

ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (pid)
  REFERENCES parent(id) NOT VALID;
ALTER TABLE child VALIDATE CONSTRAINT fk;

Changing a column type. The rewrite is unavoidable in general. The alternative is the expand-and-contract sequence: add a new column, backfill in batches, switch the application, drop the old one.

Dropping a column. DROP COLUMN is catalogue-only. The space is returned as rows are rewritten by ordinary activity or by a vacuum, not immediately.

Backfilling a large table in one UPDATE

-- do not do this on a large table
UPDATE orders SET promo_code = 'none';

Every row becomes a new row version, as lesson VII-01 established. On a 100-million-row table that is a table-sized amount of WAL, a table-sized amount of bloat, and one very long transaction holding the vacuum horizon for its whole duration — lesson VII-05’s mechanism, at scale.

Batch it, committing between batches:

UPDATE orders SET promo_code = 'none'
 WHERE id IN (SELECT id FROM orders WHERE promo_code IS NULL LIMIT 10000);

Each batch is short, releases its snapshot, and lets vacuum keep up.

Transactional DDL, and the trap inside the advantage

PostgreSQL’s DDL is transactional. You can wrap several ALTER statements in a transaction, and if one fails the whole change is rolled back. Databases without this leave you with half-applied migrations, and the difference is genuinely large.

The trap is that a transaction holds every lock it has taken until commit. So:

BEGIN;
ALTER TABLE a ADD COLUMN x int;     -- ACCESS EXCLUSIVE on a
ALTER TABLE b ADD COLUMN y int;     -- ACCESS EXCLUSIVE on b
UPDATE a SET x = 1;                 -- 20 minutes on a large table
COMMIT;

Table b is locked against everything for the entire 20 minutes, having been altered in a millisecond. Both tables are unavailable for the duration of the slowest statement.

Three rules follow, and they cover most migration incidents.

Keep DDL transactions short. Do the catalogue changes in one quick transaction and the data changes separately, in batches.

Take locks in a consistent order across all migrations, or two concurrent ones deadlock. Lesson IX-04 covers the mechanism, and it applies to DDL exactly as it does to UPDATEs.

Do not mix DDL and long data changes in one transaction. The combination is what turns a fast schema change into an outage measured in the duration of the slow part.

There is one thing that genuinely cannot go inside a transaction, and it is worth knowing before a migration framework tells you: CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY cannot run in a transaction block at all, because they need to commit between their passes. Migration tools that wrap everything in a transaction have to special-case them, and tools that do not special-case them fail with a message about transaction blocks that reads as a tool bug and is not.

What to take from this

Two questions: does it rewrite, and how long will it wait for its lock. The second causes the outages.
ADD COLUMN ... DEFAULT <constant> does not rewrite.
A waiting ALTER blocks everything behind it. Measured: a plain SELECT refused while the only granted lock was another plain SELECT.
Set lock_timeout, and set it on the migration role. Retry in a loop.
Use NOT VALID plus VALIDATE CONSTRAINT to split the lock from the scan.
Batch backfills; a single large UPDATE is table-sized WAL, bloat and one long snapshot.
Keep DDL transactions short — locks are held until commit.
CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

Cross-course references

Git, CI/CD & GitOps — Part XLIX (Infrastructure CI) covers testing a migration before it reaches production, Part LVII (Approval gates) covers the review it needs, and Part LIX (Rollback) covers why a migration’s rollback is a second migration rather than an undo.
Ansible for Production Sysadmins — Part XXV (Check mode and diff) covers seeing what a change would do before it does it.
Observability for Production Sysadmins — Part LI (Correlating metrics, logs, and traces) covers putting the migration and the latency it may change on one timeline.