A Checkout Service Degrades After a Release: Choosing the Next Diagnostic Move
A checkout service running at about 1,200 requests per second was released to production at 14:00 with two changes: a new query that fetches pending orders for the logged-in customer, and a retry wrapper (three attempts, 200 ms backoff) around the payment gateway. Ten minutes later, p50 latency is unchanged at 180 ms, but p99 has climbed from 220 ms to 1,450 ms. The error rate is steady at 0.4%, CPU utilisation is 38%, and the database connection pool sits at 99% utilisation. The new statement is SELECT id, total FROM orders WHERE customer_id = ? AND status = 'PENDING'. The orders table holds 40 million rows, 2.1 million of them PENDING; the only index on the table is on status. The new query runs on the cart page, not on every request. No infrastructure changes were made, and the previous release ran for three weeks without incident. The incident channel is arguing over three options: roll back, add an index, or raise the connection-pool limit.
1. Which of the three proposed actions is best supported by the evidence, and why are the other two weaker?
Read the worked answer
The index (with rollback as a short-term mitigation) is supported; enlarging the pool is not.
- p50 is flat at 180 ms while p99 rises six-fold. The regression therefore hits a subset of requests - those that execute the new cart-page query - not the whole service. That rules out a general platform or network problem.
- 2.1 million rows are PENDING and the only index is on
status, which is nearly useless as a filter here. Answering the query means scanning a very large row set per call, which is consistent with a ~900 ms statement and a ~1.2 s tail once queueing is added. - 99% pool utilisation is a symptom: slow statements hold connections longer, so the pool drains. Raising the limit lets more slow queries reach the database and typically pushes CPU and I/O contention up rather than removing the tail.
- The retry wrapper is a weak suspect: at a 0.4% error rate and 1,200 rps, only about 5 requests per second retry - far too few to move p99 by a second.
2. What single measurement would most efficiently confirm or refute the leading hypothesis before the team acts?
Read the worked answer
Measure the statement, not the service. Run EXPLAIN (ANALYZE, BUFFERS) on the new query against a production-sized replica, and sample pg_stat_activity during the spike to see whether the sessions holding connections are waiting on that statement.
Confirmation looks like: a plan showing a sequential scan or a multi-million-row filter, actual execution time near 900 ms, and a matching wait event in the sampled sessions.
This is the discriminating test because the rival explanations make different predictions. A pool-size explanation predicts that wait events are spread across many different statements and that per-statement times are normal. A retry explanation predicts a visible rise in gateway call volume, which the unchanged error rate contradicts. Checking the query costs minutes and is read-only; enlarging the pool changes capacity and rollback discards the feature, so both are expensive ways to test a hypothesis that one explain plan can settle.
3. If the index is the true fix, why might a rollback still be the correct immediate action?
Read the worked answer
Because mitigation and remediation run on different clocks.
Building an index on a 40-million-row table is not instantaneous and, depending on the engine and locking behaviour, can itself add load or block writes. On a service currently at 99% pool utilisation, adding that work during the incident risks turning a latency problem into an availability problem.
Rollback removes the offending query path within minutes and restores the known-good state that ran for three weeks. The index can then be built concurrently in a controlled window, verified against a replica, and shipped with the next release.
The decision rule is: stop the bleeding with the reversible action, then fix the cause with the action that needs a change window. Note also that rollback does not foreclose the diagnosis - the explain plan can be captured from a replica while the rollback is in progress.