Most of your database doesn’t need to be hot

On archiving the body of a transactional table to cold storage, and keeping the read path intact.

A database we maintain crossed a threshold last year. Backups got slower. Replication lag spiked on busy mornings. The disk warning, which we had been politely ignoring, became a disk emergency.

What was less standard: ninety-five percent of the storage lived in a single child table. The parent header table was almost trivial next to it. Every other table in the system, combined, was smaller than that one child. We had been treating the database as one big undifferentiated thing. It was actually a small fast database with a five-gigabyte appendix that happened to be ninety-five gigabytes long.

What follows is the playbook we ended up with for cleaving the body off the header. Most of it is unglamorous. None of it is novel. The number of teams I have watched reinvent it from scratch is the reason I am writing it down.

The bulk isn’t where you think it is

Most teams diagnose a slow database by looking at the busiest table. That is usually the parent header, the one with the most read traffic, the most join activity, the most cache pressure. It is almost never the storage problem.

The storage problem is whichever child table carries the heavy payloads. An audit log has events, each a few hundred bytes, but each event has a payload that is a few kilobytes of JSON. A document system has documents, each with a row of metadata, but each document has a body that is megabytes of text. A workflow system has actions, each cheap, but each action has a snapshot of the state at the time, which is not.

The math goes like this. A small header row, say five hundred bytes, multiplied by tens of millions of rows is somewhere between five and ten gigabytes. Manageable. A body row that averages five kilobytes, multiplied by tens of millions, is fifty to a hundred gigabytes. That is your problem.

Before any plan, query information_schema. Sort tables by data_length + index_length descending. Look at the top three. One of them is the patient.

Fig 1. What you see is the header. What you pay for is the body.

Don’t delete a billion rows in one query

Once you know which table is the problem, the next instinct is to clean it up. Pick a cutoff date. DELETE FROM the_table WHERE created_at < :cutoff. Done.

This is how you turn a storage problem into an availability problem.

A single large DELETE does three things you do not want. It writes one enormous binlog event, which freezes replication while replicas chew through it. It holds row-level locks across the entire range, which blocks every transaction that touches the range. It bloats the undo log, which the database holds on to until the transaction commits, at which point you discover your free space has gone down before it goes up.

The right shape is a chunked delete. Pick a small batch size, somewhere between one and ten thousand rows depending on row width. Loop. Sleep a small amount between iterations.

DELETE FROM the_table
WHERE created_at < :cutoff
ORDER BY id
LIMIT 5000;

Run this in a loop, with a small sleep between iterations, ideally during a low-traffic window. Each iteration writes a small binlog event, holds locks for milliseconds, lets replicas keep up. Total wall clock is longer. Total disruption is much smaller.

The is_archived flag and the read-stitch path

Deletion is for data you are willing to lose. Most of the heavy body data is not in that category. Archival, not deletion, is the actual move. The body goes to cold storage. The header stays.

This only works if the application can still find the archived body when it needs to. Two patterns to get right.

First, a flag on the header row, call it is_archived, that says “the body has been moved.” Second, a read path that checks the flag before deciding where to read from.

The read path looks like this:

GET /events/:id
  → load the header row
  → if is_archived = false, JOIN to the body table, return both
  → if is_archived = true, fetch the body from cold storage, return both

The invariant: the body lives in exactly one place at a time. Either the database has it, or cold storage has it. Never both. Never neither. The flag is the source of truth for “where.”

This pattern keeps most of the application code unchanged. The endpoints that returned a joined shape still return a joined shape. The difference is in one helper, where the source of the body changes based on a flag. Callers do not have to know.

Fig 2. The header stays. The body goes cold. A forwarding label says where.

Fire an event so the rest of the world catches up

Your database is not the only system that has a copy of this data. The search indexer has a copy. The data warehouse has a copy. The analytics pipeline has a copy. They were all built on the assumption that the body lives in the database. When you move it, none of them know.

The fix is not to poll for changes. The fix is to make the archive operation a message-bus event, and let each downstream system handle it on its own.

When the archive job flips the flag and writes the body to cold storage, it also publishes:

{ "event_type": "row_archived",
  "table": "event_payloads",
  "row_id": "...",
  "cold_storage_path": "..." }

Search indexers consume the event and move the document from the live collection to the archived collection. Data warehouses consume it and update their partition view. Analytics consumes it and stops counting the row as live. Each downstream system handles its own copy on its own clock.

The discipline that makes this work: every consumer must be idempotent. Receiving the same event twice should leave the system in one consistent state. Most message buses cannot promise exactly-once delivery. The consumers can be written to not care.

Fig 3. The read path checks one flag. Callers never see the join boundary move.

The reopen path

Archival is a one-way move until someone needs the data back. Eventually someone always does. A compliance request, an investigation, a question from finance about a quarter-old transaction. The body has to come back hot, for a while at least.

The reopen flow is the archive flow in reverse. Fetch the body from cold storage, write it back to the body table, flip the flag off, publish a reopen event. Same invariant applies. The body lives in exactly one place at a time. After reopen, that place is the database again.

The tricky cases are when something goes wrong mid-reopen. The body got fetched, the database write failed, the flag never flipped. Now you have a body partially written in the database, the cold-storage copy intact, the flag still saying archived. The fix is to order operations so the flag flips last. Fetch, write, flip. If the write fails, the flag stays archived, the cold-storage copy is intact, the next read still works correctly. Retry is safe.

What it adds up to

Every transactional database eventually grows a child table that becomes the storage. The body of a row outpaces the row itself. The header was fine. The body was the problem.

The playbook is the same every time. Find the table that carries the bulk. Move the heavy body to cold storage, keep the small header live. Hold a flag that says where the body is. Stitch the read path so callers never know. Tell the rest of the system through events, and write every consumer to handle the same event twice without flinching. Build a reopen path that is the archive in reverse, with the flag flipping last so a failed reopen is recoverable.

Most of it is unglamorous. None of it is novel. The number of teams who get to seventy gigabytes of binlog per day before they start writing this down is the reason it is worth writing down.