Area 08 · Backend · Concept 09 of 20

Database Indexing

Indexes make lookups fast. Know which columns your queries need indexed.

01–10 · The basePhase 0 + 5 · Weeks 1–2, 18–20≈ 1 hour

The idea

Without an index, a query scans every row. An index is a sorted structure (usually a B-tree) on one or more columns that lets the database jump straight to matching rows, turning seconds into milliseconds on large tables.

Index columns you filter, join or sort on often: foreign keys, user_id, tenant_id, created_at. Composite indexes follow the order of columns in the query (tenant_id, created_at). Special index types exist for full-text (GIN), JSONB (GIN) and vectors (HNSW).

Indexes speed reads and slow writes, and take space. Use EXPLAIN ANALYZE to see whether a query uses an index, and add indexes for real slow queries rather than everywhere.

Why it matters

Slow database queries are a common hidden cause of high latency in AI apps, before any model call.

Words to know

  • B-treeThe default balanced tree index.
  • Seq scanReading every row of a table.

Try it without code

Read EXPLAIN

  1. In psql, run EXPLAIN ANALYZE on a query filtering by a non-indexed column.
  2. Create an index on it and run again.
  3. Compare plan and time.

Use it in code

Indexes for a chat history table.

sql
CREATE TABLE messages (
  id bigserial PRIMARY KEY,
  chat_id uuid NOT NULL,
  user_id uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  content text NOT NULL
);
-- "load the last 20 messages of a chat"
CREATE INDEX messages_chat_time ON messages (chat_id, created_at DESC);
-- "all messages by a user this month"
CREATE INDEX messages_user_time ON messages (user_id, created_at);

EXPLAIN ANALYZE
SELECT content FROM messages WHERE chat_id = '00000000-0000-0000-0000-000000000001'
ORDER BY created_at DESC LIMIT 20;
PRACTICE · TICK IT AFTER THIS

Load 1 million fake messages, time your history query, add the composite index and time it again.

Common mistakes

  • Indexing every column.
  • Composite index with columns in the wrong order for your query.

Go deeper