Migrations Ship With the Code

The schema changes as often as the code, and a schema change that runs while two versions of the code are live must work for both. Stagedoor learned this the way most services do. ALTER TABLE seats ADD COLUMN section text NOT NULL, run by hand from a laptop during the spring on-sale, locked the table for four minutes and then broke the old code that did not send the column. The migration was correct SQL, the deploy that followed it was correct, and the combination took the hold path down during the busiest minute of the year.

Three rules follow. The migration is a versioned file in the repository, applied in order, and the database records which files it has seen. Any change the old code cannot survive is made in three steps, expand, migrate, contract, across three deploys. And the migration runs as a step of the deploy, before the new code, with a lock timeout, never by hand. The rest of this topic is what each of those costs and what each form of ALTER TABLE does to a table that 3,000 requests a second are reading.

Migrations as Code

A migration is a numbered file per change, 0042_add_seat_section.sql or the tool's equivalent, containing the statements that move the schema from version 41 to version 42. The migration runner applies the files in order and records each one it has applied in a schema_migrations table in the database itself, so that the same runner on the same database a second time applies nothing, and on a fresh database applies everything. The repository is then the only place the schema is defined, and any database can say which version it is at.

-- 0042_add_seat_section.sql
SET lock_timeout = '2s';                      -- fail fast instead of queueing behind on-sale traffic

ALTER TABLE seats ADD COLUMN section text;     -- nullable, no default: a catalogue write, milliseconds

-- the backfill is a job (0043 is the NOT NULL, after the job has finished)

The file is one statement and one setting. The column is added nullable and without a default, which Postgres does by writing a catalogue entry and touching no row, so the ACCESS EXCLUSIVE lock it needs is held for milliseconds. The lock_timeout above it is the part most migration files are missing: it says that if the lock cannot be taken within 2 seconds, because a long transaction holds the table, the migration fails with an error rather than waiting, and the deploy stops. The backfill is not in this file, and the constraint that makes the column mandatory is a later file, for reasons the next two sections give.

Expand, Migrate, Contract

A rename of a column is the clearest case. The old code reads and writes section_name; the new code wants section. Renaming in one statement breaks whichever version is not expecting the new name, and during a rolling deploy that is always one of them. So the change is made in three steps that each work for both versions. Expand: add the new column, nullable, and deploy code that writes both columns and reads the old one. Migrate: backfill the new column from the old one, in batches, and deploy code that reads the new column. Contract: once no running code touches the old column, drop it, in a third deploy.

Three deploys for one rename is the price, and it is the same shape as the API versioning in Chapter 3, where the old field is served alongside the new one until every client has moved. The NOT NULL arrives only after every row has a value, because before that it cannot be true. Nothing in the sequence requires the old code to know the new column exists, and nothing requires the new code to survive the old column's absence until the step that removes it, which is why either version can be running at any moment of the sequence.

What Locks and for How Long

Every form of ALTER TABLE takes a lock, and most forms take ACCESS EXCLUSIVE, the lock that conflicts with every other lock including a plain SELECT. What differs is how long the statement holds it, and on Postgres 18 the forms sort into three groups. Instant, meaning a catalogue write under the lock for milliseconds: adding a nullable column, adding a column with a constant default (the default is stored in the catalogue and materialized when a row is read, so no row is written), dropping a column, and adding a constraint as NOT VALID. A full scan under the lock: SET NOT NULL on an existing column, which reads every row to check that none is null, and takes as long as 40 million seats take to read. A full rewrite under the lock: adding a column with a volatile default such as clock_timestamp() or gen_random_uuid(), or changing a column's type, which writes every row and every index again.

Two forms deserve a sentence each. ADD COLUMN … NOT NULL with no default on a table that has rows cannot succeed at all: Postgres refuses it, because every existing row would hold null. What Marek's statement did before being refused was ask for the ACCESS EXCLUSIVE lock, and an organizer's CSV export, which in the spring still ran on the primary, had been reading seats for three minutes. The migration waited for the export; every hold request, needing only a row lock, waited behind the migration, because lock requests queue in order; and the hold path was down for four minutes until Marek killed the statement from psql. The four minutes were the queue, not the work. The rerun that night, with a constant default and the default dropped straight after so that the code would be forced to supply a value, went through in milliseconds and left a mandatory column that the old code's seat-map import on api-02, still serving mid-rollout, did not send; that was the second half of the outage, and the expand step above is its fix. The second form is the fix Postgres 18 made possible: a NOT NULL constraint can be added as NOT VALID, which is instant, and then validated with VALIDATE CONSTRAINT, which scans the table under a SHARE UPDATE EXCLUSIVE lock that blocks neither reads nor writes. Before 18 that two-step was available for CHECK and foreign keys and not for NOT NULL, and the workaround was a CHECK (section IS NOT NULL) constraint added NOT VALID, validated, and then used to let SET NOT NULL skip its scan.

