Data AccessGENERALDATABASE-SPECIFICDATABASE-SPECIFICSCALE-SPECIFIC

Schema Migrations from the Application Side

Schema change as a deployment problem: two code versions run at once, and some ALTER TABLE statements take a lock that stops the service.

What actually happensHow to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

How do I change the schema of a database that a running service is using right now?

The requirement

The orders table needs a currency column, and customer_name should become customer_id referencing a new table. The service must keep serving traffic throughout.

The obvious build

Write the migration, run it as part of the deploy, ship the code that uses the new schema in the same release. One atomic change.

Why it breaks

During a rolling deploy the old and new code run simultaneously against one schema. Old instances write rows without currency; new instances read rows expecting it (Rolling Deployments).

How it breaks in production
  • During a rolling deploy the old and new code run simultaneously against one schema. Old instances write rows without currency; new instances read rows expecting it (Rolling Deployments).
  • Dropping customer_name in the same release means every old instance that is still running starts throwing on every write.
  • Some ALTER TABLE statements take a lock that blocks reads or writes for as long as the change takes. On a large table that is an outage with a migration in the changelog.
  • The migration runs during deploy, so a failed migration halts the deploy in a state where some instances are new and some are old, and rolling back the code does not roll back the schema.
Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

  • A migration is an ordered, recorded schema change: a versioned file, applied once, tracked in a table the tool owns. That part is solved by tooling.
  • The unsolved part is temporal: schema and code deploy at different moments and roll back independently. There is always a window where version N and version N+1 both run.
  • That window makes schema changes into a compatibility problem. A change is safe only if it is compatible with the code on both sides of the deploy — which is what expand/contract encodes (Expand and Contract Migrations).
  • Locking is the second mechanism. DDL takes locks; which lock, and for how long, depends on the statement and the engine. Adding a nullable column is usually metadata-only; adding an index, rewriting a table or changing a column type may not be.
  • A DDL statement that needs a strong lock must wait for existing transactions on the table to finish — and while it waits, it queues behind them and everything else queues behind it. A long-running read can therefore turn a fast ALTER into a stall (Locks and Deadlocks).

Two versions of the code, one schema

During any rolling deploy there is a period where half the instances run the old code and half run the new one, against a single schema. The migration has already been applied. This is not an edge case — it is every deploy, and it is the reason a "simple rename" is a three-release project.

Read the steps below as the safe form of customer_name becoming customer_id. Each release is independently deployable and independently reversible, which is the property being bought.

Expand, migrate, contract
  1. 1
    R1: expand schema

    Add customer_id nullable. No code reads it yet.

    fails by Adding it NOT NULL — every existing row violates the constraint.

  2. 2
    R2: write both

    New code writes customer_name and customer_id; reads still use the old column.

    fails by Forgetting the dual write, so rows created during the backfill window are missed.

  3. 3
    Backfill

    A batched job populates customer_id for existing rows.

    fails by One giant UPDATE holding locks and bloating the table until it is killed.

  4. 4
    Verify

    Count rows where the new column is still null; must reach zero and stay there.

    fails by Skipping this and discovering the gap after the contract step.

  5. 5
    R3: read new

    Code reads customer_id, still writes both. Rollback to R2 is safe.

    fails by Reading the new column in the same release that stopped writing the old one.

  6. 6
    R4: stop writing old

    Drop the old column from the write path. Column still exists.

    fails by Nothing yet — this is the safe step, which is the point.

  7. 7
    R5: contract schema

    Drop customer_name once no running version references it.

    fails by Dropping while an instance from R3 is still alive; and the data is not coming back.

Five releases for one rename. The alternative is one release that cannot be rolled back.

The statements that stop the service

The second half of the problem has nothing to do with code versions. Some DDL takes a lock that blocks traffic, and the duration scales with table size — so the migration that was instant in a development database with a thousand rows is an outage against a hundred million.

Worse, a lock request queues: while the ALTER waits for a long-running transaction to release the table, every query arriving afterwards waits behind the ALTER. One slow report can convert a fast migration into total unavailability of that table.

