PostgreSQL Index-Only Scans and Visibility Maps: The Query Optimization That Most Backends Leave on the Table

PostgreSQL's visibility map controls whether an index-only scan can avoid fetching data from the heap. A covering index containing all columns a query needs should, in theory, allow the database to skip the heap entirely. However, in high-write mobile backends, this promise is often not fulfilled due to the visibility map being silently killed by heap fetches.

The visibility map is a compact one-bit-per-page structure where a set bit indicates all tuples on that heap page are visible to all current and future transactions. When the visibility map bit is set, Postgres can perform an index-only scan without touching the heap.

During write operations, such as updates or deletes on a heap page, the visibility map bit is cleared. For index-only scans to be effective, this bit needs to be set again. This is where autovacuum comes into play. Autovacuum automatically runs VACUUM to reset visibility map bits. In mobile backends, however, the write throughput is typically high, such as 100 rows per second or more.

This rapid write activity can lead to autovacuum failing to keep up with the clearing of visibility map bits, resulting in thousands of unnecessary heap fetches. Consequently, an index-only scan that shows "Index Only Scan" in EXPLAIN output may still perform thousands of heap fetches, as indicated by the "Heap Fetches" metric in EXPLAIN ANALYZE.

This performance degradation can go unnoticed if one only looks at the plan label without checking the actual heap fetches.

To address this issue, one must first read EXPLAIN ANALYZE correctly, looking not just at the plan node type but also at the "Heap Fetches" and "Buffers" metrics. High heap fetch rates indicate that the visibility map bits are not set as they should be. For mobile backends, tuning autovacuum per table is crucial. Default autovacuum scales may not be sufficient.

For high-write tables, it is recommended to set `autovacuum_vacuum_scale_factor` to a very low value (e.g., 0.01), allowing autovacuum to run more frequently. Additionally, increasing `autovacuum_vacuum_cost_delay` and `autovacuum_vacuum_cost_limit` provides more I/O budget for heap cleanup. After making these adjustments, manual VACUUM can be run after batch loads to ensure that the visibility map is updated promptly.

Monitoring the ratio of `vm_visible_pages / heap_pages` can provide early warning signs of index-only scan degradation. A dropping ratio before the latency graphs catch up signals that the performance bottleneck is about to manifest. By paying attention to the visibility map and autovacuum configuration, developers can ensure that their covering indexes truly deliver on their promise, avoiding the pitfall of index-only scans that are misled by stale visibility map bits.