The SELECT That Was Fine in Testing and Took Down Production

It was a Monday morning. I had shipped a feature the previous Thursday, a reporting endpoint that let account managers see a customer's transaction history grouped by category over a configurable date range. The feature had been in testing for a week. Our QA engineer had run through the scenarios, it worked correctly, everyone was happy.

By 9:40am Monday, the database server was at 95% CPU. Support tickets were coming in. The application was timing out on half the requests. The senior engineer on call had already identified that a specific query was running continuously and each execution was taking between 30 and 60 seconds. That query was mine.

What I learned in the next three hours changed how I think about databases permanently.

What the Query Actually Did

The endpoint accepted a customer ID and a date range and returned their transactions grouped by category. My implementation did something like this: fetch all transactions for the customer in the date range, then for each transaction, fetch the category details to get the category name and colour code for the UI. Clean, readable, easy to understand in code review.

This is the N+1 query problem, and I had written it without knowing the pattern had a name or that it was considered a significant anti-pattern. In my test environment, the customer I tested with had about 80 transactions over the date range. The endpoint made 81 database queries, one for the transaction list, eighty for the category lookups, and completed in roughly 3 milliseconds. That felt fast. It was fast, relative to anything I could perceive.

In production, the account managers were testing it against customers who had been using the product for three years. Some of those customers had ten thousand transactions in the database. The endpoint was making 10,001 queries for a single request. Each category lookup was hitting the database individually, with its own connection overhead, its own query planning overhead, and its own round-trip latency. At ten thousand queries per request, and with several account managers testing the new feature simultaneously, the database was being asked to execute hundreds of thousands of small queries per minute. The CPU collapsed under the load. Everything else, all the other queries from all the other features, started queuing behind it.

The fix took about twenty minutes: replace the per-transaction category lookup with a single JOIN that fetched category data alongside the transactions in one query. The rewritten endpoint made one database call regardless of how many transactions the customer had. The CPU dropped back to single digits within minutes of the fix deploying.

But the Index Problem Was Different

After we fixed the N+1 issue, a second problem emerged. The query was no longer hammering the database with thousands of round trips, but the single consolidated query was still taking 2-3 seconds on large accounts. For a reporting endpoint, that's tolerable, barely, but it was still causing the page to feel sluggish.

Running EXPLAIN ANALYZE on the query revealed the issue. The transactions table was being scanned sequentially, every row examined in order, rather than using an index to jump directly to the rows for the relevant customer and date range. With three years of transaction data for all customers in the table, a sequential scan meant the database was reading through potentially millions of rows to find the few thousand belonging to a specific customer within a specific date range.

The table had an index on customer_id. It did not have a composite index on (customer_id, transaction_date). The query filter included both columns, and the database query planner, which makes decisions about how to execute a query based on statistics it maintains about the data, had decided that the single-column index wasn't selective enough to be worth using for this particular query pattern. It scanned the whole table instead.

In development, this didn't matter. The transactions table had perhaps 500 rows across all the test accounts. A sequential scan of 500 rows is instantaneous. The query planner in development had never been given a reason to choose differently, so it had always scanned the whole table, and scanned it so quickly that the issue was invisible.

Adding the composite index brought the single-query execution time down from 2-3 seconds to under 100ms. The query planner could now use the index to locate the relevant rows directly, without scanning everything.

What the Development Environment Hid

Neither of these problems was detectable in standard testing. The N+1 issue was invisible because the test data was small enough that 81 queries completed faster than a single well-written query would have. The missing index was invisible because the test data was small enough that a sequential scan was effectively instantaneous. Both problems required real production data volumes to manifest.

This is a structural challenge in software development that's easy to underestimate. Development environments are designed to be convenient, fast to set up, easy to reset, not dependent on production data volumes for security and privacy reasons. Those properties are genuinely valuable. But they also mean that certain categories of production problem, query inefficiency at scale, memory pressure under real load, connection pool exhaustion when real users hit endpoints simultaneously, simply do not appear in development, even when the code that causes them is present and would be trivially visible if the data were there.

The tools for catching this category of problem exist. Query logging that records execution time can surface slow queries before they cause incidents. Load testing against production-representative data volumes, synthetic data that matches the shape and size of real data without containing actual customer information, can make performance problems visible before the feature ships. Code review patterns that flag N+1 antipatterns can catch the structural issue before it gets tested at all.

Most teams use some combination of these, but they're rarely comprehensive, and the gaps are where incidents like this one come from.

What I Changed After This

The most durable change was that I started reading query execution plans during development, not just after a performance incident. Running EXPLAIN or EXPLAIN ANALYZE on any non-trivial query before a feature ships takes thirty seconds and has caught index issues before they ever reached production several times since. It also builds an intuition for how query planners make decisions that's difficult to get any other way, you see, concretely, what the planner chooses when the index doesn't exist and what it chooses when it does, and that visibility changes how you think about adding indexes in future.

I also became much more careful about queries inside loops. The pattern of "fetch a list, then for each item fetch something related" is natural to write, it mirrors how you'd think about the problem in most programming contexts, and it's almost always the wrong choice for database access. A single query that fetches related data together, using joins or subqueries or bulk lookups by a set of IDs, is almost always faster and produces less database load than N+1 individual queries, even when N is small. When N is large, as it was here, the difference is not a performance optimisation, it's the difference between a working system and an unavailable one.

The thing I find genuinely strange about this incident, looking back, is how invisible the problem was until it wasn't. The code worked. Tests passed. QA approved. Nothing in the development process surfaced any signal that there was anything wrong. And then real data volumes appeared, and the whole system ground to a halt within an hour. The failure mode was binary, perfectly functional below a certain data threshold, catastrophically broken above it, with no gradual degradation to act as a warning.

That particular failure pattern, works fine until it suddenly, completely doesn't, is one of the more frustrating things about performance problems in database-heavy applications. It rewards the engineers who build the habit of testing against realistic data sizes early, because those engineers get gradual feedback rather than a production incident. Everyone else gets Monday morning.