How to Check Table Size in ClickHouse

How do I check the size of a table in ClickHouse?

Use System tables or SHOW commands to calculate disk space occupied by a ClickHouse table.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why check table size in ClickHouse?

Understand storage usage, forecast hardware needs, and spot abnormal growth early. Table-level size metrics help you plan TTL policies and shard placement.

Which system tables store size information?

system.parts (per part, per replica) and system.tables (aggregated per table) hold the byte counts you need. They update after each merge.

What is the simplest query?

Select sum(bytes_on_disk) from system.parts filtered by database and table. This returns the exact bytes currently on disk, including unmerged parts and projections.

How do I check size of every table?

Aggregate bytes for each table in system.tables. Order by size desc to see heavy hitters quickly.

How can I convert bytes to human-readable units?

Use ClickHouse functions formatReadableSize() or divide by 1024³ for GB. Presenting sizes clearly speeds decision making.

How to monitor table size over time?

Schedule the size query in your observability stack and persist results to a metrics table. A simple cron + INSERT pattern is sufficient.

Best practice: include active and detached parts

Filter active in (1,0) to capture detached parts that still consume disk. Ignore only parts where remove_time > 0.

Best practice: account for compressed data

bytes_on_disk is after compression. If you need raw size, multiply by compression_ratio from system.parts.

Why How to Check Table Size in ClickHouse is important

How to Check Table Size in ClickHouse Example Usage


-- Size of Orders table and its partitions in GB
SELECT
    partition,
    formatReadableSize(sum(bytes_on_disk)) AS part_size,
    count() AS parts
FROM system.parts
WHERE database = 'ecommerce'
  AND table = 'Orders'
GROUP BY partition
ORDER BY sum(bytes_on_disk) DESC;

How to Check Table Size in ClickHouse Syntax


-- Per-table size in bytes
SELECT sum(bytes_on_disk) AS table_bytes
FROM system.parts
WHERE database = 'ecommerce'
  AND table = 'Orders'
  AND active = 1;

-- All tables ordered by size
SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS table_size
FROM system.parts
WHERE database = 'ecommerce'
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

-- SHOW SIZE style helper (ClickHouse ≥23.3)
SHOW TABLES SIZE FROM ecommerce;

Common Mistakes

Relying on system.tables.data_uncompressed_bytes. This field shows size before compression, leading to overestimates. Instead use bytes_on_disk for actual disk usage.
Ignoring detached parts by filtering active = 1 only. Detached parts still occupy space until removed. Use active IN (0,1) to see the full footprint.