# Database Migrations Without Downtime: Patterns From Three Real Cutovers

URL: https://www.devopsness.com/blog/database-migrations-without-downtime-patterns-from-three-real-cutovers-2026-04-05
Author: Kiril Urbonas
Published: 2026-04-05
Updated: 2026-04-05
Categories: Infrastructure
Tags: Terraform, CI/CD, Monitoring

Summary: How we shipped three schema migrations with zero customer impact. Expand-then-contract, dual-writes, and the rollback plan we never had to use — but tested anyway.

---
# Database Migrations Without Downtime: Patterns From Three Real Cutovers

We've shipped three non-trivial schema migrations in the last quarter against a Postgres database serving ~12k req/s at peak. None of them caused customer-visible downtime. Here's the playbook we settled on, with the gotchas we hit along the way.

## The "Expand → Migrate → Contract" Pattern

Every migration follows the same three-phase shape:

1. **Expand**: add the new schema *additively*. New columns nullable, new tables empty, new indexes built `CONCURRENTLY`. App still reads/writes the old shape.
2. **Migrate**: dual-write to both old and new shapes; backfill old rows in batches; switch reads to the new shape behind a feature flag.
3. **Contract**: once new path is stable for ≥ 1 week, drop the old columns/tables.

The trap most teams fall into is collapsing 1 → 3 into a single deploy because "it's a small change." It's never that small once it lands on prod traffic.

## Cutover #1: Splitting a 180GB Wide Table

Original schema had `orders` carrying 47 columns including a fat JSONB blob of line items. We split out `order_items` as a proper relation.

### What we did

```sql
-- Phase 1: Expand
CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  sku TEXT NOT NULL,
  qty INT NOT NULL,
  price_cents INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX CONCURRENTLY order_items_order_id_idx ON order_items(order_id);
```

App code began dual-writing on every insert. Backfill was done in 50k-row batches with a 200ms sleep between batches:

```python
while True:
    rows = db.execute("""
      SELECT id, items FROM orders
      WHERE migrated_at IS NULL
      ORDER BY id LIMIT 50000
    """).fetchall()
    if not rows: break
    insert_items(rows)
    db.execute("UPDATE orders SET migrated_at = now() WHERE id = ANY(%s)", [[r.id for r in rows]])
    time.sleep(0.2)  # don't starve OLTP
```

Backfill took 11 hours. **CPU on the primary stayed below 60%** the whole time because of the throttle.

### What bit us

Index build time on `order_items.order_id` was 2× longer than predicted because we underestimated bloat. Lesson: **always build indexes `CONCURRENTLY`**, and run `pg_stat_progress_create_index` to monitor progress instead of guessing.

## Cutover #2: Renaming a Column That Half the Codebase Read

We had a `user.is_active` boolean. Product wanted three states: `active`, `paused`, `disabled`. Renaming an enum-like column with 200+ call sites is a minefield.

### Pattern: View-Backed Compatibility Shim

```sql
-- Phase 1: add new column, keep old
ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active'
  CHECK (status IN ('active','paused','disabled'));

-- Backfill from old column
UPDATE users SET status = CASE WHEN is_active THEN 'active' ELSE 'disabled' END;

-- Phase 2: triggers keep them in sync until all writers migrate
CREATE TRIGGER sync_user_status
  BEFORE UPDATE OF is_active, status ON users
  FOR EACH ROW EXECUTE FUNCTION sync_user_status_fn();
```

The trigger let old code keep writing `is_active` while new code wrote `status`. We removed the trigger and dropped `is_active` only after grep showed zero references in any deployed branch.

## Cutover #3: Foreign-Key Type Change (`int` → `bigint`)

A counter table hit 1.9 billion rows; `int4` was about to overflow. Changing the type with `ALTER COLUMN` rewrites the whole table — totally unacceptable here.

### Online type change

1. Add new `id_big BIGINT` column.
2. Backfill in batches.
3. Add a unique index `CONCURRENTLY` on `id_big`.
4. Atomically swap primary key in a single transaction (this is fast — it's a metadata change once the index exists).
5. Drop the old column.

The cutover transaction took **47ms**. Total project: 6 weeks of incremental backfill.

## Best Practices We Now Enforce

- **Every migration PR includes a rollback plan**, even if "rollback = forward-fix." If the rollback is non-obvious, the PR doesn't merge.
- **Backfills run as a separate job**, never inline with the schema migration. Schema migration must finish in < 10s.
- **Read replicas are warmed before cutover** by sending shadow traffic for ≥ 1 hour.
- **Feature flags gate the read path**, never the write path. Reads flip back instantly; writes don't.
- **One-week soak** between phases. We've never regretted waiting; we've regretted rushing.

## What We Test Before Every Migration

| Check | Tool | Pass criteria |
|---|---|---|
| Migration runs in < 10s on prod-sized clone | `pg_dump` + restore in CI | hard fail above 10s |
| Backfill ETA | Run on 1% sample, extrapolate | within 24h or split further |
| Replication lag during backfill | `pg_stat_replication` | stays < 30s |
| Rollback in a sandbox | Restore from snapshot, replay forward, then back | clean state |

## When You Can Skip This

You can skip the dance only if **all** of the following are true:
- Table has < 100k rows
- Migration is purely additive (new nullable column, new table, new index built CONCURRENTLY)
- Application can tolerate a 30-second window of "old code reads new schema"

If any one of those isn't true, do the full dance. The hour of planning saves the day of incident response.