The first time I watched a production database buckle under its own weight, it was a Tuesday afternoon in February, the kind of gray, forgettable day that becomes unforgettable because of what happens on it. A colleague had pushed a schema change the previous Friday — a small one, he said, barely worth a review — and by Tuesday the primary read replica was lagging forty minutes behind and the on-call engineer was eating a cold burrito over her keyboard, scrolling through slow query logs with the thousand-yard stare of someone who has been awake since three in the morning.
The fix, when it came, took eleven minutes. The diagnosis took nine hours.
That asymmetry is the thing about database performance tuning mistakes that nobody warns you about clearly enough. The error is fast. The understanding is slow. And in the gap between those two events, you learn more about your system — and about your own assumptions — than any architecture review ever taught you.
I've been thinking about that Tuesday for years, and about all the similar Tuesdays that followed, on different teams, with different databases, but with a recognizable family resemblance in how the trouble started. What follows isn't a catalog of gotchas. It's more like a field guide to the cognitive traps that make database performance tuning mistakes so reliably, painfully common.
The Index You Added Without Reading the Query
Indexes feel like pure upside. You add one, the slow query speeds up, somebody on Slack sends a party emoji. The trouble is that indexes are not free, and the cost is paid in a currency — write amplification, storage, lock contention during index maintenance — that doesn't show up on the dashboard you were looking at when you made the decision.
The deeper mistake, though, is adding an index without actually reading the query plan. Not the query. The plan. Most engineers I've worked with can read SQL fluently and read EXPLAIN output only reluctantly, the way you read the fine print on a lease. But the query plan is the only honest account of what the database engine is actually doing. The SQL is your intention. The plan is the machine's interpretation of your intention, and those two things diverge in ways that are sometimes shocking.
I once inherited a PostgreSQL instance with over two hundred indexes on a table that had forty columns. The team had been adding indexes for three years, each one a reasonable response to a specific slow query, none of them ever removed. The table had become so index-heavy that bulk inserts — a nightly ETL job — were taking four times longer than they should have, because every insert had to update two hundred index structures. Nobody had connected those two facts because the person who added the last index and the person who owned the ETL job had never been in the same meeting.
The lesson isn't "use fewer indexes." The lesson is that database performance tuning mistakes are often organizational before they are technical.
Tuning for the Query You Can See
Slow query logs are seductive. They give you a ranked list of offenders, a leaderboard of shame, and it feels productive to work your way down it. Fix the slowest query. Then the next. Then the next. Ship the changes, watch the p99 latency drop, feel good about yourself.
What this approach misses is query frequency. A query that takes two seconds but runs twice a day is not your problem. A query that takes eighty milliseconds but runs forty thousand times an hour absolutely is, and it may never appear in your slow query log if your threshold is set at, say, one second. You are optimizing for the dramatic outlier and ignoring the quiet, relentless drain.
This is one of the database performance tuning mistakes I see most often in teams that are otherwise technically sophisticated. They have observability tooling, they have dashboards, they have runbooks. But their mental model of "slow" is calibrated to human perception — seconds, not milliseconds — rather than to aggregate load. The database doesn't care that any individual execution of a query felt fast. It cares about the total work it has to do per unit of time.
The fix is to look at total execution time — duration multiplied by frequency — not just duration alone. Most query analytics tools will surface this if you ask them to. The problem is that you have to know to ask.
The Configuration Nobody Has Touched Since 2019
There is a configuration file somewhere in your infrastructure that was set by a person who no longer works at your company, tuned for hardware that no longer exists, under a workload that has since tripled. I am nearly certain of this. It is one of the most reliable facts I know about production systems.
Database defaults are conservative by design. The vendors who ship them are trying to run safely on the widest possible range of hardware, which means the defaults are often absurdly undertuned for a modern server with 256 gigabytes of RAM and NVMe storage. shared_buffers in PostgreSQL defaults to 128 megabytes. MySQL's innodb_buffer_pool_size defaults to 128 megabytes. These are numbers from a different era of computing, and if you have never changed them, you are leaving an enormous amount of performance on the table.
But the more insidious version of this mistake is the configuration that was changed, once, for a reason that made sense then and doesn't anymore. A max_connections value set high to handle a connection spike that the team later solved with a connection pooler — and now both the high limit and the pooler coexist, creating unnecessary overhead. A work_mem setting bumped up for a specific analytical query that has since been moved to a data warehouse, leaving every other query running with more memory than it needs and starving the system under concurrent load.
Configuration drift is a slow leak. It rarely causes a dramatic incident. It just makes everything slightly worse, indefinitely, until someone finally sits down with the documentation and a monitoring graph and asks: why did we set it this way?
Mistaking Caching for Solving
At some point in the life of almost every struggling database, someone suggests adding a cache. Redis, Memcached, an application-layer LRU — the specific technology varies, but the logic is consistent: if we stop hitting the database so often, the database will suffer less. This is true. It is also, frequently, a way of deferring the actual diagnosis rather than completing it.
Caching changes the shape of your problem. It does not always fix the problem. If your database is slow because a critical query is doing a sequential scan of a thirty-million-row table, a cache will help every request that hits the cached result and do nothing for the cache misses — which, depending on your cache hit rate and your data's cardinality, could still be a substantial fraction of your traffic. Worse, the cache can mask the underlying issue so effectively that the team stops investigating, and the debt compounds quietly beneath a layer of Redis that everyone has forgotten to monitor.
I'm not arguing against caches. I'm arguing against caches as a first response to database performance tuning mistakes, before you understand what is actually slow and why. A cache applied to a well-understood bottleneck is a powerful tool. A cache applied to a poorly-understood system is a blindfold.
What Tuesday Taught Me
The cold-burrito engineer on that February Tuesday eventually found her culprit: a missing index on a foreign key column, added to the schema in the Friday deploy, that turned a lookup used in a background job into a full table scan. The background job ran every five minutes. The table had twelve million rows. The math was not complicated, once you saw it.
What struck me, watching her work through it, was how many of the database performance tuning mistakes that contributed to the incident were invisible in isolation. The missing index was the trigger, but the nine-hour diagnosis happened because the slow query log threshold was too high to catch the job, because the monitoring dashboard showed replica lag but not its cause, because nobody had looked at the query plan for that background job since it was written two years earlier.
Systems fail at the seams. The tuning mistake is rarely just a technical error — it's a gap in attention, a handoff that didn't happen, an assumption that went unexamined long enough to become load-bearing.
The question worth sitting with isn't which index did we forget? It's what would we have had to believe, about our system and about each other, for this to have seemed fine?