DEV Community: Yogendra Singh

Database Bloat Is a Silent Tax on Every Write-Heavy System — Here's What Nobody Tells You

Yogendra Singh — Sun, 16 Aug 2026 17:27:03 +0000

Every write-heavy system I've worked on eventually hits the same wall, and it's rarely the wall anyone budgeted time for.

Not a traffic spike. Not a bad query someone forgot to index. Something quieter: the database itself, slowly getting heavier under its own weight, until one day a query that used to take milliseconds takes seconds, and nobody can immediately say why.

That's bloat. And in 18 years of building payment and transaction systems, it's cost me more debugging hours than almost any other single category of problem — precisely because it doesn't announce itself.

Why it happens, in plain terms

Most relational databases don't delete or update a row in place. Postgres marks the old row version as dead and writes a new one; MySQL's InnoDB does something conceptually similar with its own row versioning under MVCC. This is by design — it's how databases give you consistent reads without locking half the table.

The catch: those dead rows don't vanish. They sit there, taking up space in the table and every index on it, until something goes and reclaims them. In Postgres, that's VACUUM. In InnoDB, it's a mix of purge threads and, eventually, a table rebuild if things get bad enough.

If your write volume is high — lots of updates, lots of deletes, high-churn queues or state machines — you can generate dead rows faster than routine cleanup reclaims them. The table keeps growing on disk even though the logical data isn't. Indexes get fatter and less cache-friendly. Every query touching that table pays a small, invisible tax. Multiply that tax by every request, every day, for months, and you get exactly what I saw once: a single-character logic bug in how we handled a status flag turned into 53 seconds of unexplained latency, because it was quietly generating far more row churn than anyone had accounted for.

Why it's so easy to miss until it's expensive

A few reasons this class of problem slips past normal engineering discipline:

It degrades gradually, not suddenly. There's no alert that fires at "20% bloated." By the time it's visible in your latency graphs, it's usually been building for weeks.
It doesn't show up in code review. The bug isn't in your query. It's in the pattern of writes your query generates, which is much harder to reason about statically.
Standard monitoring doesn't surface it well. You'll see slow queries. You often won't see why they're slow, unless you're specifically looking at table and index bloat metrics — which most teams aren't, until they've been burned once.
Autovacuum/purge settings that were fine at launch stop being fine at scale. The defaults are tuned for a generic workload, not your specific write pattern. Nobody revisits them until something breaks.

What's actually worked for me

Treat bloat as a metric, not an incident. Track table and index bloat percentage the same way you track p99 latency or error rate — as an ongoing dashboard, not something you check when things are already slow.

Match cleanup aggressiveness to write pattern, not table size. A small table with extremely high churn (think: status flags, counters, queue-like tables) often needs more aggressive vacuum/purge tuning than a much larger, mostly-append table. Tune per table, not globally.

Be suspicious of "just add a flag column." Status flags, soft-delete columns, and toggle-heavy schemas are the most common source of the update churn that drives bloat. They're also the easiest thing for an engineer to add without thinking about the write amplification they cause. I've learned to ask "how often does this column change, and what does that do to the row?" before approving that kind of schema change now.

Build the runbook before you need it. Know in advance what a manual bloat-reclaim operation costs you — how long a table rebuild or full vacuum takes, whether it locks writes, what your rollback plan is. Figuring this out during an incident, on a production payments table, is a bad place to learn it.

The real lesson

Bloat isn't really a database problem. It's a systems-thinking problem — it's what happens when the shape of your writes over time isn't something anyone is explicitly tracking, only the shape of your reads. The fix isn't a smarter query. It's treating write patterns as a first-class design concern, with the same rigor you'd apply to an API contract.

The systems that don't get bitten by this aren't the ones with cleverer engineers. They're the ones who made "what does this write pattern do to the table over six months" a normal question to ask in design review — before it became a normal question to ask during an incident review.

What's the sneakiest "invisible tax" problem you've hit in a production system — the kind that doesn't show up until months in? Curious what others have run into.

One '%' Cost Us 53 Seconds: A Redis, MySQL Buffer Pool Story

Yogendra Singh — Tue, 04 Aug 2026 18:33:24 +0000

Every engineering team has that one meeting. Someone proudly announces,

"We reduced database traffic by 95% using Redis!"

