Prune, drop, detach and copy monthly partitions

Goal

On a monthly partitioned table, you read with EXPLAIN what pruning (Min-Max) discards, and compare the rows read with a table without partitions. You confirm the difference between DROP/DETACH/ATTACH PARTITION and DELETE through system.mutations and part_log, and see partitions cut too finely blocking INSERT.

Why it matters

Once decided, the partition key is hard to change, and if you choose wrongly (too fine), parts explode and INSERT gets blocked. Conversely, if you choose well, retention management ends at the file level. This lab's grader does not trust the numbers you wrote — it attaches EXPLAIN to the SELECTs you saved and re-runs them read-only, and checks partition operations against the table's row hash and system.mutations, part_log and query_log.

Steps

1. Create the database ptn and the table ptn.sales — columns ts DateTime, region LowCardinality(String), order_id UInt64, amount UInt32 (in this order), engine MergeTree, PARTITION BY toYYYYMM(ts), ORDER BY (region, ts).
2. Run /opt/lab/fixtures/partition/sales.sql once to insert 1.5 million rows, merge with OPTIMIZE TABLE ptn.sales FINAL, and then save to /root/ch/partition/partitions.json a JSON array that holds, per partition, partition_id, part (the part name) and rows.
3. Create /root/ch/partition/q_aug.sql, which sums amount for August 2026 (ts >= '2026-08-01 00:00:00' AND ts < '2026-09-01 00:00:00'), save the EXPLAIN indexes = 1 output to /root/ch/partition/explain_aug.txt, and write the remaining/total parts of the Min-Max step and this query's rows_read to /root/ch/partition/prune.json as parts_selected, parts_total and rows_read.
4. Create ptn.sales_flat with the same columns and ORDER BY (region, ts) but no partitions, move the rows of ptn.sales into it and merge. Create /root/ch/partition/q_flat.sql, which reads that table with the same August condition, and write the rows_read of the two queries to /root/ch/partition/compare.json as partitioned_rows_read and flat_rows_read.
5. Make copies ptn.s_drop and ptn.s_delete of ptn.sales (CREATE TABLE ... AS ptn.sales + copying the rows + merging) and delete July — DROP PARTITION 202607 for s_drop, and ALTER TABLE ... DELETE WHERE toYYYYMM(ts) = 202607 SETTINGS mutations_sync = 1 for s_delete. Write the number of system.mutations rows of the two tables and the number of part_log MutatePart events of s_delete to /root/ch/partition/dropdel.json as drop_mutations, delete_mutations and delete_mutated_parts.
6. Make a copy ptn.s_detach the same way, detach it with DETACH PARTITION 202609, check the part name shown in system.detached_parts, and then attach it again with ATTACH PARTITION 202609. Write the name at the time of detaching and the active part name after re-attaching to /root/ch/partition/detach.json as detached_part and attached_part.
7. Create an empty table with CREATE TABLE ptn.sales_jul AS ptn.sales, and copy July over with ALTER TABLE ptn.sales_jul ATTACH PARTITION 202607 FROM ptn.sales, without an INSERT.
8. Create ptn.sales_fine with the same columns and PARTITION BY (toDate(ts), region) and ORDER BY ts, try to INSERT all of ptn.sales into it, and save the error that comes out to /root/ch/partition/toofine.txt. Count how many (date, region) combinations there are in ptn.sales and write it to /root/ch/partition/fine.json as partitions_needed.

Notes

• Viewing partitions: SELECT partition_id, name, rows FROM system.parts WHERE database = 'ptn' AND table = '...' AND active.
• EXPLAIN shows the Min-Max, Partition and PrimaryKey steps in turn. What this lab asks about is Parts: 남은/전체 (remaining/total) of the Min-Max step.
• Measure the rows read with clickhouse-client --use_query_condition_cache 0 --queries-file q_aug.sql --format JSON | jq .statistics.rows_read (with the query condition cache off).
• CREATE TABLE 새표 AS ptn.sales copies even PARTITION BY (the placeholder stands for the new table name). For a table without partitions, as in step 4, write out the columns yourself.
• Common mistakes: in step 6, writing the name at the time of detaching as the name after re-attaching (once re-attached, it receives a new block number). In step 8, raising max_partitions_per_insert_block to get it through — this step is for seeing the rejection.
• Official docs: Table partitions · Choosing a partitioning key · Custom partitioning key · ALTER ... PARTITION · ALTER ... DELETE · EXPLAIN

Create a monthly partitioned table

Create the database ptn and the table ptn.sales. The columns are, in order, ts DateTime, region LowCardinality(String), order_id UInt64, amount UInt32, the engine is MergeTree, with PARTITION BY toYYYYMM(ts) and ORDER BY (region, ts).

PARTITION BY can be written either before or after ORDER BY. toYYYYMM(ts) returns an integer like 202607, and this value becomes the partition_id. You do not have to put the partition key in the sorting key — within one part it is the same month anyway.

