Learning center

Commit each backfill batch so vacuum keeps up

A backfill that keeps one transaction open stops vacuum from removing dead rows anywhere in the database, not only in the table it is filling. In our lab, a backfill that had changed 100 rows and then sat idle in its transaction left 20,000 dead rows in a busy sessions table that vacuum could not remove. When the backfill committed, the next vacuum removed all of them.

The lab#

Two tables share one database. orders is being backfilled. sessions has 1,000 rows that the running release updates constantly, as a session or heartbeat table does. We turned autovacuum off on sessions so that each vacuum ran when we chose. We ran this on PostgreSQL 16.15 with psycopg 3.2.

job = psycopg.connect(dsn, application_name="backfill")   # not autocommit
job.execute("UPDATE orders SET state = upper(status) WHERE id <= 100")
# the job does not commit; the application keeps working
for _ in range(20):
    app.execute("UPDATE sessions SET seen_at = now()")
app.execute("VACUUM (VERBOSE) sessions")
tuples: 0 removed, 21000 remain, 20000 are dead but not yet removable

Every one of the 20,000 dead row versions was newer than the backfill's open transaction. The backfill could not see them, but PostgreSQL could not be sure no transaction would need them, so it kept them. pg_stat_activity named the session holding them:

SELECT application_name, state, backend_xid, age(backend_xid) AS xid_age, now() - xact_start AS open_for
FROM pg_stat_activity WHERE application_name = 'backfill';
SessionStateTransaction IDAge
backfillidle in transaction8889421

After job.commit(), the same vacuum reported 20000 removed, 1000 remain, 0 are dead but not yet removable.

Why the whole database is affected#

Vacuum may remove a dead row only when no running transaction could still see it. The oldest transaction ID still in use by any session sets that boundary for every table, as the routine vacuuming chapter explains. Our backfill held an assigned transaction ID because it had written rows. A transaction at REPEATABLE READ holds its snapshot's horizon even if it only reads. A single long UPDATE in autocommit mode holds it for the duration of the statement.

A backfill over a large table that runs for hours in one transaction therefore lets busy tables bloat for hours. Their indexes grow, their scans read more pages, and the space stays allocated after vacuum catches up.

What to do#

Commit every batch. In psycopg, open the job's connection with autocommit=True or call commit() after each batch. Set idle_in_transaction_session_timeout on the job's role so a job that stops mid-batch has its transaction ended for it:

ALTER ROLE backfill_job SET idle_in_transaction_session_timeout = '60s';

Batching also keeps row locks short, as backfill in batches so the application never waits on row locks shows.

The check during the run#

While the backfill runs, query pg_stat_activity for its session's xact_start. Pass when the transaction age stays near one batch's duration. If it grows with the run, the job is not committing. More backfill labs are in moving data while two application releases share one database.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading