The client's database administrator delivered the verdict on a Thursday afternoon: a three-hour maintenance window, scheduled for 2 AM on Sunday. The product team's reaction was immediate — they had paying customers in six time zones, a SLA that didn't allow three hours of downtime, and a board meeting Monday morning where the CEO was planning to demo the new feature. A maintenance window wasn't acceptable.
The schema change itself was straightforward: rename a heavily-used column on a 200GB table and split a JSON blob column into three typed columns. Perfectly normal work. The complication was doing it while the application kept serving traffic. Here's the pattern we used — it's the same one we use across every migration that can't take a downtime.
Why the obvious approach breaks production
The instinct is to run ALTER TABLE in a migration script and deploy the new application code simultaneously. That works fine on a small table. On a 200GB table with hundreds of concurrent queries, ALTER TABLE acquires an ACCESS EXCLUSIVE lock that blocks every read and write until the operation completes. On Postgres, renaming a column on a large table can hold that lock for minutes. During peak traffic, that's an outage.
Even if you schedule it for off-peak hours, there's a second problem: the application has to work with both old and new column names during the deployment window. If you rename the column and then deploy the new code, there's a gap where the old code is running against the new schema. Every query that touches the renamed column fails until all pods are restarted.
The right pattern avoids both problems entirely.
The expand-contract pattern
The core idea: never remove or rename — only add and backfill. Keep the old structure alive until every piece of code that depends on it is gone. The migration happens over multiple deploys, not one.
Phase 1: Expand — add without removing
Add the new columns alongside the old ones. No locks, no downtime — adding a nullable column to Postgres is nearly instantaneous even on large tables.
For the column rename (user_id → account_id) we added the new column:
ALTER TABLE orders ADD COLUMN account_id bigint;For the JSON split (a metadata JSON blob → three typed columns region, currency, locale):
ALTER TABLE orders ADD COLUMN region varchar(10);
ALTER TABLE orders ADD COLUMN currency char(3);
ALTER TABLE orders ADD COLUMN locale varchar(10);At this point, the old columns still exist and are still being written to. The new columns exist but are empty. Nothing is broken.
Phase 2: Double-write — new code, both columns
Deploy the application code change that writes to both the old and new columns on every write. Reads still come from the old columns. This deploy is safe to roll back at any point — if something goes wrong, revert to the previous code and only the old columns have data.
The write path becomes:
INSERT INTO orders (user_id, account_id, metadata, region, currency, locale)
VALUES ($1, $1, $2, $3, $4, $5)New rows have both sets of columns populated. Existing rows still only have the old columns.
Phase 3: Backfill — populate historical rows
With double-write running, backfill the new columns for all existing rows. The critical constraint: do this in batches, not as a single UPDATE. A single UPDATE orders SET account_id = user_id on a 200GB table will hold row-level locks for potentially hours and cause visible performance degradation.
We wrote a backfill script that processes rows in chunks of 10,000, with a 50ms sleep between batches:
DO $$
DECLARE
last_id bigint := 0;
batch_size int := 10000;
BEGIN
LOOP
UPDATE orders
SET
account_id = user_id,
region = metadata->>'region',
currency = metadata->>'currency',
locale = metadata->>'locale'
WHERE id > last_id
AND id <= last_id + batch_size
AND account_id IS NULL;
EXIT WHEN NOT FOUND;
last_id := last_id + batch_size;
PERFORM pg_sleep(0.05);
END LOOP;
END $$;We ran this as a background Kubernetes Job during business hours. The database stayed fully operational throughout. The full backfill on 200GB took 4 hours — all of it invisible to users.
Phase 4: Switch reads — new columns only
Once the backfill completes and we've verified 100% of rows have the new columns populated (a count query against WHERE account_id IS NULL), deploy the code change that reads from the new columns. Stop writing to the old columns in the same deploy.
At this point: new columns are populated for all rows, old columns still exist but are no longer being written to or read from. The application is fully on the new schema. This is the first deploy that can't be instantly rolled back — rolling back to the previous version would mean reads from new columns, writes to old ones. Test thoroughly before this deploy.
Phase 5: Contract — drop the old columns
Wait. The instinct after a successful Phase 4 deploy is to immediately clean up. Don't. Run the system on the new columns for a full week before dropping the old ones. If a buried code path, an analytics job, or a third-party integration was reading from the old columns, you want to find that out from a log warning, not a production error.
After a week of clean operation, dropping the old columns is a one-line migration:
ALTER TABLE orders
DROP COLUMN user_id,
DROP COLUMN metadata;Postgres reclaims the space on next AUTOVACUUM or an explicit VACUUM FULL if you need the space immediately.
Handling NOT NULL constraints
The tricky edge case: what if the new column can't be nullable? Adding a NOT NULL column without a default requires a full table rewrite in older Postgres versions. In Postgres 11+, adding NOT NULL DEFAULT 'value' is instant for constant defaults — Postgres stores the default in the catalog rather than rewriting rows. But if the default must be computed per-row, you still need the phased approach: add as nullable, backfill, then add the constraint.
Adding a NOT NULL constraint to an existing column also requires a full table scan to validate. On a large table, use NOT VALID first:
ALTER TABLE orders ADD CONSTRAINT orders_account_id_not_null
CHECK (account_id IS NOT NULL) NOT VALID;
-- Validate in the background (doesn't lock):
ALTER TABLE orders VALIDATE CONSTRAINT orders_account_id_not_null;VALIDATE CONSTRAINT only takes a SHARE UPDATE EXCLUSIVE lock, which doesn't block normal reads and writes. It runs in the background, can take hours on large tables, and can be cancelled and resumed.
The outcome
The migration ran over four weeks: one week of double-write, four hours of background backfill, one week on new columns to monitor, then cleanup.
- Production downtime: 0 seconds
- User-visible errors during migration: 0
- Rollbacks needed: 0
- Database performance impact during backfill: <3% CPU increase, invisible in application metrics
"We'd been putting off this schema change for eight months because of the downtime risk. When your team explained the expand-contract pattern, I couldn't believe we hadn't been doing it this way the whole time."
When this pattern is overkill
Not every migration needs this level of care. The expand-contract pattern is worth the overhead when:
- The table has more than a few million rows
- The column being changed is in a hot path (read or written on every request)
- You have a SLA or contractual uptime commitment
- The change can't be done with a lock-free Postgres DDL operation
For small tables, low-traffic columns, or additive changes that Postgres can do instantly, a standard migration script in your deploy pipeline is fine. The goal isn't ceremony — it's knowing when the cheap path is safe and when it isn't.
Tools that help
If you're doing this regularly, pg_repack and pg_osc (Postgres Online Schema Change) automate the double-write and backfill mechanics. They're worth evaluating for teams running frequent migrations on large tables. We've used both in production; pg_repack is more mature, pg_osc has better observability hooks.
Whatever tooling you use, the mental model is the same: every schema change that removes or renames something needs to be decomposed into a forward-only sequence of additions, until the old thing has no more readers. Drop last, never first.