Five signs your database is the bottleneck

When software gets slow, the usual reflex is to buy a bigger server. Sometimes that works. More often it buys six months and the same conversation again, because the problem was never capacity. Here is how to tell the difference.

1. It is slow at the same time every day

If things crawl every morning at nine, or every Monday, that is contention, not capacity. Many users hitting the same rows, or a scheduled job competing with live traffic. A bigger server does not fix a lock that one query is holding.

Look at what runs on that schedule first. A nightly report that overruns into business hours is a classic.

2. One page is slow and the rest are fine

A general capacity problem slows everything down. When a single page is slow, the cause is almost always one query on that page: a missing index, or a loop issuing one query per row instead of one query for all rows.

That second pattern is the most common performance bug in web applications. Fetch a list of 200 customers, then fetch each customer’s orders individually, and you have made 201 round trips where 2 would do.

3. It got slower gradually, then fell off a cliff

This is the signature of a query scanning the whole table. At 10,000 rows a missing index is invisible. At 500,000 it is survivable. At 5 million the page times out. Nothing changed in your code; the table simply grew past the point where scanning it was affordable.

The fix is usually a single index and takes minutes.

4. Writes are slow, reads are fine

Usually too many indexes, not too few. Every index has to be updated on every write. It is entirely possible to over-index a table until inserts crawl.

It can also be triggers. Triggers are invisible in application code and easy to forget, and one that fires a cascade on every insert will quietly halve your write throughput.

5. Nobody knows which query is slow

This is the real problem, and it underlies the other four. If you cannot answer “which query is slowest,” you are guessing, and guessing is what leads to buying a bigger server.

Every serious database can tell you. In PostgreSQL, pg_stat_statements will rank queries by total time. Turn it on before you spend a dollar on hardware.

The order to try things

Cheapest and least risky first:

1. Find the slow query. Measure. Do not guess.
2. Read the query plan. EXPLAIN ANALYZE tells you whether it is scanning a whole table. A sequential scan on a large table is your answer.
3. Add the index. Usually one index on the column being filtered or joined. Measure again to confirm, because an index that does not get used is pure write overhead.
4. Fix the query-per-row pattern. Batch the fetches.
5. Cache what does not change. If a query is expensive and the data is stale by an hour anyway, cache it.
6. Then consider a bigger server. Genuinely last. Scaling hardware to hide a missing index means paying that premium every month, forever.

When it is worth calling someone

If you have measured, found the query, and cannot see why it is slow, that is a reasonable point to bring someone in. It is also worth it before a migration, because the cost of getting a data migration wrong is measured in lost records rather than lost seconds.

If your database has quietly become the thing everyone works around, describe what is happening and you will get a straight assessment of whether it is a ten-minute fix or a real project.