PostgreSQL表磁盘占用查询及按client分组统计技术咨询

1. 获取表的实际磁盘占用大小 #

你使用的pg_relation_size()仅返回表的主堆数据大小，不包含索引、TOAST表（存储大字段的附属表），也未计算表中未被回收的空闲空间（比如删除/更新数据后留下的空洞），这就是查询结果与云服务商提供的307MB差距巨大的原因。

要获取表的完整磁盘占用（涵盖表本身、所有索引、TOAST表的总大小），推荐使用pg_total_relation_size()函数。如果需要细分统计维度，也可以组合pg_table_size()（表+TOAST大小）和pg_indexes_size()（索引单独大小）来查看。

修正后的查询SQL示例：

SELECT 
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS total_disk_usage,
  pg_size_pretty(pg_table_size(quote_ident(table_name))) AS table_with_toast_size,
  pg_size_pretty(pg_indexes_size(quote_ident(table_name))) AS index_only_size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_total_relation_size(quote_ident(table_name)) DESC;

pg_size_pretty()用于将字节数转换为易读的MB/GB格式，方便直观查看。

如果云服务商给出的是整个数据库的磁盘占用，可直接执行：

SELECT pg_size_pretty(pg_database_size(current_database())) AS database_total_size;

2. 按client列分组统计磁盘占用 #

磁盘占用是表级别的统计项，但每个表都包含client列，可通过行数据占比估算的方式按client分组统计，以下两种方案可选：

方案1：单表单独统计（简单直接） #

针对单个表，若假设每行数据大小大致均匀，可用行数占比估算：

SELECT 
  client,
  pg_size_pretty(
    (pg_total_relation_size('your_table_name') * COUNT(*)) / (SELECT COUNT(*) FROM your_table_name)
  ) AS estimated_client_disk_usage
FROM your_table_name
GROUP BY client
ORDER BY estimated_client_disk_usage DESC;

如果表中存在大字段（如text/bytea），行大小差异较大，可通过pg_column_size()计算每行实际数据大小，再结合表总占用比例估算：

SELECT 
  client,
  pg_size_pretty(SUM(pg_column_size(t))) AS actual_client_data_size,
  pg_size_pretty(
    (SUM(pg_column_size(t)) * pg_total_relation_size('your_table_name')) / pg_table_size('your_table_name')
  ) AS estimated_total_disk_usage
FROM your_table_name t
GROUP BY client
ORDER BY estimated_total_disk_usage DESC;

这里的estimated_total_disk_usage会包含该client对应数据所占用的索引、TOAST空间估算值。

方案2：批量统计所有public schema表 #

若要一次性处理public下的所有表，可使用动态SQL自动遍历：

DO $$
DECLARE
  tbl record;
BEGIN
  FOR tbl IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'
  LOOP
    EXECUTE format(
      '
      SELECT 
        ''%s'' AS table_name,
        client,
        pg_size_pretty(SUM(pg_column_size(t))) AS client_data_size,
        pg_size_pretty(
          (SUM(pg_column_size(t)) * %L) / %L
        ) AS estimated_total_usage
      FROM %I t
      GROUP BY client
      ORDER BY estimated_total_usage DESC;
      ',
      tbl.table_name,
      pg_total_relation_size(tbl.table_name),
      pg_table_size(tbl.table_name),
      tbl.table_name
    );
  END LOOP;
END $$;

注意：所有基于行占比的估算无法做到100%精确（索引、TOAST的空间分配并非完全与行数据量线性相关），但足以满足常规统计需求。

内容的提问来源于stack exchange，提问作者MichaelS