Indexes have their own rule. CREATE INDEX takes a SHARE lock, which allows reads and blocks every write for the duration of the build; on seats during on-sale that is every hold request blocked for as long as the index takes. CREATE INDEX CONCURRENTLY builds the index without blocking writes, at the cost of two passes over the table and a rule that it cannot run inside a transaction block, so the migration runner must apply that file outside one. If it fails part way, it leaves behind an index marked invalid that must be dropped before retrying. PostgreSQL Deep Dive has the full lock table, mode by mode; the application's side is knowing which of its migration files fall into which group before the deploy runs them.

Backfills Are Jobs

Filling the new column for 40 million seats rows in one UPDATE holds a row lock on every row it has touched until it commits, writes 40 million new row versions and the WAL that describes them, and takes long enough that pg-replica-a in Topic 36 falls an hour behind replaying it. The hold path waits on the row locks; the replica serves an hour-old sales report; and if the statement is killed at minute 50, all of it rolls back. A backfill is not a migration. It is a job.

async def backfill_seat_section(svc):
    last_id = 0
    while True:
        async with svc.pool.connection() as conn, conn.transaction():
            cur = await conn.execute("""
                UPDATE seats SET section = section_name
                WHERE id IN (SELECT id FROM seats WHERE id > %s AND section IS NULL
                             ORDER BY id LIMIT 10000)
                RETURNING id""", (last_id,))
            ids = [r.id for r in await cur.fetchall()]
        if not ids:
            return                                   # every row has a value; 0043 can add the NOT NULL
        last_id = max(ids)
        await asyncio.sleep(0.1)                     # let the replica and the hold path breathe

The job walks the table by primary key, updates 10,000 rows in a transaction of its own, commits, sleeps a tenth of a second, and continues from the last id it touched. Each transaction holds locks on 10,000 rows for a few hundred milliseconds instead of on 40 million for an hour; the WAL is produced at a rate the replica can absorb; and a job killed at row 30 million resumes from row 30 million, because every batch committed. The section IS NULL filter makes it safe to run twice, which is the property Chapter 8 requires of every job. The migration file added the column; this job fills it; the next migration file, run after the job reports done, adds the constraint.

The Deploy Order

The deploy runs the migration step first, with the old code still serving, and rolls the new code out only when the step has succeeded. That order is safe only if the migration is one the old code can survive, which is what the expand step guarantees: a new nullable column that the old code never mentions costs it nothing. A migration the old code cannot survive, the drop of a column it still reads, belongs in the contract step, which runs after a deploy has replaced every instance of the old code, and never in the same deploy as the code change that stops reading the column.

The migration step also sets lock_timeout, because a deploy that queues behind on-sale traffic is worse than one that fails. With a 2-second lock timeout the migration that cannot get its lock errors out, the deploy stops with a clear message, and nothing was blocked for longer than 2 seconds; the engineer reruns it at a quieter minute. Stagedoor sets two more timeouts on the application role as standing policy: statement_timeout of 30 seconds, which would have ended the export that started the four-minute queue, and the idle_in_transaction_session_timeout of Topic 32. None of the three is a substitute for knowing which migrations are instant; all three are what turns an unknown one into a fast, visible failure.

Rollback Is Forward

Migration tools offer a down step, and for a column just added with nothing written to it, the down is a harmless drop. For a migration that has run in production for an hour, the down is a lie: the new column has data the old code never wrote, the dropped column's data is gone, and un-running the file does not un-write the rows. The plan for a bad migration is a new migration that fixes it, applied through the same runner, recorded in the same table. The expand and contract shape is what makes that survivable: because the old code still works during the fix, a bad step 2 is repaired while the service keeps serving on the shape it already understood, and nobody has to choose between a broken schema and a broken deploy.