Change the schema, keep the site

You know why the line forms now. One ALTER waits for an exclusive lock behind a report, and every request after it waits too, even plain reads (A waiting lock blocks everyone behind it). Your pool fills and the site is down over a migration that "does nothing". GoCardless wrote up exactly this: a quick foreign-key migration queued behind a long read, API requests queued behind the migration, and the API was down for about 15 seconds.

This chapter is the other half: shipping schema changes at 2 p.m. on a Tuesday without ever forming that line.

Schema changes don't hurt because they're slow. They hurt because of what they lock and for how long, so ask two questions of every statement: which lock does it take, and how long does it hold it? Almost every dangerous change has a safe version: the same change, split into steps that hold a weak lock for minutes or a strong one for milliseconds. What follows is those splits, measured.

So the first rule of migrations has nothing to do with the DDL.

Rule one: lock_timeout and retry

Set lock_timeout on every migration session. When it expires, the statement fails (canceling statement due to lock timeout), leaves the queue, and the traffic behind it flows again, even while the report is still open.

select lab.prove('after the lock timeout, the lookup completes while the report is still open',
  (select id from dblink_get_result('web') t(id bigint)) = 42
  and (select state from pg_stat_activity where pid = :report_pid) = 'idle in transaction');

That error is the system working. A few seconds of queued requests go unnoticed; an unbounded wait is an outage. The failed statement changed nothing, so retry it a bounded number of times (a timeout without a retry only turns working migrations into failed deploys):

# retry a DDL statement up to 10 times with a short lock_timeout
for i in $(seq 1 10); do
  if psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \
       -c "set lock_timeout = '2s'" \
       -c "alter table events add column note text"; then
    exit 0
  fi
  echo "attempt $i failed, retrying" >&2
  sleep $((i * 2))
done
exit 1

Put it in your migration tool's connection setup, at one to five seconds: long enough to slip between short transactions, short enough that a stall is invisible. If retries keep failing, find the blocker, usually a long transaction or a session idle in transaction (backend_xmin names the transaction holding vacuum back).

