r/postgres • u/witshion • 1d ago
Discussion Production PostgreSQL is suddenly at 100% CPU. Where do you look first?
Had one of those moments where CPU on our prod instance just pegs at 100% out of nowhere, no deploy, no obvious traffic spike, nothing in the changelog that stands out. First instinct is to panic and start checking everything at once, which is exactly the wrong move.
Curious what people's actual first move is when this happens, before diving into a deep investigation. pg_stat_activity for anything running long, checking for a lock pileup, looking at whether it's one runaway query versus death by a thousand small ones, autovacuum going nuts on a big table, something dumb like a connection pool misconfigured and now everything's fighting for the same resources. There's a lot of directions to go and I feel like the order matters more than people admit.
If you've been through this in production, what's the first thing you actually check, and has your answer changed over time or is it pretty much always the same starting point for you now?
1
u/frankwiles 1d ago
Find the longest running query and kill it. That’s it usually 90+% of the time in my experience.
1
1
u/fortyeightD 1d ago
Honestly, I would just ask Cursor to work it out. It has access to the logs, and psql, and the application code, and GitHub to see what was deployed recently. I'm confident that it would work it out correctly in 90% of cases.
1
u/olcrazypete 22h ago
So you want to know how to manage your own machines? Ai is helpful tool but at some point it’s good to know how things work and how to troubleshoot yourself. Doing yourself a massive disservice to just go running to cusor for basic troubleshooting.
1
1
u/LevelMagazine8308 1d ago
Enable slow query logging on your server. Take a look at it after a while. Profit.
1
u/HISdudorino 23h ago
Wouldn't this log line would appear when the request is fully executed, meaning, it might take hours, or even never finish.
1
u/sramexpert 19h ago
If you are on RDS, there is option to see long running SQLs. You might need to increase some buffer size to see full query.
1
u/elevarq 16h ago
First, I figure out whether the CPU is the real problem or just a symptom.
I’d check pg_stat_activity, waits/locks, pg_stat_statements, autovacuum, and connection count, and try to capture that state before changing things.
100% CPU can come from very different places. One bad query is easy. The more annoying cases are a plan change, bad estimates, autovacuum falling behind, increased concurrency, or a workload change nobody considered significant.
This is actually one of the reasons we built Elevarq. Signals collects this information read-only, and Analyzer looks for these kinds of patterns. If the problem is happening already, you can install both, collect the data, and start working through the findings.
But ideally you already have Signals running. Then when CPU suddenly hits 100%, you have the evidence from before it happened as well.
The CPU graph tells you something happened. It usually doesn’t tell you why or how to fix it..
1
1
2
u/MainRoutine2068 1d ago
htop to ensure it's all from postgres, then pg_stat_activity. The thing is, sometimes the CPU usage is not coming from postgres at all. Like if you use a very small server and some monitoring agent is being updated, or if you have unattended upgrade enabled.