clickhouse.guru
Runbooks

Runbook: storage and disk usage

On-call queries for disk pressure: size per table and column, per-disk placement, ROLLUP totals, column cardinality and TTL that deletes only matching rows.

Use this page when a disk alert fires or when you are deciding what to compress, re-type or expire. Each section is one question, one query you can paste, and a note on how to read the answer. Everything here reads system tables, so it is safe on a production node: no query writes anything.

What runs where. Sections that read system.parts, system.columns, system.disks and system.storage_policies work on any server and in clickhouse local. The cluster variants use clusterAllReplicas('my_cluster', ...) and need a configured cluster. The two-disk output in the disk-placement section came from a test table on a hot/cold storage policy; a plain install has a single default disk.

Related reading: tiered storage with multiple volumes, choosing codecs, parts and partitions.

Contents

Which tables use the most disk?

system.parts has one row per data part. Summing the active parts gives the real footprint of every table. Set db and tbl to LIKE patterns; '%' matches everything.

WITH
    'analytics' AS db,
    '%' AS tbl
SELECT
    database,
    `table`,
    formatReadableSize(sum(data_compressed_bytes) AS compressed_bytes) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes) AS uncompressed_bytes) AS uncompressed,
    round(uncompressed_bytes / compressed_bytes, 2) AS compression_ratio,
    sum(rows) AS rows,
    count() AS parts
FROM system.parts
WHERE active
  AND database LIKE db
  AND `table` LIKE tbl
GROUP BY database, `table`
ORDER BY compressed_bytes DESC;
analytics  events   922.23 KiB  3.66 MiB   4.07  105000   2
analytics  metrics   23.79 KiB  40.38 KiB  1.7     2400  12

Reading it:

  • Always filter on active. Inactive parts are old versions waiting for deletion after a merge; counting them double-counts the data.
  • compressed is what the disk pays, uncompressed is what a full scan has to decode. A ratio near 1 means the data hardly compresses (random IDs, already-compressed blobs).
  • A high parts count on a small table means many tiny inserts; continue with the parts runbook (parts per table).
  • system.parts covers MergeTree-family tables only. For a quick figure across all engines use system.tables.total_bytes.

To compare nodes, query through clusterAllReplicas and group by host. Uneven totals across replicas of one shard usually mean a replica is behind or holds leftover parts.

SELECT
    hostName() AS host,
    database,
    `table`,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed
FROM clusterAllReplicas('my_cluster', system.parts)
WHERE active AND database = 'analytics'
GROUP BY host, database, `table`
ORDER BY database, `table`, host;

Which columns use the most disk?

Disk usage is rarely spread evenly. One String column of URLs or JSON often outweighs everything else, and it is the one to attack with a better codec, LowCardinality, or a DROP COLUMN.

SELECT
    name AS column,
    type,
    formatReadableSize(data_compressed_bytes) AS compressed,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed,
    round(data_uncompressed_bytes / data_compressed_bytes, 2) AS compression_ratio
FROM system.columns
WHERE database = 'analytics' AND `table` = 'events'
ORDER BY data_compressed_bytes DESC;
event_time   DateTime                 411.97 KiB  410.16 KiB    1
user_id      UInt64                   262.63 KiB  820.31 KiB    3.12
url          String                   230.29 KiB    1.86 MiB    8.27
duration_ms  UInt32                    15.95 KiB  410.16 KiB   25.72
event_type   LowCardinality(String)      722.00 B  102.82 KiB  145.83
country      LowCardinality(String)      708.00 B  102.81 KiB  148.69

Reading it:

  • A low ratio on a big column means the codec is not helping. Here event_time is stored with the default codec and does not shrink at all, because the timestamps are monotonic and LZ4 cannot see that; CODEC(DoubleDelta, ZSTD) is the usual fix for timestamps, Delta for counters, Gorilla for floats.
  • A huge ratio with a tiny compressed size (event_type, country) is already cheap. Read the top of the list, not the bottom.
  • Small parts are stored in the Compact format, where all columns share one file and per-column sizes are reported as zero. The metrics table in the same test shows exactly that: all four columns read 0.00 B because its 200-row parts are Compact. Sizes become meaningful once parts are Wide (large parts, or min_bytes_for_wide_part = 0). On real tables with real volume this is the normal case.
  • After changing a codec, old parts keep the old encoding until they are merged. system.parts_columns gives the same numbers per part and column, so you can see the mixture; sum column_data_compressed_bytes and column_data_uncompressed_bytes over active parts to reproduce this query.

How do I get database, table and column totals in one query?

GROUP BY ROLLUP adds subtotal rows, and grouping(col) returns 1 on the rows where col was rolled up, so you can label them. system.columns carries the compressed size per column, which makes it a natural fit for “where did the disk go?”.

SELECT
    if(grouping(database) = 1, '(all databases)', database) AS db,
    if(grouping(table)    = 1, '(all tables)',    table)    AS tbl,
    if(grouping(name)     = 1, '(all columns)',   name)     AS col,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed
FROM system.columns
WHERE database = 'analytics'
GROUP BY ROLLUP(database, table, name)
ORDER BY grouping(database), grouping(table), grouping(name), table, name;
analytics        events   country       708.00 B
analytics        events   duration_ms   15.95 KiB
analytics        events   event_time    411.97 KiB
analytics        events   event_type    722.00 B
analytics        events   url           230.29 KiB
analytics        events   user_id       262.63 KiB
analytics        metrics  metric        0.00 B
...
analytics        events   (all columns) 922.23 KiB
analytics        metrics  (all columns) 0.00 B
analytics        (all tables)   (all columns) 922.23 KiB
(all databases)  (all tables)   (all columns) 922.23 KiB

