基于ZFS的PostgreSQL表空间中pg_total_relation_size返回值异常及压缩表+索引实际磁盘占用查询方法

咱们先拆解你遇到的数值矛盾问题，其实本质是PostgreSQL和ZFS统计的是完全不同维度的“大小”，两者都没错，只是统计逻辑不一样：

为什么pg_total_relation_size返回12GB左右？ #

PostgreSQL的pg_total_relation_size统计的是表观大小（apparent size）——也就是PostgreSQL自己写入到文件里的总字节数，它不会去感知底层文件系统的压缩、稀疏等特性。你看到的12GB，其实是：

• 放到ZFS表空间的a_copy表的表观大小（约6GB，对应你用du --apparent-size看到的6GB）
• 还留在默认pg_default表空间（ext4无压缩）的索引的表观大小（约6GB）
  两者加起来刚好是12GB左右，这就完全对应上了。你后来发现索引在默认表空间，这正是关键原因。

而ZFS的zfs list显示的1.22GB是实际磁盘占用——也就是经过ZFS zstd压缩后真正消耗的磁盘空间，这是文件系统级的统计，和PostgreSQL的统计维度完全不同。

怎么优雅查询压缩后表+索引的实际磁盘占用？ #

你的核心需求是批量或单个统计带压缩的表+索引的实际磁盘占用，不用每个表单独建ZFS池，这里给你几个实用方案：

方案1：结合PG系统表+系统工具（du）的脚本化查询

这个方案能精准定位每个表、索引的实际磁盘占用，步骤如下：

1. 先通过PG系统表获取目标对象的物理文件路径：
   执行以下SQL（替换成你的schema和目标表名），可以得到表、索引对应的表空间和文件路径：

   SELECT
     c.relname AS relation_name,
     CASE WHEN c.relkind = 'r' THEN '表' WHEN c.relkind = 'i' THEN '索引' ELSE c.relkind::text END AS relation_type,
     t.spcname AS tablespace_name,
     pg_tablespace_location(t.oid) AS tablespace_path,
     pg_relation_filepath(c.oid) AS full_file_path
   FROM pg_class c
   JOIN pg_tablespace t 
     ON c.reltablespace = t.oid 
     OR (c.reltablespace = 0 AND t.spcname = 'pg_default') -- 处理默认表空间
   WHERE c.relname IN ('a_copy', 'a_copy_pkey') -- 替换成你的表和索引名
     AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public'); -- 替换成你的schema
   

2. 针对每个返回的full_file_path，用du命令分别统计表观大小和实际磁盘占用：
   • 表观大小：du -h --apparent-size /pg/PG_15_202209061/24412/46072*
   • 实际磁盘占用（压缩后）：du -h /pg/PG_15_202209061/24412/46072*
3. 批量处理的话，可以写个shell或Python脚本，把SQL查询的结果遍历一遍，自动执行du命令并汇总结果。比如用Python结合psycopg2库，查询后调用subprocess执行du，最后输出每个对象的实际占用。

方案2：统一表+索引的表空间，用压缩率近似计算

如果能把表和索引都放到同一个ZFS表空间里，就能简化统计：

• 创建索引时指定ZFS表空间：

  CREATE INDEX idx_a_copy_id ON a_copy(id) TABLESPACE zfs;
  

• 此时pg_total_relation_size('a_copy')会返回表+索引的总表观大小，再乘以ZFS的压缩率（从zfs get compressratio pg获取的4.89x），就能得到近似的实际磁盘占用。
  比如总表观大小是12GB，12 / 4.89 ≈ 2.45GB，和实际磁盘占用误差不大（因为同类型数据的压缩率差异很小）。

方案3：利用ZFS工具统计特定目录的占用

如果某个ZFS表空间里的对象不多，且你能定位到单个表的文件目录（比如/pg/PG_15_202209061/24412/下的某几个文件属于同一个表），可以直接用du -h统计这个目录下对应文件的总占用：

du -h /pg/PG_15_202209061/24412/46072*

或者用zfs list查看整个表空间的占用，如果表空间里只有这个表+索引，那直接看USED列的值就行。

总结一下 #

• PostgreSQL和ZFS的统计都没问题：PG统计的是自己写入的总数据量（表观大小），ZFS统计的是压缩后的实际磁盘占用，差异来自文件系统的透明压缩和索引所在表空间的不同。
• 精准统计单个表+索引的实际占用，推荐用方案1；如果能统一表空间，方案2更简便；小范围统计用方案3就行。

备注：内容来源于stack exchange，提问作者Anatoly Alekseev