干货｜分析PostgreSql单表60w数据却占用55g空间

问题描述

突然接到运维的通知，说磁盘预发布环境磁盘空间不够，细查之下发现是由于某个表的数据太大导致的，但是查看了下数据库表发现，实际的表数据量只有60w条，很明显表哪里出问题了，一开始以为是犹豫表的设计不合理索引导致的数据量大，细看之下发现挺正常的。正在焦虑蹉跎的时候，有幸得到朋友圈大佬的指点，是死亡元组太多导致的只需要执行vacuum full清理死亡元组就好，查看了相关的博客稳定发现postgresql居然会保存mvcc多版本修改记录，简单理解就是，postgresql对你所做的修改和删除都会保存记录，不会清理释放空间。这让我顿时想到Mysql的MVCC，但是mysql的undo log也只记录执行操作的相反记录保留最新的记录，而redo log记录数据页的变更，但是大小是固定的，都可以通过配置参数配置。

单表超过55g实际数据却只有60w条

回到postgresql数据库上，一开始提到的死亡元组问题提到了VACUUM命令 简单了解之下只是看到一些博客说pg会保留更新删除数据行的MVCC版本记录数据，完了又看到官网的这句话：
简单的 VACUUM（不带FULL）简单地收回空间并使其可以被重用。这种形式的命令可以和表的普通读写操作并行，因为它不会获得一个排他锁。但是，这种形式中额外的空间并没有被还给操作系统（在大多数情况下），它仅仅被保留在同一个表中以备重用。VACUUM FULL将表的整个内容重写到一个新的磁盘文件中，并且不包含额外的空间，这使得没有被使用的空间被还给操作系统。这种形式的命令更慢并且在其被处理时要求在每个表上保持一个排他锁。敲黑板画重被重用,这留着这些数据方便重用，但是几乎不可能
这时我就觉得很扯，为什么不清理，这么一直留着只会表得数据越来越大，感觉好鸡肋啊，虽然细细的研究之下我发现官网一句特别的提示：
24.1.2. 恢复磁盘空间
在PostgreSQL中，一次行的UPDATE或DELETE不会立即移除该行的旧版本。这种方法对于从多版本并发控制（MVCC，见第 13 章）获益是必需的：当旧版本仍可能对其他事务可见时，它不能被删除。但是最后，任何事务都不会再对一个过时的或者被删除的行版本感兴趣。它所占用的空间必须被回收来用于新行，这样可避免磁盘空间需求的无限制增长。这通过运行VACUUM完成。
VACUUM的标准形式移除表和索引中的死亡行版本并将该空间标记为可在未来重用。不过，它将不会把该空间交还给操作系统，除非在特殊的情况中表尾部的一个或多个页面变成完全空闲并且能够很容易地得到一个排他表锁。相反，VACUUM FULL通过把死亡空间之外的内容写成一个完整的新版本表文件来主动紧缩表。这将最小化表的尺寸，但是要花较长的时间。它也需要额外的磁盘空间用于表的新副本，直到操作完成。
例行清理的一般目标是多做标准的VACUUM来避免需要VACUUM FULL。自动清理守护进程尝试这样工作，并且实际上永远不会发出VACUUM FULL。在这种方法中，其思想不是让表保持它们的最小尺寸，而是保持磁盘空间使用的稳定状态：每个表占用的空间等于其最小尺寸外加清理之间被用完的空间。尽管VACUUM FULL可被用来把一个表收缩回它的最小尺寸并将该磁盘空间交还给操作系统，但是如果该表将在未来再次增长这样就没什么意义。因此，对于维护频繁被更新的表，适度运行标准VACUUM运行比少量运行VACUUM FULL要更好。
一些管理员更喜欢自己计划清理，例如在晚上负载低时做所有的工作。根据一个固定日程来做清理的难点在于，如果一个表有一次预期之外的更新活动尖峰，它可能膨胀得真正需要VACUUM FULL来回收空间。使用自动清理守护进程可以减轻这个问题，因为守护进程会根据更新活动动态规划清理操作。除非你的负载是完全可以预估的，完全禁用守护进程是不理智的。一种可能的折中方案是设置守护进程的参数，这样它将只对异常的大量更新活动做出反应，因而保证事情不会失控，而在负载正常时采用有计划的VACUUM来做批量工作。
详细见24.1.2. 恢复磁盘空间这就解释了为什么一个表明明只有60w数据却空间占用55g，一条记录被更新之后他的快照依然会保留，不会立刻删除，当更新或者删除特别频繁的时候，空间占用就会特别的明显了，vacuum命令类似于标记一些过时的数据为垃圾数据（这有点像操作系统，当你的把数据删除了，其实他只是标记删除，完了继续堆积在新的未存放数据的空间，这就是说为什么理论上，不存在彻底的删除，除非你把磁盘填满之后重新覆盖），可以被之后的新记录覆盖，对于急着释放空间页面请求又不是特别多的情况下还是需要vacuum full来紧急释放空间，另外官网也不建议频繁的vacuum full来代替vacuum毕竟，版本记录有时候还是有用的。

查看元祖

备注：需要额外安装插件支持
https://docs.postgresql.tw/appendixes/additional-supplied-modules

select * from pgstathashindex('con_hash_index');

输出字段是：

字段 类型 描述
version integer HASH版本号
bucket_pages bigint 存储桶页面的数量
overflow_pages bigint 溢出页面的数量
bitmap_pages bigint 位图页数
unused_pages bigint 未使用页面的数量
live_items bigint 活元组的数量
dead_tuples bigint 死元组的数量
free_percent float 自由空间的百分比

pgstattuple

解决

注意

总结

突然发现PG数据库解决MVCC的方式如此独特，不过总的来说都解决了版本控制的逻辑，相比mysql的redo,undo,binlog来解决MVCC而言，每个DB都有自己独特的使用场景。

引用

• 【图文详解】MySQL系列之redo log、undo log和binlog详解
• PostgreSQL中文文档
• PostgreSQL官方文档