Reading it:

  • Rows with a column name are detail rows; the biggest are your compression targets.
  • (all columns) per table is the table total, (all tables) the database total, the last row the grand total.
  • grouping() is needed because a real empty name cannot be told apart from the subtotal marker.
  • Remove the WHERE to cover every database. To hunt for the heaviest column, order by sum(data_compressed_bytes) instead of by name.
  • The Compact-part caveat from the previous section applies: columns of small parts show 0.00 B.

Which disk holds a table’s data?

With a multi-disk setup (tiered storage), first check what the server loaded:

SELECT name, path, type, formatReadableSize(free_space) AS free, formatReadableSize(total_space) AS total
FROM system.disks;

SELECT policy_name, volume_name, volume_priority, disks, move_factor
FROM system.storage_policies
ORDER BY policy_name, volume_priority;

On a plain install the second query returns a single row, policy default, volume default, disk default. Then see where one table’s active parts live:

SELECT
    disk_name,
    count() AS parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE `table` = 'tiered' AND active
GROUP BY disk_name
ORDER BY disk_name;
cold_disk  1  1000  4.46 KiB
hot_disk   1  1000  4.46 KiB

(from a test table on a hot/cold policy; on a single-disk server every part reports default.) For the whole cluster, group by host as well; this needs a configured cluster.

SELECT hostName() AS host, disk_name, formatReadableSize(sum(bytes_on_disk)) AS size
FROM clusterAllReplicas('my_cluster', system.parts)
WHERE active AND `table` = 'events_local'
GROUP BY host, disk_name
ORDER BY host, disk_name;

Only active parts are counted. Inactive parts that wait for deletion after a merge still take space until cleaned up; drop AND active to include them and compare with the filesystem if df disagrees with your totals.

How many distinct values does each column have?

Cardinality is the number of distinct values in a column. The ratio distinct / rows tells you what the column is good for before you decide on LowCardinality, the order of ORDER BY columns, or a skip index.

SELECT
    count()                    AS total_rows,
    uniq(user_id)              AS users,     users / total_rows     AS user_id_ratio,
    uniq(event_type)           AS types,     types / total_rows     AS event_type_ratio,
    uniq(url)                  AS urls,      urls / total_rows      AS url_ratio,
    uniq(country)              AS countries, countries / total_rows AS country_ratio
FROM events;
total_rows:       105000
users:            5000      user_id_ratio:    0.0476
types:            3         event_type_ratio: 0.0000286
urls:             7         url_ratio:        0.0000667
countries:        3         country_ratio:    0.0000286

Reading it:

  • A tiny ratio (a few distinct values, like event_type or country): candidate for LowCardinality(String) and a good leading column in ORDER BY.
  • A ratio near 1 (an id, a timestamp): put it last in the sorting key, if at all; it gains nothing from LowCardinality.
  • In between, judge by the absolute count. LowCardinality generally pays off up to tens of thousands of distinct values and less beyond.
  • uniq is an approximation (fast, small memory); uniqExact is exact. On these 5000 ids both returned 5000, but on large sets uniq is only close.
  • On a huge table run it over a recent time range or a sample (FROM events SAMPLE ...), not the whole table.

See also: ORDER BY, PARTITION BY and how queries skip data.

How do I expire only some rows with TTL?

A plain TTL removes every row older than an interval. Add DELETE WHERE and only matching rows expire, for example finished items of a work queue:

CREATE TABLE jobs_queue
(
    job_id UInt64,
    payload String,
    state Enum8('new' = 1, 'processing' = 2, 'done' = 3),
    state_changed_at DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY job_id
TTL state_changed_at + INTERVAL 1 MONTH DELETE WHERE state = 'done';

Rows in state new or processing stay forever; done rows disappear a month after their last state change. This replaces a cron job that runs DELETE on a schedule. To add the rule to an existing table:

ALTER TABLE jobs_queue
    MODIFY TTL state_changed_at + INTERVAL 1 MONTH DELETE WHERE state = 'done';

By default this also applies to existing rows (materialize_ttl_after_modify). In a test with five rows (two done rows 60 and 45 days old, one fresh done, one new and one processing row, both 90 days old), all five were still visible right after the insert; after OPTIMIZE TABLE jobs_queue FINAL the two old done rows were gone and the other three stayed.

Notes:

  • Deletion happens during merges, so it is not instant, and expired rows are not filtered out of reads in the meantime. OPTIMIZE TABLE ... FINAL forces it; merge_with_ttl_timeout (4 hours by default) controls how often TTL merges are scheduled.
  • On replicated tables TTL merges are limited to max_number_of_merges_with_ttl_in_pool (2 by default); a backlog shows up as postponed entries, see TTL merges postponed in the replication queue.
  • Rules combine: TTL ts + INTERVAL 1 MONTH TO VOLUME 'cold', ts + INTERVAL 1 YEAR DELETE.
  • For time-partitioned tables, ttl_only_drop_parts = 1 turns expiry into cheap whole-part drops instead of rewrites. Background: mutations, lightweight DELETE and TTL.

runbookstorage

Discussion

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