Schema migrations without downtime: expand and contract

During any deploy there is a window where old and new code are both running against one database. On a rolling deploy that window is minutes. On a single process it is the few seconds between the old one finishing its last request and the new one accepting its first. Either way it exists, and every safe migration follows from that fact while every unsafe one ignores it.

The rule that comes out of it is short: a migration must leave the database readable and writable by the version of the code that is already running. Adding things satisfies that. Removing things satisfies it once nothing refers to them. Renaming and retyping satisfy it never, which is why they are done as a sequence of additions and removals instead. The rest of this post is that sequence in detail, plus the lock behaviour that turns a "instant" ALTER TABLE into a thirty-second outage, and how to backfill millions of rows without holding a transaction open for an hour.

The overlap window, and what it forbids#

Picture the two versions running side by side for five minutes. Old code selects email; new code selects email_address. During the overlap, whichever column is missing takes down half your requests. That is the entire failure mode, and it explains each of these prohibitions:

• No renames. The old code does not know the new name.
• No type changes that change meaning. The old code writes the old type.
• No dropping anything the old code still selects - including columns it never uses, if it does SELECT * into a strict row mapper.
• No adding a `NOT NULL` column without a default, because the old code inserts rows without it.
• No constraint that existing rows or old writes would violate.

And two that are about the migration's own mechanics rather than the code:

• No operation that holds a strong lock for a long time, because everything else queues behind it.
• No single transaction that touches every row, because of what that does to replication, to locks and to bloat.

Expand, migrate, contract#

The pattern has three phases and usually four or five deploys. It looks like a lot of ceremony for renaming a column, and it is - right up to the first time you do it in one step at 6pm.

1. Expand. Add the new column, table or index. Nullable, with a default if it needs one. Old code ignores it because it does not know it exists; new code may use it.
2. Dual write. Deploy code that writes both the old and the new shape, and still reads the old. Nothing depends on the new data being complete yet.
3. Backfill. Copy the existing rows across in batches while everything keeps running. New rows are already correct because of step 2, so the backfill only has to deal with history.
4. Switch reads. Deploy code that reads the new shape. Keep writing both. This is the step you can roll back cheaply, which is exactly why it is its own deploy.
5. Contract. In a later release, once you are sure, stop writing the old shape and drop it.

The gap between steps 4 and 5 is the point of the whole exercise. If reading the new column goes wrong, you revert one deploy and the old column is still there, still current, still correct. Collapse the two steps and the rollback requires a restore.

A rename, deploy by deploy#

The canonical worked example. users.email becomes users.email_address, on a table with two million rows, with no downtime.

-- Deploy 1, expand. Instant on PostgreSQL 11 and later:-- a constant default is stored in the catalogue, not written to every row.ALTER TABLE users ADD COLUMN email_address text;

// Deploy 2, dual write. Every path that writes an email writes both.await db.query(  `UPDATE users SET email = $1, email_address = $1 WHERE id = $2`,  [email, id],);

-- Deploy 3, backfill, run outside the deploy in batches (see below),-- then check it actually finished:SELECT count(*) FROM usersWHERE email IS DISTINCT FROM email_address;-- expect 0

-- Deploy 4, switch reads. Code change only, no DDL.-- Deploy 5, contract, in a later release:ALTER TABLE users DROP COLUMN email;

Five steps, four of which are a normal deploy. The only genuinely irreversible one is the last, and by then the new column has been in production for a week. DROP COLUMN in PostgreSQL is a catalogue change and returns immediately, though it does not reclaim the space until the rows are rewritten - Postgres vacuum and bloat explains why the table does not shrink.

The same shape covers splitting one column into two, changing a type, moving a field to another table and extracting a lookup table. Only the middle differs.

Locks: what each operation actually takes#

Here is the thing that catches experienced people. ALTER TABLE ADD COLUMN is instant, but it still takes an ACCESS EXCLUSIVE lock, which conflicts with everything including plain SELECT. If it cannot get that lock immediately - because a report has been running for forty seconds, or an idle transaction is holding a read lock - it waits. And while it waits, every query that arrives after it queues behind it, because lock requests are ordered. A one-millisecond migration can therefore stop your site for as long as the slowest query in front of it.

The fix is two lines and they belong at the top of every migration:

SET lock_timeout = '3s';SET statement_timeout = '30s';

