A core pinned at 100% tells you almost nothing on its own. It tells you that some backend process is busy - scanning rows, sorting a result set, hashing a join, or evaluating a function per row. The interesting question is which one, and that's a diagnosis, not a metric you can read off a dashboard. This post walks through the reasoning: what actually burns CPU cycles inside a database engine, and then the concrete queries that narrow a spike down to its cause on a running PostgreSQL instance.
What's actually consuming the cycles
Before touching pg_stat_activity, it helps to know what kinds of work are expensive in the first place, because the fix looks different depending on which one you're dealing with.
Arithmetic is cheap, some functions aren't. Addition and comparison cost almost nothing. Division and modulus cost more. Trigonometric functions, exponentiation, and cryptographic hashing cost dramatically more - a crypt() or repeated md5() call in a WHERE clause or join predicate, run over a few million rows, can pin a core by itself. For password-hashing functions like crypt(), that cost is the point: they iterate their inner hash many times specifically to make brute-forcing expensive, which means they're supposed to be slow. If you find one of these buried in a hot query path, the fix is usually to compute it once and store it, not to call it per row.
Memory access isn't uniform. The CPU doesn't talk to RAM directly - it maps virtual addresses to physical frames, and it does that through pages, typically 4KB each. When the working set of a query (a hash table being built, a sort in progress) doesn't fit comfortably in what's mapped and cached, more of that mapping and unmapping happens, and it isn't free. You don't see an error when this happens. You see a query that should be CPU-bound instead behaving like it's fighting the memory subsystem, and it shows up as elevated CPU with disproportionately bad throughput.
When the engine runs out of room, it goes to disk. Sorts and hash joins in PostgreSQL are supposed to happen in memory, bounded by work_mem (4MB by default in a fresh install, though most production configs raise it). When an operation needs more than that, the engine doesn't fail - it spills to a temp file on disk and switches to an external merge sort or a multi-batch hash join. That's still CPU work, now paying an I/O tax on top. It's one of the more common reasons a query that ran fine on a small table starts eating a full core once the table grows past what fits comfortably in the memory budget you gave it.
Locks cost more than the obvious cases. Explicit row and table locks are the visible kind. The less visible kind is contention on structures nobody thinks of as shared - a B-tree root page that every insert into that index has to touch, for instance, so a bulk load and an unrelated read can end up serializing on the same node even though neither transaction cares about the other's data. At the hardware level, something similar happens with cache lines: two threads incrementing two different counters that happen to sit on the same 64-byte cache line will bounce that line between CPU cores constantly, even though the counters are logically unrelated. Postgres backends are OS processes, not threads, so classic false sharing is rarer at the SQL level than it is in application code, but the lock-escalation version of it - unrelated transactions serializing on a shared structure - shows up regularly in write-heavy workloads.
Stale statistics send the planner down the wrong path. The query planner decides between a sequential scan, an index scan, a nested loop, or a hash join based on row-count and distribution estimates gathered by ANALYZE. When a table changes heavily and those estimates go stale, the planner can pick a plan that made sense for the table's old shape and is badly wrong for its current one - a nested loop over what it thinks is a few hundred rows, run against a table that's grown to a few million. That's often the single biggest CPU cause on a database that "used to be fine."
Dead rows and fragmentation add pure overhead. An UPDATE or DELETE doesn't remove a row immediately - it marks the old version dead and leaves it in place until vacuum reclaims it. Until that happens, every scan that touches that page has to read the dead row and skip past it, which is wasted CPU on every single access. Bloat also fragments the physical layout: pages that should be contiguous end up scattered, so a scan that should be sequential I/O turns into something closer to random access.
Every one of these is a plausible explanation for the same symptom - top showing postgres at 100% of a core - and they call for completely different fixes. Guessing wastes time: raising shared_buffers because CPU is high, when the real problem is a missing index, doesn't touch the actual cause and just makes the box more expensive.
Start with what's running right now
pg_stat_activity is a live view of every backend, and it's the first place to look. Check connection load before anything else - a flood of active backends competing for a fixed number of cores looks identical to any other CPU problem from the outside:
SELECT count(*) AS total,
count(*) FILTER (WHERE state <> 'idle') AS active,
current_setting('max_connections')::int AS max_conn
FROM pg_stat_activity;
If active is climbing toward max_conn, you may not have a slow-query problem at all - you have more work queued than the box can run in parallel, and the fix is a connection pooler, not a query rewrite.
Next, find what's actually executing and for how long:
SELECT pid,
now() - xact_start AS txn_age,
now() - query_start AS query_age,
state, wait_event_type, wait_event,
substr(query, 1, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_age DESC NULLS LAST;
Two columns matter more than the rest here. wait_event_type = 'Lock' means the backend isn't burning CPU at all - it's blocked, waiting on something else to release a lock, and the actual CPU cost sits with whatever's holding that lock (or with the pile of backends now retrying). Join pg_locks on granted = false to find the blocked PIDs and cross-reference against granted locks to find who's holding what. And an idle in transaction row sitting open for minutes is its own quiet problem: it pins an old transaction snapshot, which blocks vacuum from cleaning up dead rows behind it, which is how a single forgotten open transaction turns into bloat a week later.
Find the query, not just the moment
pg_stat_activity only shows you what's running this instant. To find what's been eating CPU cumulatively, you need pg_stat_statements (enable it via shared_preload_libraries if it isn't already):
SELECT substr(query, 1, 80) AS query,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
(If you're on a version older than PostgreSQL 13, the columns were named total_time and mean_time before planning time got split out into its own counters - worth knowing if you're reading an old runbook or Stack Overflow answer.)
Read the ratio, not just the total. A query with a huge total_exec_time but a low mean_exec_time and enormous calls isn't slow - it's just called constantly, which is a frequency problem: an N+1 pattern in the application layer, a cache that isn't being hit, a loop somewhere calling the database once per item instead of once per batch. A query with a high mean_exec_time even at low call counts is genuinely expensive per execution, and that's where EXPLAIN ANALYZE earns its keep:
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
Look for a sequential scan on a large table where you'd expect an index, a sort node reporting Sort Method: external merge Disk: ...kB or a hash join running in multiple batches (the disk-spill case above, in the flesh), or an estimated row count wildly different from the actual one - the fingerprint of stale statistics. If you're already on PostgreSQL 18, EXPLAIN ANALYZE includes buffer usage by default, so you can drop the explicit BUFFERS option and still get that information; it doesn't hurt to keep specifying it if some of your fleet is still on an older major version.
Check the hygiene metrics before you rule anything out
Cumulative counters and a live snapshot won't catch a slow-burning problem like statistics drift or bloat, since neither shows up as a single bad query. Both live in pg_stat_all_tables:
SELECT schemaname, relname, n_live_tup, n_dead_tup,
last_analyze, last_autoanalyze,
last_vacuum, last_autovacuum
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;
A last_autoanalyze that's null or weeks old on a table with heavy write traffic means the planner's estimates are almost certainly wrong - run ANALYZE table_name and see if plans change. A high n_dead_tup relative to n_live_tup means backends are scanning past dead rows constantly; VACUUM (or VACUUM ANALYZE to fix both problems in one pass) addresses it directly. Also check pg_stat_user_indexes for indexes with idx_scan near zero on a large table - every write still has to maintain that index, so an unused one is pure CPU cost with no read benefit, and it's a candidate to drop.
Missing indexes forcing sequential scans, and vacuum falling behind until bloat piles up, are the two most common root causes behind a Postgres CPU spike that doesn't trace to a single bad query - common enough to check early rather than after everything else. Our PostgreSQL high CPU usage guide covers the full cause-to-fix mapping for both, along with the FAQ-style answers to questions like "does this mean I need a bigger instance" (usually no) and "will VACUUM actually reduce CPU" (yes, indirectly, by removing the rows every scan was skipping over).
One caveat that applies to all of this: pg_stat_statements and the table statistics counters accumulate from server start or the last pg_stat_reset(). If you're debugging a spike that happened three hours ago, cumulative totals from the last six weeks can bury the signal you're looking for. Reset and re-sample over a representative window when old data is drowning out the current problem, and check secondary and replica nodes too - an index with zero scans on the primary might be exactly the one a read replica depends on.
Doing this by hand every time works, but it means joining four system views under time pressure, usually after the spike has already passed and the evidence has started to fade. That correlation - which query, which lock, which stale statistic, at the moment CPU actually climbed - is the specific gap NeverBlink is built to close: it samples these views continuously so the root cause is already assembled by the time you go looking, and it hands you a specific recommendation rather than a graph. The fix still gets approved by a person before it runs against production - the tool's job is narrowing "CPU is high" down to "this query, this lock, this table," not making the call for you.
