migrations, one move per page
Thirty-one moves for changing a live database without taking it down: the versioned file and its checksum, the history table that remembers, why ALTER TABLE holds the whole table, the nullable column you add first, fast defaults, indexes built concurrently and the INVALID one a failed build leaves behind, the backfill in batches, expand and contract, the rename nobody notices, dropping the column a release after the code forgot it, and the lock that keeps two migration runners from meeting.
A diagram, the command, and one thing to go run this week. That's a page.
migrations, one move per page
Thirty-one moves for changing a live database without taking it down: the versioned file and its checksum, the history table that remembers, why ALTER TABLE holds the whole table, the nullable column you add first, fast defaults, indexes built concurrently and the INVALID one a failed build leaves behind, the backfill in batches, expand and contract, the rename nobody notices, dropping the column a release after the code forgot it, and the lock that keeps two migration runners from meeting.
Set in Space Grotesk, Inter and JetBrains Mono (SIL Open Font License).
Every lock mode, clause, version gate and failure mode in this book is as the official sources state it, fetched and read during this build: PostgreSQL current documentation (postgresql.org, ALTER TABLE, Explicit Locking, CREATE INDEX, pg_index), the MySQL 8.0 Reference Manual (dev.mysql.com, InnoDB and Online DDL), the GitLab documentation on avoiding downtime in migrations, and the Redgate Flyway documentation on versioned migrations. Demand evidence from live beginner threads and recurring guide popularity, fetched this build; no facts are sourced from Reddit. Teaching conventions (one move a page) are named as conventions.
Your purchase is for personal use only. You do not have redistribution rights: please do not share, resell, or republish this book or its pages.
General information only. Not professional advice; check clauses against your own database version, which may differ.
© 2026 Steve Hodgkiss. All rights reserved. Personal use only; no redistribution rights.
Edition 1.0 · stevehodgkiss.net
Contents
Per the Redgate Flyway documentation, Versioned migrations: versioned migrations are applied to a target database in order, exactly once; each has a version, a description and a checksum; example naming V001.002__NewTwitterColumn.sql; versions are sorted numerically and migrations run in the order of their versions.
The versioned file
A migration isn't a command you type. It's a file with a version in its name, checked in beside the code: V001.002__NewTwitterColumn.sql is Flyway's own example, version, description, then SQL.
Each one runs exactly once, in version order. Nobody remembers what was run on staging in March 2024. The list of files is the list of changes, in the order they happened, forever.
If it isn't a file with a version, it's a story somebody tells at standup.
Find where your project keeps its migration files today. If the answer is a wiki page or a chat message, that's your first move.
Per the PostgreSQL documentation, ALTER TABLE, Description: an ACCESS EXCLUSIVE lock is acquired unless explicitly noted; when multiple subcommands are given, the lock acquired will be the strictest one required by any subcommand.
Why ALTER blocks
The ALTER TABLE docs open with the rule: an ACCESS EXCLUSIVE lock is acquired unless explicitly noted. Blocking is the default, not a bug. The database would rather hold the table than half-change it while rows move underneath.
And it's strictest-wins: combine subcommands and you get the strictest lock any of them needs. A harmless ADD COLUMN glued to a heavier change inherits the heavier lock.
Every trick in the rest of this book is a way to say: note it explicitly.
Count the subcommands in your next planned ALTER. Splitting them may split the lock too.
Per the PostgreSQL documentation, ALTER TABLE, ADD COLUMN: a column added with no default gets null values in existing rows; with a non-volatile DEFAULT the value is stored in metadata (see page 14). The add-nullable-first sequencing is the expand half of expand/contract as documented by GitLab's avoiding-downtime-in-migrations guide and the standard multi-release pattern.
Add it nullable
Want a NOT NULL column with values? Don't add it in one step. Step one: add the column nullable, no default. Existing rows get nulls, old code doesn't know the column exists and keeps working, the change is quick.
Then backfill. Then flip code to write it. The NOT NULL and the cleanup come last, in a later release. This is expand/contract: widen the schema first, tighten it once everyone has moved.
Every risky schema change is safe if you spread it across enough deploys.
Plan your next column as three deploys: add nullable, backfill, then constrain. Write the three tickets now.
Indexes
Concurrent index builds, the invalid index a failure leaves behind, and why the slow way is sometimes the safe way.
- 01Build it concurrently
- 02The invalid index
Per the PostgreSQL documentation, CREATE INDEX, Building Indexes Concurrently: a standard index build locks out writes (but not reads) until it's done; very large tables can take many hours to be indexed; CONCURRENTLY builds the index without taking any locks that prevent concurrent inserts, updates or deletes; the method requires two scans of the table and waiting for existing transactions to terminate, so it takes significantly longer than a standard build; a regular CREATE INDEX can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot; example: CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity).
Build it concurrently
A standard index build locks out writes until it's done, and the docs warn very large tables can take many hours. On production, that's not an option.
CREATE INDEX CONCURRENTLY takes no locks that prevent concurrent inserts, updates or deletes. It runs two scans and waits out existing transactions, so it's slower than a plain build, that's the price. Two rules: it can't run inside a transaction block, and the docs' own example is the shape to copy.
Slower to build, faster to ship. On production that trade is always right.
Take one index planned for a big table and write it as CONCURRENTLY. Check your tool lets it run outside a transaction.
Per the PostgreSQL documentation, CREATE INDEX, Building Indexes Concurrently: if a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an "invalid" index, ignored for querying purposes because it might be incomplete but still consuming update overhead; psql \d reports it as INVALID; the recommended recovery is to drop the index and try again (or REINDEX INDEX CONCURRENTLY); per the pg_index catalog, indisvalid false means the index cannot safely be used for queries but must still be modified by INSERT/UPDATE operations.
The invalid index
CONCURRENTLY has a famous failure mode. If the build hits a deadlock or a uniqueness violation, the command fails but leaves the index behind, marked invalid. psql shows it plain: INVALID.
It won't serve queries, but it still gets maintained on every write, pure overhead. The docs' recovery: drop it and build again, or REINDEX INDEX CONCURRENTLY. The catalog's indisvalid false is the same fact, machine-readable.
A failed concurrent build isn't gone. It's a passenger. Drop it.
Run \d on your biggest table today and scan the index list for the word INVALID. Most teams find one.
Per the PostgreSQL documentation, Explicit Locking 13.3.5, advisory locks: two ways to acquire, session level (held until explicitly released or the session ends, ignoring transaction semantics) and transaction level (automatically released at end of transaction, no explicit unlock); visible in pg_locks; plus the GitLab avoiding-downtime pattern of post-deployment ordering. Closing habit page synthesises the book's verified moves.
One habit
The whole book in two rules. One: never block the queue. Know which lock your statement takes, page 9, and pick the form that takes a milder one, pages 13 to 26. Two: never trust the past. Applied files don't change, data moves in batches, drops wait for the code that forgot them.
And when two migration runners might meet on one database, an advisory lock keeps them apart: transaction level releases itself at commit, session level holds until you let go, both visible in pg_locks.
Change the schema like the site is watching. It is.
Write the two rules on the wall beside your deploy button, then make your next migration obey them both.