An index normally helps a database find rows. A covering index can go further: it contains all columns needed by a query, allowing the database engine to answer some reads without fetching every matching row from the table.

That can reduce random I/O for read-heavy workloads, but it also makes indexes larger and writes more expensive. Covering is a workload-specific optimization, not a reason to copy every selected column into every index.

This article uses PostgreSQL terminology and syntax. PostgreSQL supports INCLUDE columns in B-tree indexes in PostgreSQL 11 and newer.

Start from the query

Consider a common query:

SELECT created_at, status, total_cents
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

A useful search index might be:

CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);

This gives the planner an efficient path to rows for one customer in the desired order.

But status and total_cents are not in the index. The executor may still need to visit the table heap for those columns.

Add payload columns with INCLUDE

A covering version can be written as:

CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC)
INCLUDE (status, total_cents);

customer_id and created_at remain key columns used for searching and ordering. status and total_cents are payload columns stored with index entries.

The distinction matters: included columns do not become part of the B-tree search key.

Covering does not guarantee an index-only scan

PostgreSQL’s planner can choose an index-only scan when the query needs only columns available from the index.

However, PostgreSQL also needs visibility information to determine whether each tuple is visible to the current transaction. Visibility is tracked at heap-page granularity in the visibility map.

If a heap page is not marked all-visible, PostgreSQL may still visit the heap even during an index-only scan.

A covering index therefore creates the possibility of avoiding heap access. It does not guarantee zero table reads for every execution.

Verify with EXPLAIN

Use execution plans rather than assumptions:

EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, status, total_cents
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Look for an Index Only Scan node and inspect Heap Fetches.

A plan with many heap fetches may still be useful, but it tells you the workload is not receiving the full expected benefit.

Always test on representative data volume and distribution. A planner decision on a tiny development table says little about production.

Keep filter and order columns in the key

Suppose the query changes to:

SELECT created_at, total_cents
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Putting status only in INCLUDE makes it available to return, but it does not make it a normal index search key.

If filtering by status is common and selective enough, a different key order may be justified:

CREATE INDEX orders_customer_status_created_idx
ON orders (customer_id, status, created_at DESC)
INCLUDE (total_cents);

Index design begins with predicates and ordering. Covering columns come after that.

Wider indexes have real costs

Every included column consumes storage in the index.

Larger indexes can:

  • reduce the number of entries that fit in cache;
  • increase write amplification;
  • make inserts and updates more expensive;
  • increase backup and replication volume;
  • take longer to build and maintain.

Wide or frequently changing payload columns are especially expensive.

Do not add large text, JSON, or binary values merely to make a query technically covered.

Updates can reduce the benefit

PostgreSQL can use HOT updates in some cases to avoid creating new index entries when indexed columns do not change. Adding more columns to an index can make more updates touch indexed data and reduce opportunities for that optimization.

If status changes frequently, including it has a different write cost than including an immutable created_at value.

Measure write behavior as well as read latency.

Partial covering indexes can target a workload

Sometimes only a subset of rows matters:

CREATE INDEX open_orders_customer_idx
ON orders (customer_id, created_at DESC)
INCLUDE (total_cents)
WHERE status = 'open';

For queries that explicitly target open orders, this can be much smaller than indexing the full table.

The query predicate must be compatible with the partial-index predicate for PostgreSQL to use it.

Avoid duplicate indexes

Before adding a covering index, inspect existing indexes. An application can easily accumulate several similar definitions that all consume write capacity.

Some may be redundant. Consolidation should be based on actual query patterns and planner usage, not just similar-looking names.

Common mistakes

Treating selected columns as key columns

Columns needed only in the result often belong in INCLUDE, not in the ordered search key.

Adding every output column

Cover only stable, commonly requested, reasonably sized payload fields.

Assuming Index Only Scan means no heap access

Check Heap Fetches in an analyzed plan.

Optimizing one query without measuring writes

Indexes affect insert, update, delete, vacuum, replication, and storage behavior.

Ignoring query-shape changes

A covering index is useful only while it matches real predicates, ordering, and projections.

A practical decision process

For a high-value query:

  1. verify that filtering and ordering have an appropriate index key;
  2. identify table columns fetched only for output;
  3. consider small, stable payload columns for INCLUDE;
  4. run EXPLAIN (ANALYZE, BUFFERS) on representative data;
  5. compare read gains with index size and write overhead;
  6. remove redundant indexes if the new design supersedes them.

Covering indexes are most valuable when they reduce table access for frequent, selective reads without turning every write into unnecessary index maintenance.

Database PostgreSQL Indexes Performance