Chapter 18 · Partitioning, Large Tables, Archiving, and Data Lifecycle
Archiving, Purging, Retention Jobs, and Avoiding Massive Delete Pathologies
Turn retention policy into verified archive and bounded purge workflows, measuring transaction and storage consequences instead of running unbounded historical DELETE statements.
Learning outcomes
ServiceHub policy says detailed operational events are retained online for six months and older events must be archived in a verifiable form before deletion. A single DELETE ... WHERE occurred_on < cutoff looks convenient, but on a very large InnoDB table it can create a huge transaction with long undo history, redo and binary-log volume, lock duration, replica apply pressure, buffer-pool churn, and a painful rollback. Retention needs an engineered workflow.
Translate legal/business retention rules into archive, verification, purge, and recovery steps.
Explain why one massive DELETE is operationally different from bounded transactions or partition removal.
Build an archive manifest/checkpoint and verify archived data before destructive work.
Implement bounded purge batches and a safe partition-drop workflow on disposable data.
Prove archived data remains searchable/restorable according to the stated requirement.
Destructive purge is allowed only after the archive has a defined format/location, row-count and integrity checks, access controls, retention rules, and a tested restore/search path. The lab uses database tables for simplicity; production archives may use other storage but require the same verification discipline.
Retention design starts with the requirement
| Question | Why it matters |
|---|---|
| What must remain online? | defines the active cutoff and application query expectations |
| What must be retained offline and for how long? | defines archive retention and deletion eligibility |
| How quickly must archived data be searchable/restorable? | determines archive format, indexing, catalog, and restore process |
| What constitutes proof before purge? | row counts, time bounds, checksums/business aggregates, manifest identity |
| Who can purge? | least privilege and two-person/change-control boundaries for destructive operations |
For the lab, treat dates before 2026-01-01 as expired from the online event table after archive verification. Never apply this cutoff to a real system without the actual policy owner and legal/business approval.
Build an archive and manifest before deletion
USE servicehub_lifecycle_lab;DROP TABLE IF EXISTS work_order_events_archive;CREATE TABLE work_order_events_archive LIKE work_order_events_plain;INSERT INTO work_order_events_archiveSELECT *FROM work_order_events_plainWHERE occurred_on < '2026-01-01';DROP TABLE IF EXISTS archive_manifest;CREATE TABLE archive_manifest ( archive_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, archive_name VARCHAR(80) NOT NULL, cutoff_date DATE NOT NULL, row_count BIGINT UNSIGNED NOT NULL, min_event_id BIGINT UNSIGNED NULL, max_event_id BIGINT UNSIGNED NULL, event_id_sum DECIMAL(30,0) NOT NULL, verified_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;INSERT INTO archive_manifest(archive_name, cutoff_date, row_count, min_event_id, max_event_id, event_id_sum)SELECT 'servicehub-events-before-2026-01-01', '2026-01-01', COUNT(*), MIN(event_id), MAX(event_id), SUM(event_id)FROM work_order_events_archive;SELECT * FROM archive_manifest;The simple aggregate manifest is not a cryptographic archive format, but it provides deterministic business invariants for the lab. Production systems should add artifact hashes, object/version identity, encryption/key metadata, retention/legal-hold state, and restore-test history as appropriate.
Prove the archive is exact before purge
SELECT 'source' AS side, COUNT(*) AS rows_n, MIN(event_id) AS min_id, MAX(event_id) AS max_id, SUM(event_id) AS id_sumFROM work_order_events_plainWHERE occurred_on < '2026-01-01'UNION ALLSELECT 'archive', COUNT(*), MIN(event_id), MAX(event_id), SUM(event_id)FROM work_order_events_archive;SELECT event_id, occurred_on, work_order_id, event_typeFROM work_order_events_archiveORDER BY event_idLIMIT 5;Counts and aggregates must match before destructive work. These checks still do not prove every payload byte is identical, so a production archive should use stronger artifact/checksum validation. The sample query proves that archived records remain searchable in the lab.
Failure drill: see why one giant DELETE is dangerous without committing it
Create a disposable copy and perform the historical deletion inside a transaction that you roll back. This demonstrates transaction/lock/undo/redo work at safe scale without permanently deleting the lab source. Global counters can be affected by other sessions, so compare deltas only on an otherwise quiet disposable server.
DROP TABLE IF EXISTS purge_bad_demo;CREATE TABLE purge_bad_demo LIKE work_order_events_plain;INSERT INTO purge_bad_demo SELECT * FROM work_order_events_plain;SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_os_log_written','Innodb_buffer_pool_pages_dirty');SELECT NAME, COUNT AS history_lengthFROM information_schema.INNODB_METRICSWHERE NAME='trx_rseg_history_len';START TRANSACTION;DELETE FROM purge_bad_demoWHERE occurred_on < '2026-01-01';SELECT ROW_COUNT() AS rows_marked_deleted;-- Inspect transaction/locks in another session if desired, then:ROLLBACK;SELECT COUNT(*) AS rows_after_rollback FROM purge_bad_demo;SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_os_log_written','Innodb_buffer_pool_pages_dirty');SELECT NAME, COUNT AS history_lengthFROM information_schema.INNODB_METRICSWHERE NAME='trx_rseg_history_len';Even rolled back work can generate substantial undo/redo and dirty-page activity. A committed massive delete can additionally generate large binary-log transactions, replication apply work, and long purge cleanup. Do not extrapolate exact bytes from this small lab; use it to understand the causal path.
Bound the purge: short transactions with checkpointed progress
DROP TABLE IF EXISTS purge_batch_demo;CREATE TABLE purge_batch_demo LIKE work_order_events_plain;INSERT INTO purge_batch_demo SELECT * FROM work_order_events_plain;DROP TABLE IF EXISTS purge_progress;CREATE TABLE purge_progress ( job_name VARCHAR(80) PRIMARY KEY, cutoff_date DATE NOT NULL, deleted_rows BIGINT UNSIGNED NOT NULL DEFAULT 0, last_batch_rows INT UNSIGNED NOT NULL DEFAULT 0, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;INSERT INTO purge_progress(job_name, cutoff_date)VALUES ('events-before-2026-01-01','2026-01-01');START TRANSACTION;DELETE FROM purge_batch_demoWHERE occurred_on < '2026-01-01'ORDER BY occurred_on, event_idLIMIT 500;SET @batch_rows := ROW_COUNT();UPDATE purge_progressSET deleted_rows = deleted_rows + @batch_rows, last_batch_rows = @batch_rowsWHERE job_name='events-before-2026-01-01';COMMIT;SELECT @batch_rows AS this_batch, p.* FROM purge_progress AS p;SELECT COUNT(*) AS remaining_expiredFROM purge_batch_demoWHERE occurred_on < '2026-01-01';Repeat only while server/replica/application health remains inside the written gates, and stop when @batch_rows=0. Batching caps transaction size and creates pause points; it does not make deletion free. The retention predicate needs an index that makes each batch find old rows efficiently.
Partition retention: archive, verify, then remove a whole old partition
A major lifecycle advantage of RANGE partitioning is that an expired partition can be removed as a partition operation rather than row-by-row DML. But DROP PARTITION deletes the data in that partition. Treat it as destructive and use a disposable clone for the lab.
DROP TABLE IF EXISTS partition_purge_demo;CREATE TABLE partition_purge_demo LIKE work_order_events_part;INSERT INTO partition_purge_demo SELECT * FROM work_order_events_part;SELECT PARTITION_NAME, TABLE_ROWSFROM information_schema.PARTITIONSWHERE TABLE_SCHEMA='servicehub_lifecycle_lab' AND TABLE_NAME='partition_purge_demo'ORDER BY PARTITION_ORDINAL_POSITION;SELECT COUNT(*) AS exact_old_rowsFROM partition_purge_demoWHERE occurred_on < '2026-01-01';ALTER TABLE partition_purge_demo DROP PARTITION p2025q4, ALGORITHM=INPLACE;SELECT COUNT(*) AS remaining_old_rowsFROM partition_purge_demoWHERE occurred_on < '2026-01-01';SELECT PARTITION_NAME, TABLE_ROWSFROM information_schema.PARTITIONSWHERE TABLE_SCHEMA='servicehub_lifecycle_lab' AND TABLE_NAME='partition_purge_demo'ORDER BY PARTITION_ORDINAL_POSITION;Expected business state: remaining_old_rows=0 and p2025q4 is absent. The original partitioned lab table remains intact. In production, preserve proof that the archive corresponds to that exact period before dropping it.
Prove restore/searchability after online data is gone
DROP TABLE IF EXISTS archive_restore_probe;CREATE TABLE archive_restore_probe LIKE work_order_events_plain;INSERT INTO archive_restore_probeSELECT * FROM work_order_events_archive;SELECT COUNT(*) AS restored_rows, MIN(occurred_on) AS first_day, MAX(occurred_on) AS last_day, SUM(event_id) AS restored_id_sumFROM archive_restore_probe;SELECT row_count, min_event_id, max_event_id, event_id_sumFROM archive_manifestWHERE archive_name='servicehub-events-before-2026-01-01';The restored count/time bounds/aggregate should agree with the manifest. This is the minimum acceptance test for the lab. A production archive should be restored into a clean target on a schedule, not only during an incident.
Production judgment and bridge to capacity planning
Choose bounded row deletion when retention boundaries do not align with partitions or when only a subset of rows expires. Choose partition removal when the table was deliberately designed around immutable time boundaries and the entire partition is eligible. In both cases, archive/verify first, monitor redo/undo, lock waits, binary-log volume, replica lag, disk, and application latency, and retain restartable progress.
Lesson 5 expands from one retention job to multi-terabyte economics: storage growth, secondary-index multiplication, binary logs, backups, replicas, temporary DDL space, and maintenance windows must all fit before the table reaches its next growth milestone.
Optional cleanup for Lesson 4 probe tables
DROP TABLE IF EXISTS archive_restore_probe;DROP TABLE IF EXISTS partition_purge_demo;DROP TABLE IF EXISTS purge_batch_demo;DROP TABLE IF EXISTS purge_bad_demo;-- Keep work_order_events_archive and archive_manifest for Lesson 5 capacity examples.Knowledge check
- Why is one massive DELETE risky on a large InnoDB table?
- What must happen before destructive purge?
- What does batching improve?
- Why can DROP PARTITION be attractive for retention?
- Why use a restore probe?
Reveal answers
- It can create a huge transaction with long undo history, redo/binlog volume, locks, buffer/I/O pressure, replica apply cost, and expensive rollback/purge cleanup.
- Archive according to policy, verify deterministic integrity/business invariants, and prove the archive is searchable/restorable.
- It bounds transaction size and creates checkpoints/pause points; it does not eliminate total work.
- When an entire time partition is expired, MySQL can remove that partition as a partition operation instead of deleting rows individually.
- A backup/archive is not operationally trustworthy until a clean target can reconstruct the required data and pass invariants.