clickhouse maintence.md

Get all user tables & size

SELECT database, table, storage_policy, round(total_bytes / 1024 / 1024) as MiB
FROM system.tables 
WHERE storage_policy IS NOT NULL AND lower(database) NOT IN ('system', 'information_schema')
ORDER BY MiB DESC;

Get all user tables with TTLs

SELECT database, table, create_table_query
FROM system.tables 
WHERE lower(database) NOT IN ('system', 'information_schema') AND create_table_query LIKE '%TTL%'
FORMAT Vertical

Get all active user partitions

SELECT database, table, name, round(bytes_on_disk / 1024 / 1024) as MiB, disk_name
FROM system.parts
WHERE database NOT IN ('system', 'information_schema') AND active = 1;