Migrate my 180-million-row production table with zero downtime
An engineer used AI to plan and write an expand/contract migration moving a 180M-row events table from int4 to bigint over 9 days: backfill peaked at 4.1 seconds of replica lag and the final swap locked for 1.4 seconds, with zero downtime. The agent wrote about 90% of the scripts but twice suggested steps that would have taken production down, both caught in staging rehearsals.
This only partly worked. Read what happened before you try it.
How it works
- Map every foreign key, index, view, trigger and app query touching events.id before proposing any fix
- Add a bigint column kept in sync by a trigger
- Backfill in 20,000-row batches that pause when replica lag exceeds 5 seconds
- Build the unique index concurrently
- Prove NOT NULL via a validated check constraint
- Rehearse the swap six times on staging
- Swap in 1.4 seconds and repeat for three foreign-key tables
Why it’s worth knowing
The agent migrated 180 million rows live — and its 'simpler' shortcut would have locked the whole product for minutes.
Where it’s from
Summarized from a public post on dev.to. See the original ↗