Hot/Cold Data, Rolling Partitions, Archiving, Retention, and Bulk Loads

Learning outcomes

Partitioning becomes operationally valuable when it changes how data ages. ServiceHub now has a rolling event-retention policy: recent data stays hot, older data is archived, and expired data must disappear predictably without a multi-hour DELETE. This lesson turns the partition design from Lesson 1 into a controlled lifecycle.

1. Hot, warm, cold, and expired are operational states

Partition names should encode the boundary they represent, and the retention procedure should calculate boundaries from policy—not from a DBA remembering which partition “looks old.”

2. Create a rolling time-partition fixture with a safety partition

DROP DATABASE IF EXISTS servicehub16_l2;CREATE DATABASE servicehub16_l2;USE servicehub16_l2;CREATE TABLE event_archive (  event_id BIGINT NOT NULL AUTO_INCREMENT,  event_day DATE NOT NULL,  ticket_id BIGINT NOT NULL,  event_type VARCHAR(32) NOT NULL,  payload VARCHAR(255),  PRIMARY KEY(event_id,event_day),  KEY ix_day_ticket(event_day,ticket_id)) ENGINE=InnoDBPARTITION BY RANGE COLUMNS(event_day) (  PARTITION p2026_06 VALUES LESS THAN ('2026-07-01'),  PARTITION p2026_07 VALUES LESS THAN ('2026-08-01'),  PARTITION p2026_08 VALUES LESS THAN ('2026-09-01'),  PARTITION p_future VALUES LESS THAN (MAXVALUE));INSERT INTO event_archive(event_day,ticket_id,event_type,payload) VALUES('2026-06-10',1,'created','old'),('2026-07-10',2,'created','warm'),('2026-08-10',3,'created','hot');

The p_future partition prevents a new date from failing simply because the next maintenance job did not run, but it also means future rows can accumulate in a catch-all partition. Monitor it; do not let it become permanent storage.

3. Roll the window forward by reorganizing the MAXVALUE partition

ALTER TABLE event_archiveREORGANIZE PARTITION p_future INTO (  PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),  PARTITION p_future VALUES LESS THAN (MAXVALUE));SELECT PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWSFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA='servicehub16_l2'  AND TABLE_NAME='event_archive'ORDER BY PARTITION_ORDINAL_POSITION;

Reorganization changes the partition map. Treat it as DDL: capture metadata-lock exposure, disk headroom, replica/Galera consequences, and a maintenance window appropriate to the target table. “Partition maintenance” is not synonymous with “zero-impact metadata change.”

4. Archive with EXCHANGE PARTITION—but validate first

An exchange swaps a partition with a nonpartitioned table having compatible structure. It can move a whole lifecycle unit between logical tables without row-by-row copying, but only if the candidate rows fit the partition definition.

CREATE TABLE june_archive LIKE event_archive;ALTER TABLE june_archive REMOVE PARTITIONING;ALTER TABLE event_archive  EXCHANGE PARTITION p2026_06 WITH TABLE june_archive WITH VALIDATION;SELECT COUNT(*) AS rows_left_in_live_juneFROM event_archive PARTITION(p2026_06);SELECT COUNT(*) AS rows_in_archiveFROM june_archive;

WITH VALIDATION is the safe default because MariaDB verifies that rows belong to the partition boundary. Current MariaDB also supports WITHOUT VALIDATION, but skipping validation moves correctness responsibility to you. Use it only when a separate pre-validation process proves every row satisfies the boundary and you have a rollback path.

5. Wrong approach: disable validation because the table is large

SELECT MIN(event_day) AS min_day, MAX(event_day) AS max_day, COUNT(*) AS rows_checkedFROM june_archive;SELECT COUNT(*) AS out_of_rangeFROM june_archiveWHERE event_day >= '2026-07-01';

If out_of_range is not zero, a validation-free exchange could create a logically inconsistent partition map. The repair is to reject/clean the staging set or use validated exchange. Performance pressure is not a reason to waive a partition invariant silently.

6. Expiration: DROP PARTITION versus DELETE

-- Example only after proving June is archived and policy authorizes deletion.ALTER TABLE event_archive DROP PARTITION p2026_06;SELECT PARTITION_NAMEFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA='servicehub16_l2' AND TABLE_NAME='event_archive';

Dropping a partition does not create a recovery point. Confirm backup/archive restoreability first. In asynchronous replication, DDL must be applied and can affect replica availability; in Galera, DDL follows cluster-specific total-order/schema-change semantics. Test your topology rather than extrapolating from a standalone lab.

7. Bulk load pattern: stage → validate → integrate

CREATE TABLE september_stage LIKE june_archive;INSERT INTO september_stage(event_id,event_day,ticket_id,event_type,payload)VALUES (900001,'2026-09-03',9,'created','bulk');SELECT COUNT(*) AS bad_rowsFROM september_stageWHERE event_day < '2026-09-01' OR event_day >= '2026-10-01';-- Only after bad_rows = 0 and structure is compatible:ALTER TABLE event_archive  EXCHANGE PARTITION p2026_09 WITH TABLE september_stage WITH VALIDATION;

This creates a clear acceptance gate. For large imports, also validate checksums/counts, duplicate-key behavior, SQL mode, character sets, and replication/binlog capacity.

8. Production judgment and cleanup

Rolling partitions are strongest when lifecycle boundaries are exact and predictable. Automate future-partition creation before the boundary, alert on rows accumulating in p_future, keep archive manifests and restore tests, and make retention destructive only after policy plus recoverability checks pass.

DROP DATABASE IF EXISTS servicehub16_l2;