Skip to content
Tutorials Center
My learning

Topics

Lesson 85 of 125 ยท Designing Databases

Schema Migrations and Zero-Downtime Changes

Learn how to change a live database safely. Write versioned SQL migrations, track them in a table, and rename or split columns with the expand/contract pattern.

A database schema is never finished. Next month someone wants a phone number on every customer, next quarter the name column has to become first_name and last_name, and next year a table needs a new index. Changing a schema on your laptop is easy: you edit the CREATE TABLE and start over. Changing it on a production database that holds millions of rows, with an application serving traffic at the same time, is a different job entirely.

The answer is the migration: a small, numbered SQL script that moves the schema from one version to the next. Migrations are kept in version control next to your code, applied in order, and recorded in the database itself, so every environment (your laptop, the test server, production) can be brought to exactly the same state.

In this lesson you'll build a tiny migration runner out of plain SQL, learn which changes are safe to apply to a live system, and meet the expand/contract pattern, the standard recipe for changes that would otherwise break the running application. The examples run on SQLite, but the ideas apply to every database. If you need a refresher on the statements involved, see ALTER and DROP and Constraints.

What a migration is

A migration is a script with three properties:

  • Versioned. Each one has a number or timestamp, such as 0003_add_customer_phone.sql. The order is fixed.
  • Applied once. The database remembers which migrations it has already run, so running the tool twice does nothing the second time.
  • Immutable once shared. After a migration has run anywhere other than your own machine, you never edit it. If it was wrong, you write a new migration that fixes it. Editing history means two databases that both claim to be at version 3 can have different schemas.

Frameworks such as Flyway, Liquibase, Alembic, Rails and Laravel all do the same three things. The database side is simply a bookkeeping table:

