Migrations: changing a schema without downtime

Changing an empty schema is free. Changing one with ten million rows and live traffic is where people take production down, and they do it with statements that look completely harmless.

The thing to understand: the danger is almost never the work. It is the lock.

Migrations as files

Never change production with psql by hand. Every change is a numbered, reviewed, committed file:

migrations/
  001_create_customers.sql
  002_create_subscriptions.sql
  003_add_route_to_customers.sql

The tool does not much matter — Alembic, Django migrations, Flyway, golang-migrate, Prisma Migrate, Knex. What matters:

Ordered and recorded. The tool tracks which have run, in a table in the database itself.

In version control, reviewed like code. A migration is the most dangerous code in your repository and deserves the most careful review.

Forwards only, in practice. Most tools support a down migration. In production you will almost never run one — rolling back a schema change after new data has arrived usually means losing that data. Write the down for local development, and treat a bad migration as something you fix with a new forward migration.

Tested against production-like data. A migration that runs in 200ms on your 500-row dev database can take 40 minutes on 50 million rows. Restore a production backup and time it. This is the single most valuable habit in this lesson.

The lock table you need

This is the reference. ACCESS EXCLUSIVE blocks everything, including SELECT.

The pattern: anything that scans or rewrites the table while holding ACCESS EXCLUSIVE is dangerous. Anything instant is fine.

Measured on a table of one million rows, so the differences are not theoretical:

Two things to read out of that. A constant default is instant — PostgreSQL 11 stopped rewriting the table for those, and a lot of advice on the internet predates it. A volatile default is 2,700× slower, because every row genuinely needs a different value. And NOT VALID turns a 57 ms exclusive scan into a 0.56 ms metadata change plus a 36 ms scan under a lock that blocks nobody.

Scale those to a hundred million rows and the safe ones are still sub-millisecond while the others are minutes.

And the error you get from ADD COLUMN z text NOT NULL with no default, which is the fourth row of the table above:

ERROR:  column "z" of relation "t" contains null values

The lock queue, which is the real trap

Here is the thing that catches everyone, and it is worth reading twice.

A migration needing ACCESS EXCLUSIVE must wait for existing queries to finish. While it waits, it queues — and every new query touching that table queues behind it.

1. A long analytics SELECT on `orders` is running (20 minutes left)
2. Your ALTER TABLE orders asks for ACCESS EXCLUSIVE → waits
3. Every subsequent query on `orders` queues behind your ALTER
4. Every page that reads orders stops responding

Your instant migration just caused a 20-minute outage, and it had not started yet. The table was never rewritten. The lock queue did it.

Note the precise scope: queries on that table, not every query on the server. A SELECT that never touches orders sails past. That is small comfort when orders is your busiest table, but it is worth being accurate about — I over-stated this to myself first, built the demo, and found an unrelated SELECT 1 completely unaffected.

Here is the demo, with three sessions against a one-million-row table:

Session 3 is the point. It is a trivial indexed read that should take under a millisecond, and it took five seconds, because it was queued behind a migration that does no work at all.

The defence, and it is essential:

SET lock_timeout = '3s';
ALTER TABLE customers ADD COLUMN notes text;

Put lock_timeout at the top of every migration that takes a strong lock. Repeating the experiment with lock_timeout = '2s':

The migration failed and nothing else noticed. Failing is the correct outcome — you retry when the table is quiet, and your users never find out.

statement_timeout is a different guard: it limits how long the statement runs once it has the lock. Both are useful; lock_timeout is the one that prevents outages.

And before a migration, look:

SELECT pid, now() - query_start AS duration, state, left(query, 60)
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - query_start > interval '30 seconds'
ORDER BY duration DESC;

Safe recipes

Adding a NOT NULL column

Not in one step. Four:

-- 1. add it nullable (instant)
ALTER TABLE customers ADD COLUMN notes text;

-- 2. backfill in batches, so no single transaction is long
UPDATE customers SET notes = '' WHERE notes IS NULL AND id BETWEEN 1 AND 10000;
-- repeat, or loop in application code

