PostgreSql 之 postgresql.conf 参数设置（二）
参考了阿里云最佳实践，和 postgresql 官方手册，研究学习设置 postgresql.conf 参数。（下列只列出需要修改的参数，默认值即为合适值的没有列出。）
IO相关设置
bgwriter_delay = 10ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 10.0 effective_io_concurrency = 0 max_worker_processes = 256 max_parallel_workers = 28 max_parallel_maintenance_workers = 6 max_parallel_workers_per_gather = 0 synchronous_commit = remote_write wal_compression = on wal_writer_delay = 10ms commit_delay = 10 checkpoint_timeout = 30min max_wal_size = 32GB min_wal_size = 16GB archive_mode = on max_replication_slots = 64 复制代码
bgwriter_delay：后台写入器活动轮次之间的延迟。在每个轮次中，写入器都会为一定数量的脏缓冲区发出写操作，之后它就休眠 bgwriter_delay 的时长， 然后重复动作。当缓冲池中没有脏缓冲区时，不管 bgwriter_delay，它都会进入更长的休眠，默认值是 200ms。bgwriter_lru_maxpages：在每个轮次中，不超过这么多个缓冲区将被后台写入器写出，把这个参数设置为零可禁用后台写出，默认值是 100 个缓冲区。 bgwriter_lru_multiplier：最近所需缓冲区的平均值乘以 bgwriter_lru_multiplier可以估算下一轮次中将会需要的缓冲区数目。脏缓冲区将被写出直到有很多干净可重用的缓冲区（然而，每一轮次中写出的缓冲区数不超过 bgwriter_lru_maxpages）。 因此，设置为 1.0 表示一种“刚刚好的”策略，这种策略会写出正好符合预测值的数目的缓冲区，更大大的值可以为需求高峰提供某种缓冲，默认值是 2.0。
effective_io_concurrency：设置了该值，磁盘的预读会失效，顺序扫描和索引扫描都可以利用磁盘预读，建议关闭，默认值为 1。
max_worker_processes：系统能够支持的后台进程的最大数量，默认值为 8。在更改这个值时，考虑也对 max_parallel_workers、max_parallel_maintenance_workers、max_parallel_workers_per_gather 进行调整。
max_parallel_workers：设置系统支持的最大并行数量，默认值为8，要注意将这个值设置得大于 max_worker_processes 将不会产生效果，因为并行工作者进程都是从 max_worker_processes 所建立的工作者进程池中取出来的。
max_parallel_maintenance_workers：单一工具性命令能够启动的最大并行数。当前支持使用并行工作者的工具性命令是 CREATE INDEX，并且只有在构建B-树索引时才能并行，并且 VACUUM 没有 FULL选项。并行工作者从由 max_worker_processes 创建的进程池中取出，数量由max_parallel_workers控制，默认值为2。
max_parallel_workers_per_gather：设置允许的最大并行查询数，并行查询可能消耗比非并行查询更多的资源，把这个值设置为0将会禁用并行查询执行，默认值是2。
wal_compression：当这个参数为on时，如果 full_page_writes 为打开（默认值打开）或者处于基础备份期间，PostgreSQL服务器 会压缩写入到 WAL 中的完整页面镜像。压缩页面镜像将在 WAL 重放时 被解压。默认值为off。只有超级用户可以更改这个设置，打开这个参数可以减小 WAL 所占的空间且无需承受不可恢复的数据损坏风险， 但是代价是需要额外的 CPU 开销以便在 WAL 记录期间进行压缩以及在 WAL 重放时解压。
wal_writer_delay：指定 WAL 写入器刷写 WAL 的频繁程度，以时间为单位。 在刷写WAL之后，写入器将根据 wal_writer_delay所给出的时间长度进行睡眠，除非被一个异步提交的事务提前唤醒。 如果最近的刷写发生在 wal_writer_delay 之前，并且小于 wal_writer_flush_after WAL的值产生之后，那么WAL只会被写入操作系统，而不会被刷写到磁盘，默认值是 200ms。
commit_delay：在一次 WAL 刷写被发起之前，commit_delay 增加一个时间延迟。 如果系统负载足够高，使得在一个给定间隔内有额外的事务准备好提交，那么通过允许更多事务通过一个单次 WAL 刷写来提交能够提高组提交的吞吐量。 但是，它也把每次 WAL 刷写的潜伏期增加到了最多 commit_delay。 因为如果没有其他事务准备好提交，就会浪费一次延迟，只有在当一次刷写将要被发起时有至少 commit_siblings(默认是5个)个其他活动事务时，才会执行一次延迟。 另外，如果fsync被禁用，则将不会执行任何延迟。
checkpoint_timeout：自动 WAL 检查点之间的最长时间，合理的范围在 30 秒到 1 天之间，默认是 5min。增加这个参数的值会增加崩溃恢复所需的时间。
max_wal_size：在自动 WAL 检查点之间允许 WAL 增长到的最大尺寸。这是一个软限制，在特殊的情况下 WAL 尺寸可能会超过max_wal_size， 例如在重度负荷下、archive_command失败或者高的 wal_keep_size设置，默认为 1 GB。增加这个参数可能导致崩溃恢复所需的时间。
min_wal_size：只要 WAL 磁盘用量保持在这个设置之下，在检查点时旧的 WAL 文件总是 被回收以便未来使用，而不是直接被删除。这可以被用来确保有足够的 WAL 空间被保留来应付 WAL 使用的高峰，例如运行大型的批处理任务。 如果指定值时没有单位，则以兆字节为单位，默认是 80 MB。
archive_mode：当启用archive_mode时，可以通过设置 archive_command命令将完成的 WAL 段发送到归档存储。除用于禁用的off之外，还有两种模式：on 和 always。在普通操作期间，这两种模式之间没有区别，但是当设置为 always 时，WAL 归档器在归档恢复或者后备模式下也会被启用。在 always 模式下，所有从归档恢复 的或者用流复制传来的文件将被（再次）归档。当wal_level被设置为minimal时，archive_mode不能被启用。
max_replication_slots：服务器可以支持的复制槽最大数量，默认值为10。将它设置为一个比当前已有复制槽要少的值会阻碍服务器启动。此外，要允许使用复制槽，wal_level必须被设置为 replica或更高。
synchronous_commit：有 on、off、local、remote_write、remote_apply 5个选项。
单实例环境：
on：当数据库提交事务时，wal先写入 wal buffer 再写入 wal 日志文件，设置成on表示提交事务时需等待本地wal写入wal日志后才向客户端返回成功。on 为默认设置，数据库非常安全，但性能有所损耗。
local：local含义和on类似，表示提交事务时需要等待本地wal写入后才向客户端返回成功。
off：当数据库提交事务时不需要等待本地 wal buffer 写入 wal 日志，随即向客户端返回成功，设置成 off会给数据库带来一点风险：数据库宕机时最新提交的少量事务可能丢失，数据库重启后会认为这些事务异常终止，会rollback。适用对数据库准确性要求不高同时追求数据库性能的的场景。
流复制环境：
on：表示流复制主库提交事务时，需等待备库接收主库发送的wal日志流并写入wal文件，之后才向客户端返回成功，简单的说on表示本地wal已落盘，备库的wal也已落盘，有两份持久化的wal，但备库此时还没有完成重做。这个选项带来的事务响应时间较高。
remote_write：表示流复制主库提交事务时，需等待备库接收主库发送的wal日志流并写入备节点操作系统缓存中，之后向客户端返回成功，这种情况下备库出现异常关闭时不会有已传送的wal日志丢失风险，但备库操作系统异常宕机就有已传送的wal丢失风险了，此时wal可能还没有完全写入备节点wal文件中，简单的说 remote_write 表示本地wal已落盘，备库的wal还在备库操作系统缓存中，也就是说只有一份持久化的wal。这个选项带来的事务响应时间较低。
remote_apply：表示流复制主库提交事务时，需等待备库接收主库发送的wal流并写入wal文件，同时备库已经完成重做，之后才向客户端返回成功，简单的说remote_apply 表示本地wal已落盘，备库wal已落盘并且已经完成重做，这个设置保证了拥有两份持久化的wal，同时备库也已经完成了重做。这个选项带来的事务响应时间最高。
作者：脑子进水养啥鱼
链接：https://juejin.cn/post/7023696870270582820