The first sign of trouble was a wave of gateway timeouts on a service that, as far as anyone could tell, hadn’t changed. No new deploy, no traffic spike worth mentioning, no obvious culprit in the logs. Callers were just timing out, and nobody debugging it from the outside would have guessed “there’s probably a slow UPDATE running somewhere” as the cause. It just looked like the service had fallen over.
The actual cause was a MySQL table that had to do two jobs at once: serve live inserts around the clock, and once a week, absorb a bulk update touching millions of rows. That update was quietly holding row locks for its entire duration, and every new insert that needed those same rows queued up behind it. Enough of them stacked up waiting, and the caller eventually gave up.
On paper, that’s almost too simple a problem statement to be interesting. A table serving live writes, and a batch job touching a lot of rows once in a while. And yet it took down a production service more than once before we actually fixed it properly. I think this pattern shows up more often than people realize, maybe in a different form. This is a story about what happens when one data store has to be good at two very different jobs at once, and what we did about it.
And yes, at some point you’ll probably ask the obvious question: wasn’t this predictable? Shouldn’t a design review have caught it? We’ll get to that, and the answer isn’t as satisfying as “we should have just tested at scale.”
The setup
The service in question is an internal service that accepts deletion requests. Teams onboard a data lake asset (Hive tables) with a column mapped to a label, and from then on, data owners submit deletion requests using that label with a value.
Before we go further, why do you even need a service or central platform to delete data from a Hive table? Why can’t the table owners just do it themselves? Many tables even today are simple Parquet or JSON formatted tables where deletion isn’t straightforward, since they lack support for Delete DML the way Delta or Iceberg table formats do. So when companies are mandated under GDPR/CCPA privacy laws to delete customer data on request, every data-owning team ends up implementing its own deleter (that’s a separate story in itself, how efficiently that delete actually works).
But compliance is honestly a small slice of it. We get tens of thousands of requests a day, cumulative across teams in the organization, and no, that’s not because millions of people are exercising a privacy right every single day. It’s a generic cleanup service, so it gets used for stale data, prod test data left behind from QA runs, internal cleanup initiatives like flagging inactive or dormant customer accounts for security, and yes, compliance deletes too, all funneled through the same pipe.
Once you build a solid “delete this ID, eventually” service, everyone with a cleanup problem wants to use it. And not every data asset owner is a data engineer. Some teams brought tables into the lake for retention because of other capabilities, so they don’t have the expertise or the bandwidth to maintain deletion pipelines on their own. That’s exactly how you end up with millions of rows a day landing on a table that was never designed for that kind of volume.
And that’s really the reason this wasn’t caught at design time. The service didn’t launch handling millions of rows a day. It started small, with a handful of privacy deletion requests a day, and the original schema was perfectly fine for that. Nobody sat down and designed for the volume it eventually saw, because that volume didn’t exist yet.
It grew gradually, team by team, use case by use case, as word got around that this was the place to send any “delete this eventually” problem. By the time the traffic looked nothing like what the table was built for, the schema had been live in production for a while, and there was no single moment that screamed “redesign this now.” It’s the kind of scaling problem that’s nearly impossible to catch in a design review, because at design time, the assumptions were correct. They just didn’t stay correct.
Deletion itself doesn’t happen the moment a request lands. Going and deleting one ID at a time across data lake assets just isn’t realistic at that volume, so the actual deletion runs as a bulk job on a weekly cadence, working through everything that piled up.
And that’s where the real problem starts. Not the deleting. The bookkeeping afterward.
Where it actually broke
When the weekly job finishes, the online service needs to know what happened to every request it processed. Not thousands. Millions, all at once, against the same table that’s taking live inserts the entire time.
The first version of this just ran a big UPDATE ... WHERE id IN (...) against that table. Technically, MySQL handled it fine. It didn’t crash, it didn’t corrupt anything. But it held row locks for the whole duration of that transaction, and anything else needing those rows queued up behind it. That’s the gateway timeout from the opening, traced back to its actual source.
Chunking the update helped, a little. Shorter locks, less damage per chunk. But it didn’t change the underlying fact that we were still running a heavy write against the exact table that live traffic needed to be fast and available. We were treating the symptom, not the disease.
The actual fix: stop sharing the table
Here’s the reframing that mattered. This was never really a “how do we make this UPDATE faster” problem. It was a “why is this UPDATE running against the same table that live writes depend on” problem.
So we split it. New requests stopped writing to the main request (primary) table, the one that gets bulk-updated. Instead, they go into a separate request queue (queue) table. Reads and writes are split: inserts always go to the queue, everything else (status checks, the weekly reconciliation) touches the primary. There’s still exactly one source of truth for what the current state of a request is. It’s just never the table catching all the live traffic.
Why not just two separate tables
There’s another option: keep the status in a separate table and let live traffic land in the primary table directly. We didn’t go that route, because we wanted a single source of truth for when a request came in, when it was updated, and so on. Also, an online data store isn’t an ideal place to keep a long history, so you’d typically offload that to something cheaper storage like S3 (with an external table pointing at it, for audit purposes). Between those two constraints, maintaining two separate tables for the same data didn’t end up being a clean solution.
Instead, new requests are insert-only into the queue table, and get merged into primary once an hour via an upsert that skips any request ID already present, just in case.
Rotating the queue without ever stopping writes
An insert-only table that never gets cleaned up eventually becomes its own headache. It just keeps growing, scans get slower, and there’s more to merge every cycle. So the queue table needs to rotate and get truncated periodically. The tricky part is doing that without ever blocking a live insert, and without losing or double-counting a single request along the way.
We run two physical queue tables with a view sitting in front of both, and the service always writes through the view. It has zero idea which physical table is actually active, or that a rotation is even happening. Repointing a view in MySQL is basically instant, so that moment of switching doesn’t leave a gap for anything to fall through.
The rotation itself isn’t a single script, though. It’s four stages, and we treat it as a resumable workflow rather than something we just hope runs to completion:
- Record which queue is active and which one it’s about to become, before touching anything.
- Switch. Repoint the view. From here on, new inserts land in the new table.
- Drain whatever’s left in the now-inactive queue into primary.
- Truncate the old queue once its contents are confirmed merged, so it’s ready to be reused next cycle.
Every stage gets checkpointed in a workflow table. If something dies halfway through, say a deploy at the wrong moment or a network hiccup, it doesn’t start over from scratch or risk merging things twice. It picks up exactly where it left off. That first stage exists specifically so a resumed rotation can confirm it’s actually starting from a known-good state, not just assuming it.
The weekly update, now boring
With writes routed away from primary, that original millions-of-rows update problem isn’t an issue anymore. It’s a chunked update of status straight to the primary table, and it’s allowed to take however long it takes, sometimes ten-plus minutes. Nobody notices, because nothing on the live path is waiting on it. New requests keep landing in the queue table the whole time, completely oblivious to whatever lock contention is happening over on primary.
Chunking still earns its keep here. An unchunked update brings its own problems, filling up redo logs, draining database resources, and even causing crashes if it isn’t handled correctly.
A detail that’s easy to overlook: partitioning
This next part feels almost too obvious to mention, but it made a real difference, so I’ll include it anyway. The primary table is range-partitioned by request_received_date, one partition per month, and we only keep the last two months of data live. Everything older gets backed up to the data lake for long-term retention and dropped from the online store.
At millions of rows a day, an unbounded table doesn’t stay fast forever, no matter how well the write path is designed around it. Keeping the live table small and bounded, and pushing history somewhere cheaper, isn’t a clever idea. It’s just good hygiene. But it’s the kind of good hygiene that’s easy to skip until you’re staring at a table that’s grown ten times bigger than it needs to be for anything anyone actually queries day to day.
The trade-off I’m not going to pretend doesn’t exist
Because new requests sit in the queue table for up to an hour before making it into primary, a status check that happens in that window comes back empty. The request is real, it’s just not visible yet.
In practice this barely matters for us. The deletion SLA is 14 days, and the merge runs hourly, so nobody’s realistically checking within that first hour in a way that changes anything. When someone does hit it, they sometimes resubmit, assuming it failed. That’s exactly what the idempotent merge is there to absorb gracefully.
I’ll be upfront: this gap is fixable if you actually need to close it. A status check could fall back to the queue table when primary comes up empty, and return “received, not yet processed” instead of “not found.” We chose not to build that. It happens rarely enough that adding a second lookup path to every single status check felt like more permanent complexity than the problem was worth. If your SLA is tighter than ours, or people genuinely check status within minutes of submitting, this is probably the first thing I’d add.
What actually changed
Live intake and the weekly status reconciliation no longer fight over the same rows. The update that used to threaten to take the whole service down now just runs on its own schedule, and nobody has to babysit it. The queue-and-rotate setup keeps the write path fast and bounded no matter how much volume built up that week, and the partitioning keeps the table itself from becoming the next bottleneck down the line.
What I’d tell someone hitting this for the first time
When a bulk update is causing contention, the instinct is almost always to make the update itself gentler: smaller batches, more throttling, run it at 3am. That helps, but it’s still treating the symptom. The question worth asking is whether that update needs to touch the same table that live traffic depends on at all. Once we answered that with “no, it doesn’t,” most of what looked like a database performance problem turned out to be a data-flow design problem instead.
The other thing I’d say is this. If you’re building anything that runs unattended on a schedule and has more than one step, assume it will get interrupted at some point, and design for that from day one. A rotation that always completes cleanly doesn’t need checkpointing. A rotation that eventually gets killed mid-flight by something completely unrelated absolutely does, and retrofitting that after the first failure in production is a much worse time to build it than doing it upfront.
Closing thought
The table wasn’t slow because of bad indexing or under-provisioned hardware. It was slow because we were asking it to do two genuinely different jobs, absorbing constant live writes and periodically shrugging off a multi-minute batch update, and treating that as one job. Splitting those responsibilities, even with something as unglamorous as a queue table and a view, let both halves work the way they were meant to. If you’ve run into this same shape of problem with a different store, I’d genuinely like to hear how you approached it.
One thing I glossed over here on purpose: how you actually delete records efficiently from a large volume Hive table that isn’t on Delta or Iceberg, and so has no Delete DML to lean on. That’s a real problem on its own, with its own set of trade-offs, challenges and deserves its own write-up rather than a paragraph tacked onto this one.