Database CPU hits 100% during peak traffic: how do you respond

• Identify top CPU queries – use pg_stat_statements or Performance Insights to rank queries by total execution time and call count.
• Diagnose each offender – run EXPLAIN ANALYZE looking for full table scans (missing indexes), inefficient joins (nested loops over millions), and sorts spilling to disk.
• Check call frequency – a 2ms query called 50,000 times per second from an N+1 ORM pattern consumes more CPU than one slow query.
• Check connection pool – a pool sized far larger than the database can handle increases context switching and contention.
• Load-test after the fix – a fix that looks good on one query in isolation can behave differently under concurrency, so replay realistic traffic.
• Never scale up first – scaling hides the real cause and costs money forever.

Why should you not scale up immediately?

Scaling up hides the real cause and costs money forever. A missing index or an N+1 pattern will still waste CPU on a bigger machine. Start by measuring where the CPU goes.

-- PostgreSQL: top 10 CPU-consuming query patterns
SELECT query,
       calls,
       total_exec_time / 1000 AS total_seconds,
       mean_exec_time AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

query                               | calls  | total_seconds | avg_ms
------------------------------------+--------+---------------+-------
SELECT * FROM txn WHERE acct_id=?  | 250000 | 485.2         | 1.94
SELECT * FROM accounts WHERE id=?  | 250000 | 312.8         | 1.25
UPDATE balances SET amount=? ...    | 50000  | 210.5         | 4.21

The first two queries are cheap individually but called 250,000 times each: classic N+1.

How do you identify a full table scan from a missing index?

Run EXPLAIN ANALYZE on the top CPU query and look for Seq Scan on a large table.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM transactions
WHERE account_id = 42 AND created_at > '2026-01-01';

Seq Scan on transactions  (cost=0..125000 rows=50000 actual time=0.1..850 rows=120)
  Filter: (account_id = 42 AND created_at > '2026-01-01')
  Rows Removed by Filter: 4999880
  Buffers: shared hit=95000 read=30000

The engine reads all 5 million rows and discards 4,999,880. Fix with an index:

CREATE INDEX idx_txn_acct_date ON transactions (account_id, created_at);

What is an inefficient join and how do you fix it?

A nested loop over millions of rows where a hash join would be far cheaper usually means a missing index on the join key.

-- Slow: nested loop because no index on loan.customer_id
EXPLAIN ANALYZE
SELECT c.name, l.amount
FROM customers c
JOIN loans l ON l.customer_id = c.id
WHERE c.city = 'Mumbai';

Nested Loop  (cost=... rows=50000 actual time=0.1..12500 rows=48000)
  -> Seq Scan on customers  (rows=5000)
  -> Seq Scan on loans  (rows=10 per loop, 5000 loops = 50000 scans!)

-- Fix: add index on the join key
CREATE INDEX idx_loan_customer ON loans (customer_id);

After the index, the nested loop uses an index scan per loop iteration, dropping from 12 seconds to 50ms.

What is the N+1 query pattern and how do you fix it?

N+1 happens when loading a list fires one extra query per element. Rajesh's dashboard loads 500 accounts, then fires 500 individual queries for their latest transactions.

package com.bank.service;

import java.util.List;

public class AccountDashboard {

    // Wrong: N+1 pattern (1 query for accounts + N queries for transactions)
    public void loadDashboardBad() {
        List<Account> accounts = accountRepo.findByCity("Mumbai");
        for (Account a : accounts) {
            // This fires one query PER account
            List<Transaction> txns = txnRepo.findByAccountId(a.getId());
        }
    }

    // Correct: single fetch-join query
    public void loadDashboardGood() {
        // One query with JOIN replaces 501 queries
        List<AccountWithTxns> results = accountRepo
            .findByCityWithTransactions("Mumbai");
    }
}

The fix reduces 501 queries to 1, cutting CPU by orders of magnitude.

How does connection-pool misconfiguration cause CPU issues?

A pool sized far larger than the database can handle lets too many heavy queries run concurrently. Context switching plus contention drives CPU up.

# Wrong: pool too large
spring:
  datasource:
    hikari:
      maximum-pool-size: 200  # database has 8 cores

# Better: sized to database capacity
spring:
  datasource:
    hikari:
      maximum-pool-size: 20   # roughly 2x cores for mixed workload

Sometimes a smaller, well-sized pool actually reduces CPU because it limits concurrency to what the database can efficiently handle.

How do you apply the fix?

Target the biggest offenders first for maximum impact.

-- 1. Add the missing index
CREATE INDEX CONCURRENTLY idx_txn_acct_date
    ON transactions (account_id, created_at);

-- 2. Fix N+1 with a fetch join (in JPA)
-- @Query("SELECT a FROM Account a JOIN FETCH a.transactions WHERE a.city = :city")

-- 3. Add caching for hot read-mostly data
-- @Cacheable("accountSummary")

-- 4. Right-size the pool
-- maximum-pool-size: 20

Why must you load-test after the fix?

A fix that helps a single query in isolation can behave differently under concurrency. A new index speeds reads but adds write cost. Caching helps only if the hit rate is high under real access patterns.

package com.bank.loadtest;

// k6 or JMeter load test configuration
// Replay salary-day concurrency: 200 virtual users, 10 minutes
// Monitor: CPU, p95 latency, connection pool, cache hit ratio
// Pass criteria: CPU < 70%, p95 < 300ms

-- Load test results at National Bank of India
Before fix: CPU 100%, p95 = 8500ms, pool exhausted
After fix:  CPU 58%,  p95 = 180ms,  pool healthy

Only the load test, not a single quiet query, is honest proof that the fix holds.

What is the senior response checklist?

1. Do not scale up first.
2. Identify top CPU queries by total time and call count.
3. Run EXPLAIN ANALYZE on each offender.
4. Fix: add missing indexes, rewrite inefficient joins, eliminate N+1, add caching, right-size pool.
5. Load-test with production-like concurrency and data volume.
6. Monitor CPU and p99 latency under the load test.
7. Declare victory only when the load test passes.

What are common interview follow-up questions?

• How do you distinguish CPU saturation from I/O saturation? (Check with iostat and top; I/O-bound shows high disk wait, CPU-bound shows high user/system CPU.)
• What if the fix helps reads but hurts writes? (The load test with a realistic write mix will reveal this.)
• How do you handle N+1 in GraphQL? (Use DataLoader to batch field-level resolvers.)