Safe migrations on a live database

Why this lesson exists

"It's a two-second migration, it just adds a column" - and the site is down for ten minutes. Schema changes are the most common way an application team takes its own database down, and it is almost never the change itself that is slow: it is the lock it needs, and the queue of every other query that forms behind it. As the SRE you review migrations, you get paged when one hangs, and you are expected to know which changes are safe on a live table and how to make the others safe.

What you need to know already: lock modes, ACCESS EXCLUSIVE, blocking chains and pg_blocking_pids (lesson 6), indexes and EXPLAIN (lesson 8), lock_timeout (lesson 6).

The words you need first

• DDL - data definition: CREATE, ALTER, DROP. Most ALTER TABLE forms take ACCESS EXCLUSIVE - nothing else may touch the table, not even a SELECT.
• Lock queue - lock requests are granted in order. A request that cannot be granted waits, and every request after it that conflicts with it waits too - even ones that would not conflict with what is actually held.
• Table rewrite - some changes copy the whole table into a new file (changing a column type, a volatile default). The lock is held for the whole copy.
• Validation scan - adding a CHECK, NOT NULL or foreign key reads every row to prove it.
• NOT VALID - add a constraint without checking existing rows; check them later with VALIDATE CONSTRAINT, which takes a much weaker lock.
• CONCURRENTLY - build or rebuild an index without blocking writes.

The lock queue, live

A reporting session runs a long transaction that has read orders (it holds ACCESS SHARE - harmless on its own). Then a deploy runs a migration:

$ sudo -u postgres psql -d orders -c "alter table orders add column gift_note text" &
[1] 17416
$ sudo -u postgres psql -c "select pid, application_name, state, wait_event_type, pg_blocking_pids(pid) as blocked_by, left(query, 45) as query from pg_stat_activity where datname = 'orders' order by backend_start"
  pid  | application_name |        state        | wait_event_type | blocked_by |                     query
-------+------------------+---------------------+-----------------+------------+-----------------------------------------------
 17413 | weekly-report    | idle in transaction | Client          | {}         | SELECT count(*) FROM orders
 17418 | psql             | active              | Lock            | {17413}    | alter table orders add column gift_note text
 17421 | psql             | active              | Lock            | {17418}    | select id, status from orders where id = 5
 17424 | psql             | active              | Lock            | {17418}    | update orders set updated_at = now() where id
(4 rows)

The ALTER TABLE waits for the report's ACCESS SHARE (it needs ACCESS EXCLUSIVE). That alone would be fine - but the SELECT and UPDATE that came after it wait for the ALTER, not for the report: they conflict with the ACCESS EXCLUSIVE request in front of them in the queue. Every request for orders is now stuck until the report ends. The app's pools fill up with waiting requests, and that is the outage: from a migration whose own work takes milliseconds.

Stop the bleeding: cancel the migration, not the report - the queue behind it drains at once:

$ sudo -u postgres psql -Atc "select pg_cancel_backend(pid) from pg_stat_activity where query like 'alter table orders%' and state = 'active'"
ERROR:  canceling statement due to user request
t
$ sudo -u postgres psql -c "select pid, application_name, state, wait_event_type, left(query, 45) as query from pg_stat_activity where datname = 'orders' order by backend_start"
  pid  | application_name |        state        | wait_event_type |            query
-------+------------------+---------------------+-----------------+-----------------------------
 17413 | weekly-report    | idle in transaction | Client          | SELECT count(*) FROM orders
(1 row)

lock_timeout: fail fast, retry

The fix is to never let a migration wait for its lock for long. With lock_timeout the ALTER gives up after a few seconds instead of holding the queue; the app sees a hiccup of at most that long, and the deploy retries:

$ sudo -u postgres psql -d orders -c "set lock_timeout = '3s'" -c "alter table orders add column gift_note text"; echo "exit: $?"
SET
ERROR:  canceling statement due to lock timeout
exit: 1

canceling statement due to lock timeout after 3 seconds, and nothing else waited longer than that. A retry loop belongs in the migration runner:

for i in 1 2 3 4 5; do
  psql -X -v ON_ERROR_STOP=1 -d orders \
       -c "set lock_timeout = '3s'" -c "alter table orders add column gift_note text" && break
  sleep $((i * 5))
done

End the report (in real life: wait for it, or ask its owner), then the migration goes through in milliseconds:

$ sudo -u postgres psql -Atc "select pg_terminate_backend(pid) from pg_stat_activity where application_name = 'weekly-report'"
t
$ sudo -u postgres psql -d orders -c "set lock_timeout = '3s'" -c "alter table orders add column gift_note text"
SET
ALTER TABLE

Two more rules from the same incident: set statement_timeout for the migration too (a change that turns out to rewrite the table should fail, not run for an hour), and look at pg_stat_activity for long transactions before you deploy.

What is instant and what rewrites

