PostgreSQL index-only scans: the hidden VACUUM dependency

SEO Meta Description: Why your PostgreSQL index-only scans may secretly be doing full heap reads — and how aggressive VACUUM tuning and visibility map monitoring actually fix it.

---

TL;DR

Index-only scans (IOS) in PostgreSQL promise to skip heap fetches entirely. In practice, without a properly maintained visibility map, they degrade silently into full heap reads. The culprit is VACUUM — or rather, the lack of aggressive autovacuum tuning. Here’s how to measure it.

---

What index-only scans actually promise

Most teams enable index-only scans and assume they work. Many are silently falling back to full heap reads on every query — with no warning in the query plan, no error in the logs, just quietly wasted I/O. For read-heavy workloads on wide tables, the gap between expected and actual behavior is a real performance liability.

A canonical example:

CREATE INDEX idx_orders_user_status
  ON orders (user_id, status)
  INCLUDE (created_at, total_amount);

-- This *should* be an IOS:
SELECT user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 42 AND status = 'completed';

Run EXPLAIN (ANALYZE, BUFFERS) and you expect to see Index Only Scan. What the plan output doesn’t immediately tell you is how many of those index hits still fell back to heap reads.

---

The visibility map: the gate between index and heap

PostgreSQL’s visibility map (VM) is a compact bitmap — two bits per heap page — tracking two properties:

Bit	Meaning
all-visible	Every tuple on the page is visible to all current transactions
all-frozen	Every tuple is frozen (safe to skip during VACUUM)

When executing an index-only scan, PostgreSQL checks the VM for each candidate heap page. If the all-visible bit is set, it trusts the index value and skips the heap fetch. If that bit isn’t set, it falls back to reading the heap page to verify tuple visibility — negating the entire benefit of the IOS.

This is the dependency that bites teams in production.

---

Why heap fetch fallback rates are higher than you think

In any table with active writes, the all-visible bits get cleared whenever a page is modified. VACUUM is the only process that re-sets them. On tables with moderate write throughput, a large fraction of pages may have stale visibility bits at any given time.

Consider a table receiving 500 writes/second:

Autovacuum setting	VM coverage (est.)	Heap fetch rate
Default (scale_factor=0.2)	40–60%	40–60% of IOS hits
Tuned (scale_factor=0.01)	85–95%	5–15% of IOS hits
Aggressive (scale_factor=0.005)	95–99%	1–5% of IOS hits

Methodology note: Estimates assume ~200-byte rows, 8KB pages, default page fill factor, and default autovacuum cost settings. Wide rows, low fill factors, or heavily throttled VACUUM will shift these numbers significantly — calibrate against your own heap_fetches metrics.

These numbers are directional, but the pattern holds across production systems I’ve observed: default autovacuum is calibrated for safety, not for index-only scan effectiveness.

---

The metrics that reveal the truth

Stop guessing. The first query gives you a broad picture:

SELECT
  relname,
  idx_scan,
  heap_blks_read,       -- broad signal: ALL heap page reads, not IOS-specific
  heap_blks_hit,        -- heap pages served from shared_buffers
  n_live_tup,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

heap_blks_read is a useful starting point, but it counts all heap block reads across the table — not just IOS fallbacks. Cross-reference it with the IOS-specific signal below before drawing conclusions.

For the precise IOS degradation rate, use heap_fetches from pg_stat_user_indexes — this column counts exclusively the visibility-map misses that forced a heap read during an index-only scan:

SELECT
  indexrelname,
  idx_scan,
  idx_tup_read,
  heap_fetches,
  heap_fetches::float / NULLIF(idx_scan, 0) AS heap_fetch_rate
FROM pg_stat_user_indexes
WHERE relname = 'orders';

The heap_fetch_rate ratio is the number to watch. A well-vacuumed table under a healthy IOS workload should sit well below 0.05. If you’re seeing 0.3 or higher, your visibility map is stale and VACUUM isn’t keeping up.

---

Tuning autovacuum for index-only scan effectiveness

The fix is targeted autovacuum tuning at the table level, not globally:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay   = 2,
  autovacuum_vacuum_cost_limit   = 400
);

For high-write tables, also reduce the cost delay to let VACUUM run more aggressively without being throttled. In my experience building production systems on PostgreSQL at scale, this single change — applied to the right tables — produces the most measurable IOS improvement with the lowest risk.

---

What to actually do

1. Track heap_fetch_rate, not just query plans. EXPLAIN showing Index Only Scan doesn’t mean you’re getting IOS benefits. Query heap_fetches::float / NULLIF(idx_scan, 0) from pg_stat_user_indexes regularly on your high-read tables. This ratio directly measures visibility-map misses — the actual cost of degraded IOS.

2. Tune autovacuum per table, not globally. Lower autovacuum_vacuum_scale_factor to 0.005–0.01 on tables where index-only scans matter. Default settings optimize for stability, not VM coverage. Apply changes with ALTER TABLE ... SET (...) to avoid unintended side effects elsewhere.

3. Schedule manual VACUUM after bulk writes. Batch imports, nightly ETL jobs, and migrations all clear visibility bits en masse. A targeted VACUUM (ANALYZE) immediately after restores VM coverage before your read traffic hits — and it’s the fastest path back to a clean heap_fetch_rate.

---

Tags: backend architecture devops productengineering