垂直扩展PostgreSQL有关配置

PostgreSQL可以相当好地垂直扩展。您可以为PostgreSQL服务器提供的资源（CPU，内存，磁盘）越多，它就能越好地执行。但是，虽然Postgres的某些部分可以自动使用增加的资源，但其他部分需要进行配置更改才能注意到改进。

继续阅读以了解有关如何确保PostgreSQL充分利用您正在运行的系统的更多信息。

中央处理器

自动缩放的内容：PostgreSQL有一个传统的流程​​架构，由一个主进程（称为postmaster）组成，为每个新的客户端连接产生一个新进程（称为 后端）。这意味着如果有更多CPU核心可用，则可以同时运行更多进程，因此后端不必为CPU可用性争用多少。CPU绑定查询将更快完成。

您可能希望在系统范围，每个数据库或每个用户级别调整允许的最大同时连接数：

-- system level
ALTER SYSTEM SET max_connections = 200;

-- database level
ALTER DATABASE dbname CONNECTION LIMIT 200;

-- user level
ALTER ROLE username CONNECTION LIMIT 20;

所以流氓应用程序不能最终占用过多的连接。

什么需要调整

PostgreSQL服务器可以生成进程以处理内务处理任务，如清理，复制，订阅（用于逻辑复制）等。此类工作程序的数量不是动态确定的，而是通过配置设置，默认为8。

工作进程数的顶级配置设置为：

# typically specified in postgresql.conf
max_worker_processes = 16

并行查询:从版本9.6开始，如果查询计划程序决定它将提供帮助，Postgres可以并行执行查询。并行查询涉及产生工作人员，在他们之间分配工作，然后收集（收集）结果。根据之前设置的总体限制max_worker_processes，Postgres将根据两个配置设置的值确定可以为并行查询生成多少个工作者：

# the maximum number of workers that the system can
# support <b>for</b> parallel operations
max_parallel_workers = 8

# the maximum number of workers that can be started
# by a single Gather or Gather Merge node
max_parallel_workers_per_gather = 8

如果您有空闲的CPU和可并行化的查询，增加这些值可以加速此类查询。

并行索引创建: 在Postgres 11，支持并行创建B树索引的。如果您经常创建B树索引或REINDEX它们，增加此值可以帮助：

# the maximum number of parallel workers that can be
# started by a single utility command
max_parallel_maintenance_workers = 8

这将允许Postgres产生这些许多工作者（受限于总体限制max_worker_processes）以加速B-Tree索引的创建。

逻辑复制：逻辑复制（在Postgres 10及更高版本中可用）依赖于订阅方的工作进程来从发布者获取更改。通过要求Postgres生成更多逻辑复制工作程序，可以并行获取和应用更改，尤其是在有更多表的情况下。此配置设置会增加复制工作者的总数：

# maximum number of logical replication workers
max_logical_replication_workers = 8

在流复制中，您可以开始与基本备份同步。但是，对于逻辑复制，必须通过复制协议本身通过网络引入更改。这可能很耗时。在同步阶段允许更多工作人员可以加快此过程：

# basically the number of tables that are synced in
# parallel during initialization of subscription
max_sync_workers_per_subscription = 8

自动清理：Postgres会定期根据一系列配置设置，推出一堆VACUUM数据库表的工作人员。这当然称为autovacuum，每次自动启动autovacuum产生的工作人员数量可以通过配置设置来设置：

# the maximum number of autovacuum processes
autovacuum_max_workers = 8

WAL压缩：如果你有备用的CPU，你可以通过压缩写入WAL文件的页面来交换CPU的磁盘带宽。这减少了需要写入磁盘的数据量，代价是压缩数据的CPU周期更多。它还减少了需要通过线路发送以进行流复制的数据大小。

实际上，WAL压缩的好处非常值得合理的开销。要打开它，请使用：

# compresses full page images written to WAL
wal_compression = on

内存

自动缩放的内容：操作系统自动管理和使用任何应用程序未使用的内存，用于缓存最近读取和写入磁盘的数据。这极大地加速了磁盘密集型应用程序，当然还有PostgreSQL。

在Postgres最受欢迎的主机 Linux 中，用户无法设置操作系统磁盘缓存的大小。它的管理是Linux内部的。在内存压力下，它将为应用程序提供磁盘高速缓存。

什么需要调整？

