Database Failure Under Load: A Production Incident Response

A live client's production database went unresponsive during peak traffic. Peak hour is when a database problem costs the most and leaves the least room to experiment. [NEEDS INPUT: what did users see, errors or a product that appeared to work and hung?] I run delivery on accounts like this as a Technical Project Manager, and because I read the code my team ships, I worked the incident directly rather than relaying it. [NEEDS INPUT: is this a SaaS or an e-commerce account, and what does the platform do?]

What I had at the start was unresponsiveness, not a crash. The distinction decides the response: a process that has died gets restarted, a process that is holding on gets investigated, and restarting the second one destroys the evidence. Unresponsiveness spread across unrelated parts of a product cannot be explained by any single feature; it points at the shared resource underneath, which is why I treated this as a database-level failure and not a transient blip. [NEEDS INPUT: which monitor or channel surfaced the failure first?]

The obvious first suspect was capacity. Peak traffic plus a slow database invites the reflex to add hardware, and I ruled it out before spending anything: vertical scaling helps when the bottleneck is throughput, but when sessions are waiting on each other for locks, a larger instance waits faster at the same standstill. The second suspect was a recent deploy, since most production surprises arrive with a change. That one I could not dismiss by reasoning alone, so it went into the check queue, not the discard pile. [NEEDS INPUT: had a deploy shipped in the hours before the failure?]

What the evidence supported was slow queries and lock contention. The useful view in that state is not aggregate CPU but the live session list: which statements have been running longest, which are blocked, and which one at the head of the chain holds everything behind it. That reframes the problem: not a slow database, but a small number of statements holding locks that the rest of the workload needs, which is a sentence you can act on.

I killed the long-running queries while traffic was live. That is not a free action: terminating in-flight statements throws away work users had already started, rolls back open transactions, and surfaces as an error for whoever was on the other end of them. I took it because the alternative, waiting for those queries to drain or holding for a maintenance window, kept everyone blocked in order to spare a few. Routing reads to replicas in the same move traded exact freshness for availability, and I accepted mildly stale reads over a database that answered nothing at all.

I identified the slow queries and lock contention as the root cause, then worked both ends of it. Killing the long-running queries released the locks and let the queued work move again. Standing up read replicas took read load off the primary, so the recovery did not immediately collapse back under the same peak traffic that had caused it. With the incident stabilised, I optimised the indexes behind the offending queries and deployed a query caching layer, which is what turned an incident fix into a change in how much work the same queries take under normal load. [NEEDS INPUT: what the caching layer was, Redis or application-level?]

Zero downtime recovery. 80% query performance improvement. The first is the incident result; the second is the reason it did not repeat, because the same queries that had blocked the database became cheap enough that peak-hour load no longer reached the contention threshold. [NEEDS INPUT: how long the database was unresponsive before recovery?]

The durable part of this was structural rather than procedural. The read replicas, the indexes and the caching layer stayed in the architecture, so the system's behaviour under peak load changed permanently instead of for one afternoon. The habit I carry into every account since is that query plans and lock behaviour get reviewed while the system is healthy, because the live session list is a diagnostic tool nobody should be learning to read during an outage. [NEEDS INPUT: was a standing slow-query review added to the delivery process afterwards?]