Insert-Only Tables and the Autovacuum Insert Threshold #

An events table receives ten million rows a day and never updates or deletes any of them. It has no dead tuples, so on PostgreSQL 12 and earlier autovacuum never touched it — and its index-only scans read the heap for every row, its freeze debt grew until an anti-wraparound vacuum rewrote the whole table at once, and nobody understood why a table with no dead rows needed vacuuming.

The general relationship between vacuum and plans is in autovacuum and plan stability. This page is about the append-only case specifically, and about autovacuum_vacuum_insert_threshold, which exists because of it.

The Condition #

VACUUM does more than remove dead tuples. Two of its other jobs matter to plans on a table that has none:

Before PostgreSQL 13 the only autovacuum triggers were dead tuples and modifications since analyze, so an insert-only table triggered ANALYZE but not VACUUM. PostgreSQL 13 added autovacuum_vacuum_insert_threshold (default 1000) and autovacuum_vacuum_insert_scale_factor (default 0.2), which trigger a vacuum based on inserted rows.

The defaults still behave badly at scale, because the scale factor applies: a hundred-million-row table needs twenty million inserts before the insert trigger fires.

Why a table with nothing dead still needs vacuum Four stages. Rows are inserted and pages fill. Vacuum marks fully-visible pages in the visibility map. Index-only scans then answer from the index without heap fetches. Vacuum also freezes old transaction IDs, spreading the freeze work instead of deferring it to an anti-wraparound run. rows inserted pages fill vacuum runs marks all-visible index-only scans no heap fetches tuples frozen no wraparound cliff none of this depends on dead tuples existing

Annotated Evidence #

The scan before vacuum:

EXPLAIN (ANALYZE, BUFFERS)
SELECT device_id, occurred_at FROM events WHERE device_id = 4821 ORDER BY occurred_at DESC LIMIT 100;
Limit  (actual time=0.084..412.208 rows=100 loops=1)
  ->  Index Only Scan Backward using events_device_occurred_idx on events  (actual rows=100 loops=1)
        Index Cond: (device_id = 4821)
        Heap Fetches: 100
        Buffers: shared hit=12 read=104
-- Heap Fetches equals rows: every value needed a heap visit to check visibility

The table’s state:

SELECT relname, n_live_tup, n_dead_tup, n_ins_since_vacuum, last_vacuum, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'events';
 relname | n_live_tup | n_dead_tup | n_ins_since_vacuum | last_vacuum | last_autovacuum
---------+------------+------------+--------------------+-------------+-----------------
 events  |  104820044 |          0 |           41204880 |             |
-- never vacuumed: no dead tuples ever existed to trigger it

After a vacuum:

