PostgreSQL index-only scans: the hidden VACUUM dependency

Meta description: Why your PostgreSQL covering indexes silently fall back to heap access — and how to tune VACUUM and visibility maps for high-write SaaS workloads.

---

TL;DR

You added a covering index. EXPLAIN says Index Only Scan. But heap fetches are still happening — silently, expensively. The culprit is almost always the visibility map. Without frequent enough VACUUM runs, your carefully crafted indexes degrade into heap-touching index scans. Diagnose it, fix it, and tune autovacuum before your SaaS workload buries you.

---

What index-only scans actually require

Most developers understand the first requirement for an index-only scan: all columns referenced by the query must live in the index. Create a covering index, done. What they miss is the second requirement — and it’s the one that kills you in production.

PostgreSQL must confirm that every tuple returned by the index is visible to the current transaction without touching the heap. It does this using the visibility map — a compact bitmap, one bit per heap page, maintained separately from the main data files. When the all-visible bit is set for a page, PostgreSQL knows VACUUM has confirmed every tuple on that page is visible to all current and future transactions. Index-only scan proceeds without a heap fetch.

When that bit is not set — because writes have dirtied the page since the last VACUUM run — PostgreSQL falls back to a heap fetch to confirm visibility. Your “optimized” query just became a regular index scan with extra steps.

---

Reading the signals in EXPLAIN and pg_stat_user_tables

A query plan will tell you something is wrong, but you need to know where to look:

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, created_at FROM events
WHERE tenant_id = 42 AND created_at > now() - interval '7 days';

Output to watch for:

Index Only Scan using idx_events_covering on events
  Heap Fetches: 18402
  Buffers: shared hit=4821 read=3107

Non-zero Heap Fetches is the smoking gun. Your index-only scan is not actually index-only.

Now cross-reference with pg_stat_user_tables:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio_pct,
  last_autovacuum,
  last_vacuum,
  n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'events'
ORDER BY n_dead_tup DESC;

If dead_ratio_pct is above 5% and last_autovacuum is hours or days old on an active table, you’ve found your problem.

---

Why high-write SaaS workloads are especially vulnerable

In my experience building production systems for multi-tenant SaaS platforms, the events and audit tables are always the first to degrade. Every INSERT or UPDATE clears the all-visible bit for the affected heap pages. Autovacuum’s default configuration was designed for OLTP workloads with moderate write rates — not the firehose of a modern SaaS backend processing thousands of events per minute.

Parameter	Default	High-Write Recommendation
autovacuum_vacuum_scale_factor	0.20 (20% of table)	0.01–0.05
autovacuum_vacuum_threshold	50 rows	100–500 rows
autovacuum_vacuum_cost_delay	2ms	0–1ms
autovacuum_max_workers	3	5–8
autovacuum_naptime	1 min	15–30 sec

The default scale factor means autovacuum won’t trigger on a 10-million-row table until 2 million rows have been modified. That’s a long time to accumulate dead tuples and un-vacuumed pages on a write-heavy workload.

Apply table-level overrides for your hottest tables rather than globally tuning the cluster — more surgical, fewer surprises:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_delay = 1
);

---

The visibility map recovery path

Once you tune autovacuum, you need to prime the visibility map on existing tables. A manual VACUUM with the ANALYZE option is the fastest path:

VACUUM (ANALYZE, VERBOSE) events;

Monitor progress for large tables using pg_stat_progress_vacuum. After VACUUM completes, re-run your EXPLAIN — Heap Fetches should drop dramatically. On a 50-million-row events table ingesting roughly 8,000 writes per minute on PostgreSQL 15 (c5.2xlarge, gp3 storage), this dropped heap fetches from ~20,000 per query to under 50, with query latency falling by approximately 60%. Your mileage will vary with write rate and hardware, but the directional impact of restoring the visibility map is consistently large.

I run this diagnostic query weekly on high-write SaaS databases to stay ahead of it:

SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  age(relfrozenxid) AS xid_age,
  last_autovacuum
FROM pg_stat_user_tables
JOIN pg_class ON pg_stat_user_tables.relid = pg_class.oid
WHERE schemaname = 'public'
  AND n_live_tup > 10000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

---

Three things to take away

Always verify index-only scans with EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN. Non-zero Heap Fetches means your covering index is not doing what you think. The plan node name is misleading without the runtime data.

Lower autovacuum_vacuum_scale_factor on high-write tables. The default 20% is far too conservative for event logs, audit tables, or any write-heavy SaaS workload. Set it per-table to 1–5% and monitor pg_stat_user_tables weekly.

Treat VACUUM as a first-class performance concern, not a maintenance afterthought. The visibility map is the invisible layer between your index strategy and actual query performance. A well-tuned autovacuum configuration is worth more than most index additions.

---

Tags: backend saas architecture postgresql