Partitioning Strategies, Partition Pruning, Keys, Constraints, and Design Limits

Learning outcomes

ServiceHub has grown from a small ticketing database into an event platform that retains years of operational telemetry. A query that asks for one recent day should not need to touch every historical chunk, and retention should not mean deleting hundreds of millions of rows one by one. Partitioning can help—but only when its routing rules, key restrictions, optimizer pruning, and operational limits are understood precisely.

1. Mental model: one logical table, many physical partitions

A partitioned table is still one SQL table. MariaDB evaluates a partitioning expression to decide which partition owns each row. That write-time decision is routing. On reads, the optimizer may infer that only a subset of partitions can satisfy a predicate; eliminating the rest is partition pruning. Pruning is an optimizer result, not a promise that follows merely from creating partitions.

MariaDB also supports SYSTEM_TIME partitioning for system-versioned tables, but that is a separate lifecycle mechanism covered later in the course. For this chapter, focus on conventional partitioning of current tables.

2. Verify support and capture a baseline before creating anything

SELECT VERSION() AS server_version;SHOW PLUGINS;SHOW ENGINES;SELECT @@sql_mode, @@innodb_file_per_table;

Look for active partition support and an available InnoDB engine. “CREATE TABLE succeeded on another MariaDB machine” does not prove your packaged server, storage engine, or version has the same capabilities.

3. Build a time-series table whose keys obey partition rules

MariaDB requires every unique key—including the primary key—to include every column used by the partitioning expression. That requirement affects identifier design. A globally unique event_id by itself cannot be the primary key if the table is partitioned by event_day.

DROP DATABASE IF EXISTS servicehub16_l1;CREATE DATABASE servicehub16_l1;USE servicehub16_l1;CREATE TABLE ticket_events (  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) NULL,  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  PRIMARY KEY (event_id, event_day),  KEY ix_ticket_day (ticket_id, event_day)) ENGINE=InnoDBPARTITION BY RANGE COLUMNS(event_day) (  PARTITION p2026q1 VALUES LESS THAN ('2026-04-01'),  PARTITION p2026q2 VALUES LESS THAN ('2026-07-01'),  PARTITION p2026q3 VALUES LESS THAN ('2026-10-01'),  PARTITION p2026q4 VALUES LESS THAN ('2027-01-01'),  PARTITION p_future VALUES LESS THAN (MAXVALUE));INSERT INTO ticket_events(event_day,ticket_id,event_type,payload) VALUES('2026-02-10',101,'created','web'),('2026-05-12',101,'assigned','queue-a'),('2026-08-03',202,'created','api'),('2026-11-18',303,'closed','resolved');

SELECT PARTITION_NAME, PARTITION_ORDINAL_POSITION,       PARTITION_DESCRIPTION, TABLE_ROWSFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA='servicehub16_l1'  AND TABLE_NAME='ticket_events'ORDER BY PARTITION_ORDINAL_POSITION;

TABLE_ROWS is metadata and may be estimated for InnoDB; use it to understand partition shape, not as an exact accounting ledger.

4. Prove pruning instead of assuming it

EXPLAIN PARTITIONSSELECT event_id,ticket_id,event_typeFROM ticket_eventsWHERE event_day >= '2026-08-01'  AND event_day <  '2026-09-01';

The partitions column should identify p2026q3 for this predicate. That proves the optimizer can derive the relevant range. It does not prove the query is fast: index selectivity, row count inside the partition, cache state, and result volume still matter.

EXPLAIN PARTITIONSSELECT event_id,event_day,event_typeFROM ticket_eventsWHERE event_type='created';

Because event_type does not constrain event_day, multiple/all partitions can remain candidates. Partitioning did not replace the need for suitable indexes.

5. Wrong approach #1: keep a primary key that omits the partitioning column

CREATE TABLE bad_events (  event_id BIGINT PRIMARY KEY,  event_day DATE NOT NULL,  payload VARCHAR(100)) ENGINE=InnoDBPARTITION BY RANGE COLUMNS(event_day) (  PARTITION p_old VALUES LESS THAN ('2026-07-01'),  PARTITION p_new VALUES LESS THAN (MAXVALUE));

MariaDB rejects this design because the primary key does not include event_day. A representative error is ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function. The repair is not to remove uniqueness blindly; redesign the key so every unique key contains the partitioning column, and verify whether that changes application lookup semantics.

6. Wrong approach #2: add a foreign key to a partitioned InnoDB table

CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY) ENGINE=InnoDB;ALTER TABLE ticket_events  ADD CONSTRAINT fk_event_ticket  FOREIGN KEY (ticket_id) REFERENCES tickets(ticket_id);

Current MariaDB partitioning does not allow a partitioned table to contain or be referenced by foreign keys. The operation fails rather than quietly weakening integrity. If the domain requires database-enforced referential integrity, partitioning this table may be the wrong design. Application-side checks are not automatically equivalent to an InnoDB foreign key.

7. Design limits that should influence the decision

8. Production judgment and cleanup

Use partitioning when the partition key aligns with a real management or access boundary—especially time-based retention, archival, or predictable pruning. Do not introduce it solely because a table is “large.” First prove the bottleneck, check key/FK consequences, measure representative plans, and document how partitions will be created, monitored, backed up, and retired.

DROP DATABASE IF EXISTS servicehub16_l1;