Changing the schema under a running site

Two facts that make schema changes hard

The first you have already met: during a deploy, old and new code run at the same time. The second is that a schema change takes a lock, and locks in a busy database queue.

Every technique below follows from those two.

The lock that turns a 1ms change into an outage

In Postgres, most ALTER TABLE statements take an ACCESS EXCLUSIVE lock, which conflicts with everything, including plain SELECTs.

Here is the sequence that gets people:

1. A long-running query (an analytics report, an open transaction from a stuck worker) is reading users. It has a shared lock.
2. Your ALTER TABLE users ADD COLUMN asks for an exclusive lock. It waits.
3. Every subsequent query on users queues behind your waiting lock request, because Postgres does not let new shared locks jump the queue.

The change itself takes a millisecond. The site is down for as long as that report runs. And the cause looks nothing like the symptom: your monitoring shows every query on one table timing out and a migration that has not finished.

The guard is one line before the DDL:

SET lock_timeout = '3s';
ALTER TABLE users ADD COLUMN display_name text;

Now, if the lock is not available in three seconds, your migration fails cleanly and nothing else is disturbed. Fail, retry in a minute, and let a blocked migration be a five-second annoyance instead of an incident.

Which changes are cheap and which rewrite the table

• Adding a nullable column is metadata only. Instant.
• Adding a column with a constant default has been metadata-only since Postgres 11. Before that it rewrote the whole table. A volatile default like random() still rewrites.
• Adding NOT NULL to an existing column scans the whole table under an exclusive lock. Instead, add a CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT, which takes a weaker lock.
• Changing a column type usually rewrites. varchar(50) to text is free; int to bigint is not.
• Creating an index locks out writes, unless you use CREATE INDEX CONCURRENTLY, which does not. Two caveats: it cannot run inside a transaction, so your migration tool needs to be told, and if it fails it leaves an INVALID index behind that you must drop by hand before retrying.

MySQL has its own version of this table, with different entries. The habit is the same: before you run a migration on a live database, know whether the statement rewrites the table.

Expand, migrate, contract, in detail

Renaming name to display_name is three deploys, and it is worth walking through what each one guarantees.

Deploy 1: expand. Add the new column. Change the code to write to both columns and read from the old one. Old instances write only name, which is still the source of truth, so both versions are correct at once.

SET lock_timeout = '3s';
ALTER TABLE users ADD COLUMN display_name text;

Backfill, separately from any deploy. In batches, with a pause between them:

UPDATE users SET display_name = name
WHERE display_name IS NULL AND id IN (
  SELECT id FROM users WHERE display_name IS NULL LIMIT 5000
);

Loop that until it affects zero rows. Do not write it as a single UPDATE users SET display_name = name. Ten million rows in one statement holds a transaction open for minutes, blocks autovacuum on that table, and bloats it, and if it fails at 90% you get all of it rolled back and none of the work.

Deploy 2: migrate. Switch reads to display_name. Keep writing both. Now roll back is still free, because name is still current.

Deploy 3: contract. Stop writing name. Then, in a later deploy once you are sure, drop it.

Tedious. It is also the difference between a rename and an outage, and each step is individually reversible.

Where migrations run, and the race nobody expects

If your container’s start command is npm run migrate && npm start, then deploying five containers runs the migration five times, simultaneously. Some migration tools handle this with their own lock; many produce a partial application and a wedged ledger.

Run migrations as one separate step, before the new code rolls out: a release job, a one-off task, a step in the deploy script. If you cannot, take an advisory lock so exactly one process proceeds:

SELECT pg_advisory_lock(72401);

Test against production-sized data

A migration on your development database with 200 rows tells you the SQL is valid. It tells you nothing about duration. The same statement against ten million rows can be forty minutes.

Restore a recent backup to a scratch instance and time it there. This is the only way to know, and it costs an hour of a Saturday against an outage you would otherwise discover live.

Forward-only, in practice

Write the down migration if your tool demands one, but do not plan to use it. Rolling a schema backwards after the new code has written data in the new shape loses that data. In an incident, the reliable move is to roll the code back (which the expand/migrate/contract sequence has kept safe) and then fix forward with a new migration in daylight.