Engineering Journal · ENGINEERING · April 16, 2024 · 16 min read

We Did Not Need a Bigger Database

When a page is slow, the first offer is often more hardware. Most of the time the application is asking the database a foolish question, and a larger instance will only answer it faster for a while.

By Golam Sorwar, Tech Lead and Full Stack Engineer in Dublin.

The conversation usually starts the same way. A screen that used to feel fine now spins. A report times out near the end of the month. Someone says the database is under pressure. The next sentence is almost automatic: maybe we need a bigger instance, more memory, a read replica, another Redis layer in front of the pain. I have said that sentence. I have also spent days proving it was the wrong one. The uncomfortable pattern, at least in the products I have lived in, is that the database is rarely innocent and rarely the first thing that should grow. It is being asked to do application work: load graphs that nobody narrowed, sort unindexed columns for a UI that only needs a page, compute the same dashboard on every request, or walk a relationship in a loop because the code was easier to read that way. This is not a lecture about adding indexes. Indexes matter, and I have written about how we choose them. This is about the decision that happens before that work is even allowed: are we going to buy headroom, or are we going to change the question the system asks?

Context

The applications were ordinary Laravel products on MySQL, with Redis already in the building for cache and queues. They were not hyperscale. They were busy enough that a bad query was felt by staff who used the same screens all day, and by students or agents who hit a small number of hot paths. Volume was uneven. Enrolment windows, payment deadlines, reporting days. The system was fine on a quiet Tuesday and rude on a day that mattered.

The team reflex toward infrastructure was understandable. Vertical scale is a ticket. It has a vendor page. It does not require arguing about an Eloquent relation in a ten-year-old module. Application work requires someone to own a slow path, and slow paths live in the least fashionable code.

We also had the usual monitoring: instance CPU, memory, disk IO, a few application timers. What we did not always have was a habit of asking which query, for which screen, with which parameters, caused the room to feel slow. Without that, every graph becomes a Rorschach test. You see what you already wanted to buy.

The real problem

The apparent problem was capacity. The box was working hard. Connections stacked. A replica sounded mature. Cache sounded responsible.

The deeper problem was amplification. One user action became dozens or hundreds of queries. A list endpoint loaded children, then grandchildren, then a helper that ran a count per row. A dashboard recomputed a permission-heavy report that was identical for every staff user in the same role for minutes at a time. A search that allowed "just one more filter" defeated the only index that had been doing real work.

There is a second deeper problem: we often cache as a way of not admitting the read model is wrong. Redis will happily store a blob that took two seconds to build. Then invalidation becomes a folklore project, and you have a fast lie or a slow truth, depending on the day. I like Redis. I do not like it as a witness protection programme for a query we are afraid to look at.

Infrastructure can still be the answer. If the working set no longer fits, if you are CPU-bound on honest work, if a replica would isolate reporting from checkout, buy the thing. The failure is buying it so you can postpone naming the query.

Constraints

We could not take the hot screens down for a rewrite. Staff would simply export to CSV and leave, which is a worse architecture than a slow page.

We could not pretend we had infinite time to perfect every list. Some reports were going to remain heavy. The decision was which heaviness was honest and which was accidental.

Schema change was possible but not free. An index is a write tax. A new table for a read model is a consistency tax. Both are cheaper than a class of instance you will be shy to downsize later, but they still need owners.

Cache was already in play, which meant some of the worst paths were hidden except when they missed. A cache hit is not proof of a good design. It is proof that yesterday's answer was reusable.

And we had to keep mobile and web clients working. If the fix was "the API now returns less," the clients had to be able to live with less, or we had to add a dedicated shape instead of starving the old one.

Options considered

Scale the primary. Advantage: sometimes it works immediately, which is a hell of a drug. Disadvantage: you have not reduced work, only the time each unit of work takes, until growth eats the gift. Risk: the next busy period returns with a larger invoice and the same queries. Maintenance of the application stays the same. The business pays rent for a shape nobody improved.

Add a replica and send reports there. Advantage: this can be the right isolation when reporting and checkout truly fight. Disadvantage: replication lag becomes a product bug the first time someone pays and then opens a report. Risk: you copy a bad query to a second machine and call it architecture. Complexity in routing and failover is real. I would do it for isolation, not for shame.

Cache the world. Advantage: the next request is cheap if you get the key right. Disadvantage: you now own invalidation, stampedes, and the support ticket that says the number is wrong. Risk: you cache a permission-sensitive payload too broadly, or you cache too narrowly and get no hit rate. Maintenance shifts into a second correctness problem. Business impact is ugly when a cached dashboard disagrees with a live invoice.

Fix the access path. Narrow selects, eager load what you actually need, stop N+1, give the list a cursor or a cap, push a truly expensive report into a job and a stored result. Advantage: the database does less, which is the only scale factor you own forever. Disadvantage: it is slower to show in a procurement meeting. Risk: you micro-optimise the wrong screen. Complexity is local if you stay honest. Maintenance usually improves because the code starts stating its data needs.

Build a small read model for one hot view. Advantage: you can index for the page you have, not the relations you inherited. Disadvantage: you write twice, or you project asynchronously and accept delay. Risk: the projection lags and people trust it anyway. This is the option I reach for when the screen is important and the relational truth is the wrong shape for it.

