Running PostgreSQL Tables with Moderate Write Churn

A latency-sensitive PostgreSQL query had a peculiar pattern. Immediately after VACUUM, it was fast. Over the next few hours it became progressively slower. Another vacuum made it fast again.

The plan still said Index Only Scan, but the changing number inside EXPLAIN (ANALYZE, BUFFERS) was Heap Fetches. Fixing the maintenance cadence exposed a second problem: long-term index growth had left the hot indexes several times larger than fresh copies.

The useful operating model is straightforward: moderate, continuous writes create two separate maintenance problems. Vacuum restores tuple visibility and reusable space. Reindexing compacts an index whose physical structure has grown inefficient. One does not substitute for the other.

This post explains both failure modes, shows their production impact, and provides a local reproduction and a practical monitoring loop.

What “all-visible” means

PostgreSQL uses multiversion concurrency control. Different transactions can legally see different versions of the same row. An index entry contains the indexed values and a pointer to the heap tuple, but it does not contain enough transaction state to decide whether that tuple belongs in a particular snapshot.

PostgreSQL therefore maintains a visibility map alongside each heap relation. The map has an all-visible bit for every heap page. When the bit is set, PostgreSQL knows that every tuple on that page is visible to every relevant snapshot. An index-only scan can trust the index entry and skip the heap page.

When the bit is not set, the executor must visit the heap and inspect tuple visibility. The plan node is still named Index Only Scan, but each visit appears under Heap Fetches.

Index Only Scan using idx_queue_ready on queue_items
  Index Cond: (...)
  Heap Fetches: 290035

That is the practical distinction: an index can cover every selected column while the scan still performs hundreds of thousands of heap reads.

Why writes clear visibility

The visibility map is conservative and page-level. One write can invalidate the guarantee for an entire 8 KB page.

An inserted tuple is not visible to a transaction whose snapshot predates the insert. An updated or deleted tuple can also have different answers for older and newer snapshots. PostgreSQL immediately clears the page’s all-visible bit when the page is modified.

Once the relevant transactions finish, PostgreSQL does not set the bit again inline with foreground traffic. VACUUM examines the page and restores the bit when it is safe.

Append-only traffic usually damages a narrow tail of new pages. Scattered churn is worse. Deletes and updates touch pages across the table. After vacuum makes deleted space reusable, later inserts can fill holes across old pages and clear visibility throughout the heap again.

Measurements under steady writes

The measured relation held several million rows. A manual vacuum left essentially every page all-visible. A later batch of writes—small relative to the full table—was enough to leave fewer than half of the pages marked all-visible.

The query relied on a covering index. As visibility declined, PostgreSQL performed more heap checks and sometimes selected a substantially more expensive plan. Latency increased by roughly four times. Immediately after vacuum, the same representative SQL returned to its fast path.

The table was not waiting on an extraordinary number of dead tuples. Its insert-triggered vacuum threshold was simply too large relative to how quickly writes invalidated useful pages.

Vacuum fixed visibility; reindexing fixed bloat

Standard VACUUM removes dead index entries and marks their space reusable. It generally does not make relation files smaller. An index can therefore remain physically large after its dead entries are cleaned up, consuming more cache and requiring more pages to be traversed.

Concurrent rebuilds reduced the valid footprint of one index set from about 3.87 GB to 1.15 GB. A second set fell from about 1.74 GB to 422 MB.

After both fixes, warm-cache latency was about one-fifth of the degraded result. Cold-cache executions remained slower because they still had to read index and heap blocks. The plan also made some heap fetches after new writes arrived, which is why both fixes matter.

The indexes were rebuilt with REINDEX INDEX CONCURRENTLY, one at a time. The operation needs temporary disk headroom and does more work than a normal vacuum, but it avoids blocking ordinary inserts, updates, and deletes for the duration of the rebuild. It also cannot run inside a transaction block.

REINDEX INDEX CONCURRENTLY public.idx_queue_ready;

A failed or interrupted concurrent rebuild can leave an invalid transient index. Always verify the result rather than assuming command completion means every index is healthy:

