Indexing

Why it matters

Most "the database is slow" incidents are one missing index. A query that scans ten million rows to return three takes seconds and holds resources the whole time; the same query with an index takes a millisecond. Before replicas, caches, partitions or shards, indexing is the scaling step with the highest return, and reading a query plan is the skill that finds it.

The idea

An index is a separate structure, usually a B+ tree, that stores the values of one or more columns in sorted order with pointers to the rows. A lookup descends the tree in a few page reads instead of scanning the table. The database maintains the index on every insert, update and delete, which is where the cost lives.

What to index

Columns that appear in WHERE, JOIN ... ON and ORDER BY of queries that run often or must be fast. Foreign keys almost always. High selectivity matters: an index on status with three values barely narrows anything, while an index on email finds one row. The planner will ignore an index that does not narrow the result enough, and it will prefer a scan for queries that return a large fraction of the table.

Composite, covering and partial indexes

1. 1

   Composite indexes follow the leftmost prefix

   An index on (tenant_id, created_at) serves filters on tenant_id alone and on tenant_id plus created_at, and orders results by created_at within a tenant. It does not serve a filter on created_at alone. Put equality columns first, then the range or sort column.
2. 2

   Covering indexes answer from the index alone

   If the index includes every column the query reads (through INCLUDE or by being in the key), the database never visits the table. The list query that needs id, title and created_at can be served entirely from an index on (user_id, created_at) INCLUDE (title).
3. 3

   Partial indexes shrink the tree

   An index WHERE status = 'open' only contains open orders, which is what the operational queries look at. Smaller index, faster lookups, cheaper writes for closed orders.
4. 4

   Expression indexes match how you query

   If you filter on lower(email), index lower(email). An index on email will not be used.

Reading a plan

EXPLAIN ANALYZE shows what the planner chose and what it cost. The things to look for: a Seq Scan on a large table where you expected an index; estimated versus actual row counts that differ by orders of magnitude (stale statistics; run ANALYZE); a Sort step that an index could have avoided; and nested loops over large sets where a hash join was expected. The plan tells you which index to add far more reliably than guessing from the query text.

The cost of indexes

Every index is maintained on every write to its table: an insert into a table with eight indexes writes nine trees plus the log. Indexes consume disk and, more importantly, buffer cache memory that could hold table data. Unused indexes are pure cost; most databases track usage so you can find and drop them. The discipline is to index for the queries you actually run, review periodically, and resist "index every column just in case".

Trade-offs

Indexes trade write cost and space for read speed, and the trade is overwhelmingly worth it for read-heavy access paths and ruinous for write-heavy tables with many unused indexes. Composite indexes serve several query shapes at once if column order is chosen well; a wrong order serves none of them.

In an interview

Volunteer indexes with the schema, not after: "Orders are queried by customer and sorted by date, so the index is (customer_id, created_at DESC) and includes status so the list is covering. Open orders are a partial index for the operations dashboard." Then show the cost awareness: "The events table has one index because it is write-heavy."