SQL
CREATE TABLE schema_migrations (
  version    INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Before applying migration N, the tool checks whether row N exists. After applying it, the tool inserts row N. If a migration fails, the tool stops, and the version row is not written.

flowchart TD
  A["Start the migration tool"] --> B["Read applied versions from schema_migrations"]
  B --> C{"Is there a file with a higher version?"}
  C -- no --> D["Done: schema is up to date"]
  C -- yes --> E["Begin transaction"]
  E --> F["Run the migration's SQL"]
  F --> G["Insert row into schema_migrations"]
  G --> H["Commit"]
  H --> C

A migration runner: apply each missing file in order, and record it in the same transaction.

A migration runner in plain SQL

The playground below plays both roles at once: it holds the migrations as comments and applies them by hand. Each step is a transaction, so a failed step leaves nothing half-done (see Transactions). SQLite can even roll back schema changes, which many databases cannot.

SQL Applying migrations one by one

Press Run (or Ctrl + Enter) to see the result here.

The second result shows what a live-data migration looks like: the existing rows survive, and the new phone column is simply NULL for them. Adding a nullable column is the gentlest migration there is.

A runner also needs to know where it stands. Every database has some way to store a version number. SQLite offers a built-in integer for exactly this purpose:

SQL A version number inside the database file

Press Run (or Ctrl + Enter) to see the result here.

Most teams prefer a schema_migrations table, because it records names and timestamps too, but user_version shows the idea in its smallest form.

Rolling forward, not back

Many tools let you write a "down" migration that undoes the "up" migration. In practice, down migrations are only trustworthy for changes that lose no data. You can drop a column you just added, but once a DROP COLUMN has run, a down script can recreate the column and not its contents.

Because of that, most production teams roll forward: if migration 12 turns out to be a mistake, they write migration 13 that corrects it. The real safety net is a backup taken before the deploy (see Backup and Maintenance) and a rehearsal on a copy of production data.

Safe and unsafe changes

Not all changes are equal. What matters on a live system is whether the old version of the application can keep working while the change happens, and how long the database holds a lock.

Change Usually safe? Why
Add a nullable column Yes Old code ignores it; existing rows need no rewrite in most databases
Add a table Yes Nothing uses it yet
Add an index (online/concurrent build) Yes, with care Slows writes while it builds; PostgreSQL needs CREATE INDEX CONCURRENTLY to avoid blocking writes
Add a NOT NULL column without a default No Existing rows have no value to satisfy it
Rename a column or table No Old code still uses the old name and breaks instantly
Drop a column or table No Old code that still reads it breaks
Change a column's type Often no May rewrite the whole table under a lock, and old code may send the wrong type
Add a constraint (CHECK, FOREIGN KEY) Risky The database must check every existing row, and fails if one is bad

The risky rows have one thing in common: they change something the running application depends on. Fixing that is a matter of ordering, not cleverness.

Expand and contract

The expand/contract pattern (also called parallel change) splits a breaking change into steps that are each backward compatible. The idea: never take something away in the same release that stops using it.

  1. Expand. Add the new structure next to the old one. Nothing breaks, because nothing is removed.
  2. Migrate the data. Copy existing values into the new structure, usually in small batches.
  3. Dual write. Deploy application code that writes to both the old and the new structure and reads from the old.
  4. Switch reads. Once the data matches, deploy code that reads from the new structure (still writing to both, in case you must roll back).
  5. Contract. After the old structure is no longer used by any deployed code, drop it.
sequenceDiagram
  participant M as Migration
  participant D as Database
  participant A as Application
  M->>D: 1. Expand: add new column
  M->>D: 2. Backfill old values in batches
  A->>D: 3. Write to old and new
  A->>D: 4. Read from new
  M->>D: 5. Contract: drop old column

Five small steps replace one big, risky change. Between any two steps the system works.

Let's run the classic example: splitting name into first_name and last_name. A single ALTER TABLE ... DROP COLUMN name would break every query that reads name. Expand/contract does it without ever breaking anything.

SQL Expand, backfill, contract

Press Run (or Ctrl + Enter) to see the result here.

Notice Plato, who has no space in his name. A backfill must handle the messy rows in your data, not only the tidy ones. Always run a query like SELECT COUNT(*) FROM people WHERE first_name IS NULL after a backfill to prove nothing was skipped.

Dropping a column is the one step you cannot undo. Do it in a separate release, long after the code stopped reading it, and only after a backup. "We deleted it and nobody noticed" is a sentence you want to be true for a few weeks first.

Backfilling big tables in batches

An UPDATE over a table with 50 million rows can hold locks for minutes, fill the transaction log and starve every other query. Real backfills run in batches: update a few thousand rows, commit, pause, repeat. The WHERE ... IS NULL filter makes the job restartable, because finished rows no longer match.

SQL A restartable batch backfill

Press Run (or Ctrl + Enter) to see the result here.

A script would repeat that statement until it reports zero rows changed. Because each batch is its own short transaction, the application keeps running, and the job can be stopped and resumed at any point.

Changes SQLite cannot do in place

SQLite's ALTER TABLE is limited: it can add, rename and drop columns, but it cannot change a column's type or add a constraint to an existing table. The standard workaround is the table rebuild, which is a miniature expand/contract: create a new table, copy the data, drop the old table, rename the new one.

SQL Rebuilding a table to add a CHECK constraint

Press Run (or Ctrl + Enter) to see the result here.

PostgreSQL and MySQL can alter types and constraints directly, but on big tables they may still rewrite the whole table. There, tools such as pg_repack, gh-ost or pt-online-schema-change do the same shadow-table dance in the background.

A migration checklist

Before you ship a schema change, ask:

  1. Can the old code still run against the new schema? If not, split it using expand/contract.
  2. Can the new code run against the old schema? During a deploy, both versions run side by side for a while.
  3. How long will it lock the table? Test on a copy of production-sized data.
  4. Is every step restartable? Use IF NOT EXISTS, WHERE ... IS NULL and batches.
  5. Is there a backup, and did you rehearse restoring it?
  6. Do you verify afterwards? Row counts, null counts and a few spot checks.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should you never edit a migration that has already run in production?

    Each database records only the version number, not the file contents. If you edit an applied migration, some databases have the old schema and others the new one, while all claim to be at the same version. Write a new migration instead.
  2. 2. Which change is generally safe to apply to a live table while the old application code is still running?

    Old code ignores a column it does not know about, and existing rows simply get NULL. The other changes either break old queries or cannot be satisfied by the existing rows.
  3. 3. What is the correct order of an expand/contract change?

    You add the new structure first, fill it, move the application over, and only then remove the old structure. The old and the new coexist in the middle, so nothing breaks at any step.
  4. 4. Why are large backfills run in small batches?

    A single huge UPDATE can hold locks for minutes and bloat the transaction log. Short batches release locks quickly and can resume where they stopped.

Summary

  • A migration is a versioned, immutable SQL script; a schema_migrations table records which ones a database has applied.
  • Prefer rolling forward with a new migration over editing history or relying on down scripts.
  • Adding nullable columns and new tables is safe; renames, drops, type changes and new constraints are not.
  • Expand/contract turns a breaking change into compatible steps: expand, backfill, dual write, switch reads, contract.
  • Backfill big tables in short, restartable batches, and verify the result before you drop anything.
  • When ALTER TABLE is too limited, rebuild the table: create new, copy, drop old, rename.

Next: Reading Query Plans, where you'll learn to see exactly how the database runs your queries.