RANGE partition maintenance: add future partitions, archive / drop old ones

Command

-- Current partitions
SELECT PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'events_log' ORDER BY PARTITION_ORDINAL_POSITION;

-- Add a new month by splitting the empty pmax
ALTER TABLE shop.events_log REORGANIZE PARTITION pmax INTO (
  PARTITION p2026_11 VALUES LESS THAN ('2026-12-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE));

-- Move the old month into an archive table (instant), then drop the empty partition
CREATE TABLE shop.events_log_2026_08 LIKE shop.events_log;
ALTER TABLE shop.events_log_2026_08 REMOVE PARTITIONING;
ALTER TABLE shop.events_log EXCHANGE PARTITION p2026_08 WITH TABLE shop.events_log_2026_08;
ALTER TABLE shop.events_log DROP PARTITION p2026_08;

-- Pruning check: the partitions column should list a single partition
EXPLAIN SELECT * FROM shop.events_log WHERE created >= '2026-10-01' AND created < '2026-11-01';

What it does

On a date-based RANGE-partitioned table, adds the next period's partition, moves an old period into a separate table within milliseconds via EXCHANGE, and drops the partition far more cheaply than DELETE.

When to use it

For monthly retention and archiving on tables that grow over time, such as logs, events and audit trails.

Caution

The partitioning column must be part of every unique key (PK included). REORGANIZE PARTITION pmax rewrites the data in pmax; keep pmax empty and create partitions ahead of time (e.g. 3 months). DROP PARTITION cannot be undone and is not logged row by row; on replicas it runs as DDL. The EXCHANGE target must have the same structure and no partitioning; rows outside the range cause an error (WITHOUT VALIDATION is faster but can let wrong data in). Partition DDL takes a table-level metadata lock. For pruning, use the partitioning column in WHERE without wrapping it in a function.