Partition Keys, Unique-Key Rules, Hot/Cold Data, and Operational Tradeoffs

Learning outcomes

The RANGE table from Lesson 1 prunes cleanly by month, but a schema review uncovers a business rule: ServiceHub wants a globally unique external event reference and also wants foreign-key enforcement to a technicians table. Those requirements collide with current MySQL partitioning rules. This lesson treats that collision as a data-model decision, not as an error to bypass.

The unique-key rule from first principles

For a partitioned table, every column used by the partitioning expression must appear in every unique key, including the primary key. The reason is structural: MySQL must be able to enforce uniqueness without treating unrelated partitions as independent uniqueness domains that could admit duplicates the key definition cannot reliably constrain.

USE servicehub_lifecycle_lab;CREATE TABLE bad_global_identity (  event_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,  occurred_on DATE NOT NULL,  external_ref VARCHAR(64) NOT NULL,  UNIQUE KEY uq_external_ref (external_ref)) ENGINE=InnoDBPARTITION BY RANGE COLUMNS(occurred_on) (  PARTITION p1 VALUES LESS THAN ('2026-04-01'),  PARTITION pmax VALUES LESS THAN (MAXVALUE));-- Expected: rejected because occurred_on is not part of every unique key.

Do not “repair” this blindly by changing the primary key to (event_id, occurred_on) and the unique key to (external_ref, occurred_on). That would permit the same external reference on different dates. If the business requirement is global uniqueness, the data model still has a problem even though the DDL would compile.

A valid partitioned design must preserve the business invariant

Historical event logs often have a natural escape hatch: the globally unique identity can be generated outside the partitioned fact table and enforced in a small unpartitioned registry, or the application can treat event IDs as immutable globally assigned identifiers while the history table uses a composite primary key. Whether that is acceptable depends on the transaction boundary and the required database-level guarantees.

Foreign keys: prove the incompatibility instead of assuming

CREATE TABLE technicians_ref (  technician_id INT PRIMARY KEY,  technician_name VARCHAR(80) NOT NULL) ENGINE=InnoDB;INSERT INTO technicians_ref VALUES (1,'Ava'),(2,'Omar');CREATE TABLE bad_partition_fk (  event_id BIGINT UNSIGNED NOT NULL,  occurred_on DATE NOT NULL,  technician_id INT NOT NULL,  PRIMARY KEY (event_id, occurred_on),  CONSTRAINT fk_bad_partition    FOREIGN KEY (technician_id) REFERENCES technicians_ref(technician_id)) ENGINE=InnoDBPARTITION BY RANGE COLUMNS(occurred_on) (  PARTITION p1 VALUES LESS THAN ('2026-04-01'),  PARTITION pmax VALUES LESS THAN (MAXVALUE));-- Expected: MySQL rejects the partitioning/foreign-key combination.

The safe response is architectural: partition a history/event projection that does not require FK enforcement, or retain the relational OLTP table without partitioning. Application-side validation is not equivalent to a foreign key; if you choose it, document the weaker enforcement boundary and add reconciliation tests.

Hot and cold partitions are workload facts, not labels

Time partitioning often creates a hot partition: the current month receives nearly all inserts and updates while older months become read-mostly or cold. That can be operationally useful for retention, but it also concentrates write pressure. Conversely, a HASH scheme can distribute writes but makes month-level lifecycle operations less direct. Measure the actual distribution before selecting a key.

SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTHFROM information_schema.PARTITIONSWHERE TABLE_SCHEMA='servicehub_lifecycle_lab'  AND TABLE_NAME='work_order_events_part'ORDER BY PARTITION_ORDINAL_POSITION;SELECT DATE_FORMAT(occurred_on,'%Y-%m') AS month_bucket,       COUNT(*) AS exact_rows,       COUNT(DISTINCT customer_id) AS customersFROM servicehub_lifecycle_lab.work_order_events_partGROUP BY DATE_FORMAT(occurred_on,'%Y-%m')ORDER BY month_bucket;

The first query exposes optimizer estimates and storage metrics; the second gives exact business counts. A highly skewed current month is not automatically wrong—it may simply reflect the workload. The question is whether the hot partition, indexes, redo, and storage system can absorb the write rate and whether maintenance operations remain predictable.

Access locality: choose a key that removes work you actually perform

EXPLAINSELECT COUNT(*)FROM servicehub_lifecycle_lab.work_order_events_partWHERE occurred_on >= '2026-06-01' AND occurred_on < '2026-07-01';EXPLAINSELECT COUNT(*)FROM servicehub_lifecycle_lab.work_order_events_partWHERE customer_id=42;EXPLAINSELECT COUNT(*)FROM servicehub_lifecycle_lab.work_order_events_partWHERE customer_id=42  AND occurred_on >= '2026-06-01' AND occurred_on < '2026-07-01';

The date-bounded statements can prune by the partition key. A customer-only history query may touch all time partitions even though the customer/date index can still provide useful access inside each partition. If customer-only lookup dominates the workload and retention-by-month is rare, this partition key may be the wrong optimization target.

Partition count is an operational cost

More partitions can make lifecycle boundaries finer, but each partition adds metadata and separate partition-level statistics/maintenance work. DDL that touches many partitions, backups, metadata queries, open-file/tablespace management, optimizer planning, and operational runbooks all become more complex. There is no defensible rule such as “one partition per day” without workload and retention evidence.

Verify the accepted key semantics before calling the design complete

The working event-history table from Lesson 1 is valid precisely because every unique key contains occurred_on and the table has no foreign keys. Inspect that fact explicitly; do not rely on a diagram or migration file that may have drifted from the server.

SHOW CREATE TABLE servicehub_lifecycle_lab.work_order_events_part\GSHOW INDEX FROM servicehub_lifecycle_lab.work_order_events_part;SELECT PARTITION_METHOD, PARTITION_EXPRESSION, COUNT(*) AS partition_countFROM information_schema.PARTITIONSWHERE TABLE_SCHEMA='servicehub_lifecycle_lab'  AND TABLE_NAME='work_order_events_part'GROUP BY PARTITION_METHOD, PARTITION_EXPRESSION;

Verify that the primary key is (event_id, occurred_on), that secondary indexes match actual query shapes, and that the partition expression is still the intended date column. Schema drift can invalidate the reasoning even when the table remains syntactically valid.

Wrong approach: force partitioning into a foreign-key-heavy OLTP model

A real operator may decide to remove foreign keys, broaden every unique key, and add hundreds of partitions merely because the table is “large.” The resulting schema can lose global uniqueness, move integrity checks into application code, and create more maintenance work without improving the dominant query.

The corrected alternative for ServiceHub is to keep transactional work orders and technicians normalized and FK-protected, while partitioning only an append-oriented historical event table whose retention/access pattern aligns with time. This is a deliberate projection boundary, not a workaround hidden from the data model.

Production judgment and bridge to Lesson 3

Before partitioning, write down the partition key, every primary/unique key, every required FK, dominant read predicates, write distribution, retention boundary, and maintenance operation. Reject the design if satisfying MySQL partition rules changes a business invariant you still require. Monitor skew, rows/bytes per partition, partition-pruning frequency, and maintenance duration.

Lesson 3 moves from table organization to schema change execution. Even the right partitioned design can become an outage if an ALTER TABLE unexpectedly copies data or waits behind a long metadata lock.

Authoritative references

• MySQL 8.4 Reference Manual
• MySQL Community Server 8.4 downloads
• Partitioning Keys, Primary Keys, and Unique Keys
• Partitioning Limitations Relating to Storage Engines
• KEY Partitioning