+1 (541) 238-9429
QueryKings

The table that outgrew the box: partitioning, archiving, and when neither is the answer

Sooner or later a team sends us a row count with a question mark after it. Six hundred million rows in events. Nine hundred gigabytes in audit_log. The implied question is "is this too big?", and the honest answer is that size on its own is not a problem. InnoDB does not slow down because a table is large. B-tree lookups grow with the logarithm of row count, and the difference between a four-level and a five-level index is one more page read.

What actually hurts is one of four specific constraints. Naming which one you have hit determines the fix, and it is usually not the fix people arrive with.

The four constraints

1. The working set stopped fitting in the buffer pool. Queries that used to be memory-resident now touch disk. The tell is a falling innodb_buffer_pool_read_requests to Innodb_buffer_pool_reads ratio, rising read IOPS, and latency that got worse gradually rather than at a deploy. Note the word working set: a 900 GB table whose queries only ever touch last week's rows has a small working set and is perfectly happy on a 64 GB buffer pool.

2. Maintenance operations no longer fit in a window. The ALTER takes eleven hours. The mysqldump takes longer than the backup window. Restoring for a point-in-time recovery drill takes a day you do not have. This is the constraint that most often justifies real structural change, and the one teams notice last.

3. Deleting is more expensive than inserting. Retention cleanup generates more locking, more binlog, and more purge work than the writes it is cleaning up after. If you have read our note on history list length, this is where those symptoms come from.

4. One writer is no longer enough. Write throughput is capped by a single primary's IO and redo throughput, and you cannot buy a bigger box. This is the rarest of the four, and the only one partitioning cannot help with at all.

Before choosing a remedy, get the numbers. Table and index sizes:

SELECT table_name,
       ROUND(data_length/1024/1024/1024, 1)  AS data_gb,
       ROUND(index_length/1024/1024/1024, 1) AS idx_gb,
       table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC
LIMIT 20;

table_rows is an estimate for InnoDB; treat it as an order of magnitude, not a count. Then ask the question that decides everything downstream: what fraction of this table does production actually read in a day? If it is under ten percent, you have an archiving problem. If it is most of it, you have a scaling problem. They have different answers.

Archiving: the boring option that usually wins

If reads concentrate on recent data, the cheapest structural change is to stop storing old data in the hot table. Copy it to an archive table, an archive instance, or object storage, and delete it from the source.

The part that goes wrong is the delete. A single DELETE FROM events WHERE created_at < '2025-01-01' on a hundred million rows holds locks for the entire statement, writes one enormous transaction to the binlog, stalls replicas behind that one event group, and pushes the history list into the millions. Chunk it instead:

-- repeat until row_count() = 0, with a pause between iterations
DELETE FROM events
WHERE created_at < '2025-01-01'
ORDER BY id
LIMIT 5000;

Four details make this safe. The WHERE column must be indexed, or each chunk scans the table. ORDER BY on the primary key keeps each chunk a contiguous range rather than a scattered set of locks. The sleep between chunks is what lets replicas keep up — make it adaptive, reading Seconds_Behind_Source (or, better, a heartbeat table) and backing off when lag rises. And run it as many small transactions, never one loop inside one transaction.

One thing chunked deletes do not do: give the space back. InnoDB marks the pages reusable for that table, but the file on disk stays the size it was. To return space to the filesystem you need a rebuild — OPTIMIZE TABLE, which on InnoDB is an online ALTER ... FORCE, or a copy-based tool. That rebuild has the same cost and replication profile as any other large table rebuild, so plan it as a separate piece of work rather than assuming it comes free with the cleanup.

Partitioning: what it is and is not good for

Partitioning splits one logical table into several physical ones on a partitioning key, transparently to the application. In MySQL 8.0 this means InnoDB native partitioning. What it genuinely buys you:

  • Instant retention. ALTER TABLE events DROP PARTITION p2024_11 removes a month of data in roughly the time it takes to unlink a file. No row locks, no binlog flood, no purge backlog, and the disk space comes back immediately. For high-volume append-only tables with a retention policy, this alone is the reason to partition.
  • Partition pruning. If a query's WHERE clause constrains the partitioning key, the optimizer only touches the relevant partitions. Verify it — do not assume it:
EXPLAIN SELECT ... ;   -- read the `partitions` column

If that column lists every partition, you are getting none of the benefit and paying all of the cost.

  • Smaller per-partition indexes, which can improve buffer pool behaviour when access is time-local.

What partitioning does not buy you: it is not a general performance feature. Point lookups by primary key are not faster on a partitioned table, and can be marginally slower. And there are two hard constraints people discover late:

  • Every unique key, including the primary key, must contain every column of the partitioning key. Partitioning orders by created_at means the primary key becomes (id, created_at) — which changes what REFERENCES clauses and ORM assumptions can do, and widens every secondary index.
  • Partitioned InnoDB tables do not support foreign keys. Not on them, not to them. On schemas that lean on FKs this ends the conversation.

Queries that do not constrain the partitioning key get worse: the optimizer opens all partitions, and a scan across two hundred partitions costs more than one scan of one table. Partitioning by customer_id when every report filters by date is a common way to make a system slower while believing you have tuned it.

A concrete RANGE layout

The common, defensible shape is RANGE on a date expression, one partition per month, with a maintenance job that adds the next month ahead of time and drops the oldest:

ALTER TABLE events
  PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p2026_02 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION pmax     VALUES LESS THAN MAXVALUE
  );

Two operational notes. Keep a MAXVALUE catch-all so inserts never fail with "table has no partition for value" if the maintenance job misses a run — then reorganise it rather than adding past it. And automate partition creation with monitoring on it; the most common partitioning incident we are called for is not a query problem, it is a cron job that stopped six weeks ago and nobody noticed until pmax held forty percent of the table.

The initial ALTER TABLE ... PARTITION BY is a full table rebuild. On a large table that means a copy-based online schema change, with all the planning that implies. Partitioning is cheap to live with and expensive to adopt, which is an argument for deciding early on tables you already know will grow.

When the answer is sharding, and how to tell honestly

If a single primary cannot absorb the write rate, no amount of partitioning helps — every partition still lives on that one server. The real options are functional separation (move a heavy table family to its own instance, which is far less work than people assume and buys years), or horizontal sharding by tenant or entity key with a routing layer in front.

Sharding is the most expensive decision on this list. It changes transactions, joins, unique constraints, schema migrations, and every report you have. We recommend it when write throughput or dataset size genuinely exceeds one machine, and we recommend against it when the true problem is a missing index or a retention policy nobody wrote. The difference is usually settled in an afternoon with the slow query log and a growth chart, and that afternoon is much cheaper than the wrong answer.

A short decision path

  1. Is the hot working set small relative to the table? Archive, with chunked deletes and an adaptive lag throttle. Revisit in a quarter.
  2. Append-only with a clear retention period, and every important query filters on the time column? Partition by RANGE on that column, drop partitions for retention, verify pruning in EXPLAIN.
  3. Foreign keys on the table, or queries that ignore the candidate partitioning key? Do not partition. Fix indexes, archive, or separate the table onto its own instance.
  4. Single-primary write ceiling, confirmed by measurement rather than by forecast? Functional separation first; sharding only if that is not enough.

Most tables we are asked about land on step one or two. Partitioning has a reputation as the serious answer for serious data, and it is a good tool with a narrow brief: cheap retention and time-local access. Used outside that brief, it adds a constraint to your primary key and a scan across two hundred partitions, and gives back very little.

If you are weighing this decision on a table you cannot afford to rebuild twice, send us the schema, the top queries by total time, and the growth rate. The shape of the right answer is usually visible in those three things.