PostgreSQL Index-Only Scans & Covering Indexes: Eliminating Heap Fetches on High-Throughput Read APIs

The Hidden Cost of Heap Fetches in Standard Index Scans

When database engineers optimize slow read queries, the standard prescription is to add a B-Tree index on the filtering columns (e.g., WHERE tenant_id = ? AND status = ?). While this converts a disastrous sequential scan into an index scan, high-throughput APIs often hit an unexpected performance ceiling. Under high concurrency, execution latency and disk I/O remain elevated.

The culprit is the Heap Fetch. In PostgreSQL's multi-version concurrency control (MVCC) architecture, a standard B-Tree index stores only the indexed key values and a physical pointer—a Tuple ID (TID) consisting of a block number and offset—pointing to the physical table heap page. Even if the index perfectly isolates the matching rows, PostgreSQL must still perform a secondary random disk read to the heap table page for two reasons:

1. Payload Column Retrieval: If the SELECT clause requests columns not present in the index (e.g., created_at, amount, email), the engine must visit the heap page to retrieve those attributes.
2. MVCC Visibility Verification: Traditional index entries do not store MVCC transaction metadata (xmin / xmax). The engine must verify that the tuple is visible to the active transaction snapshot.

For high-throughput read endpoints executing tens of thousands of queries per second, this dual-hop penalty—reading the index page, then reading the heap page—saturates storage controller IOPS and blows out cache lines. Achieving true zero-heap-fetch execution requires an Index-Only Scan.

Standard Index Scan (2 Disk Hops)
┌────────────────────────┐      Heap Pointer (TID)      ┌────────────────────────┐
│ B-Tree Index Page      │─────────────────────────────►│ Physical Heap Table    │
│ (tenant_id, status)    │                              │ Page (Payload + MVCC)  │
└────────────────────────┘                              └────────────────────────┘

Covering Index-Only Scan (1 Disk Hop, 0 Heap Visits)
┌───────────────────────────────────────────────┐
│ B-Tree Index Page (INCLUDE Clause)            │
│ Keys: (tenant_id, status)                     │───► Payload Returned Instantly!
│ Payload: (amount, created_at)                 │     (Visibility Map confirms all-visible)
└───────────────────────────────────────────────┘
  

Covering Indexes with the INCLUDE Clause

Historically, developers attempted to create index-only scans by creating wide composite indexes containing all required columns: CREATE INDEX idx ON orders (tenant_id, status, amount, created_at);. While functional, wide composite indexes introduce severe architectural drawbacks:

• B-Tree Tree Bloat: All columns in a standard composite index form part of the B-Tree search key hierarchy. This inflates internal index node sizes, reducing fan-out, deepening the tree height, and requiring more page reads per traversal.
• Unenforced Constraints: You cannot easily create unique constraints where only a subset of columns guarantees uniqueness while auxiliary columns are retrieved.
• Write Amplification: Every update to any column in the key forces an index entry recreation.

The Elegance of the INCLUDE Clause

Introduced in PostgreSQL 11+, the INCLUDE clause decouples the search key from the payload payload. The search key columns define the navigational structure of the B-Tree, while the included columns are appended exclusively to the leaf pages as non-searchable payload data:

-- High-Performance Covering Index
CREATE INDEX idx_orders_covering_api 
ON orders (tenant_id, status) 
INCLUDE (amount, created_at, reference_code);

This design delivers three distinct advantages:

1. Slim Upper B-Tree Nodes: Internal navigational pages store only (tenant_id, status), keeping index branches cache-resident in shared buffers.
2. Unique Constraints with Payloads: Enforce unique constraints on primary keys while allowing index-only reads of foreign keys: CREATE UNIQUE INDEX idx_user_api_key ON api_keys (key_hash) INCLUDE (user_id, rate_limit);.
3. Zero-Cost Payload Access: As soon as the index scan lands on a matching leaf tuple, all required columns are immediately available in memory without visiting the table heap.

The Visibility Map: The Prerequisite for Index-Only Scans

Even with a covering index, PostgreSQL will continue visiting the table heap unless the Visibility Map (VM) confirms that the corresponding heap page contains only tuples visible to all current and future transactions.

Each table in PostgreSQL has a Visibility Map bitmap tracking two bits per heap page:

• All-Visible: Every tuple on this page is older than the oldest running transaction. PostgreSQL can safely trust the index entry without verifying MVCC headers on the heap!
• All-Frozen: Every tuple has been frozen by autovacuum (preventing transaction ID wraparound).

If rows on a page are actively being inserted, updated, or deleted, the page's all-visible bit is cleared. A query using a covering index will fall back to an index scan with heap fetches. Maintaining true Index-Only Scans requires tuning autovacuum and table maintenance to keep visibility maps up to date.

-- Inspect Visibility Map and Heap Fetch Ratios
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT tenant_id, status, amount, created_at, reference_code
FROM orders
WHERE tenant_id = '550e8400-e29b-41d4-a716-446655440000' 
  AND status = 'completed';

-- Look for:
-- "Heap Fetches: 0"  <- The Holy Grail of Index-Only Scans
-- If Heap Fetches > 0, autovacuum has not processed recently modified heap pages

Django ORM Integration: Declarative Covering Indexes

In Django 3.2+, covering indexes are natively supported via the include argument in models.Index. This integrates cleanly alongside partial and expression indexes to slash storage footprints on skewed workloads:

# models.py
from django.db import models

class OrderLedger(models.Model):
    tenant_id = models.UUIDField(db_index=False)
    status = models.CharField(max_length=30, db_index=False)
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    reference_code = models.CharField(max_length=64)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        indexes = [
            # High-Throughput Covering Index for API Summaries
            models.Index(
                name='idx_order_covering_summary',
                fields=['tenant_id', 'status'],
                include=['amount', 'reference_code', 'created_at'],
                condition=models.Q(status__in=['completed', 'settled'])
            )
        ]

Autovacuum Tuning for Zero-Heap-Fetch Stability

To prevent heap fetches from degrading query latency over time, calibrate autovacuum specifically on high-velocity tables to vacuum newly updated pages aggressively, as outlined in our guide on query planner cost factor calibration:

-- Optimize Visibility Map refresh frequency for high-read table
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,  -- Trigger vacuum after 5% tuple updates (default is 20%)
    autovacuum_vacuum_threshold = 500,
    autovacuum_vacuum_cost_limit = 2000     -- Increase I/O budget for faster vacuum completion
);

Benchmark: Impact on p99 API Latency and Disk I/O

By eliminating heap fetches through covering indexes and maintaining clean visibility maps, database read throughput increases by over 15x while reducing buffer pool churn and storage controller pressure.