Database Migrations Without Downtime: A 2026 Guide

Most outages attributed to "the database" are not capacity problems — they are a single migration that took a table-level lock during peak traffic. The good news is that nearly every schema change can be expressed as a sequence of small, safe, backward-compatible steps. We build and operate production systems for a living, and our custom software practice treats "no maintenance window" as a baseline requirement, not a luxury. If you are still deciding on an engine, our Postgres vs MySQL comparison covers the operational tradeoffs that make online DDL easier or harder.

A breaking change — renaming a column, splitting a table, tightening a constraint — becomes safe when you decompose it into an expand phase, a transition phase, and a contract phase. The rule that makes it work: at every deploy boundary, both the previous and the next version of your application must run correctly against the schema that exists at that moment.

• Expand. Add the new column, table, or index. This is purely additive and breaks nothing.
• Transition. Deploy code that writes to both old and new, backfill historical data, then move reads to the new shape.
• Contract. Once nothing reads or writes the old structure, drop it in a final migration.

A "rename" that would normally lock a table and break in-flight requests turns into four boring, reversible deploys. That is the whole trick.

The danger is not the migration concept — it is specific DDL that takes an ACCESS EXCLUSIVE lock or rewrites the table. Adding an index without CONCURRENTLY blocks writes for the entire build. Knowing which statements are safe is most of the battle.

-- DANGEROUS — locks the table while the index builds
CREATE INDEX idx_orders_user ON orders (user_id);

-- SAFE — builds the index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

-- DANGEROUS on older Postgres — rewrites every row
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'open';

-- SAFE — split it: add nullable, set default, backfill, then enforce
ALTER TABLE orders ADD COLUMN status text;          -- instant
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'open';  -- instant
-- backfill in batches (see below), then:
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

• Always build indexes with CONCURRENTLY on populated tables; it is slower but never blocks writes.
• Add columns as nullable first. Setting a default is metadata-only on modern Postgres, but a backfill of existing rows is not.
• Adding a foreign key or check constraint NOT VALID first, then VALIDATE CONSTRAINT, avoids a full-table lock.
• Set a short lock_timeout on migration sessions so a blocked DDL fails fast instead of queueing behind production traffic.

A single UPDATE across millions of rows is the second most common self-inflicted outage. It holds row locks, balloons the write-ahead log, and can starve vacuum. Backfill in bounded, resumable batches instead.

// Batched, resumable backfill — a few thousand rows at a time
let lastId = 0;
const BATCH = 2000;
for (;;) {
  const rows = await db.query(
    `UPDATE orders SET status = 'open'
       WHERE id > $1 AND status IS NULL
       ORDER BY id
       LIMIT $2
     RETURNING id`,
    [lastId, BATCH],
  );
  if (rows.length === 0) break;
  lastId = rows[rows.length - 1].id;
  await sleep(50); // leave headroom for live traffic
}

• Key batches on the primary key and commit between them — short transactions release locks and let vacuum keep up.
• Make the backfill idempotent (the status IS NULL guard) so it can crash and resume without double-applying.
• Throttle. A backfill that finishes in twenty minutes with zero user impact beats one that finishes in two and spikes latency.

While historical rows backfill, new rows keep arriving. The transition phase keeps both shapes correct by writing to each and reading from the old one until the new column is fully populated and verified.

• Deploy code that writes the new column on every insert/update while still reading the old one. Now no new row is ever missing data.
• Run the backfill to fill the gap left by old rows. When it completes, every row has the new value.
• Flip reads to the new column behind a flag, watch your metrics, then stop writing the old one.
• Prefer a database trigger or application-layer dual write over a long-lived ETL job — fewer moving parts to fail mid-migration.

This is the same discipline that keeps a sharded or partitioned system consistent. For the broader scaling picture, see our guide to scaling a SaaS database.

Tooling discipline keeps migrations boring as a team grows:

• Lint migrations in CI. A guard that rejects a non-concurrent index or a blocking type change stops the outage before review.
• Test on production-shaped data. A migration that is instant on an empty staging table can lock for minutes on a hundred-million-row production table.
• Observe the rollout. Watch lock waits, replication lag, and error rates during and after each step — covered in our observability for startups guide.

If your migrations run from a background worker or a one-off job runner, the same batching and idempotency rules apply — see background jobs and queues in production.