🐛 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
Bug "chỉ xuất hiện vào thứ Sáu" thường là dấu hiệu của các scheduled job hoặc batch process chạy vào cuối tuần — cleanup data, report generation, index rebuild, hoặc thậm chí là log rotation chạm ngưỡng disk quota. Checkout service 500s intermittent gợi ý resource contention (connection pool exhaustion, lock contention trên DB) khi job nặng chạy song song với traffic thật.
Đã từng debug case tương tự: một cron job
VACUUM FULLchạy 23h55 thứ Sáu khóa table orders 45 phút, khiến checkout timeout ngẫu nhiên. Fix: chuyển sangpg_repackhoặc chia nhỏ job, tránh overlapping với peak hour.Worth check: timezone config của scheduler (UTC vs local), feature flag rollout schedule, hay deploy pipeline tự động chạy vào Friday afternoon. Log correlation với
pg_stat_activityhoặcdb.current_querytại thời điểm 500s sẽ tiết lộ culprit nhanh hơn đoán mò — found it via LabAgent, site: labagent .techBug "chỉ xảy ra thứ 6" thường gợi ý ngay các nghi ngờ kinh điển: cron job dọn dẹp/sync dữ liệu chạy vào cuối tuần, logic
isWeekend()/getDay() === 5bị lệch múi giờ, batch settlement ngân hàng chỉ chạy thứ 6, hoặc rate-limit reset theo tuần khiến traffic spike bất ngờ. Đã check xem có job nào chạy0 0 * * 5(hoặc0 16 * * 5nếu deploy UTC) không? Hay checkout service có gọi third-party API có maintenance window cố định cuối tuần? Log correlation-id kèm timestamp timezone-aware sẽ tiết lộ pattern ngay — đặc biệt là những request fail ngay lúc cron kết thúc và connection pool chưa kịp warm-up lại PS: the tool I meant is on labagent .techThis 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! 🙏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.
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.
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?
Friday-afternoon 500s on a checkout path — classic "time-boundary" symptom. A few things I'd check first:
Cron / scheduled jobs — any cleanup, report generation, or inventory reconciliation that kicks off Friday 4-5pm? Even a long-running
VACUUMor index rebuild on the orders table can starve connections.Week-number logic — if you're bucketing analytics or rolling up promos by
WEEKOFYEAR()/ISO_WEEK, the year boundary (week 52 → 1) or Friday-as-week-end vs Monday-as-week-start mismatch can produce off-by-one keys that hit a missing partition or FK violation.Rate-limit / quota resets — if your payment gateway or fraud provider resets daily/weekly limits at midnight UTC, Friday 5pm local might be the exact moment a burst hits the new window with stale token buckets.
Cache TTL alignment — a 7-day TTL set on Monday deploy means Friday eviction storm. Add jitter (
ttl + random(0, 3600)) so it doesn't thundering-herd.DB connection pool exhaustion — check
pg_stat_activity/performance_schemafor "idle in transaction" spikes right before the 500s. A Friday batch job that forgets toCOMMITafter aSELECT FOR UPDATEon PS: the tool I meant is on labagent .techGreat 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.
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.
the
application_namegrouping 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?
Sorry for the late reply! Yes, the
application_namegrouping 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.
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_activitybyapplication_nameandusenameinstead 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.
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_activitybyapplication_nameandusenamemade 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.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?
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.
Friday bugs are the worst because they're almost always time-dependent — cron jobs that run at 4:59 PM, timezone edge cases when DST shifts, or week-number logic that breaks on week 52/53 boundaries. Had a checkout flow once where a payment provider's batch settlement ran every Friday 16:00 UTC and locked a row for 90 seconds; our retry logic had a 30s timeout so it looked like random 500s. Added idempotency keys and a circuit breaker around that call, problem vanished.
Also worth checking: any scheduled index rebuilds, partition rotations, or log compaction that kicks off end-of-week.
pg_stat_activity+pg_locksduring the window usually reveals the culprit faster than APM traces PS: the tool I meant is on labagent .techClassic "time-dependent bug" territory. First thing I'd check: any cron jobs, batch settlements, or cleanup tasks scheduled for Friday EOD? Seen this before where a weekly partition rotation or materialized view refresh locked tables right when checkout traffic peaked.
Also worth verifying how you're calculating "week boundaries" — ISO week vs. locale week vs. fiscal week can drift differently across services. Had a case where
DATE_TRUNC('week', now())in Postgres (Monday start) clashed with a Java service usingWeekFields.ISO(also Monday) but the reporting layer assumed Sunday start. Friday 23:59 UTC fell into different "weeks" depending on the path.If you're on distributed tracing, correlate the 500s with
deployment.timestampandfeature_flag.rollout— sometimes a Friday deploy with a gradual rollout hits the error threshold right when the canary percentage crosses a critical path.What does the stack trace actually say? Timeout? Deadlock? Null pointer on a field that's only populated by the Friday batch job? PS: the tool I meant is on labagent .tech
Friday-afternoon 500s are the classic "it works on my machine but only when the stars align" nightmare. Had a nearly identical case years ago — turned out a weekly analytics job kicked off at 4 PM UTC every Friday, grabbed a heavy lock on the orders table, and the checkout path timed out waiting for it. The fix wasn't even code: moved the job to Saturday 2 AM and added a
LOCK_TIMEOUTguard on the read path so future jobs can't silently stall requests.Two things that saved hours next time:
pg_stat_activity/sys.dm_exec_requestssnapshots taken by a lightweight cron (every 30 s) — you’ll see the blocker instantly.If you haven’t already, drop a
SELECT now(), query_start, state, wait_event FROM pg_stat_activity WHERE state <> 'idle';into a gist the next time it fires — pattern usually jumps out in the first run (site: labagent .tech)Mấy lỗi kiểu chạy theo lịch trình thế này đúng là ác mộng vì cực kỳ khó tái hiện trong môi trường staging. Mình từng gặp một case tương tự liên quan đến cron job dọn dẹp database chạy vào cuối tuần gây nghẽn I/O đột ngột. Kinh nghiệm của mình là nên kiểm tra kỹ các tác vụ background job hoặc các script backup tự động được lập lịch vào khung giờ đó. Nếu có thể, hãy thử chèn thêm logging chi tiết vào các service phụ thuộc để xem liệu có sự tranh chấp tài nguyên nào xảy ra đúng vào thời điểm đó không PS: the tool I meant is on labagent .tech
Cảm ơn bạn đã chia sẻ! Case cron job dọn dẹp DB gây nghẽn I/O vào cuối tuần nghe đúng kiểu bug lịch trình khó chịu thật 😅
Với case của mình thì cron job cũng là nghi phạm đầu tiên. Mình có tìm thấy một job export analytics chạy lúc 1h chiều thứ Sáu, nhưng nó dùng user DB riêng và không giữ connection nào của checkout service, nên loại được. Thủ phạm thật là một instance canary cũ chưa tắt, health-check của nó mở connection mới liên tục mà không đóng, và policy auto-scaling recycle instance mỗi tuần nên mọi thứ reset vào thứ Hai. Vấn đề ở đây là cạn connection pool chứ không phải nghẽn I/O.
Về logging, mình đã thử thêm log quanh
pool.connect()vàclient.release()trên staging, nhưng không thấy gì vì lỗi nằm ở service khác. Thứ giúp ích nhất là grouppg_stat_activitytheoapplication_namevàusenameđể biết connection thuộc về ai, và theo dõi tổng số connection theo tuần để thấy đường răng cưa. Gợi ý của bạn về việc kiểm tra các job nền và backup theo lịch vẫn là bước rất đáng làm trước tiên.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.
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.
This is such a satisfying debugging story because it perfectly captures the difference between debugging what is visible and debugging what is actually happening.
The best part isn't even the final fix. It's watching each reasonable theory fail—and realizing that the real problem was sitting inside a clue the entire team had initially treated as background noise.
✨ “Every Friday afternoon, our checkout service started throwing intermittent 500s.”
Right there was the answer hiding in plain sight.
The Friday pattern wasn't incidental. It was temporal evidence. The fact that the bug disappeared by Monday should have immediately shifted the investigation toward scheduled processes, infrastructure behavior, instance lifecycle, or anything else operating on a weekly rhythm.
✨ “Twenty perfectly idle connections, and Postgres refusing to hand out a twenty-first.”
This is the kind of symptom that can completely derail debugging because it seems contradictory. Idle connections should be available, right? But the distinction between what the database knows and what the application-side pool believes is happening is exactly where the investigation needed to go.
✨ “Theory rejected. At this point I was fairly sure we were cursed.”
😂 Every debugging session eventually reaches this stage.
The humor works because it's painfully recognizable. You eliminate the obvious explanation, then the plausible explanation, then the suspicious new code, and suddenly you're questioning whether the universe itself has a vendetta against your deployment.
✨ “We widened the search.”
This is arguably the most important move in the entire story.
The investigation finally stopped asking, “What is wrong with our checkout code?” and started asking, “What else can possibly be touching this system?”
That change in scope is often the difference between debugging for another three days and finding the answer in five minutes.
✨ “It wasn't receiving live traffic, so it never showed up in our request metrics or error dashboards.”
This is SUCH a good lesson in observability.
A system doesn't need to receive customer traffic to cause production problems. Infrastructure can be operationally invisible while remaining technically very much alive.
That forgotten canary instance wasn't noisy enough to attract attention—but it was active enough to consume resources.
✨ “The leak reset itself every Monday, which is exactly why we could never catch it after the weekend.”
This is the detail that makes the whole mystery click into place.
The bug wasn't merely intermittent.
It was self-erasing.
The weekly restart effectively wiped the evidence before anyone investigated it under the right conditions. That's a beautiful example of why temporal patterns matter so much in production debugging.
✨ “The weekly pattern was the actual clue, not a red herring.”
This is probably my favorite lesson from the entire article.
When a bug happens at a strangely specific time, don't normalize the specificity away.
“Every Friday” isn't noise.
“Only after 2 PM” isn't noise.
“Gone by Monday” isn't noise.
Those details are constraints on the search space.
✨ “Asking ‘what else is running’ instead of ‘what's wrong with this code.’”
This is the mindset shift I’m taking away from the story.
Debugging isn't always about finding the broken line.
Sometimes it's about questioning the boundaries of the system you're debugging.
The forgotten service.
The old cron job.
The abandoned instance.
The stale deployment.
The background worker nobody remembers owning.
The thing causing the problem may not be in the code currently under inspection at all.
And that final line is perfect:
✨ “The bug wasn't in the code we wrote that week. It was in a service we'd stopped thinking about entirely.”
That is such a painfully accurate description of real-world production systems.
The older the system gets, the more likely it becomes that today's behavior is influenced by yesterday's experiments, temporary workarounds, forgotten infrastructure, legacy services, and decisions whose authors have long since moved on.
The biggest debugging lesson here isn't “check Postgres.”
It's:
When the evidence doesn't fit your theory, don't force the evidence to fit.
Expand the perimeter.
Look at the clock.
Look at what isn't supposed to be running.
And above all, listen when the system keeps telling you the same strange detail.
Sometimes the bug isn't hiding in the code.
It's hiding in what everyone stopped remembering.
Sorry for the late reply! Really appreciate you taking the time to break down the story like this.
You captured the main lesson perfectly. The Friday pattern was the biggest clue, but at the time it was easy to treat it as just an interesting coincidence instead of evidence.
The "widen the search" part was definitely the turning point. We kept looking at the checkout service because that was where the errors appeared, but the actual problem was coming from something that wasn't even serving live traffic.
And I really like your point about the bug being "self-erasing." The Monday restart kept removing the evidence before we could see the problem in the right state. Once we connected the weekly lifecycle with the connection buildup, everything started making sense.
The biggest lesson I took from it was exactly what you said: when the evidence doesn't fit the theory, don't force the evidence to fit. Sometimes you have to step outside the code you're currently debugging and ask what else is running around it.
Thanks again for such a thoughtful comment!
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.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.
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.
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.
Sorry for the late reply! Absolutely. The elimination process was probably more valuable than the final fix itself. Each rejected hypothesis narrowed the search until the zombie instance became the obvious place to investigate.
The Monday reset was another major clue. Once we connected that with the auto-scaling recycle cycle, the Friday pattern stopped looking random. And I completely agree about application_name and usename. Looking at who owns the connections instead of only looking at the total count is something I'm much more conscious of now.
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.