Zero-Downtime Database Migrations: Expand/Contract, Locks, Tools (2026)

The riskiest line in most pull requests I review is not application code. It is the one-line ALTER TABLE that passes every test against an empty CI database and then meets a production table with a long transaction sitting on it. Zero-downtime database migrations are the discipline of changing a schema while the application keeps serving traffic: no stalled queries, and no window where running code and schema disagree. This post explains why ALTER TABLE locks in PostgreSQL and MySQL, how expand/contract works, why backfills are the slow part, which tools do what in 2026, and how I gate migrations in CI now that coding agents write so many. The commands live in the companion Postgres zero-downtime migration guide.

What is a zero-downtime database migration?

A migration is zero-downtime when two things stay true throughout. Every query against the affected tables completes in roughly its normal time, and every running version of the application (plus the one you might roll back to) can use the schema as it exists at that moment. The first is a locking problem. The second is a compatibility problem, and it catches teams who already know about locks.

GitHub's 2025 availability reports show both. On 23 April, an interaction between query load and an ongoing schema change saturated database connections and failed 2 to 5 percent of requests for about 20 minutes. On 5 August, a migration dropped a column from a table backing pull requests while the ORM still referenced it in some queries: 32 minutes, peaking at about 4 percent of web and REST API traffic. On 27 August it happened again on a Copilot table, with Copilot failures peaking at 77 percent, after which GitHub put a temporary block on all drop-column operations. Nothing exotic: a column left before its last reader did.

DORA's database change management capability calls database changes a major source of risk and delay in deployments, and recommends versioned migration scripts, online schema change tools and expand/contract. The advice predates coding agents and matters more now: generating a migration costs nothing, and a bad one costs what it always did.

Why does ALTER TABLE lock the table in PostgreSQL?

PostgreSQL has eight table-level lock modes. A plain SELECT takes ACCESS SHARE, which conflicts with exactly one mode: ACCESS EXCLUSIVE. The explicit locking docs say so in one line ("Only an ACCESS EXCLUSIVE lock blocks a SELECT"), and the ALTER TABLE reference says that ACCESS EXCLUSIVE is what you get "unless explicitly noted". Most schema changes therefore need, at least briefly, the one lock that stops reads.

Brief is usually fine. The trap is the wait. A lock request that cannot be granted joins a queue, and later requests queue behind it even if they would not conflict with the current holder. I reproduced this on PostgreSQL 18.4 with three sessions: a reporting transaction that read orders and sat open, an ALTER TABLE orders ADD COLUMN note text, and a single-row SELECT. pg_locks showed the report holding AccessShareLock, the migration waiting for AccessExclusiveLock, and the SELECT waiting for a lock it would normally get instantly, with pg_blocking_pids() pointing it at the migration. Squawk's safe migrations page describes the same effect. That is how a metadata-only change takes an application down.

The fix is boring: set lock_timeout for the migration session so the DDL gives up quickly, then retry. With lock_timeout = '2s', my migration failed after 2,003 ms, the queued query completed, and a retry loop applied the change on attempt two. Set it per session; the docs advise against putting it in postgresql.conf.

How long the lock is held depends on whether the change is metadata-only, scans the table or rewrites it. Times below are from a 5 million row, 433 MB orders table on PostgreSQL 18.4 on my laptop, so treat them as illustrative:

Change	Lock and cost	Measured	Safer route	Source
ADD COLUMN nullable, or with a constant DEFAULT	ACCESS EXCLUSIVE, metadata only since PG 11	3.8 ms	Fine with lock_timeout	PG 11 notes
ADD COLUMN ... DEFAULT clock_timestamp() (volatile)	ACCESS EXCLUSIVE held for a full table and index rewrite	9,757 ms	Add nullable, backfill, then set the default	ALTER TABLE
SET NOT NULL	ACCESS EXCLUSIVE plus a full scan	350 ms	CHECK ... NOT VALID, VALIDATE, then SET NOT NULL (under 1 ms)	ALTER TABLE
Add NOT NULL on PG 18	NOT NULL ... NOT VALID, then VALIDATE under SHARE UPDATE EXCLUSIVE	339 ms validate, writes allowed	Use it on 18+	PG 18 notes
CREATE INDEX	SHARE: reads allowed, writes blocked for the build	n/a	CREATE INDEX CONCURRENTLY (1.9 s here, writes allowed)	CREATE INDEX
ALTER COLUMN TYPE	Usually a rewrite of table and indexes	n/a	New column plus expand/contract	ALTER TABLE
RENAME COLUMN, DROP COLUMN	ACCESS EXCLUSIVE, instant	0.4 ms, 0.8 ms	Instant for the database, breaking for old code	ALTER TABLE

