🐛 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! 🙏

[build996]

Friday was never a trigger, it's just where the ramp crossed the line. A pinger leaking connections every few seconds is a sawtooth that resets on restart, and the spare slots are simply how far it gets by Friday afternoon. That also explains "most Fridays, not every Friday": a crossing near the end of a ramp is sensitive to tiny changes in slope, while a cron-triggered failure would pin to the same hour every week. Was total connection count ever trended over a full week? A plain count from pg_stat_activity would have drawn that sawtooth on day one.

[Harsh Raval]

Sorry for the late reply! Yes, trending the total connection count over a full week would probably have exposed the pattern much earlier. The gradual buildup was the real clue, but we weren't looking at it with enough historical context.

And I agree with your point about Friday not actually being the trigger. Once we understood the gradual accumulation and the Monday reset, the "most Fridays, but not every Friday" behavior made much more sense.

[build996]

The "most Fridays, but not every Friday" part is the bit I would keep as a heuristic. A real scheduled trigger fires every time; a calendar correlation that is strong but leaky almost always means something accumulating against a threshold, where the crossing date moves with the week's traffic. It hides well because dashboards mostly plot a current value, and a slow leak looks flat there - you only see it in a cumulative series, or by comparing Monday's floor week over week. Did that floor itself creep up over months, or did every Monday reset land back on the same number?

[Mudassir Khan]

the application_name grouping being the key is the part that's easy to miss. most dashboards show aggregate connection counts, which hides which service actually holds them. we've had this exact scenario with a canary that survived its cutover because the teardown script had a conditional that evaluated false in one env.

the autoscale policy acting as an accidental weekly cleanup cycle is what makes it nasty to spot — it only shows up if you correlate incident timestamps with infrastructure lifecycle events.

did this push you toward a connection pooler, or are connection limits scoped to each service now part of your deploy checklist?

[Harsh Raval]

Sorry for the late reply! Yes, the application_name grouping was one of the biggest lessons for me. Looking only at the aggregate connection count made the issue much harder to trace.

After this incident, I started treating connection limits and connection ownership as part of the deployment checklist. A pooler is something I'd consider depending on the architecture, but the first step for us was making sure every service properly manages and closes its connections and that we have visibility into which service is consuming them.

[Harper Elise Callahan]

This is such a satisfying read, mainly because of the process it shows, not just the fix. The zombie canary instance quietly opening a raw connection every few seconds is such a sneaky root cause, and the detail about it resetting every Monday because of the auto-scaling recycle policy is what made this bug basically uncatchable without the theory-by-theory elimination you did.

The real takeaway for me is that "idle" doesn't mean "harmless." It's easy to assume idle connections are just waiting their turn, not actively starving the pool of new ones. Grouping pg_stat_activity by application_name and usename instead of trusting the count feels like it should be step one for any pool exhaustion issue from now on.

Also appreciated that you followed up with the drift-detection job in the comments. Chasing one zombie is good, but building something to catch the next one automatically is the part that actually prevents a repeat.

[Harsh Raval]

Sorry for the late reply, and thank you! The "idle doesn't mean harmless" takeaway was probably the biggest lesson from this incident.

Grouping pg_stat_activity by application_name and usename made the difference because the aggregate count was hiding the actual source. And yes, fixing the zombie was only half the job. The drift-detection check was added specifically because we didn't want to rely on manually discovering another instance like this again.

[frank chu]

Twenty perfectly idle connections and Postgres refusing the twenty-first is a great detail, because it rules out the theory everyone reaches for first. What I like about the writeup is that you show the rejected hypothesis rather than jumping to the answer — pg_stat_activity showing all idle is the evidence that makes the slow-query theory dead rather than unlikely. I've been collecting bugs whose defining property is that nothing errors: my publishing pipeline silently skipped attaching a cover image on exactly one post because a substring check matched text in the article body. 200 OK, article live, no cover. The Friday pattern gave you a thread to pull; what would you have done if the timing had been random instead?

[Harsh Raval]

Sorry for the late reply! Exactly. Showing the rejected hypotheses was important because that's how the debugging actually happened. The fact that everything was idle made the slow-query theory much less convincing.

If the timing had been completely random, I think I would have started with continuous connection-count monitoring and correlated it with deployments, instance lifecycle events, and application-level connection activity. Without the Friday pattern, the sawtooth buildup would probably have been the strongest clue.

