Database indexes explained without the jargon

If a developer has ever told you a slow page was “a missing index”, and you nodded without knowing what that meant, this is for you. It is worth understanding at a business level, because indexes explain most sudden performance collapses and most of them are cheap to fix.

What an index actually is

Think of a physical filing cabinet with 50,000 customer records in the order they arrived. Someone asks for the file for “Henderson”. With no other information, you check every folder until you find it.

An index is a separate, alphabetically sorted list of names with the drawer number beside each one. Now finding Henderson takes seconds. The records themselves have not moved; you have added a lookup.

A database index is exactly that: a second structure that lets the database jump to matching rows instead of examining every one.

Why things get slow suddenly rather than gradually

This is the part that surprises people. Without an index, the database reads every row. At 10,000 rows that is fast enough that nobody notices. At 500,000 it is slow but survivable. At 5 million the page times out.

Nothing changed in your software. The table simply grew past the point where reading all of it was affordable. That is why performance problems tend to appear as a cliff rather than a slope, and why the answer is almost never “buy a bigger server”.

Why not index everything?

Because indexes are not free. Every index has to be updated on every write. Add ten indexes to a table and every insert now does eleven pieces of work instead of one.

The classic mistake, after learning what indexes do, is adding them everywhere. Reads get fast, writes get slow, and the system feels worse overall. An index that no query uses is pure cost.

What to ask your developer

“Which query is actually slow?” If nobody can name it, the work is guesswork. Every serious database can rank queries by total time. In PostgreSQL that is pg_stat_statements. Turn it on before spending anything.

“What does the query plan say?” EXPLAIN ANALYZE shows whether the database is scanning a whole table. A sequential scan on a large table is usually your answer.

“Did the index get used?” An index that gets created and never used is write-overhead with no benefit. Measure after, not just before.

“Is it one query per row?” The other common cause. Fetching a list of 200 customers, then fetching each customer’s orders individually, is 201 round trips where 2 would do. No index fixes that; the code has to change.

Roughly what to expect

Situation	Typical fix	Effort
One page slow, rest fine	A single index, or batching a query loop	Hours
Slow at the same time daily	A scheduled job colliding with live traffic	Hours to days
Gradual then sudden collapse	Missing index on a grown table	Hours
Writes slow, reads fine	Too many indexes, or a trigger	Days
Nobody knows what is slow	Add measurement first	Days

Most of these are hours of work, which is worth knowing before agreeing to a migration or a bigger server.

The order to try things

Measure, read the query plan, add the index, re-measure, fix any query-per-row pattern, cache what does not change, and only then consider hardware. Scaling the server to hide a missing index means paying that premium every month, forever.

More on the symptoms and what they mean in five signs your database is the bottleneck.

If your database has quietly become the thing everyone works around, describe what is happening.

Further reading

• PostgreSQL: index types and when to use them
• PostgreSQL: pg_stat_statements