pg wal最佳实践

背景：

PostgreSQL在写入频繁的场景中，可能会产生大量的WAL日志，而且WAL日志量远远超过实际更新的数据量。我们可以把这种现象起个名字,叫做“WAL写放大”，造成WAL写放大的主要原因有2点。

1. 在checkpoint之后第一次修改页面，需要在WAL中输出整个page，即全页写(full page writes)。全页写的目的是防止在意外宕机时出现的数据块部分写导致数据库无法恢复。
2. 更新记录时如果新记录位置(ctid)发生变更，索引记录也要相应变更，这个变更也要记入WAL。更严重的是索引记录的变更又有可能导致索引页的全页写，进一步加剧了WAL写放大。

过量的WAL输出会对系统资源造成很大的消耗，因此需要进行适当的优化。

1. 磁盘IO WAL写入是顺序写,通常情况下硬盘对付WAL的顺序写入是绰绰有余的。所以一般可以忽略。
2. 网络IO 对局域网内的复制估计还不算问题，远程复制就难说了。
3. 磁盘空间 如果做WAL归档，需要的磁盘空间也是巨大的。

优化手段：

PostgreSQL在未经优化的情况下，20倍甚至更高的WAL写放大是很常见的，适当的优化之后应该可以减少到3倍以下。引入SSL/SSH压缩或归档压缩等外部手段还可以进一步减少WAL的生成量。

如何判断是否需要优化WAL？

关于如何判断是否需要优化WAL，可以通过分析WAL，然后检查下面的条件，做一个粗略的判断：

• FPI比例高于70%
• HOT_UPDATE比例低于70%

以上仅仅是粗略的经验值，仅供参考。并且这个FPI比例可能不适用于低写负载的系统，低写负载的系统FPI比例一定非常高，但是，低写负载系统由于写操作少，因此FPI比例即使高一点也没太大影响。

优化WAL的副作用

前面用到了3种优化手段，如果设置不当，也会产生副作用，具体如下：

1. 延长checkpoint时间间隔 导致crash恢复时间变长。crash恢复时需要回放的WAL日志量一般小于max_wal_size的一半，WAL回放速度(wal_compression=on时)一般是50MB/s~150MB/s之间。可以根据可容忍的最大crash恢复时间（有备机时，切备机可能比等待crash恢复更快），估算出允许的max_wal_size的最大值。
2. 调整fillfactor 过小的设置会浪费存储空间，这个不难理解。另外，对于频繁更新的表，即使把fillfactor设成100%，每个page里还是要有一部分空间被dead tuple占据，不会比设置一个合适的稍小的fillfactor更节省空间。
3. 设置wal_compression=on 需要额外占用CPU资源进行压缩，但根据实测的结果影响不大。