Now the migration either gets its lock within three seconds or fails cleanly, taking nothing with it, and you retry. A migration that fails and is retried is a non-event. A migration that waits is an incident.

Two more habits worth having. Keep each migration to one table where you can, so a failure is small. And never let a migration sit inside a long transaction that also does application work - a migration that holds ACCESS EXCLUSIVE while it waits on a slow SELECT in the same transaction is the worst of both.

Backfilling without a long transaction#

A single UPDATE users SET email_address = email on two million rows is one transaction that writes two million new row versions, generates gigabytes of write-ahead log, holds locks for its whole duration, blocks VACUUM from cleaning anything up, and pushes your replicas behind. If it fails at 90 per cent, all of it is rolled back and you start again.

Batch it instead. Walk the primary key, commit each batch, and pause briefly so that ordinary traffic and autovacuum get a turn.

-- One batch. Run in a loop from a script, not from psql by hand.WITH batch AS (  SELECT id FROM users  WHERE email_address IS NULL AND email IS NOT NULL  ORDER BY id  LIMIT 5000  FOR UPDATE SKIP LOCKED)UPDATE users uSET email_address = u.emailFROM batch bWHERE u.id = b.id;

# The loop, with a pause between batches$ while true; do    rows=$(psql -qtAX -f backfill_batch.sql)    [ "$rows" = "UPDATE 0" ] && break    sleep 0.2  done

Points that make a backfill boring instead of exciting:

• Batch size 1,000 to 10,000 rows. Big enough to be efficient, small enough that each transaction is milliseconds.
• Make it resumable. The WHERE clause should describe work not yet done, so that stopping and restarting the script is free.
• Make it idempotent. Running it twice must be harmless.
• `FOR UPDATE SKIP LOCKED` means a row currently being written by the application is skipped rather than waited on, and picked up on a later pass.
• Watch replication lag and disk while it runs. If lag grows, increase the pause rather than the batch size.
• `ANALYZE` the table afterwards, so the planner knows the new column's statistics before your new queries start using it.

A backfill on a big table is not a thing to run during the deploy. Start it, let it run for an hour or a day, and check the count of remaining rows before you ship the code that depends on it.

Indexes and constraints, the concurrent options#

New indexes are the most common migration after new columns, and the default is wrong for a live system. CREATE INDEX holds a lock that blocks writes for the entire build, which on a large table is minutes.

-- Outside a transaction. Most migration tools wrap statements in one,-- so this usually needs an explicit escape hatch.CREATE INDEX CONCURRENTLY idx_users_email_address ON users (email_address);-- If it fails, it leaves an invalid index behind. Find it:SELECT indexrelid::regclass AS index, indisvalidFROM pg_index WHERE NOT indisvalid;-- Drop it and try again:DROP INDEX CONCURRENTLY idx_users_email_address;

CONCURRENTLY makes two passes over the table and is slower in total, which is the trade you want. It cannot run inside a transaction block, so in Alembic it goes in an autocommit_block, in Django it is AddIndexConcurrently with atomic = False, and in Rails it needs disable_ddl_transaction! with algorithm: :concurrently. Getting this wrong is the most common reason a migration tool refuses the statement outright. Which index to create in the first place is Postgres indexes explained, and whether it helped is EXPLAIN ANALYZE.

Constraints have the same escape hatch. Adding NOT NULL or a foreign key normally scans the whole table while holding a strong lock; splitting it into two steps means the scan happens under a lock that allows reads and writes:

-- Step 1: brief strong lock, no scanALTER TABLE users  ADD CONSTRAINT users_email_address_not_null  CHECK (email_address IS NOT NULL) NOT VALID;-- Step 2: the scan, without blocking trafficALTER TABLE users VALIDATE CONSTRAINT users_email_address_not_null;-- Step 3: PostgreSQL 12 and later can now use that constraint-- to set NOT NULL without scanning againALTER TABLE users ALTER COLUMN email_address SET NOT NULL;ALTER TABLE users DROP CONSTRAINT users_email_address_not_null;

The same NOT VALID then VALIDATE pair works for foreign keys, and it is the difference between a deploy and an outage on any table with more than a few million rows.

MongoDB: the same pattern without the DDL#

It is tempting to think a schemaless database makes this go away. It removes the ALTER TABLE, not the overlap window. Two shapes of document still coexist, and old code still has to cope.

