Indexing strategy

An index is a separate, sorted structure that lets the database find matching rows without reading the whole table. Most real database performance is won or lost on indexes. Picking the right columns in the right order matters more than most tuning. Each index also costs something on every write.

The big idea

Think of the index at the back of a book. You look up a topic, get a page number, and go straight there. You do not read every page.

The analogy breaks on writes. Every new page means a new entry in the back of the book. A database index is a second copy that must be updated on every insert, delete and change to an indexed column.

Walking through it

Three index shapes cover most needs. This Postgres DDL creates the first two for the order table.

-- Pattern 2: a customer's orders, newest first, with the fields the list shows
CREATE INDEX idx_orders_customer_recent
  ON orders (customer_id, created_at DESC)
  INCLUDE (status, total_cents);

-- Pattern 4: only about 1% of orders are pending, so index just those
CREATE INDEX idx_orders_pending
  ON orders (warehouse_id, created_at)
  WHERE status = 'PENDING_SHIPMENT';

Output: Postgres creates both indexes and returns no rows.

• Composite index. (customer_id, created_at) serves WHERE customer_id = ?, with or without the sort. It does not serve WHERE created_at > ? alone, because the leftmost column is missing. Put equality columns first, then the range or sort column.
• Covering index. INCLUDE adds payload columns, so the query never touches the table.
• Partial index. Indexes only the rows that match a condition. Ideal for a small, hot subset.

Now the cost. An orders table gets 200 inserts per second. It has a primary key plus 5 secondary indexes, so each insert does 6 B-tree insertions. That is 1,200 page changes per second, instead of 200. The write-ahead log grows from about 20 KB/s to about 120 KB/s for the indexes.

On a small table, that is noise. On a table with 20,000 writes per second, it is the difference between keeping up and falling behind.

Common mistakes

• Indexing every column. Writes slow down, and the planner may still pick the wrong index.
• Putting the range column first. (created_at, customer_id) cannot serve an equality on customer_id well.
• Indexing a column that changes often. In Postgres, an update to an indexed column cannot use a HOT update, so every index is changed.

Check yourself

1. Does an index on (customer_id, created_at) serve WHERE created_at > ? alone? No. The leftmost column is missing, so the index cannot be searched by date alone.
2. Why is indexing a column like last_seen_at expensive? It changes often, and an update to an indexed column cannot use a HOT update, so every index on the table must be updated.

Key takeaways

• An index lets the database jump to matching rows.
• Put equality columns first in a composite index, then the range or sort column.
• Covering and partial indexes are cheap wins for the right queries.
• Every index adds write cost, so remove the ones that are never used.

Further learning

Next, see how to model data when the store has no joins, in query-first modeling.

Use The Index, Luke explains indexing in depth, with examples across databases.

Use The Index, LukeSQL indexing and tuning tutorial for developers. No unnecessary database details—just what developers need to know. Covers all major SQL databases.use-the-index-luke.com