Part 5 — Building it properly › Chapter 36

Schema migrations and zero-downtime change

Versioned migrations, the expand/contract pattern, and the ALTER that locks a table for eleven minutes.

From the advert

“Utilizzo di sistemi di versionamento (Git)” — the schema is code, and code that is not in Git is a rumour. This chapter is how a schema gets a history.

The schema is code

🧒 Simple

If the only record of how the cupboard is arranged is the cupboard, then two cupboards in two houses drift apart and nobody can say which is right. Writing down each change, in order, means any empty cupboard can be turned into the correct one by replaying the list.

🎓 Professional

A migration is a numbered, immutable script that takes the schema from version n to version n+1, recorded in a table in the database itself. Any environment can be brought to any version by replaying them in order — which is what makes a new developer's laptop, CI, and production the same schema rather than three schemas with a family resemblance. Flyway, Liquibase, DbUp, Alembic, EF Core migrations and dbmate all implement the same idea.

Rule	Why
Never edit an applied migration	Its checksum is recorded. Editing it means environments that already ran it are permanently different from ones that have not
Forward-only	A “down” script is written when calm and run when panicking, is rarely tested, and cannot restore data that a drop destroyed. Roll forward with a new migration
One logical change per migration	So a failure has an obvious scope, and the log reads as a history
Data migrations are migrations too	Backfills belong in the same ordered, versioned mechanism, not in a wiki page
The migration account is not the app account	DDL rights belong to the deploy step, not to a running service (chapter 35)

The ALTER that stops the site

⚠ What each engine does when you add a column

PostgreSQL 11+: adding a nullable column, or one with a constant default, is metadata-only and instant. Adding one with a volatile default rewrites the whole table under an exclusive lock.
SQL Server 2012+ Enterprise: adding a NOT NULL column with a default is metadata-only. Standard edition rewrites.
MySQL 5.6+: many ALTERs are online, but not all — and the ones that are not take a metadata lock that queues behind every open transaction and then blocks every new one. A single long-running SELECT can turn an instant ALTER into a total outage.

✔ The habit that prevents the incident

Always set a short lock timeout before DDL. SET lock_timeout = '3s' in PostgreSQL, SET LOCK_TIMEOUT 3000 in T-SQL. If the lock cannot be taken quickly the migration fails fast and retries later — instead of queueing and blocking every subsequent query for eleven minutes. This one line has prevented more outages than any amount of planning.

Expand and contract

🎓 The pattern for changing a schema without downtime

The application and the database deploy separately, so there is always a moment when old code is running against the new schema. Every zero-downtime change follows from accepting that: make the schema change additive first, migrate the code, and only then remove the old shape.

Renaming customers.name to full_name, safely — four deploys:

Renaming a column without downtime: expand, backfill, migrate, contract — four deploys, never one.

-- 1 · EXPAND. Add the new column. Old code is completely unaffected by it.
ALTER TABLE customers ADD COLUMN full_name VARCHAR(200) NULL;  -- NULLable: metadata-only, instant

-- 2 · BACKFILL, in batches so nothing locks for long. Idempotent and resumable.
UPDATE customers SET full_name = name              -- copy the value across…
WHERE full_name IS NULL                            -- …only where it has not been copied yet…
  AND id BETWEEN :lo AND :hi;                      -- …one key range at a time

-- 3 · MIGRATE. Deploy code that WRITES BOTH columns and READS the new one.
--    Both are now correct, so either version of the application works. This is
--    the step that makes a rollback safe, and it is the one people skip.

-- 4 · CONTRACT. Once every instance is on the new code, and only then:
ALTER TABLE customers DROP COLUMN name;            -- the point of no return

Why a rename cannot be one step

During any deploy, old and new code run at the same time. A single ALTER TABLE … RENAME breaks every instance still running the old version, and it breaks them between the migration and the last pod restarting.
ADD COLUMN … NULL with no default is metadata-only on modern PostgreSQL and SQL Server — instant, whatever the table size. Add NOT NULL DEFAULT and older versions rewrite every page while holding a lock.
The backfill is written to be re-runnable: WHERE full_name IS NULL means an interrupted run simply continues. Batching by key range keeps each transaction short (chapter 31).
Every step but the last is reversible. That is the property being bought, and it is worth four deploys.

A rename is four deploys, and each one is individually reversible.

The same shape for other changes

Adding NOT NULL: add nullable → backfill → add a CHECK … NOT VALID and then VALIDATE CONSTRAINT (PostgreSQL takes only a light lock for the validation) → set NOT NULL.
Changing a type: new column, dual write, backfill, swap, drop.
Adding an index: CREATE INDEX CONCURRENTLY in PostgreSQL, or WITH (ONLINE = ON) in SQL Server Enterprise. Both are slower and do not block writes — which is the trade you want on a live table.

Testing a migration

Against a restored copy of production, or at least production-sized data. An ALTER that takes 40 ms against 200 rows tells you nothing about 200 million.
Time it, and know the number before the deploy window.
Run it twice. A migration that fails halfway must be safe to re-run — which for MySQL and Oracle is not optional, because DDL there is not transactional and a failure leaves the schema half-changed.
Have a rollback plan that is not "run the down script" — usually: the previous application version still works against the new schema, because you expanded rather than replaced.

The schema is code. If it is not in version control it is a rumour.
A migration is numbered, immutable and recorded in the database. Never edit an applied one.
Forward-only. Down scripts are written calm and run panicking, and cannot undo a drop.
Set a short lock timeout before DDL, so a blocked migration fails instead of queueing.
DDL is transactional in PostgreSQL and SQL Server, not in MySQL or Oracle — so there, every step must be safe to have half-applied.
Expand, backfill, migrate the code, contract. Old code and new schema must coexist.
Backfills go in batches, and are idempotent and resumable.
Create indexes concurrently or online on a live table.
Test against production-sized data and time it before the window.

Interview questions this chapter answers

How do you manage database schema changes?

Versioned migration scripts in the same repository as the code, applied by a tool that records what has run, forward-only, one logical change each and never edited once applied. A separate account with DDL rights runs them at deploy time; the application account has no DDL rights at all.

How would you rename a column with no downtime?

Expand and contract. Add the new column nullable; backfill in batches; deploy code that writes both and reads the new one; and once every instance is on that code, drop the old column. Four deploys, and at every point both the old and new application versions work.

A migration locked a table for eleven minutes in production. How do you prevent a repeat?

Set a short lock timeout before the DDL so it fails fast rather than queueing behind a long transaction and blocking everything behind it. Then check whether the operation is metadata-only on that engine and version, use the concurrent or online variant where one exists, and test the timing against production-sized data first.