clickhouse查看空间占用 和 postgresql查看空间占用情况

SELECT
database,
table,
formatReadableSize(size) AS size,
formatReadableSize(bytes_on_disk) AS bytes_on_disk,
formatReadableSize(data_uncompressed_bytes) AS data_uncompressed_bytes,
formatReadableSize(data_compressed_bytes) AS data_compressed_bytes,
compress_rate,
rows,
days,
formatReadableSize(avgDaySize) AS avgDaySize
FROM
(
SELECT
database,
table,
sum(bytes) AS size,
sum(rows) AS rows,
min(min_date) AS min_date,
max(max_date) AS max_date,
sum(bytes_on_disk) AS bytes_on_disk,
sum(data_uncompressed_bytes) AS data_uncompressed_bytes,
sum(data_compressed_bytes) AS data_compressed_bytes,
(data_compressed_bytes / data_uncompressed_bytes) * 100 AS compress_rate,
max_date - min_date AS days,
size / (max_date - min_date) AS avgDaySize
FROM system.parts
WHERE active
GROUP BY
database,
table
ORDER BY
database ASC,
size DESC
)

select
    sum(rows) as totalRows,--总行数
    formatReadableSize(sum(data_uncompressed_bytes)) as data_uncompressed,--原始大小
    formatReadableSize(sum(data_compressed_bytes)) as data_compressed,--压缩大小
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) as data_compressed_rate--压缩率
from system.parts

SELECT 
    partition AS `分区`,
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
WHERE (database IN ('default')) AND (table IN ('table_name'))
GROUP BY partition

 

SELECT 
    column AS `字段名`,
    any(type) AS `类型`,
    formatReadableSize(sum(column_data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(column_data_compressed_bytes)) AS `压缩大小`,
    sum(rows) AS `行数`
FROM system.parts_columns
WHERE (database = 'default') AND (table = 'table_name')
GROUP BY column

 

select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;