Most of the partitioned tables I have inherited were partitioned for the wrong reason. Someone had a slow query on a big table, read that partitioning "improves performance," and split the table into 64 hash partitions. The query got slower, DELETE-based retention still took hours, and now every unique key had to include the partition column. Partitioning is a data-lifecycle tool that occasionally helps performance, not a performance tool that occasionally helps lifecycle.
Used for the right job, it is superb. Dropping a month of data from a range-partitioned table is a metadata operation that takes milliseconds; the equivalent DELETE writes gigabytes of undo, bloats the table, and hammers replicas. This guide covers the operational side: proving pruning works, automating partition maintenance so it never pages you, and the restrictions that should make you walk away.
Partition for retention first, queries second
The strongest case for partitioning in MySQL is time-series-ish data with a retention policy: events, logs, audit rows, order history that ages out to cold storage. RANGE partitioning on a date expression lets you drop expired data with ALTER TABLE ... DROP PARTITION, which is fast, does not fragment the table, and generates almost no replication traffic compared to a bulk DELETE.
CREATE TABLE events (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
occurred_at DATETIME NOT NULL,
event_type VARCHAR(64) NOT NULL,
payload JSON,
PRIMARY KEY (id, occurred_at)
)
PARTITION BY RANGE (TO_DAYS(occurred_at)) (
PARTITION p2026_05 VALUES LESS THAN (TO_DAYS('2026-06-01')),
PARTITION p2026_06 VALUES LESS THAN (TO_DAYS('2026-07-01')),
PARTITION p2026_07 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p_future VALUES LESS THAN MAXVALUE
);Notice the primary key includes occurred_at. That is not a style choice. MySQL requires every unique key on a partitioned table, including the primary key, to include all columns in the partitioning expression. This single rule reshapes schemas: your clean surrogate-key design becomes a composite key, and any uniqueness guarantee that does not naturally include the partition column can no longer be enforced by the database. If that trade is unacceptable, partitioning is the wrong tool, full stop.
Verify pruning; never assume it
Partition pruning is the optimizer skipping partitions that cannot contain matching rows. It is the entire query-performance value of partitioning, and it is fragile. Pruning works when the WHERE clause constrains the partitioning column with comparisons the optimizer can map to partition ranges. It silently fails when the column is wrapped in a function the pruner cannot see through, when the predicate arrives via a JOIN instead of a constant, or when a query simply omits the partition column.
The verification is one keyword away and I still see teams skip it:
EXPLAIN SELECT event_type, COUNT(*)
FROM events
WHERE occurred_at >= '2026-07-01'
AND occurred_at < '2026-07-08'
GROUP BY event_type;
-- Check the "partitions" column of the EXPLAIN output.
-- One or two partitions listed: pruning works.
-- Every partition listed: you are scanning the whole table,
-- through 30 smaller B-trees instead of one big one.The failure mode when pruning does not happen is worse than an unpartitioned table. A query that touches all partitions probes one index per partition instead of one index total, so a well-indexed lookup can do 30 index dives instead of one. This is how "we partitioned the table" becomes "the table got slower." Make an EXPLAIN with the partitions column part of code review for every query that touches a partitioned table, and re-check after ORM upgrades, because generated SQL changes shape.
Pay special attention to two query shapes. Predicates that arrive through a JOIN — "give me events for orders created last week" — often cannot prune, because the pruner needs constraints on the events table's own partition column, not on a joined table's column. And queries built by ORMs love to wrap date columns in functions or cast them, which defeats pruning even when the literal values would have pruned fine. In both cases the fix is usually to pass the partition-column bounds explicitly from the application, redundant as that feels.
Automate partition maintenance or it will page you
Range-partitioned tables need new partitions ahead of the data and old partitions dropped behind it. Both jobs must be automated, because the failure of the first one is nasty: if inserts arrive with a partition key beyond your last defined range and there is no MAXVALUE catch-all, they fail outright. With a MAXVALUE catch-all, they silently pile into p_future, and months later you discover a giant unsplittable partition holding your newest, hottest data. REORGANIZE PARTITION can split it, but that rebuilds the rows it moves, which is exactly the expensive operation partitioning was supposed to avoid.
My baseline automation, whether it runs as a cron job, a scheduled event, or a migration-framework task: create partitions at least two periods ahead; alert (do not just log) when fewer than two future partitions exist; drop expired partitions on an explicit allowlist pattern rather than "anything older than X" so a naming mistake cannot drop the wrong thing; and record every DROP PARTITION in an audit trail, because a dropped partition is unrecoverable outside backups. Monitor partition counts and sizes from INFORMATION_SCHEMA.PARTITIONS so growth anomalies surface before they are emergencies — a p_future partition with a nonzero and growing TABLE_ROWS estimate is the alert that matters most, because it means your partition-creation job has already failed and the catch-all is absorbing live data.
The foreign-key restriction is absolute
InnoDB partitioned tables cannot participate in foreign keys at all. A partitioned table cannot have FK constraints pointing at other tables, and no table can have an FK pointing at a partitioned table. The moment you partition, referential integrity for that table moves entirely into the application layer.
For append-heavy event data this is usually tolerable; those tables tend not to carry FKs anyway. For core relational tables it is a serious cost that teams underweight at design time. I have watched a team partition an orders table for retention, quietly lose the FK from order_items, and discover orphaned rows eight months later during a data-warehouse reconciliation. If your candidate table sits in the middle of the constraint graph, either the FKs go or the partitioning does. This restriction is also one of the sharpest contrasts with PostgreSQL, which has supported foreign keys on partitioned tables since version 12 — I wrote up the full comparison in MySQL partitioning vs PostgreSQL partitioning.
When partitioning is the wrong tool
Some patterns show up repeatedly as regret. Partitioning to make point-lookup queries faster: an index does that job better with none of the constraints. Hash partitioning "to spread the load": InnoDB already spreads pages; hash partitions mostly guarantee that pruning never helps because equality on the exact partition column is the only prunable predicate. Partitioning a table under a few hundred gigabytes with no retention need: you take on unique-key contortions, FK loss, and maintenance automation for essentially no benefit. And partitioning as a substitute for archiving: if the real requirement is "move old data somewhere cheaper," a separate archive table or store often does it with fewer constraints.
A decision rule that has served me well: partition when you can name the specific ALTER TABLE ... DROP PARTITION or pruned-scan win, you can live with composite unique keys, and the table has no foreign keys you care about. If any clause fails, do not.
It is also worth saying that unpartitioned InnoDB tables scale further than intuition suggests. A well-indexed multi-terabyte table with sane queries is routine; the B-tree does not care how many rows it holds nearly as much as your access patterns do. Reaching for partitioning before exhausting indexing, query shape, and archiving is usually solving the wrong layer of the problem.
Where MonPG fits
Honest positioning: MonPG is a PostgreSQL monitoring platform, and MySQL support is coming soon rather than available today. Partitioned tables are on the roadmap for a reason — the failure modes above are observability gaps: pruning regressions that arrive silently with an ORM change, future-partition exhaustion that nobody alerts on, p_future quietly absorbing the newest data. The MySQL monitoring (coming soon) page tracks where that work stands. If PostgreSQL is also part of your fleet, partition-aware monitoring is already live there and you can start there today.