SELECT
    n.nspname AS schema_name,
    c.relname AS index_name,
    i.indisvalid,
    i.indisready,
    pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indrelid = 'public.queue_items'::regclass
ORDER BY pg_relation_size(c.oid) DESC;

Names ending in _ccnew or _ccold are a useful clue after a concurrent operation, but validity flags—not the name—are the authoritative check.

A one-million-row reproduction

The reproduction uses a disposable unlogged table with a queue-shaped access pattern. The table has a state, priority, availability time, and payload. A partial covering index supports ordered reads of ready work.

CREATE UNLOGGED TABLE queue_items (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    state text NOT NULL,
    priority integer NOT NULL,
    available_at timestamptz NOT NULL,
    payload text NOT NULL
);

CREATE INDEX idx_queue_ready
ON queue_items (available_at, id)
INCLUDE (priority)
WHERE state = 'ready';

The benchmark loads one million ready rows, vacuums the table, warms the cache, and measures the median of three executions. The query reads half the ready queue through the covering index.

EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(priority)
FROM (
    SELECT id, priority
    FROM queue_items
    WHERE state = 'ready'
    ORDER BY available_at, id
    LIMIT 500000
) ready_jobs;

Immediately after vacuum

Index Only Scan using idx_queue_ready on queue_items
  (actual rows=500000 loops=1)
  Heap Fetches: 0
Execution Time: 102.982 ms

All 20,409 heap pages were all-visible. The executor returned every selected value from the index.

Apply distributed churn

The churn stage deletes 10% of the rows, distributed across the table, then inserts 100,000 replacements.

DELETE FROM queue_items
WHERE id % 100 < 10;

INSERT INTO queue_items (state, priority, available_at, payload)
SELECT ...
FROM generate_series(1, 100000);

Only 100,000 rows were deleted and 100,000 were inserted. Each counter remained below the default vacuum trigger for a one-million-row table:

dead-tuple trigger ≈ 50   + 0.20 × 1,000,000 = 200,050
insert trigger     ≈ 1000 + 0.20 × 1,000,000 = 201,000

The changed rows were a minority, but they were spread across the heap. After ANALYZE refreshed the catalog estimate, only 38% of pages remained all-visible.

The same query after churn

Index Only Scan using idx_queue_ready on queue_items
  (actual rows=500000 loops=1)
  Heap Fetches: 290035
Execution Time: 196.898 ms

The SQL, index, row count, and warm-cache measurement method were unchanged. The scan made 290,035 heap fetches and took almost twice as long.

Vacuum restores the fast path

VACUUM (ANALYZE) queue_items;

After vacuum, all 22,449 pages were all-visible again:

Index Only Scan using idx_queue_ready on queue_items
  (actual rows=500000 loops=1)
  Heap Fetches: 0
Execution Time: 105.174 ms

The experiment produces the same sawtooth: fast after vacuum, slower as writes clear visibility, and fast again after the next vacuum.

Why percentage thresholds fail on large tables

Percentage-based settings scale with total table size. Query performance, however, can deteriorate according to how many heap pages the workload touches. Those are not equivalent.

Ten thousand modifications concentrated on a few append-only pages may have little effect. Ten thousand modifications scattered across ten thousand old pages can invalidate a meaningful portion of the visibility map.

On a multi-million-row relation, even an insert scale factor of 0.01 can delay vacuum until tens of thousands of inserts have accumulated. Visibility and latency can deteriorate well before that point.

Tune the table, not the whole cluster

A lower per-table insert scale factor was tested across complete autovacuum cycles:

ALTER TABLE queue_items SET (
    autovacuum_vacuum_scale_factor = 0.002,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_insert_scale_factor = 0.001,
    autovacuum_vacuum_insert_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.002,
    autovacuum_analyze_threshold = 1000
);

The lower threshold kept effective visibility above 90% near the end of an observed cycle. Autovacuum then fired and began the next cycle without manual intervention.

