PostgreSQL表膨胀终结者

(译自:http://blog.dataegret.com/2018/03/postgresql-bloatbusters.html

无论是DBA还是开发者,只要工作中使用PostgreSQL,多多少少都会遇到vacuum:DBA需要配置vacuum,开发者遇到因vacuum导致的数据库性能下降、慢查询问题。

背景

vacuum对开发者来说是个黑盒,DBA知道vacuum很重要,vacuum的配置也很重要。不怎么熟悉vacuum的人只知道vacuum是清理数据库的垃圾收集器。有人会问,vacuum是怎么清理数据库的。可在官方文档中找到十分详细的答案,在这里只做个简短的概要。

处理数据时,PostgreSQL会为每一个客户端提供单独的快照。客户端对快照中的数据进行更新和删除,一些数据最终变成过期数据,但它们仍存在于数据库中,还占用表数据文件和索引数据文件的空间。因此数据文件中会存在碎片,引起整体数据库性能下降。这时候vacuum出来干活了,vacuum的主要任务就是清理表和索引中不需要的数据(死数据),为新加入的数据清理出来空间。

世界并不完美,vacuum也有存在的问题。比如,vacuum完成清理工作后,那些空间并没有真正被释放掉,只能被vacuum清理过的表和索引所利用。虽然看上去表和索引大小都已经减少了,但是实际上和vacuum清理前的大小是一样。这让很多刚开始使用Postgres的用户感到困惑。

还有,某些场景下vacuum比较无能为力,无法有效完成工作。比如执行空事务,vacuum会推迟清理死数据导致表和索引膨胀。空事务,甚至经常执行长事务都会对数据库有影响,应避免这些操作。

还有个有趣的地方,假设有一张表上有大量索引,表上还有密集的更新操作。遇到这样的情况,大量更新会触发一次新的vacuum,有可能会发现这张表上有无穷无尽的vacuum在执行。无论怎么修改vacuum的配置都没有用,结果导致——表膨胀了。

好像没有什么vacuum配置有用,也无法拿回占用的空间。当然,Postgres还有个vacuum full操作可以实现,但它会block所有对表的访问,不只是写操作,读操作也会全部block。很多情况下这是不可接受的,尤其是生产环境。

还是有门路的,PostgresSQL是开源的,还有个很棒的社区。社区为PostgreSQL提供了很多工具,并且有些工具能够突破vacuum full的限制,在不阻止表的访问的情况下完成vacuum full的功能。

pg_repack

第一个工具是:pg_repack。它有点像吃了药的vacuum full,创建一个新表,将数据从旧表移动到新表。为了避免表被独占锁定,创建了一个额外的日志表来记录原始表的改动,还添加了一个把INSERT / UPDATE / DELETE操作记录到日志表的触发器。当原始表中的数据全部导入到新表中,索引重建完毕,日志表的改动全部完成,pg_repack会连同新索引,用新表替换旧表,并将原旧表Drop掉。整个过程非常简单,非常可靠,但是需要注意的是——需要额外剩余足够的磁盘空间(原表大小 + 索引 + 额外的日志表空间)。

以上是pg_repack的主要功能,但pg_repack还有一些有意思的功能,可以让你:

  • 仅处理没有父表的索引
  • 在不同的表空间中移动表和索引,比如在快速的SSD和慢速的HDD存储分区之间进行移动归档操作,这样有助于平衡client的IO
  • 执行根据指定columns进行CLUSTER(聚簇)重排序
  • 通过并行工作的workers加速索引建立
  • 取消阻碍pg_repack的查询

pg_repack看起来很不错,重视速度,所以应该要记住,不正确的使用会导致大量磁盘IO,降低client的查询性能。

pgcompacttable

另一个工具是:pgcompacttable(也被称为compactor),是另一种用于压缩表和索引,达到理想大小的工具。

pgcompacttable的工作方式和pg_repack不一样。它使用PostgreSQL的一个有趣特性,在执行INSERT和UPDATE操作时,会将所有新版本的行移到表最开始的可用空间。这个特性十分重要,因为如果从末端反向开始更新所有行,最终所有可用空间被这些行填充,并将表尾部的空间全部释放以便让定期vacuum进行truncate。它使用特殊的系统column——ctid,才有可能更新行,并将行从表末端移到表开头。这样一来,pgcompacttable通过批量更新和vacuum强制PostgreSQL将行数据在表中移动,最终整个表被整理压缩得很好。

pgcompacttable还有一些额外的特性和pg_repack相似,可以说一嘴:

  • 只处理没有父表索引
  • 能够调整pgcompacttable的影响

总之,pg_repack可能会导致数据库性能暂时下降,pgcompacttable则是一个更安全的选项。后者不需要为表维护预留空间(但仍需要为索引重建保留空间),并且能够在压缩表的同时保证client的查询性能。

结论

选谁?以下是个简短问卷能帮你方便做选择:

  1. 数据库是否需要保证性能?是否需要保证client查询性能?

    是 - pgcompacttable,否 - pg_repack

  2. 是否需要在表空间之间移动表或索引?

    是 - pg_repack,否 - pgcompacttable

  3. 是否有预留的空间进行维护?

    是 - pg_repack,否 - pgcompacttable

不管怎样,将两个工具都放在服务器上总不会错的。

Show Comments