Migrations: changing the schema under a running app

The schema is code. A migration is one numbered change to it, kept in the repo and applied in order by a tool that records which ones have run:

migrations/
  0001_create_users.sql
  0002_create_orders.sql
  0003_add_orders_status.sql      ← new in this pull request

-- 0003_add_orders_status.sql
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending';

Every environment — your laptop, CI, staging, production — runs the same files in the same order, so they end up with the same schema. Three rules keep that true:

• Never edit a migration that has run anywhere shared. Production already applied the old version and will never run your edit. Fix it with a new migration.
• Ship the DDL with the code that needs it, in the same pull request — a review that can't see the schema change can't judge the code.
• Make each migration small — one change, easy to reason about, quick to run.

Why "forward-only"

Many tools let you write a down migration that undoes the up. For some changes it's honest:

-- up                                         -- down
ALTER TABLE orders ADD COLUMN status text;    ALTER TABLE orders DROP COLUMN status;

For others it can't be:

-- up                                         -- down
ALTER TABLE users DROP COLUMN phone;          ALTER TABLE users ADD COLUMN phone text;

The "down" brings back an empty column. The phone numbers are gone; only a backup has them. The same goes for a narrowed type, merged tables or rewritten values. A rollback script that runs cleanly in CI and restores nothing in production is worse than none, because it looks like a plan.

So teams that run production databases mostly roll forward: when a migration turns out wrong, the fix is the next migration. What makes that safe isn't a down script — it's making each step small and compatible with the code that's running, which is the rest of this unit.

Goal: rename users.fullname to users.display_name, with the app serving traffic the whole time. Five steps, each one compatible with every version of the code that can be running while it happens. Each line is a separate deploy or migration.

1. Expand — migration

ALTER TABLE users ADD COLUMN display_name text;   -- nullable: old code is unaffected

2. Write both — deploy

await db.query(
  "UPDATE users SET fullname = $1, display_name = $1 WHERE id = $2",
  [name, userId],
);
// reads still use fullname

Inserts set both columns too. From now on every write keeps the new column current; rows nobody has touched since still have display_name = NULL.

3. Migrate the old rows — backfill

UPDATE users SET display_name = fullname
WHERE display_name IS NULL AND id > $1 AND id <= $2;     -- in batches; next example

The IS NULL makes it safe to re-run and safe to race with step 2's writes: a row the app already wrote is skipped.

4. Read the new column — deploy

const { rows } = await db.query("SELECT display_name FROM users WHERE id = $1", [userId]);

Keep writing both for now: if this deploy is rolled back, the previous version reads fullname, and it must still be correct.

If the new column should be required, add that now, without a long lock (the complex tier): ADD CONSTRAINT … CHECK (display_name IS NOT NULL) NOT VALID, then VALIDATE, then SET NOT NULL.

5. Contract — deploy, then migration

If fullname is NOT NULL, first ALTER TABLE users ALTER COLUMN fullname DROP NOT NULL — otherwise the inserts from the next deploy, which no longer set it, fail. Then deploy code that no longer mentions fullname at all. Then, once no running instance, worker or cron job uses the old version:

ALTER TABLE users DROP COLUMN fullname;

This is the only irreversible step, and it's last on purpose: until it runs, every earlier step can be undone by deploying the previous code. Many teams leave a gap of days before contracting.

The same shape fits most breaking changes

• Change a type (int → bigint, or text → timestamptz): add the new column, write both, backfill, switch reads, drop the old.
• Split a table: create the new table, write both, backfill, switch reads, stop writing the old.
• Add a required column: add it nullable or with a default, backfill, then constrain.

What makes it work is the ordering rule: schema changes that add things go before the code that uses them; schema changes that remove things go after the last code that used them is gone.

Most ALTER TABLE forms take ACCESS EXCLUSIVE

The manual: an ACCESS EXCLUSIVE lock is acquired unless a subform says otherwise. That lock conflicts with everything, including plain SELECT. For a metadata-only change it's held for milliseconds — once it's granted. Getting it granted is the trap.

The lock queue

Three sessions, run against a real server (PostgreSQL 14; the queueing is the same in 18):

A: BEGIN; SELECT count(*) FROM orders;        -- a long report; holds ACCESS SHARE until it ends
B: ALTER TABLE orders ADD COLUMN status text;  -- needs ACCESS EXCLUSIVE: waits for A
C: SELECT total FROM orders WHERE id = 1;      -- conflicts with B's *pending* request: waits too
-- 1.5 s later A commits; B runs; C finishes after ~1.7 s

C only needed ACCESS SHARE, which A also holds — but it queued behind B's waiting request. One slow transaction plus one "instant" migration stalls every query on the table for as long as the slow transaction lasts. That's how a one-millisecond ADD COLUMN causes an outage.

The defence is to give up quickly and retry:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN status text;
-- ERROR 55P03: canceling statement due to lock timeout   (and C proceeds)

With lock_timeout = '500ms' in the same scenario, B gave up after 500 ms and C finished right after. Retry the migration later, ideally after finding what held the lock.

Rewrites: when "ALTER" means "copy the whole table"

Some changes rewrite every row and every index, holding ACCESS EXCLUSIVE the whole time. Checked on PostgreSQL 18 by watching whether the table's storage file changed:

Note the second row's trap: every existing row gets the migration's timestamp — one value for all 100,000 rows in the test — not when the row was created. A column that means "created at" needs a real backfill, or NULL for the past.

Indexes: CREATE INDEX blocks writes

A plain CREATE INDEX lets reads continue but blocks INSERT, UPDATE and DELETE until it finishes — minutes, on a big table. CREATE INDEX CONCURRENTLY keeps writes flowing, at a price:

• It scans the table twice and waits for transactions that might use the index, so it's slower.
• It can't run inside a transaction block (25001) — if your migration tool wraps each migration in a transaction, this one needs the tool's escape hatch.
• If it fails (a deadlock, or a duplicate in a unique index), it leaves an invalid index behind that queries ignore but writes still maintain. Drop it and try again, or REINDEX INDEX CONCURRENTLY.