原文位于：https://www.percona.com/blog/2018/08/31/tuning-postgresql-database-parameters-to-optimize-performance/
开门见山的说，默认的PostgreSQL配置参数并不是针对某种负载优化过的。默认值主要是确保PostgreSQL可以在任何环境下都能运行起来，因此默认配置使用了最少的资源，并且没有任何隐患。根据数据库的负载来对数据库进行调优，是DBA或是开发的只要职责。在这篇博客里面，我们将会根据数据库的负载，建立几个基本的指导原则来配置数据库的参数，最终获得更好的性能。
首先，我们需要知道的是虽然优化PostgreSQL的配置可以获得更好的性能，但是数据库开发人员在写SQL的时候还是得费点心思。有时候，虽然可以使用索引，但是SQL却使用了全表扫描，或是执行了开销比较的join或是聚集操作，这种情况下，就算是数据库是调优过了，数据库的性能也不会好。这种情况下，在写SQL的时候就需要格外的小心。
但是，数据的参数调优，依然是非常重要的。所以，我们还是来研究下8个最可能影响数据库性能的参数。

shared_buffer

PostgreSQL一方面使用了自身的缓存区，一方面也使用了内核的缓存IO。这就意味着数据被缓存了两次，首先是缓存在数据库里面，然后是缓存到内核里面。和其他数据库不一样，PostgreSQL没有提供Direct IO。这叫做双缓冲。PostgreSQL的缓冲区叫shared_buffer,对于绝大多数操作系统来说，这个参数是最有效的一个可调参数。这个参数控制了多少指定的内存会被PostgreSQL用来做cache。
shared_buffer的默认值非常小，你很难从中获得什么好处。之所以设置这么低，是因为有些机器或是操作系统不支持太大的值。但是，在现在的操作系统中，你可以增大这个参数来获得最优的性能。
建议值是设置成硬件内存的25%。你可以把这个值设置得低于或是高于这个建议值，因为某些情况下，如果把这个值设置得大约25%的内存可以获得更好的性能。这个配置实际上取决于你的硬件配置和你的工作数据集的大小。如果你能轻易将你所有的数据加载到RAM里面，那么你可以增大shared_buffer来容纳你的数据库的数据，因此整个工作数据集可以驻留在cache中。
在生产环境中，可以看到，将shared_buffer设置成更大值，通常都是可以获得想到好的性能，尽管你可以通过benchmark来获得一个合理的值：

testdb=# SHOW shared_buffers;
shared_buffers
----------------
128MB
(1 row)

注意：某些版本的内核并不允许较大的值，特别是Windows中，将这个值设置得很大根本没有什么用。

wal_buffers

PosgreSQL会将WAL记录写入缓存区，然后刷到磁盘上。这个缓冲区的大小是wal_buffers参数定义的，默认16M，如果你有很多并发的连接时，可以将这个值设置的更大一些，以获得更好的性能。

effective_cache_size

effective_cache_size参数是用来估计可以做磁盘cache的内存的大小。它只是一个指导方针，而不是分配的内存大小，也不是cache的大小。这个参数并不会实际分配内存，而是告诉优化器内核中可用的cache的大小。如果这个值设置得太低，那么查询优化器会决定放弃使用索引，尽管这些索引会非常有用。因此，通常将这个参数调大，是有好处的。

work_mem

这是配置主要是用来做复杂的排序的(原文字面意思这个，明显不够准确，因为做hash表也会需要work_mem，例如做hash的连接或是聚集时候)。如果你的SQL要执行复杂的排序，那么你可以把这个值设置得更大一些来获得更好的结果。内存中的排序往往是比需要借助磁盘临时文件的排序要快很多的。将这个参数设置成一个比较大的值，会导致你的部署环境中内存成为瓶颈，因为每个排序操作都需要这么多的内存。因此，当你有很多用户需要执行排序操作，那么系统将会为所有的用户分配work_mem×total_sort_operations大小的内存。全局地将这个值设置得很高，会导致很高的内存使用率。因此，将来推荐在会话级别来设置这个参数：

testdb=# SET work_mem TO "2MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=509181.84..1706542.14 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=508181.79..514431.86 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)
(5 rows)

原来查询的sort操作的预计开销是514431.86。开销是一个没量纲的计算单位。上面这个SQL，我们的work_mem只有2M。处于测试的目的，我们，我们将它增大到了256M，来看看是否对开销有影响：

testdb=# SET work_mem TO "256MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=355367.34..1552727.64 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=354367.29..360617.36 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)

查询的开销从514431.86降低到了360617.36，降低了30%。

maintenance_work_mem

maintenance_work_mem是针对维护操作的一个内存配置项。默认是64M。设置更大的值，对VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY 和ALTER TABLE都很帮助。
设置成10M时：

postgres=# CHECKPOINT;
postgres=# SET maintenance_work_mem to '10MB';
postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 170091.371 ms (02:50.091)

设置成256M时：

postgres=# CHECKPOINT;
postgres=# set maintenance_work_mem to '256MB';
postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 111274.903 ms (01:51.275)

当maintenance_work_mem设置成10M时，索引的创建时间是 170091.371ms，当设置成256M时，索引创建时间减少到了111274.903 ms。

synchronous_commit

这个参数会确保在向客户端返回成功的状态之前，事务的提交一定要等待将事务的WAL写入了磁盘。这是在性能和可靠性之间的一个权衡。如果你的应用程序的性能比可靠性更加重要，那么可以将这个参数设置为off。这就意味着在返回的成功状态和将数据可靠地写入磁盘之间有个时间差。当数据库崩溃的时候，数据可能会丢失一部分，尽管客户端收到了成功提交事务的消息。在这种情况下，事务的提交是非常快的，因为不会等待WAL刷回文件，但是可靠性是打了折扣的。

checkpoint_timeout, checkpoint_completion_target

PostgreSQL会把数据的变化写入WAL。检查点进程会将缓存的数据刷回数据文件。当促发检查点操作时，数据库会进行前面说的（刷脏数据）行为。这是一个代价十分大的操作，因为它会引发大量的IO。这个过程涉及到了大量读写磁盘的操作。用户可以在任何有必要的时候手工执行检查点操作，或是数据库根据checkpoint_timeout和checkpoint_completion_target这两个参数来自动触发检查点。
checkpoint_timeout 参数用来设置检查点之间的时间间隔。将这个参数设置得太低，会减小数据库崩溃之后恢复需要的时间，因为大量的脏数据已经被写回了磁盘，但是这个同样会降低性能，因为检查点最终还是会消耗大量有价值的系统资源的。checkpoint_completion_target参数是检查点完成时间的一个比例。太频繁的检查点会影响性能。为了平滑检查点操作，checkpoint_timeout必须设置成一个比较低的值。否则，操作系统会将脏页累积到一个阈值之后，触发一个大的刷脏页操作。

总结

虽然还可以调整很多其他参数来获得更好的性能，但是，他们对性能的影响远远比不上上面提到的这8个参数。最后，我们需要知道的是，并不是所有的参数都与所有的应用程序相关。调整某个参数可能会提高某些应用程序的性能，但是对其他应用程序却毫无作用。PostgreSQL性能调优时，必须根据应用程序的需求以及所在OS环境，来调整参数。

PostgreSQL性能调优