PostgreSQL Index-Only Scans: Why Your Covering Indexes May Be Lying to You

Meta description: Learn how PostgreSQL’s visibility map controls index-only scan efficiency, why autovacuum frequency is critical for high-write backends, and how to diagnose heap fetch fallbacks with EXPLAIN ANALYZE.

Tags: backend architecture microservices mobile api

---

TL;DR

You added a covering index. EXPLAIN says “Index Only Scan.” You ship it, feeling good. But EXPLAIN ANALYZE tells a different story: thousands of heap fetches, nearly identical latency. The culprit is the visibility map — a bitmap PostgreSQL uses to decide whether it can trust the index alone. When autovacuum can’t keep up with your write throughput, that map goes stale and your index-only scan silently degrades into a full index-plus-heap scan. This post shows you how to detect it, why it matters for mobile backends, and how to fix it.

---

How index-only scans actually work

Most engineers understand the surface-level promise: a covering index contains all columns the query needs, so Postgres never touches the heap. What gets glossed over is the conditional nature of that promise.

PostgreSQL’s MVCC model means heap tuples carry visibility metadata. An index entry doesn’t; it points to a tuple version that may or may not be visible to the current transaction. Before skipping the heap, Postgres must answer: “Is this tuple definitely visible to everyone?”

That answer comes from the visibility map (VM), a compact, one-bit-per-page structure where a set bit means every tuple on that heap page is visible to all current and future transactions. Only when the VM bit is set can an index-only scan skip the heap fetch entirely.

VACUUM sets these bits. Autovacuum runs VACUUM. Connect the dots.

---

The EXPLAIN ANALYZE signal you’re probably missing

Most teams get this wrong: they read the node type, not the statistics.

EXPLAIN (ANALYZE, BUFFERS) 
SELECT user_id, event_ts, event_type
FROM mobile_events
WHERE user_id = 42
ORDER BY event_ts DESC
LIMIT 50;

Index Only Scan using idx_events_covering on mobile_events
  (cost=0.56..18.23 rows=50) (actual time=0.341..1.204 rows=50)
  Heap Fetches: 2847
  Buffers: shared hit=312 read=198

Heap Fetches: 2847. That is not an index-only scan in any meaningful sense. Postgres visited the heap 2,847 times because the visibility map bits were not set on those pages. You got the plan label without the performance benefit.

A healthy index-only scan on a well-vacuumed table:

Index Only Scan using idx_events_covering on mobile_events
  Heap Fetches: 0
  Buffers: shared hit=14

Zero heap fetches. That’s what you thought you were getting.

---

Why high-write mobile backends suffer most

Mobile apps generate relentless, bursty write patterns: session events, telemetry, push acknowledgements, sync deltas. Each UPDATE or DELETE on a heap page clears its visibility map bit. Autovacuum must re-visit and re-set it before index-only scans can skip heap fetches again.

Write Throughput	Default Autovacuum Keeps Up?	Typical Heap Fetch Rate
< 100 rows/sec	Yes	Near 0%
100–500 rows/sec	Marginally	10–40%
> 500 rows/sec	No	60–100%
Bulk ingest (ETL)	No	100%

At high churn, the visibility map is perpetually stale. Your covering index becomes decoration.

---

Tuning autovacuum for write-heavy tables

PostgreSQL’s default autovacuum thresholds are designed for balanced workloads. For high-write tables, you need per-table overrides:

ALTER TABLE mobile_events SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- trigger at 1% dead tuples (default: 20%)
  autovacuum_vacuum_cost_delay   = 2,      -- ms between cost limit hits (default: 2, but verify)
  autovacuum_vacuum_cost_limit   = 800     -- more I/O budget per round (default: 200)
);

Monitor VM coverage directly:

SELECT relname,
       n_dead_tup,
       n_live_tup,
       last_autovacuum,
       (pg_relation_size(oid) / 8192)::int AS heap_pages,
       (SELECT count(*) FROM pg_visibility(oid) WHERE all_visible) AS vm_visible_pages
FROM pg_stat_user_tables
WHERE relname = 'mobile_events';

If vm_visible_pages is significantly less than heap_pages, your index-only scans are paying for heap fetches you shouldn’t be making.

---

When to force a VACUUM

For tables that receive large batch writes followed by read-heavy periods — common in mobile analytics pipelines — a manual VACUUM immediately after the batch load resets the visibility map aggressively:

VACUUM (VERBOSE, ANALYZE) mobile_events;

This isn’t a permanent fix. But if query latency drops significantly after a manual VACUUM, you’ve just confirmed the real bottleneck. At that point, autovacuum configuration is what needs work.

---

What to actually do about this

1. Check Heap Fetches, not just the plan node type. A plan labeled “Index Only Scan” with thousands of heap fetches is a lie your query planner is technically allowed to tell you.

2. Tune autovacuum per table, not globally. High-write tables need aggressive autovacuum_vacuum_scale_factor (1-2%) and a higher autovacuum_vacuum_cost_limit to keep visibility map bits current. Global defaults will fail you at scale.

3. Add VM coverage to your observability dashboard. Track vm_visible_pages / heap_pages alongside dead tuple counts and last autovacuum timestamps. A dropping ratio predicts index-only scan degradation before your latency graphs catch up.

In my experience building production systems with heavy mobile write workloads, visibility map coverage is the single most under-monitored PostgreSQL metric — and the one with the highest return when you fix it.