Two releases matter here. PostgreSQL 18 (current minor 18.6, per the versioning page) can add NOT NULL as NOT VALID: enforced for new rows at once, validated later without blocking writes. PostgreSQL 19, at RC 1 with general availability planned for 19 November 2026, adds REPACK (CONCURRENTLY) and turns log_lock_waits on by default, so lock queues finally show up in the logs.

A migration file written with all of this in mind looks dull, which is the point:

-- 0042_expand_orders_amount.sql (PostgreSQL 14+; tested on 18.4)
SET lock_timeout = '2s';        -- give up fast instead of queueing every query
SET statement_timeout = '30s';  -- cap anything that turns out to scan

-- metadata-only: nullable, no volatile default
ALTER TABLE orders ADD COLUMN IF NOT EXISTS amount_cents bigint;

-- constraints arrive NOT VALID; a later migration runs VALIDATE CONSTRAINT
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_cents_positive CHECK (amount_cents >= 0) NOT VALID;

-- CREATE INDEX CONCURRENTLY cannot run in a transaction block,
-- so it goes in its own file with the runner's transaction turned off.

How MySQL online DDL differs: INSTANT, INPLACE and COPY

InnoDB online DDL has three algorithms, summarised in the MySQL 8.4 operations table. Adding and dropping a column default to INSTANT, and a plain rename can be instant. Setting NOT NULL is INPLACE with a rebuild but allows concurrent DML. Changing a data type is COPY only, with no concurrent DML. Each instant add or drop creates a row version, capped at 64 (255 as of MySQL 9.1.0), after which you need a rebuild. MySQL 9.7.0, released 21 April 2026, starts the 9.7 LTS line.

The queue problem is the same under another name. Online DDL takes a brief exclusive metadata lock to commit the new table definition, and the online DDL performance docs say a pending one "blocks subsequent transactions on the table." SHOW FULL PROCESSLIST shows Waiting for table metadata lock; the knob is lock_wait_timeout.

For changes InnoDB cannot do online, use a shadow-table copier. pt-online-schema-change (Percona Toolkit 3.7.2) creates an altered empty copy, copies rows in chunks, keeps it current with triggers, and swaps with an atomic RENAME TABLE. gh-ost does the same without triggers by tailing the binary log, which lets it throttle to a true pause and lets you postpone the cut-over; v1.1.11 in August 2026 added MariaDB support.

The expand/contract pattern, step by step

Locks are half the story. You also cannot deploy code and schema atomically, so for a while old and new application versions share one schema. Danilo Sato's 2014 write-up of parallel change names the three phases: expand the interface to support old and new, migrate the clients, contract by removing the old. Applied to renaming orders.total_cents to amount_cents, it takes six steps and at least three deploys.

The rule: every step must work with the application version running now and the one you would roll back to. Steps 1 to 5 reverse with a redeploy. Step 6 is the one-way door, and it is where GitHub's August incidents happened. Their mitigation, telling the ORM to ignore the removed column, belongs before the drop, deployed to every pod, canary and custom environment. Rails documents ignored_columns for exactly this two-step drop; most ORMs have an equivalent.

It applies beyond renames. Cloudflare's 18 November 2025 outage began with a ClickHouse permissions change that made a query lacking a database-name filter return a second schema's rows, doubling a feature file past a 200-feature limit. Expand/contract is partly the habit of asking who reads this before changing it.

Backfills: why the data migration is the slow part

DDL is usually quick once it has its lock. Copying data is not. One UPDATE orders SET amount_cents = total_cents is the worst way: a single transaction that locks every row until commit, writes its WAL in one burst for replicas to chew through, and cannot resume if it dies. Under MVCC each updated row is a new row version waiting for vacuum.

Instead, walk the primary key in ranges, update only rows that still need it (so a crashed run can restart), commit each batch, pause, and watch replica lag. Backfilling 5 million rows in 500 batches of 10,000 on my laptop, with the sync trigger firing per row, took 45 seconds and left the table at 890 MB with 4,985,875 dead tuples. On a busy primary with replicas, expect hours. pgroll batches its own backfills (1,000 rows by default, with --backfill-batch-size and --backfill-batch-delay flags), and Atlas v1.3 added Atlas Scripts for declaring backfills as reviewed, batched code.

Zero-downtime migration tools compared (2026)

The tools do three jobs: run migrations in order, change how a change is applied, or lint it first. Versions are the latest releases as of 31 October 2026.

