Four times in three weeks
The alert always arrived the same way: production PostgreSQL CPU above 80%. By the time anyone opened the graph, the database was at 97, 98, 99% of its limit. Every customer felt the platform slow down at once. Then, about an hour later, with nobody having touched anything, CPU dropped and the alert resolved itself.
The first time, that looks like luck. The fourth time in three weeks, it's a pattern. I decided to treat all four occurrences as one problem and use each repetition to see a little more.
At first, the database was blind
During the first occurrence, all I had were PostgreSQL's own statistics. They showed things worth fixing (tables with no index on the filter column, idle connections with no pooler, an unsupported version), but what worried me most had nothing to do with CPU: the database had no replica and no point-in-time recovery. If the disk failed, the company could lose up to six hours of data. I logged that as a priority before going back to the symptom.
During the second occurrence, EXPLAIN on the paginated listing showed every call sorting about 400 MB on disk. The database had already produced 32 TB of temporary files in less than a month. I measured transactions minute by minute during the spike and the line stayed flat. Traffic hadn't grown. It was the same few queries, two hundred times heavier.
To find out who was running those queries I needed the slow query log, which was off. I turned it on with a hot reload, without restarting the database or dropping a single connection. The 10 MB threshold for logging temporary files wasn't a guess: it was the value that separated normal operation from the incident in my measurements.
The culprit gets a name
The recommended index was created and the disk sort disappeared. Even so, on the third occurrence CPU went back to 98%.
This time the log I had enabled showed the query parameters, and the answer appeared: 99.97% of the slow executions came from a single customer with about 140 thousand records, paging through the listing from start to finish. Rows read per second had gone up 69 times with traffic flat. Part of it came from a small detail: a count(i.*) forced the database to build the whole row just to count it. Switching to count(*) let it read only the index.
I also showed that the number that mattered wasn't the CPU percentage but the throttling: the database was frozen in 97% of its 100 ms windows, and that slowed every customer down, not only the one causing it.
And the incident ended on its own once again. Now I knew why: the customer had stopped paging. The cause was still in the code, and the trigger depended on someone outside. It was going to happen again.
Splitting what belongs to each team
The development team shipped the count change, and it worked. On the fourth occurrence, what remained was OFFSET pagination: to return 15 rows, one call read 119,145.
That's the point where the conversation can turn into infrastructure and development pointing at each other, so I separated each side's share with numbers. The code set the ceiling of the cost, and no infrastructure tuning would change that. Infrastructure acted as a multiplier: on its default trigger, autovacuum left the visibility map stale and cancelled part of the gain the team had already delivered.
I also corrected the priority the team had been given. The count accounted for about 92% of the reads and OFFSET for 8%. Without that number, they would have changed only the pagination and left most of the load where it was.
So we wouldn't have to wait for another spike, I proposed an alert that warns earlier: rows read per scan on that table, an indicator that rises before CPU crosses 80% and doesn't fire on legitimate traffic growth.
The same pattern in another product
Later, the same signature showed up on an AI agents platform. This time I started at the edge: requests per second at the ingress tracked database CPU minute by minute. The question stopped being "what broke in the database" and became "who is calling". It was a single public API customer re-reading 641 pages of history in a loop.
Throttling that customer was a product decision, so I also built a plan that didn't depend on it: response caching with version-based invalidation (based on the real calls during the spike, the theoretical hit rate was 80% to 94%), caching of token validation and a cap on concurrent queries to the database.
What changed
The cause went from "isolated spike" to a line of code with an owner. The disk sort went from 215 MiB/s of temporary files to zero, and the count started reading only the index. The database gained the observability it lacked (slow query log, log collection and saturation panels) without a single production restart. And the risks that were worse than the symptom, like the missing replica and point-in-time recovery, went onto the priority list.
What I take from it
When an incident disappears with nobody doing anything, the right question is what stopped, not what fixed it. Every repetition is a chance to instrument a bit more: it was the log enabled during the second occurrence that named the culprit in the third.
I also learned that when the cause is split between infrastructure and code, the number is what keeps each side from waiting for the other.