Live schema migrations

A migration that passes on an empty dev database proves the SQL parses. It says nothing about how long the lock is held, whether the old application version still reads the column you dropped, or whether the rollback can reach the data.

When this fires

You are about to write, review or sequence a schema or data migration against a database that has rows in it and something reading them. It does not fire for greenfield schema design, for a throwaway local database, or for a query change with no DDL.

Procedure

1. Read the current state before writing any SQL. The live schema, the migration history, and every reader of the objects you are touching — grep the application, the reports, the jobs, the other services. A column no code in this repo reads may still be read by a deployed older version. Name the readers you found; say if you could not enumerate them.

2. Classify the change. Additive (new table, new nullable column, new index), rewriting (type change, adding NOT NULL or a unique constraint to existing data, renaming, dropping), or data-only (backfill, correction). Only additive is safe to ship as one step. Everything else gets sequenced.

3. Sequence anything non-additive as expand → migrate → contract, across separate deploys.

   • Expand — add the new shape: nullable, unconstrained, unused. Old code is unaffected.
   • Dual-write — deploy code that writes both shapes. Wait until every running instance is on that version, including anything that scales up from an older image.
   • Backfill — fill the historic rows (step 5).
   • Switch reads — deploy code that reads the new shape. Watch before going further.
   • Contract — drop the old shape in a later migration, once no supported version reads it.

   Collapsing these into one migration is the most common way a change described as backward compatible takes the application down.

4. Make the DDL lock-safe. Every statement takes a lock; what matters is which lock, for how long, and what queues behind it. A strong lock waiting on one slow query blocks every read that arrives after it.

   • Set a short lock timeout (and statement timeout) for the migration session so blocked DDL fails fast instead of stalling the table. Retry; do not wait.
   • Build indexes without holding a write lock where the engine supports it. On Postgres that is the concurrent index build — it cannot run inside a transaction block, and a failed one leaves an invalid index behind that must be dropped before retrying.
   • Add check and foreign-key constraints unvalidated first, then validate as a separate statement, so the full-table scan does not sit under a strong lock.
   • On MySQL, confirm the operation is genuinely online for that version and storage engine, or route it through online-schema-change tooling (gh-ost, pt-online-schema-change) instead.
   • Do not assume adding a column is free. Whether it rewrites the table depends on engine, version, and whether the default is a constant.

5. Batch the backfill. Bounded ranges over the primary key, committed per batch, resumable from a recorded cursor, with a pause between batches. A single statement across the whole table holds locks for its whole duration, bloats WAL/undo, and cannot be stopped halfway. Backfill in the database where the transform is expressible in SQL; reprocessing rows through the application pipeline that produced them re-runs its side effects.

6. Write the rollback and say plainly what it cannot recover. Dropping a column you just added is a real rollback. A down migration after a destructive step re-creates the shape, not the data — that rollback is a restore from backup. When that is the case, say so, name the backup that would be used, and state how long a restore takes. Never describe an irreversible migration as reversible.

7. Rehearse on a copy with realistic volume. Record per-statement duration and what each one locked. Timings from a small dataset are not evidence about production.

8. Stop before the production run. Applying to a shared or production database is an outward-facing and potentially destructive action: present the plan, the rehearsal timings, the rollback and its limits, and ask. Apply to a local, branch or disposable database yourself; promoting it is the user's call, not yours.

9. Hand off to verification. Applying is not verifying. The before-counts and checksums that database-migration-verification needs have to be captured before the migration runs — take them in this procedure or they are gone.

Checklist

• Every reader of the touched objects enumerated, or the gap named
• Change classified; anything non-additive split across deploys
• Contract step is a separate, later migration
• Lock and statement timeouts set for the migration session
• Index builds and constraint validation kept off strong locks
• Backfill batched, resumable, and expressible without re-running app side effects
• Rollback written, and its limits stated where it cannot restore data
• Rehearsed on realistic volume, with timings recorded
• Before-state counts and checksums captured for verification
• Production application left to the user, with the plan presented

Failure handling

• DDL blocks and the timeout fires — that is the timeout working. Find the blocking session, wait for a quieter window, retry. Do not raise the timeout to push it through.
• A concurrent index build fails — the leftover index is invalid and will not be used. Drop it explicitly before retrying; a retry alone does not clean it up.
• The backfill dies partway — resume from the recorded cursor. If there is no cursor, you cannot tell done rows from undone ones without a full comparison; say that rather than re-running blind.
• Rehearsal timings look fine but production is much larger — the rehearsal did not cover it. Say the lock duration is unknown at production scale instead of extrapolating.
• You cannot reach a database at all — the migration is written, not tested. Report it as written, and do not call it safe.

Evidence to report

The migration files, in the order they deploy. The classification and the deploy boundaries. Per statement: what lock it takes and how long the rehearsal took, with the row count it ran against. The backfill's batch size and resume mechanism. The rollback, and what it cannot recover. The readers you enumerated and the ones you could not. What is still unapplied and awaiting approval — stated as unapplied, not as done.