PATTERN Cited by 1 source

Per-partition maintenance¶

Per-partition maintenance is the operational pattern of running table-wide maintenance operations — bulk deletion, backup, index rebuild, space reclamation — one partition at a time instead of on the whole table at once. Each partition-scoped operation is cheaper than the equivalent whole-table operation because it touches only 1/N of the data and holds locks on only one physical unit.

Morrison's three maintenance examples¶

Verbatim from Brian Morrison II's MySQL partitioning primer:

"Another potential benefit of MySQL partitioning can be seen with select maintenance operations. For example, truncating all of the data in a partition is faster than performing a DELETE ... WHERE statement on the same data. Backup operations can also be performed on individual partitions, which can reduce the load on the overall server. Rebuilding indexes or reclaiming unused space can also be done per partition instead of on the entire table."

— (sources/2026-04-21-planetscale-what-is-mysql-partitioning)

Three canonical per-partition operations:

ALTER TABLE ... TRUNCATE PARTITION p_name — atomically empties one partition. On a RANGE-partitioned log table where p_oldest holds 2019 data, dropping 2019 is TRUNCATE PARTITION p_oldest (O(metadata)) vs DELETE FROM logs WHERE year = 2019 (O(rows) with write amplification on every secondary index). Same-result, orders-of-magnitude cheaper.
Per-partition backup — physical-backup tools (mysqldump --tab, XtraBackup per-file selection) can target one partition's file on disk; the other partitions don't need to be read. Reduces IO load on the live database during the backup window.
Per-partition index rebuild / space reclamation — ALTER TABLE ... REBUILD PARTITION p_name or OPTIMIZE PARTITION p_name runs only on one partition. Compared to a whole-table rebuild that locks the whole table and rewrites all rows, the per-partition version locks and rewrites 1/N of the data.

Why this matters¶

Whole-table maintenance on a multi-TB MySQL table is a production-hostile event:

DELETE ... WHERE year = 2019 on a 10 TB table takes hours, holds row locks, writes to every secondary index for every deleted row, bloats the binlog, and can trigger replication lag.
OPTIMIZE TABLE (required periodically to reclaim space from deleted rows) rewrites the whole table while holding a metadata lock.
ALTER TABLE ... REBUILD for index maintenance touches every row.

Partitioning lets operators amortise these costs across maintenance windows: rebuild p1 on Monday, p2 on Tuesday, etc. — keeping any single operation small enough to fit in a quiet hour.

Relation to shard-parallel maintenance¶

Per-partition maintenance at the same-server altitude is the sibling of shard-parallel backup-and-restore at the cross-server altitude:

Axis	Per-partition maintenance	Shard-parallel maintenance
Unit	One partition on one server	One shard on one server in a sharded cluster
Parallelism	Serial across partitions (one CPU on one machine)	Parallel across shards (N machines, N-way parallelism)
Wall-clock	O(N_partitions × partition_size)	O(shard_size)
Fault isolation	None	One shard's backup failure doesn't affect others

See patterns/shard-parallel-backup-and-restore for the cross-server sibling.

Per-partition maintenance is a cost-amortisation technique; shard-parallel maintenance is a wall-clock-reduction technique. The sharded fleet gets N×-faster maintenance; the partitioned single server gets N×-cheaper-per-operation maintenance.

Relation to ghost-table online schema changes¶

The ghost-table pattern used by gh-ost, pt-online-schema-change, and Vitess runs a whole-table copy to rebuild the schema. Per-partition maintenance in MySQL 8.0 does not replace ghost-table schema changes for DDL — ALTER TABLE ... MODIFY COLUMN on one partition is not supported. Per-partition maintenance is for data-only operations (TRUNCATE, OPTIMIZE, backup); structural changes still require the ghost-table workflow or INSTANT DDL.

Caveats¶

Only works on partitioned tables — tables without PARTITION BY ... don't have this surface.
Partition boundary must align with the maintenance unit — TRUNCATE PARTITION drops whole partitions; if you need to delete a subset of a partition's rows, you're back to DELETE ... WHERE.
Backup tool support varies — not all backup tools can target individual partitions; some (logical dumps via mysqldump) treat the partitioned table as logically one table.
Foreign keys forbid partitioning — InnoDB partitioned tables cannot be the source or target of foreign-key constraints in MySQL 8.0. Applications relying on FK enforcement can't use partitioning + per-partition maintenance on those tables.

Seen in¶

sources/2026-04-21-planetscale-what-is-mysql-partitioning — Brian Morrison II (PlanetScale, 2023-10-10) names three per-partition maintenance wins (TRUNCATE vs DELETE, per-partition backup, per-partition index rebuild / space reclamation) as the second first-order benefit of MySQL partitioning after pruning. Pedagogy altitude; no production numbers.