Migrating 40M rows to a new column type without a maintenance window
Contents
events.payload was json. It needed to be jsonb so we could index into it. ALTER TABLE ... ALTER COLUMN ... TYPE jsonb takes an ACCESS EXCLUSIVE lock and rewrites the whole table. On our hardware that was a shade over four hours of total unavailability. Not acceptable.
The shape that worked
- Add
payload_b jsonb, nullable, no default. Instant — no rewrite in modern Postgres. - Deploy application code that writes both columns and reads
payload_bwith a fallback topayload. This is the step people skip and then regret. - Backfill in batches of 5,000 by primary key, with a 200ms sleep between batches, driven by a script we could stop. Ran for eleven hours across two nights. Replication lag never exceeded 400ms.
- Verify:
count(*) WHERE payload_b IS NULLreaches zero, plus a checksum comparison on a 100k sample. - Add
NOT NULLviaNOT VALIDthenVALIDATE CONSTRAINT— a share update exclusive lock, not an access exclusive one. - Drop the old column, two weeks later, once we were sure we would not roll back.
The bit that bit us
Step 3 ran while step 2 was live, so rows written during the backfill were already correct — but our batch script had a WHERE payload_b IS NULL predicate with no index to support it, so each batch did a seq scan that got slower as the backfill progressed. Last batches took ninety seconds each. A partial index on (id) WHERE payload_b IS NULL fixed it, and it self-destructs as the backfill completes since the predicate stops matching rows.
Total user-visible downtime: zero. Total elapsed: sixteen days. That ratio is the whole trick.
Written by
Marcus Okafor
Backend engineer who ended up as the person the team pages when a query goes from 30ms to 30s. Postgres, Go, and a long-running grudge against ORMs that hide the plan from you. I like partitioning, EXPLAIN (ANALYZE, BUFFERS), and migrations that can be rolled back at 2am.
5 Comments
Sign in to join the discussion
A partial index that self-destructs as the backfill completes is genuinely delightful engineering.
The same trick works for embedding backfills — index on the not-yet-processed predicate and it shrinks to nothing as you go. I had been eating a full scan per batch and calling it the cost of doing business.
Sixteen days elapsed, zero downtime, and the ratio is the trick. I am going to use that framing to defend a token migration everyone keeps asking me to do in one release.
Do. Nobody has ever complained to me that a migration took two weeks. They complain about the four hours.
Step 2 — write both, read new with a fallback — is the step people skip, and it is also the only step that makes the whole thing reversible. Skip it and a rollback silently loses every write since the deploy.