Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

Schema migrations without downtime

Changing a table that never stops taking writes: expand, migrate and contract across deploys, dual writes, batched backfills, online index builds, code that tolerates both shapes, and the small statements that lock a table.

Five steps for one column

A parcel carrier's shipments table holds 800 million rows and takes about 1,500 writes a second. Its weight column is text, 2.5 kg and 800g, and billing wants grams as an integer it can sum. Written as one statement, that change is a single ALTER TABLE; done without downtime, it takes five steps over several deploys. The one-statement version first:

  1. The migration runs ALTER TABLE shipments ALTER COLUMN weight TYPE integer USING …. PostgreSQL takes a lock that blocks every read and write on the table and starts rewriting all 800 million rows and every index.
  2. Tracking pages, label printing and the depot scanners queue behind that lock for the hours the rewrite takes.
  3. The new code rolls out. Until the rolling deploy finishes, old instances still write 2.5 kg into a column that now takes only integers, and their inserts fail.
  4. Someone rolls back, and the old code reads an integer where it expects text.

Two things changed at the same instant: the schema and the code that talks to it. Migrating without downtime is less about clever DDL than about never asking those two to change together.

Widen, fill, switch, narrow

The model is expand, migrate, contract, in that order and across separate deploys. Expand adds the new shape beside the old: a nullable column, a new table, a new index. Migrate fills it and moves readers over. Contract removes the old shape once nothing reads or writes it. Each step must keep the schema and its data usable by every code version that may still run or be rolled back to; that is what makes a single deploy, or its rollback, safe. For the weights:

  1. Expand. ALTER TABLE shipments ADD COLUMN weight_grams integer NULL. A nullable column with no default is a metadata-only change in PostgreSQL, and in MySQL 8.0.12 and later with ALGORITHM=INSTANT: no row is rewritten. Nothing reads it yet.
  2. Dual-write. Deploy code that writes both weight and weight_grams on every insert and update. Both live in the same row, so one statement writes both and a crash cannot leave one without the other. Every new shipment is now correct in both shapes.
  3. Backfill. A worker fills weight_grams for the 800 million older rows, in batches.
  4. Switch reads. Once the backfill has finished with no row left unconverted, keep serving weight but also read weight_grams and compare the two (shadow reads); when they agree, flip a flag to serve weight_grams.
  5. Contract. Keep dual-writing while any version you could roll back to, or any flag setting, still reads weight; then stop writing it, and drop it once no version you could roll back to still reads or writes it. The drop is the one step here you cannot roll back, so it goes last, after a backup you have checked you can restore (Backups, high availability & disaster recovery).

800 million rows, a few thousand at a time

The tempting backfill is one statement, UPDATE shipments SET weight_grams = parse_weight(weight). It holds row locks on every row it touches until it commits, hours of blocked writes for any request that updates a shipment. It writes WAL for every row, so the log balloons and a replica streaming it can fall hours behind. And if it fails at row 600 million, it rolls back and starts again from zero. A batched backfill walks the table by primary key instead:

  1. The worker reads its checkpoint: the last id it finished, say 41,200,000.
  2. It updates ids 41,200,001 to 41,205,000, only where weight_grams IS NULL so it never overwrites a value the dual-writing code set, and commits.
  3. It records 41,205,000 as the new checkpoint.
  4. It checks replica lag, waits while the lag is above its threshold, and takes the next batch.

Each batch holds its locks only while it runs. A crash costs one batch, not the run, and because the update skips filled rows, redoing a batch is harmless. Start it only once the dual-write deploy has finished everywhere: an old instance that updates weight on a row the backfill already passed leaves weight_grams stale for good. Rows the parser cannot read (one says heavy) go to a list for a person, not into weight_grams as a guess. Replication & read replicas explains the lag the worker watches.

Dual-write window with a batched backfill

New rows arrive correct in both shapes; the worker walks the older rows in bounded batches and slows down when the replica falls behind; reads switch only after both columns agree.

An index that does not stop the depots

Billing will filter on weight_grams, so it needs an index; which one is for Indexing & query optimisation, and how to build it on a live table is the question here. A plain CREATE INDEX in PostgreSQL blocks writes for the whole build, and on a hot table that is an outage with a progress bar. CREATE INDEX CONCURRENTLY lets writes continue, and pays for it: it scans the table twice, cannot run inside a transaction block, and if it fails it leaves an invalid index to drop before trying again. MySQL's InnoDB builds a secondary index online (ALGORITHM=INPLACE, LOCK=NONE), with a brief metadata lock at the start and the end.

Old code, new table; new code, old table

A rolling deploy runs v12 and v13 side by side, and a rollback runs the old code against the new schema. So every migration is a compatibility contract: new code must read a row the old code wrote, and old code must not break on a row the new code wrote. In practice:

  • Never SELECT * into a struct with strict columns, or the first added column breaks the old code.
  • Add columns nullable or with a database default, so old code that does not know them can still insert.
  • Make enums and status fields accept values they do not recognise; the next version will add one.
  • Rename by adding, dual-writing and dropping: even a metadata-only RENAME COLUMN can wait on a lock, and it breaks every query that still uses the old name.
  • Version the migration scripts with the code: apply each schema change before the code that needs it ships, and drop the old shape only once no version that may still run or be rolled back to uses it.

When the copy lives in another database instead of the same row, two writes from the code can drift apart; Change data capture describes reading the source's own log instead.

The statements that look small

The riskiest migrations are the ones that look harmless in review:

  • Adding a column at all. Even a metadata-only ALTER TABLE needs a brief exclusive lock. If a long-running query holds the table, the ALTER waits, and every new query on the table queues behind it. Set a short lock_timeout and retry rather than wait.
  • A NOT NULL column with a default. PostgreSQL before 11 rewrites the table for it, and so does any version when the default is volatile, computed per row, such as gen_random_uuid().
  • A foreign key, or NOT NULL on an existing column. Each validates every row under a lock that blocks writes. In PostgreSQL, add the constraint NOT VALID (for not-null, a CHECK (weight_grams IS NOT NULL)), then VALIDATE CONSTRAINT, which scans without blocking writes; from PostgreSQL 12, SET NOT NULL uses that validated check and skips its own scan.

Test each one on a production-sized copy and time it before it meets the live table.

Rehearse the loop in Data Backfill Migration

Open the Data Backfill Migration challenge and focus on one thing: the batch loop. Prepare the batch size, cursor and target range, bound the loop with a loop limit, then validate and either complete or roll back the written batches (a dry run has nothing to roll back). Then walk it as if the worker died halfway: if the restart cannot resume from the cursor, the checkpoint is missing. Monolith to Microservices puts the whole expand-to-contract sequence around a table leaving a shared database.

Practice this