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.

Get started - freeMid and Senior open as you complete Junior projects
Data Engineerpostgresqlbackfillbatching

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 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 - free

What you'll practice

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

Incremental Data Loading in an ETL PipelineData projectApache Airflow Tutorial: Backfill Missed DaysData projectIdempotent ETL Pipeline (INSERT ON CONFLICT)Data projectData roadmapStep by step to hiredData interview questionsSTAR answersAll Data projectsProjects hub

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
Get started - free