Partitioning by month, and the query that scanned all of them

An events table with four hundred million rows and an eighteen-month retention policy that had never been enforced, because the delete that would enforce it could not finish. Partitioning makes the delete instant and costs a change to the primary key, which is the part that takes planning.

The symptom

$ mysql -e "DELETE FROM events WHERE created_at < '2021-12-01' LIMIT 1000000"
# killed after 6h14m

$ mysql -e "SELECT table_rows, data_length/1024/1024/1024 AS gb,
            index_length/1024/1024/1024 AS idx_gb
            FROM information_schema.tables WHERE table_name='events'G"
table_rows: 412884102
        gb: 188.4
    idx_gb:  94.1

# 282 GB, of which roughly 60% is older than the
# retention policy nobody could enforce.

A delete of two hundred million rows is two hundred million undo records, an index update per row, and a replica applying every one of them. There is no batch size that makes this a good idea; it is the wrong operation.

Why it happens

Deleting rows is expensive by design — the engine has to preserve the ability to roll back and to serve concurrent readers a consistent view. Dropping a partition is a file operation, and the difference is not a factor, it is a category.

The fix

The primary key change that partitioning forces

-- the rule: every UNIQUE index must contain every column
-- of the partitioning expression.

-- before
PRIMARY KEY (id),
UNIQUE KEY uq_idempotency (idempotency_key)

-- partitioning by created_at therefore requires
PRIMARY KEY (id, created_at),
UNIQUE KEY uq_idempotency (idempotency_key, created_at)

-- which weakens the second one: the same idempotency key
-- may now exist twice in different months.

The weakened uniqueness constraint is the real cost and it has to be reasoned about rather than accepted. Here the idempotency window is twenty-four hours, so a duplicate across month boundaries is possible for one day a month — which was judged acceptable and is written down.

The migration to get there

-- a rebuild, on a 282 GB table, is not an ALTER
-- the route taken: a new table, backfilled, switched

CREATE TABLE events_new (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at  DATETIME(6) NOT NULL,
  type        VARCHAR(64) NOT NULL,
  payload     JSON NOT NULL,
  PRIMARY KEY (id, created_at),
  KEY idx_type_created (type, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2022_01 VALUES LESS THAN ('2022-02-01'),
  PARTITION p2022_02 VALUES LESS THAN ('2022-03-01'),
  -- ...
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

the backfill, over nine days:

  only rows within the retention window: 162M of 412M
  batches of 50,000, keyed on id
  a sleep proportional to replica lag
  dual writes to both tables for the duration
  a final catch-up pass, then RENAME TABLE

RENAME TABLE events TO events_old,
             events_new TO events;

which is atomic, and the old table is dropped a week
later once nothing has complained.

Not migrating the rows outside the retention window is what made this nine days rather than three weeks — the exercise was to stop keeping them, so copying them first would have been perverse. The old table staying for a week is the rollback.

Pruning, confirmed in EXPLAIN

EXPLAIN SELECT * FROM events
WHERE created_at >= '2023-06-01' AND created_at < '2023-07-01'
  AND type = 'order.placed';

-- partitions: p2023_06
-- one partition. 3.4M rows instead of 162M.

-- and the query that did NOT prune
EXPLAIN SELECT * FROM events WHERE id = 41208104;
-- partitions: p2022_01,p2022_02,...,pmax
-- every partition. the filter is on id, and the
-- partitioning expression is created_at.

Looking up by primary key scanning every partition is the counter-intuitive result and it follows directly: the engine cannot know which partition holds an id without a range on the partitioning column. Every lookup by id now needs a date alongside it, which is a change to every caller.

The query that scanned all of them

// what the code did, in 41 places
$event = Event::find($id);

// what it has to do
$event = Event::where('id', $id)
    ->where('created_at', '>=', $knownDate->startOfMonth())
    ->where('created_at', '<',  $knownDate->addMonth()->startOfMonth())
    ->first();

// and where the date is genuinely unknown — a support
// tool taking an id from a log line — the scan is
// accepted, and takes 900ms instead of 4ms.

Thirty-eight of the forty-one call sites already had a date in scope, because they had arrived at the id through a query that included one. The three that did not are support tools, and a nine-hundred-millisecond lookup in a support tool is a reasonable price for the retention story.

Maintaining partitions

-- monthly, on the 25th
ALTER TABLE events
  REORGANIZE PARTITION pmax INTO (
    PARTITION p2023_08 VALUES LESS THAN ('2023-09-01'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
  );

-- and the drop, which is the whole reason
ALTER TABLE events DROP PARTITION p2022_01;
-- 1.8 seconds

The job that adds next month’s partition has to run before the month starts, and its failure mode is that everything lands in pmax — which works, silently, until pmax is the size of the original table. Alerting on the row count in pmax is the monitor that catches it.

Verifying it worked

$ time mysql -e "ALTER TABLE events DROP PARTITION p2022_01"
real    0m1.812s        # was: could not finish

$ mysql -e "SELECT SUM(data_length+index_length)/1024/1024/1024 AS gb
            FROM information_schema.partitions
            WHERE table_name='events'"
112.4                   # was 282.5

$ ./bin/explain-top-20 | grep -c 'partitions: p'
18                      # 18 of 20 prune to one partition

$ mysql -e "SELECT partition_name, table_rows
            FROM information_schema.partitions
            WHERE table_name='events' AND partition_name='pmax'"
pmax  0                 # the guard: this must stay near zero

Eighteen of the top twenty queries pruning to one partition is the measurement that says the partitioning key matches the access pattern. The two that do not are the support lookups, which is expected and documented.

What this costs

A schema constraint that every future index must respect. Any unique index added from now on has to include created_at, which means any uniqueness rule is really a uniqueness-within-a-month rule, and somebody will add one without realising that.

It also breaks the assumption that a primary key lookup is fast, which is deep enough in most people’s instincts that it will not be questioned. The forty-one call sites were found by grep; the forty-second will be written next year by somebody who has never read this.