Use query evidence before adding infrastructure

A slow endpoint can spend time waiting for a database connection, executing a query, transferring rows, or converting them into a response. A database plan describes only part of that path. Begin with a request trace or timed stages so you know which boundary owns the delay. Adding a cache before identifying the delay creates a second consistency problem and may leave the original bottleneck intact.

Query plans distinguish estimates from observations. An estimated row count describes the planner's expectation. An actual row count comes from execution. A large mismatch can indicate stale statistics or a data distribution that the planner did not model well. Cost units are not milliseconds. PostgreSQL also warns that execution analysis runs the statement, which matters for statements that change data.

Read loops carefully. If an inner node reports an average time for each loop, repeated execution can dominate total work. A small lookup repeated thousands of times may cost more than one apparently expensive scan. Likewise, an index is not automatically faster. Reading most of a table can make a sequential scan reasonable, and every additional index costs storage and write work.

Bound the response contract. If the interface needs fifty records, returning fifty thousand and filtering in application code transfers avoidable work across the network. Stable pagination requires a deterministic order with a unique tie-breaker. Keyset pagination can avoid deep offset traversal, but it changes navigation semantics and still needs clear behavior when records change.

A fictional endpoint takes 1,100 milliseconds. Connection wait is 50, query execution 120, row transfer and decoding 700, and response assembly 230. It returns 40,000 rows so the application can select the newest 50. An index alone may reduce the 120 but cannot remove the 700 while the query still returns all rows. Move filtering, deterministic ordering, and the limit into the query, then measure the same stages again.

Suppose the bounded query returns 50 rows, query time becomes 35, transfer 8, and assembly 4 while connection wait stays 50. The new total is 97 milliseconds. This is a teaching calculation, not a benchmark or promised speedup. The next question is whether the remaining connection wait matters at expected load.

The following timing record is invented. Each stage uses the same request boundary and includes only one measured run, so it is a diagnostic example rather than a reliable percentile estimate.

The largest original cost was not query execution. A proposal that reduces execution from 120 to 60 milliseconds while returning the same 40,000 rows would leave a total near 1,040 milliseconds. This calculation does not make query tuning worthless. It shows why the first experiment should address unnecessary transfer and assembly under the supplied evidence.

A bounded query might have this teaching shape:

1SELECT id, created_at, title2FROM reports3WHERE account_id = :account4  AND (created_at, id) < (:cursor_time, :cursor_id)5ORDER BY created_at DESC, id DESC6LIMIT 50;

The account predicate is part of both authorization-aware data selection and index design. The unique ID is a tie-breaker for equal timestamps. The query assumes the cursor direction and comparison match descending order, and it requires a separate first-page path without a cursor predicate. The actual schema, timestamp nullability, and permitted data access must be verified before using it.

Keyset pagination can avoid scanning past a deep offset, but it does not automatically provide a frozen snapshot. If records change their ordering field between pages, users may see omissions or repeats depending on the contract. Immutable creation time is easier to reason about than mutable last-updated time. A snapshot token or export job can be appropriate when the user needs a stable complete dataset rather than a live browsing view.

The client should treat an opaque cursor as server-provided navigation state, not infer permission from it. The server must validate the current account and query constraints on every page. A cursor copied from another account must not let the caller traverse that account's records. Signing or encoding a cursor can protect its representation, but authorization still belongs at the data boundary.

Imagine the query plan estimates ten matching rows but produces fifty thousand. The response needs all fifty thousand for an authorized export, so adding LIMIT 50 would violate the task. The right diagnosis differs from the browsing example. Investigate statistics, predicate selectivity, join strategy, and whether the export should stream or run asynchronously. Optimization must preserve the requested result.

Now imagine a nested loop performs a one-millisecond lookup twenty thousand times. The individual lookup looks cheap, but repeated work can dominate. Inspect actual loops and total request time. Avoid adding node times blindly because plan-tree values can include child work and per-loop conventions matter. Explain the specific repeated operation you want to remove or batch.

The first misconception is that any index makes a query faster. An index has maintenance and storage cost, and a query returning most rows may reasonably scan the table. Measure the relevant workload after the proposed index rather than treating its presence as success.

The second misconception is that a plan's cost number is elapsed milliseconds. PostgreSQL uses planner cost units to compare strategies. Network transfer and application assembly can sit outside the plan, so endpoint timing remains necessary.

A third failure is optimizing by changing the result contract silently. Returning fewer rows is valid for a page that needs fifty, but not for an export promised to contain every eligible record. The user outcome constrains the optimization.

A report requires all 25,000 eligible rows, and transfer dominates. Propose a design without silently truncating output. A valid answer moves the full export to a bounded asynchronous job or streams the complete authorized result, while a preview remains paginated. Award one point for preserving completeness, one for bounding resource use, and one for measuring the new end-to-end path.

A plan estimates 20 rows but produces 20,000. The endpoint also serializes a large unused text field. Choose two checks before buying a larger database. Compare statistics and predicate selectivity, and reduce selected columns to the response requirement. Award one point for each check and one for measuring endpoint time after the change. Reject a claim that the estimate is an execution-time guarantee.

When would you still add a cache? A strong answer identifies repeated reads, tolerated staleness, authorization-safe keys, and an invalidation strategy. Follow up with per-user results. A weak answer treats a cache as a substitute for understanding the query. Explain the measured bottleneck and the smallest change that removes avoidable work.