Database Migration Strategies for Zero-Downtime Deployments

The Problem with Stopping the World

Traditional database migrations bring the application down. You stop the servers, run the migration, start the servers back up, and hope nothing went wrong. This works fine for small applications with maintenance windows. It doesn't work when your SLA says 99.95% uptime, which leaves you roughly 22 minutes of downtime per month — not enough for a migration that might take 45 minutes on a large table.

Zero-downtime migrations keep the application running throughout the schema change. They're more complex to implement, but they're a solved problem. The patterns are well-established, and the tooling has matured significantly.

The Expand-Contract Pattern

Every zero-downtime migration follows the expand-contract pattern, even if the documentation doesn't call it that. Expand means adding new things (columns, tables, indexes) without removing or changing existing things. Contract means removing the old things after all code has been updated to use the new ones.

A column rename, for example, can't be done atomically without downtime. Instead, you do it in three phases:

Phase 1 (expand): Add the new column. Deploy code that writes to both old and new columns. Backfill existing rows.

Phase 2 (transition): Deploy code that reads from the new column. Old column is still populated but no longer read.

Phase 3 (contract): Drop the old column. Remove dual-write code.

Each phase is a separate deployment with its own migration. The gap between phases can be hours or days — whatever your deployment cadence supports.

Adding Columns Safely

In PostgreSQL, ALTER TABLE ADD COLUMN with a default value used to require a full table rewrite. Since PostgreSQL 11, adding a column with a constant default is nearly instantaneous — the default value is stored in the catalog, not physically written to every row. But adding a column with a volatile default (like now()) still rewrites the table.

-- Safe: instant in PostgreSQL 11+
ALTER TABLE orders ADD COLUMN status varchar(20) DEFAULT 'pending';

-- Unsafe: rewrites the table (volatile default)
ALTER TABLE orders ADD COLUMN created_at timestamp DEFAULT now();

-- Safe alternative: add without default, then backfill
ALTER TABLE orders ADD COLUMN created_at timestamp;
-- Backfill in batches (see below)

Batch Backfilling

When you need to populate a new column for existing rows, don't run a single UPDATE against the entire table. A single UPDATE on a million-row table acquires a lock that blocks writes for the duration of the update. Instead, backfill in batches:

-- Backfill in batches of 1000 rows
DO $$
DECLARE
  batch_size INT := 1000;
  updated INT;
BEGIN
  LOOP
    UPDATE orders
    SET created_at = updated_at
    WHERE id IN (
      SELECT id FROM orders
      WHERE created_at IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS updated = ROW_COUNT;
    EXIT WHEN updated = 0;
    PERFORM pg_sleep(0.1);  -- brief pause between batches
    COMMIT;
  END LOOP;
END $$;

FOR UPDATE SKIP LOCKED is the key. It prevents the backfill from blocking application writes on the same rows, and it allows multiple backfill processes to run concurrently without deadlocking.

Index Creation Without Locking

Creating an index on a large table blocks writes for the duration of the build in most databases. PostgreSQL's CREATE INDEX CONCURRENTLY builds the index without blocking, but it takes longer and has caveats: it can't run inside a transaction, and if it fails, it leaves an invalid index that needs to be dropped manually.

-- Standard index creation: blocks writes
CREATE INDEX idx_orders_status ON orders (status);

-- Concurrent index creation: doesn't block
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

-- If concurrent creation fails, clean up:
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;

MySQL's equivalent is ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE, LOCK=NONE, which uses online DDL to build the index without blocking DML operations.

Foreign Key Constraints

Adding a foreign key constraint in PostgreSQL validates existing rows by default, which requires a full table scan and holds an ACCESS EXCLUSIVE lock on the referenced table. For large tables, this can take minutes and block all reads and writes.

-- Unsafe on large tables: validates all existing rows with exclusive lock
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders(id);

-- Safe: add as NOT VALID, then validate separately
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders(id)
  NOT VALID;

-- Validate existing rows (allows concurrent reads/writes)
ALTER TABLE order_items
  VALIDATE CONSTRAINT fk_order_items_order;

The NOT VALID flag adds the constraint for new rows immediately but doesn't check existing data. The separate VALIDATE CONSTRAINT checks existing rows with a weaker lock that allows concurrent DML. Split these into two migrations with a deployment between them.

Migration Tooling

Standard migration frameworks (Alembic, Flyway, Liquibase, Rails migrations) don't prevent you from writing unsafe migrations. Some tools help catch dangerous patterns before they reach production.

strong_migrations (Ruby), django-pg-zero-downtime-migrations (Python), and squawk (SQL linter) analyze migration scripts and flag operations that could cause downtime — full table rewrites, missing CONCURRENTLY on index creation, missing NOT VALID on foreign keys.

# .squawk.toml - SQL migration linter configuration
excluded_rules = []

[rule.ban-drop-column]
# Ensure columns are deprecated before dropping
severity = "error"

[rule.require-concurrent-index-creation]
severity = "error"

[rule.prefer-robust-stmts]
severity = "warning"

Run these linters in CI on migration files. A two-line CI check that catches a table-locking migration saves you from an incident.

Rolling Back Safely

Every migration should be reversible without data loss, but the expand-contract pattern changes what "reversible" means. You don't roll back by undoing the expand — you roll back by deploying code that stops using the new schema and then running the contract migration that removes it.

This means your expand migrations should never be destructive. Adding columns, tables, and indexes is safe. Dropping columns, changing column types, and removing constraints should only happen in contract migrations that run after the new code is proven in production.

Some teams maintain explicit down migrations for every up migration. I've found these rot quickly and are rarely tested. A better approach: keep expand migrations non-destructive so there's nothing to roll back, and treat contract migrations as one-way operations that don't need rollback because the old schema is already proven unnecessary.

Table Restructuring Without Downtime

Sometimes you need to change a column type, split a table, or merge tables. These operations can't be done with a simple ALTER TABLE in most cases. The pattern: create the new structure alongside the old one, dual-write during the transition, backfill historical data, switch reads to the new structure, then drop the old one.

For column type changes (say, widening a varchar(50) to varchar(255)), PostgreSQL can handle some type changes in-place without a table rewrite. But changing from integer to bigint, or from varchar to uuid, requires rewriting every row. The safe approach:

-- Phase 1: Add new column
ALTER TABLE orders ADD COLUMN order_ref uuid;

-- Phase 2: Deploy dual-write code (writes to both old and new columns)
-- Phase 3: Backfill in batches
-- Phase 4: Add NOT NULL constraint (if needed)
ALTER TABLE orders ALTER COLUMN order_ref SET NOT NULL;

-- Phase 5: Deploy code that reads from new column only
-- Phase 6: Drop old column (after validation period)
ALTER TABLE orders DROP COLUMN old_order_id;

Large table restructuring (splitting a monolithic orders table into orders and order_items) follows the same expand-contract pattern but at a table level. Create the new table, set up triggers or application-level dual-write to keep both tables in sync, backfill historical data, switch reads, then remove the old table and triggers.

Triggers for dual-write synchronization work reliably but add latency to every write operation. For high-throughput tables, consider Change Data Capture (Debezium, pg_logical) to replicate changes asynchronously instead. The tradeoff is eventual consistency — the new table lags behind the old one by a few seconds. For most use cases this is acceptable during the migration period.