Database Index

A database index is an auxiliary structure that lets the engine find rows without scanning the whole table. It trades write cost and storage for faster reads.

data

Definition

A database index is a data structure, usually a B-tree or a variant, that maintains a sorted or hashed mapping from key values to row locations so the query planner can locate matching rows with fewer page reads than a full table scan.

Context and scope

Indexes serve filters, joins, ordering, and uniqueness constraints. Choosing them requires understanding query patterns, selectivity, composite column order, and the write amplification each additional index introduces.

Common misconceptions

Every column should be indexed to make all queries fast
An index always makes a query faster once it exists
An index on (a, b) is equally useful for filtering by b alone

Anti-patterns

Creating duplicate or overlapping indexes with the same leading columns
Wrapping indexed columns in functions inside WHERE clauses
Adding indexes without checking query plans and usage statistics

Related concepts

Related examples

Authoritative source

https://www.postgresql.org/docs/current/indexes.html

How it works

Most relational indexes are B-trees: sorted pages with root, branch, and leaf nodes that allow logarithmic lookups and ordered range scans. A composite index is ordered left to right, so (customer_id, created_at) serves filters on customer_id and sorting by created_at, but not filters on created_at alone. Covering indexes include extra columns so a query can be answered without visiting the table.

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

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC LIMIT 20;

When to use it

Index columns used in frequent, selective filters and joins, and columns used for ordering or uniqueness. Check plans with EXPLAIN and realistic data distributions. Compared with denormalization or caching, an index keeps one source of truth and improves the query itself, but it cannot fix a badly shaped query.

Trade-offs

Every index adds storage, write amplification, and maintenance cost. Low-selectivity indexes may lose to sequential scans. Unused indexes are pure overhead, and duplicates waste space. Functions on indexed columns, stale statistics, or type mismatches can prevent the planner from using a good index.

Concrete example

An orders page filters by customer and sorts by date. Without an index, the planner scans millions of rows and sorts them. With the composite index above, it reads about twenty leaf entries and stops, while insert latency rises slightly because each new order updates one more structure.

Indexing every column is not optimal, an existing index is not always chosen, and (a, b) is not a substitute for (b). Related concepts include Index Selectivity, N+1 Query Problem, Partition Pruning, and Write Amplification.