PostgreSQL 14中pg_profile数据库增长与表、索引增长数据不匹配排查

• 系统目录表的增长
  PostgreSQL的核心系统目录（如pg_class、pg_statistic、pg_attribute等）会随着数据库对象的创建（比如序列、函数、触发器、临时表）、统计信息更新而占用更多磁盘空间。pg_profile的表增长统计通常仅覆盖用户自定义表，未包含系统目录表的增量。
  验证方法：分别统计用户对象总大小与系统目录总大小，对比周期前后的差值：

  -- 用户对象总大小
  SELECT sum(pg_total_relation_size(relid)) AS user_objects_size
  FROM pg_class
  WHERE relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname IN ('pg_catalog', 'information_schema', 'pg_toast'));
  
  -- 系统目录总大小
  SELECT sum(pg_total_relation_size(relid)) AS system_catalog_size
  FROM pg_class
  WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog');
  

• TOAST表的未统计增量
  存储大字段（text、bytea等）的TOAST表是用户表的关联附属表，会独立占用磁盘空间。若pg_profile仅统计主表的增长，未包含对应TOAST表的增量，会导致数据缺口。
  验证方法：查询所有TOAST表的大小及变化：

  SELECT c.relname AS toast_table, pg_total_relation_size(c.relid) AS toast_size
  FROM pg_class c
  JOIN pg_class mt ON c.reltoastrelid = mt.oid
  WHERE c.relkind = 't';
  

• 事务ID回卷防护相关文件
  PostgreSQL 14中，pg_xact和pg_commit_ts目录下的事务日志文件会随着事务数量累积而增长，尤其是当数据库长时间运行、事务ID接近回卷阈值时，这部分文件会占用大量空间。它们属于数据库目录的一部分，会被pg_database_size()统计，但不属于常规表/索引范畴。
  验证方法：直接查看数据库数据目录下pg_xact、pg_commit_ts文件夹的磁盘占用，对比周期前后的大小变化。

• 未清理的死元组（表膨胀）
  若表存在大量更新、删除操作，产生的死元组在autovacuum未及时清理时，会持续占用磁盘空间。pg_profile的表增长统计可能仅计算逻辑数据的增量，未包含死元组带来的膨胀空间。
  验证方法：查看用户表的死元组数量及实际磁盘大小变化：

  SELECT relname, n_dead_tup, pg_table_size(relid) AS table_disk_size
  FROM pg_stat_user_tables;
  

• 异常残留的临时表文件
  临时表通常在会话结束后自动删除，但如果客户端崩溃、会话异常终止，临时表的磁盘文件可能未被及时清理。这些文件属于数据库目录的一部分，会被pg_database_size()统计，却不会出现在常规表/索引统计中。
  验证方法：检查数据库数据目录下base/pgsql_tmp文件夹的大小，或开启track_temp_files参数后，用SELECT * FROM pg_stat_tmpfiles;查看临时文件记录。

• 扩展（Extension）对象的增长
  若数据库安装了第三方扩展（如pg_stat_statements、timescaledb等），这些扩展会创建自身的表、索引或存储对象。pg_profile的统计可能未覆盖这些扩展所属的对象，导致增量遗漏。
  验证方法：查询扩展所在的schema，统计该schema下所有对象的大小变化：

  -- 查看扩展及所属schema
  SELECT extname, nspname FROM pg_extension;
  
  -- 统计扩展schema下的对象总大小
  SELECT sum(pg_total_relation_size(relid)) AS extension_objects_size
  FROM pg_class
  WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = '<扩展schema名>');
  

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