MariaDB Range Partitions: The Maintenance Cron Nobody Was Watching

The table was called events, and it did exactly what it said: 900 million telemetry rows a year, queried almost always by time range, retained for thirteen months, then gone. It was partitioned by RANGE COLUMNS on event_date — one partition per month, plus a final partition defined as VALUES LESS THAN MAXVALUE to catch anything the monthly definitions missed. A cron job on an old utility host ran on the 25th of each month and added the partition for the month after next. In March the utility host was decommissioned as part of a cleanup. The cron went with it. Nobody got paged, because nothing broke: inserts kept working, queries kept working, and every new row quietly landed in pmax, the MAXVALUE partition, which is exactly what a catch-all is for. Eleven months later pmax held 900 million rows, the monthly partitions held the twelve oldest months, and queries for recent data — the hot path — were scanning a 900-million-row partition because from the partitioner's point of view that is where recent data lived. The fix, REORGANIZE PARTITION on pmax, had to copy every one of those rows into new monthly partitions while the table stayed online under a metadata lock queue that made the evening deploy window very interesting.

Range partitioning on MariaDB works. The failure mode is never the feature; it is the lifecycle around it, which is a cron job and an alert, and both of those are easy to lose. This is the operational guide I rebuilt after that quarter: how MAXVALUE hides rot, how to prove pruning is actually happening, and the maintenance loop that does not depend on a remembered cron host.

Why does the MAXVALUE catch-all hide missing maintenance?

The catch-all exists for a good reason: without it, an insert whose partition key falls outside every defined range fails with an error, and a missing partition becomes a production outage at midnight on the first of the month. With it, the failure mode inverts — nothing ever fails, and the missing partition becomes a silent accumulation in pmax. SHOW CREATE TABLE looks fine. Inserts succeed. The only symptoms are indirect: one partition's row count climbing out of proportion in information_schema.PARTITIONS, queries against recent data getting slower by degrees, and disk consumption drifting upward because the thirteen-month retention drop stopped dropping current data.

That inversion is the whole argument for monitoring the lifecycle instead of monitoring errors. The health check is not "did an insert fail" but "does the highest defined partition boundary cover today plus a lead time," and it is a one-line query away. If you take one thing from this article: a MAXVALUE partition is a dead-letter queue, and dead-letter queues need alarms, not optimism.

How do you verify partition pruning is actually happening?

Partitioning only pays when the optimizer prunes — when a query touches one or two partitions instead of forty. The verification tool is EXPLAIN PARTITIONS, and the discipline is to run it against the real queries, not against a query you wrote to demonstrate pruning:

-- which partitions does this query actually touch?
EXPLAIN PARTITIONS
SELECT COUNT(*)
FROM events
WHERE event_date >= '2026-07-01' AND event_date < '2026-08-01';

-- partition-level reality check: rows, data length, per partition
SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'telemetry' AND TABLE_NAME = 'events'
ORDER BY PARTITION_ORDINAL_POSITION;

-- the alarm that would have saved us: does the top boundary cover next month?
SELECT MAX(PARTITION_DESCRIPTION) AS top_boundary
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'telemetry' AND TABLE_NAME = 'events'
  AND PARTITION_DESCRIPTION <> 'MAXVALUE';

The partitions column in the EXPLAIN output is the verdict, and when it lists every partition on a query that names a date range, the usual suspects are three. First, the WHERE clause wraps the key in a function — WHERE DATE(event_ts) = '2026-07-14' prunes nothing, because the optimizer prunes on the partitioning expression, not on expressions you invent around the column; compare the column directly and let the range do the work. Second, type coercion: comparing a DATE partition key to a string with a time component or a mismatched type can disable pruning depending on version, which is one of those claims to verify with EXPLAIN PARTITIONS on your exact release rather than trust from memory — mine included. Third, the query genuinely spans everything, in which case partitioning was never going to help that query and the answer is an index, not more partitions. Pruning and indexing are complementary: the partition narrows the haystack, the index finds the needle inside it.

