Zero-Downtime PostgreSQL Migrations: Expand and Contract

If your migration strategy is “take the site down, run ALTER TABLE, pray, bring it back up,” you’re doing it wrong. Every schema change can be decomposed into steps that keep old and new code running simultaneously. It takes more discipline but zero heroics.

The problem with “just run the migration”

One system I worked on was a high-volume data API. Its clients didn’t pause because the engineering team needed a maintenance window, and a feed that goes dark is a feed that loses subscribers. When I took over database operations there, the existing pattern was to schedule migrations at 2 AM on Sundays, take the API offline, run the DDL, and hope the application came back cleanly. It worked until it didn’t.

The breaking point was a column type change on a table with 40 million rows of historical data. The migration ran for 47 minutes. During that time the table was locked, the API returned errors, and a downstream consumer silently switched to a stale cache it never recovered from. We spent the next two days cleaning up data consistency issues.

After that I decided every migration would be zero-downtime or it wouldn’t ship.

The expand-and-contract pattern

The core idea is simple. Instead of making a breaking change in one step, you split it into phases where the schema is always compatible with whatever application code is currently running.

Expand: add the new structure alongside the old one. Both coexist.

Migrate: deploy code that writes to both old and new, reads from new. Backfill historical data.

Contract: once every running instance uses the new structure, remove the old one.

This pattern handles nearly every schema change. The details vary, but the rhythm stays the same.

Adding a column safely

The simplest case. A nullable column with no default doesn’t rewrite the table in PostgreSQL. It’s metadata-only and takes a lock for milliseconds.

ALTER TABLE orders ADD COLUMN processed_date DATE;

Fast. Safe. Old code ignores the column. New code starts writing to it.

If you need a NOT NULL constraint, don’t add it in the same statement. PostgreSQL will scan the entire table to verify the constraint, holding an ACCESS EXCLUSIVE lock the whole time. Instead:

-- Step 1: add nullable column
ALTER TABLE orders ADD COLUMN processed_date DATE;

-- Step 2: backfill in batches (see below)

-- Step 3: add constraint without full table scan
ALTER TABLE orders ADD CONSTRAINT orders_processed_not_null
  CHECK (processed_date IS NOT NULL) NOT VALID;

-- Step 4: validate separately (only takes a SHARE UPDATE EXCLUSIVE lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_processed_not_null;

The NOT VALID trick is critical on large tables. It tells PostgreSQL to enforce the constraint on new writes immediately but skip the full table scan. The VALIDATE step runs later with a weaker lock that doesn’t block writes. On our 40-million-row tables, this was the difference between a 200-millisecond migration and a 20-minute outage.

Renaming a column

You don’t rename columns in production. Full stop.

What you actually do is expand-and-contract. Here is the concrete sequence, shown on an example orders table where price becomes unit_price:

-- Phase 1: Expand
ALTER TABLE orders ADD COLUMN unit_price NUMERIC(18,8);

Deploy code that writes to both price and unit_price. Reads come from unit_price with a fallback to price.

-- Phase 2: Backfill
UPDATE orders SET unit_price = price
WHERE unit_price IS NULL
AND id BETWEEN 1 AND 100000;

-- Repeat in batches...
UPDATE orders SET unit_price = price
WHERE unit_price IS NULL
AND id BETWEEN 100001 AND 200000;

Once backfill is complete and every application instance reads from unit_price:

-- Phase 3: Contract
ALTER TABLE orders DROP COLUMN price;

Three deployments minimum. That’s the cost. The benefit is zero downtime and a clean rollback at every step.

Changing a column type

Same pattern, different details. We had a case where an external identifier was stored as INTEGER but needed to become TEXT to support a new ID format.

-- Expand
ALTER TABLE products ADD COLUMN external_id_new TEXT;

-- Backfill
UPDATE products SET external_id_new = external_id::TEXT
WHERE external_id_new IS NULL
AND id BETWEEN 1 AND 50000;

Deploy dual-write code. Cut over reads. Drop the old column.

The temptation is to use ALTER COLUMN ... TYPE TEXT which rewrites the entire table under an exclusive lock. On a table with millions of rows that’s actively being queried, that isn’t an option.

Batched backfills that don’t kill the database

Large backfills are where most zero-downtime migrations go wrong. A single UPDATE ... WHERE condition on 40 million rows will generate enormous WAL, bloat the table, and hold locks that block concurrent operations.

The pattern I use:

#!/bin/bash
set -e
batch_size=5000
max_id=$(psql -Atc "SELECT COALESCE(MAX(id), 0) FROM orders")

for ((current_id = 0; current_id < max_id; current_id += batch_size)); do
  psql -c "UPDATE orders
           SET unit_price = price
           WHERE id > $current_id
             AND id <= $current_id + $batch_size
             AND unit_price IS NULL"
  sleep 0.1  # breathe
done

Each psql call is its own transaction, so every batch commits and releases its locks before the next one starts. A DO block can’t do that: it runs as a single transaction from start to finish.

The pause matters. Without it, you saturate I/O and replication lag spikes. On that system our replicas served read traffic for dashboards and analytics. A backfill that caused 30 seconds of replication lag would show stale data to every user watching a dashboard. We settled on batches of 5,000 rows with 100ms pauses. The backfill took longer but production stayed healthy.

Indexes without locking

CREATE INDEX on PostgreSQL takes a SHARE lock on the table, which blocks writes for the duration of the build. On a large table, that can be minutes.

CREATE INDEX CONCURRENTLY idx_orders_processed ON orders(processed_date);

CONCURRENTLY builds the index without blocking writes. It takes longer and does two table scans instead of one, but it’s the only option for production tables. There are two caveats worth knowing:

First, it can’t run inside a transaction. If your migration tool wraps everything in a transaction, you need to handle this case separately.

Second, if it fails partway through, it leaves an invalid index behind. Find it in pg_index and drop it before retrying.

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

Foreign keys without blocking

Adding a foreign key constraint with ADD CONSTRAINT ... FOREIGN KEY scans the whole table while holding a SHARE ROW EXCLUSIVE lock on both tables, which blocks writes for the duration. Same trick as NOT NULL:

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_product
  FOREIGN KEY (product_id) REFERENCES products(id)
  NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_product;

The first statement adds the constraint for new writes only, taking a brief lock. The second validates existing rows with a much weaker lock.

The deployment sequence

The order matters. Getting it wrong means you have a window where the application and schema disagree.

1. Run expand migration. New columns, new indexes (concurrently), new constraints (NOT VALID). Schema is now compatible with both old and new code.

2. Deploy new application code. Writes to both old and new columns. Reads from new with fallback to old.

3. Run backfill. In batches, during low traffic if possible. Monitor replication lag and query latency.

4. Verify completeness. SELECT COUNT(*) FROM orders WHERE unit_price IS NULL should be zero.

5. Deploy read-cutover code. Reads exclusively from new columns. Still writes to both.

6. Run contract migration. Drop old columns, drop temporary constraints. Schema is clean.

Each step is independently reversible. If the backfill causes problems, pause it. If the new code has bugs, roll back the deployment. The old schema is still there. This is the entire point.

What this costs

More deployments. More code that handles two schemas simultaneously. Migrations that used to be one PR become three or four. Application code carries temporary dual-write logic that gets cleaned up in the contract phase.

It’s more work. But on that system, the alternative was telling API consumers that we needed a maintenance window during their business hours. That conversation never goes well.

The discipline pays off in a different way too. When every migration follows expand-and-contract, deployments become boring . Nobody pages you at 2 AM for a schema change. Nobody holds their breath during a release. The process is mechanical and predictable.

The best migration is the one nobody notices.