Ask a team how to run a zero-downtime database migration and the conversation goes straight to the SQL. Is this ALTER TABLE safe? Does Postgres rewrite the table? Should we reach for gh-ost or pt-online-schema-change? Those are fair questions, and they are almost never where migrations take production down.

The outage comes from the release, not the statement. A migration and the code that depends on it are two artifacts shipped on two different clocks, and most pipelines have nothing enforcing the order between them. An engineer renames a column, tests the migration on a laptop, runs it against a staging database that receives no traffic, then merges it into the feature branch. In production the old application code is still answering requests on three of six servers while the rolling deploy works through the fleet. Those ninety seconds are the incident. The ALTER TABLE finished before the deploy even started.

Postgres can add a column in a couple of milliseconds. It can also drop a table out of service for eight minutes because an analytics query was holding a conflicting lock when the DDL arrived.

Zero downtime is a property of the release, not the DDL

A migration is zero-downtime if, at every moment between the first server of a rollout and the last, at least one application version can serve correct traffic against the schema that exists at that instant. That definition says nothing about how fast the DDL runs. It is entirely about whether the old code and the new code can coexist against the same database.

Three rules follow from it. They belong in the deploy checklist, because all three get discarded under deadline pressure.

• Never ship a release where old code has to understand a new schema, or new code has to understand an old one. During a rolling deploy, both versions are live at the same time.
• Never run a destructive change in the same release as the code change that makes it safe. Dropping the old column belongs at least one full deploy cycle after the last line of code that reads it.
• Rolling back the application has to be enough to recover. If the only path back is a database restore, the design is wrong.

Expand and contract, in five steps

The pattern that satisfies those rules is expand and contract. Nothing about the pattern is clever. It costs three deploys where the naive version costs one, and that price is why teams skip it right up until the first bad Friday night. Here is the sequence applied to a rename: customer_name becomes full_name on a table with 40 million rows.

Step 1: expand

Add the new column as nullable. On Postgres 11 and later, adding a column with a constant or stable default is a catalog-only change and the table is not rewritten; a volatile default such as random() still forces a full rewrite, so leave it out. On MySQL 8.0, ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT is metadata-only for most types. Check the algorithm your version actually chose with SHOW WARNINGS instead of trusting the docs.

Step 2: dual write

Deploy code that writes both columns and still reads the old one. This release is boring by design. Its only job is to make sure no row created from now on is missing the new value.

Step 3: backfill

Run a separate job that copies historical rows into the new column in batches. Separate matters. A 40-million-row UPDATE inside the migration transaction holds locks for minutes, bloats the write-ahead log, and turns a schema change into an availability event. Backfill mechanics get their own section below.

Step 4: switch reads

Deploy code that reads the new column and continues writing both. Before this goes out, compare the two columns on a sample of rows. A query that counts mismatches will catch the case where application logic and the backfill disagree about formatting, and it costs one join.

Step 5: contract

Stop writing the old column, then remove it in a later release. If the new column needs to be NOT NULL, do not issue SET NOT NULL against a large table on an older Postgres: it takes an ACCESS EXCLUSIVE lock and scans every row. Add a CHECK (full_name IS NOT NULL) NOT VALID constraint, which only touches new rows, then VALIDATE CONSTRAINT, which takes a SHARE UPDATE EXCLUSIVE lock and does not block inserts, updates or deletes. On Postgres 12 and later, the following SET NOT NULL sees the validated check constraint and skips the scan.

What actually holds the lock

Postgres is the database where a tiny DDL becomes a visible outage, and the mechanism surprises people. Almost every ALTER TABLE takes an ACCESS EXCLUSIVE lock. The lock itself is held for milliseconds. The problem is the wait queue: when the DDL cannot acquire the lock because a 40-second reporting query is running, every query that arrives afterwards queues behind the DDL, because Postgres lock acquisition is first-in, first-out. A statement that would have taken 20 milliseconds blocks reads for the remainder of that reporting query. You can watch it happen in pg_stat_activity: the DDL sits on wait_event_type = 'Lock', and every query behind it shows the same wait. From the outside it looks like the database fell over when the migration ran.

Two settings prevent that, and both belong in the migration runner rather than in someone's memory:

