---
title: "PostgreSQL Index-Only Scans: Why Your Covering Indexes May Be Lying to You"
published: true
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: postgresql, architecture, performance, api
canonical_url: https://blog.mvpfactory.co/postgresql-index-only-scans-visibility-map
---
## What You Will Learn
You added a covering index. EXPLAIN says "Index Only Scan." You ship it, feeling good. Then EXPLAIN ANALYZE tells a different story: thousands of heap fetches, nearly identical latency.
In this workshop, I will show you exactly what the visibility map is, how to detect when it is silently killing your index-only scans, and how to tune autovacuum for high-write mobile backends so your covering indexes actually deliver on their promise.
## Prerequisites
- PostgreSQL 12+
- A table with at least one covering index
- Basic familiarity with `EXPLAIN ANALYZE` output
- Access to `pg_stat_user_tables` and `pg_visibility`
---
## How Index-Only Scans Actually Work
Here is a pattern I see misunderstood in every codebase I audit.
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 does not. 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 that bit is set can an index-only scan skip the heap fetch entirely.
VACUUM sets these bits. Autovacuum runs VACUUM. Connect the dots.
---
## Step 1: Read EXPLAIN ANALYZE Correctly
Most teams read the node type and stop there. Do not do that.
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, event_ts, event_type
FROM mobile_events
WHERE user_id = 42
ORDER BY event_ts DESC
LIMIT 50;
Here is what a degraded result looks like:
sql
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.
Here is what a healthy scan looks like on a well-vacuumed table:
sql
Index Only Scan using idx_events_covering on mobile_events
Heap Fetches: 0
Buffers: shared hit=14
Zero heap fetches. That is what you thought you were getting.
---
## Step 2: Understand 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.
---
## Step 3: Tune Autovacuum Per Table
Here is the minimal setup to get this working. PostgreSQL's default thresholds are designed for balanced workloads. High-write tables need per-table overrides:
sql
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
autovacuum_vacuum_cost_limit = 800 -- more I/O budget per round (default: 200)
);
Then monitor VM coverage directly:
sql
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 they should not be making.
---
## Step 4: Force VACUUM After Batch Loads
For tables that receive large batch writes followed by read-heavy periods — common in mobile analytics pipelines — run a manual VACUUM immediately after the batch load:
sql
VACUUM (VERBOSE, ANALYZE) mobile_events;
This is not a permanent fix. But if query latency drops significantly after a manual VACUUM, you have just confirmed the real bottleneck. At that point, autovacuum configuration is what needs work.
---
## Gotchas
**The plan label does not guarantee the performance.** "Index Only Scan" with thousands of heap fetches is a lie your query planner is technically allowed to tell you. Always check `Heap Fetches` in ANALYZE output — not just the scan node type.
**Tuning autovacuum globally will fail you at scale.** The docs do not emphasize this strongly enough, but global defaults are not designed for your write throughput. Tune per table. High-write tables need `autovacuum_vacuum_scale_factor` at 1–2% and a higher `autovacuum_vacuum_cost_limit`.
**VM coverage belongs in your observability stack.** 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 — that is the gotcha that will save you hours of puzzling over query plans that look fine on paper.
---
## Conclusion
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.
Three actions to take right now:
1. Run `EXPLAIN (ANALYZE, BUFFERS)` on your most critical queries and check `Heap Fetches`.
2. Tune autovacuum per table, not globally, for every high-write table in your schema.
3. Add `vm_visible_pages / heap_pages` to your monitoring dashboards.
Your covering index is only as good as your visibility map. Now you know how to keep them in sync.
Top comments (0)