Before You Blame the Database, Measure It

“The database is slow” gets blamed for a lot of things. Sometimes that’s accurate, and sometimes the database is just where slowness from another part of the system shows up.

Before anyone starts pricing new hardware, a different database, or a big migration, I think it’s worth spending a day finding out where the time goes. That day is well spent even when the database turns out to be the problem.

Start from something a user notices

Pick one slow thing a person experiences, such as a page or a report, and measure where its time goes from end to end.

If your application already has tracing or request timing, use it. If not, adding basic timing around database calls is usually quick. The question is simple: of the four seconds that page takes, how many are spent waiting on queries?

Now and then the answer is “hardly any,” and the problem is in the application code, the network, or a slow call to another service. That’s a cheap thing to learn early.

Common causes

When the database is involved, the cause is often one of a few familiar patterns.

A page that loads a list and then runs a separate query for each row can make hundreds of round trips. Each query is fast, but together they add up. This is the N+1 problem, and ORMs make it easy to create without noticing.

Missing or unused indexes are another common cause. A query that filters or sorts on a column without a suitable index may read the whole table, which is fine at a thousand rows and miserable at ten million.

Some applications fetch more than they need, selecting every column or every row and filtering in application code afterward. And sometimes requests are simply waiting, either on locks held by long transactions or on a connection pool that’s too small for the traffic.

Look at the queries directly

Most databases will tell you how they plan to run a query. In PostgreSQL, you use EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT id, status, created_at
FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;

The output shows whether an index was used, how many rows each step touched, and where the time went. Watch for a sequential scan over a big table, or an estimated row count that’s far off from the actual one.

For the wider view, PostgreSQL’s pg_stat_statements extension collects statistics across all queries, so you can sort by total time. A query that takes 20 milliseconds but runs a million times a day can cost far more overall than a slow report someone runs once a week.

Other databases have their own equivalents. Whichever you use, look at where the total time goes as well as at the slowest single query.

Cheap fixes first

Roughly from least to most effort:

1. Fix N+1 patterns by loading related data in one query.
2. Add or adjust indexes for the queries that matter, then check the plan again.
3. Select only the columns and rows you need, and paginate long lists.
4. Shorten long transactions, and size the connection pool for your real workload.
5. Cache results that are expensive to compute and don’t change often.

Try these one at a time and measure after each. If you change three things at once, you won’t know which one helped.

When the design is the real issue

Sometimes the easy fixes run out. The data model doesn’t match how the application uses it, one table is doing the work of three, or the workload has outgrown the schema.

That’s when a redesign or migration is worth discussing, and the discussion goes better with measurements in hand, because you’ll know what’s slow and what a change would need to fix.