🚀Indexes basics

Indexes trade write heat for read speed

Every secondary index is another structure to maintain on INSERT/UPDATE/DELETE. The art is indexing the predicates and sort orders you truly query, everything else is drag on ingestion pipelines.

SQL

B-tree: default workhorse

CREATE INDEX idx_runs_instrument_started
ON sequencing_runs (instrument_id, started_at DESC);

✓Illustrative result set

result
CREATE INDEX

SQL

Query shaped to use the index

SELECT run_id, started_at
FROM sequencing_runs
WHERE instrument_id = 'SEQ-01'
  AND started_at >= TIMESTAMP '2026-04-01'
ORDER BY started_at DESC
LIMIT 20;

✓Illustrative result set

run_id	started_at
R-991	2026-04-11 18:22
R-990	2026-04-09 07:01

🧰Pick the family for the access pattern

Wrong index type = expensive no-op.

Index type	Best for
B-tree	Equality, ranges, ORDER BY on leading keys, OLTP bread and butter.
Hash (where available)	Point equality only, no range scans, often not worth the specialization.
GiN / GiST (PostgreSQL)	Full text, JSON containment, spatial, different cost rules entirely.

Index design FAQ

B-tree rules that prevent “we added an index but the plan is still Seq Scan.”

Why does my multi-column index ignore my WHERE clause?

Composite B-trees are ordered by leading columns first. If you filter on column B but not column A of (A, B), the optimizer often cannot narrow the tree efficiently. Match column order to the most selective predicates you always apply.

When does a partial or expression index beat a plain B-tree?

When a large table is queried on a subset (e.g. WHERE status = 'OPEN') or on a transform you cannot rewrite (lower(email)): smaller indexes mean fewer pages touched and fresher statistics on the hot slice.

How many indexes are too many?

There is no magic number, track write latency, autovacuum / rebuild time, and unused-index reports. Overlapping indexes on the same leading prefix usually mean merge or drop candidates after proving plans with production-like skew.

Index sprawl on intuition

Indexing low-cardinality columns alone with almost no selectivity gain.
Assuming indexes help ORDER BY when the WHERE clause does not match a leading prefix.
Ignoring write amplification on tables that churn every few seconds.

Measure, then add

Compare p95 before/after on production-like row counts and parameter skew.
Learn covering vs heap-fetch plans on your engine so you know what you bought.
Review unused-index reports quarterly; pair new indexes with a retirement owner.

Leading column discipline

Multi-column indexes are not symmetric, filters on trailing columns alone may skip the index. Model keys in the order of your most selective predicates.

Write amplification

Hot tables with many overlapping indexes show up as insert latency and bigger checkpoints. Periodically audit pg_stat_user_indexes (or equivalent) for zero-use indexes.

Covering indexes

If an index includes all columns a query touches, the engine can satisfy reads without touching the heap, huge for read-mostly dashboards. Pair with INCLUDE columns (SQL Server) or careful column ordering (PostgreSQL).

TL;DR

An index is a separate ordered structure (usually a B-tree) that speeds lookups, joins, and ORDER BY on chosen columns at the cost of extra storage and write amplification. The optimizer chooses seek (jump to a range) vs scan (walk the whole index) based on selectivity and statistics. Start indexes from real query predicates, do not index every column by default.