本文仅讨论主机的全部资源分配给单个集群使用的场景下，Postgresql配置的调优。如果主机上部署多个集群，需要根据实际情况适当调整配置参数。
一、通用配置
| 参数 | 说明 | 建议值 |
| shared_buffers | 共享缓冲区大小。Postgresql对数据操作时都要先将数据从磁盘读取到内存中，然后进行更新，最后再将数据写回磁盘。shared_buffers的功能就是用于存放从磁盘读取的数据。 | 系统物理内存的25%~30% |
| effective_cache_size | 优化器假设一个查询可以使用的最大内存（包括Postgresql使用的和操作系统缓存），和shared_buffer等内存无关，只是给优化器生成计划使用的一个假设值。 | 系统内存的75% |
| maintenance_work_mem | 维护工作内存，主要是针对数据库的维护操作或者语句。尽量的将这些操作在内存中进行。主要针对VACUUM，CREATE INDEX，REINDEX等操作。在对整个数据库进行VACUUM或者较大的index进行重建时，适当的调整该参数非常必要 | 系统内存的1/16，最大2G即可 |
| work_mem | 声明内部排序和哈希操作可使用的工作内存大小。对于复杂查询，提高这个值可能会带来明显的性能提升。这个内存大小是给每个连接单独分配的 |
OLTP：2MB OLAP：可以适度调大 |
| max_connections | 最大连接数。work_mem * max_connections 可以估算最大连接数时所需的内存量。因而针对特定场景，可以适度调小max_connections，调大work_mem或相反 | |
| max_worker_processes | 最大后台进程数 | CPU线程数 |
| max_parallel_workers | 并行查询的最大进程数 | CPU线程数 |
| max_parallel_workers_per_gather | 并发查询时，单个gather节点可以使用的最大进程数 |
OLTP：CPU线程数/2，最大4即可 OLAP：CPU线程数/2 |
| max_parallel_maintenance_workers | 维护命令(vacuum或索引相关的命令)最大的后台进程数 | CPU线程数/2，最大4即可 |
二、IO配置
磁盘IO相关的参数，主要有两个参数可以调优
1）. random_page_cost： 执行计划成本估算的参数，用于随机访问磁盘页的代价，默认值是4。顺序磁盘页访问的代价seq_page_cost=1.0。这个默认配置是针对机械硬盘的。针对更快速的存储(如SSD)，可以适当调小这个值，使得执行计划更倾向于使用索引。
2）. effective_io_concurrency：同时执行IO操作的数量。 默认值是1。 针对更快速的存储(如SSD)，可以适当调大这个值以获取更好的IO性能。
2.1 机械硬盘
| 参数 | 建议值 |
| random_page_cost | 4 |
| effective_io_concurrency | 2 |
2.2 固态硬盘
| 参数 | 建议值 | 调优建议 |
| random_page_cost | 1.0~2.0 | 可以全局，也可以只针对表空间优化 |
| effective_io_concurrency | 200 | 可以全局，也可以只针对表空间优化 |
| bgwriter_delay | 10ms | 数据都存储在SSD盘的情况 |
| bgwriter_lru_maxpages | 1000 | 数据都存储在SSD盘的情况 |
| bgwriter_lru_multiplier | 10.0 | 数据都存储在SSD盘的情况 |
| bgwriter_flush_after | 0 | 数据都存储在SSD盘的情况 |
| backend_flush_after | 0 | 数据都存储在SSD盘的情况 |
| checkpoint_flush_after | 0 | 数据都存储在SSD盘的情况 |
| wal_writer_delay | 1ms | pg_wal目录(即数据目录)在SSD盘的情况 |
| wal_writer_flush_after | 0 | pg_wal目录(即数据目录)在SSD盘的情况 |
三、WAL日志配置
WAL(Write Ahead Log)日志保证数据库异常场景不可用时，重启后也可从WAL恢复数据，保证数据不丢失。
| 参数 | 说明 | 建议值 |
| max_wal_size | WAL日志的最大容量，根据存储的大小可以适度调整 | 200GB |
| checkpoint_timeout | 系统自动执行checkpoint之间的最大时间间隔 | 1h |
| wal_keep_segments | 至少保存的WAL日志数量，用于备库同步 | |
| checkpoint_completion_target | checkpoint刷盘的时间，越大说明刷盘的时间更长，期间对IO的性能影响越低 | 0.9 |
Postgresql为了保证性能，会同步写入WAL和异步写入数据文件。但为了数据的可靠性，会执行checkpoint命令，把数据文件刷盘。checkpoint命令会占用不少IO资源，可能导致期间数据库性能下降。因而通过配置减少checkpoint的次数来提升性能。
checkpoint触发的机制：1）接收到 checkpoint命令 2）数据库正常停止的时候 3）WAL日志在上次checkpoint后，写入量达到了max_wal_size 4）离上次checkpoint达到了checkpoint_timeout时间
# 四、场景优化
默认配置一般针对OLTP的场景进行。针对特定的场景也有一些优化的策略。
4.1 OLAP场景
| 参数 | 建议值 |
| work_mem | 1GB，或适当更大的值 |
| max_connections | 200, 减少连接数，使得每个连接分配到更多的work_mem |
| default_statistics_target | 500 ，用于更精细分析值的分布，可以获取更好的执行计划 |
| max_parallel_workers_per_gather | CPU线程数/2 |
4.2 数据割接场景
针对大量数据全量导入场景（割接)，可以采用将同步策略设为异步，再把备库都停了，只用单节点导数的方式进行。导数完毕后，再重做备库。
针对这种场景，可以调低数据安全相关参数，以提升写入数据的性能。
下面参数是针对单节点导数的优化配置，修改后需重启：
| 参数 | 建议值 |
| wal_level | minimal |
| archive_mode | off |
| max_wal_senders | 0 |
| fsync | off |
| synchronous_commit | off |
| full_page_writes | off |
| wal_log_hints | off |
| checkpoint_timeout | 1d |
| max_wal_size | 根据磁盘空间，可以设置更大的值 |
以上参数仅用于大量数据导入场景。割接完毕后，请务必把参数值改回原来的，并且重启Postgresql(wal_level,archive_mode等参数需要重启才能生效)。最后重做备库。