-- 3. add a NOT VALID check (instant, no scan)
ALTER TABLE customers ADD CONSTRAINT notes_not_null CHECK (notes IS NOT NULL) NOT VALID;

-- 4. validate it (scans, but takes only a SHARE UPDATE EXCLUSIVE lock)
ALTER TABLE customers VALIDATE CONSTRAINT notes_not_null;

NOT VALID then VALIDATE is the key trick of this lesson. NOT VALID adds the constraint instantly and enforces it on all new rows; VALIDATE checks the existing ones under a weak lock that does not block reads or writes.

On PostgreSQL 12+ you can then convert it to a real SET NOT NULL cheaply, because the planner uses the validated CHECK to skip the scan.

Why batch the backfill? A single UPDATE of ten million rows is one enormous transaction: it holds locks on every row it touches, bloats the table with dead tuples, and generates a WAL spike that can stall replication. Batch, and commit between batches.

Adding a foreign key

ALTER TABLE deliveries ADD CONSTRAINT fk_staff
  FOREIGN KEY (staff_id) REFERENCES staff(id) NOT VALID;

ALTER TABLE deliveries VALIDATE CONSTRAINT fk_staff;

Same pattern. And index the referencing column first, CONCURRENTLY, or the validation scan is much worse.

Changing a column's type

Some type changes are free because the on-disk representation is unchanged — varchar(50) to varchar(100), varchar to text. Most are not, and int to bigint on a big table rewrites it entirely.

The safe route is the new-column dance:

ALTER TABLE payments ADD COLUMN amount_paise_new bigint;

CREATE FUNCTION sync_amount() RETURNS trigger AS $$
BEGIN NEW.amount_paise_new := NEW.amount_paise; RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_amount BEFORE INSERT OR UPDATE ON payments
FOR EACH ROW EXECUTE FUNCTION sync_amount();

-- backfill in batches, then:
BEGIN;
  ALTER TABLE payments RENAME COLUMN amount_paise TO amount_paise_old;
  ALTER TABLE payments RENAME COLUMN amount_paise_new TO amount_paise;
COMMIT;

Laborious, and the reason bigint is the right default for an id in the first place. Running out of int on a primary key at 2.1 billion rows is a genuinely bad day, and it has happened to well-known companies.

Renaming anything

The lock is instant. Your deployed code is the problem. The moment you rename, every running instance referring to the old name breaks — and during a rolling deploy both versions are live.

So: do not rename in one step. Add the new name, write to both, migrate readers, then drop the old one. Or simply accept the old name. An imperfect column name is cheaper than an outage, and this is the migration people most often regret attempting.

Adding a unique constraint

CREATE UNIQUE INDEX CONCURRENTLY customers_phone_key ON customers (phone);

ALTER TABLE customers ADD CONSTRAINT customers_phone_unique
  UNIQUE USING INDEX customers_phone_key;

ALTER TABLE ... ADD UNIQUE builds the index while holding ACCESS EXCLUSIVE. Building it CONCURRENTLY first and then adopting it makes the second step instant. USING INDEX is worth remembering.

Dropping a column

The lock is instant, and PostgreSQL only marks it dead — space comes back on a later rewrite.

But deploy the code that stops using it first. A SELECT * from an old instance against a dropped column is an error. Two deploys, in order: code, then schema.

The expand–contract pattern

The general principle behind all of the above, and the one thing to remember if you remember nothing else:

1. EXPAND    add the new thing. Old and new both work.
2. MIGRATE   write to both; backfill; move readers to the new thing.
3. CONTRACT  remove the old thing, once nothing uses it.

Three deploys instead of one. Every step is independently reversible, and at no point is the schema incompatible with the code that is running — which is the actual requirement during a rolling deploy, where two versions of your application are live at once.

It is slower. It is why zero-downtime deployment works at all.

A migration checklist

Before you run one against production:

