Most database performance problems are not mysterious. They are design decisions that stayed invisible while the system was small: an endpoint that loads far more data than its response needs, a useful index that was never added, a background job that competes with customer traffic, or an API contract that quietly turns one query into a hundred.

That is good news. It means the answer is rarely “hunt for a magical database setting.” The durable answer is to understand the workload, make data access intentional, and give the database a shape it can execute efficiently.

Start with the slow path, not the suspected bug

A slow page does not prove that the database is at fault. Application code, network calls, serialization, cache misses, and overloaded workers can all create the same symptom. Measure the full request, then identify the queries contributing meaningful time or volume.

For each important endpoint, ask a few direct questions: How many queries does it execute? How many rows does each query examine and return? How often is the endpoint called? Is latency stable, or does it degrade as a table grows? These questions turn a vague complaint into an engineering problem with boundaries.

Query logs and application-level timing are useful because they reveal patterns that a single manually run query may hide. A query taking 20 milliseconds once can still be expensive if it runs 50 times per request. Conversely, an occasional analytical query may be acceptable even when it is slower, provided it is isolated from interactive traffic.

Indexes are about access paths, not decoration

An index helps when it matches how the database filters, joins, or orders data. Adding indexes blindly can make writes more expensive and leave the real bottleneck untouched. Every insert, update, and delete may need to maintain each relevant index.

Consider an endpoint that lists recent paid orders for one account:

SELECT id, total, created_at
FROM orders
WHERE account_id = ?
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

A composite index beginning with account_id and status, followed by created_at, aligns with this access pattern. The exact best index depends on the database engine and workload, but the principle is stable: equality filters generally narrow the search before range conditions and ordering become useful.

Use the database’s query-plan tooling to verify assumptions. A plan that scans a large table, creates a temporary sort, or examines vastly more rows than it returns deserves attention. Do not stop at “the index exists.” Confirm that the optimizer can and does use it for the query you actually ship.

Make predicates index-friendly

Indexes can be defeated by otherwise innocent-looking expressions. Applying a function to an indexed column in a filter may prevent an efficient lookup. For example, filtering with a date-extraction function can force more work than filtering against a calculated date range.

-- Less friendly to a created_at index
WHERE DATE(created_at) = '2026-08-17'

-- Typically easier to use with an index
WHERE created_at >= '2026-08-17 00:00:00'
  AND created_at < '2026-08-18 00:00:00'

The same caution applies to leading wildcard searches, implicit type conversions, and broad OR conditions. These are not forbidden constructs; they are signals to inspect the plan and consider a different data-access strategy.

Eliminate accidental query multiplication

The classic N+1 query problem is still common because object-oriented code can make it look natural. Load 50 orders, then lazily load the customer for each order, and one endpoint can become 51 database round trips. Add related items or permissions and the count grows quickly.

Fix this deliberately. Fetch required relationships in batches, use a join when it produces the right result shape, or collect foreign keys and retrieve related records with one additional query. The goal is not always “one query.” The goal is a small, predictable number of queries that return only the data required by the response.

Be equally wary of the opposite extreme: one enormous join that repeats parent data for every child row, consumes memory, and becomes difficult to reason about. For a detailed API response, two or three focused queries can be clearer and cheaper than a deeply joined result set. Performance and maintainability often improve together when data boundaries are explicit.

Pagination is a database contract

Offset pagination is convenient:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 25 OFFSET 10000;

But large offsets can make the database walk past many rows before returning the page. They also behave poorly when new records arrive between requests. For frequently browsed, large datasets, cursor-based pagination is usually a better contract.

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 25;

The cursor should include a stable tie-breaker such as id. The corresponding index must support the chosen order. This is a small API design choice with a large operational effect: it avoids increasingly expensive deep pages and gives clients a more consistent traversal.

Separate transactional work from expensive work

Transactions protect correctness, but long transactions can hold locks and increase contention. Keep them focused: validate the necessary state, write the required records, and commit. Avoid performing slow HTTP calls, large file processing, or broad reporting queries while a transaction is open.

Background jobs help, but they are not automatically harmless. A worker that updates millions of rows in one transaction can compete with production requests just as effectively as a slow web endpoint. Process large maintenance tasks in bounded batches, make retries idempotent, and monitor the pressure they place on the same tables used by interactive traffic.

• Use a unique business key or recorded operation identifier when retries could create duplicate work.
• Commit in manageable batches so locks and rollback scope stay limited.
• Apply backoff when dependent systems or the database are under pressure.
• Schedule heavy reporting or maintenance away from known traffic peaks when possible.

Cache results with an expiration story

Caching is valuable when repeated reads are expensive and slightly stale data is acceptable. It is not a substitute for understanding an inefficient query. A cache can hide a poor access pattern until a cold-cache event, invalidation bug, or new traffic pattern exposes it again.

Before caching, define what the cached value represents, how it expires, and what happens after a write. A short-lived cache for a public aggregate has different correctness requirements than a cached account balance. If invalidation is difficult, consider whether the feature can tolerate time-based freshness, versioned keys, or a precomputed read model.

Design for the workload you expect to have

Database speed is not a final optimization pass. It is part of API design, schema design, and operational design. A good endpoint defines a bounded response. A good schema reflects the queries that matter. A good deployment includes safe migrations, observability, and a rollback plan for changes that alter query behavior.

The most useful habit is to treat every slow query as feedback about the system’s design. Measure it in context, inspect its execution path, reduce unnecessary work, and verify the improvement under realistic conditions. Stop chasing database bugs as isolated surprises. Design predictable access paths, and the database becomes what it should be: a dependable part of the system rather than its recurring emergency.