🐛 The Bug That Only Happened on Fridays 📅😱

🐛 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_activity grouped by application_name and usename, 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. 🕯️

Comments

[Jennifer Smith]

This hit way too close to home 😅 We had almost the exact same issue but with a Redis connection pool from a "temporary" staging worker someone spun up for a demo two years ago 🧟. Nobody remembered it existed until it started eating connections during a traffic spike, and by the time we noticed, we'd already burned a full day blaming our own application code for a leak that wasn't there 🙃.

The pg_stat_activity tip on filtering by application_name and usename is gold 🏆. We didn't think to group by owner until way later than we should have, we just kept staring at the count going up and assuming it had to be us 😩. Bookmarking this post for the next time I get gaslit by a connection pool 🔖.

[Harsh Raval]

Haha the Redis version of this story is somehow even more relatable 🧟. There's always one forgotten thing running somewhere with a totally reasonable-sounding origin story ("just for a demo," "just for testing") that quietly outlives everyone's memory of why it exists. And yeah, the application_name/usename grouping felt so obvious in hindsight that it was almost embarrassing we didn't check it sooner 😅. Now it's the first thing I check for anything pool-related. Glad the post saved you a future day of confusion! 🙏

[Michael Johnson]

Great writeup, love that you included the theories that didn't pan out instead of just jumping straight to the fix 👏. That's the part most debugging posts skip, and it's honestly the most useful part for learning how to actually think through a problem like this instead of just copying the solution 🧠. Watching you rule out the slow query ❌, then the leak ❌, then the cron job ❌, made the eventual "wait, why is this idle but still counted" realization 💡 land a lot harder than if you'd opened with it.

One question 🤔: did you end up adding any monitoring to catch "instances that exist but shouldn't" going forward, or was decommissioning the one-off enough for now? Curious whether you're relying on periodic audits 📋, some kind of infra-as-code drift detection ⚙️, or just tribal knowledge to keep zombie services 🧟‍♂️ like this from creeping back in.

[Harsh Raval]

Really appreciate that, and good question 🤔. We didn't have great answer for a while, decommissioning was genuinely it for the first couple months. Eventually we added a lightweight weekly job that diffs "instances registered with the load balancer / service discovery" against "instances actually receiving traffic," and flags anything running-but-idle for more than a few days ⚙️. It's not fancy, no fancy drift-detection tooling, just a script and a Slack alert, but it's caught two more zombies since we set it up 🧟‍♂️. Tribal knowledge got us this far but clearly wasn't going to scale, so automating the "does this still need to exist" check felt like the actual fix behind the fix.