Insert three months and write the parts per partition

Run /opt/lab/fixtures/partition/sales.sql once to insert 1,500,000 rows and merge with OPTIMIZE TABLE ptn.sales FINAL. Save to /root/ch/partition/partitions.json a JSON array of objects that have, per partition, partition_id, part (the active part name) and rows.

Even for a single INSERT, if there are three partitions there are at least three parts. FINAL merges per partition, so even after merging, as many parts remain as there are partitions. If you bundle the lines received as JSONEachRow with jq -s ., you get an array.

Filtering only August — Min-Max pruning

Create /root/ch/partition/q_aug.sql, which sums amount for August 2026 (ts >= '2026-08-01 00:00:00' AND ts < '2026-09-01 00:00:00'), and save the output of that SELECT with EXPLAIN indexes = 1 attached to /root/ch/partition/explain_aug.txt. Write Parts: 남은/전체 (remaining/total) of the Min-Max step and this query's rows_read (with the query condition cache off) to /root/ch/partition/prune.json as parts_selected, parts_total and rows_read.

Each part records the minimum and maximum of the column used in the partition key (ts), so parts whose range does not overlap are not opened. The Partition step after Min-Max looks again only at the remaining parts. Compare the number of rows read with the row count of the August partition.

How much more is read without partitions

Create ptn.sales_flat with the same columns and ORDER BY (region, ts) but no partitions, move the rows of ptn.sales into it and merge with OPTIMIZE ... FINAL. Create /root/ch/partition/q_flat.sql, which sums amount on that table under the same August condition, and write the rows_read of q_aug.sql and q_flat.sql to /root/ch/partition/compare.json as partitioned_rows_read and flat_rows_read.

CREATE TABLE ... AS ptn.sales copies even the PARTITION BY, so write out the columns yourself. Even without partitions, ts is the second column of the sorting key and the earlier column region has only five values — recall what the generic exclusion search of the earlier module does for you.

Delete the same July by two methods

Make copies ptn.s_drop and ptn.s_delete of ptn.sales (CREATE TABLE ... AS ptn.sales, copying the rows, OPTIMIZE ... FINAL) and delete July — ALTER TABLE ptn.s_drop DROP PARTITION 202607 for ptn.s_drop, and ALTER TABLE ptn.s_delete DELETE WHERE toYYYYMM(ts) = 202607 SETTINGS mutations_sync = 1 for ptn.s_delete. Write the number of system.mutations rows of the two tables and the number of part_log MutatePart events of s_delete to /root/ch/partition/dropdel.json as drop_mutations, delete_mutations and delete_mutated_parts.

DROP PARTITION is detaching parts from the table, so it neither reads nor writes rows. ALTER DELETE becomes a mutation and rewrites the parts as new versions — count with part_log whether even parts with no rows to delete become new versions. mutations_sync = 1 makes it wait until the mutation finishes.

Detach and re-attach, and the name changes

Make a copy ptn.s_detach the same way as in step 5 and detach it with ALTER TABLE ptn.s_detach DETACH PARTITION 202609. After checking the part name shown in system.detached_parts, attach it again with ATTACH PARTITION 202609, and write the name at the time of detaching and the active part name of 202609 after re-attaching to /root/ch/partition/detach.json as detached_part and attached_part.

DETACH does not delete; it moves the part to the detached/ directory, and in the meantime the table forgets that part (count() decreases). A re-attached part receives a new block number in the table, so the middle numbers of the name change. Save the name in a variable right after detaching.

Copy a month without an INSERT

Create an empty table with the same structure using CREATE TABLE ptn.sales_jul AS ptn.sales, and copy the July partition over with ALTER TABLE ptn.sales_jul ATTACH PARTITION 202607 FROM ptn.sales. ptn.sales must stay at 1.5 million rows, and do not run an INSERT into ptn.sales_jul.

ATTACH PARTITION ... FROM copies a partition without deleting from either the source or the target. The two tables must have the same structure and partition key. The grader also checks in query_log that there was no INSERT into this table — if you fill it with INSERT ... SELECT, it fails even with the same rows.

Partitions cut too finely

Create ptn.sales_fine with the same columns and PARTITION BY (toDate(ts), region) and ORDER BY ts, run INSERT INTO ptn.sales_fine SELECT * FROM ptn.sales, and save the error (stderr) that comes out to /root/ch/partition/toofine.txt. Count how many (date, region) combinations there are in ptn.sales and write it to /root/ch/partition/fine.json as partitions_needed.

There is a limit (max_partitions_per_insert_block) on the number of partitions one INSERT block can create, and if it is exceeded, the whole block is rejected. How many would it be with three months × five regions? Count the number of combinations with uniqExact(toDate(ts), region). Do not raise the limit to get it through.