clickhouse.guru
Blog· Tested on ClickHouse 26.4

Deleting a slice of a huge table

Removing about a tenth of the rows from tables with hundreds of billions of rows: count first, back up to a side table, then choose between ALTER DELETE per partition, lightweight DELETE and REPLACE PARTITION. What each costs, how to watch it, and why one slow replica set the pace.

The job sounded small. A few large fact tables had accumulated rows that referenced entities that no longer existed in the dimension tables: decommissioned sensors, a retired metric name. The orphan rows were about a tenth of the biggest table, which held hundreds of billions of rows in several hundred monthly partitions. Nobody was inserting new orphans, there was no deadline beyond “this is wasting disk and confusing reports”, and the delete itself was one predicate. It still turned into careful work, and the interesting part was not the predicate.

This is the log of how that went on a staging cluster that mirrors production, rewritten on the demo schema so you can run the mechanics yourself. The cluster was replicated, on a 25.x server; the local runs below are on clickhouse local 26.4.

The setup, in demo terms

CREATE TABLE sensors (sensor_id UInt32) ENGINE = MergeTree ORDER BY sensor_id;
INSERT INTO sensors SELECT number + 1 FROM numbers(95);          -- sensors 96..100 were decommissioned
CREATE TABLE metric_catalog (metric LowCardinality(String)) ENGINE = MergeTree ORDER BY metric;
INSERT INTO metric_catalog VALUES ('temp'), ('humidity'), ('pressure');   -- 'legacy' was retired

CREATE TABLE metrics (ts DateTime, sensor_id UInt32, metric LowCardinality(String), value Float64)
ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (metric, sensor_id, ts);
INSERT INTO metrics
SELECT toDateTime('2026-01-01 00:00:00') + number * 40,
       1 + number % 100,
       if(number % 21 = 0, 'legacy', ['temp','humidity','pressure'][1 + number % 3]),
       rand() % 1000 / 10
FROM numbers(400000);

The doomed rows are those whose sensor_id is missing from sensors, or whose metric is missing from metric_catalog, or both. In production the equivalent predicate was NOT IN (SELECT ... FROM dimension FINAL) against two ReplacingMergeTree dimension tables, and the first cleanup design assumed the list of bad ids would be small and could be frozen into a literal IN (...) list. The bad ids did cluster in a few ranges, but the number of doomed rows behind them was large enough that freezing a list would have told us nothing about the cost.

Count first, and count by reason

Before writing any DELETE, get a number you can compare against later, broken down by why each row is doomed. The breakdown matters because the two causes had very different sizes, and the one people had in mind (the missing entities) turned out to be the smaller one on two of the tables:

SELECT toYYYYMM(ts) AS part,
       count() AS total,
       countIf(sensor_id NOT IN (SELECT sensor_id FROM sensors) AND metric IN (SELECT metric FROM metric_catalog)) AS bad_sensor_only,
       countIf(sensor_id IN (SELECT sensor_id FROM sensors) AND metric NOT IN (SELECT metric FROM metric_catalog)) AS bad_metric_only,
       countIf(sensor_id NOT IN (SELECT sensor_id FROM sensors) AND metric NOT IN (SELECT metric FROM metric_catalog)) AS bad_both,
       countIf(sensor_id NOT IN (SELECT sensor_id FROM sensors) OR metric NOT IN (SELECT metric FROM metric_catalog)) AS doomed,
       round(100 * doomed / total, 1) AS pct
FROM metrics GROUP BY part ORDER BY part;
   part   total  bad_sensor_only  bad_metric_only  bad_both  doomed  pct
 202601   66960             3186             3030       159    6375  9.5
 202602   60480             2881             2736       144    5761  9.5
 202603   66960             3190             3029       160    6379  9.5
 ...
 202607    9040              433              408        22     863  9.5

On the real tables this scan was the expensive part, in a way that surprised us. The smaller tables answered in seconds. The biggest one took about 40 minutes for a full scan, and it would have to be repeated for every question we wanted to ask about partition distribution. The fix is to scan once and keep the result, which leads to the next step.

Back up the rows you are about to delete

Copy the doomed rows into a plain, non-replicated MergeTree table on one node, with a name nobody will mistake for production data (deleteme_ plus the table and a date). Same columns, same partition key, same sort key, so the copy is cheap and a later restore is a REPLACE PARTITION or an INSERT SELECT away:

