"The database is at 100% CPU" is one of the most common production incidents and one of the most common interview prompts, because the instinctive answer — add capacity — is almost always wrong. A database at 100% CPU is usually doing a huge amount of unnecessary work, not a reasonable amount of necessary work. Your job is to find the unnecessary work.
The first sixty seconds
Before touching anything, answer three questions. They narrow the cause enormously.
- Did something change? A deploy, a migration, a feature flag, a config push, a new cron job, a marketing campaign. Correlate the CPU graph with the deploy timeline. If CPU stepped up at 14:03 and a deploy landed at 14:02, you have your suspect.
- Is traffic up, or is work-per-request up? Compare requests per second against CPU. If RPS is flat and CPU tripled, this is not a capacity problem — the same traffic suddenly costs more, which means a plan changed or a cache stopped absorbing reads.
- Reads or writes? Read-heavy CPU points at query plans and missing indexes. Write-heavy CPU points at index maintenance, lock contention, autovacuum, or a batch job.
Finding the queries burning CPU
You need two views: what is running right now, and what has consumed the most time cumulatively. In PostgreSQL:
-- What is active right now?
SELECT pid, now() - query_start AS duration, state, wait_event_type, wait_event,
left(query, 120) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND backend_type = 'client backend'
ORDER BY duration DESC
LIMIT 20;-- Requires the pg_stat_statements extension
SELECT calls,
round(total_exec_time) AS total_ms,
round(mean_exec_time, 2) AS mean_ms,
rows,
left(query, 140) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;Read that second result carefully. The worst offender is often not the slowest query — it is a 4ms query called 300,000 times a minute. Total time is what burns CPU, and calls × mean_time is where you should look first. A query that got 10x more frequent usually means a cache stopped working or someone introduced an N+1 in a loop.
Then explain the plan for the top offenders and look for the signatures of CPU-heavy work:
| Plan signature | What it usually means |
|---|---|
| Seq Scan on a large table | Missing index, or an index the planner refuses to use |
| Nested Loop with a huge outer row count | Bad cardinality estimate; statistics are stale |
| Sort / Hash spilling to disk | work_mem too small, or you are sorting far too many rows |
| Rows Removed by Filter: very large | You are reading everything and discarding most of it |
| Repeated identical plans, thousands of calls | N+1 query pattern in application code |
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ... ; -- run against a replica if the query is expensive
The five causes that explain most incidents
1. A missing or newly-unused index
Someone added a filter to an existing query, or a table grew past the point where the planner switches from index scan to sequential scan. The table did not change; the plan did. Adding the right index turns a 400ms scan into a 0.4ms lookup, which is a thousandfold reduction in CPU for that query. Use CREATE INDEX CONCURRENTLY so you do not lock writes.
2. Stale statistics producing a terrible plan
After a bulk load or a large delete, the planner's row estimates are wrong, so it picks a nested loop over a hash join and does a hundred million comparisons. ANALYZE on the affected tables can resolve an incident in seconds. This is the classic "nothing changed but everything got slow" case.
3. The cache stopped absorbing reads
The database is fine; its shield disappeared. A key format change, an eviction storm, a Redis restart, or a synchronised TTL expiry sends the full read volume to the database at once. Check cache hit rate on the same time axis as database CPU — if hit rate fell as CPU rose, you are debugging the cache, not the database. That failure mode has its own playbook in Your Redis cache suddenly became useless.
4. Connection storm and pool exhaustion
Each PostgreSQL connection is a process. Two thousand connections from autoscaled app instances means thousands of processes competing for CPU, and context switching alone can pin the machine. Symptoms: high CPU with low throughput, many backends in idle in transaction. The fix is a pooler (PgBouncer in transaction mode) and a hard cap on per-instance pool size, because pool size multiplies by instance count.
5. A background job you forgot about
Analytics queries against the primary, a nightly export that now overlaps peak, a migration backfilling a column, autovacuum finally kicking in on a huge table. These are legitimate work in the wrong place at the wrong time. Move them to a replica and schedule them off-peak.
Mitigate now, fix properly later
During an incident you want the fastest safe action that restores headroom, then a real fix afterwards. In rough order of aggressiveness:
- Kill the offender.
pg_cancel_backend(pid)first (graceful),pg_terminate_backend(pid)if it ignores you. One runaway analytics query is often the entire incident. - Set a statement timeout. A global
statement_timeoutof a few seconds for the application role prevents any single query from consuming the box, and turns a total outage into a few failed requests. - Shed load deliberately. Disable the expensive non-critical feature — the recommendation panel, the search-as-you-type — via a feature flag. Degrading one feature beats losing the product.
- ANALYZE the hot tables. Cheap, fast, and fixes the stale-statistics class of incident outright.
- Add the index concurrently. Slower to build but does not block writes.
- Warm the cache before you turn traffic back up. Otherwise you re-create the stampede that caused the incident.
When scaling is actually the answer
Sometimes the queries are efficient, the cache is healthy, and the machine genuinely cannot do the work. Then scale, in this order, because each step costs more complexity than the last:
| Step | Good for | Cost you take on |
|---|---|---|
| Vertical scale (bigger instance) | Immediate headroom, buys time | Money; a ceiling you will hit again |
| Connection pooling | Too many connections, not too much work | An extra hop and component |
| Read replicas | Read-heavy load, analytics, exports | Replication lag and read-your-writes bugs |
| Caching layer / materialised views | Repeated expensive reads | Invalidation complexity, staleness |
| Partitioning | Huge tables with time-based access | Migration effort, query changes |
| Sharding | Write throughput beyond one primary | Cross-shard queries, rebalancing, ops burden |
Two traps worth naming. Read replicas do not help write-heavy CPU at all — every write is replayed on every replica. And sharding is not a scaling knob you turn during an incident; it is a quarter of engineering work.
Preventing the next one
- Enable
pg_stat_statementseverywhere, before you need it. - Alert on the leading indicators — mean query time, cache hit rate, connection count, replication lag — not just CPU, which tells you too late.
- Set
statement_timeoutandidle_in_transaction_session_timeoutfor application roles by default. - Review query plans in code review for anything touching a large table; make "new query on a big table needs an index" a checklist item.
- Keep analytics off the primary. Give reporting its own replica.
- Load-test with production-shaped data volumes — plans change with table size, so a 10k-row test database proves nothing.
How to answer this in an interview
The interviewer is testing whether you diagnose or guess. Structure your answer as triage → diagnosis → mitigation → prevention, and say the numbers out loud.
"First I'd check whether traffic changed or work-per-request changed — if RPS is flat and CPU tripled, it's a plan or cache problem, not capacity. Then pg_stat_statements ordered by total_exec_time, and I'd look for a query whose call count jumped, which usually means a cache stopped working, or one whose mean time jumped, which usually means a plan change or missing index. To stabilise: kill the runaway query, set a statement timeout, and flag off the most expensive non-critical feature. Then the real fix — index, ANALYZE, or fixing the cache — and only if the queries are genuinely efficient do I talk about replicas for reads or sharding for writes, knowing replicas don't help write-bound CPU."
If the interviewer pushes with "you can't find a bad query, CPU is spread evenly," that is your cue to talk about genuine capacity: connection pooling, replicas with a read/write split, partitioning by time, and finally sharding with a discussion of the shard key.
Summary
- 100% CPU usually means too much unnecessary work, not too little capacity.
- Ask first: did anything deploy, is traffic up, is it reads or writes.
pg_stat_statementsordered by total time finds the offender; watch call count as well as mean time.- Most incidents are a missing index, stale statistics, a broken cache, a connection storm, or a stray background job.
- Mitigate with kills, timeouts and load shedding; fix with indexes, statistics and caching.
- Scale in order of increasing complexity, and remember replicas do nothing for write-bound CPU.