Tool	Databases	What it does	Latest release	License
pgroll	PostgreSQL 14+	Expand/contract engine: versioned schemas of views, sync triggers, batched backfills; start expands, complete contracts	v0.16.3, 8 Sep 2026	Apache-2.0
gh-ost	MySQL, MariaDB	Triggerless shadow-table copy from the binlog, controllable cut-over	v1.1.11, 17 Aug 2026	MIT
pt-online-schema-change	MySQL	Trigger-based chunked copy, atomic RENAME TABLE	Percona Toolkit 3.7.2	GPL-2.0
Atlas	PostgreSQL, MySQL and others	Schema-as-code diffs plus migrate lint: destructive, data-dependent and backward-incompatible checks; Postgres lock checks need Atlas Pro	v1.3.0, 2 Aug 2026	Apache-2.0 (repo)
Flyway	Many	Versioned SQL runner; per-script executeInTransaction=false for concurrent index builds	13.12.0, 30 Oct 2026	Apache-2.0 (open source engine)
Liquibase	Many	Changelog runner with rollbacks	v5.0.4, 20 Aug 2026	FSL since 5.0, each version reverts to Apache 2.0 after two years
Squawk	PostgreSQL	Migration linter: lock and compatibility rules, GitHub Action	v2.71.0, 29 Oct 2026 (npm still 2.70.0)	Apache-2.0

The point people miss: Flyway and Liquibase run whatever SQL you give them. They make migrations ordered and repeatable, not safe; a CREATE INDEX without CONCURRENTLY still blocks writes. Safety comes from the file's content, from an engine like pgroll or gh-ost, or from a linter that rejects the file first. Liquibase's FSL licence still allows production use but bars competing commercial offerings.

Database migrations in CI/CD, and when an AI agent wrote them

Pipeline shape matters as much as SQL. Expand migrations run as one job before the new code rolls out; contract migrations ship in a later release. On Kubernetes that means a single Job, such as an Argo CD PreSync hook (the docs' own migration example), not an init container in every replica racing for locks. ORMs help if asked: Django ships AddIndexConcurrently, AddConstraintNotValid and ValidateConstraint, and warns that the last two belong in separate migrations.

Then lint every migration file on every pull request, whoever wrote it:

# .github/workflows/migrations.yml: lint Postgres migrations on every PR
name: migrations
on:
  pull_request:
    paths: ["migrations/**.sql"]
jobs:
  squawk:
    runs-on: ubuntu-24.04
    steps:
      - uses: actions/checkout@v7
      - uses: sbdchd/squawk-action@v2.2.0
        with:
          pattern: "migrations/*.sql"
          version: "2.70.0"          # pin squawk-cli (latest on npm), the action defaults to latest
          pg-version: "18.0"
          fail-on-violations: "true" # default is comment-only

Squawk's require-lock-timeout, require-concurrent-index-creation and constraint-missing-not-valid rules are on by default and catch most lock mistakes above. They cannot see that the ORM still reads a dropped column, which is why Squawk's docs say passing lint is not the same as safe.

That matters for AI-written migrations. When Databricks bought Neon in May 2025 it said over 80 percent of Neon databases were created by AI agents. Prisma ORM 6.15.0 detects coding agents such as Claude Code and Cursor and blocks prisma migrate reset --force until the user consents via PRISMA_USER_CONSENT_FOR_DANGEROUS_AI_ACTION; Atlas publishes an agent workflow that says lint before applying. The Replit incident, covered in AI agent failures in production, showed that a prompt file is not a control. The gate belongs where the agent cannot argue past it: a required CI check, an agent database role that does not own the tables (in PostgreSQL only an owner or superuser can ALTER or DROP them), and a human approval on contract steps. That is ordinary DevOps discipline, and it does not care who typed the SQL.

FAQ

Can you add a column without downtime in PostgreSQL?

Yes, if it is nullable or has a constant default: since PostgreSQL 11 that is metadata-only and holds its lock for milliseconds. Set lock_timeout so it cannot queue behind a long transaction, and retry. Volatile defaults, stored generated columns and identity columns force a full rewrite.

Is renaming a column safe in production?

The database side is instant, but the rename breaks every running instance of the old code when it commits. Use expand/contract (add, sync, backfill, switch reads, drop), or pgroll, which exposes old and new names through versioned views until you complete the migration.

Should database migrations run before or after the deploy?

Expand migrations run before the deploy that needs them, and must work with the code already running. Contract migrations run after, in a later release, once nothing reads the old structure. If one migration has to run both before and after a single deploy, it is two migrations.

What is the difference between expand/contract and blue-green deployment?

Blue-green swaps application environments that usually share one database, so the schema must serve blue and green at once. Expand/contract is how you make that true; they complement each other.

This quarter I would do three things on any Postgres estate I ran: put lock_timeout and a retry wrapper in the migration runner rather than trusting each file, turn on Squawk with fail-on-violations, and require every DROP COLUMN to link an already-deployed change that makes the ORM ignore that column. If PostgreSQL 19 lands on schedule, I would plan REPACK (CONCURRENTLY) for the bloat backfills leave. None of it is new. All of it is cheaper than a status page.

Related: Progressive Delivery in 2026: Feature Flags vs Canary vs Blue-Green, Cloud Outages 2025-2026: What 7 Official Postmortems Actually Teach and CI/CD in 2026: Agents Opened 1M PRs in 5 Months, Bots Write 1 in 5 Reviews, and Most Agent PRs Get No Human Look.