---
title: "Vacuum between backfill batches to keep the table from doubling | Proofline"
description: "Every row a backfill updates leaves a dead version behind, and an UPDATE cannot reuse that space until vacuum has marked it free."
url: https://proofline.sh/learn/postgres-backfill-dead-tuples-vacuum/
---

[Learning center](/learn/)

# Vacuum between backfill batches to keep the table from doubling

Every row a backfill updates leaves a dead version behind, and an UPDATE cannot reuse that space until vacuum has marked it free. A backfill that outruns vacuum grows the table to about twice its size, and vacuum afterwards does not give the space back. In our lab, a million-row table grew from 77 MB to 161 MB without vacuum between batches, and to 92 MB with a VACUUM every ten batches.

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

## The lab

`orders` has 1,000,000 rows with an empty `state`. The backfill updates 10,000 rows per batch. We turned autovacuum off for the table so that the only vacuum was the one the job ran, then compared three jobs on PostgreSQL 16.15:

```
for lo in range(0, 1_000_000, 10_000):
    db.execute("UPDATE orders SET state = upper(status) WHERE id > %s AND id <= %s", (lo, lo + 10_000))
    batches += 1
    if batches % VACUUM_EVERY == 0:
        db.execute("VACUUM orders")
```

| Job | Backfill | Table size after | After a final `VACUUM` |
| --- | --- | --- | --- |
| No vacuum during the backfill | 4.8 s | 161 MB | 161 MB |
| `VACUUM` every 10 batches | 5.3 s | 92 MB | 92 MB |
| `VACUUM` every batch | 5.1 s | 87 MB | 87 MB |

The table started at 77 MB. A full scan of the table took 37 ms after the job without vacuum and 19 ms after the job that vacuumed every ten batches, because it reads twice as many pages.

## Why the space stays

Without vacuum, every new row version needs fresh space, so the first pass writes a second copy of the table. Vacuum after the fact marks the dead versions as free space inside the file, but it only shortens the file when the free pages are at its very end, as the [routine vacuuming chapter](https://www.postgresql.org/docs/16/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY) explains. Here the dead versions were spread through the whole file. Getting the space back takes `VACUUM FULL` or a tool that rewrites the table, and `VACUUM FULL` holds an `ACCESS EXCLUSIVE` lock for the whole rewrite.

With a vacuum every ten batches, each batch after the first ten found the space that the vacuum had just freed and wrote its new versions there.

## In production

Autovacuum would normally do this, but its default thresholds trigger after 20% of a table has changed, and a backfill changes that much quickly. Either run `VACUUM` from the job every few batches, or lower `autovacuum_vacuum_scale_factor` for the table during the backfill:

```
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);
-- reset after the backfill
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor);
```

Vacuum can only free a dead version that no open transaction can still see. A backfill that keeps one transaction open, or any other long transaction in the database, makes every one of these vacuums do nothing; [commit each backfill batch so vacuum keeps up](/learn/postgres-backfill-long-transaction-xmin/) shows that. A replica with `hot_standby_feedback` and a long query has the same effect, covered in [backfill replica lag comes from replay conflicts too](/learn/postgres-backfill-replica-lag/).

## The rehearsal

On a copy at production size, run the backfill with the vacuum cadence you plan, and read `pg_relation_size` before and after. Pass when the table grows by no more than the free space you can afford to keep. Index growth from the same backfill is in [create the new column's index after the backfill](/learn/postgres-backfill-hot-updates-index-order/).

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

-   ### [Commit each backfill batch so vacuum keeps up](/learn/postgres-backfill-long-transaction-xmin/)
    
    Capacity overruns The Proofline team November 22, 2026 2 min read
    
-   ### [Create the new column's index after the backfill](/learn/postgres-backfill-hot-updates-index-order/)
    
    Capacity overruns The Proofline team November 22, 2026 3 min read
