Skip to content

.tip

· one of the small things

Backfill in batches, not one UPDATE

One UPDATE over ten million rows holds a transaction open for an hour, pins xmin so autovacuum cannot clean behind it, and rolls everything back if it fails near the end. Loop over keyed batches instead.

UPDATE orders SET currency = 'TRY'
WHERE id IN (
  SELECT id FROM orders WHERE currency IS NULL ORDER BY id LIMIT 5000
);

Each batch commits on its own, so a failure costs one batch and the next run resumes where it stopped. Repeat until it reports UPDATE 0, and keep a partial index on currency IS NULL so the scan does not slow down every pass.

postgresqlmigrations