Posted inDatabase

PostgreSQL Performance: Speed Up Queries with Covering Indexes

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The Hidden Cost of the Heap

Six months ago, my team hit a scaling wall. Our permission-checking microservice was struggling under heavy load. Even with a standard B-tree index on user_id, database CPU usage hovered at 80%, and I/O wait times were spiking. The query was basic: SELECT status FROM user_permissions WHERE user_id = $1;.

PostgreSQL uses a heap-based storage system. Unlike MySQL’s InnoDB, which can store row data directly within a primary key index, Postgres stores data in a separate area called the “Heap.” When you run a query, the database finds the entry in the index and then performs an extra jump to the heap to fetch the actual column values. This jump is a random I/O operation. At 10,000 queries per second, these tiny jumps crippled our throughput.

We solved this by implementing Covering Indexes. This technique eliminated the heap access entirely, dropping our average query latency from 12ms to under 0.5ms. Here is how you can do the same.

Implementing a Covering Index

An Index-Only Scan occurs when the index itself contains every piece of data the query needs. Since PostgreSQL 11, the INCLUDE clause makes this straightforward.

1. The Setup

Consider a table tracking millions of user actions:

CREATE TABLE user_activity (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    activity_type TEXT,
    created_at TIMESTAMP DEFAULT now()
);

2. The Standard Index

A typical index on user_id looks like this:

CREATE INDEX idx_user_id ON user_activity(user_id);

EXPLAIN ANALYZE 
SELECT created_at FROM user_activity WHERE user_id = 500;

Check your execution plan. You will likely see an Index Scan. While fast, the database still visited the heap to grab the created_at timestamp.

3. The Covering Index

We can “cover” the query by attaching the payload column to the index:

CREATE INDEX idx_user_id_covering 
ON user_activity(user_id) 
INCLUDE (created_at);

Run EXPLAIN ANALYZE again. The plan should now show Index Only Scan. The database retrieved the timestamp directly from the index file, bypassing the table data files completely.

The Role of the Visibility Map

Why doesn’t every index-only scan work perfectly? The answer lies in row visibility. PostgreSQL indexes do not store transaction information. The database must usually check the heap to ensure a row is visible to your current transaction and not a stale version pending deletion.

To bypass this check, Postgres uses a Visibility Map. This small bitset tracks data pages where all rows are known to be visible to all active transactions. If the map confirms a page is “clean,” the Index-Only Scan succeeds without a heap visit. However, if the page was recently updated, the database must still check the heap. This makes VACUUM performance critical for maintaining high-speed indexes.

INCLUDE vs. Multi-column Indexes

You might wonder why we don’t just use a standard composite index like (user_id, created_at). There are two main reasons:

  • Purpose: Columns in the main part of the index are used for filtering and sorting. Columns in the INCLUDE clause are just “payload.” They sit in the leaf nodes but don’t affect the B-tree’s search logic.
  • Maintenance: Included columns don’t require the same overhead during index rebalancing. This keeps the index slightly more efficient for writes while clearly signaling to other developers which columns are there for performance rather than logic.

Advanced Strategy: Partial Covering Indexes

Combining covering indexes with partial logic is incredibly effective for high-volume systems. This is ideal for “Active” data patterns where you only care about a subset of rows.

If you frequently query the total_amount of “Pending” orders, don’t index the whole table. Use a targeted index:

CREATE INDEX idx_pending_orders_amount 
ON orders(customer_id) 
INCLUDE (total_amount) 
WHERE status = 'PENDING';

This index remains small and fast. It provides near-instant results for your dashboard without bloating your storage or slowing down inserts for completed orders.

Production Lessons Learned

Moving our permission system to this model taught us several practical lessons about database maintenance.

1. Watch the Vacuum

Index-Only Scans live and die by the Visibility Map. If your table handles heavy UPDATE traffic and autovacuum is too slow, your performance will degrade. Monitor the “Heap Fetches” counter in your EXPLAIN plans. If it’s high, your Visibility Map is stale, and you’re losing the benefits of the covering index.

2. Avoid Index Bloat

Every extra column in an INCLUDE clause grows the index size. In one of our tests, adding a large VARCHAR column increased index size by 60%. Larger indexes consume more shared_buffers (RAM) and slow down backups. Only include columns that appear in your most frequent, latency-sensitive queries.

3. Measure Success

Use this query to verify if your indexes are actually performing Index-Only Scans in the wild:

SELECT 
    schemaname, relname, indexrelname, 
    idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan > 0 
ORDER BY idx_scan DESC;

A healthy covering index will show a high idx_tup_read count but a very low idx_tup_fetch count. This indicates the data came from the index, not the table.

4. Keep it Slim

Never include JSONB or large TEXT fields in a covering index. Indexes should be compact enough to stay in memory. If an index becomes too large to fit in the cache, you’ll trade one I/O problem for another. Stick to small, fixed-width types like integers, timestamps, or short enums.

Think of indexes as more than just a search tool. They are a strategic way to store data for your most critical query paths. By cutting out the heap access, you can scale your PostgreSQL instance much further on the same hardware.

Share:
Tags: