PostgreSQL CPU pressure should be investigated as database work over time, not as a red chart alone. The same CPU percentage can come from a successful batch, a poorly estimated plan, excess connection concurrency, procedural code or a request retry loop. The useful outcome is a defensible explanation of where CPU time went and whether the workload can be made cheaper.
Correlate demand with execution
Put CPU, response time, active backends, transactions and query execution time on the same timeline. If response time rises while CPU remains modest, wait or I/O evidence may be more important. If CPU rises with a particular query fingerprint or client, inspect its plan and row estimates. A sudden plan change can be triggered by changed statistics, data distribution or parameters rather than a code deployment.
Look for common CPU amplifiers
- Sequential scans caused by a missing or unsuitable index.
- Misestimated joins that create large intermediate result sets.
- Repeated queries from chatty application paths.
- Too many active connections competing for the same cores.
- Expensive sorts, aggregates or functions applied to avoidable rows.
Change one cause at a time
Use explain output and representative parameters before adding an index or changing planner-related settings. An index that helps a selective lookup may add cost to a write-heavy table. Reducing connection concurrency can protect latency but may expose an upstream pool configuration problem. Test against a realistic workload and compare both average and tail latency.
Scale compute when evidence shows the workload is efficient and the sustained demand exceeds available capacity. Keep the query evidence with the capacity decision so the next growth event is easier to forecast.
Use query statistics to find candidates
Where pg_stat_statements is installed and configured, compare calls, total execution time, rows and block activity between two samples. Enabling the module can require shared_preload_libraries configuration and a server restart; do not assume CREATE EXTENSION alone enables collection. Available columns depend on the installed PostgreSQL and extension versions.
SELECT queryid, calls, total_exec_time,
rows, shared_blks_read, shared_blks_hit
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
This example suits modern versions with total_exec_time. It identifies accumulated expensive work, not CPU attribution. Take interval deltas, allow for entry eviction or resets, and correlate with current activity and host CPU. A blocked query can accumulate elapsed execution time without using much CPU.
Example: a plan is good for one tenant and bad for another
Imagine an orders query that is fast for small customers but slow for a customer holding half the table. Testing only the small customer's parameter value can conceal the problem. Save representative values for both distributions and compare estimated rows with actual rows at each important plan node. The useful question is where the estimate first diverges enough to make later work expensive.
Start with EXPLAIN. EXPLAIN ANALYZE executes the query, including writes and functions with side effects, so run expensive or mutating examples in a controlled environment. BUFFERS can help explain access work, but cache hits still consume CPU. A plan consisting mostly of cached reads can remain costly if it touches an excessive number of rows.
Choose a change that fits the evidence
If estimates are wrong after a bulk load, investigate statistics freshness and column distributions. If predicates on correlated columns mislead the planner, assess whether extended statistics address that relationship. If estimates are accurate but millions of rows are legitimately required, rewriting the report, pre-aggregating results or changing its schedule may matter more than another index.
For verification, compare application p95 latency and execution time per call under the same parameter distribution and request rate. Include large and small tenants. Track calls per business operation as well: an ORM change that multiplies the number of individually fast queries can waste CPU without producing a single conspicuously slow statement.
Keep the evidence for the next incident
Mini DBA PostgreSQL monitoring provides query and metric history alongside session, lock, vacuum and WAL diagnostics, helping teams compare an incident with normal operation. Available evidence depends on the monitored version, permissions and configured collection.
References and further reading