Traffic went up by a factor of two and database CPU went to a hundred percent and stayed there. The instinct is to resize the instance, and it sometimes buys a few hours, but load rarely scales that way on its own. Something crossed a threshold and changed behaviour - a query plan flipped, a cache stopped fitting in memory, or connections saturated. Finding which one takes about twenty minutes of structured checking.
01Find the queries burning the CPU first
Start with pg_stat_statements ordered by total execution time rather than by mean. A query taking four milliseconds and running two hundred thousand times per minute costs far more than a two second report that runs hourly, and the fast one is easy to overlook precisely because it looks harmless in isolation.
If pg_stat_statements is not installed, enable it now - it needs a restart, which is painful mid-incident, and that is exactly why it belongs in your base configuration before you need it. In the meantime pg_stat_activity shows what is running right now, which is enough to spot a single pathological query.
| View | Shows | Use it for |
|---|---|---|
| pg_stat_statements | Cumulative cost per query shape | Finding the real CPU consumer |
| pg_stat_activity | What is executing now | Spotting a stuck or blocking query |
| pg_locks | Lock waits | Confirming contention rather than CPU |
| pg_stat_user_tables | Sequential scans per table | Missing index candidates |
02The plan flip nobody changed
Postgres picks a plan from table statistics. As a table grows, the point arrives where the planner switches from an index scan to a sequential scan, or reorders a join, and the new plan can be dramatically worse under concurrency. Nobody deployed anything - the data crossed a line.
Run EXPLAIN ANALYZE on the top queries and compare estimated rows against actual rows. A large divergence means the statistics are stale or unrepresentative, and ANALYZE on the affected table is the immediate fix. Where the estimate is wrong because the column has skewed distribution, raising the statistics target for that column produces a durable improvement.
03Connection saturation looks exactly like slowness
Each Postgres connection is a process. Several hundred active connections on a machine with eight cores means the operating system is spending its time context switching rather than executing queries, and every client observes this as the database being slow.
Check the count of active connections against your core count. If active connections substantially exceed cores, a pooler in transaction mode will produce a larger improvement than a bigger instance, and it costs nothing to run.
| Symptom | Likely cause | First action |
|---|---|---|
| High CPU, few slow queries | Too many connections | Add or tune a pooler |
| One query dominating total time | Missing index or plan flip | EXPLAIN ANALYZE it |
| High I/O wait, moderate CPU | Working set exceeds memory | Check cache hit ratio |
| CPU spikes on a schedule | Batch job or autovacuum | Check timing correlation |
04When the working set stops fitting in memory
A database that comfortably served requests from cache behaves very differently once the frequently accessed data exceeds available memory. Reads that were satisfied from shared buffers start hitting disk, latency rises sharply, and CPU climbs handling the extra work.
Compute the cache hit ratio from pg_statio_user_tables. Sustained values below about ninety-nine percent on a transactional workload indicate the working set no longer fits, and that is one of the few situations where adding memory is genuinely the correct answer rather than an expensive delay.
05What to do in the first ten minutes
Stabilise before diagnosing. Cancel any long-running query that is blocking others, since one badly planned analytical query can hold locks that stall the transactional workload. Confirm autovacuum is not fighting a large table at peak time. If a specific endpoint is responsible, rate limit that endpoint rather than degrading everything equally.
Then fix the actual cause. Resizing the instance without understanding why load changed means the same failure returns at the next threshold, usually at a worse moment and with less headroom to absorb it.
Topics
Priya Iyer
Staff Engineer · SyncTrix
Writes about the engineering decisions behind production systems - architecture, delivery and the trade-offs that only show up at scale.
Building something like this?
SyncTrix engineers AI, SaaS, platform and cloud systems for enterprises and high-growth teams. Tell us what you're shipping and we'll scope it with you.
Talk to an engineer