Limit  (actual time=0.041..0.208 rows=100 loops=1)
  ->  Index Only Scan Backward using events_device_occurred_idx on events  (actual rows=100 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=8
-- the visibility map now covers these pages; the heap is untouched
Three signals on an append-only table Three items are annotated. Heap fetches equal to the row count in an index-only scan indicate the visibility map is not set. Zero dead tuples with a large insert count since vacuum indicate the table never triggered the classic thresholds. An empty last vacuum timestamp confirms it. Heap Fetches: 100 of 100 rows visibility map not set n_dead_tup = 0, n_ins_since_vacuum = 41M never triggered the old thresholds last_autovacuum is null never vacuumed at all the plan looks right; the execution is not

Step-by-Step Resolution #

  1. Find append-only tables that are never vacuumed: zero or near-zero n_dead_tup, a large n_ins_since_vacuum, and a null or very old last_autovacuum.

  2. Confirm the plan cost by checking Heap Fetches in an index-only scan on the table. A figure close to the row count means the visibility map is not helping.

  3. Set an absolute insert threshold with the scale factor at zero, so the trigger does not grow with the table:

    ALTER TABLE events SET (
  autovacuum_vacuum_insert_threshold    = 100000,
  autovacuum_vacuum_insert_scale_factor = 0.0
);

  4. Keep the analyze thresholds absolute too, for the reason in per-table autovacuum settings: a growing table’s statistics otherwise age indefinitely.

  5. Lower vacuum_freeze_min_age for the table if freeze debt is the concern, so ordinary vacuums freeze tuples rather than leaving them for an anti-wraparound run.

  6. On PostgreSQL 12 or earlier, schedule an explicit VACUUM — there is no insert trigger to configure.

  7. Re-check Heap Fetches after the next vacuum cycle. It should fall to zero or near it for recently vacuumed ranges.

How the visibility map keeps up A timeline across a day. Inserts arrive continuously. Without an insert threshold no vacuum runs and no pages are marked all-visible. With an absolute insert threshold a vacuum runs every few hours, marking the newly filled pages, so index-only scans over recent data need no heap fetches. 00:00 inserts begin 06:00 vacuum marks pages 12:00 index-only scans clean 18:00 vacuum marks pages 24:00 freeze debt bounded without the trigger, every stage stays unmarked

Before and After #

-- BEFORE: never vacuumed
Index Only Scan Backward … Heap Fetches: 100   Buffers: shared hit=12 read=104   412 ms

-- AFTER: autovacuum_vacuum_insert_threshold = 100000, scale factor 0
Index Only Scan Backward … Heap Fetches: 0     Buffers: shared hit=8             0.2 ms

Partitioned append-only tables #

Time-partitioned event tables are the common shape here, and they have a useful property: once a partition stops receiving inserts it never changes again, so a single vacuum makes it permanently all-visible and permanently frozen. The work is bounded and can be done once, at the moment the partition goes cold.

That makes the retention job the natural place for it. A scheduler that creates tomorrow’s partition and drops the oldest can also run VACUUM (FREEZE, ANALYZE) on the partition that has just stopped being written to. The cost is paid once per partition on a table nobody is querying heavily yet, instead of by an anti-wraparound vacuum on a random Tuesday.

Index-only scans and the value of the map #

The reason to care is worth stating precisely, because “vacuum makes things faster” is too vague to act on. An index-only scan that must check visibility performs, per row, a random heap read. For a scan returning a hundred rows that is a hundred random reads instead of a handful of sequential index reads — a difference of two or three orders of magnitude on cold storage, and still large on warm.

The planner knows this. It estimates the fraction of pages that are all-visible from pg_class.relallvisible divided by relpages, and it prices the index-only scan accordingly. So an unvacuumed table does not merely execute index-only scans slowly; it makes the planner reject them, because it correctly prices them as expensive. A covering index built specifically to enable index-only scans can therefore appear to do nothing at all until the table is vacuumed, which is a confusing way to discover that the two features depend on each other.

The settings still matter for the current partition, which is being written to continuously and where recent-data queries concentrate. An absolute insert threshold on the partition template keeps its visibility map close to current, which is exactly where index-only scans are most valuable — dashboards almost always ask about the last hour.

Common Pitfalls #

Assuming no dead tuples means no vacuum needed. Visibility and freezing both require it. Diagnostic signal: high Heap Fetches on index-only scans. Fix: insert-triggered vacuum.

Leaving the insert scale factor at 0.2. The trigger recedes as the table grows. Diagnostic signal: vacuum intervals lengthening on a growing table. Fix: absolute threshold, zero scale factor.

Setting it on the partitioned parent. Partitions are unaffected. Diagnostic signal: partitions with default settings. Fix: set it in the partition creation path.

Vacuuming cold partitions repeatedly. Nothing changes, so nothing is gained. Diagnostic signal: repeated vacuums of read-only partitions. Fix: VACUUM (FREEZE) once when the partition goes cold.

Frequently Asked Questions #

Why does an insert-only table need vacuuming? #

To set visibility map bits, which index-only scans depend on, and to freeze old transaction IDs before wraparound. Neither requires dead tuples, and both affect performance on a table that only ever receives inserts.

What is autovacuum_vacuum_insert_threshold? #

A PostgreSQL 13 setting that triggers autovacuum based on rows inserted since the last vacuum, rather than on dead tuples. It exists because append-only tables previously never qualified for autovacuum at all.

Why does my index-only scan show high Heap Fetches? #

The pages holding the matching rows are not marked all-visible, so visibility must be checked in the heap. That happens when the table has not been vacuumed recently, or when it is being updated continuously.

Up: Autovacuum and Plan Stability