PostgreSQL库大小统计与各表大小汇总值不一致的排查及修复

问题现象 #

• 全库统计维度：对report数据库执行整体大小查询，返回总大小为134 GB，对应查询语句：

  SELECT pg_size_pretty( pg_database_size('report') );
  

• public模式表统计维度：对public schema下的表按大小倒序统计，所有表累加大小仅约数百MB，对应查询语句：

  select table_name, pg_size_pretty( pg_relation_size(quote_ident(table_name)) )
  from information_schema.tables
  where table_schema = 'public'
  order by pg_relation_size(quote_ident(table_name)) desc
  

  统计结果靠前的表分别为：zinc_source_bind（188 MB）、zinc_blob_bind（122 MB）、zinc_agent_info_ex（121 MB）、zinc_blob（110 MB）、zinc_main（86 MB）、zinc_attribs_bind（49 MB）、zinc_not_readed（47 MB）、zinc_aggregate_info（46 MB）、zinc_gate（39 MB）、zinc_source（2488 kB）。

差异原因排查步骤 #

• 排查非public schema的对象占用
  pg_database_size统计的是当前数据库下所有schema内的表、索引、TOAST存储的全量磁盘占用，仅统计public schema的表会完全遗漏其他schema的空间占用。可执行以下SQL统计全库各schema的空间占比，定位是否存在非public schema下的大对象：

  SELECT nspname AS schema_name, pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS total_size 
  FROM pg_class c 
  JOIN pg_namespace n ON c.relnamespace = n.oid 
  WHERE c.relkind IN ('r','i','S','to','t','m') 
  GROUP BY nspname 
  ORDER BY sum(pg_total_relation_size(c.oid)) DESC;
  

  常见的大占用来源包括：系统内置schema（pg_catalog、information_schema）、业务自建的非public schema、数据库扩展自带的schema（如timescaledb、postgis、pgvector的扩展表）。
• 排查原查询的统计口径偏差
  原查询使用pg_relation_size函数统计，仅计算表本体主文件的大小，未纳入三类易产生大量占用的内容：
  • 表关联的所有索引大小
  • 表的TOAST外部存储数据（大文本、二进制字段默认存储在独立的TOAST表中，通常可占单表总大小的30%以上）
  • 物化视图、非普通类型的存储对象
• 排查长事务持有的未释放磁盘空间
  当数据库中存在长事务时，如果其他会话已经对长事务引用过的表执行了DROP操作，PG不会立刻删除对应表的物理文件，会直到长事务结束后才回收空间。此时pg_database_size会统计到这部分物理占用，但因为表已经被逻辑删除，不会出现在information_schema.tables的统计结果中，是造成大规模统计差异的最常见诱因。可通过以下SQL排查长事务：

  SELECT pid, now() - xact_start AS xact_duration, query 
  FROM pg_stat_activity 
  WHERE state != 'idle' AND now() - xact_start > interval '1 minute' 
  ORDER BY xact_duration DESC;
  

• 排查系统大对象的占用
  通过lo_create创建的大对象（Large Object）默认存储在pg_catalog schema下的pg_largeobject表中，不会出现在public schema的业务表统计结果中，如果长期未清理失效的大对象，很容易积累数十GB的占用。

对应修复方案 #

• 明确非public schema的空间归属：对统计定位到的非public schema下的大对象，属于业务实际需要的就做好容量规划，属于无用的测试、历史遗留对象的，直接执行DROP清理；对膨胀率较高的存量表，在业务低峰期通过VACUUM FULL或pg_repack在线回收磁盘空间。
• 对齐统计口径：将单表统计的函数从pg_relation_size替换为pg_total_relation_size，该函数会自动计算表本体、关联索引、TOAST表的总大小，和pg_database_size的统计口径完全对齐。对占比较高的TOAST大字段，评估迁移到外部对象存储，降低数据库存储压力。
• 释放长事务持有的未回收空间：定位到无业务意义的长事务后，评估影响执行SELECT pg_terminate_backend(<长事务pid>);终止连接，PG会在事务结束后自动回收被持有、未释放的磁盘空间，统计值会和实际业务表大小对齐。
• 清理无用大对象：执行系统自带的vacuumlo工具扫描全库未被业务表引用的大对象，执行lo_unlink清理，释放无意义的磁盘占用。
• 常态化空间治理：对频繁执行UPDATE/DELETE的大表关闭autovacuum的长延迟配置，定期巡检表膨胀率，避免死元组长期占用磁盘空间。

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