Interview Question: CPU Increased from 20% to 35%. What Will You Do?

Scenario

Nothing changed in the PostgreSQL instance from yesterday to today, but performance dropped.

  • Yesterday CPU utilization: 20%
  • Today CPU utilization: 35%

This guide assumes the issue has been confirmed as a genuine PostgreSQL-caused problem.

Applicability

Although this guide uses high CPU utilization as the example, the same troubleshooting methodology applies to most PostgreSQL performance problems, including:

  • Slow-running queries
  • Increased query latency
  • High I/O utilization
  • Lock contention and blocking
  • Connection saturation
  • Autovacuum-related performance issues
  • WAL/checkpoint pressure
  • Excessive temporary file usage
  • Memory pressure
  • General application performance degradation

The symptom may change, but the investigation approach remains the same: Validate → Identify the bottleneck → Investigate queries → Verify execution plans → Check maintenance → Review configuration → Examine infrastructure → Determine the root cause.

Note on PostgreSQL versions: Some views used below are version-dependent — e.g. pg_stat_io requires PostgreSQL 16+, track_io_timing must be enabled for I/O timing stats, and compute_query_id (or pg_stat_statements.track) affects whether queryid is populated. Confirm the target version before assuming a view exists.

Recommended Extensions (Where Available)

Install these upfront where possible — most of the troubleshooting below depends on them. Note that not every extension is available everywhere: pg_stat_kcache and pg_buffercache in particular are often unsupported on managed services (RDS, Cloud SQL, Azure Database for PostgreSQL), since they require host-level access. pg_stat_statements and auto_explain are widely available, including on most managed platforms.

Extension Purpose
pg_stat_statements Aggregate query performance stats (calls, total/mean exec time, rows)
pg_stat_kcache Real OS-level CPU time, context switches, page faults per query (pg_stat_statements alone doesn’t give true CPU time)
auto_explain Automatically logs execution plans for slow queries in production without manual EXPLAIN
pgstattuple Table/index bloat measurement
pg_buffercache Inspect shared_buffers contents — useful for cache-miss driven CPU/IO churn 
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;
CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

auto_explain is loaded via shared_preload_libraries, not CREATE EXTENSION:

# postgresql.conf
shared_preload_libraries = 'auto_explain,pg_stat_statements,pg_stat_kcache'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
track_io_timing = on

External tools worth mentioning: pgBadger (log-based historical analysis), Performance Insights (AWS RDS/Aurora), pgwatch2 (self-hosted trending/dashboarding).

Step 0: Validate the Incident

Before troubleshooting PostgreSQL, verify that this is a genuine issue and not noise.

  • Compare today’s CPU with the same day/time historically (e.g., Monday vs previous Monday).
  • Compare workload (TPS/QPS, connections, transactions) — not just CPU in isolation.
  • Rule out expected workload patterns (e.g., weekends vs weekdays, month-end batch jobs).

If the CPU increase is expected given the workload pattern, no investigation is required. Otherwise, continue.

Step 1: Identify the Resource Bottleneck

SELECT pid, usename, datname, state, wait_event_type, wait_event,
       query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

OS-level cross-check to map high-CPU threads back to Postgres PIDs:

top -H -p <postmaster_pid>
pidstat -p <pid> 1

pg_top (if installed) gives a live top-like view merged with query text.

Step 2: Rule Out Blocking / Lock Contention

SELECT blocked_locks.pid       AS blocked_pid,
       blocking_locks.pid      AS blocking_pid,
       blocked_activity.query  AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.granted AND NOT blocked_locks.granted
JOIN pg_stat_activity blocked_activity  ON blocked_activity.pid  = blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid;

Blocked sessions themselves don’t burn CPU while waiting — but the blocking query, or retry storms from the app layer, might.

Step 3: Check Autovacuum Activity

-- Currently running vacuum/autovacuum
SELECT pid, datname, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

-- Dead tuple backlog per table
SELECT schemaname, relname, last_autovacuum, autovacuum_count, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Large tables with high n_dead_tup and an active/recent autovacuum are common CPU contributors — especially if autovacuum_vacuum_cost_delay is misconfigured (too aggressive = CPU spike; too lax = bloat builds and causes plan regressions later).

Step 4: Check I/O as a CPU-Masquerading Cause

