Engineering Atlas › System Design Fundamentals

Changing a database schema without downtime

12 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Versioned migrations

Each migration is a file with an id and forward SQL. A tool records applied ids and checksums in a history table. On deploy it applies pending ones in order. If someone edits an applied migration, the checksum changes and the tool refuses to run, which catches drift between environments. Flyway, for example, stores a CRC32 checksum for each SQL migration when it runs and fails validation if the file no longer matches (Flyway docs).

Expand and contract

Renaming users.fullname to users.display_name in one step breaks whichever app version is running. Instead:

  1. Expand: add display_name as nullable. Old code ignores it.
  2. Dual write: deploy code that writes both columns and reads the old one.
  3. Backfill: copy existing rows in batches.
  4. Switch reads: deploy code that reads display_name.
  5. Stop writing the old column.
  6. Contract: drop fullname, once no running version and no rollback target uses it.

Each step is independently deployable and reversible until the final drop.

Locks

In PostgreSQL, ALTER TABLE takes an ACCESS EXCLUSIVE lock unless the docs say otherwise for that subcommand, and a multi-part statement takes the strictest lock any part needs (PostgreSQL docs). Adding a nullable column without a volatile default is a metadata change and finishes in milliseconds, but it still needs the lock. If a long-running transaction holds a weaker lock, the ALTER waits, and every new query on that table queues behind the ALTER. A 2-second wait at 5,000 QPS is 10,000 stalled queries. Always set lock_timeout (for example 2 seconds) in the migration session and retry, so a migration fails fast instead of freezing the table. It defaults to 0, which means wait forever, and the docs advise against setting it globally because it would hit every session (PostgreSQL docs). The usual culprit is a long analytics query or an idle open transaction; GoCardless lost about 15 seconds of API traffic to exactly this queue (GoCardless).

Index creation should use CREATE INDEX CONCURRENTLY, which does two table scans, waits for older transactions, and takes noticeably longer than a plain build (PostgreSQL docs). Check and foreign key constraints are safest added as NOT VALID, which skips checking existing rows, followed by a separate VALIDATE CONSTRAINT, which takes only a SHARE UPDATE EXCLUSIVE lock so reads and writes continue (PostgreSQL docs). The add step still needs a brief strong lock, and a foreign key also locks the referenced table, so lock_timeout still applies.

NOT NULL used to be the awkward case, because it could not be NOT VALID. The workaround is a validated CHECK (col IS NOT NULL); since PostgreSQL 12, SET NOT NULL then skips its full-table scan (PostgreSQL 12 notes). PostgreSQL 18, released in September 2025, accepts NOT VALID on not-null constraints directly (PostgreSQL 18 notes).

Large-table changes in MySQL

First check whether MySQL can do the change in place. Since 8.0.29, ALGORITHM=INSTANT adds or drops a column at any position by changing only metadata; 8.0.12 could add instantly only at the end (MySQL blog). INSTANT is the default in 8.4, but each instant change adds a row version and a table allows 64 of them (255 from 9.1) before it must be rebuilt, and changing a column's type still needs a full ALGORITHM=COPY (MySQL manual).

Where DDL rewrites the table, tools such as gh-ost or pt-online-schema-change create a shadow table with the new schema, copy rows in chunks, replay ongoing changes, and swap names at the end. Copy speed is throttled by replication lag. gh-ost reads the binlog instead of using triggers, so throttling truly stops its writes to the primary, and the final table swap can be postponed to a quiet moment (gh-ost).

Backfills

Backfills run as batched jobs: update 1,000 to 10,000 rows per transaction by primary key range, sleep briefly, and watch replica lag. One giant UPDATE holds locks for minutes and bloats the write-ahead log.

Down the stack

Layer 1The contract

PostgreSQL DDL is transactional: a migration wrapped in a transaction either applies completely or not at all. The exceptions matter, because CREATE INDEX CONCURRENTLY cannot run inside a transaction block and can leave an INVALID index behind on failure. A migration tool promises each version runs once, in order, and is recorded with its checksum. The lock manager promises that ACCESS EXCLUSIVE conflicts with every other lock mode, including the ACCESS SHARE taken by a plain SELECT.

Layer 2The mechanism

