The migration that works on staging and melts down production usually dies in the lock queue. Someone runs an ALTER TABLE that took two seconds locally, it requests an exclusive lock on a hot table, and the query finds itself behind a long-running transaction. Every new query on that table — every read, every write — queues behind the migration. In thirty seconds the connection pool is exhausted, health checks start failing, and you are rolling back a “simple” column add. Zero-downtime schema changes are not about writing better SQL; they are about sequencing a change so that no single statement ever blocks production traffic for more than a moment.
The expand-contract pattern is the standard answer. Instead of one destructive change, you make a sequence of small, individually boring changes: expand the schema so it supports both the old and new shapes, migrate data at your own pace, switch the application over, and only then remove the old structure. This post walks the pattern on PostgreSQL with real statements, a batched backfill job in Go, and the operational guardrails that keep each step from becoming its own outage.
Why ALTER TABLE Hurts
Most table rewrites you fear take an ACCESS EXCLUSIVE lock, which blocks reads and writes alike. The subtler danger is the lock queue: PostgreSQL grants locks fairly, so once the migration is waiting, every subsequent query on that table queues behind it — even trivially fast selects. A two-second migration parked behind a five-minute reporting query becomes five minutes of full outage on that table.
Modern PostgreSQL quietly fixed several of the worst offenders. Since version 11, adding a column with a constant default is a metadata-only change — no table rewrite, near-instant even on billions of rows. Since version 12, SET NOT NULL can skip its full-table scan when a validated CHECK constraint already proves the column has no nulls. The operations that still require real blocking work — changing a column type, rebuilding a table, most ADD CONSTRAINT forms — are exactly the ones expand-contract decomposes into safe steps.
The Four Phases
Every expand-contract migration, regardless of the specific change, decomposes the same way:
- Expand. Add the new structure alongside the old one: a new nullable column, a new table, a new index. The old application version must keep working untouched.
- Backfill. Copy existing data into the new structure in small batches, at a pace your replication lag and disk I/O can absorb. This can run for hours or days; nothing depends on it finishing.
- Switch. Deploy an application version that reads and writes the new structure, usually writing both shapes during a transition window so a rollback stays possible.
- Contract. Once no running code touches the old structure, remove it: drop the old column, delete the dual-write path. This is the only destructive step, and by the time it runs it is provably safe.
The pattern’s real value is that the risky work — the data migration — moves out of the critical path entirely. Schema statements become tiny; the long-running part is a batch job you can pause, throttle, or kill.
A Concrete Example: Reshaping a Money Column
Suppose products stores prices as a float price and you need exact arithmetic: an integer price_cents. The single-statement version rewrites the whole table under an exclusive lock. The phased version:
Phase 1 — expand. Add the new column nullable, plus a check constraint marked NOT VALID. The constraint addition takes only a brief lock because NOT VALID skips the scan of existing rows; new inserts are immediately checked.
ALTER TABLE products
ADD COLUMN price_cents bigint;
ALTER TABLE products
ADD CONSTRAINT products_price_cents_required
CHECK (price_cents IS NOT NULL)
NOT VALID;Phase 2 — backfill. A background job (next section) copies the old values in batches. When it finishes, validate the constraint. VALIDATE CONSTRAINT scans the table but takes only a SHARE UPDATE EXCLUSIVE lock — reads and writes continue throughout.
ALTER TABLE products
VALIDATE CONSTRAINT products_price_cents_required;Phase 3 — switch, cheaply. Here is the payoff of the validated check: because it proves the column is fully populated, SET NOT NULL skips its own table scan. The “dangerous” tightening becomes a metadata operation:
ALTER TABLE products
ALTER COLUMN price_cents SET NOT NULL;
ALTER TABLE products
DROP CONSTRAINT products_price_cents_required;If the new column needs an index, create it concurrently — it does not block writes, though it runs slower, waits out concurrent transactions, cannot run inside a transaction block, and leaves an INVALID index behind if it fails (drop and retry):
CREATE INDEX CONCURRENTLY idx_products_price_cents
ON products (price_cents);Phase 4 — contract. After every deployed instance writes only price_cents and no rollback window remains, drop the old column. This is instant metadata: PostgreSQL marks the column dropped without rewriting any rows. Verify with your team that no hotfix branch or batch job still references it — dropping the old column is the point of no return.
ALTER TABLE products
DROP COLUMN price;The Backfill Job in Go
The backfill is ordinary code, which is exactly why it belongs in application code review and not in a migration script. Key properties: bounded batches, ordered by primary key so progress is resumable, small transactions that never hold a lock for long, and a deliberate pause between batches. Using pgx:
package main
import (
"context"
"log"
"time"
"github.com/jackc/pgx/v5"
)
type item struct {
id int64
cents int64
}
func backfill(ctx context.Context, conn *pgx.Conn) error {
const batchSize = 5000
var lastID int64
for {
rows, err := conn.Query(ctx, `
SELECT id, round(price::numeric * 100)::bigint
FROM products
WHERE id > $1 AND price_cents IS NULL
ORDER BY id
LIMIT $2`, lastID, batchSize)
if err != nil {
return err
}
var batch []item
for rows.Next() {
var it item
if err := rows.Scan(&it.id, &it.cents); err != nil {
rows.Close()
return err
}
batch = append(batch, it)
}
rows.Close()
if err := rows.Err(); err != nil {
return err
}
if len(batch) == 0 {
return nil
}
tx, err := conn.Begin(ctx)
if err != nil {
return err
}
for _, it := range batch {
if _, err := tx.Exec(ctx,
`UPDATE products SET price_cents = $2 WHERE id = $1`,
it.id, it.cents); err != nil {
tx.Rollback(ctx)
return err
}
lastID = it.id
}
if err := tx.Commit(ctx); err != nil {
return err
}
log.Printf("backfilled through id %d (%d rows)", lastID, len(batch))
time.Sleep(50 * time.Millisecond) // throttle: watch replication lag
}
}Two details matter more than they look. First, the WHERE price_cents IS NULL predicate makes the job idempotent and safe to restart; combined with id > lastID ordering, a crashed run resumes where it left off. Second, keep each transaction small — a backfill that updates a million rows in one transaction recreates the lock problem the pattern exists to avoid, and on busy tables it also bloats the table faster than autovacuum can keep up. Size batches by replication lag, not by wall-clock speed.
The dual-write during the transition window belongs in the same code review: application writes set both price and price_cents until contract. That can live in the repository layer, or in a temporary trigger for codebases where touching every write path is impractical. Triggers are guaranteed complete but hide application-visible latency; application-level dual writes are explicit but must be audited for missed paths. Teams that run this pattern frequently automate it — pgroll and Reshape both manage PostgreSQL schema changes as sequenced expand-contract phases, including keeping old and new schema versions live simultaneously for blue-green deploys.
Guardrails for Every Step
Even with the pattern applied correctly, wrap every DDL statement in defensive settings. Set a lock_timeout so a migration that cannot get its lock quickly fails fast instead of accumulating a queue behind it — a failed migration is an incident-free retry, whereas a queued one is an outage in progress. Pair it with a retry loop:
SET lock_timeout = '5s';
ALTER TABLE products
ADD COLUMN price_cents bigint;Run migrations with this pattern from a deployment step that retries on lock timeout, and monitor pg_stat_activity plus pg_locks for waiting queries during any DDL window — a pile-up of connections waiting on one statement is your earliest warning. Two process rules complete the setup. First, expand and contract must ride separate deployments: expand ships first and must be backward compatible with the currently running version, and contract ships only after every old-version instance is gone. Second, keep the phases as separately reviewable, separately revertible units — the moment you combine “add column, backfill, drop column” into one migration script, you have rebuilt the original problem with extra steps.
Wrapping Up
Expand-contract trades a single dangerous statement for a sequence of trivially safe ones: expand with nullable additions, backfill in bounded batches, switch reads and writes, contract when nothing references the old shape anymore. PostgreSQL’s recent releases have absorbed several classic blockers — constant-default column adds and the validated-check shortcut for SET NOT NULL — but column type changes and constraint rebuilds still follow this choreography. The next time a migration ticket says “add NOT NULL to a hot table,” resist the one-liner. Write the four phases, put the backfill in code with tests, and ship each phase behind its own deploy. Downtime is not a property of the schema change; it is a property of the sequence you chose.