Decision

We did not start with a bigger instance. We started with a rule: no infrastructure ticket without a query story. Which request, which SQL, how often, what it did in EXPLAIN, whether the application issued it in a loop. If we could not say that, we were not ready to spend money.

Where the story was an N+1 or a missing composite index that matched a real filter, we fixed that first. Where the story was a dashboard that rebuilt a universe, we stopped rebuilding it on every request. Where the story was a report that belonged in the background, we took it off the request/response path instead of giving MySQL more CPU to fail more expensively.

I chose that order because I had already watched hardware hide bugs. A faster disk makes a tablescan less embarrassing. It does not make it a strategy. I also chose it because a small team feels every recurring cost. An instance you do not need becomes a quiet tax on every other decision.

We left the door open to scale. That was important. The decision was sequence, not religion.

Implementation / Process

We treated the slow path like an incident with a lower pulse. Capture the endpoint. Turn on enough query logging in a controlled way to see the shape, not to drown. Count queries on the page in development until the number stopped being a joke. Read the plan. Look at cardinality instead of guessing that an index on status would save us when status had three values and the filter was something else.

For lists, we asked a rude question: does anyone need the tenth thousandth row in this UI? If not, the UI had been lying about being a list. It was an export wearing a table tag. Exports can be jobs.

For dashboards, we asked whether the number had to be live to the second. Most operational numbers do not. They have to be trustworthy and fresh enough. A computed snapshot, rebuilt on a schedule or on the writes that matter, is often the whole architecture.

Redis entered as a specific answer, not a mood. Cache a snapshot that is safe to reuse, with a key that includes the tenant and the meaning of the number, and an invalidation path you can describe in one sentence. If you cannot describe the invalidation, you do not have a cache. You have a rumour with a TTL.

We did not need a new service. We needed the monolith to stop being shy about its reads.

// Count what a screen actually does before arguing about hardware.
Model::withoutEvents(function () {
    // enable query log in a staging replay, not as folklore
});

// Prefer a snapshot with a boring key over a clever cache of an entire graph.
Cache::remember(
    "ops:inbox-count:{$tenantId}:{$role}",
    60,
    fn () => Inbox::countFor($tenantId, $role)
);

Problems and failures

The first failure was local optimisation. We made one admin list respectable and left a related export that still walked the same graph. Users switched to the export and the database stayed busy. Performance work that ignores the escape hatches users already have is decoration.

The second was an index we added because it looked official. It helped a report we run rarely and slowed a write path we run constantly. Unused or wrongly ordered indexes are not free. They are a permanent comment in InnoDB.

Cache created a trust incident. A count was right enough until a permission changed and the key did not know. The number was fast and wrong. Fast and wrong is how you train people to ignore the dashboard, at which point you have paid for a UI that nobody believes.

We also wasted time arguing from production feelings. "It felt worse after the deploy" is a clue, not a measurement. Until we could replay the endpoint, we were just swapping anecdotes. That is an engineering process failure, not a MySQL one.

Communication with non-engineers was clumsy. "We are not upgrading the database" sounds like refusal. "The page is asking the database the same question two hundred times" is a sentence people can dislike for honest reasons. I learned to lead with the second.

Trade-offs

We chose application complexity over vendor simplicity in a few places. A snapshot table is more to maintain than a larger RDS class. I still prefer the snapshot if the team can own it, because the cost stays visible in the codebase instead of in a bill nobody revisits.

We chose slightly stale operational numbers over live-but-random latency. That is a product decision dressed as a technical one. Some rooms need live. Most rooms need calm.

We did not choose purity. Some paths stayed cached and a bit ugly. Some indexes stayed because dropping them felt riskier than proving they were useless. Sequence does not require zeal.

Result

The useful result was that a few screens stopped being folklore. People could open them on a busy day without a ritual. The database still worked hard when the business was actually doing a lot. That kind of hard looks different in the graphs: it is fat work, not frantic work.

We still scaled other things later when the case was honest. I do not consider that a contradiction. The point of refusing the first bigger-database conversation was to make the later ones boring and specific.

I will not quote a latency number I did not keep. I will say the team started bringing EXPLAIN plans to discussions that used to start with instance types. That was the cultural result I actually wanted.

What I would do differently today

I would have instrumented the hot endpoints before the first panic, not during it. Adding timing and query counts when people are already angry makes the work feel like blame. Doing it in peacetime makes it a dashboard.

I would have been harsher about unbounded admin lists. We kept a few "load everything" views because a stakeholder liked to scroll. That is not a requirement. It is a habit, and it is expensive.

I would also have separated "reporting MySQL" from "we need a replica" earlier as a language habit. Those are not the same project. One can be a job writing to a table. The other is topology. Mixing the words makes people buy topology.

Broader lesson

Infrastructure is a way of paying for work you have already agreed to do. Architecture is a way of refusing work that should not exist. When a system feels slow, ask which of those you are looking at.

If you cannot name the query, you are not ready to name the instance. If you can name the query and it is honest, then buy the machine and do not perform a morality play about it.

Closing thought

A bigger database is sometimes the right purchase. It is almost never the right first sentence. Make the application tell you what it is asking. Then decide whether you want to ask less, ask less often, or pay more to keep asking the same thing.