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.
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
- In psql, run EXPLAIN ANALYZE on a query filtering by a non-indexed column.
- Create an index on it and run again.
- Compare plan and time.
Use it in code
Indexes for a chat history table.
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;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.