• SET lock_timeout = '3s' before the DDL, with retries and backoff. A migration that cannot get the lock fails fast instead of freezing the queue, and it can try again in a quieter minute.
• SET statement_timeout on the session, so a backfill that goes sideways gets killed before anyone has to find the connection.

Indexes are their own case. CREATE INDEX blocks writes for the duration; CREATE INDEX CONCURRENTLY does not, but it scans the table twice, cannot run inside a transaction block, and leaves an INVALID index behind if it fails. That invalid index still gets updated on every write and is never used for queries, so it is pure overhead until someone drops and rebuilds it. A failed concurrent build is not a no-op, and migration tooling that runs every migration in a transaction will fail on it every single time.

MySQL moves the problem around instead of removing it. ALGORITHM=INSTANT handles adding a column, renaming one, or changing a default without touching rows. Index builds run INPLACE with LOCK=NONE, which does not block writes on the primary, but the build applies serially on replicas. A long index build on a write-heavy table produces replication lag, and if your application reads from replicas, lag is an outage with a different error message. For the changes INSTANT cannot do, gh-ost copies into a shadow table and reads the binary log, which requires ROW-format binlogs, a usable unique key, and no foreign keys on the table. pt-online-schema-change uses triggers instead, which is slower on writes but survives foreign keys. Pick based on table shape, not on which tool has the better README.

Backfills cause more incidents than schema changes

The DDL is usually a metadata operation. The backfill is the part that moves millions of rows while customers are using the system, and it is where the real risk sits.

Batch by primary key range, not by OFFSET. OFFSET 4000000 makes the database read four million rows and throw them away before it returns the ten thousand you asked for, which means the backfill gets slower as it progresses and the last batches take minutes. Keyset pagination stays flat: select rows where id > last_id order by id limit 5000.

Store the watermark in a table. Jobs die mid-run: deploys roll, pods get evicted, someone closes a laptop. A resumable backfill restarts from the last committed batch, and idempotent batches mean a double run does nothing harmful. Without both, a failure at 80 percent means starting over against a table that has been getting writes the whole time.

Throttle on replication lag, not on a sleep timer. A fixed sleep(0.1) between batches knows nothing about how far behind the replicas are on the day the table is hot. Read the lag from the replica and pause when it crosses a threshold, then resume when it recovers. That single change prevents most of the "the site was slow for two hours" reports that follow backfills.

Watch for lock contention between the backfill and normal traffic. Small batches that touch a narrow key range are far kinder than one large transaction that updates a million rows and holds row locks while it does, because a batch that grabs too much will deadlock against the checkout path and lose.

Test it like a production change

A migration that ran clean on an empty staging database has proved nothing. Restore a production-sized dump to a scratch instance, run the migration with timing on, and watch the lock waits while a load test hits the table. If a full copy is impractical, a copy-on-write clone or a shadow write path will do; an empty schema will not.

Put a linter in CI. Squawk catches the Postgres patterns that need a second look, including adding a column with a volatile default, setting NOT NULL on an existing table, and creating an index without CONCURRENTLY. Wire it as a warning first, then as a failure once the backlog is clear, or the whole team learns to bypass it.

Then test the rollback, which almost nobody does. Deploy the previous application version against the new schema and confirm it still works. That is the exact situation you will be in when a feature misbehaves at 2am, and it is the one scenario a staging environment never exercises unless someone sets it up on purpose.

The checklist

• Schema change and application change ship in separate releases.
• Expand first; contract at least one full deploy cycle later.
• No release requires old code to read a new column, or new code to read an old one.
• lock_timeout and statement_timeout set on the migration session, with retries.
• Indexes built with CONCURRENTLY, and a plan for the invalid index if the build fails.
• Backfill batched by key range, resumable, idempotent, throttled on replica lag.
• Dual writes verified by a sampled comparison query before reads switch.
• Rollback rehearsed by running the previous application version against the new schema.
• Lock waits and replication lag watched during the change window, not after it.

The DDL statement is the part you can copy from the documentation, and modern databases keep making it cheaper. The release ordering is the part that needs a decision before the branch is cut: which version of the code reads which version of the schema, at every second of the rollout. Get the sequence right and the migration runs while the site stays open. That sequence gets decided in the release plan, long before anyone opens a migration file.