Partitions — a unit of management before a unit of filtering

In one line

PARTITION BY makes rows go into different parts per partition key value. Merges do not cross partitions, so a month's rows are always only in that month's parts. So deleting, detaching and moving a whole month ends at the file level. Faster queries are a bonus, and that share is smaller than you might think — most of the skipping is done by the sorting key.

Why this was needed

A time-series table such as logs or orders eventually meets the demand "delete the old ones". As we saw in the earlier module, parts do not change. To delete a few rows, you must write anew the parts holding those rows (a mutation), and rewriting the entire table to delete data from three months ago makes no sense. If rows are held in separate parts per month, the story changes. You just detach the July parts from the table and you are done.

This is why the official documentation (Table partitions) calls partitioning "primarily a data management feature". The same document also says that a query that crosses all partitions is usually slower than on a table without partitions. The moment you cut partitions finely to use them "like an index", the number of parts explodes and you are back at the previous module's TOO_MANY_PARTS.

How it works

An INSERT writes a part per partition. When I inserted 1.5 million rows covering three months at once, the parts were 202607_1_1_0, 202608_2_2_0, and for September, the block was cut in two, so two were created. The 202607 at the front of the name is the partition_id. OPTIMIZE ... FINAL merges per partition, so there are three parts — a part combining July and August never appears.

Pruning has two layers. Each part records the minimum and maximum of the column used in the partition key (ts), so when you filter by the August range, EXPLAIN shows this.

Min-Max
  Keys: ts
  Parts: 1/3
  Granules: 62/183
Partition
  Keys: toYYYYMM(ts)
  Parts: 1/1

Min-Max discarded the July and September parts without even opening them. Inside the remaining part, the sorting-key index from the earlier modules chooses granules again. A condition that cannot be turned into the partition key (for example toDayOfWeek(ts) = 1) gives Parts: 3/3 — it cannot discard anything.

But even without partitions it is almost the same. On a table with the same sorting key (region, ts) and no partitions, the same August query read 581,632 rows (the partitioned table 505,454 rows). Since ts is the second column of the sorting key and the earlier column region has only five values, the generic exclusion search of the earlier module finds almost all of the August interval. When filtering by region and August together, the table without partitions actually read less (106,496 versus 114,688). The gain partitions gave is on the order of a few boundary granules.

Management operations are per part. ALTER TABLE ... DROP PARTITION 202607 detaches the July parts from the table. Nothing remains in system.mutations, and the August and September parts do not even change their names. If you delete the same rows with ALTER TABLE ... DELETE WHERE toYYYYMM(ts) = 202607, one mutation is created, and MutatePart appears three times in part_log — even the August and September parts, which have no July rows at all, became new versions (_5 at the end of the name). The mutation itself is covered in depth in the last module.

DETACH PARTITION moves the parts to the table's detached/ directory and the table forgets they exist (they show in system.detached_parts). When you put it back with ATTACH PARTITION, the part receives a new block number, and 202609_3_4_1 came back as 202609_5_5_0. ATTACH PARTITION ... FROM 다른표 copies a partition from a table with the same structure (here the Korean word stands for "another table") — as the documentation says, it deletes from neither the source nor the target, and the parts come over as they are without an INSERT query. It is a common way to set a month aside in an archive table.

If you cut too finely, it gets blocked. PARTITION BY (toDate(ts), region) is 460 partitions over three months. When I inserted everything into that table, the whole INSERT was rejected with the error Too many partitions for single INSERT block (more than 100) (252) and not a single row went in. The limit is the setting max_partitions_per_insert_block (default 100). The error message itself says "partitions are not meant to make SELECT fast".

What it looks like in the field

The most common mistake is "we often filter by date, so partition by date". Daily partitions are 365 for a year, and if you mix in users and regions it becomes thousands. Each partition has its own parts and merges do not cross partitions, so small parts pile up without end. The documentation (Choosing a partitioning key) says to choose a partition key of low cardinality — usually a month is enough. Date range filtering is solved by putting the time column in the sorting key.

Conversely, where partitions shine is the retention cycle. If "delete data older than 13 months" is a DELETE, each time a mutation rewrites all the parts, but with monthly partitions, one line of DROP PARTITION finishes it. Operations that detach (DETACH) a month that needs reprocessing and fill in the corrected data from another table (ATTACH ... FROM / REPLACE PARTITION) work on the same principle.

What you will do in the next lab

You create ptn.sales with monthly partitions, insert three months and write the parts per partition. You read the number of parts Min-Max left in the EXPLAIN of the August query, and compare rows_read with a table of the same sorting key without partitions. You delete the same July with DROP PARTITION and with DELETE and compare the number of mutations, detach and re-attach September to see the name change, and copy July to another table without an INSERT. Finally you confirm that a (date, region) partitioned table is rejected at INSERT.