ADD COLUMN gift_note text was instant: since PostgreSQL 11, adding a column - even with a constant DEFAULT - only changes the catalog. The lock is still ACCESS EXCLUSIVE, so it still needs lock_timeout, but it is held for milliseconds.

NOT NULL without the long lock

The column needs a value for every existing row first (the backfill, below), then:

$ sudo -u postgres psql -d orders -c "update orders set gift_note = '' where gift_note is null"
UPDATE 40000
$ sudo -u postgres psql -d orders -c "set lock_timeout = '3s'" -c "alter table orders add constraint orders_gift_note_nn check (gift_note is not null) not valid"
SET
ALTER TABLE
$ sudo -u postgres psql -d orders -c "alter table orders validate constraint orders_gift_note_nn"
ALTER TABLE
$ sudo -u postgres psql -d orders -c "set lock_timeout = '3s'" -c "alter table orders alter column gift_note set not null" -c "alter table orders drop constraint orders_gift_note_nn"
SET
ALTER TABLE
ALTER TABLE

The NOT VALID step is instant; VALIDATE scans but only with SHARE UPDATE EXCLUSIVE; and SET NOT NULL finds the validated CHECK and skips its own scan. (The one-shot UPDATE above is fine for 40,000 rows in a lab; on 400 million rows it is its own incident - next section.)

Indexes: CONCURRENTLY

A plain CREATE INDEX takes a SHARE lock - reads continue, every write waits until the build ends. CONCURRENTLY builds in several passes while writes continue:

$ sudo -u postgres psql -d orders -c "create index concurrently orders_status_created_idx on orders (status, created_at)"
CREATE INDEX
$ sudo -u postgres psql -d orders -c "\d orders" | grep -A8 Indexes
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_status_created_idx" btree (status, created_at)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)
Referenced by:
    TABLE "order_items" CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)

Its rules:

• It cannot run inside a transaction block (ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block) - migration tools that wrap every file in BEGIN/COMMIT need it marked as non-transactional (Flyway executeInTransaction=false, Liquibase runInTransaction="false", Alembic autocommit_block(), Rails disable_ddl_transaction!).
• It waits for every transaction that could use the table to finish - a long transaction makes it hang (it shows as waiting on virtualxid).
• If it fails or is cancelled it leaves an INVALID index behind: still updated on every write, never used for reads. \d shows INVALID; drop it (DROP INDEX CONCURRENTLY) and build again.

$ sudo -u postgres psql -d orders -c "select indexrelid::regclass as index, indisvalid from pg_index where indrelid = 'orders'::regclass"
           index           | indisvalid
---------------------------+------------
 orders_pkey               | t
 orders_status_created_idx | t
(2 rows)

REINDEX INDEX CONCURRENTLY rebuilds a bloated or corrupt index the same way.

Backfills in batches

One UPDATE of every row is one transaction: it locks every row until it commits, writes a dead tuple per row (bloat), produces a burst of WAL (replicas fall behind, the disk fills) and cannot be stopped halfway without rolling all of it back. Do it in batches that each commit:

$ for start in $(seq 1 10000 40000); do sudo -u postgres psql -d orders -qc "update orders set gift_note = 'none' where id between $start and $((start + 9999)) and gift_note = ''"; done
$ sudo -u postgres psql -d orders -Atc "select count(*) from orders where gift_note = ''"
0

In production: batches of 1,000-10,000 rows by primary key, a short pause between them, watch replication lag and n_dead_tup while it runs, and make the job restartable (the where gift_note = '' makes each batch idempotent).

The migration checklist

1. Does it take ACCESS EXCLUSIVE? Then SET lock_timeout = '3s' and a retry loop.
2. Does it rewrite or scan the table? Then split it (new column + backfill, NOT VALID + VALIDATE, CONCURRENTLY).
3. Any long transactions open right now? select pid, now() - xact_start, query from pg_stat_activity where xact_start < now() - interval '1 minute'.
4. Is the app compatible with both the old and the new schema during the deploy (expand, migrate, contract)?
5. statement_timeout for the migration, and someone watching pg_stat_activity while it runs.

In an interview: "A migration that adds a column took the site down - why?" - ALTER TABLE needs an ACCESS EXCLUSIVE lock; a long-running transaction held ACCESS SHARE, so the ALTER waited, and every query after it queued behind the ALTER in the lock queue. Fix: cancel the migration (the queue drains), and run migrations with SET lock_timeout = '3s' and retries; use CONCURRENTLY for indexes and NOT VALID + VALIDATE for constraints.

What you can do now

• Explain the lock queue and why a waiting ALTER TABLE blocks plain SELECTs.
• Stop a stuck migration safely, and run migrations with lock_timeout and retries.
• Tell catalog-only changes from rewrites and scans, and split the expensive ones.
• Add NOT NULL and foreign keys without long locks (NOT VALID + VALIDATE).
• Use CREATE INDEX CONCURRENTLY, handle an INVALID index, and backfill in batches.