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:
- Add indexes to support real, measured slow queries — not hypothetical ones.
- Periodically drop indexes that nothing uses (Postgres tracks index usage stats).
- 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.
Originally published on the Doktouri Agency blog. We build web, mobile, SaaS, and AI products — let's talk.
Top comments (0)