🐛 The Bug
Every Friday afternoon, our checkout service started throwing intermittent 500s. Not all requests, maybe 1 in 50. Not every Fr...
For further actions, you may consider blocking this person and/or reporting abuse
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_activitytip on filtering byapplication_nameandusenameis 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 🔖.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/usenamegrouping 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! 🙏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.
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.
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.
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.
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.
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/usenamegrouping 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.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.
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.
This is such a satisfying read because of the order you told it in. Ruling out the slow query, then the leak, then the cron job before landing on the zombie instance made the final "idle but still counted" insight actually land, instead of just handing us the answer up front.
The line that stuck with me is "the connections were idle, but Postgres still wouldn't reuse them. That's not a leak." That's such a clean way to reframe the whole investigation, since every earlier theory was implicitly assuming it had to be a leak.
Also relieved to see you turned it into an actual weekly check instead of just closing the ticket. A lot of postmortems stop at "we decommissioned the thing," so it's good to see the follow up on catching the next zombie before it costs another two weeks of theorizing.
Thanks so much for this, genuinely made my day. The ordering was the hardest part to get right in the writeup. It would've been so much easier to just say "zombie connection pool instance" in paragraph two, but that's not how it felt living through it, and I wanted the "wait, that's not a leak" moment to hit the reader the same way it hit me at 11pm on a Thursday.
And yeah, the weekly check was non-negotiable once we found it. "We decommissioned the instance" fixes this Friday. It doesn't fix the next one hiding somewhere else in the fleet. Appreciate you reading closely enough to call that out specifically, that's exactly the kind of follow-through I wanted people to take away from this, not just the punchline.
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.
This is a great example of how the elimination process matters as much as the fix itself. Ruling out the slow query, then the leak, then the cron job before landing on the zombie instance shows exactly how much of debugging is narrowing the search space, not just spotting the answer. The detail that got me was the health check pinger opening raw connections and never cleaning them up, since that's such a small, easy to miss piece of code to cause that much chaos.
The weekly auto-scaling recycle explaining why the leak reset every Monday is such a satisfying reveal too. It's the kind of pattern that looks like noise until you realize it's actually the clue pointing straight at the cause. Grouping pg_stat_activity by application_name and usename instead of just eyeballing the count is a good takeaway on its own, that alone would save a lot of people from chasing their own code for something living somewhere else entirely.