Find the expensive request before changing the database
Begin from a user action: category load, article search, add to cart, or order submission. Capture its route, query count, database duration, returned rows, and request duration. A slow page can also wait on application code or another dependency, so do not diagnose from page-load time alone.
A query plan is the database route through tables, indexes, joins, sorts, and aggregates. In PostgreSQL, EXPLAIN shows that route. EXPLAIN ANALYZE runs the statement and reports actual rows and timings, so use a safe representative query. A large estimate-versus-actual row gap is a reason to investigate statistics or conditions, not an automatic index request.
Tie a candidate query to a route and outcome, so a dashboard-only report does not outrank a slow checkout.
Record before-change timings and comparable traffic without logging customer identifiers, tokens, or card-related values.
Design indexes for the access path you actually use
An index is a maintained structure that can help locate rows or return them in order; it is not a universal speed switch. Start with an important query’s filters, join keys, and sort order, then confirm the proposed index in a plan. PostgreSQL documents that a matching B-tree index can satisfy ORDER BY without a separate sort, especially with a small LIMIT.
Indexes add storage and write work because inserts, updates, and deletes maintain them. For a query that needs a large fraction of a table, a sequential scan and explicit sort can be faster. Test on production-like data and keep an index only when benefit justifies its cost.
Use the query’s actual filters and ORDER BY, including a deterministic tie-breaker where the interface needs stable order.
Review whether an index is used and what it costs during writes; recheck as data and traffic change.
Make pagination stable and affordable
Page-number pagination usually uses LIMIT plus OFFSET. Always impose a predictable order. PostgreSQL warns that without ORDER BY, LIMIT can return an unpredictable subset; different LIMIT or OFFSET values can yield inconsistent subsets because the optimizer may choose different plans.
Large offsets deserve scrutiny because skipped rows still have to be computed. For deep catalog, order-history, or archive browsing, consider cursor or keyset pagination: request rows after the last seen stable sort value and unique tie-breaker. Define how new or deleted records appear before changing the interface.
Return the smallest useful page rather than an unbounded result.
Decide whether the interface really needs an exact total on every request.
Cache read work without serving the wrong response
Caching reuses a prior result instead of reconstructing it from the database. Start at the HTTP layer for public, repeatable pages and versioned assets, because a cache hit can avoid application and database work. web.dev recommends long-lived caching for versioned URLs; changing the URL is the practical invalidation mechanism.
Cache policy is a correctness decision. A shared cache can reuse one response across users, so do not share account, cart, price, or authorization-specific output unless it is designed and tested for that scope. Cache-Control private restricts reuse to a browser cache, while no-store directs caches not to retain it.
Separate public catalog or article output from session-specific, location-specific, and permission-sensitive output.
Define who may see a stale value, for how long, and the invalidation or revalidation path.
Protect connection capacity instead of simply raising the ceiling
A connection is a live client session to the database. Workers, cron jobs, previews, or retries can exhaust slots even when individual queries look acceptable. PostgreSQL’s max_connections controls concurrent connections, is set at server start, and a higher value allocates more resources including shared memory. Raising it without a workload model can move the failure.
Set one budget across web workers, background jobs, administration, and replicas. Use bounded pooling where it fits, preserve headroom for recovery, and apply timeouts so stranded work does not wait indefinitely. Safe numbers depend on the engine, memory, query mix, and provider limits; confirm them with the operator.
Include web processes, queues, scheduled tasks, migrations, reporting tools, and deployment jobs.
Cap concurrency and use backoff so a fault does not multiply connection demand.
Observe query cost with context and privacy controls
Rank database work by total impact, not one slow sample. A moderately slow query called thousands of times can matter more than a rare report. PostgreSQL’s pg_stat_statements aggregates planning and execution data including calls, total and mean execution time, rows, and buffer activity. It needs server configuration and a restart to add or remove; planning tracking can add overhead.
Connect query groups to route, release version, and time window. OpenTelemetry advises collecting query text by default only after sanitization replaces literals, and using a low-cardinality query summary for grouping. This helps owners see trends without exporting customer data or creating a label for every value.
Review duration, total time, calls, errors, connections, and rows; each exposes a different failure mode.
Use parameterized queries and verify literals are removed before traces or logs leave the application.
Ship database changes as reversible reliability work
Treat an index, rewrite, cache policy, pool setting, or schema change as a production change with an owner, success measure, and rollback decision. Validate in a representative environment, release to a limited slice, and watch the affected journey. Faster is not success if the change returns wrong, missing, duplicate, or stale data.
Schema changes need care because application versions can overlap. Google Cloud guidance describes a compatibility sequence: release an application that handles old and new schema, change the schema, then release the feature that uses it. This preserves an application rollback route. Locking and online-change behavior differs by engine and managed service; obtain qualified review for high-volume or payment-related systems.
Name the route, performance and correctness signals, and threshold that stops the rollout.
Verify performance and correctness in a controlled slice before widening; practice the return path outside an incident.