CREATE TABLE deleteme_metrics_20261014 AS metrics;
INSERT INTO deleteme_metrics_20261014
SELECT * FROM metrics
WHERE sensor_id NOT IN (SELECT sensor_id FROM sensors) OR metric NOT IN (SELECT metric FROM metric_catalog);
SELECT count() AS backed_up FROM deleteme_metrics_20261014;   -- 38096 here; compare with the sum of 'doomed' above

That table is now the cheap place to ask every follow-up question: rows per partition, which partitions are touched at all, how the orphan share changes over time. On the production table the answer was far from uniform: a long tail of small old partitions, a band of recent-ish months holding most of the doomed rows (billions each), and a handful of modern partitions with almost none. Without the backup, each of those questions was another 40 minute scan.

We also decided, deliberately, not to freeze the list of bad ids into a literal. A snapshot of the dimension tables would be stale by the time the delete ran, and it would give a false sense of precision. Instead the same NOT IN (SELECT ... FINAL) predicate is used in the backup, the delete and the verification, with a per-partition check afterwards that the number deleted equals the number backed up. If the dimension tables change in between, that check fails loudly.

Three ways to remove the rows

On the demo data, each method on one partition:

1. ALTER TABLE ... DELETE IN PARTITION, the classic mutation, scoped so it only rewrites one partition’s parts:

ALTER TABLE m_alter DELETE IN PARTITION '202601'
  WHERE sensor_id NOT IN (SELECT sensor_id FROM sensors) OR metric NOT IN (SELECT metric FROM metric_catalog)
  SETTINGS mutations_sync = 2;
SELECT command, parts_to_do, is_done FROM system.mutations WHERE table = 'm_alter';
SELECT name, rows FROM system.parts WHERE table = 'm_alter' AND partition = '202601' AND active;
command:     (DELETE IN PARTITION '202601' WHERE ...)    parts_to_do: 0    is_done: 1
202601_3_3_0_8   60585        -- 66960 - 6375; the _8 is the mutation version

The order is DELETE IN PARTITION '...' WHERE ..., the partition clause before the filter. We spent a few minutes at the wrong end of that once, trying WHERE ... IN PARTITION, which is a parse error.

2. Lightweight DELETE FROM, which writes a mask and rewrites nothing at first:

DELETE FROM m_light WHERE toYYYYMM(ts) = 202602
  AND (sensor_id NOT IN (SELECT sensor_id FROM sensors) OR metric NOT IN (SELECT metric FROM metric_catalog))
  SETTINGS mutations_sync = 2;
SELECT count() FROM m_light WHERE toYYYYMM(ts) = 202602;
SELECT name, rows, has_lightweight_delete FROM system.parts WHERE table = 'm_light' AND partition = '202602' AND active;
count():        54719                                  -- the table already looks clean
202602_3_3_0_8  60480  has_lightweight_delete = 1      -- but the part still holds every row

After OPTIMIZE TABLE m_light PARTITION '202602' FINAL the part is 202602_3_3_1_8 with 54719 rows and has_lightweight_delete = 0. Queries see the right answer immediately; the disk gets its space back only when a merge rewrites the part. The mutation entry is UPDATE _row_exists = 0 WHERE ...: a mask, not a rewrite.

3. REPLACE PARTITION from a clean copy: build the partition without the bad rows in a side table, then swap it in atomically.

CREATE TABLE clean_stage AS metrics;
INSERT INTO clean_stage
SELECT * FROM m_repl WHERE toYYYYMM(ts) = 202603
  AND sensor_id IN (SELECT sensor_id FROM sensors) AND metric IN (SELECT metric FROM metric_catalog);

-- the gate: live total minus keepers must equal what the backup holds for this partition
SELECT (SELECT count() FROM m_repl WHERE toYYYYMM(ts) = 202603) AS live_total,
       (SELECT count() FROM clean_stage) AS keepers,
       live_total - keepers AS doomed_by_difference,
       (SELECT count() FROM deleteme_metrics_20261014 WHERE toYYYYMM(ts) = 202603) AS doomed_in_backup;

