clickhouse get tables size

clickhouse-get-tables-size.sql

SELECT table,
    formatReadableSize(sum(bytes)) as size,
    min(min_date) as min_date,
    max(max_date) as max_date
    FROM system.parts
    WHERE active
GROUP BY table

any idea how to remove the parts. in parts.database and parts.table without writing down all columns again?

brilliant. I rounded the ratio for a smaller column

round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes), 3) AS ratio

Hey all, awesome scripts! Many thanks for this contributions ❤️

awesome scripts!

I add a compression percentage as find them easier than ratios at-a-glance

select parts.*,
       columns.compressed_size,
       columns.uncompressed_size,
       columns.compression_ratio,
       columns.compression_percentage
from (
         select table,
                formatReadableSize(sum(data_uncompressed_bytes))          AS uncompressed_size,
                formatReadableSize(sum(data_compressed_bytes))            AS compressed_size,
                round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes), 3) AS  compression_ratio,
                round((100 - (sum(data_compressed_bytes) * 100) / sum(data_uncompressed_bytes)), 3) AS compression_percentage

             from system.columns
             group by table
         ) columns
         right join (
    select table,
           sum(rows)                                            as rows,
           max(modification_time)                               as latest_modification,
           formatReadableSize(sum(bytes))                       as disk_size,
           formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size,
           any(engine)                                          as engine,
           sum(bytes)                                           as bytes_size
    from system.parts
    where active 
    group by database, table
    ) parts on columns.table = parts.table
order by parts.bytes_size desc;

Thanks a lot. Very helpful scripts.

Thx you guys and gals! Real useful sqls! You've just inspired me to code a little exporter to view the presented data in prometheus or grafana.

Thx you guys and gals! Real useful sqls! You've just inspired me to code a little exporter to view the presented data in prometheus or grafana.

Mmm... Smth like this https://github.com/alexeyantropov/clickhouse_table_exporter/blob/main/src/exporter.py

@alexeyantropov do you mind to add some custom queries? Have you finished to code a little exporter?

@alexeyantropov do you mind to add some custom queries? Have you finished to code a little exporter?

I've finished and use it in my production environment. But sure I can add some extra queries and metrics. What have you to suggest?