Partitioning Interview Questions

RANGE partitioning slices rows by a continuous expression such as YEAR(created_at) or a UNIX timestamp, so dropping last year is a metadata unlink. Pruning works when WHERE specifies that column without wrapping it in a function. Hot recent partitions still share innodb_buffer_pool with cold ones. It is not a substitute for archiving to another cluster.

Partition pruning uses the WHERE clause to open only matching .ibd files; a function on the partition column (YEAR(ts) when you partitioned on ts) defeats it. EXPLAIN PARTITIONS or EXPLAIN FORMAT=TREE shows the pruned set. Prepared statements still prune when the bound value is constant per execution. OR conditions across many partitions can open all of them.

Every unique key, including PRIMARY KEY, must include the partition columns—this is a MySQL rule so uniqueness can be checked inside one partition. People who wanted a global UNIQUE email on a RANGE(user_id) table cannot have it. A unique secondary index that omits the partition key is rejected at CREATE. Sharding is the escape hatch for global uniqueness.

EXCHANGE PARTITION swaps a partition with an identical non-partitioned table, which is how you load a staged InnoDB table or archive one partition out. The table must have the same indexes and row format, and WITH/WITHOUT VALIDATION decides whether rows are checked against the partition bound. It is metadata plus file swap when validation is skipped carefully. FK-less tables only.

NULL in a RANGE partition goes to the lowest partition; LIST requires an explicit NULL partition or the INSERT errors. HASH maps NULL to a partition as well, which surprises people who thought NULL meant unassigned. Make partition keys NOT NULL unless you intentionally use the lowest RANGE slice as a junk drawer. Pruning IS NULL still needs that column in WHERE.

Subpartitioning (RANGE of HASH) is rarely worth it; it multiplies files and makes prune rules harder to reason about. It can spread a hot month across HASH subpartitions for INSERT concurrency. File-per-table then means hundreds of .ibd files and slower crash recovery. Measure mutex and open-file cost in PERFORMANCE_SCHEMA before copying a textbook diagram.

Queries that touch every partition still open every tablespace, so a 365-day RANGE of daily partitions plus SELECT without a date filter is worse than one table. Index statistics are per partition and can be stale independently. The optimizer may pick a plan that opens all partitions for a JOIN. Partitioning helps WHEN the WHERE names the key; it is not a global speedup.

Foreign keys are not supported on partitioned InnoDB tables; you enforce referential integrity in the application or with triggers that do not span partitions well. EXCHANGE PARTITION would also break parent/child file layout. This limitation is why people shard parent and child together instead of partitioning only the child. Unique-key-must-include-partition-key already fights global FK style.

Dropping an old RANGE partition is instant metadata plus file unlink, which is the cheap retention mechanism compared with DELETE FROM ... WHERE ts < that generates huge undo and binlog ROW events. Replicas apply DROP PARTITION as DDL, so lag is a short spike, not a row-apply storm. Keep MAXVALUE unused so you drop named historical slices. Backup the partition first if compliance needs the rows.

The partition expression must be deterministic and integer/date-like; using UNIX_TIMESTAMP(ts) when queries filter on ts (or vice versa) prevents pruning. Nested functions, UDFs, and non-integer HASH expressions are rejected or useless. Generated columns that duplicate the expression and appear in WHERE help. EXPLAIN is the proof, not CREATE TABLE success.

A unique secondary index that omits the partition key is rejected at CREATE; people then add a non-unique index and check uniqueness in a procedure, which races. A second table mapping email to user_id (not partitioned) restores global uniqueness. Triggers that INSERT into that map must share the InnoDB transaction. This is the usual interview trade-off versus sharding.

Online DDL on a partitioned table still rebuilds partition by partition in some algorithms, extending the metadata lock window. INSTANT ADD COLUMN may apply per table, not per slice, depending on version. A failed REORGANIZE leaves the table in a locked copy state. Test the exact 8.0 algorithm with ALGORITHM=INPLACE, LOCK=NONE on a clone, not in the interview whiteboard vacuum.

LIST partitioning maps discrete values (region_id IN (1,2,3)) to named partitions; HASH and KEY spread rows by a modulus to even out size. KEY uses MySQL’s internal hash of the column list; HASH uses a user expression that must be integer. Range queries cannot prune HASH/KEY. LIST is what people want for tenant_id when the tenant set is known.

HASH and KEY partitioning spread rows to avoid hot partitions, but they make range deletes and date reports touch every partition. Pruning only works on equality of the hash expression. Reorganizing HASH later is a full copy. Prefer RANGE on a date for time-series; use HASH only when access is point lookups on the partition key.

Partitioning is not sharding: all partitions still live in one mysqld and share innodb_buffer_pool, redo, and a single binlog. Sharding (Vitess, application shards) splits data across instances and failure domains. Partitioning helps prune IO and drop old RANGE slices. If one buffer pool is the bottleneck, partitions will not save you.

MAXVALUE is the catch-all RANGE bound; forgetting a new year partition makes INSERT fail with “no partition for the value”. Applications see a hard error, not a silent extra partition. Schedule ALTER ... ADD PARTITION before January 1. A MAXVALUE catch-all hides the operational miss but then you cannot drop “next year” cleanly.

ALTER TABLE ... REORGANIZE PARTITION splits or merges RANGE/LIST partitions with a copy of those slices only, not the whole table, on recent algorithms. Concurrent DML is allowed with INPLACE in some cases but still rebuilds the affected .ibd. HASH cannot be split that way—you rebuild. Always dump the partition first if the copy fails mid-way.

Partition-level ANALYZE and OPTIMIZE operate on a single partition, which is how you refresh innodb stats after loading one month. OPTIMIZE still copies that .ibd. Persistent stats in mysql.innodb_index_stats are per (table, partition, index). Skipping ANALYZE after a bulk load of one RANGE slice is a classic wrong EXPLAIN rows estimate.

EXPLAIN PARTITIONS (legacy) or EXPLAIN FORMAT=TREE shows which partitions are pruned; 8.0 also lists partitions in FORMAT=JSON. If every pYYYYMM appears, the WHERE is not sargable on the partition key. Bind parameters still prune. A JOIN that only constrains the partition key on the second table may prune late or not at all.

vs sharding: Vitess or application shards split data across mysqld instances; partitions never buy you a second innodb_buffer_pool or a second redo log. Cross-partition JOINs stay in one server. Operational limits (open files, DDL, backup size) hit first. Partition for drop-old-RANGE and prune; shard for capacity and failure isolation.