ALTER TABLE m_repl REPLACE PARTITION '202603' FROM clean_stage;
live_total  keepers  doomed_by_difference  doomed_in_backup
66960       60581    6379                  6379
-- after the swap: 202603_9_9_0 holds 60581 rows, doomed_left = 0, clean_stage still holds its copy
ALTER ... DELETE IN PARTITION lightweight DELETE FROM REPLACE PARTITION
Cost rewrites every affected part, all columns one mask column, then a merge later one full read of the kept rows, written once into a side table
Space back when the mutation lands after the next merge of each part at the swap
Where it runs on every replica, as a queued mutation the same, but the work is small once, on the node you choose; replicas fetch finished parts
Rollback KILL MUTATION (parts already rewritten stay rewritten) KILL MUTATION swap the backup copy back
Bad fit a node whose merge pool is saturated very large deletes; parts stay bloated until merged partitions where most rows are doomed; huge partitions on slow storage

Which one we used where

The first plan, written before anything ran, had a lightweight DELETE FROM for the biggest table, since a single mutation would rewrite every part of a table that size. Two corrections from the operator changed it: do not use ON CLUSTER, and split big deletes by partition.

The ON CLUSTER point is worth stating plainly. On a replicated table, a mutation is one entry in the replication log: submit it on one replica and every replica executes it. ON CLUSTER sends the same statement to every node, so each one creates its own entry for the same data, racing the replication. Submit once, then watch system.mutations on all replicas.

The partition point is the reason the large table did not become a multi-hour single mutation. Per-partition mutations are independently cancellable, give you honest progress (one partition done, three to go), and let you skip partitions that contain nothing to delete, which the backup table tells you in a second. The size threshold we settled on: below about 50 GiB, one mutation for the whole table; above, one per partition.

Where it hurt: one replica sets the pace

The small tables went fine. One mid-sized table, with no partitioning, took most of a working day on a single replica, while the others finished quickly. Nothing was failing: latest_fail_reason was empty and num_tries kept climbing. What the replica was saying is in system.replication_queue:

SELECT hostName() AS host, `table`, new_part_name, type, num_tries, num_postponed, postpone_reason
FROM clusterAllReplicas('my_cluster', system.replication_queue)
WHERE database = 'analytics' AND num_postponed > 5
ORDER BY num_postponed DESC LIMIT 10;

Two postpone_reason strings explained everything. First, Not executing log entry ... because it is not disjoint with part ... that is currently executing: one large consolidating merge, which also applied our mutation version, covered the block range of every smaller queued entry, so they all waited for it. Second, and the real cause: source parts size (6.36 GiB) is greater than the current maximum (0.00 B). The server caps the size of a merge or mutation it will schedule by how many background slots are free: with every slot of the pool taken by long unrelated merges, the cap collapses and nothing new, however small, is allowed to start. The pool was full because that replica sat on much slower disks than its peers and had an enduring backlog.

Things we learned about monitoring this, each of which cost time:

  • num_tries is not a failure count. It counts how often the queue looked at the entry. With an empty last_exception, a high count and a rising num_postponed, the entry is waiting, not broken.
  • A mutation applied through a merge does not show as a mutation in system.merges. Filtering on is_mutation = 1 returned nothing while a consolidating merge that included our mutation version was running. Drop the filter when you are looking for what a mutation is doing.
  • Watch the pool itself: BackgroundMergesAndMutationsPoolTask against BackgroundMergesAndMutationsPoolSize in system.metrics. When they are equal, nothing new will be scheduled.
  • Do not kill other tables’ merges to free slots. They are doing work somebody needs, and the mutation will wait the same time either way.
  • A column alias can shadow the column it is computed from. formatReadableTimeDelta(elapsed) AS elapsed turns the numeric column into a string, and an ETA expression that uses elapsed afterwards then multiplies a string. The fix is just a different alias, which this local run shows:
SELECT formatReadableTimeDelta(elapsed) AS elapsed, formatReadableTimeDelta(elapsed * (1 - progress) / progress) AS eta
FROM (SELECT 120. AS elapsed, 0.25 AS progress);
-- Code: 43. Illegal types String and Float64 of arguments of function multiply
SELECT formatReadableTimeDelta(elapsed) AS elapsed_h, formatReadableTimeDelta(elapsed * (1 - progress) / progress) AS eta
FROM (SELECT 120. AS elapsed, 0.25 AS progress);
-- elapsed_h: 2 minutes   eta: 6 minutes

