---
title: "PostgreSQL Index-Only Scans: The Hidden VACUUM Dependency Killing Your Read Performance"
published: true
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."
tags: [postgresql, performance, architecture, devops]
canonical_url: https://mvpfactory.co/blog/postgresql-index-only-scans-vacuum-dependency
---
What You Will Learn
By the end of this walkthrough, you will understand why an Index Only Scan in your query plan does not guarantee you are actually skipping heap reads — and you will have the exact SQL queries and ALTER TABLE settings to measure, diagnose, and fix the problem on any high-read PostgreSQL table.
This is the gotcha that bites production teams who enable covering indexes, nod at their EXPLAIN output, and wonder why I/O costs never drop.
Prerequisites
- PostgreSQL 9.6 or later (visibility map bits became reliable here)
- Access to
pg_stat_user_tablesandpg_stat_user_indexes - A table with meaningful read and write volume to measure against
The Setup: A Covering Index That Should Work
Let me show you the pattern. You create a covering index to serve a frequent query without touching the heap:
CREATE INDEX idx_orders_user_status
ON orders (user_id, status)
INCLUDE (created_at, total_amount);
-- This *should* be an index-only scan:
SELECT user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 42 AND status = 'completed';
You run EXPLAIN (ANALYZE, BUFFERS) and see Index Only Scan. Job done, right? Not quite. The plan label tells you PostgreSQL attempted an index-only scan. It does not tell you how many of those index hits silently fell back to full heap reads.
Why the Fallback Happens: The Visibility Map
PostgreSQL maintains a visibility map (VM) — a compact bitmap with two bits per heap page:
| Bit | Meaning |
|---|---|
all-visible |
Every tuple on the page is visible to all current transactions |
all-frozen |
Every tuple is frozen and safe to skip during VACUUM |
During 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 is not set, it falls back to reading the heap page to verify tuple visibility — and the entire benefit of the covering index evaporates.
The only process that re-sets those all-visible bits after writes clear them is VACUUM. On a table receiving 500 writes per second with default autovacuum settings, a large fraction of your pages will have stale visibility bits at any given time.
Here is the directional picture:
| 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 |
Note: These estimates assume ~200-byte rows, 8KB pages, default fill factor, and default autovacuum cost settings. Calibrate against your own metrics — the pattern holds, but wide rows and throttled VACUUM shift the numbers.
Step 1: Measure Your Actual Heap Fetch Rate
Stop guessing. Start with the broad table signal:
SELECT
relname,
idx_scan,
heap_blks_read,
heap_blks_hit,
n_live_tup,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
heap_blks_read is a useful starting signal, but it counts all heap block reads across the table — not exclusively index-only scan fallbacks. For the precise IOS degradation rate, go to pg_stat_user_indexes:
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';
heap_fetches counts exclusively the visibility-map misses that forced a heap read during an index-only scan. The heap_fetch_rate ratio is the number to track. A well-vacuumed table should sit well below 0.05. If you are seeing 0.3 or higher, your visibility map is stale and VACUUM is not keeping up.
Step 2: Tune Autovacuum Per Table
Apply targeted autovacuum settings at the table level — not globally, to avoid unintended side effects elsewhere:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 2,
autovacuum_vacuum_cost_limit = 400
);
Lowering autovacuum_vacuum_cost_delay lets VACUUM run more aggressively without being throttled between work bursts. In my experience, this single change applied to the right tables produces the most measurable IOS improvement with the lowest risk.
Step 3: Schedule Manual VACUUM After Bulk Writes
Batch imports, nightly ETL jobs, and migrations all clear visibility bits en masse. A targeted manual VACUUM immediately after restores VM coverage before your read traffic hits:
VACUUM (ANALYZE) orders;
This is the fastest path back to a clean heap_fetch_rate after any large write operation.
Gotchas
EXPLAIN showing Index Only Scan does not mean you are getting IOS benefits. The plan label reflects the scan strategy chosen by the planner, not whether heap reads actually occurred. Always cross-reference with heap_fetches from pg_stat_user_indexes.
The docs do not mention this, but heap_blks_read from pg_stat_user_tables is not IOS-specific. Teams frequently mistake a low heap_blks_read for a healthy visibility map — they are measuring different things.
Default autovacuum is calibrated for safety, not VM coverage. The defaults optimize for not over-vacuuming. For index-only scan effectiveness on write-heavy tables, they are almost always too conservative.
Conclusion
The pattern here is straightforward: query heap_fetch_rate regularly on your high-read tables, tune autovacuum per table with ALTER TABLE ... SET (...), and run a manual VACUUM (ANALYZE) after bulk writes. These three habits are the difference between an index-only scan that works and one that silently costs you the heap reads you thought you eliminated.
Further reading:
Top comments (0)