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