The query set we kept open for the whole operation (cluster-wide forms need a server; I checked their syntax with clickhouse format):

SELECT hostName() AS host, `table`, mutation_id, parts_to_do, is_done, latest_fail_reason
FROM clusterAllReplicas('my_cluster', system.mutations)
WHERE database = 'analytics' AND NOT is_done ORDER BY create_time;

SELECT hostName() AS host, result_part_name, round(progress * 100, 1) AS pct, elapsed
FROM clusterAllReplicas('my_cluster', system.merges)
WHERE database = 'analytics' AND `table` = 'metrics';

The pivot to REPLACE PARTITION

For the largest table the numbers did not work for an in-place mutation. Every one of the several hundred partitions had to be rewritten on every replica, and the slow replica could not keep up: its mutation sat at the last two parts for the better part of an hour, queued behind its own merge backlog. So the plan changed to build-the-clean-copy-once, swap it in, and let the other replicas fetch finished parts instead of re-running the delete:

  1. Pick the least loaded node with fast disks and create a non-replicated staging table with the exact structure of the target, including codecs and keys.
  2. For one partition: INSERT INTO staging SELECT ... WHERE partition = ... AND <keep predicate>.
  3. Gate: live total minus keepers must equal the doomed count in the backup table for that partition. If not equal, stop and refresh that partition’s backup first.
  4. ALTER TABLE target REPLACE PARTITION 'x' FROM staging. It took seconds on the node where it ran, and the other replicas picked it up as ordinary part fetches.
  5. Verify on every replica: doomed rows are zero, row counts agree, the replication queue drains. Then TRUNCATE the staging table (the swap hard-links its parts into the target, so emptying staging does not affect the replicas).

It worked on the first real partition, with three surprises worth knowing about:

  • The counts looked wrong for good reason. After the insert, count() on the staging table was about 3% lower than the number of rows the insert reported. The target is a ReplacingMergeTree, and staging background-merged away old versions of duplicate keys. Compare raw numbers taken at the same moment (the insert’s reported rows against total - doomed), not a merged count against an unmerged one. We confirmed with one independent scan that returned total, kept and doomed in a single read.
  • Reads of old partitions were slow to ramp. Old data lived on a cold object-storage tier, and the insert crawled for the first minutes before reaching millions of rows per second. A single large partition took minutes, which is fine; budget for the whole band.
  • The slow replica failed the fetch once. Its part download died mid-stream with Unexpected EOF, retried on its own, and succeeded within the quarter hour. The fix that was not a fix: the swap does not spare a struggling replica, it only changes the work from “rewrite” to “download”. So the sweep must go one partition at a time, with all replicas converged before the next one starts, or the slow replica’s queue grows and the failures compound.

At the end of the rehearsal, one partition was cleaned end to end with identical row counts on all replicas and zero orphans, the mechanic was proven, and the plan for the remaining hundreds of partitions was a paced, resumable loop whose speed is set by the slowest replica, not by the amount of work.

What I would do next time

  1. Count by cause, once, into a side table. Scan the big table a single time to fill the deleteme_ backup, then do every follow-up analysis on the backup.
  2. Decide the method by the doomed fraction and partition size, not habit. Small doomed share and small partitions: ALTER ... DELETE IN PARTITION. Tiny, scattered deletes you need to hide now: lightweight DELETE FROM, and accept bloated parts until merged. Large share, big partitions on a slow replica: REPLACE PARTITION.
  3. Look at the weakest replica before you start. The sweep will take as long as its pool and disks allow. Check BackgroundMergesAndMutationsPoolTask against the pool size on every node, and the replication queue depth.
  4. One mutation at a time, scoped by partition, no ON CLUSTER.
  5. Make the verification a gate, not a report: doomed left is zero, backup count equals deleted count, replicas agree.

The surrounding material: how parts and mutations are laid out is in how MergeTree stores data; the commands for watching merges and mutations are collected in the parts, merges and mutations runbook; the disk side of removing data is in the storage and disk runbook; and the general order of operations when something is slow on a live cluster is in the incident playbook. If a cluster is mid-upgrade, read upgrading ClickHouse safely before queueing hundreds of mutations.

field-notesmutations

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.