When Postgres starts eating CPU, the instinct is to look at slow query logs. That's right, but there's a faster starting point: pg_stat_activity combined with pg_stat_statements. Together they tell you exactly what's running and what's historically expensive.
Step 1: Find what's running right now (30 seconds). Query pg_stat_activity for rows where state != 'idle', ordered by duration descending. Queries running for more than a few seconds on an OLTP system are suspects. Look especially for state='active' with a null wait_event — that's pure CPU work.
Step 2: Check for lock waits. If wait_event_type = 'Lock', something is blocking. Run pg_blocking_pids(pid) to find the blocker. Kill the blocker if it's stuck.
Step 3: Historical expensive queries via pg_stat_statements. Sort by total_exec_time descending. If the top result is a sequential scan on a hot table, you have a missing index.
Step 4: Autovacuum. A surprising number of Postgres CPU spikes come from autovacuum running on a heavily-updated table. Check pg_stat_activity for rows where query starts with 'autovacuum'. Autovacuum doing a VACUUM ANALYZE on a large table is IO and CPU intensive. Tune autovacuum_vacuum_cost_delay and autovacuum_vacuum_scale_factor for hot tables.
Step 5: Connection count. Every idle connection in Postgres holds a small amount of memory and contributes to lock manager overhead. More than 100-200 connections without a connection pooler (pgBouncer/pgPool) is a problem. Check the count in pg_stat_activity.
The cpum.ai agent correlates Postgres process CPU time with query start times captured in pg_stat_activity to attribute CPU spikes to specific query patterns without requiring pg_stat_statements to be enabled.