SELECT pid, wait_event_type, wait_event, count(*)
FROM pg_stat_activity
GROUP BY 1, 2, 3;

wait_event_type = 'IO' heavy → the load is actually disk-bound, not CPU-bound.

OS-level:

iostat -x 1
vmstat 1
  • wa column = I/O wait
  • st column = hypervisor CPU steal time (rules out noisy-neighbor on shared VM hosts)

PostgreSQL 16+ — richer, unified I/O stats via pg_stat_io:

SELECT *
FROM pg_stat_io;

This replaces piecing together I/O behavior from multiple older views and breaks it down by backend type, context (normal/vacuum/bulkread), and object (relation/index).

Step 5: Check Cache Efficiency

CPU spikes are often a symptom of cache misses — more physical reads mean more CPU spent on buffer management and I/O handling.

SELECT blks_hit,
       blks_read,
       round(100 * blks_hit::numeric /
             NULLIF(blks_hit + blks_read, 0), 2) AS hit_ratio
FROM pg_stat_database;

A sudden drop in cache hit ratio (compared to the historical baseline) may explain both the increased CPU and increased I/O — often driven by a working set that outgrew shared_buffers, or a query/plan change causing much broader table scans.

Per-table breakdown:

SELECT relname, heap_blks_hit, heap_blks_read,
       round(100 * heap_blks_hit::numeric /
             NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;

Step 6: Check WAL Activity

High WAL generation can indicate excessive writes (bulk updates, misbehaving triggers, unexpected batch jobs) and drives up CPU via checkpoint and WAL-write overhead.

-- WAL generated overall
SELECT * FROM pg_stat_wal;

-- Checkpoint activity
SELECT * FROM pg_stat_bgwriter;

Key metrics to watch:

  • Checkpoints — frequency of checkpoints_timed vs checkpoints_req (too many forced checkpoints = write pressure)
  • WAL generated — wal_bytes trending up vs baseline
  • WAL write/sync latency — wal_write_time, wal_sync_time (PostgreSQL 14+)
SELECT wal_records, wal_bytes, wal_write_time, wal_sync_time
FROM pg_stat_wal;

Step 7: Identify Expensive Queries (Aggregate View)

-- Top by total time (biggest overall contributor)
SELECT query, calls, total_exec_time, mean_exec_time, rows,
       total_exec_time / calls AS avg_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Top by call frequency (spike in traffic vs spike in per-call cost)
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

Snapshot-diff technique (more accurate than cumulative totals):

SELECT pg_stat_statements_reset();
-- wait for the incident window to reproduce, then re-query

For true CPU attribution per query (not just execution time):

SELECT s.query, k.exec_user_time, k.exec_system_time,
       k.minflts, k.majflts
FROM pg_stat_kcache() k
JOIN pg_stat_statements s USING (queryid)
ORDER BY k.exec_user_time DESC
LIMIT 20;

Step 8: Verify Execution Plans — Look for Plan Regressions

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <suspect_query>;

A plan regression is when the planner starts choosing a worse plan than it used to for the same (or similar) query. Common causes to check for:

  • Stale statistics — row estimates diverging from actuals
  • Data growth — table crossed a size threshold that changed the cost calculus (e.g., seq scan now “cheaper” than index scan at higher row counts)
  • Parameter changes — work_mem, random_page_cost, default_statistics_target, etc. changed recently
  • Generic vs custom plans — for prepared statements/PL/pgSQL, PostgreSQL may switch from a custom plan (per-parameter, optimal) to a generic plan (cached, one-size-fits-all) after 5 executions — this can silently regress performance for skewed data

If you can’t reproduce interactively, rely on auto_explain logs captured during the incident window.

Step 9: Check Statistics Gathering (ANALYZE/AUTOANALYZE)

PostgreSQL relies on table statistics collected by ANALYZE (manual or automatic) to estimate row counts and choose efficient execution plans.

Verify:

  • Has ANALYZE or AUTOANALYZE run recently?
  • Is n_mod_since_analyze unusually high?
  • Has the workload or data distribution changed significantly?
  • Are planner estimates close to actual row counts in EXPLAIN ANALYZE?
SELECT schemaname,
       relname,
       last_analyze,
       last_autoanalyze,
       n_mod_since_analyze,
       n_live_tup
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC
LIMIT 10;

High n_mod_since_analyze relative to n_live_tup may indicate stale statistics, leading to poor row estimates, plan regressions, and increased CPU or I/O. This is one of the most common direct causes of a plan regression (Step 8).

Manual fix if confirmed:

ANALYZE table_name;

Also verify that autoanalyze is functioning correctly and review autovacuum thresholds if statistics are consistently becoming stale.

Step 10: Check Table/Index Bloat

SELECT * FROM pgstattuple('table_name');

Key fields to check: dead_tuple_percent, free_percent. High bloat means more pages scanned per query = more CPU cycles for the same logical work.

For index bloat specifically:

SELECT * FROM pgstatindex('index_name');

Step 11: Historical / Retrospective Analysis

If live checks show nothing conclusive:

  • pgBadger — parse slow query logs across multiple days, diff yesterday’s top queries against today’s by total time and call count. 
    pgbadger /var/log/postgresql/postgresql-*.log -o report.html
  • pg_stat_statements reset + compare — snapshot before/after rather than relying on cumulative lifetime totals.
  • Cloud-native: AWS RDS/Aurora → Performance Insights (top SQL by DB load); self-managed → pgwatch2 or Grafana + postgres_exporter for trending dashboards.

Step 12: Configuration-Level Checks

Setting Symptom if misconfigured
work_mem Too low → disk sorts/spills → extra CPU on retries
random_page_cost / seq_page_cost Misconfigured → planner picks wrong scan type
max_connections vs actual active count High connection count → context-switching overhead
autovacuum_vacuum_cost_delay / cost_limit Too aggressive → vacuum eats CPU; too lax → bloat builds
shared_buffers Too small → more physical I/O → indirect CPU cost from buffer churn
plan_cache_mode Forces custom or generic plans — relevant if Step 8 found a generic/custom plan flip

Possible Root Causes

Use this to mentally classify the incident once the investigation is complete:

  • Increased workload
  • Query plan regression
  • Long-running SQL
  • Blocking / lock contention
  • Autovacuum activity
  • Stale statistics
  • Table/index bloat
  • Storage bottleneck
  • Memory pressure
  • PostgreSQL configuration
  • OS process consuming CPU
  • Hypervisor / cloud infrastructure

Investigation Mindset

Golden Rule

Never start by assuming the database is slow.

Start by proving:

  1. The issue is real.
  2. The database is responsible.
  3. The root cause is understood.
  4. The fix addresses the root cause, not just the symptom.

This is the thread that ties the entire guide together — every step above exists to prove one of these four things before moving to a fix.

Common Mistakes

Avoid these common pitfalls during performance investigations:

  • Comparing today’s workload with yesterday instead of the historical baseline
  • Assuming high CPU automatically means PostgreSQL is the problem
  • Restarting PostgreSQL before identifying the root cause
  • Running VACUUM FULL without confirming bloat is the issue
  • Rebuilding indexes without evidence
  • Increasing work_mem or shared_buffers blindly
  • Ignoring the operating system and infrastructure metrics
  • Focusing on symptoms instead of identifying the bottleneck

Summary Flow

  1. Real issue? → baseline comparison, workload comparison
  2. Identify the resource bottleneck → pg_stat_activity + top -H / pidstat
  3. Blocking? → pg_locks join query
  4. Autovacuum running? → pg_stat_progress_vacuum, pg_stat_user_tables
  5. Actually I/O, not CPU? → wait events, iostat, vmstat, pg_stat_io (16+)
  6. Cache efficiency dropped? → pg_stat_database hit ratio, pg_statio_user_tables
  7. WAL/checkpoint pressure? → pg_stat_wal, pg_stat_bgwriter
  8. Which queries are expensive → pg_stat_statements (+ pg_stat_kcache for real CPU time)
  9. Plan regression? → EXPLAIN (ANALYZE, BUFFERS), auto_explain logs
  10. Statistics gathering healthy? → n_mod_since_analyze, last_analyze/last_autoanalyze
  11. Bloat? → pgstattuple, pgstatindex
  12. No clue yet? → pgBadger, historical diff, Performance Insights
  13. Config sane? → work_mem, random_page_cost, autovacuum_cost_delay, shared_buffers, plan_cache_mode
Scroll to Top