[转载]再谈PostgreSQL的膨胀和vacuum机制及最佳实践

本文转载自 www.postgres.cn 下的文章:

再谈PostgreSQL的膨胀和vacuum机制及最佳实践http://www.postgres.cn/news/viewone/1/390

还有两个相关的文章也挺不错:

也谈PostgreSQL的Vacuum机制及其最佳实践http://bbs.postgres.cn/news/viewone/1/387

新特性：postgresql的vacuum漫谈https://mp.weixin.qq.com/s/EzRqxPDowf3mqsbV6FkJWA

作者介绍

朱贤文，成都文武信息技术有限公司创始人，PostgreSQL中国用户会核心组成员，熟悉数据库，存储和集群技术；

成都文武信息技术有限公司是PostgreSQL和GreenPlum数据库服务的专业厂商，主要产品是ECOX集群管理系统和Hunghu Cloud，专门运行数据库的私有云系统，带高端存储功能。公司总部位于天府软件园。公司网站 w3.ww-it.cn

写本文的原因

这两天有两篇专门介绍PostgreSQL的vacuum机制的技术文章，得到了比较热烈和正面的反馈，让用户可以比较清楚地理解和使用这个特性。

我个人觉得有点小遗憾：这两篇文章没有跳出技术的角度，分析为什么会有这样的机制和实现。

所以我打算写点文字补充一下，跳出技术角度，来理解和分析为啥会有这两种机制，以及PostgreSQL选择这样的机制又啥好处。最后在总结一下如何用好这种机制，让数据库更快更好地服务我们的生产系统。

如果有不合妥当的地方，欢迎斧正 !

多版本并发控制机制

要说清楚这个事情之前，我们先看看几种基本的多版本并发控制机制的实现方式。实现MVCC的数据库管理系统，当它需要更改某块数据的时候，它不会直接去更改，会创建这份数据的新版本，在新版本进行更改，所以会存储多份版本，每个事务能看见哪一份版本的数据，要看隔离级别的实现方式，通常的办法是数据块的快照。

正是因为这个原因，引入了另一个问题，如何消除老旧的、没有使用的、无用数据(版本)，目前主流上有3种处理实现方式：

• 第一种，以Oracle为代表的，把旧版本数据放入UNDO，新数据放入REDO，然后更改数据。这种方式，旧版本的数据放入了UNDO，所以可以有效避免膨胀。
• 第二种，以SQL Server为代表的，把旧版本的数据写入专门的临时表空间，新数据写入日志，然后去更改数据。这种方式，旧版本的数据放入了专门的临时表空间，所以也可以有效地避免膨胀。
• 第三种，以PostgreSQL为代表的，把旧版本标示为无效，新数据写入日志，成功后把新版本的数据写入新的位置。这种实现机制是导致数据膨胀严重的一个重要原因，因为旧版本的数据虽然表示为无效状态，但是没被回收前还是占据存储空间。

各种机制的比较

对于第一种、第二种实现方式，好处是更改数据的时候不会导致明显的膨胀，把数据膨胀限制在专门的存储空间内。

不好的地方也比较明显，因为改写数据的时候，会先把旧版本数据写入到UNDO或者临时表空间，然后再写日志，所以IOPS的消耗会大一倍，IOPS没有被有效地用于事务处理，这是其一；其二以Oracle为例，undu/redo设置的大小跟业务系统的负载特征相关性很强，设置不好容易导致问题，有经验的DBA一定遇到过snapshot too old这样的错误，就是undo空间不合适导致的一种问题；其三，在数据库崩溃的时候，重新启动数据库会有一个恢复的过程，简单地说Oracle这种实现方式，繁忙的数据库恢复的过程长，启动很慢，因为正确的数据版本需要通过undu,redo的数据去做比较和计算，对于生产系统可能不允许(当然也跟checkpoint本身的设置有关系)。

对于第三种实现方式，好处是IOPS可以充分地用于事务处理，提高业务系统的性能，系统恢复的时候，回滚操作只需要变更指针，所有的数据都在系统能，系统可以快速完成回滚和恢复，让数据库系统尽快投入生产工作。

这种实现方式也会有明显的副作用，就是容易产生数据膨胀，数据库需要经常做回收的工作；

再论实现机制的取舍

站在另一个角度，要弄清楚这个问题，还需要理解一下磁盘提供的基本功能。磁盘本身提供几个特性：

1. 对外提供存储容量，保存数据，比如600GB；
2. 磁盘每秒向应用提供一定中断响应次数，术语上叫IOPS (IO Per Sec)，这个跟磁盘转速有关系；现在的高速磁盘单盘都可以提供80～100 IOPS。
3. 传输数据的的带宽，比如ATA 33/66/100 SATA 3G/6G等。

磁盘驱动器在90年代前是很昂贵的外部设备，提供的存储空间和IOPS都非常有限；它作为应用系统永久存储数据的重要存储设备，在使用它的时候需要仔细规划，合理取舍，需要仔细权衡如何使用好这种资源。

Oracle为代表的数据库，明显是倾向将磁盘的空间有效利用起来，避免数据膨胀，虽然会牺牲一些性能，但是磁盘空间的利用效率高一些，90年代以前的应用系统大多数是单机系统，没那么并发用户，所以将磁盘空间合理利用起来，这种设计思想也是合理的。

PostgreSQL为代表的数据库，明显倾向于有效利用IOPS，将能利用的IOPS尽量用于支持应用系统，以提升应用系统的性能；同时牺牲磁盘空间的利用率作为代价，因为90年代以前，磁盘的IOPS真的很昂贵，PostgreSQL的设计可以充分的用好硬件的IOPS，有数据膨胀的负面影响也是值得的，因为膨胀的空间可以放在夜间来回收。

所以设计MVCC的实现机制，为啥要选择牺牲数据膨胀，或者牺牲IOPS，它们只是站在不同的角度，选择了不同的技术实现方式，做了合理的取舍的结果；不同的实现有好的方面，也有负面影响。

最佳实践

既然现在PostgreSQL有比较大的潜在的数据膨胀的问题，如何利用好PostgreSQL，避免这种机制引起的数据膨胀和相关的负面影响，让PostgreSQL更好地为业务系统服务，那么解决方法不外乎从两个方面入手：

一方面需要尽量减少数据膨胀，具体方法就是设置合适的fillfactor这个参数，让每个数据块预留一定的空间用于记录更新；当有预留更新的空间，新记录会在预留的空间内更新，因为旧数据跟更新后的数据在同一个数据块中，所以索引本身不必更新；可以降低IO发生的次数，并且提高性能；一般来说设置为建议为80，这个值可以根据应用的特点进行调整，比如：如果没有update，只有insert/delete这样的操作，可以用默认的100；如果update的几率比较大，比如每一条数据都需要改，那么50是一个好的开始。

另一方面让被占用的磁盘空间尽快回收，具体方法就是设置好vacuum相关的参数，让数据库内的垃圾数据及时和有效地得到回收。中心思想就是需要让回收的动作在系统设置允许的时间内完成垃圾数据的回收清理，这里面涉及到一些具体的参数，需要强调一下，系统默认的参数autovacuumvacuumscalefactor=0.2，以及autovacuumanalyzescalefactor=0.1，这两个系统默认设置对于大表明显太大，建议对于大表可以有针对性的设置。具体的方法，用户可以更加自己系统的硬件配置，负载特征以及表的实际情况，设置合适的值，主要是autovacuum*相关的设置；

好消息是PostgreSQL 12会引入类似于undo的机制，来改善目前这个问题，顺利的话会在19年第四季度发布，让我们拭目以待。