Database indexing explained

Almost every "the database is slow" complaint we investigate comes down to a missing or misused index. Indexing is the highest-leverage performance skill a developer can have, and yet it's often treated as arcane. It isn't. Once you understand what an index actually is and how to read a query plan, you can turn a ten-second query into a ten-millisecond one on purpose instead of by luck.

What an index actually is

Think of an index like the index at the back of a book. Without it, finding every mention of a topic means reading every page — a sequential scan. With it, you jump straight to the right pages. In a database, an index is a separate, sorted data structure that lets the engine find rows without examining the whole table.

Most indexes are B-trees, which keep values sorted so the database can binary-search them. That's why a B-tree index speeds up equality (=), ranges (<, >, BETWEEN), sorting, and prefix matches — but does nothing for a leading-wildcard LIKE '%foo'.

Index what you filter, join, and sort on

The rule of thumb: any column that regularly appears in a WHERE clause, a JOIN condition, or an ORDER BY is a candidate for an index. Foreign keys are almost always worth indexing, since you'll join on them constantly.

For queries that filter on multiple columns, a composite index on several columns together beats separate single-column indexes. But column order matters: an index on (tenant_id, created_at) helps queries filtering by tenant_id alone or by both — but not queries filtering by created_at alone. Put the most selective, most-often-filtered column first.

Learn to read EXPLAIN

You never have to guess whether an index is used. In PostgreSQL, run EXPLAIN ANALYZE on your query and read the plan:

• Seq Scan on a large table in a hot query is a red flag — the database is reading everything.
• Index Scan or Index Only Scan means your index is doing its job.
• The reported row estimates versus actual rows tell you whether the planner's statistics are accurate.

Make EXPLAIN ANALYZE a reflex before and after adding an index. It turns performance work from folklore into measurement.

Indexes aren't free

It's tempting to index everything, but each index has a cost. Indexes take disk space, and — more importantly — every INSERT, UPDATE, and DELETE has to update every affected index. Over-indexing a write-heavy table slows down exactly the operations you care about.

So index deliberately:

1. Add indexes to support real, measured slow queries — not hypothetical ones.
2. Periodically drop indexes that nothing uses (Postgres tracks index usage stats).
3. Watch for redundant indexes where a composite index already covers a single-column one.

Build the habit

The whole discipline fits in one loop: write the query, run EXPLAIN ANALYZE, add the index the plan is begging for, and measure again. Do that consistently and slow-database fire drills mostly disappear from your life. The teams that struggle aren't missing some secret — they've just never looked at a query plan.

If your app is crawling and you suspect the database, talk to us.