3.5 Million Database Messages Against a Thirty Minute Recovery Window

Thirty minutes was all we had before the recovery job would be stopped, and the first version was headed toward 3.5 million separate messages to the database.

A site on a client-facing web platform had been deleted. Bringing it back meant copying its old records from a backup: units, events, results, and the other pieces that make the site hang together. The copy itself was only half the problem. When an event was put back, it received a new number. Every result that belonged to that event had to be pointed at the new number. If any were left on the old one, the site could look repaired while its results were tied to the wrong event, or to no event at all.

The careful version

We had already built the careful version. For every old event number and new event number, it sent a separate instruction to change one kind of record. It was easy to follow. It was also the kind of approach that feels sensible when you are handling one unit at a time. You can picture each change, check it, and move to the next one.

Then the real job turned out to be an account with 327 units.

The same small instruction had to be repeated for many events, many kinds of records, and the saved copies of older records too. What had been a tidy little loop became about 3.5 million individual requests. Each one had to leave the recovery job, reach the database, get an answer, and make the trip back before the next one could start. None of those pauses looked dramatic by itself. Put millions of them in a row, though, and the job could not finish inside its thirty minute limit.

That was the cost of our first answer. Every step in it was correct, and it was still too slow to be useful once the number of units got big.

One list instead of millions of trips

The fix was a temporary table. That is just a short-lived list inside the database, used while a job is running and then thrown away. We put every old event number beside its new event number in that list, along with the unit it belonged to.

That changed the conversation completely. Instead of asking the database to make one change, then another, then another, we gave it the whole address book first. The list was loaded in groups of 500 entries so the instructions stayed manageable. Then each kind of record received one broad instruction: find every item whose unit and old event number match this list, and replace that old number with the new one next to it.

It is the difference between changing the forwarding address on one envelope at a time and handing the post office a clean sheet of every address that changed. The destination still matters. The names still matter. But the person doing the work is no longer stopping to make a new trip for every envelope.

The part we would not trade for speed

Different units can reuse the same old event number. A change based only on the number could point one unit’s result at another unit’s event. So every match used two pieces of information together: the unit name and the old event number. The faster version kept that same rule, and only the way the instructions were delivered changed.

When the new route turns on

We also did not force the bigger method onto every recovery. A single account does not need the extra setup of making and filling a short-lived list. For that smaller case, the original approach stays easier to trace. A plan-only run that makes no changes also stays on the no-write path. The new route only turns on when there are at least two accounts and the recovery is actually allowed to change records.

We made the job say which route it had taken and report its progress as accounts were handled. That sounds small, but it matters when a recovery takes long enough for somebody to wonder whether it is working or stuck. A plain line saying that the faster route is in use gives the next person something real to check.

The lookup card

One more problem was hiding behind the faster instructions. Giving a database a long list does not guarantee that it can find the matching records quickly. It needs an index, which is a lookup card that helps it find the right drawer without opening every drawer in the room. In this case, the useful card paired the unit name with the event number. Without that, a few large instructions could still waste time searching through whole tables. The list and the lookup card had to work together.

What it came to

The recovery went from roughly 3.5 million separate messages to a few hundred: a couple of short-lived lists, several groups of 500 entries, and one broad change for each kind of record. The smaller jobs kept their readable path. The large account had a path that could actually finish.

If you have a recovery or migration that must run inside a fixed time window, count how many separate requests it makes to the database before you trust it, because each step being right will not tell you whether it fits. And check that the database has an index that fits the match, since a short list of large instructions can still crawl without one.

dxdev LESSON · paste into your AI coding agent
LESSON: One database round trip per record turns a 30-minute deadline into a math problem you lose
SOURCE: dxdev.com/everyday-ai/thirty-minutes-and-3-5-million-messages
WHAT HAPPENED: A recovery job had to repoint every result and record at a newly-assigned event id after copying a deleted site back from backup. The first version sent one database instruction per old-id-to-new-id remap, per kind of record, which was easy to follow for a single unit but scaled to roughly 3.5 million separate round trips for an account with 327 units, blowing well past the recovery's 30-minute limit. The fix loaded every old-id/new-id pair into a temporary table in batches of 500, then ran one broad UPDATE per record type that joined against that table, matching on both unit and old id together (not old id alone, since the same old id can belong to different units) so the rewrite stayed exact while dropping the round-trip count to a few hundred.
THE RULE: A loop that sends one request per item to a database scales linearly with the item count and can miss a hard deadline that a single set-based operation against a batched staging table would clear easily; the fix is changing the shape of the work, not making the loop faster.
CHECK MY CODE, then report PASS or FAIL with file:line for each:
  1. Any loop that issues one INSERT/UPDATE/DELETE per item where the same records could be matched and changed in one set-based statement joined against a batch of the same items.
  2. Any id remap or lookup that matches on a single column when that id is only unique within a narrower scope (e.g. per-account, per-unit), creating a collision risk across scopes.
  3. Any newly-added bulk join or bulk UPDATE with no supporting index on the columns it joins or filters on.
THEN PRINT: a table (check, PASS/FAIL, evidence, fix) + a verdict (applies / partially / OUT_OF_SCOPE / no) + the single most important next action.