A Practical Indexing Strategy: Choosing, Ordering, and Pruning Indexes

An index trades write cost and disk space for read speed. Choose well and a 2-second query drops to 2 milliseconds. Choose badly and every insert gets slower while the planner ignores the index. This article covers how to choose well, with PostgreSQL examples, though most of the concepts apply to any relational database.

What an index actually is

An index is a separate data structure that maps column values to row locations, kept sorted or hashed so lookups avoid scanning the whole table. Every INSERT, every DELETE, and every UPDATE to an indexed column also has to update each relevant index. Every index has an ongoing cost, paid on every write.

Index types and what they're for

B-tree (the default)

A balanced tree of sorted keys. It supports:

• Equality: =, IN
• Ranges: <, >, BETWEEN
• Sorting: ORDER BY can read the index in order and skip the sort step
• Prefix matches: LIKE 'abc%' (with the right collation or operator class)

If you're unsure which type to use, use a B-tree.

Hash

Supports only equality. It can be slightly smaller than a B-tree for long keys, but it can't handle ranges or ordering. Use it rarely, and only when you've measured an advantage.

GIN (Generalized Inverted Index)

Maps each element inside a value to the rows that contain it. It's the right choice for:

• JSONB containment: WHERE attributes @> '{"color": "red"}'
• Arrays: WHERE tags && ARRAY['go', 'sql']
• Full-text search: WHERE document @@ to_tsquery('index & strategy')
• Trigram similarity (with pg_trgm): WHERE name ILIKE '%phat%'

GIN indexes are fast to read and relatively slow to update, so watch write-heavy tables.

GiST and SP-GiST

Frameworks for more complex data: geometric shapes, ranges (tstzrange overlap), nearest-neighbor searches, and IP ranges. Use them when you query "overlaps" or "closest to" rather than "equals".

BRIN (Block Range Index)

Stores only the min/max value for each range of physical table blocks, so it's tiny. It works well for huge, append-only tables where the column follows insertion order, such as timestamps in an events log. A BRIN index on a 500 GB table can be a few megabytes.

Composite indexes: column order matters

An index on (a, b, c) is sorted by a, then by b within each a, then by c. Think of a phone book sorted by last name, then first name. That ordering determines which queries it can serve:

Ordering rules

1. Equality columns first, range columns last. For WHERE tenant_id = ? AND created_at > ?, index (tenant_id, created_at). The reverse order forces the database to walk every tenant's rows within the time range.
2. Match the sort order. For WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20, index (customer_id, created_at). The database jumps to that customer, reads 20 entries in order, and stops.
3. Think in query shapes, not columns. Design indexes around your top queries, not by indexing each column separately.

Why not index every column separately?

Separate single-column indexes on a and b can be combined (a bitmap AND in Postgres), but that's usually much slower than one composite index designed for the query. It also doubles the write overhead.

Covering indexes and index-only scans

If an index contains every column a query needs, the database can answer from the index alone without visiting the table. That's an index-only scan:

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

-- Served entirely from the index:
SELECT created_at, status, total_cents
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

INCLUDE adds payload columns to the leaf pages without making them part of the sort key. That keeps the tree compact while still covering the query. In PostgreSQL, index-only scans also depend on the visibility map being up to date, which is one more reason to keep vacuum healthy.

Partial indexes: index only what you query

If queries always target a small subset of rows, index only that subset:

-- 98% of orders are 'completed'; the app only queries the active ones
CREATE INDEX orders_active_idx
    ON orders (created_at)
    WHERE status IN ('pending', 'processing');

The index is a fraction of the size, stays in memory, and costs nothing when completed orders are written. Partial unique indexes can also enforce rules like "only one active subscription per user":

CREATE UNIQUE INDEX one_active_sub
    ON subscriptions (user_id)
    WHERE cancelled_at IS NULL;

Expression indexes

When queries filter on a computed value, index that expression:

CREATE INDEX users_email_ci_idx ON users (LOWER(email));
-- matches: WHERE LOWER(email) = LOWER($1)

The query must use exactly the same expression for the planner to match it.

Selectivity: when an index won't help

An index pays off when it narrows the result to a small fraction of the table. A standalone index on a boolean is_active column, where 90% of rows are true, is nearly useless for finding active rows. Reading the whole table sequentially is cheaper than millions of random index lookups. The planner knows this and will ignore the index, but you still pay to maintain it.

Low-selectivity columns belong in composite indexes (after a selective leading column) or in a partial index predicate.

Find indexes that only cost you

Indexes accumulate. Someone adds one to fix an incident and nobody removes it. Audit them regularly:

-- Indexes never used since statistics were last reset
SELECT schemaname, relname AS table, indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Also look for:

• Duplicates: two indexes with identical definitions.
• Redundant prefixes: an index on (a) is usually redundant if (a, b) exists, because the composite already serves a = ? lookups.
• Bloat: indexes on heavily updated tables can grow far beyond their live data. REINDEX CONCURRENTLY rebuilds them without blocking writes.

Before dropping an index, check that statistics cover a representative period, including month-end jobs, and that the index doesn't enforce uniqueness.

Creating indexes safely in production

A plain CREATE INDEX blocks writes to the table while it builds. On a busy table, that means downtime. Use:

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

It takes longer and can't run inside a transaction, but it keeps the table writable. If it fails, it leaves an INVALID index behind. Drop it and try again.

Summary

• Default to B-tree. Use GIN for JSONB, arrays, and text search, and BRIN for huge append-only tables.
• Design composite indexes around query shapes: equality first, range last, matching the sort order.
• Use INCLUDE for index-only scans and partial indexes for hot subsets.
• Don't index low-selectivity columns on their own.
• Audit regularly and drop unused, duplicate, and redundant indexes.
• Build indexes CONCURRENTLY on live systems.

A small set of well-designed indexes almost always outperforms a large pile of speculative ones.