Postgres High CPU Usage: Find and Fix the Queries Behind It

When a Postgres server sits at 90 to 100 percent CPU, the instinct is to reach for a bigger instance. Resist it for an hour. In almost every case we see, CPU is being burned by a small number of specific queries, and finding them is quick if you know where to look.

This guide is the playbook we use on client databases: find the queries, understand why they are expensive, fix them in order of impact, and confirm the result. It is written for PostgreSQL 13 and later and notes what changed in PostgreSQL 18 (current stable as of September 2026).

Step 1: Confirm it is Postgres and not the host

Before touching SQL, make sure the CPU belongs to Postgres backends and not to something else on the box (a backup job, an agent, a noisy neighbour).

• On a VM, top or htop should show many postgres processes near the top.
• On RDS or Aurora, open the database load view (AWS has folded Performance Insights into CloudWatch Database Insights) and check that load is dominated by CPU, not IO or lock waits. Note that the database's CPU load and the instance's CPUUtilization metric can disagree.
• Check what is running right now. An active session with no wait event is almost always on CPU:

SELECT pid, now() - query_start AS runtime,
       wait_event_type IS NULL AS on_cpu,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
ORDER BY runtime DESC;

If you see the same query shape dozens of times, or a few queries running for minutes, you already have your first suspects. Also compare the number of active sessions with your CPU core count. Hundreds of active sessions on an 8-core machine is a connection storm, and that alone can pin the CPU.

Step 2: Find the expensive queries with pg_stat_statements

pg_stat_statements is the single most useful extension for this problem. It aggregates every normalized query with its call count and timing. If it is not enabled yet, add it to shared_preload_libraries, restart, and run CREATE EXTENSION pg_stat_statements;. On managed services it is usually one parameter-group change.

Then rank queries by total time spent executing them:

SELECT
  round(total_exec_time::numeric / 1000, 1)        AS total_s,
  calls,
  round(mean_exec_time::numeric, 2)                AS mean_ms,
  round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct,
  shared_blks_hit,
  rows,
  left(query, 140)                                  AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Read the output in two ways:

1. High mean_ms, modest calls: individually expensive queries. Usually a missing index, a big sort, or a bad plan.
2. Low mean_ms, enormous calls: cheap queries executed far too often. Usually an N+1 query pattern in the application.

The pct column tells you where to spend your time. It is common for the top three queries to account for more than half of all execution time. Sort by total_exec_time, not mean_exec_time: a 2 ms query called 50,000 times a minute burns more CPU than a 3-second report that runs hourly. A very high shared_blks_hit means the query is churning through cached pages, which is pure CPU work.

Step 3: Understand why each query is expensive

Take the top query and run it through EXPLAIN (ANALYZE, BUFFERS) with realistic parameters (from PostgreSQL 18, EXPLAIN ANALYZE includes buffer counts automatically). On production, run it against a replica or wrap writes in a transaction you roll back.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 4821 AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Look for these patterns:

Step 4: Fix in order of impact

Add the index the query actually needs

For the example above, a composite index matching the equality filters first and the sort column last lets Postgres read just 20 rows instead of scanning and sorting:

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

Always use CONCURRENTLY on a live table so writes are not blocked. Then re-run EXPLAIN to confirm the planner uses it.

Kill queries that run thousands of times

If the top entry is a trivial SELECT ... WHERE id = $1 with millions of calls, the database is not the problem; the code calling it is. Batch those lookups into one query with WHERE id = ANY($1) or use your ORM's eager loading. This single change often drops CPU more than any index.

Refresh statistics and check autovacuum

Bad row estimates lead to bad plans. Run ANALYZE on the tables involved, and check that autovacuum is keeping up:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Tables with dead tuples approaching live tuples are bloated. Scans read more pages, which costs CPU. For hot tables, lower autovacuum_vacuum_scale_factor per table rather than globally.

Cap and pool connections

Hundreds of active connections compete for CPU and cause context switching, even if each query is cheap. Put a pooler such as PgBouncer in front of Postgres and keep active connections close to a small multiple of your CPU cores. See our PgBouncer best practices guide for safe settings.

Stop runaway queries from hurting everyone

Set sensible guardrails so a single bad query cannot pin the server:

ALTER ROLE app_user SET statement_timeout = '15s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

Special case: CPU spiked right after a major upgrade

If CPU jumped the moment you moved to a new major version, suspect missing planner statistics before anything else. Before PostgreSQL 18, pg_upgrade did not carry optimizer statistics across, so every table was planned with no statistics until autovacuum got around to analyzing it. Plans fall back to sequential scans and bad join orders, and CPU climbs.

The fix is immediate:

vacuumdb --all --analyze-in-stages

PostgreSQL 18 preserves most statistics through pg_upgrade (extended statistics are the exception), and adds vacuumdb --missing-stats-only to fill the gaps. Managed services such as RDS and Aurora run their own upgrade process, so do not assume statistics came across. Check last_analyze and last_autoanalyze in pg_stat_user_tables and run ANALYZE if they are empty.

Step 5: Verify, then decide on scaling

After each fix, compare the same metrics: CPU utilisation, the pct of the query you fixed, and p95 latency of the endpoints that use it. Only if CPU is still high after the top queries are fixed, and the remaining load is spread evenly across many well-planned queries, is it time to scale up or add read replicas.

A quick checklist

1. Confirm the CPU is Postgres query execution.
2. Rank queries by total_exec_time in pg_stat_statements.
3. EXPLAIN (ANALYZE, BUFFERS) the top offenders.
4. Add targeted indexes with CREATE INDEX CONCURRENTLY.
5. Remove N+1 patterns in the application.
6. ANALYZE and tune autovacuum on hot tables.
7. Pool connections and set timeouts.
8. Re-measure. Scale only what is left.

To run several of these checks at once, our free Postgres health check is a read-only script that reports your slowest queries, missing and unused indexes, and vacuum lag in one pass.