These values are observations from one workload, not universal defaults. A useful threshold comes from the table’s write rate, the distribution of modified pages, the query’s tolerance for heap fetches, and the cost of vacuuming the table and its indexes.

Build one monitoring loop

Start with the execution plan. The most useful field is not the node’s name; it is Heap Fetches.

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

Then compare catalog visibility and churn counters:

SELECT
    c.relname,
    round(100.0 * c.relallvisible / NULLIF(c.relpages, 0), 1)
        AS catalog_visible_pct,
    s.n_dead_tup,
    s.n_ins_since_vacuum,
    s.last_autovacuum
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relname = 'queue_items';

pg_class.relallvisible is useful for trends but can lag active writes. Heap-fetch ratios from a representative index-only scan show the effective cost directly. For an exact visibility-map inspection, PostgreSQL provides the optional pg_visibility extension.

Finally, inspect the table’s effective storage parameters rather than assuming the cluster defaults apply:

SELECT relname, reloptions
FROM pg_class
WHERE relname = 'queue_items';

Track the heap and each index separately. Relation size alone does not prove bloat—a growing live data set should produce growing indexes—but a sudden change in bytes per live row or a large difference from a freshly rebuilt equivalent deserves investigation.

SELECT
    c.oid::regclass AS relation,
    c.relkind,
    pg_size_pretty(pg_relation_size(c.oid)) AS size,
    s.idx_scan,
    i.indisvalid,
    i.indisready
FROM pg_class c
LEFT JOIN pg_stat_user_indexes s ON s.indexrelid = c.oid
LEFT JOIN pg_index i ON i.indexrelid = c.oid
WHERE c.oid = 'public.queue_items'::regclass
   OR c.oid IN (
       SELECT indexrelid
       FROM pg_index
       WHERE indrelid = 'public.queue_items'::regclass
   )
ORDER BY pg_relation_size(c.oid) DESC;

For a direct sample of dead tuples and free space, use the optional pgstattuple extension. On large production relations, choose its approximate functions where possible and account for the I/O cost of exact scans.

An operating playbook for moderate writes

1. Keep a representative plan. Record execution time, buffers, plan shape, and heap fetches for the query whose latency matters.
2. Measure a complete vacuum cycle. Watch writes since vacuum, dead rows, all-visible pages, and query latency immediately after vacuum and near the next trigger.
3. Set per-table thresholds. Base them on write rate and the amount of visibility loss the query tolerates. Do not make a cluster-wide setting carry the needs of one hot table.
4. Separate vacuum health from physical size. Vacuum should keep dead rows and visibility under control. Index size and bytes per live row reveal a different trend.
5. Rebuild deliberately. Use concurrent index rebuilds on live systems, allow temporary disk headroom, handle one large index at a time, and inspect validity afterward.
6. Keep observing. A single fast post-vacuum query is not proof of stable operation. The goal is acceptable latency at the worst point of the cycle.

Reproduce it

The benchmark automation creates one disposable local table, runs the three measured stages, records the plans, and removes the table afterward. The schema, query, and churn operations shown above are the complete reproduction.

Autovacuum is disabled only on the disposable benchmark table so the three stages remain deterministic. The benchmark also calculates the approximate default triggers to show that neither side of the simulated churn would have launched a default vacuum.

Takeaway

PostgreSQL handles moderate writes well, but “autovacuum is on” is not an operating strategy. A steady stream of distributed writes can clear visibility across much of a large heap before percentage-based thresholds fire, while years of index page splits and reusable gaps can leave indexes far larger than fresh copies.

Measure the two problems separately. Use vacuum cadence to control dead tuples, planner statistics, and visibility. Use index-size trends and deliberate concurrent rebuilds to control physical index bloat. Then judge the system at the slow end of its maintenance cycle, not only in the clean minute immediately after vacuum.

References

• PostgreSQL: Routine Vacuuming
• PostgreSQL: Visibility Map
• PostgreSQL: Index-Only Scans and Covering Indexes
• PostgreSQL: Automatic Vacuuming
• PostgreSQL: REINDEX