查询计划程序：查询计划程序必须将操作系统提供的磁盘缓存量作为其估计值的一个因素。如果您已设法显着增加操作系统磁盘缓存（通过增加可用内存），则增加此配置设置可能有助于提高计划程序的估计值：

# the planner's assumption about the effective size
# of the disk cache that is available to a single query.
effective_cache_size = 64GB

共享内存：PostgreSQL使用一组在所有worker和后端进程之间共享的缓冲区。这些称为共享缓冲区，并使用配置设置设置为共享缓冲区分配的内存量：

shared_buffers = 32GB

临时缓冲区

当查询访问临时表时，将分配缓冲区以缓存读入的内容。使用配置设置来设置此缓冲区的大小：

# the maximum number of temporary buffers used
# by each database session
temp_buffers = 100MB

如果您有足够的内存和大量使用临时表的查询，那么增加此值可以加速此类查询。

工作内存：工作内存由后端本地和私有分配。它用于完成 排序 和连接，而无需创建临时表。从默认值4MB增加此值可以在临时表创建期间更快地完成查询：

# the amount of memory to be used by internal sort
# operations and hash tables before writing to temporary disk files
work_mem = 16MB

维护操作：VACUUM使用的内存，索引创建和其他此类维护命令由配置设置控制maintenance_work_mem。增加此数量可以加快这些操作，尤其是在需要重新创建的索引或表上。

autovacuum工作人员使用的内存可以从维护工作内存中获取（通过设置autovacuum_work_mem = -1）或单独配置。

# the maximum amount of memory to be used by
# maintenance operations
maintenance_work_mem = 128MB

# maximum amount of memory to be used by each
# autovacuum worker process
autovacuum_work_mem = -1

磁盘

自动缩放的内容：可以使磁盘更大，更快或更并发。磁盘的大小是PostgreSQL唯一没有被指示的东西。默认情况下，PostgreSQL不会限制自己使用任何可用的磁盘空间。这通常很好。

您可以对创建的临时文件的总大小设置限制，以针对尝试排序十亿行等的查询提供一定程度的保护：

# the maximum amount of disk space that a process
# can use <b>for</b> temporary files
temp_file_limit = 500GB

什么需要调整？

并发：可以设置RAID-ed磁盘和ZFS等文件系统以支持更多的并发性。也就是说，由于内部存储或处理数据的方式，这些文件系统可以同时为一些磁盘读/写提供服务。

您可以使用此配置设置让Postgres发出多个并发磁盘I / O：

# the number of concurrent disk I/O operations that
# PostgreSQL expects can be executed simultaneously
effective_io_concurrency = 4

这当前仅由位图堆扫描使用。

随机页面成本：Postgres查询计划程序假定顺序读取比随机读取更快。你可以调整的值到底有多快。默认情况下，它假定随机读取的成本是4倍。

根据您的磁盘设置，工作负载和基准测试，如果您确定随机读取，只是顺序读取的两倍，您可以告诉Postgres：

# the planner's estimate of the cost of a disk page
# fetch that is part of a series of sequential fetches
seq_page_cost = 1

# the planner's estimate of the cost of a
# non-sequentially-fetched disk page
random_page_cost = 2

表空间：要利用未安装为单个大文件系统的多个磁盘，可以使用 表空间 。使用表空间，您可以放置​​表或索引不同的文件系统。这可以提高并发性并提供一种处理表增长的简便方法。

REATE TABLESPACE disk2 LOCATION '/mnt/disk2/postgres';

在这里 阅读有关 表空间的 更多信息。

网络

网络通常是PostgreSQL服务器上使用最少的资源，并且很少饱和。如果你确实需要扩展，很容易添加更多的网络接口，每个接口都有自己的IP，并让PostreSQL全部监听：

listen_addresses = '10.1.0.10,10.1.0.11'

必须在Postgres监听的所有IP上对客户端进行负载平衡。

其他

还有一些其他配置设置可以调整，其中大部分都耗尽了更多的CPU 和内存。

分区操作：

Postgres 10引入了表分区， 在Postgres 11中进行了 改进。 默认情况下，分区上的某些查询优化没有打开，因为它们可能导致更高的CPU和内存消耗。这些是：

# allow a join between partitioned tables to be
# performed by joining the matching partitions
enable_partitionwise_join = on

# allow grouping or aggregation on a partitioned
# tables per<b>for</b>med separately <b>for</b> each partition
enable_partitionwise_aggregate = on