Composite Indexes and Access Patterns

An index may look completely reasonable when inspecting a database schema and yet remain ineffective for the queries that matter most. Relational engines do not execute "important columns"—they traverse access paths defined by filter conditions, sort orders, range boundaries, and selectivity.

Consider a common query pattern:

WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 50

Creating three isolated single-column indexes on tenant_id, status, and created_at does not mean PostgreSQL can efficiently combine them into an optimal execution plan. At best, the planner might attempt a bitmap index scan, but it will still incur heap lookups and an explicit sort operation before yielding results.

Anatomy of a Composite Index

A composite index structured as (tenant_id, status, created_at DESC) directly mirrors this query pattern:

1. It immediately narrows the search space using equality predicates on tenant_id and status.
2. It traverses the matching index entries already in the required sort order (created_at DESC).
3. It terminates execution as soon as it retrieves the first 50 matching rows, completely eliminating an expensive in-memory or on-disk sort.

The true metric of an index is not merely its presence, but the actual work it eliminates: buffer pages read, discarded rows, explicit sorts avoided, random heap fetches prevented, and elapsed time to deliver the first batch of results.

Column Ordering and Traversal Mechanics

Column ordering within a composite index is not an arbitrary convention; it dictates how the B-tree can be traversed. Equality filters must generally precede range conditions and sort clauses. As soon as a range predicate (such as >, <, or BETWEEN) is evaluated, subsequent columns in the index cannot be used for direct equality lookups and must be scanned sequentially.

For this reason, indexing must always begin with the access pattern:

• Which filter eliminates the largest volume of unneeded rows?
• Which sort order must be preserved without invoking an external sort?
• Where do range predicates appear in the query?
• How many rows are expected to return, and what is the strict latency budget in production?

Balancing Read Acceleration with Write Overhead

An effective index is not one that merely mirrors the schema; it is one that drastically cuts the computational cost of critical queries. Every index introduces continuous overhead during INSERT, UPDATE, and DELETE operations, consumes storage, and adds cache pressure. Optimal design requires balancing query acceleration against the write amplification imposed on the storage engine.