🐛 The Bug
Every Friday afternoon, our checkout service started throwing intermittent 500s. Not all requests, maybe 1 in 50. Not every Friday either. Just most Fridays, starting sometime after 2 pm, and always gone by Monday morning. 😩
The error was unhelpful in the way only database errors can be:
error: sorry, too many clients already
Connection pool exhaustion. Except our pool was configured for 20 connections; on a normal Friday afternoon, we needed maybe 6, and nothing in our metrics showed a traffic spike. Whatever was eating connections wasn't customers. 🤔
🔍 Theory 1: A Slow Query Was Holding Connections Open
This was the obvious first guess. Somewhere, a query was taking forever, holding a connection the whole time, and eventually 20 of them piled up.
We checked pg_stat_activity during the next incident. Nothing. No long-running queries, no locks, no blocked transactions. Every connection was idle, not idle in transaction, not active. Twenty perfectly idle connections, and Postgres refusing to hand out a twenty-first.
Theory rejected ❌. If they were idle, the pool should've been reusing them.
🕵️ Theory 2: A Connection Leak in Our Own Code
Next suspect: somewhere in the app, we opened a connection and forgot to release it back to the pool. A classic leak. We'd shipped a new report-generation endpoint a few weeks earlier that ran a handful of raw queries outside our usual ORM wrapper, a very plausible place to forget a .release().
We audited it line by line. Every query went through a try/finally that released the client. We added logging around every pool.connect() and client.release() call, shipped it to staging, and hammered the reports endpoint with a script for an hour. Connections opened and closed exactly as expected. No leak.
Theory rejected ❌. At this point I was fairly sure we were cursed. 👻
📅 Theory 3: Some External Job Was Hammering the DB
We widened the search. Cron jobs? A scheduled Friday report someone set up two years ago and forgot about? We grepped every repo for cron, schedule, and setInterval, and found a weekly analytics export that ran Friday at 1 pm. Surely that was it.
Except the export used its own dedicated database user, and when we checked pg_stat_activity again, all 20 idle connections belonged to our checkout service's user, not the analytics job.
Theory rejected ❌. Again.
✅ The Actual Fix
The detail we'd been staring past the whole time: the connections were idle, but Postgres still wouldn't reuse them. That's not a leak. That's a pool that thinks its connections are busy when the database knows they're not.
We were running two instances of the checkout service behind a load balancer, each with its own connection pool capped at 20. Fine, that's 40 max connections total, well under Postgres's limit of 100. But we'd set up a third, older instance months back for a canary deployment experiment, and never fully decommissioned it. It wasn't receiving live traffic, so it never showed up in our request metrics or error dashboards. 👀
It was still running, though, and it still opened its full pool of 20 database connections on startup. Here's the actual killer: it had a bug in its health-check pinger that opened a new raw connection every few seconds instead of reusing one from the pool, and only cleaned them up when the process restarted. That process restarted every Friday because an auto-scaling policy recycled idle instances weekly. The leak reset itself every Monday, which is exactly why we could never catch it after the weekend, and why it always came back by Friday afternoon. 🎯
The "fix" that took two weeks of theorizing took five minutes to apply. We decommissioned the zombie instance. Connection counts dropped from a Friday peak of 94 to a steady 12, and the 500s never came back. 🎉
💡 What Actually Helped
-
pg_stat_activitygrouped byapplication_nameandusename, not just count. We assumed all 20 idle connections were "ours" because they were idle, not because we checked who owned them against every service that could plausibly connect, including services we'd forgotten existed. - Asking "what else is running" instead of "what's wrong with this code." Every theory we tried assumed the bug lived in the request path we were staring at. It didn't. It lived in infrastructure we hadn't thought about in months.
- The weekly pattern was the actual clue, not a red herring. We treated "happens on Fridays" as an annoying detail of an otherwise generic bug. It was the whole story, screaming "something on a weekly cycle" the entire time, and we didn't listen until theory 3 forced us to go looking for exactly that.
The bug wasn't in the code we wrote that week. It was in a service we'd stopped thinking about entirely, which, in hindsight, is usually where the real ones hide. 🕯️
