Turn a slow database into a specific problem

The problem: a slow screen does not identify the database fix

“The database is slow” describes a symptom. It does not yet tell you what to change.

An order screen might be waiting on a query, a lock, a remote service, or repeated application calls that each retrieve a small amount of data. Begin with the operation people experience and trace where its time goes.

A practical approach

Reproduce a meaningful example

Capture a slow request with enough context to compare it later: the operation, relevant input size, timing, and what else was happening. Avoid copying sensitive customer data into an unrestricted troubleshooting document.

Check whether the issue affects every request or particular cases. A search that performs well on a small account may behave very differently on an account with years of history.

Measure more than the average. Occasional long waits can disrupt a workflow even when most requests are fast.

Inspect the work being done

Look at the queries involved, how often they run, and how many records they examine or return. An application that loads a related record separately for every row can make a reasonable query expensive through repetition.

For PostgreSQL, EXPLAIN helps inspect the planner's chosen approach. EXPLAIN ANALYZE actually executes the statement, so use it with appropriate care, especially for statements that change data or place substantial load on the system.

Interpret the plan in the context of the schema, data distribution, and workload. A particular scan type is not automatically proof of a problem.

Change one thing you can evaluate

Possible changes include narrowing the requested data, reducing repeated queries, adding an appropriate index, or adjusting the application workflow. An index also has storage and write costs, so avoid adding them indiscriminately.

Test against representative data. Compare the result and the execution behavior before and after. Verify that the change has not made another important operation worse.

If contention is involved, investigate transaction duration and competing work rather than assuming more CPU will remove the wait.

Keep the evidence

Record the original symptom, the change, and the observed result. Put monitoring around the operation that matters to the business.

Capacity increases can be the right answer. They are easier to justify when you know which resource is constrained and why. A database review should leave the problem more specific than it found it.

The result: a measured improvement to the operation users experience

Keep a before-and-after record for the same representative request: response time, query count, relevant resource use, and correctness of the result. Check another important operation for regressions before accepting the change. If the bottleneck lies outside the database, assign the next investigation there. Start with one reproducibly slow user task so each change has a meaningful comparison.