Index Design

An index is a copy of part of your data in a different order. It makes matching reads faster and every write slower, and it consumes memory that would otherwise cache your table.

Index for access patterns, not for columns

Start from the queries. For each frequent query, the index needs to support, in this order:

1. Equality predicates — columns compared with =.
2. Range predicates — >, <, BETWEEN.
3. Sort order — the ORDER BY columns.

That order is also the column order in a composite index, and it is the rule that decides whether an index gets used at all.

-- Query: most recent orders for one customer, in a status.
SELECT * FROM orders
WHERE customer_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 20;
 
-- Equality columns first, then the sort column.
CREATE INDEX orders_customer_status_created_idx
    ON orders (customer_id, status, created_at DESC);

With that index the engine seeks directly to the matching rows and reads them already sorted — no sort step, and the LIMIT stops early.

The leftmost-prefix rule

A composite index on (a, b, c) can serve queries filtering on a, on a, b, or on a, b, c. It cannot efficiently serve a query filtering only on b or only on c.

This means one well-ordered composite index frequently replaces three single-column ones — fewer indexes, less write amplification, less memory.

Covering indexes

If an index contains every column a query needs, the engine never reads the table. In PostgreSQL this is an index-only scan, in MySQL a covering index, in MongoDB a covered query.

-- PostgreSQL: INCLUDE adds payload columns without affecting the key order.
CREATE INDEX orders_customer_created_idx
    ON orders (customer_id, created_at DESC)
    INCLUDE (total_cents, status);

The trade is size: a covering index is larger, so it evicts more useful pages from cache. Use it for hot queries, not everywhere.

Partial and filtered indexes

Index only the rows that are queried:

-- Only pending orders are ever polled; the other 99% need not be indexed.
CREATE INDEX orders_pending_idx ON orders (created_at)
    WHERE status = 'pending';

A partial index on a small subset of a huge table is often a thousand times smaller than the full index, which means it stays in cache and costs almost nothing to maintain.

The cost of every index

• Writes. Each INSERT updates every index. Each UPDATE updates the indexes whose columns changed — and in PostgreSQL, unless the update qualifies as HOT, every index on the table.
• Memory. Indexes compete with table data for cache.
• Maintenance. Indexes are vacuumed, compacted and rebuilt with the table.
• Planning. More indexes mean more plan candidates to consider.

Reviewing indexes

Indexes accumulate. Review them on a schedule using the engine's own usage statistics — pg_stat_user_indexes in PostgreSQL, $indexStats in MongoDB — and drop what is unused. See Index Selection.