--- title: "PostgreSQL DBA – Real time Interview Questions – 2" id: "4697" type: "post" slug: "postgresql-dba-real-time-interview-questions-2" published_at: "2026-07-29T07:23:52+00:00" modified_at: "2026-07-29T07:32:35+00:00" url: "https://labs.postgreshelp.com/postgresql-dba-real-time-interview-questions-2/" markdown_url: "https://labs.postgreshelp.com/postgresql-dba-real-time-interview-questions-2.md" excerpt: "Interview Question: CPU Increased from 20% to 35%. What Will You Do? Scenario Nothing changed in the PostgreSQL instance from […]" taxonomy_category: - "Uncategorized" --- # 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 pidstat -p 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) ; ``` 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`