• Timed against a restored production-sized copy.
• SET lock_timeout at the top.
• Any scan or rewrite split with NOT VALID / CONCURRENTLY / batching.
• Backfills batched and committed between batches.
• Compatible with the currently deployed code, and with the next version.
• Reviewed by somebody else.
• Long-running queries checked in pg_stat_activity.
• A recent backup, and you know how long a restore takes.
• A plan for what you do if it fails halfway.

The last one is the one people skip, and it is the difference between a bad ten minutes and a bad weekend.

Check your work

Where the danger in a migration usually is. The lock, not the work.

Four properties of a good migration process. Ordered and recorded, reviewed in version control, forwards-only in practice, timed against production-sized data.

Why down migrations are rarely run in production. Rolling back a schema change after new data has arrived loses data.

What ACCESS EXCLUSIVE blocks. Everything, including SELECT.

The lock queue problem. A waiting strong lock makes every later query on that table queue behind it, so an instant migration can cause a long outage. Measured: a trivial SELECT took 5.1 s, and 0.1 s once lock_timeout was set.

Why a constant default is safe but a volatile one is not. A constant is stored as metadata (PostgreSQL 11+); a volatile default needs a distinct value per row, so the table is rewritten — measured at 0.53 ms against 1,467 ms.

The one-line defence. SET lock_timeout at the top of the migration.

The difference between lock_timeout and statement_timeout. Waiting for the lock versus running with it.

The four steps to add a NOT NULL column. Nullable, batched backfill, NOT VALID check, VALIDATE.

What NOT VALID buys. The constraint applies to new rows instantly; validation of old rows happens later under a weak lock.

Why backfills are batched. One huge transaction holds locks, bloats the table and spikes WAL.

Why bigint for an id. Converting int to bigint later rewrites the whole table.

Why renaming is dangerous even though the lock is instant. Deployed code refers to the old name, and during a rolling deploy both versions are live.

How to add a unique constraint safely. CREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT ... USING INDEX.

The order for dropping a column. Code first, then schema.

The three phases of expand–contract. Expand, migrate, contract — each independently reversible.

Practice

1. Set up a migration tool in a project and create two migrations.
2. Find the table it uses to track which have run, and read it.
3. Load a million rows. Time ADD COLUMN x text and ADD COLUMN y text NOT NULL DEFAULT ''.
4. Try ADD COLUMN z text NOT NULL with no default and read the error.
5. Try ADD COLUMN w text DEFAULT gen_random_uuid() and time it. Explain the difference.
6. The lock queue: open a session, BEGIN; SELECT * FROM t LIMIT 1; and leave it open. In a second session run an ALTER TABLE. In a third, run a SELECT. Watch the third block.
7. Repeat with SET lock_timeout = '2s' and confirm the ALTER fails instead.
8. Query pg_locks joined to pg_stat_activity during that experiment and identify the blocker.
9. Add a NOT NULL column with all four steps. Time step 3 and step 4 separately.
10. Add a CHECK constraint normally on a million rows, then NOT VALID. Compare the times.
11. Add a foreign key NOT VALID, insert a violating row, and confirm it is rejected.
12. Do the new-column dance to widen an int to bigint without downtime.
13. Add a unique constraint the slow way and the USING INDEX way. Compare the lock durations.
14. Write a three-step expand–contract plan for renaming customers.phone to customers.mobile, listing what deploys between each.
15. Restore a production-sized backup and time your riskiest migration against it.

Official documentation

• PostgreSQL — ALTER TABLE — The Notes section says exactly which operations require a rewrite. Read it before every risky migration.
• PostgreSQL — Explicit locking — The lock modes and the conflict matrix that the table in this lesson summarises.
• PostgreSQL — Client connection defaults — lock_timeout and statement_timeout.
• PostgreSQL — Monitoring: pg_locks — How to find what is blocking your migration.
• PostgreSQL — CREATE INDEX — The CONCURRENTLY section.

Next: the mistakes that are expensive to undo.