Add a generous statement_timeout too, so an accidental rewrite of a 500 GB table fails in minutes instead of holding an exclusive lock for an hour. Only concurrent index builds (and their siblings, reindex concurrently and drop index concurrently) turn both off (Run a concurrent build alone, untimed, and check it's valid).

Every change is a catalog edit, a scan, or a rewrite

Once you hold the lock, what matters is how long. A change either edits a few catalog rows (milliseconds at any size), scans every row to check something, or rewrites the table and its indexes, with time, disk, and WAL growing with the table. On events (275 MB, 2 million rows) that's a deploy nobody notices against a nine-second freeze; on CI's 1,000 rows you can't tell them apart.

So test migrations on a restored copy of production (it tests your backups too), and watch two numbers. A rewrite changes pg_relation_filenode, the file the table lives in; a scan bumps seq_scan, the table's count of full scans.

The lock to aim for is ShareUpdateExclusive, which blocks neither reads nor writes (Most ALTER TABLE forms take ACCESS EXCLUSIVE). Every safe pattern below moves the slow part of a change under it.

Constant defaults are free; volatile defaults rewrite

Since PostgreSQL 11, a column with a constant default is catalog only. A volatile default is not:

alter table events add column source text not null default 'web';
select pg_relation_filenode('events');
alter table events add column ingest_id uuid default gen_random_uuid();
select pg_relation_filenode('events');

Time: 65.657 ms
 pg_relation_filenode
                16943
...
Time: 9236.902 ms (00:09.237)
 pg_relation_filenode
                17220

The first filenode is the one the table already had: the constant touched no rows. Postgres stored 'web' in the catalog and supplies it when it reads an older row. The UUID needs a different value per row, so Postgres rewrote all 2 million rows, for nine seconds under AccessExclusive. The rule is volatility, not functions: now() is stable, evaluated once, and stored like a constant.

For a per-row value, split it: add the column bare (catalog only), set default so new rows get one (catalog only), then backfill the old rows in batches.

select lab.prove('a constant default does not rewrite: same filenode',
  pg_relation_filenode('events') = :fn0);
select lab.prove('every existing row reports the moment of the migration',
  (select count(distinct ts2) from events) = 1);
-- the rewrite is shown on a 200,000 row copy of events to keep the lab quick
select lab.prove('a volatile default (gen_random_uuid()) rewrites: new filenode',
  pg_relation_filenode('events_copy') <> :cfn);

NOT NULL goes through a CHECK first

set not null has to prove there are no nulls, so it scans the whole table under AccessExclusive, blocking reads while it pulls every page off disk. Use a CHECK as a stepping stone:

-- 1. brief AccessExclusive, no scan: new rows are checked from now on
alter table events
  add constraint events_user_id_nn check (user_id is not null) not valid;
-- 2. ShareUpdateExclusive: scans, but reads and writes continue
alter table events validate constraint events_user_id_nn;
-- 3. brief AccessExclusive, no scan: Postgres trusts the validated CHECK
alter table events alter column user_id set not null;
-- 4. the CHECK is now redundant
alter table events drop constraint events_user_id_nn;

Step 3 is the clever bit (PostgreSQL 12+): a valid CHECK proving col is not null lets SET NOT NULL skip the scan. On PostgreSQL 18 you can skip the CHECK: add the NOT NULL itself NOT VALID, then validate it.

begin;
select xact_seq_scans('events') as s0 \gset
alter table events add constraint events_user_id_nn check (user_id is not null) not valid;
select lab.prove('step 1, add check ... not valid: no scan',
  xact_seq_scans('events') = :s0);
alter table events validate constraint events_user_id_nn;
select lab.prove('step 2, validate: one scan',
  xact_seq_scans('events') = :s0 + 1);
alter table events alter column user_id set not null;
select lab.prove('step 3, set not null with a valid CHECK in place: no scan',
  xact_seq_scans('events') = :s0 + 1);
rollback;

-- no scan
alter table events
  add constraint events_user_id_not_null not null user_id not valid;
-- scans, under a weak lock
alter table events validate constraint events_user_id_not_null;

select lab.prove('18: add not null ... not valid takes AccessExclusive',
  my_strongest('events') = 'AccessExclusiveLock');
select lab.prove('before validation, pg_constraint.convalidated is false',
  (select not convalidated from pg_constraint where conname = 'events_user_id_not_null'));

Add a CHECK NOT VALID, then validate it

Never add a CHECK to a large table in one step. Add it NOT VALID (brief lock, no scan, enforced for new writes at once), then VALIDATE CONSTRAINT (a scan under ShareUpdateExclusive), exactly as steps 1 and 2 above. If validation finds a bad row it fails, and the constraint stays, guarding new writes while you fix the old data.

alter table events add constraint events_no_login check (kind <> 'login') not valid;
-- validate fails with check_violation (caught in the lab)
select lab.prove('if validation hits a violating row it fails, and the constraint stays, not valid',
  (select not convalidated from pg_constraint where conname = 'events_no_login'));

Add foreign keys NOT VALID, then validate

A one-step foreign key scans the child table while blocking writes on both tables. Split it the same way:

-- ShareRowExclusive on both tables: blocks writes briefly, no scan
alter table events add constraint events_project_fk2
  foreign key (project_id) references projects (id) not valid;
-- ShareUpdateExclusive on events, RowShare on projects: traffic continues
alter table events validate constraint events_project_fk2;

The add still blocks writes on the parent, maybe your hottest table, and queues like anything else (that's what bit GoCardless), so it gets lock_timeout. And index the referencing column yourself, concurrently; without it every delete on projects scans events (Constraints, chosen on purpose).

Pick the index build that matches the table's traffic

Postgres builds an index two ways, and the choice fits in a sentence. A plain CREATE INDEX does the least work and stops every write to the table until it finishes; CREATE INDEX CONCURRENTLY lets writes through and pays for it with twice the reading, a lot of waiting, and a way to fail halfway.

The same (project_id, created_at) index on events, built both ways with nothing else running:

   build    | table_scans | pages_read |  wal  | index_pages
------------+-------------+------------+-------+-------------
 plain      |           1 |      35175 | 54 MB |        7703
 concurrent |           2 |      70348 | 54 MB |        7703

Same index, same WAL, twice the table reads: on our machine the concurrent build took about one and a half times as long as the plain one.

That's the cost a benchmark sees. The cost your users feel is what happens to writes. Four clients inserted into events as fast as they could while we built the index:

Every insert that arrived during the plain build waited for all of it. On events that's under a second. On a 200 GB table it's the whole build, with every checkout, signup, and webhook in line.

The difference is one lock. A plain build's ShareLock lets reads through but blocks the RowExclusiveLock every write needs; a concurrent build's ShareUpdateExclusiveLock blocks neither.

 pid  |             query              |       mode       | granted | blocked_by
------+--------------------------------+------------------+---------+------------
 3932 | create index plain_idx on even | ShareLock        | t       | {}
 3933 | insert into events (account_id | RowExclusiveLock | f       | {3932}

select lab.prove('a plain build holds a Share lock on events',
  exists (select 1 from pg_locks where pid = :b_mig and relation = 'events'::regclass
             and mode = 'ShareLock' and granted));
select lab.prove('an INSERT waits for it, on a relation lock',
  (select wait_event_type || ':' || wait_event from pg_stat_activity where pid = :b_web) = 'Lock:relation'
  and exists (select 1 from pg_locks where pid = :b_web and relation = 'events'::regclass
                 and mode = 'RowExclusiveLock' and not granted));
select lab.prove('a concurrent build reads it twice',
  :cic_scans = 2 and :cic_pages between 1.9 * :events_pages and 2.1 * :events_pages);
select lab.prove('while a primary key lookup goes straight through',
  (select k from dblink('b_hold', 'select kind from events where id = 42') t(k text)) is not null);

select lab.prove('at the defaults (64 MB, 2 workers allowed) the build gets only one worker: each needs 32 MB',
  current_setting('maintenance_work_mem') = '64MB'
  and current_setting('max_parallel_maintenance_workers') = '2'
  and (select scans - :s0 = 2 from events_reads()));
select lab.prove('with 256 MB it gets both workers: three processes scan the table',
  (select scans - :s0 = 3 from events_reads()));

If you have to ask whether it's busy, build concurrently

Use a plain CREATE INDEX only when nobody is writing to the table: a new or empty table (including one created earlier in the same migration, where a plain build keeps table, index, and constraints atomic), a real maintenance window with writes actually stopped, not "usually quiet", or a table nobody writes, like a lookup table, an archive, or a partition that stopped receiving rows.

Everything that takes writes in production gets CREATE INDEX CONCURRENTLY. If you have to ask whether a table is busy, treat it as busy.

The plain build isn't a bad default: it's faster, transactional, and fails cleanly. It's a bad default for the big, busy tables you most often need to index.

Run a concurrent build alone, untimed, and check it's valid

1. Turn lock_timeout off for this one statement, the exception to Rule one: lock_timeout and retry. A concurrent build's lock blocks no reads or writes, even while it waits, so the timeout protects no traffic. Worse, Postgres applies it to the build's waits for other transactions: a 1 s timeout cancels a build stuck behind a slow report and leaves an invalid index. Set lock_timeout = 0 and statement_timeout = 0 for the build. (Plain builds keep it: one merely waiting for its lock already blocks the writes behind it.)
2. Run it alone. It refuses to run in a transaction block, so give it its own migration with your tool's "no transaction" switch. A second concurrent build on the same table queues behind the first.
3. Watch it. pg_stat_progress_create_index shows the phase, the pages done, and the session it waits on (current_locker_pid), from anywhere in the database: an uncommitted write to the table, a repeatable read transaction, an open cursor, or a running query. An idle read committed session doesn't count.
4. Check indisvalid when it's done. A failed, canceled, or timed-out build leaves an invalid index behind (When a concurrent build fails). Drop it concurrently before you retry.

                 phase                  | blocks_done | blocks_total | locker
----------------------------------------+-------------+--------------+--------
 waiting for writers before build       |           0 |            0 |   2081
 building index: scanning table         |       34375 |        35173 |
 building index: sorting live tuples    |       35173 |        35173 |
 building index: loading tuples in tree |           0 |            0 |
 index validation: scanning index       |        7415 |         7703 |
 index validation: sorting tuples       |           0 |            0 |
 index validation: scanning table       |       34855 |        35173 |
 waiting for old snapshots              |       35172 |        35173 |   2082

select lab.prove('a plain build that is only waiting for its lock already blocks the inserts behind it',
  (select not granted from pg_locks where pid = :b_web and relation = 'events'::regclass));
select lab.prove('a 1s lock_timeout cancels a concurrent build waiting on an old snapshot, leaving an invalid index',
  (select indisready and not indisvalid from pg_index where indexrelid = 'kind_idx'::regclass));
select lab.prove('a second concurrent build on the same table queues behind the first',
  exists (select 1 from pg_locks where pid = :b_mig and relation = 'events'::regclass
             and mode = 'ShareUpdateExclusiveLock' and not granted));

select lab.prove('an idle read committed transaction that read another table does not hold it up',
  (select indisvalid from pg_index where indexrelid = 'kind_idx'::regclass)
  and (select state from pg_stat_activity where pid = :b_hold) = 'idle in transaction');
select lab.prove('an idle repeatable read transaction on another table parks it in "waiting for old snapshots"',
  (select current_locker_pid = :b_hold and wait_event = 'virtualxid' from build_progress)
  and pg_blocking_pids(:b_cic) = array[:b_hold]);
select lab.prove('while the unique build waits, still invalid, a duplicate insert is already rejected (23505)',
  (select not indisvalid from pg_index where indexrelid = 'handles_handle_key'::regclass));

select indexrelid::regclass, indisvalid, indisready
  from pg_index where not indisvalid;
 
drop index concurrently projects_account_name_key;  -- a failed unique build

select lab.prove('every write still maintains it: it grew with 20,000 inserts',
  pg_relation_size('kind_idx') > :k0);
select lab.prove('and leaves an index behind, indisvalid = false (and here indisready = false)',
  (select not indisvalid and not indisready from pg_index
    where indexrelid = 'projects_account_name_key'::regclass));
create unique index concurrently if not exists projects_account_name_key
  on projects (account_id, name);
select lab.prove('"if not exists" after a failure does nothing and reports success: still invalid',
  (select not indisvalid from pg_index where indexrelid = 'projects_account_name_key'::regclass));

Build a unique index concurrently, then attach it

Adding a unique constraint directly builds its index under AccessExclusive. Build the index concurrently, then attach it. The attach is catalog only but still takes a brief AccessExclusive, so it runs under lock_timeout like any other ALTER:

create unique index concurrently users_email_key on users (email);
alter table users add constraint users_email_key unique using index users_email_key;

A primary key works the same way (primary key using index) once its columns are NOT NULL; if they aren't, the attach runs SET NOT NULL and scans the table under that lock.

Never run a plain REINDEX on a live table. It takes AccessExclusive on the index, and because planning any query locks every index of its table, even a primary key lookup that never touches the rebuilt index waits until it commits. Rebuild with reindex index concurrently (PostgreSQL 12+) instead. A partitioned table refuses concurrently outright, so build each partition's index concurrently and attach them to the parent's.

select lab.prove('and that blocks even a primary key lookup: planning it opens every index on the table',
  (select wait_event from pg_stat_activity where pid = :b_web) = 'relation');
select lab.prove('reindex concurrently builds a second copy beside the old one, named _ccnew',
  (select command = 'REINDEX CONCURRENTLY' from build_progress)
  and to_regclass('events_created_at_idx_ccnew') is not null);

-- 1. the parent's index: catalog only, 2 ms, marked invalid
create index events_m_kind_idx on only events_m (kind);
 
-- 2. each partition, concurrently, then attach it (one pair per partition)
create index concurrently events_m_2026_07_kind_idx on events_m_2026_07 (kind);
alter index events_m_kind_idx attach partition events_m_2026_07_kind_idx;
-- ... and the same for events_m_2026_08 and events_m_2026_09

select lab.prove('a plain create index on the parent takes Share on every partition',
  strongest(:'pm_locks', 'events_m_2026_07') = 'ShareLock'
  and strongest(:'pm_locks', 'events_m_2026_08') = 'ShareLock'
  and strongest(:'pm_locks', 'events_m_2026_09') = 'ShareLock');
select lab.prove('after the last attach, it turns valid on its own',
  (select indisvalid from pg_index where indexrelid = 'events_m_kind_idx'::regclass)
  and (select count(*) from pg_inherits where inhparent = 'events_m_kind_idx'::regclass) = 3);

Widening a type is free; most other type changes rewrite

Loosening a limit is free. Adding a limit, changing scale, or changing representation rewrites the table and every index on the column. On a 100,000 row table:

So express length limits as CHECK constraints. For timestamps, the conversion reads each old value as a time in the session's zone. If the stored values really are UTC, set timezone = 'UTC' first: the bytes don't change and nothing is rewritten. If they were written as local wall-clock time, set the session to that zone and accept the rewrite: converting them as UTC would skip the rewrite but shift every value by the offset.

An integer primary key fails inserts at 2.1 billion, and alter column id type bigint rewrites the table and every index under AccessExclusive: hours of outage at that size. The online fix is a small project built from this chapter's parts, it takes weeks to do safely, and it's entirely avoidable: use bigint from day one.

For the tables you already have, graph every sequence against its maximum and alert long before it gets there:

select schemaname, sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 1) as pct_used
  from pg_sequences
 order by pct_used desc nulls last;

One trap: a database upgraded from before PostgreSQL 10 can have serial columns of type integer backed by bigint sequences. Those report a fraction of a percent while the column itself is close to overflowing, so compare last_value against the column's type, not just the sequence's max_value.

-- rewrites() compares pg_relation_filenode('tc') before and after the change
select lab.prove('varchar(20) to varchar(50): no rewrite', not rewrites('alter column v20 type varchar(50)'));
select lab.prove('text to varchar(100): rewrite, even though every value fits', rewrites('alter column t2 type varchar(100)'));
select lab.prove('int to bigint: rewrite', rewrites('alter column i type bigint'));
select lab.prove('numeric(12,2) to numeric(12,4): rewrite', rewrites('alter column n2 type numeric(12,4)'));
select lab.prove('timestamp to timestamptz with the session in UTC: no rewrite',
  not rewrites('alter column ts1 type timestamptz', 'UTC'));
select lab.prove('timestamp to timestamptz in another time zone: rewrite',
  rewrites('alter column ts2 type timestamptz', 'America/New_York'));

ERROR:  nextval: reached maximum value of sequence "tickets_id_seq" (2147483647)

do $$
begin
  insert into tickets (title) values ('c');
  raise exception 'no error';
exception when sqlstate '2200H' then
  if sqlerrm <> 'nextval: reached maximum value of sequence "tickets_id_seq" (2147483647)' then
    raise exception 'NOT PROVED: %', sqlerrm;
  end if;
end $$;
select lab.prove('the third insert fails: nextval reached the int maximum (2147483647)', true);
select lab.prove('pg_sequences shows tickets_id_seq at 100 percent of its maximum',
  (select round(100.0 * last_value / max_value, 1) = 100.0
   from pg_sequences where sequencename = 'tickets_id_seq'));

Renames ship as expand and contract

rename column is catalog only. The problem is the dozens of app processes that know the old name: code and schema can't change at the same instant, so whichever goes first breaks the other. Never make a change that old and new code can't both survive. Split it into expand and contract, with deploys between:

  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐
  │ 1. expand    │  │ 2. deploy    │  │ 3. backfill  │  │ 4. deploy    │
  │ add new col  │─▶│ write both,  │─▶│ copy old to  │─▶│ read new,    │
  │ (nullable)   │  │ read old     │  │ new, batched │  │ write both   │
  └──────────────┘  └──────────────┘  └──────────────┘  └──────┬───────┘
                                                               │
                    ┌──────────────┐  ┌──────────────┐         │
                    │ 6. contract  │  │ 5. deploy    │         │
                    │ drop old col │◀─│ stop writing │◀────────┘
                    │              │  │ old column   │
                    └──────────────┘  └──────────────┘

Renaming events.kind to event_type this way takes three migrations and three deploys. That's the real price of zero downtime, and the best argument for choosing names carefully. A trigger or a view can stand in for some app code, but neither removes the phases. The same shape splits columns, merges tables, and changes the ids your API hands out (Move an API off integer ids by accepting both).

Backfill in short batches that walk the primary key

update events set status = payload->>'status' is one transaction over 2 million rows: 2 million new row versions and 2 million dead ones (Updates are inserts), every row locked until commit, vacuum held back, a table's worth of WAL fired at your replicas, and if it fails at the last row, all of it rolls back. Batch it:

• Walk the primary key in ranges (id > $1 and id <= $1 + 10000 and status is null), never OFFSET. Every batch costs the same, and status is null makes the job restartable.
• Commit each batch, keeping each well under a second. A procedure can (Procedures can commit); ours filled 2 million rows in about 16 seconds.
• Expect bloat: a fast backfill can double the table before autovacuum catches up, so vacuum partway through a big one.
• Run it as a resumable job outside the schema migration, and pause it when replicas lag.

Loading new rows in bulk is a different job with better tools (Freeze the partition, attach it built, analyze the parent).

Drop code first, then the column

Dropping a column is catalog only but takes AccessExclusive, so it gets lock_timeout. The danger is the app: code that names the column fails, and so can code that doesn't, like an ORM that cached the column list at startup. Ship code that ignores the column, wait until every old process is gone, then drop it.

Dropping a table takes AccessExclusive on every table linked to it by a foreign key. Drop those keys first, one short lock_timeout statement each, so no single statement needs every lock at once; for a cheap undo, rename the table and drop it a release later.

Transactional DDL holds every lock until commit

Postgres DDL is transactional, so most tools wrap each migration in a transaction and a half-applied migration can't happen. The cost: every lock is held until commit.

begin;
alter table accounts add column region text;
alter table users    add column region text;
update accounts set region = 'us';
-- still holding AccessExclusive on accounts and users
commit;

After the update, pg_locks still shows AccessExclusiveLock on both tables, and every query on either one queues until commit.

begin;
alter table accounts add column region text;
alter table users    add column region text;
update accounts set region = 'us';
select lab.prove('after the update, the transaction still holds AccessExclusive on accounts and users',
  my_strongest('accounts') = 'AccessExclusiveLock' and my_strongest('users') = 'AccessExclusiveLock');
rollback;

Every lock you hold makes the next wait worse. So: one risky table per migration, and no data changes in schema migrations. (MySQL commits each DDL statement on its own; see DDL is transactional.)

The short version

• Every migration sets lock_timeout (1 to 5 seconds) and retries. DDL waiting for a lock blocks everything behind it, even reads.
• Know whether a change is catalog only, a scan, or a rewrite, and test on production-sized data.
• Constant defaults are instant; volatile ones like gen_random_uuid() rewrite.
• Add CHECK, FOREIGN KEY, and NOT NULL constraints NOT VALID, then VALIDATE under a lock traffic passes.
• Plain create index stops writes for the whole build; concurrently keeps them flowing for twice the reading. Use plain only on tables nobody writes.
• Run concurrent builds with lock_timeout off, and check for INVALID leftovers. Never plain reindex a live table.
• int to bigint, text to varchar(n), and scale changes rewrite.
• Renames ship as expand and contract; backfills run as batched jobs in primary-key ranges, outside the migration.

Further reading

• ALTER TABLE, PostgreSQL documentation. The lock level, rewrite behavior, and NOT VALID rules for every form.
• CREATE INDEX: building indexes concurrently, PostgreSQL documentation. What the two-phase build waits for and why it can leave an invalid index.
• Zero-downtime Postgres migrations: the hard parts, GoCardless. The lock-queue outage above, told by the team it happened to.
• Zero-downtime Postgres schema migrations need this: lock_timeout and retries, Nikolay Samokhvalov, PostgresAI. The case for rule one, with retry strategies.
• strong_migrations, Squawk, and pgroll. Tooling that encodes these rules so reviewers don't have to remember them.