Database indexing

An index is a secondary data structure that lets the database find rows without reading the whole table. It trades write throughput and disk space for read latency — which makes indexing a design decision about access patterns, not a switch you flip when a query gets slow.

How a B-tree index works

Most relational indexes are B-trees: a balanced structure where every leaf sits at the same depth and each node holds a sorted run of keys. A lookup walks from the root to a leaf, comparing keys at each level, so cost grows with the logarithm of table size rather than linearly. Because leaves are linked in key order, the same structure also serves range scans and ordered reads without a sort step.

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

-- serves: WHERE customer_id = $1 ORDER BY created_at DESC
-- does not serve: WHERE created_at > $1  (leading column missing)

Column order in composite indexes

A composite index is only usable from its leading column inward. An index on(customer_id, created_at) supports predicates on customer_id alone or on both columns together, but not on created_at in isolation. Order equality predicates first, range predicates last.

Index types and when they apply

What indexes cost

Every write must maintain every applicable index, so an over-indexed table slows inserts, updates and vacuum work. Indexes also compete for cache: pages held for a rarely used index are pages not held for the heap. Audit usage statistics periodically and drop what the planner never picks.