Backfilling a billion rows without a maintenance window
A customer had a table with 1.1 billion rows and a broken join key. The old key was a string, the new one an integer, and six teams read that table every day. The obvious plan — stop writes, migrate, restart — was not available: the pipeline could be paused, the readers could not.
Three constraints that shaped the plan
First, every reader had to keep getting correct rows during the whole operation. Second, the backfill had to be resumable, because it ran for eleven hours and we wanted to be able to stop it at 2am if anything looked wrong. Third, the new column had to be filled without doubling storage for weeks.
What we did
We added the new column nullable, backfilled in primary-key ranges of 500k rows, and switched readers with a view that coalesced the two columns. Writes went to both columns through a trigger for the transition period, then the trigger was removed once the backfill caught up.
-- chunked backfill, one range per run
UPDATE events
SET event_key_int = event_key::bigint
WHERE event_key_int IS NULL
AND id BETWEEN :lo AND :hi;
-- readers see one column during the transition
CREATE VIEW events_v1 AS
SELECT id,
COALESCE(event_key_int, event_key::bigint) AS event_key,
...
FROM events;
The one metric
Replication lag on the read replica. Our chunks were sized so that a single chunk finished in under 90 seconds and produced less than 40 seconds of lag. Slower chunks meant smaller ranges; faster ones let us grow them. Nothing else on the dashboards mattered during that night.
Total downtime for readers: zero. Total temporary storage: about 6% of the table, reclaimed after the view was dropped. The runbook and the SQL are in the customer's repo now — which, honestly, is the part I am proudest of.