PostgreSQL：按五年周期统计各时段数据行的GB级占用大小

针对你的数十亿条记录的大表，分两种场景给出解决方案：

场景1：表已按日期分区（推荐） #

如果表已经基于date列做了范围分区（比如按年/五年划分分区），直接统计分区大小再聚合即可，速度快且准确：

SELECT
  -- 提取分区年份并按五年分组
  floor(extract(year from (regexp_match(relname, '\d{4}'))[1]::int)/5)*5 AS year,
  -- 转换为GB单位，保留1位小数
  round(pg_total_relation_size(oid)/1024/1024/1024::numeric, 1) AS "size(GB)"
FROM pg_class
WHERE relname LIKE 'tablename_%' -- 替换为你的分区表命名规则（比如tablename_2010）
  AND relkind = 'r'
GROUP BY floor(extract(year from (regexp_match(relname, '\d{4}'))[1]::int)/5)*5
ORDER BY year;

说明：

• pg_total_relation_size会统计表数据、索引、TOAST表的总占用空间
• 假设分区命名包含年份（如tablename_2010），用正则提取年份后按五年分组

场景2：非分区表（估算/精确统计） #

非分区表的精确统计会触发全表扫描，对数十亿条记录的表来说耗时极长，优先用估算方法：

快速估算方法 #

利用PostgreSQL的系统统计信息，按时间区间行数占比估算大小：

SELECT
  floor(extract(year from date)/5)*5 AS year,
  round(
    (count(*)::numeric / (SELECT reltuples FROM pg_class WHERE relname='tablename')) *
    (pg_total_relation_size('tablename')/1024/1024/1024::numeric),
    1
  ) AS "size(GB)"
FROM tablename
GROUP BY floor(extract(year from date)/5)*5
ORDER BY year;

注意：执行前建议先跑ANALYZE tablename;更新统计数据，提升估算精度。

精确统计（仅小表或非紧急场景使用） #

如果必须要精确值，用pg_column_size统计单条记录大小再累加，但全表扫描会非常慢：

SELECT
  floor(extract(year from date)/5)*5 AS year,
  round(sum(pg_column_size(t))/1024/1024/1024::numeric, 1) AS "size(GB)"
FROM tablename t
GROUP BY floor(extract(year from date)/5)*5
ORDER BY year;

说明：这个结果只包含表数据本身，不包含索引和TOAST表的大小。

额外建议 #

对于超大规模的表，强烈建议按date列做分区重构，后续的统计、查询、维护都会高效很多。

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