How to Backfill a Column in Postgres in Batches (Without Locking the Table)
A single UPDATE over a large table holds locks, bloats the table and can run for hours with nothing to show if it fails. Backfill in small verified batches that commit as they go, so the job is safe to stop and resume.
Why one big UPDATE is risky
UPDATE transactions SET amount_cents = round(amount_usd * 100);
On a large table this runs as one transaction: it locks every row it touches, writes a new version of every row at once, and if it fails at 90% everything rolls back. Replicas fall behind and other writes queue up.
Batch by "still NULL", not by offset
while True:
cur.execute("""SELECT id, amount_usd FROM transactions
WHERE amount_cents IS NULL ORDER BY id LIMIT 500""")
rows = cur.fetchall()
if not rows:
break
for id_, usd in rows:
cur.execute("UPDATE transactions SET amount_cents = %s WHERE id = %s",
(int(round(usd * 100)), id_))
# verify before committing
cur.execute("""SELECT count(*) FROM transactions WHERE id = ANY(%s)
AND abs(amount_cents::numeric / 100 - amount_usd) > 0.005""",
([r[0] for r in rows],))
if cur.fetchone()[0]:
conn.rollback()
raise RuntimeError("batch failed verification")
conn.commit()
WHERE amount_cents IS NULLmakes the job resumable: a rerun picks up exactly where the last one stopped. A fixedOFFSETwould skip or repeat rows.- Small batches keep each transaction short, so locks are brief.
- Verify, then commit catches a bad conversion before it spreads.
Where this fits in a migration
A column change usually goes: add the new column, dual-write from the app, backfill old rows, verify, switch reads, then drop the old column. The backfill is the long, risky middle step.
What you do in the project
You write the backfill for 5,000 rows in 500-row batches, verify each batch, then NULL half the rows and rerun to prove it resumes correctly.
Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.
Get started - freeWhat you'll practice
- Backfilling a column in small batches keyed on rows still to process
- Verifying each batch before committing and rolling back on mismatch
- Making a long-running data job resumable
FAQ
How do I update millions of rows in Postgres without locking the table?
Update in small batches (for example a few thousand rows by primary key), commit after each batch, and optionally pause between batches. Each transaction stays short, so locks are brief and replicas keep up.
Why not use OFFSET for batching?
Rows change as the backfill runs, so OFFSET can skip or repeat rows, and it gets slower on later pages. Selecting rows that still need the update (WHERE new_col IS NULL) is both correct and resumable.
What is a backfill?
A backfill fills in historical data that a new column, table or pipeline did not have, such as computing a value for every existing row after adding a column.
Keep learning
Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.
Get started - free