Batched Postgres backfills that survive a mid-deploy restart
The backfill had been written as one statement inside one transaction. It worked on a laptop against a seeded database, and it worked in staging, where the table was small. In production it was orders of magnitude larger. The job either ran for hours or was killed by a deploy halfway through, and nobody could say how much of it had landed.
Measure before rewriting
The interesting number was not the query plan, it was the transaction age. One long transaction held row locks the application needed, kept dead tuples un-vacuumable, and grew the WAL until the replica fell behind. The SQL was fine. The shape of the job was wrong.
Three changes
Batch the work into small committed chunks. Page with a keyset instead of OFFSET, which degrades linearly as the offset grows. Persist a cursor outside the data transaction so a restart resumes instead of repeating. This is the shape it settled into as a NestJS task.
Results
End-to-end runtime fell sharply, replication lag stayed under a second, and the job became something you could stop mid-flight and restart without thinking about it. SKIP LOCKED also meant two workers could share the queue on the days we wanted it finished sooner.
The transferable part
Migration performance is usually a batching problem, not a query problem. Before optimising SQL, ask how long the transaction stays open and what it holds while it does.