今天我们来聊聊 PostgreSQL 的性能优化；数据库优化是一个系统的工程，本文只专注于服务器的参数配置优化。
默认安装时，PostgreSQL 的配置参数通常都偏小，不太适合作为生产服务器使用。所以，安装 PostgreSQL 数据库之后首先需要执行的操作就是对服务器的配置参数进行调整。
查看/设置参数值
我们使用 PostgreSQL 12，服务器的配置参数有 300 多个，运行时的参数值可以使用 SHOW 命令查看：
show server_version;
server_version|
--------------|
12.3 |
show all;
name |setting |description |
-----------------------------------|-----------------------------------------|----------------------------------------------------------------------------------------------------------|
allow_system_table_mods |off |Allows modifications of the structure of system tables. |
application_name |DBeaver 7.0.5 - SQLEditor <Script-13.sql>|Sets the application name to be reported in statistics and logs. |
archive_cleanup_command | |Sets the shell command that will be executed at every restart point. |
...
这些参数的详细信息也可以使用 pg_settings 视图进行查看：
SELECT name, setting, unit, source, sourcefile, sourceline, short_desc
from pg_settings
where name like '%buffers%';
name |setting|unit|source |sourcefile |sourceline|short_desc |
--------------|-------|----|------------------|--------------------------------------|----------|------------------------------------------------------------------|
shared_buffers|16384 |8kB |configuration file|/var/lib/pgsql/12/data/postgresql.conf| 121|Sets the number of shared memory buffers used by the server. |
temp_buffers |1024 |8kB |default | | |Sets the maximum number of temporary buffers used by each session.|
wal_buffers |512 |8kB |override | | |Sets the number of disk-page buffers in shared memory for WAL. |
通过 pg_settings 视图不仅可以查看运行时的参数值，而且可以知道这些值的来源。
这些参数有些可以在服务器运行时进行修改，有些需要重启服务器之后才能生效；不同修改方式的优先级不同，下图列出了所有可能的修改方式：
以上设置方式的优先级从高到低；也就是说，在一个在事务内部设置的参数值会覆盖其他任何设置，不过该设置只在当前事务中有效。需要注意的是，并非所有参数都可以支持所有的修改方式，具体可以参考官方文档关于 pg_settings 的说明。
接下来我们介绍几个重要的配置参数。
max_connections
max_connections 决定了客户端的最大并发连接数，默认值通常为 100。如果出现连接数过多，无法连接数据库的错误时，可能需要考虑增加最大连接数。不过，修改该参数还需要考虑对其他参数的影响（尤其是 work_mem）；因为它们是基于每个连接设置的值，增加连接数也会导致这些内存使用量的增加。
通常来说，商业服务器至少可以支持几百个连接。如果应用的连接数到大上千或者几千，可以考虑使用连接池技术减少连接的消耗。
修改 max_connections 的方式有两种，修改之后必须重启服务器才能生效：
修改配置文件 postgresql.conf；
alter system set max_connections = N;，该命令会修改配置文件 postgresql.auto.conf。
对于主从复制中的从节点，必须将该参数的值设置为大于等于主节点上的值；否则，从节点将无法执行查询操作。
shared_buffers
除了操作系统的 I/O 缓存之外，PostgreSQL 还会使用自己的内部缓存。PostgreSQL 共享内存缓冲区由参数 shared_buffers 设置，它决定了 PostgreSQL 能够使用的专用缓存大小。
为确保在所有机器和操作系统上的兼容性，PostgreSQL 默认将该值设置得很小，通常是 128 MB。因此，增加 shared_buffers 的值是提高性能最有效的设置之一。
虽然对于 shared_buffers 没有具体的推荐值，但是可以针对具体的系统计算出一个大概的值。一般来说，对于专用的数据库服务器，shared_buffers 大概可以设置为系统内存的 25%。增加 shared_buffers 的值通常可以提高性能，例如，当整个数据库都可以被加载到缓存中时，可以明显减少磁盘的读取操作。由于 PostgreSQL 还依赖于操作系统的缓存，大于内存 40% 的 shared_buffers 并不会带来性能的提示，反而可能会下降。
虽然增加 shared_buffers 的值可以提高以读为主的系统性能，但是可能影响以写为主的系统性能；因为 shared_buffers 的全部内容必须在写入操作时进行处理。
修改 shared_buffers 的方式有两种，修改之后必须重启服务器才能生效：
修改配置文件 postgresql.conf；
alter system set shared_buffers = 'xxx';，该命令会修改配置文件 postgresql.auto.conf。
另外，增加 shared_buffers 的值通常也需要相应地增加 max_wal_size 的值，以便延长检查点的时间间隔。
wal_buffers
PostgreSQL 使用预写日志（WAL）确保数据的持久性；与 shared_buffers 作用类似，PostgreSQL 将 WAL 日志写入缓冲并且批量写入磁盘。
默认的 WAL 缓冲大小由 wal_buffers 参数进行设置，初始值为 16MB（shared_buffers 的 1/32）。WAL 缓冲区在每次事务提交时都会写入磁盘，因此过大的值并不会带来显着的性能提升。不过，对于大量并发的写入操作，适当增加该参数的值可以提高系统的性能。
修改 wal_buffers 的方式有两种，修改之后必须重启服务器才能生效：
修改配置文件 postgresql.conf；
alter system set wal_buffers = 'xxx';，该命令会修改配置文件 postgresql.auto.conf。
effective_cache_size
effective_cache_size 参数为 PostgreSQL 提供了一个可供操作系统和数据库使用的缓存估值（考虑了操作系统自身和其他应用之后）。
该参数只是一个评估值，而不是实际的分配值；它仅用于 PostgreSQL 查询计划器判断索引的代价，越大的值越可能使用索引扫描，否则更可能使用表的顺序扫描。
1569
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
