Skip to content

How to fix high CPU on a PostgreSQL database

Last updated

View as Markdown

Quick answer: High CPU is most often caused by the queries, not the config, so start there. See what is running now in pg_stat_activity (rows where state = 'active' and backend_type = 'client backend'), rank cumulative cost in pg_stat_statements by total_exec_time, and fix the cause. Common causes include a missing index forcing sequential scans, a plan that regressed after stats drift, or too many active connections.

pg_stat_activity is the live view, with one row per server process:

SELECT pid, state, wait_event_type, wait_event,
now() - query_start AS running_for,
query
FROM pg_stat_activity
WHERE state = 'active'
AND backend_type = 'client backend'
ORDER BY running_for DESC;

Filter on backend_type = 'client backend' or the list fills with autovacuum workers, parallel workers, and replication processes. Those processes can also be active, but they are not the application queries you are looking for.

  • Many active rows running the same query shape usually indicate a hot query using the CPU, often an un-indexed query that runs constantly.
  • A few very long-running active queries usually indicate an expensive scan, sort, or aggregate.
  • wait_event_type distinguishes compute from waiting: no wait event = running on CPU; IO or Lock = blocked, not compute-bound.

Live activity shows the current moment; pg_stat_statements shows the pattern:

SELECT calls, total_exec_time, mean_exec_time, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

See identifying slow queries for guidance on reading the results. Remember that total_exec_time = calls × mean_exec_time, so a cheap query run constantly can dominate.

What you seeLikely causeFix
Top query does a Seq Scan on a big tableMissing indexAdd the index (find missing indexes)
A query that used to be fast is now hotPlan regression from stats driftANALYZE; see why it got slow
Hundreds of active backendsToo many connectionsAdd a connection pooler
Slow sorts/hashes spilling to diskUnder-sized work_mem or SELECT * moving too muchSelect fewer columns; tune work_mem carefully, note a spill is mostly I/O, with some added CPU from extra merge passes

Usually, change the query or add an index rather than changing a setting. Configuration and contention can also drive CPU. Common examples include far more connections than cores, no connection pooler in front of a serverless app, or JIT compilation firing on short queries where it costs more than it saves. Check the queries first, then inspect configuration and contention.

Why does AI-generated code cause CPU spikes?

Section titled “Why does AI-generated code cause CPU spikes?”

AI coding agents can produce queries whose cost is not obvious in the source: an un-indexed filter that becomes a Seq Scan, an N+1 loop that fires thousands of small queries, or a SELECT * that hashes far more data than needed. CPU load appears when those queries run against production data volumes, which the model does not see.

  • Watch pg_stat_activity for a pile-up of the same active query.
  • Keep pg_stat_statements sorted by total_exec_time in a dashboard.
  • Index the scans, fix the regressions, and pool the connections before touching server config.
  • What causes high CPU usage in PostgreSQL?

    Most often the queries rather than configuration. The common causes are missing indexes forcing sequential scans, a plan regression after statistics drift, and expensive sorts or aggregates. Configuration and contention can be the primary cause too, too many connections thrashing context switches, no connection pooling, or JIT overhead on short queries, so check both, but start with the queries.

  • How do I find which query is burning CPU right now?

    Query pg_stat_activity for rows where state = active, ordered by how long they have been running (now() - query_start). That shows what is executing this instant; pg_stat_statements ordered by total_exec_time shows what costs the most cumulatively.

  • Is high CPU an I/O problem or a compute problem?

    Check wait_event_type in pg_stat_activity. Backends with no wait event are actively running on CPU; backends waiting on IO or Lock are blocked, not compute-bound. High CPU with little waiting points to raw query work like scans, sorts, and hashing.

  • Will raising max_connections or shared_buffers fix high CPU?

    Usually not. High CPU is normally caused by the queries themselves. Adding indexes, fixing a regressed plan, or pooling connections addresses the cause; raising config limits often just lets more expensive work run at once.