Lock requests on a table wait in a queue. When an ALTER TABLE is waiting for ACCESS EXCLUSIVE, new requests that conflict with it, which is all of them, queue behind it instead of jumping ahead. That is how one blocked migration stalls every reader. Adding a nullable column only inserts a row into pg_attribute. Since PostgreSQL 11, a column with a non-volatile default stores that default in attmissingval and existing rows are not touched. CREATE INDEX CONCURRENTLY scans the table twice and waits for older transactions to finish between phases. Flyway keeps a flyway_schema_history table with a CRC32 checksum per script and locks that table while it works so two runners cannot apply migrations at once. gh-ost connects to MySQL as a replica, reads the binlog to replay changes into the ghost table, and finishes with an atomic RENAME TABLE.

Layer 3The machine

A table rewrite of 180 GB reads and writes every page: at about 500 MB/s that is 6 minutes each way, plus an equal volume of WAL. After each checkpoint, the first change to a page writes the full 8 KB page image to WAL, so WAL volume during a backfill can exceed the data changed. Replicas replay WAL in a single startup process, so a burst that the primary writes in parallel can leave replicas minutes behind. Under MVCC, every UPDATE writes a new row version and leaves the old one as a dead tuple, so a full backfill can double the table's size on disk until vacuum reclaims it. Updates can stay on the same page without touching indexes (heap-only tuples) only if no indexed column changes and the page has free space, which is what a lower fillfactor reserves.

Why it's built this way

The catalog is shared by every session, so changing it needs a strong lock. Locks queue in order for fairness, so a waiting DDL blocks everyone and lock_timeout is essential. MVCC turns updates into inserts, so backfills must be batched and followed by vacuum. Replication is log shipping with serial replay, so migration speed is limited by replica lag rather than primary capacity.

Diagram

sequenceDiagram
  participant M as Migration tool
  participant DB as Database
  participant App as App fleet
  M->>DB: expand: add nullable column
  App->>DB: v2 writes old and new columns
  M->>DB: backfill in batches
  App->>DB: v3 reads new column
  App->>DB: v4 stops writing old column
  M->>DB: contract: drop old column

Worked example

An orders table holds 400 million rows, 180 GB, with 3,000 writes per second at peak. The team needs a region column, non-null, derived from shipping_country.

Expand: ALTER TABLE orders ADD COLUMN region text; with lock_timeout = '2s'. It is metadata only, so it completes in milliseconds when it gets the lock.

Dual write: deploy code that sets region on every insert and update.

Backfill: batches of 5,000 rows by id range. Each batch takes about 50 ms, so 80,000 batches take about 67 minutes of pure work. Adding a 100 ms pause to protect replicas stretches it to about 3.3 hours. WAL volume is roughly the size of rewritten rows, around 20 to 40 GB, which matters for replica catch-up and backup storage.

Constraint: ALTER TABLE orders ADD CONSTRAINT region_nn CHECK (region IS NOT NULL) NOT VALID; then VALIDATE CONSTRAINT region_nn;, which scans the table holding only a lock that allows reads and writes. Finally ALTER COLUMN region SET NOT NULL skips its scan because the validated check already proves there are no nulls, and the check can then be dropped. On PostgreSQL 18 a NOT NULL ... NOT VALID constraint gets there in fewer steps.

The naive alternative, ADD COLUMN region text NOT NULL DEFAULT compute(...), would rewrite the whole table under an exclusive lock. On 180 GB that is many minutes of full outage.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Expand and contractZero downtime, reversible stepsSeveral deploys, temporary dual-write codeAny change to a live, shared table
Single-step DDL in a maintenance windowSimpleDowntime, no rollback mid-waySmall tables or internal tools
Online schema change tool (shadow table)Handles table rewrites without long locksDoubles storage temporarily, slow, trigger or binlog complexityLarge MySQL tables needing rewrites
Migrations run at app startupNo separate stepMany instances race, failures block startupSingle-instance apps only

Pitfalls in production

Leadership lens

Check yourself

  1. Why can adding a nullable column, a millisecond operation, still cause an outage?

    Show answer

    It needs an exclusive lock. If a long transaction holds a conflicting lock, the ALTER queues, and every subsequent query on the table queues behind it.

  2. Why does expand and contract keep the drop step until after the rollback window?

    Show answer

    Rollback means running the previous version, which may still read the old column. Dropping it earlier makes rollback fail.

  3. Why use NOT VALID followed by VALIDATE for a new constraint?

    Show answer

    Adding a validated constraint scans the table under a strong lock. Splitting it enforces the rule for new rows immediately and validates old rows under a weaker lock that allows writes.

  4. What does a checksum in the migration history table protect against?

    Show answer

    Edited migration files that were already applied somewhere, which would make environments diverge silently.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →