---
title: "Commit each backfill batch so vacuum keeps up | Proofline"
description: "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."
url: https://proofline.sh/learn/postgres-backfill-long-transaction-xmin/
---

[Learning center](/learn/)

# 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.

Capacity overruns The Proofline team November 22, 2026 2 min read

## 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';
```

| Session | State | Transaction ID | Age |
| --- | --- | --- | --- |
| backfill | idle in transaction | 88894 | 21 |

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](https://www.postgresql.org/docs/16/routine-vacuuming.html) 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](/learn/postgres-backfill-batch-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](/learn/data-migration-across-service-versions/).

## 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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Backfill in batches so the application never waits on row locks](/learn/postgres-backfill-batch-row-locks/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [Moving data while two application releases share one database](/learn/data-migration-across-service-versions/)
    
    Breaking migrations The Proofline team November 22, 2026 7 min read