What does the maintenance lifecycle look like when it works?

The lifecycle has two jobs — create partitions ahead of time, and retire partitions behind the retention line — and both are metadata operations when the layout is right. Adding partitions to a RANGE COLUMNS layout without a catch-all is instant: ALTER TABLE events ADD PARTITION appends a new boundary. With a catch-all present, the new boundary must be carved out of pmax via REORGANIZE PARTITION, and REORGANIZE copies every row currently in the reorganized partition into the new layout. That is fine when pmax holds a few stray days of data — our corrected cron keeps the lead time at two months, so pmax is always near-empty and the monthly reorganize finishes in seconds. It was a six-hour, 900-million-row table copy when pmax held a year. The lesson is not "avoid REORGANIZE"; it is "keep the catch-all empty so REORGANIZE stays cheap."

-- carve next month out of the catch-all (cheap ONLY if pmax is near-empty)
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- retire the oldest month: metadata-fast, no row-by-row delete
ALTER TABLE events DROP PARTITION p2025_07;

-- refresh optimizer stats after big partition churn
ANALYZE TABLE events;

Dropping old partitions is the other half of the value proposition, and it is the reason the retention policy was partitioning-shaped in the first place: DROP PARTITION removes thirteen-month-old data as a metadata operation that returns instantly, where the DELETE equivalent would have been hours of row churn, binlog volume, and undo growth. The retirement cron and the creation cron are one script in our repo now — create two months ahead, drop anything older than thirteen months, then assert the top boundary covers the lead time and page a human if it does not. The script is idempotent, it logs what it did, and its assertion is the alarm that replaces "someone remembers the utility host."

Which constraints do you design around from day one?

Partitioning's constraints are load-bearing, and three of them shape schema design whether you like it or not. Every unique key on the table — including the primary key — must include the partitioning expression. Our events table wanted a globally unique event_id, and the only honest way to keep it was a composite primary key of (event_id, event_date), with global uniqueness enforced by the application or a separate non-partitioned lookup table. This is the constraint that kills the most partition migrations at design review, so surface it first, not after the table exists. Foreign keys are the second wall: InnoDB does not support foreign keys on partitioned tables in either direction, so referential integrity moves to the application layer or to unpartitioned reference tables. And there is a hard ceiling on partition count — 8192 per table — which no sane range scheme approaches, but which matters if someone proposes per-day partitions on a ten-year retention window.

Two operational footnotes from the incident quarter. DDL on a partitioned table still takes the metadata lock, and our six-hour REORGANIZE ran under ALGORITHM defaults that copied rows while holding the table against new DDL — the queue of blocked ALTERs behind it was what made the deploy window tense, and it is the same lock mechanics I covered in the instant DDL version-by-version notes: know which ALTERs copy and which do not before you run them on the biggest table you own. And OPTIMIZE TABLE on a partitioned table rebuilds every partition; after the great reorganize we rebuilt only the partitions that needed it, one ALTER TABLE ... OPTIMIZE PARTITION at a time, in the small hours. MySQL's operational answer to the same lifecycle is documented in the MySQL partitioning operational guide, and most of the discipline transfers — the MariaDB specifics are in the pruning edge cases and the engine support matrix, so verify those per release.

Where MonPG fits

The signals worth trending on a partitioned table are the ones that would have caught our eleven silent months: row count and data length per partition over time (one partition growing out of band is the smell), the top partition boundary versus a lead-time threshold, and pruning effectiveness measured as partitions-touched per query on the hot paths. Full disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and partition lifecycle health is on the list of signals it is being built around: per-partition growth and boundary coverage surfaced as continuous metrics instead of discovered in a quarterly review. Until that ships, the information_schema queries above, run from the same cron that does the maintenance, are your early-warning kit. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.