PostgreSQL high CPU: start with pg_stat_statements

The CPU graph sits at 90%, and the load average is above the core count. A graph tells you how much load there is, not what is causing it. In PostgreSQL, the answer to "what" is usually in pg_stat_statements.

What is pg_stat_statements?

It's an extension that ships with PostgreSQL. It groups every executed query in its normalized form (stripped of parameters) and tracks how many times it ran, its total and mean execution time, the number of rows returned and the blocks it read.

Setup

Because the extension uses shared memory, it has to be preloaded first. This step requires a restart:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top

After the restart, in the database you want to monitor:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The most expensive queries

The query that burns the most CPU isn't always the slowest one. A 5 ms query running thousands of times per second easily outweighs a 30-second report that runs once a day. So look at total time first:

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

Then sort by mean_exec_time to see the queries that are individually expensive. If you suspect disk reads, the shared_blks_read and shared_blks_hit columns show the cache hit rate.

In PostgreSQL 12 and earlier, the columns are named total_time and mean_time.

Mind the time window

Statistics accumulate since the last reset. A batch job from three months ago may still be sitting at the top of the list. So before interpreting the numbers, find out the window:

-- PostgreSQL 14+
SELECT stats_reset FROM pg_stat_statements_info;

For a clean measurement, reset the statistics and wait through the period when the problem occurs:

SELECT pg_stat_statements_reset();

Next steps

Once you've found the source, typical interventions are:

1. Examine the suspect query with real parameters using EXPLAIN (ANALYZE, BUFFERS).
2. Look for missing indexes, unnecessary SELECT *, N+1 query patterns and frequently run queries that could be cached.
3. After the change, reset the statistics and measure again over the same window.

Lesson

The server graph is the symptom; query statistics are the diagnosis. Before adding hardware, first take a look: which query is generating the load?