Everyone smiles. Graphs look beautiful. CPU usage drops. Someone even starts preparing a "Performance Improvement" presentation.

Then...
Production throws an alert. A query that had been taking 9 seconds suddenly starts taking 62 seconds. Naturally, Redis becomes the prime suspect.

Except...
Redis wasn't the problem. It merely exposed a problem that had always existed.

The Background

We had an entity_attribute table that stored dynamic attributes.

entity_id	key	value
101	STATUS	ACTIVE
102	TYPE	PREMIUM
103	STATUS	BLOCKED

Almost every API fetched attributes by entity_id. Those lookups were extremely frequent.

To reduce database load, we cached the attributes in Redis. Instead of hitting MySQL thousands of times every minute, requests were served directly from memory.

The result?

Lower database CPU ✅
Lower latency ✅
Happy developers ✅ Until one query reminded us that production always gets the last laugh.

The One Query That Couldn't Use Redis

Most lookups were by entity_id. But one API searched using the last few digits of the value. The query looked like this:

SELECT entity_id
FROM entity_attribute
WHERE value LIKE '%123';

Notice the leading wildcard. That tiny % completely changes how MySQL works.

Why LIKE '%123' Is Expensive

Indexes are like dictionaries. They help MySQL quickly locate values that start with something.

For example:
WHERE value LIKE 'ABC%'
can efficiently use an index.

But
WHERE value LIKE '%123'
asks MySQL:

"Find every value that ends with 123."
Since MySQL doesn't know where those values begin, it cannot efficiently traverse a normal B-tree index. Instead, it has to inspect a huge number of rows.

In other words...
The leading % quietly disables one of the database's biggest performance advantages.

Why Did It Become Worse After Redis?

Before Redis, this table was one of the hottest tables in MySQL. Thousands of reads kept its pages inside the InnoDB Buffer Pool. Even though the query performed a large scan, much of the data was already in memory.

The query was slow...
but tolerable.
Around 9 seconds.

After Redis, almost every lookup disappeared. MySQL gradually evicted those pages from the Buffer Pool.

Now the same scan had to fetch data from disk. Nothing about the SQL changed. Nothing about the indexes changed.

Only one thing changed. The table became cold.

The result?
9 seconds → 62 seconds.

Redis didn't slow MySQL. It simply stopped keeping the table warm.

The Investigation

At first, everyone blamed Redis. Then we looked deeper. Execution plans were nearly identical. The real difference was physical I/O.

The query wasn't CPU-bound anymore. It had become disk-bound. That's when we realized something important.

Even if we warmed the Buffer Pool again...
The query itself was fundamentally inefficient.

We needed a better query.

The Real Fix

Instead of searching using
WHERE value LIKE '%123'
We extracted the searchable suffix into a Generated Column.
For example:

ALTER TABLE entity_attribute
ADD COLUMN gen_val VARCHAR(3)
GENERATED ALWAYS AS (RIGHT(value,3)) STORED;

Then we indexed it.
CREATE INDEX idx_gen_val ON entity_attribute(gen_val);

Now the query became
SELECT entity_id FROM entity_attribute WHERE gen_val = '123';

No wildcard. No table scan. A simple indexed lookup.
Exactly what MySQL loves.

The Result

Instead of asking MySQL to inspect nearly every row...

We gave it a proper index. The query became dramatically faster.

More importantly...
Its performance was now independent of whether the table happened to be warm in the InnoDB Buffer Pool.
The optimization wasn't just faster. It was predictable.

Lessons Learned

Cache doesn't fix bad queries. It often hides them.

Lower database traffic changes memory behavior.
Redis reduced reads so effectively that MySQL no longer kept this table in the InnoDB Buffer Pool.

Leading wildcards are expensive.

LIKE '%123'
is one of those queries that should immediately make every DBA slightly uncomfortable.

Generated Columns are underrated.
They let you convert an expensive expression into something MySQL can index efficiently, without changing the original data.

Final Thoughts

Redis wasn't the villain. The Buffer Pool wasn't broken.
MySQL wasn't slow.
Our query was asking MySQL to do something it was never optimized to do.
Redis simply removed the accidental performance boost that had been masking the problem.
Sometimes production doesn't create bugs. It reveals assumptions. And in our case, the biggest optimization wasn't Redis.

It was replacing one innocent-looking % with an indexed generated column.

This may not be the perfect solution. Comment what could have been done better.