Migration failures and what they look like from the outside
TriggerSymptomCauseResponse
ALTER TABLE needing a table rewrite on a large tableAll queries on that table hang; requests time out service-wideDDL holds an exclusive lock for the duration of the rewriteUse the engine's non-blocking path or an online schema change tool; run it as an operation, not a deploy step
DDL waiting behind a long-running transactionTable becomes unavailable minutes after a migration "finished"The lock request queued, and new queries queued behind itSet a short lock timeout so the DDL fails fast; kill or bound long transactions first (External Calls Inside a Transaction)
Backfill written as a single UPDATEReplication lag climbs, disk grows, migration killed halfwayOne enormous transaction holding row versions and undo/WALBatch it in a job with bounded ranges and a pause between batches
Auto-generated rename migrationColumn data missing after a successful deployDiffing produced drop + add rather than renameReview every generated migration by hand; renames go through expand/contract
Migrations run at container startDuplicate-key or partially-applied migration errors; crash loopEvery replica raced to apply the same versionRun migrations once as a distinct job with a database-level lock
Rollback of the releaseOld code errors on every requestCode rolled back; schema did notOnly deploy schema changes that the previous release tolerates

Index creation is an operation, not a statement

DATABASE-SPECIFICPostgres syntax. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, which is why it must leave the migration tool's transaction. MySQL 8.0 builds most secondary indexes online by default (ALGORITHM=INPLACE, LOCK=NONE) but silently falls back to a copy for some column types — specify the algorithm explicitly so a fallback errors instead of blocking.

Adding an index is the most common production migration and the one most often treated as trivial. The ordinary form takes a lock that blocks writes to the table for as long as the build takes; the non-blocking form takes longer, does more work, and comes with rules.

Whether the index is the right index at all is Database Engineering's question (Should I Add an Index?). Getting it in place without stopping traffic is this domain's.

Adding an index to a live table
Inside the migration transaction
-- migration 0042
BEGIN;
CREATE INDEX idx_orders_customer ON orders (customer_id);
COMMIT;
-- blocks writes to orders for the whole build
As a separate, non-blocking operation
-- run outside any transaction, as its own step
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

-- afterwards, verify it is valid:
-- SELECT indisvalid FROM pg_index
--   WHERE indexrelid = 'idx_orders_customer'::regclass;

The concurrent build does two passes and lets writes continue, at the cost of taking longer and being able to fail into an invalid index that must be dropped and rebuilt. On a small table the first form is fine; on a large one it is an outage. The size of the table, not the elegance of the migration, decides.

How to build it

Most important first.

  • Split every incompatible change into expand, migrate, contract, across at least three deploys: add the new thing, write both, backfill, read new, then remove the old (Expand and Contract Migrations).
  • Make every migration backward-compatible with the immediately previous code version. That is the property that makes a rollback survivable.
  • Add columns nullable or with a safe default; never add a NOT NULL column with no default to a populated table in one step.
  • Create indexes without blocking writes where the engine offers it, and treat those statements as operations rather than as part of a deploy.
  • Set a short lock timeout for DDL so a blocked ALTER fails fast instead of queueing the whole table behind it, and retry rather than wait.
  • Backfill in bounded batches with a pause between them, as a job — not as a statement inside the migration (Background Jobs).
  • Run migrations as a separate, explicit step with its own success signal, not as a side effect of a container starting — otherwise N replicas race to apply the same migration.

What can go wrong

Failure modes
  • An ALTER that requires a table rewrite on a table too large for the maintenance window, discovered when it is already holding a lock.
  • A blocked DDL statement queueing every subsequent query on that table: the symptom is total unavailability of one table, not slowness.
  • A backfill inside the migration running as one enormous transaction, holding locks and bloating the database until it is killed halfway.
  • Auto-generated migrations inferring a rename as drop-then-add. The deploy succeeds and the data is gone.
  • Multiple instances applying migrations at startup concurrently; some tools take an advisory lock and some do not.
  • A rollback that reverts the code but not the schema, leaving old code reading a column that no longer exists.
  • An ALTER that succeeds in staging in a second and takes an hour in production, because the row counts differ by four orders of magnitude.
