Migrations: Changing the Floor While People Are Walking on It

The schema in Chapter 13 of the first series was created once and left alone. Real schemas are never left alone. Columns get added, types get widened, tables get split, and all of it has to happen to a database that already holds data and is serving live traffic. That process — evolving a schema in controlled, reviewable, reversible steps — is a migration. This chapter is about doing it without losing data and without taking the application down. Every ALTER here runs against a scratch schema, because the whole subject is changing tables that already exist.

A migration is a script in version control

The core discipline is simple and non-negotiable. Every schema change is a script: checked into the repository alongside the application code, applied in order, and never edited once it has run anywhere. Each migration comes in two halves: a forward script that makes the change, and a rollback script that undoes it. They are the schema equivalent of a commit and its revert.

-- 014_add_locale.up.sql   (forward)
ALTER TABLE account ADD COLUMN locale text DEFAULT 'en';

-- 014_add_locale.down.sql (rollback)
ALTER TABLE account DROP COLUMN locale;

The numbering matters because migrations must run in a fixed order; migration 15 may depend on a column 14 added. A small table in the database records which migrations have already run, so a deploy applies only the new ones. You do not have to build this yourself. Flyway, Liquibase, and Alembic — along with Rails, Django, and most ORMs — are migration runners. They track applied versions and apply the pending scripts for you. What they all share is the shape above — ordered, versioned, forward-and-back.

Idempotency: safe to run twice

Deploys get retried. A migration that half-applied, or ran once and then ran again because a pipeline hiccuped, should not blow up on the second pass. Postgres gives you IF NOT EXISTS and IF EXISTS for exactly this:

ALTER TABLE scratch.account ADD COLUMN IF NOT EXISTS locale text DEFAULT 'en';
ALTER TABLE scratch.account ADD COLUMN IF NOT EXISTS locale text DEFAULT 'en';  -- run twice

ALTER TABLE
NOTICE:  column "locale" of relation "account" already exists, skipping
ALTER TABLE

The second run noticed the column was already there, said so, and succeeded anyway. An idempotent migration turns “did this already run?” from a question you have to answer correctly into one that doesn’t matter.

DDL in a transaction — and the one that refuses

Here is a genuine Postgres superpower that many databases lack: DDL is transactional. You can wrap several schema changes in BEGIN ... COMMIT. If any of them fails, ROLLBACK undoes all of them, and the schema snaps back as if the migration never started. No half-applied migration, ever. Watch two ALTERs vanish on rollback:

BEGIN;
ALTER TABLE scratch.mig_demo ADD COLUMN note text;
ALTER TABLE scratch.mig_demo ADD COLUMN qty int;
ROLLBACK;
\d scratch.mig_demo

             Table "scratch.mig_demo"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |

Both columns are gone. This is why a good migration runner wraps each migration in a transaction by default: a failure leaves the database untouched, not stranded halfway. (MySQL, notably, does not do this. Most DDL there is auto-committed and cannot be rolled back, which is why MySQL migrations lean much harder on the expand/contract discipline below.)

There is one important exception, and it bites people. CREATE INDEX CONCURRENTLY builds an index without locking the table against writes — the only form you’d use on a live production table. And it cannot run inside a transaction block:

BEGIN;
CREATE INDEX CONCURRENTLY mig_demo_id_idx ON scratch.mig_demo (id);
ROLLBACK;

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

It manages its own multi-phase locking and has to own the transaction boundary itself. The practical consequence: a concurrent index build must be its own migration, run outside the automatic transaction wrapper. Every serious migration tool has a flag to mark a migration as “no transaction” for exactly this. (A few other commands share the restriction, VACUUM and ALTER TYPE ... ADD VALUE among them, but the concurrent index is the one you’ll hit first.)

The fast default: ADD COLUMN with a value

There is a piece of folklore that adding a column with a default to a big table is slow, because every existing row must be rewritten. That was true a decade ago. In modern Postgres, adding a column with a constant default is a metadata-only change — the default is recorded once and applied virtually on read, with no table rewrite. Time it on a 150,000-row table:

CREATE TABLE scratch.big AS SELECT * FROM order_items;   -- 149,942 rows
\timing on
ALTER TABLE scratch.big ADD COLUMN status text NOT NULL DEFAULT 'active';

ALTER TABLE
Time: 2.029 ms

Two milliseconds for a NOT NULL column with a default across 150,000 rows, and every row reads back 'active'. That is not a rewrite; that is a catalog entry. The reason to know this precisely is the flip side: a non-constant default still forces a full rewrite, because each row needs its own value. Add a column defaulted to random() to the same table:

ALTER TABLE scratch.big ADD COLUMN token double precision NOT NULL DEFAULT random();

ALTER TABLE
Time: 106.382 ms

Fifty times slower, because Postgres had to visit all 149,942 rows to give each a distinct value. So “add a column with a default” is cheap when the default is a constant and expensive when it isn’t. On a large, busy table that distinction is the difference between an instant migration and one that locks the table for the length of a rewrite.

Expand/contract: changing a column without downtime

The hardest migrations are the ones where the old and new application code must both keep working during the deploy, because you can’t update every server at the same instant. Renaming or retyping a column in one step breaks this: the moment you RENAME, the still-running old code queries a column that no longer exists. The pattern that solves it is expand/contract (also called parallel change), and it trades one risky step for four safe ones.

Say we want to rename full_name to display_name. Walk it:

-- 1. EXPAND: add the new column, nullable, no rewrite. Old code unaffected.
ALTER TABLE scratch.account ADD COLUMN display_name text;

-- 2. BACKFILL: copy existing data across (in batches on a real table).
UPDATE scratch.account SET display_name = full_name WHERE display_name IS NULL;

-- 3. (Deploy app code that writes BOTH columns and reads display_name.)
--    Then tighten the constraint now that every row has a value.
ALTER TABLE scratch.account ALTER COLUMN display_name SET NOT NULL;

-- 4. CONTRACT: once nothing reads the old column, drop it.
ALTER TABLE scratch.account DROP COLUMN full_name;

                           Table "scratch.account"
    Column    |  Type  | Collation | Nullable |           Default
--------------+--------+-----------+----------+------------------------------
 id           | bigint |           | not null | generated always as identity
 display_name | text   |           | not null |

The table now has display_name, populated and NOT NULL, and full_name is gone. The point is the ordering. Each step is safe against a mix of old and new code running at once. Step 1 adds something no one reads yet. Step 2 fills it. The app deploy switches over while both columns exist, and step 4 removes the old column only after nothing references it. Contrast a single ALTER TABLE ... RENAME COLUMN, which is atomic in the database but detonates in the fleet, where old and new binaries briefly coexist. Expand/contract is the general answer to every “change a column while the app is live” problem: never break the contract both versions rely on at the same time.

Final thoughts

Migrations are how a schema stays alive without falling over. Keep every change as an ordered, versioned pair of forward and rollback scripts in the repository, and make them idempotent so a retry is harmless. Lean on transactional DDL so a failed migration rolls back cleanly, and remember the one command — CREATE INDEX CONCURRENTLY — that must stand outside the transaction. Know that adding a column with a constant default is nearly free while a computed default rewrites the table. And when a change has to happen under live traffic, reach for expand/contract: add, backfill, switch, drop, so old and new code never disagree about what the table looks like. The database now changes safely. The last two chapters turn to the code on the other side of the connection: the application that sends these statements. We start with the oldest way it can go wrong.

Next: Little Bobby Tables: injection and parameters — building a query by string concatenation, watching it leak, and the one habit that stops it cold.