// Expand: nothing to do, the field simply starts appearing.// Dual write:db.users.updateOne({ _id: id }, { $set: { email: e, emailAddress: e } })// Backfill in batches, resumable by _id:db.users.updateMany(  { emailAddress: { $exists: false }, email: { $exists: true } },  [{ $set: { emailAddress: "$email" } }],)// Contract, later:db.users.updateMany({}, { $unset: { email: "" } })

Three MongoDB-specific notes. The $rename update operator exists, and using it in one pass is exactly the mistake this whole post is about - it leaves old code reading a field that has gone. Index builds since version 4.2 use a single build type that only holds an exclusive lock briefly at the start and end, so creating an index on a live collection is far less dangerous than it used to be, but it still costs memory and I/O, so do it off-peak. And if you use schema validation, tighten it with validationLevel: "moderate" first, so the new rule applies to documents you touch rather than rejecting every legacy document at once. MongoDB indexes and schema design has more on the shape decisions behind this, and PostgreSQL or MongoDB on which of the two you should have been using.

Making it repeatable#

The pattern is only as good as the process around it.

• Use a migration tool, one file per change, in version control. Alembic, Django migrations, Flyway, Liquibase, golang-migrate, whatever your stack uses. Hand-typed SQL in production is not a migration, it is an anecdote.
• Forward only. Write the down migration if your tool insists, but do not plan to use it. Recovering from a bad migration means rolling the code back and shipping a new migration forward, because a down migration that drops a column deletes the data written since.
• Do not trust a down migration. One that drops a column deletes every value written to it since the deploy, which is exactly the data you were trying to save by rolling back.
• One migration runner at a time. If two instances deploy at once, two runners can race. Some tools take a lock automatically; if yours does not, take one yourself with SELECT pg_advisory_lock(...) around the run.
• Rehearse on a copy of production. Restore a recent dump into a scratch database, run the migration, and time it. A statement that takes 200 ms on your laptop's 5,000 rows can take four minutes on the real table, and that is the number you need before you schedule anything. Staging and production on one account is a cheap way to keep that copy around, and pg_dump and pg_restore covers getting the data there.
• Take a backup before the contract step. Expanding is reversible, dropping is not. On RE:NODE, backup slots come with every plan, you can take one on demand or put it on the Schedules tab with a cron expression, and restoring is a button rather than a support conversation - but a backup nobody has restored is a hypothesis, so prove it works on a day when nothing is wrong.
• Deploy code and migrations on separate steps. On the application plans here, deploy-on-push restarts only a server that was already running and records one row per deploy, so the sequence above shows up as four or five clearly separated events rather than one mystery. The application side of the same problem - draining connections, health checks, old and new processes overlapping - is zero-downtime deploys on a small server.

Five boring deploys are cheaper than one interesting one. Nobody has ever regretted the extra release.

FAQ#

Why can I not just rename a column?

Because the code currently running does not know the new name. The rename itself is instant and safe for the database; it is the running application that breaks, in the seconds or minutes when old and new versions overlap. Add, dual write, backfill, switch, drop.

My migration is instant, so why did the site stall?

Because it had to wait for its lock. ALTER TABLE takes a lock that conflicts with everything, and while it waits, every new query queues behind it. Set lock_timeout to a few seconds at the top of the migration so it fails and retries instead of blocking traffic.

How do I add a NOT NULL column to a big table?

Add it nullable, deploy code that always sets it, backfill the old rows in batches, then add a CHECK (col IS NOT NULL) NOT VALID constraint, validate it, and set NOT NULL. On PostgreSQL 11 and later, a column with a constant default can be added directly without rewriting the table.

Can I run a migration during peak traffic?

Small ones, yes, with a lock timeout. Anything that rewrites a table, builds an index or backfills millions of rows should run when the database is quiet - not because it will fail, but because it competes for the same CPU and disk as your users.

Do I need this for MongoDB too?

Yes. There is no ALTER TABLE, but the overlap window is identical: two shapes of document exist at once and old code must tolerate both. The difference is that MongoDB will let you skip the discipline silently, and you find out later.

What do I do if a migration half finished?

Stop, check what state the database is in, and go forward. Make the migration resumable and idempotent so it can be run again, and prefer fixing forward with a new migration to running a down migration, which will happily drop data written in the meantime.