Greenplum 查找数据目录占用最大的表

背景

社区中某同学提出问题：
某环境磁盘占用空间较大，于是想找到数据目录占用最大的表。使用常规查询找不出来，于是到数据目录下分析filenode，找到3个filenode占了400G。然而根据filenode从pg_class中确找不到对应的relfilenode。
查询方法为：

select oid,relname,relfilenode from pg_class where relfilenode=...
1

分析

PostgreSQL中的表会有一个RelFileNode值指定这个表在磁盘上的文件名（外部表、分区表除外）。一般情况下在pg_class表的relfilenode字段可以查出这个值，但是有一些特定表在relfilenode字段的查询结果是0，如某些系统表。
另外，对于普通表，将对表执行truncate，vacuum full等操作后，会重写这个表的数据，也会引发这个表relfilenode值的变更。
具体可以参考文章https://blog.csdn.net/weixin_46199817/article/details/113108402 中的描述。
这意味着，某个对象的Oid和RelFileNode可能并不能完全对应起来，所以，当在磁盘目录中找到一个filenode，但从pg_class中找不到也是有可能的。

解决

通过pg_relation_filenode()将oid转化为relfilenode，其结果永远是正确的，比从pg_class中查询更为准确。
因此，针对这位同学的需求，既然已经从磁盘文件中找到对应占用空间大的oid，我们就可以通过以下语句找到对应的表。

select oid,pg_relation_filenode(oid),relname from pg_class where pg_relation_filenode(oid)=...
1

正常情况下，通过以上命令就能找到对应的表了。
不过，如果之前系统发生了异常情况，比如在大量写入数据时突然系统宕机，那么有可能导致数据文件残留的情况。针对于这种情况，我们可以通过查看对应产生文件的时间是否是异常发生的时间，一般情况下能对应上。针对这种情况，这样的文件可以直接删除即可，当然，为了保险起见，我们可以备份到一个别的目录。