🐛 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

[shieldx]

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 FULL chạy 23h55 thứ Sáu khóa table orders 45 phút, khiến checkout timeout ngẫu nhiên. Fix: chuyển sang pg_repack hoặ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_activity hoặc db.current_query tại thời điểm 500s sẽ tiết lộ culprit nhanh hơn đoán mò — found it via LabAgent, site: labagent .tech

[shieldx]

Bug "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() === 5 bị 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ạy 0 0 * * 5 (hoặc 0 16 * * 5 nế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 .tech

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

[Anh Nguyễn Văn]

Friday-afternoon 500s on a checkout path — classic "time-boundary" symptom. A few things I'd check first:

1. Cron / scheduled jobs — any cleanup, report generation, or inventory reconciliation that kicks off Friday 4-5pm? Even a long-running VACUUM or index rebuild on the orders table can starve connections.

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

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

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

5. DB connection pool exhaustion — check pg_stat_activity / performance_schema for "idle in transaction" spikes right before the 500s. A Friday batch job that forgets to COMMIT after a SELECT FOR UPDATE on PS: the tool I meant is on labagent .tech

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

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