[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.

[Rafid Bottler]

Really enjoyed this one. The part that stands out is treating "happens on Fridays" as the actual clue instead of a weird footnote, that's such a common trap. Most postmortems jump straight to the fix and skip the dead ends, but the theories you ruled out are exactly what make this useful, since that's the actual thought process, not just the answer. The zombie canary instance with a broken health-check pinger resetting itself every Monday is such a sneaky failure mode. Also glad you added the drift-detection job afterward, that's the kind of follow-through most teams skip once the fire is out.

[Harsh Raval]

Thanks so much for this comment, really made my day!

You nailed exactly what I was going for with the "Fridays" detail. It's so tempting to bury that kind of thing as a throwaway observation, but it turned out to be the whole key to the mystery. I think we're trained to distrust anything that sounds superstitious, so it took way longer than it should have to start treating it as real data.

And yeah, the dead ends were honestly harder to write than the resolution. It felt a little embarrassing to admit how many theories we chased before landing on the zombie canary, but that's the actual job most days, not the clean version you'd put in a postmortem summary for leadership.

The drift-detection job was the one good decision we made afterward. Nice to know that part landed too, since it's easy for that kind of follow-up work to get deprioritized once things are stable again.

Thanks again for reading closely enough to catch all that, comments like this are exactly why I keep writing these up.

[Loren]

Great example where the calendar pattern is the biggest clue. Once you see "never during business-day peak, always off-hours," it stops looking like load and starts looking like something scheduled leaking pool slots.

[Harsh Raval]

Sorry for the late reply! Exactly. Once we looked at the timing as evidence instead of coincidence, the whole investigation started pointing in a different direction.

The off-hours pattern was especially useful because it didn't really match normal traffic behavior. It pushed us toward looking at scheduled processes, background services, and infrastructure lifecycle events instead of only investigating the application code.

[Mayur Upadhyay]

This is such a satisfying read because it shows the actual process instead of the highlight reel. Most debugging writeups skip straight to the fix, but the real value here is watching each theory get tested and ruled out systematically instead of just guessing. The zombie canary instance is such a classic trap too, it wasn't lying to your metrics, it just wasn't part of the story you were looking at.

The bit about the weekly auto scaling recycle resetting the leak every Monday is such a sneaky detail. That's exactly the kind of thing that makes a bug look intermittent and random when it's actually perfectly deterministic once you find the missing variable.

Also a great reminder that "idle" doesn't mean "accounted for." Grouping pg_stat_activity by application_name and usename feels like one of those checks that should be a default habit, not something you learn the hard way. Saving this one, thanks for writing it up in such detail.

[Harsh Raval]

Really glad the process itself landed, not just the fix. That's honestly the part I almost cut for length, since "here's the one-liner that fixed it" is so much easier to write than the two weeks of dead ends. But the dead ends are the useful part, since most real bugs don't announce themselves.

The zombie canary was the perfect trap because every signal we trusted (request metrics, error dashboards) was scoped to traffic, and this thing had none. It existed in a blind spot we didn't know we had, not a place we were failing to look correctly.

And yeah, the Monday reset is what made it borderline evil. A leak that just grows would've shown up in a trend line eventually. One that gets wiped clean every week just looks like noise, right up until you have the missing variable and it snaps into "oh, it's not random at all."

Appreciate you calling out the application_name/usename grouping specifically. That's the one habit change I'd want people to take away even if they forget everything else. "Idle" answers "is this connection doing work right now," not "should this connection exist at all," and those are very different questions.

[Sameer Qaiser]

The zombie instance detail is brutal — and honestly the scariest kind of bug. Not the one where something is broken, but the one where something is still running that nobody remembers.

I'm a beginner (just started writing Python tutorials this week), so my "debugging" is small stuff. But this post made me realize something: the lesson isn't just about infrastructure. It's about assumptions.

Every theory your team tried assumed the bug was in the code you were staring at. It wasn't. It was in something you'd stopped thinking about.

I catch myself doing the same thing when my code doesn't work. I stare at the line I just wrote, over and over. Then 20 minutes later I realize the problem was in a function I imported three steps up that I forgot was even running.

"What else is running" is a better question than "what's wrong with this line." I'm stealing that.

Great write-up. That weekly reset detail being the actual clue is chef's kiss.

[Harsh Raval]

Sorry for the late reply, and thank you! I really like your example because the same debugging mindset applies even to small Python projects.

Staring at the line you just wrote is such a common trap. 😄 Sometimes the problem is one or two layers away from where the error appears.

The "what else is running?" question ended up being one of the most useful questions in this incident, so definitely steal it! 😂

And yes, the weekly reset was the detail that finally made all the other clues connect. Once we understood that, the Friday pattern stopped looking random and started looking like a very useful signal.