Skip to content
Back to blog
Guides

Zero-downtime schema changes, step by step

Renaming a column is a one-line change and a three-deploy project. The expand-and-contract pattern we use to evolve production tables without anyone noticing.

Daniel Okoye

Engineering Manager, Platform

Published
Updated
Reading time
2 min
The open interior of a hard disk drive, with platters and read arm in sharp focus
Photograph via Unsplash

In a development environment, renaming a column takes one line. In production, with traffic and more than one version of your application running at once, it takes at least three deploys and a lot of discipline. This guide walks through the pattern we use for every non-trivial schema change at Leaf.

The core idea: expand, then contract

Every breaking schema change can be decomposed into additive steps and subtractive steps, and the two must never ship together. Expand by adding the new structure alongside the old. Move traffic across. Contract by removing the old structure once nothing touches it.

The reason is rolling deploys. For several minutes, old and new application code run side by side against the same database. A migration that only the new code understands will break the old code mid-deploy.

Worked example: renaming a column

We want to rename accounts.display_name to accounts.name. Here is the sequence.

Step 1 — Expand

Add the new column. It must be nullable, or have a cheap default, so the migration doesn't rewrite the table.

20261001_add_accounts_name.sql
ALTER TABLE accounts ADD COLUMN name text;

Step 2 — Write to both

Deploy application code that writes to both columns and still reads from the old one. This keeps the two in step for all new data.

typescript
await db.accounts.update(id, {
  display_name: value,
  name: value, // dual write — remove in step 5
});

Step 3 — Backfill

Copy historical values in small batches. A single large UPDATE holds locks and bloats the table, so keep each batch short and pause between them.

sql
UPDATE accounts
SET name = display_name
WHERE id IN (
  SELECT id FROM accounts
  WHERE name IS NULL
  ORDER BY id
  LIMIT 5000
);
-- repeat until 0 rows are affected

Step 4 — Switch reads

Deploy code that reads from name. Because every row is now populated and every write goes to both, this is a safe, reversible change. If anything looks wrong, roll back the deploy; the old column is still correct.

Step 5 — Contract

Once the old column has been unread for a full release cycle, stop dual-writing and drop it.

sql
ALTER TABLE accounts DROP COLUMN display_name;

Things that look safe but aren't

  1. Adding a NOT NULL column with no default rewrites or blocks the table on many database versions.
  2. Creating an index normally takes a write lock. Use the concurrent variant.
  3. Adding a foreign key validates every existing row while holding a lock. Add it as unvalidated, then validate separately.
  4. Changing a column type almost always rewrites the table. Treat it as a rename: new column, dual-write, backfill, swap.

A short checklist

Before merging any schema change, we ask: Is every step additive or subtractive, but never both? Can the previous application version run against the new schema? Can the next one run against the old? And — the question people skip — how do we undo this?

The pattern feels slow the first time. By the fifth, it's muscle memory, and the thing you stop thinking about is downtime.

Keep reading

All stories