When a PostgreSQL-backed application slows down, larger infrastructure can hide the symptom while preserving the cause. A better response starts with evidence.

Find the expensive work#

Enable and review query statistics, then rank statements by total time—not only average duration. A moderately slow query executed thousands of times may cost more than one dramatic report. Correlate database behaviour with application endpoints, background jobs, and recent releases.

Use EXPLAIN (ANALYZE, BUFFERS) on a safe environment with representative data. Look for large row-estimate errors, sequential scans over selective predicates, repeated nested operations, sorting that spills to disk, and far more rows removed than returned.

Index for queries, not columns#

An index should serve an observed access pattern. Composite index order matters: equality filters generally lead, followed by ranges or ordering. Partial indexes can be powerful when queries repeatedly target a small active subset. Expression indexes help when the predicate applies a stable function.

Every index has a cost in storage, cache pressure, writes, and maintenance. Remove redundant indexes only after checking their real usage and operational role.

Improve the data path#

Select only needed columns and avoid unbounded results. Fix N+1 access in the application. Use keyset pagination for deep, ordered lists. Keep transactions short so they do not hold locks across network calls or user interaction.

Schema design matters too. Encode invariants with constraints, choose data types that reflect meaning, and be cautious with flexible JSON when the application needs relational filtering and reporting. Normalisation is a strong default; denormalise only for a measured read path with a clear consistency strategy.

After a change, compare plans and production metrics. Watch latency percentiles, throughput, locks, cache hit rate, connection use, replica lag, and vacuum health. Database performance is not a one-off tuning exercise—it is a feedback loop between product behaviour, application code, and the data model.