What can race
  • Several instances starting at once and applying the same migration concurrently. Tools that take a database-level advisory lock serialise this; tools that do not produce duplicate or partial application.
  • A backfill racing the application: rows written by live traffic after the batch has passed them are missed unless the code also writes the new column during the expand phase.
  • DDL waiting on a lock while new queries queue behind it — a three-way pile-up in which a single long-running transaction is the root cause (Locks and Deadlocks).
Security
  • The application's runtime database user should not have DDL privileges. Migrations run as a separate, higher-privileged identity used only for that purpose (Database Privileges and Blast Radius).
  • Migrations frequently touch personal data during backfills. That is a data-handling operation with the same obligations as any other, including not dumping rows into deploy logs.
  • A migration is executable code running with elevated database rights in your deploy pipeline — it is part of the supply chain and deserves review, not a rubber stamp (Dependency Security).
Misreads
  • "The migration ran, so the deploy is safe." The migration is compatible with the code that just shipped. The question is whether it is compatible with the code still running.
  • "Adding a column is always safe." Adding a nullable column usually is. Adding one with a default that forces a rewrite, or with NOT NULL on a populated table, is not — and which is which is engine- and version-specific.
  • "We can roll back the migration." Reverting DDL that dropped data reverts the structure, not the data. Down-migrations are a convenience for development, not a production recovery plan.
  • "Migrations belong in the app start-up." Then every replica races, and a failing migration becomes a crash loop.

Operating it

How you see it in production
  • Emit a deploy marker and a migration-applied event so schema changes appear on the same timeline as latency graphs ("What Changed?" — Deploy Markers and the Invisible Deploys).
  • Watch lock waits during and immediately after a migration; a spike in waiting sessions is the signal that DDL is blocking traffic (Low CPU, High Latency: Lock Contention).
  • Track backfill progress explicitly — rows remaining, batch duration — because a backfill is a long-running job that can stall silently.
  • After a contract step, confirm no code path still references the removed column: log a warning from the old accessor for a release before deleting it.
What changes at 10x and 100x
  • Everything here is a function of table size. On a small table any migration is instant and none of this matters; the same statement on a hundred million rows is an operational event.
  • At larger scale, schema changes move from "part of a deploy" to "a planned operation with a rollback plan", often using an online schema change tool that copies the table and swaps it.
  • More replicas mean a longer window in which both versions run, so backward compatibility matters more the bigger you get, not less.
What this costs
  • Expand/contract turns one change into three deploys and leaves the system in a dual-write state in between. That is real complexity, and it is what buys you a rollback that works.
  • Non-blocking index creation is slower, cannot run inside a transaction, and can leave an invalid index behind if it fails. You trade duration for availability.
  • Batched backfills take much longer than one statement, and they let the service keep serving while they run.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

  • GENERALThe two-versions-at-once problem and expand/contract hold for any database behind any rolling deployment.
  • DATABASE-SPECIFICPostgres runs DDL inside transactions, so a failed migration rolls back cleanly — except CREATE INDEX CONCURRENTLY, which cannot run in a transaction and can leave an invalid index. MySQL 8.0 has atomic DDL per statement but a DDL statement implicitly commits any open transaction, so a multi-statement migration is not atomic there.
  • DATABASE-SPECIFICWhat is metadata-only differs by engine and version: Postgres 11+ adds a column with a non-volatile default without rewriting the table, earlier versions rewrite it. MySQL 8.0 supports instant ADD COLUMN for many cases and falls back to a copy for others. Check the documentation for your exact version before assuming a change is free.
  • SCALE-SPECIFICBelow roughly a million rows most of this is theoretical — the ALTER finishes before anyone notices. The advice becomes mandatory once a lock held for the duration of a rewrite is longer than your request timeout.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

Databaserequirements-to-schemashould-i-add-an-indexlocks-and-deadlocksreplication
Securitydatabase-privileges
Performancedeployment-markerslock-contentionreplication-lag
Cloudrolling-deploymentdeployment-strategies
Domains that do not exist yet
  • — Testing & Reliability Engineering — rehearsing a migration against a production-sized copy, and what a rollback drill looks like.