High PostgreSQL CPU

Investigation principle

Before upgrading the instance, establish whether the workload would simply consume the larger instance too.

Common wrong assumptions

• “A bigger instance will fix it.” If three statements account for most of the database time, more vCPUs only delay the problem. Growth fills the larger instance too.
• “The slowest query is the culprit.” A 2 ms query called millions of times an hour often costs more in total than a 10-second report. Rank by total time, not mean time.
• “High CPU means the work is CPU-bound.” Lightweight-lock contention and more active sessions than cores can also show up as CPU. Check how many sessions are active and what they are waiting on.
• “It started with the release, so the release is the cause.” Plans also change when statistics are refreshed or data crosses a size threshold. Line the CPU curve up against deploys, autoanalyze runs and data growth.

First queries to run

Read-only. Run them during the problem window if you can; most of these views show current state, not history.

-- Top statements by total execution time (pg_stat_statements, PostgreSQL 13+)
SELECT queryid,
       calls,
       round(total_exec_time::numeric / 1000, 1)                   AS total_s,
       round(mean_exec_time::numeric, 2)                           AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_of_total,
       shared_blks_hit + shared_blks_read                           AS blocks,
       left(query, 80)                                              AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- What active sessions are doing right now
SELECT coalesce(wait_event_type, 'none (on CPU)') AS wait_type,
       wait_event,
       count(*)
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
GROUP BY 1, 2
ORDER BY 3 DESC;

When a deeper investigation is justified

• The top statements explain less than half of total time, or the ranking changed without a deploy.
• The instance has already been scaled once and CPU came back.
• The obvious fix is an index on a write-heavy table, so the read win has a write cost to weigh.

To rank the statements behind the CPU, see finding slow queries with pg_stat_statements. For the index decision, see how to test BRIN against B-tree on large, physically ordered tables.