Case Study: Breaking Down a Slow API Without Any Deployment — a Database Network I/O Surge
Friday, 5:00 PM. The time that should mark the end of the work week. But PagerDuty is ringing. The initial report shows a confusing condition — API response times jumped from about 200 milliseconds to about 8 seconds, even though CPU and memory across all instances look normal, there’s no deployment or configuration change, and database queries appear to run as usual. The problem started exactly 47 minutes ago, and one striking anomaly appears on the dashboard: network I/O on the primary database surged sharply at the same time. This isn’t an ordinary incident — it’s a classic case demanding an analytical approach, not hasty assumptions.
First Step: Read the Signals, Don’t Guess
The most common mistake when on-call is directly guessing the cause based on past experience — “usually when it’s slow like this, it must be an N+1 query” or “there must be a memory leak again.” The temptation to jump straight to familiar conclusions is natural, especially under the pressure of Friday 5:00 PM. But in this case, the data actually speaks quite clearly if you’re willing to stop for a moment and read it calmly. Rather than guessing, the more productive step is eliminating possibilities one by one based on what the metrics truly show.
flowchart TD
A[Alert: API slow 8s] --> B{High CPU?}
B -- No --> C{High memory?}
C -- No --> D{Any new deployment?}
D -- No --> E{Slow query execution?}
E -- No --> F[Bottleneck isn't computation]
F --> G[Suspect I/O: Network or Disk]What Didn’t Happen
Based on the incoming metrics, several possibilities can be ruled out immediately. CPU stayed low across all instances, so this isn’t a CPU-bound problem. Memory was stable without spikes, so the memory leak or GC pressure possibility also falls away. No code changes came in during the last few hours, so a deployment regression isn’t the cause. And query execution at the database level appears normal, meaning this isn’t about computationally slow queries.
The four most common possibilities are already ruled out. That means the bottleneck isn’t in computation at all. If not computation, then what remains — logically — is I/O.
An elimination process like this is far more reliable than guessing based on “feeling” or previous incidents. Every ruled-out possibility significantly narrows the search space, and prevents you from wasting valuable time chasing hypotheses already disproven by data.
One Signal That Screams: Database Network I/O
Among all the metrics that look normal, the sharp surge in database network I/O is the most important clue — and the most often ignored by engineers used to only monitoring CPU and memory. This signal indicates one fairly specific thing: the database is sending far more data than usual. Not reading slower. Not computing heavier. But sending — in a volume far beyond habit.
This difference is crucial. Many people equate “slow database” with “slow query,” even though the two can happen independently. A query can finish executing in milliseconds, but if its result is millions of rows that must be transferred over the network, the transfer time itself becomes the bottleneck — not the execution time.
Request, Database, and Network Flow Diagrams
To understand this problem quickly, comparing the normal request flow versus the flow during the incident greatly helps visualize where the time is actually wasted.
Normal Conditions
sequenceDiagram
participant Client
participant API
participant DB as Database
Client->>API: Request
API->>DB: Small query + LIMIT
DB-->>API: Small result data
API-->>Client: Response (~200 ms)Under normal conditions, the database executes queries quickly, the data sent is relatively small, and the network is never a bottleneck. The entire request cycle completes in a few hundred milliseconds without significant obstruction at any layer.
Conditions During the Incident
sequenceDiagram
participant Client
participant API
participant DB as Database
Client->>API: Request
API->>DB: Query without LIMIT / large dataset
Note over DB,API: Tens-hundreds of MB of data transferred
Note over API: API idle, waiting for the fetch to finish
DB-->>API: Result data (after a long delay)
API-->>Client: Response (~8 seconds)In this condition, query execution on the database side stays fast, but the data transfer takes a very long time. The thread or goroutine on the API side is essentially idle, just waiting for the network to finish sending the entire query result. This diagram explains the contradiction that confused the on-call team at the start: low CPU, but high latency. Both can be true simultaneously because CPU measures computational work, while user-perceived latency covers the entire wait time — including data transfer time over the network that involves no CPU at all.
Main Hypothesis: A Query Producing a Very Large Dataset
Based on all the signals collected, the most reasonable hypothesis is that one query or a set of queries suddenly produces an enormous amount of result data, so network transfer time dominates the overall response time.
This hypothesis explains all the symptoms consistently. A query can finish executing quickly, which explains why database CPU stays low. But the application waits a long time during the row fetch process, which explains why client-side response time surges drastically. And the API looks slow to users even though the database itself doesn’t look “busy” from computational metrics — because computation indeed isn’t the problem.
Many monitoring dashboards by default only display query execution time, not the total data fetch time from the database to the application. If your dashboard only tracks this metric, incidents like this can look “fine” on the surface, even though users are already experiencing timeouts on their side.
Why Is This Problem Hard to Detect?
The difficulty of detecting this type of problem is rooted in already-too-standard investigation habits. Many engineers, when facing a slow API, automatically check query execution time, index usage, and explain plans — three things indeed relevant to computational problems, but not capturing the data volume dimension at all.
Yet the reality is simple: fast execution doesn’t mean a fast response if the size of the produced data is large. The simplest example to illustrate this:
-- ANTI-PATTERN: a query without LIMIT on a large table
SELECT * FROM orders WHERE status = 'PAID';
-- CORRECT: limit the number of rows fetched at once
SELECT * FROM orders WHERE status = 'PAID' ORDER BY created_at DESC LIMIT 100;
The first query could be executed very quickly by the database engine — an index on the status column makes finding matching rows nearly instant. But if the status = 'PAID' condition matches hundreds of thousands of rows, the time to transfer all those rows from the database to the application can reach several seconds, regardless of how quickly the query itself “finishes” on the database side.
The Time Clue: Why Exactly 47 Minutes Ago?
Time details are often ignored, even though they often hold the most valuable clues. A problem appearing at a specific minute — not gradually, but as if suddenly “switching on” — is almost always caused by a scheduled system, not random human activity.
Several possibilities worth suspecting at this point include an internal cron job running at certain intervals, a scheduled report or data export process triggered automatically, a cache TTL expiring simultaneously for a large number of keys at once, or a background worker activating periodically that happens to touch large tables.
No deploy is recorded, but the system behavior changes suddenly — and this kind of sudden change, without any code change, is the typical signature of something scheduled and running automatically in the background.
Common Cause Patterns in the Field
From experience investigating similar incidents across many production systems, several cause patterns recur with relatively small variations.
Missing Pagination or LIMIT
One of the most frequently found patterns is a small unnoticed change to a query that was previously correctly bounded.
-- CORRECT: a query with an explicit LIMIT
SELECT * FROM users ORDER BY created_at DESC LIMIT 50;
-- ANTI-PATTERN: a LIMIT accidentally removed or missed
SELECT * FROM users ORDER BY created_at DESC;
A change as small as removing the LIMIT clause — whether from an unintended refactor, a condition making the limit parameter unfilled, or a bug in the query builder layer — can change the returned dataset from dozens of rows to hundreds of thousands of rows. The impact isn’t on CPU, but directly on network saturation, exactly as seen in this incident.
Cache Miss Storm
The second frequently appearing pattern is when the cache for a large number of keys expires at nearly the same time. Once that happens, all requests previously served by the cache suddenly break through directly to the database, and the same queries — each possibly sending large amounts of data — execute repeatedly within a short time span. Database network I/O rises drastically as a result, even though each individual query actually runs fast.
Scheduled Export or External Integration
The third pattern involves parties external to the core system — such as a BI tool pulling data periodically, a data sync process with partners, or an admin feature for CSV export. Such processes often have three characteristics making them slippery to detect: they aren’t recorded as deployments because they indeed aren’t code changes, they access the database directly without going through the main API, and they pull large amounts of data as a normal part of their function.
The Right Technical Investigation Steps
Once the hypothesis is strong enough, the next step is verifying with concrete data, not stopping at the speculation level.
Find Queries with the Largest Row Counts
The investigation focus at this stage isn’t execution duration, but the number of returned rows. On PostgreSQL, the pg_stat_statements extension provides this visibility directly.
SELECT query, calls, rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;
Compare this result with the historical baseline from previous days at the same hour. Queries that usually return hundreds of rows but suddenly return hundreds of thousands of rows are the main incident cause candidates.
Break Down Time in APM or Tracing
In tracing tools like APM, pay close attention to the difference between two metrics often merged into one number: query execution time and fetch or transfer time. The typical pattern appearing in incidents like this usually becomes clear once separated:
Execution: 40 ms
Fetch / Network: 7.8 s
This separation becomes concrete confirmation that the bottleneck is indeed in data transfer, not in the query process itself — aligned with all the hypotheses built from the start.
Audit Scheduled Jobs
In line with the time clue discussed earlier, the relevant audit step is finding processes active at the exact minute the incident appeared, accessing the system’s large tables, and not going through the main API path — characteristics usually pointing to cron jobs, schedulers, or background processes running independently of regular user traffic.
flowchart LR
A[pg_stat_statements: find the largest rows] --> B[APM: separate execution vs fetch time]
B --> C[Audit cron/scheduler active at the incident time]
C --> D{Cause found?}
D -- Yes --> E[Confirm the root cause]
D -- No --> AQuick Mitigation During the Incident
When an incident is still ongoing and the team is in firefighting mode, the priority isn’t finding the perfect solution — it’s stabilizing the system as fast as possible to reduce user impact.
IMMEDIATE MITIGATION ACTIONS:
□ Apply a hard limit to the suspected query results
□ Limit API response sizes for affected endpoints
□ Temporarily stop the export job suspected as the trigger
□ Add rate limiting to endpoints sensitive to data surges
The goal of these steps isn’t permanently solving the root cause, but stopping the bleeding — returning the API to a condition that can serve users reasonably, while the root cause investigation continues without the same urgency pressure.
Avoid the temptation to immediately restart instances or the database without understanding the root cause first. If the cause is a still-running scheduled job, restarting only delays the problem a few minutes before it returns — while you lose the chance to capture the system condition while it’s malfunctioning for further investigation.
Architectural Lessons
This incident, beyond its resolution, teaches several important things about how production systems should be monitored. Monitoring shouldn’t only focus on CPU and memory — the two most commonly installed dashboard metrics, but capturing none of the data volume dimension flowing between system layers. Data size and network I/O should be treated as first-class metrics, equal in importance to the more conventional computational metrics. And most importantly, a query that’s “fast” in execution can still cripple the entire system, if the data volume it produces far exceeds anything ever considered when that query was first written.
Summary
- Systematic elimination is more reliable than guessing — rule out possibilities one by one based on real metrics (CPU, memory, deployment, query execution), not instincts from previous incidents.
- Low CPU + high latency points to I/O, not computation — check network I/O and the size of transferred data, not just query execution time.
- Execution time and fetch/transfer time are two different metrics — a query can be “fast” in execution but still cripple the system if its result is a very large dataset.
- Problems appearing at a very specific time usually come from scheduled systems (cron, scheduled exports, simultaneous cache TTLs), not human activity.
- Three common cause patterns: a LIMIT/pagination missing from queries, a cache miss storm making many requests break through to the database simultaneously, and scheduled exports or external integrations pulling large data without going through the main API.
pg_stat_statementsand APM tracing are key investigation tools for separating data volume problems from computational problems.- While an incident is ongoing, prioritize quick mitigation (hard limits, rate limits, stopping suspected jobs) over finding the perfect solution.
- Monitoring CPU and memory alone isn’t enough — data size and network I/O must be first-class metrics in production observability.