PostgreSQL性能调优-优化你的数据库服务器

一份从硬件到PostgreSQL查询优化的数据库能力提升指南

本文档介绍了如何调优PostgreSQL和EDB Postgres Advanced Server（EPAS）10至13版本。所使用的系统是红帽企业版Linux（RHEL）系列发行版8版本。这些只是通用指南，实际调优细节会因工作负载而异，但它们应该能为大多数部署提供一个良好的起点。

在调优时，我们从硬件开始，逐步向上处理整个技术栈，最后优化应用程序的SQL查询。随着我们在技术栈中不断上移，调优工作中与工作负载相关的部分会越来越多，因此我们先从最通用的方面入手，再过渡到最针对特定工作负载的方面。

优化机器

本文档侧重于裸机和虚拟化机器。未来版本可能会包含云设计和容器化设计。

裸机

设计用于PostgreSQL的裸机服务器时，需要考虑几个方面。在某些情况下，这些方面包括CPU、内存、磁盘和网卡。

CPU

合适的CPU可能对PostgreSQL的性能至关重要。在处理更大规模数据时，CPU速度会很重要，而拥有更大L3缓存的CPU将提升性能。对于OLTP性能而言，更多、更快的内核有助于操作系统和PostgreSQL更高效地运行。同时，使用具有更大L3缓存的CPU适用于更大的数据集。

什么是三级缓存（L3 cache）？ 中央处理器（CPU）至少有两种缓存：一级缓存（L1，又称主缓存）和二级缓存（L2，又称次缓存）。一级缓存是嵌入在CPU核心中最小且速度最快的缓存。二级缓存比一级缓存稍慢，但容量更大，用于为一级缓存提供数据。

与每个核心独有的L1和L2缓存不同，L3缓存由所有可用核心共享。L3的速度慢于L1和L2，但仍快于内存。更大的L3缓存能在处理更大数据集时提升CPU性能，这对PostgreSQL的并行查询也有好处。

RAM

在硬件中，内存（RAM）是成本最低的，同时对提升PostgreSQL性能效果最佳。操作系统会充分利用可用内存，尽可能多地缓存数据。缓存越多，磁盘I/O操作就越少，查询速度也会越快。购买新硬件时，应尽可能配置更多内存，因为日后再添加内存，无论从经济成本还是技术层面来说都会更昂贵。除非系统配备了热插拔内存，否则添加内存时需要停机。以下提到的多个PostgreSQL参数会根据可用内存进行调整。

Disk

如果应用程序受输入/输出限制（读写密集型），更快的驱动器组将显著提升性能。有多种解决方案可供选择，包括NMVE和SSD驱动器。

首要原则是将WAL磁盘与数据磁盘分开。WAL可能会成为写入密集型数据库的瓶颈，因此将WAL存放在独立且快速的Disk上可以解决这个问题。至少使用RAID 1，不过在某些情况下，如果数据库写入量很大，可能需要使用RAID 10。

为索引和数据使用单独的表空间和Disk也会提高性能，尤其是当PostgreSQL运行在SATA驱动器上时。这对于SSD和NVME驱动器通常是不必要的。我们建议为数据使用RAID 10。

有关优化驱动器的更多信息，请参阅优化文件系统[1]部分。此外，本博客还讨论了可与PostgreSQL配合使用的存储和RAID选项。

Network

尽管网卡似乎与PostgreSQL性能无关，但当数据量大幅增长时，更快的网卡或绑定网卡也会加快基础备份的速度。

虚拟机

与物理服务器相比，虚拟机由于虚拟化层的存在，性能会略有不足。此外，由于资源共享，可用的CPU和磁盘输入/输出性能也会下降。

在虚拟机中提升PostgreSQL性能有几个技巧：

1、将虚拟机固定到特定的CPU和磁盘上。这样可以消除或限制因主机上运行其他虚拟机而造成的性能瓶颈。

2、在安装前预分配磁盘。这将防止主机在数据库操作期间分配磁盘空间。如果无法做到这一点，您可以在postgresql.conf中修改这两个参数：

3、在postgresql.conf中禁用wal_recycle参数。默认情况下，PostgreSQL通过重命名来回收WAL文件。但是，在写时复制（COW）文件系统上，创建新的WAL文件可能更快，因此禁用此参数将对虚拟机有所帮助。

4、在postgresql.conf中禁用 wal_init_zero 参数。默认情况下，WAL空间会在插入WAL记录之前分配。这会降低COW文件系统上的WAL操作速度。禁用此参数将关闭该功能，有助于提升虚拟机的性能。如果设置为off，文件创建时只会写入最后一个字节，以使其达到预期大小。

系统调优

调整PostgreSQL的操作系统可以为你提供提升性能的额外机会。正如引言中所指出的，本指南重点介绍如何为红帽企业Linux（RHEL）系列调整PostgreSQL。

守护进程

RHEL 上的大多数调优都是通过 tuned 守护进程完成的。该守护进程会调整操作系统，使其在工作负载下表现更佳。

请注意，下面显示的命令适用于RHEL 8。如果您使用的是RHEL 7，请在示例中出现dnf的地方使用yum命令。

tuned守护进程默认已安装。如果未安装（可能是由于kickstart文件的配置问题），请使用以下命令安装：

dnf -y install tuned

并通过以下命令启用它：

systemctl enable --now tuned

tuned 帮助系统管理员快速且动态地更改内核设置，他们不再需要在 /etc/sysctl 中进行修改——这可以通过 tuned 来完成。

tuned 附带了一些预定义的配置文件。你可以使用 tuned-adm list 命令获取列表。RHEL 安装程序会根据环境选择一个合适的默认值。物理机的默认值是 throughput-performance，其目的是提高吞吐量。你可以运行以下命令，查看 tuned 守护进程在评估系统后会给出什么建议：

tuned-adm recommend

使用以下命令查看预配置的值：

tuned-adm active
Current active profile: virtual-guest

然而，默认设置可能会降低PostgreSQL的速度；这些设置可能更倾向于节能，这会使CPU变慢。类似的论点也适用于网络和I/O调优。为了解决这个问题，我们将创建自己的PostgreSQL性能配置文件。

创建新配置文件相对简单。我们将这个配置文件命名为edbpostgres。以root身份运行以下命令：

#  This directory name will also be the
# name of the profile:
mkdir /etc/tuned/edbpostgres
# Create the profile file:
echo "
[main]
summary=Tuned profile for EDB PostgreSQL Instances
[bootloader]
cmdline=transparent_hugepage=never
[cpu]
governor=performance
energy_perf_bias=performance
min_perf_pct=100
[sysctl]
vm.swappiness = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
vm.dirty_ratio = 10
vm.dirty_background_ratio = 3
vm.overcommit_memory=0
net.ipv4.tcp_timestamps=0

[vm]
transparent_hugepages=never
" > /etc/tuned/edbpostgres/tuned.conf

包含 [ 和 ] 的行被称为经过调整的插件，用于与系统的指定部分进行交互。

让我们来检查这些参数和值：

• • [main] 插件包含摘要信息，还可以通过 include 语句包含来自其他优化配置文件的值。
• • [cpu]插件包含围绕CPU调节器和CPU电源设置的相关配置。
• • [sysctl]插件包含与procfs交互的值。
• • [vm]和[bootloader]插件可启用/禁用透明大页（bootloader插件将帮助我们与GRUB命令行参数进行交互）。

通过这些变更，我们旨在实现以下目标：

• • CPU不会进入省电模式（PostgreSQL不会遭受随机的性能下降）。
• • Linux 发生交换的可能性将大大降低。
• • 内核将帮助Postgres刷新脏页，减轻后台写入器（bgwriter）和检查点进程（checkpointer）的负载。
• • pdflush守护进程将更频繁地运行。
• • 关闭TCP时间戳是一种良好的做法，这样可以避免或至少减少由时间戳生成导致的峰值。
• • 禁用透明大页对PostgreSQL的性能大有裨益。

要启用这些更改，请运行以下命令：

tuned-adm profile edbpostgres

此命令可能需要一些时间才能完成。

要完全禁用透明大页，请运行以下命令：

grub2-mkconfig -o /boot/grub2/grub.cfg

并重启您的系统：

systemctl start reboot.target

优化文件系统

另一个优化点是磁盘。PostgreSQL 不依赖访问时间（文件最后被访问的时间戳）来处理数据文件，因此禁用访问时间可以节省 CPU 周期。

打开/etc/fstab，在保存PostgreSQL数据和WAL文件的驱动器的defaults值附近添加noatime。

/dev/mapper/pgdata-01-data /pgdata xfs defaults,noatime 1 1

要立即激活它，请运行：

mount -o remount,noatime,nodiratime /pgdata

这些建议作为开端是不错的，你需要同时监控操作系统和PostgreSQL，以收集更多数据来进行更精细的调整。

$ man mount|grep "noatime"
noatime:
the current modify or change time. (Similar to noatime, but doesn't break mutt or other applications that need to know if a file has
Since Linux 2.6.30, the kernel defaults to the behavior provided by this option (unless noatime was specified), and the strictatime
Allows to explicitly requesting full atime updates. This makes it possible for kernel to defaults to relatime or noatime but still allow

Huge Pages

在Linux系统中，默认的页面大小为4KB。一个典型的PostgreSQL实例可能会分配数GB的内存，这会因页面大小过小而导致潜在的性能问题。此外，由于这些页面会产生碎片，为大型数据集映射这些页面需要额外的时间。

在Linux上启用大页会提升PostgreSQL的性能，因为它会一次性分配大块（大页）内存。

默认情况下，Linux 上未启用大页，这也适用于 PostgreSQL 的默认大页设置"try"，即"如果操作系统上有大页则使用，否则不使用"。

为PostgreSQL设置大页有两个方面：配置操作系统和配置PostgreSQL。

首先，确定你的系统需要为PostgreSQL分配多少个大页。当PostgreSQL实例启动时，postmaster会在$PGDATA目录中创建一个postmaster.pid文件。你可以在该文件中找到主进程的进程ID（pid）：

$ head -n 1 $PGDATA/postmaster.pid
1991

现在，找到该实例的VmPeak：

$ grep -i vmpeak /proc/1991/status
VmPeak: 8823028 kB

提示: 如果在同一台服务器上运行多个PostgreSQL实例，请在下一步计算所有VmPeak值的总和。

让我们确认大页的大小：

$ grep -i hugepagesize /proc/meminfo
Hugepagesize: 2048 kB

最后，让我们计算实例所需的大页数量：

8823028 / 2048 = 4308.12

huge pages 数量应略高于此值 —— 仅高出一点点。如果将此值调得过高，需要小页和操作系统空间的进程将无法启动。这甚至可能导致操作系统无法启动，或者同一服务器上的其他 PostgreSQL 实例无法启动。

现在编辑上面创建的tuned.conf文件，并将以下行添加到[sysctl]部分：

vm.nr_hugepages=4500

并运行此命令以启用新设置：

tuned-adm profile edbpostgres

现在你可以设置:

huge_pages=on

在postgresql.conf中，并（重新）启动PostgreSQL。

我们还需要确保经过调优的服务在PostgreSQL服务之前以及重启后启动。因此，下一步是编辑文件：

systemctl edit postgresql-13.service

并添加这两行：

[Unit]
After=tuned.service

运行：

systemctl daemon-reload

以使更改生效。

Huge Pages and Transparent Huge Pages[2]

PostgreSQL性能调优起点

以下配置选项应从PostgreSQL的默认值更改。其他值可能会显著影响性能，但此处不做讨论，因为它们的默认值已被认为是最佳的。

配置与认证

max_connections

max_connections的最佳数值大约是CPU核心数的四倍。这个公式得出的通常是一个最小值，容错空间不大。建议的数值为GREATEST（4×CPU核心数，100）。超过这个数值时，应该使用pgbouncer等连接池工具。

重要的是不要将max_connections设置得过高，因为这会增加Postgres中各种数据结构的大小，浪费CPU周期。相反，我们还必须确保分配足够的资源来支持所需的工作负载。

Resource Usage

shared_buffers

这个参数的差异是所有参数中最大的。有些工作负载在值最小（例如1GB或2GB）时表现最佳，即便数据库容量很大也是如此。而其他工作负载则需要较大的值。以最小（RAM/2，10GB）作为起始点是比较合理的。

这个公式没有什么特定的原因，它只是基于PostgreSQL社区多年来的集体智慧和经验。内核缓存与shared_buffers之间的复杂交互，使得我们几乎无法确切解释为什么这个公式通常能带来良好的结果。

work_mem

work_mem的建议起始值为((Total RAM - shared_buffers)/(16 x CPU cores))。该公式背后的基本原理是，如果存在大量可能导致内存耗尽的查询，系统将已受到CPU容量的限制。此公式为一般情况提供了一个相对较大的限制值。

将work_mem设置为更高的值可能很诱人，但这应该避免，因为这里指定的内存量可能会被单个查询计划中的每个节点使用。因此，单个查询总共可能使用多个work_mem，例如在嵌套的哈希连接字符串中。

maintenance_work_mem

这决定了用于维护操作（如VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY以及数据加载操作）的最大内存量。执行这些操作时，可能会增加数据库服务器的I/O，因此为它们分配更多内存可能会加快这些操作的完成速度。1GB是一个不错的起始值，因为这些是数据库管理员明确运行的命令。

autovacuum_work_mem

将maintenance_work_mem设置为较高的值也会使自动清理工作进程各自使用同样多的内存。清理工作进程每清理一个死元组会使用6字节内存，因此仅8MB的内存就大约能处理140万个死元组。

effective_io_concurrency

该参数用于某些操作期间的预读，其值应设置为用于存储数据的磁盘数量。它最初的目的是帮助Postgres了解在使用旋转磁盘的条带化RAID阵列时，可能会有多少并行读取操作。不过，实践发现将该值设置为磁盘数量的倍数会带来性能提升，这可能是因为高质量的RAID适配器能够对请求进行重新排序和流水线处理以提高效率。对于SSD磁盘，由于其性能特性与旋转磁盘不同，应将该值设置为200。effective_io_concurrency 参数基准性能测试[3]

Write-Ahead Log

wal_compression

当此参数开启时，若full_page_writes处于开启状态或在基础备份期间，PostgreSQL服务器会对写入WAL的全页镜像进行压缩。将此参数设置为开启，因为大多数数据库服务器的瓶颈可能在于I/O而非CPU。

wal_log_hints

使用pg_rewind需要此参数。请将其设置为on。

wal_buffers

这控制着后端在内存中写入WAL数据的可用空间，以便WALWriter随后在后台将其写入磁盘上的WAL日志。默认情况下，每个WAL段为16MB，因此从内存角度来说，缓冲一个段的成本较低。观察发现，更大的缓冲区大小可能对测试性能有积极影响。将此参数设置为64MB。

checkpoint_timeout

更长的超时时间会减少总体WAL量，但会使崩溃恢复变慢。建议值至少为15分钟，但最终，业务需求的恢复点目标（RPO）将决定该值的大小。

checkpoint_completion_target

这决定了PostgreSQL目标完成检查点的时间。这意味着检查点不需要导致I/O峰值；相反，它旨在将写入操作分散在checkpoint_timeout值的这一比例时间内。推荐值为0.9（这将成为PostgreSQL 14中的默认值）。

max_wal_size

为了获得更好的性能和可预测性，超时应始终触发检查点。max_wal_size参数应用于防止磁盘空间耗尽，确保在达到该值时发生检查点，以便WAL能够被回收。建议值为WAL所在位置可用磁盘空间的一半到三分之二。

archive_mode

由于更改此项需要重启，因此应将其设置为开启，除非您确定永远不会使用WAL归档。

archive_command

如果归档模式开启，则需要有效的归档命令。在准备好配置归档之前，建议使用默认值":to be configured"。

原语在POSIX系统（包括Windows）上只需返回成功，告知Postgres WAL段可以被回收或移除。待配置的是一组会被忽略的参数。

PostgreSQL查询优化

random_page_cost

该参数为PostgreSQL优化器提供了从磁盘读取随机页面的成本提示，使其能够决定何时使用索引扫描而非顺序扫描。如果使用SSD磁盘，建议值为1.1。对于机械硬盘，默认值通常是足够的。此参数应在全局和每个表空间中进行设置。如果您有一个表空间，其中包含存储在磁带驱动器上的历史数据，您可能希望将此值设置得非常高，以阻止随机访问；顺序扫描加过滤可能比使用索引更快。

effective_cache_size

该值应设置为以下两个值中的较小者：0.75乘以总RAM量，或者free命令输出中的buff/cache、空闲RAM和共享缓冲区之和，用于向PostgreSQL提示可用的总缓存空间大小。这里指的是主内存中的缓存，而非CPU缓存。

在这个示例中，effective_cache_size将取最小值（64304 * 0.75，58113 + 5808 + 8192）（假设shared_buffers为8GB，即48228 MB）。

cpu_tuple_cost

指定查询期间处理每行的相对成本。其默认值为0.01，但这可能低于最优值，经验表明，为了更贴合实际成本，通常应将其提高到0.03。

Reporting and Logging

logging_collector

如果日志目标包含stderr或csvlog，为将输出收集到日志文件中，此参数应设为开启状态。

log_directory

如果日志收集器处于开启状态，此设置应指向数据目录之外的位置。这样，日志就不会成为基础备份的一部分。

log_checkpoints

为了将来的诊断目的，这应该设置为开启状态——特别是为了验证检查点是按checkpoint_timeout发生的，而不是按max_wal_size发生的。

log_line_prefix

这定义了添加到日志文件中各行前的前缀格式。该前缀应至少包含时间、进程ID、行号、用户和数据库以及应用程序名称，以辅助诊断。

建议值: %m [%p-%l] %u@%d app=%a

别忘了末尾的空格！

log_lock_waits

设置为开启。此参数在诊断慢查询时至关重要。

log_statement

设置为数据定义语言。除了留下基本的审计跟踪外，这还有助于确定灾难性人为错误发生的时间，例如误删了错误的表。

log_temp_files

设置为0。这将记录所有创建的临时文件，表明work_mem的调优不正确。

timed_statistics EPAS

控制动态运行时插桩工具架构（DRITA）功能的计时数据收集。设置为"开启"时，会收集计时数据。请将此参数设置为"开启"。

Autovacuum

log_autovacuum_min_duration

监控自动清理活动将有助于对其进行调优。建议值：0，这将记录所有自动清理活动。

log_autovacuum_min_duration

这是自动清理（autovacuum）的工作进程数量。默认值为3，且需要重启数据库服务器才能更新。每个表只能有一个工作进程对其进行操作，因此增加工作进程数量仅有助于在多个表之间实现并行且更频繁的清理操作。默认值较低，因此最好先将此值增加到5。

autovacuum_vacuum_cost_limit

为防止自动清理（autovacuum）给数据库服务器带来过大负载，Postgres 设置了一个 I/O 配额。每次读写操作都会消耗该配额，一旦配额用尽，自动清理进程就会休眠一段固定时间。此配置用于提高配额上限，增加清理操作可执行的 I/O 量。默认值较低，我们建议将其提高到 3000。

Client Connection Defaults

idle_in_transaction_session_timeout

在事务中保持空闲的会话可能会持有锁并阻止清理操作。此计时器将终止在事务中长时间保持空闲的会话，因此应用程序必须做好准备以从这种中断中恢复。

建议值：如果应用程序可以处理，为10分钟。

lc_messages

日志分析器只能理解未翻译的消息。将此设置为C以避免翻译。

shared_preload_libraries

添加pg_stat_statements的开销较低，但价值很高。这是推荐的，但非必需。

基于工作负载分析的PostgreSQL性能调优

Finding Slow Queries

找到慢查询主要有两种方法：

• • log_min_duration_statement参数。
• • pg_stat_statements模块和扩展。

log_min_duration_statement参数是一个时间设置（粒度为毫秒），用于指定查询需要运行多长时间才会被写入日志文件。若要获取所有查询，请将其设置为0，但需注意：这可能会导致大量的I/O操作！

通常，这被设置为1秒，所有查询都会按照如下方式进行优化。然后，逐渐降低该值，并重复这一过程，直到达到合理的阈值，之后便保持该阈值以进行持续优化。合理阈值的判定取决于你的工作量。

这种技术有助于发现执行缓慢的查询，但并非最佳选择。试想有一个查询需要1分钟执行完毕，且每10分钟运行一次。再假设有另一个查询，执行时间仅为20毫秒，却每秒运行20次。那么，优化哪一个更为重要呢？按10分钟来换算，第一个查询会占用服务器1分钟的时间，而第二个查询则会占用4分钟。因此，第二个查询比第一个更需要优化，但它很可能不会被log_min_duration_statement检测到。

下面来介绍pg_stat_statements模块。它的一个缺点是需要在shared_preload_libraries中进行设置，这需要重启服务器。不过幸运的是，它的开销非常低，而带来的收益却很高，因此我们建议在生产环境中始终安装它。

该模块的作用是记录服务器执行的每一个（已完成的）查询，通过各种方式对其进行标准化（例如用参数替换常量），然后将"相同"的查询聚合为单个数据点，并附上有趣的统计信息，如总执行时间、调用次数、最长和最短执行时间、返回的总行数、创建的临时文件总大小等。

如果两个查询在解析后的规范化内部结构相同，则它们被视为"相同"。因此，SELECT * FROM t WHERE pk = 42; 与 SELECT * FROM T WHERE pk=56; 是"相同"的查询，尽管 pk 的值不同。

要查看pg_stat_statements模块收集的统计信息，您首先需要使用CREATE EXTENSION pg_stat_statements安装pg_stat_statements扩展，并创建一个pg_stat_statements视图。

关于安全性的一点说明。该模块会收集对服务器的所有查询的统计信息，无论这些查询是针对哪些用户/数据库组合运行的。如有需要，此扩展可以安装在任何数据库中，甚至可以安装在多个数据库中。默认情况下，任何用户都可以从该视图中进行查询，但仅限于查看自己的查询（与pg_stat_activity视图相同）。超级用户以及被授予pg_read_all_stats或pg_monitor角色的用户可以查看所有内容。

EDB的PostgreSQL企业管理器（PEM）有一个SQL分析器[4]，可以很好地显示这一点。

Rewriting Queries

有时，重写查询的部分内容可以显著提升性能。

Naked columns

一个非常常见的错误是写出这样的内容：

SELECT * FROM t
WHERE t.a_timestamp + interval '3 days' < CURRENT_TIMESTAMP

而不是这样：

SELECT * FROM t 
WHERE t.a_timestamp < CURRENT_TIMESTAMP - interval '3 days'

这两个查询的结果是相同的，没有语义上的差异。不过，第二个查询可以使用 t.a_timestamp 上的索引，而第一个则不能。要让表列"展示"地放在左边，把所有表达式都放在右边。

永远不要将NOT IN与子查询一起使用

IN谓词有两种形式：x IN (a, b, c) 和 x IN (SELECT …) 。肯定形式可以使用其中任意一种。否定形式则使用第一种，这是由于空值的处理方式决定的。

考虑：

demo=# select 1 in (1, 2); 
?column?
---------- 
t 
(1 row) 

demo=# select 1 in (1, null);
?column? 
---------- 
t 
(1 row) 

demo=# select 1 in (2, null);
?column? 
---------- 
(null) 
(1 row)

这表明在存在空值的情况下，IN谓词只会返回true或null，绝不会返回false。由此可见，NOT IN只会返回false或null，绝不会返回true！

在给出这样的常量列表时，很容易发现其中是否存在空值，也能看出查询永远不会得到预期结果。但如果使用子查询版本，就不那么容易发现了。更重要的是，即使子查询结果保证没有空值，Postgres 也不会将其优化为反连接。请改用 NOT EXISTS。

使用EXPLAIN (ANALYZE, BUFFERS)

如果你的查询始终没有结果（至少在你失去耐心并放弃之前），你应该联系专家，或者自己成为专家来研究简单的EXPLAIN计划。在其他所有情况下，你都必须使用ANALYZE选项来优化查询。

Bad estimates

性能不佳最常见的原因是估算错误。如果表的统计信息不是最新的，Postgres可能会预测只返回两行，而实际会返回200行。对于单纯的扫描来说，这并不重要；它只会比预期多花一点时间，仅此而已。

真正的问题在于蝴蝶效应。如果Postgres认为一次扫描会产生两行数据，它可能会选择嵌套循环来进行连接操作。而当实际得到200行数据时，查询就会变慢；如果它事先知道会有这么多行数据，就会选择哈希连接或合并连接了。使用ANALYZE更新统计信息可以解决这个问题。

或者，你可能拥有规划器不知道的强相关数据。你可以使用CREATE STATISTICS来解决这个问题。

External sorts

如果排序操作的work_mem不足，Postgres会将数据写入磁盘。由于内存比磁盘（即使是固态硬盘）快得多，这可能会导致查询变慢。如果遇到这种情况，可以考虑增加work_mem。

demo=# create table t (c bigint);
CREATE TABLE
demo=# insert into t select generate_series(1, 1000000); 
INSERT 0 1000000 
demo=# explain (analyze on, costs off) table t order by c;
                         QUERY PLAN
---------------------------------------------------------------------- 
Sort (actual time=158.066..245.686 rows=1000000 loops=1) 
 Sort Key: c 
 Sort Method: external merge Disk: 17696kB 
 -> Seq Scan on t (actual
time=0.011..51.972 rows=1000000 loops=1)
Planning Time: 0.041 ms
Execution Time: 273.973 ms
(6 rows)

demo=# set work_mem to '100MB';
SET
demo=# explain (analyze on, costs off) table t order by c;
                         QUERY PLAN
----------------------------------------------------------------------
Sort (actual time=183.841..218.555 rows=1000000 loops=1)
 Sort Key: c
 Sort Method: quicksort Memory: 71452kB
 -> Seq Scan on t (actual time=0.011..56.573 rows=1000000 loops=1)
Planning Time: 0.043 ms
Execution Time: 243.031 ms
(6 rows)

由于数据集较小，这里的差异并不明显。现实世界中的查询差异可能会显著得多。有时，最好添加一个索引来完全避免排序。

为防止出现异常的、失控的查询，请设置temp_file_limit参数。生成这么多临时文件的查询将被自动取消。

Hash batches

另一个表明work_mem设置过低的迹象是哈希操作分批进行。在接下来的示例中，我们在运行查询前将work_mem设置为尽可能低的值。然后，我们重置该值并重新运行查询以比较执行计划。

demo=# create table t1 (c) as select generate_series(1, 1000000);
SELECT 1000000
demo=# create table t2 (c) as select generate_series(1, 1000000, 100);
SELECT 10000
demo=# vacuum analyze t1, t2;
VACUUM
demo=# set work_mem to '64kB';
SET
demo=# explain (analyze on, costs off, timing off)
demo-# select * from t1 join t2 using (c);
                         QUERY PLAN
------------------------------------------------------------------
Gather (actual rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (actual rows=3333 loops=3)
   Hash Cond: (t1.c = t2.c)
-> Parallel Seq Scan on t1 (actual rows=333333 loops=3)
-> Hash (actual rows=10000 loops=3)
   Buckets: 2048 Batches: 16 Memory Usage: 40kB
-> Seq Scan on t2 (actual rows=10000 loops=3)
Planning Time: 0.077 ms
Execution Time: 115.790 ms
(11 rows)

demo=# reset work_mem;
RESET
demo=# explain (analyze on, costs off, timing off)
demo-# select * from t1 join t2 using (c);
                         QUERY PLAN
------------------------------------------------------------------
Gather (actual rows=10000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (actual rows=3333 loops=3)
   Hash Cond: (t1.c = t2.c)
-> Parallel Seq Scan on t1 (actual rows=333333 loops=3)
-> Hash (actual rows=10000 loops=3)
   Buckets: 16384 Batches: 1 Memory Usage: 480kB
-> Seq Scan on t2 (actual rows=10000 loops=3)
Planning Time: 0.081 ms
Execution Time: 63.893 ms
(11 rows)

仅通过处理一个批次，执行时间就减少了一半。

Heap fetches

一行数据对执行查询的事务是否可见，存储在表中该行数据本身之中。可见性映射是一个位图，用于指示页上的所有行是否对所有事务可见。因此，索引扫描在找到匹配的行时，必须检查表（此处也称为堆），以确定找到的行是否可见。

仅索引扫描会利用可见性映射来避免从堆中读取行。如果可见性映射表明页面上并非所有行都可见，那么本应是仅索引扫描的操作最终会执行比必要更多的输入/输出操作。在最坏的情况下，它会完全退化为常规索引扫描。

执行计划将显示由于可见性映射不是最新的，它需要访问表的次数。

demo=# create table t (c bigint)  with (autovacuum_enabled = false);
CREATE TABLE
demo=# insert into t select generate_series(1, 1000000);
INSERT 0 1000000
demo=# create index on t (c);
CREATE INDEX
demo=# analyze t;
ANALYZE
demo=# explain (analyze on, costs off, timing off, summary off)
 select c from t where c <= 2000; 
                          QUERY PLAN 
---------------------------------------------------------------
Index Only Scan using t_c_idx on t (actual rows=2000 loops=1) 
Index Cond: (c <=2000) 
Heap Fetches: 2000
(3 rows)

理想情况下，这个数值应该是0，但它取决于表上的活动。如果你在不断修改和查询相同的页面，这里会显示出来。如果不是这种情况，就需要更新可见性映射。这可以通过VACUUM来完成（这也是我们在本演示中关闭自动VACUUM的原因）。

demo=# vacuum t; 
VACUUM demo=# explain
(analyze on, costs off, timing off, summary off) 
demo-# select c from t where c <=2000; 
                        QUERY PLAN
--------------------------------------------------------------- 
Index Only Scan using t_c_idx on t (actual rows=2000 loops=1) 
Index Cond: (c <=2000) 
Heap Fetches: 0 
(3 rows)

Lossy bitmap scans

当数据分散在各处时，Postgres会执行位图索引扫描。它会为找到的每个匹配行构建一个包含页面及其在页面内偏移量的位图。然后，它会扫描表（堆），只需对每个页面进行一次读取就能获取所有行。

然而，这只有在有足够的work_mem可用时才会发生。如果没有足够的work_mem，它就会"忽略"这些偏移量。只需记住，该页面上至少有一行匹配。堆扫描将不得不检查所有行，并过滤掉不匹配的行。

demo=# create table t (c1, c2) as
demo-# select n, n::text from generate_series(1, 1000000) as g (n)   order by random(); 
SELECT 1000000 
demo=# create index on t (c1);
CREATE INDEX 
demo=# analyze t; 
ANALYZE 
demo=# explain (analyze on, costs off, timing off)  select * from t
where c1 <=200000; 
                         QUERY PLAN 
------------------------------------------------------------------ 
Bitmap Heap Scan on t (actual rows=200000 loops=1) 
Recheck Cond: (c1 <=200000) 
Heap Blocks: exact=5406 
-> Bitmap Index Scan on t_c1_idx (actual rows=200000 loops=1)
   Index Cond: (c1 <= 200000) 
Planning Time: 0.065 ms 
Execution Time: 48.800 ms 
(7 rows)

demo=# set work_mem to '64kB' ; 
SET 
demo=# explain (analyze on, costs off, timing off) 
demo-# select * from t where c1 <=200000;
                         QUERY PLAN 
------------------------------------------------------------------ 
Bitmap Heap Scan on t (actual rows=200000 loops=1) 
Recheck Cond: (c1 <=200000) 
Rows Removed by Index Recheck: 687823 
Heap Blocks: exact=752 lossy=4654 
-> Bitmap Index Scan on t_c1_idx (actual rows=200000 loops=1)
   Index Cond: (c1 <= 200000) 
Planning Time: 0.138 ms 
Execution Time: 85.208 ms 
(8 rows)

Wrong plan shapes

这是最难检测的问题，而且只有通过经验才能发现。我们之前了解到，work_mem不足会导致哈希操作使用多个批次。但如果PostgreSQL认为不使用哈希连接、转而采用嵌套循环会更高效呢？这时，不会出现本节其他部分提到的那种"表现突出"的问题，但增加work_mem会使其重新采用哈希连接。了解查询应该采用何种特定的执行计划形态，并注意到它实际采用了不同形态的时候，就能获得一些很好的PostgreSQL优化机会。

Partitioning

分区有两个原因: 维护和并行化。

当一个表变得非常庞大时，默认自动清理设置所允许的死行数也会增加。对于一个只有10亿行的表，要等到有2亿行被更新或删除后，清理工作才会开始。在大多数工作负载下，这需要一段时间才能实现。当清理工作启动时——更糟的是，当环绕式到来时——就必须承担后果了：一个自动清理工作进程必须扫描整个表，收集死行列表。该列表每行死行占用6字节，因此大约需要1.2GB的内存来存储。然后，它必须逐一扫描表的每个索引，删除列表中找到的条目。最后，它会再次扫描表以删除这些死行本身。

如果您没有，或者无法为autovacuum_work_mem分配1.2GB的空间，这个过程会分批重复进行。在该操作的任何时刻，如果有查询需要的锁与autovacuum冲突，后者会主动退出并从头开始。但是，如果autovacuum是为了防止事务ID回卷，那么查询将不得不等待。

自动清理（Autovacuum）使用可见性映射来跳过自上次清理后未被触及的大量表数据，9.6版本在防止事务ID回卷的清理方面更进一步，但索引方法没有这样的优化；它们每次都要被完全扫描。此外，表中留下的空洞可以被未来的插入/更新操作填充，但由于索引中的值是有序的，所以重用索引中的空闲空间要困难得多。清理次数减少意味着必须更频繁地重建索引以保持其性能。在PostgreSQL 11及之前的版本中，重建索引需要锁定表以阻止写入，而PostgreSQL 12则可以并发重建索引。

通过将数据分割成更小的块，每个分区及其索引可以由不同的工作节点处理。这样每个节点的工作量会减少，而且它们处理工作的频率会更高。

有时，分区可以用来消除执行清理（vacuum）的需求。如果你的表存储的是类似时间序列的数据，基本属于"插入后就不再管"的情况，那么上述问题就不那么严重了。一旦旧行被冻结，自动清理（autovacuum）就再也不会处理它们了（如前所述，这是9.6版本及以上的特性）。这里的问题在于数据保留策略。如果数据只保留10年，之后可能在归档到冷存储后就被删除，这会产生一个空洞，新数据会填补这个空洞，导致表变得碎片化。这会使所有BRIN索引完全失效。

解决这个问题的常用方法是按月份（或任何所需的粒度）进行分区。这样，操作流程就变成了：分离旧分区，将其转储以存档，然后删除表。此时，就完全不需要进行清理操作了。

至于并行化，如果您有需要随机访问的大型表（例如多租户设置），那么按租户对这些表进行分区可能是可取的，这样可以将每个租户（或租户组）放在单独的表空间中，以提高I/O性能。

通常不适合进行分区的一个原因是错误地认为多个小表在查询性能上比一个大表更好。这往往会降低性能。

优化你的OLTP性能

这些说明应该能为大多数OLTP工作负载提供一个良好的起点。监控和调整这些及其他设置，对于让PostgreSQL在特定工作负载下发挥最佳性能至关重要。在未来的文档中，我们将介绍优秀数据库管理员（DBA）的监控工作和其他日常任务。

introduction-postgresql-performance-tuning-and-optimization[5]

引用链接

[1] 优化文件系统: https://docs.google.com/document/d/1ZoCH9UFBeUBg9BbFRjb9b9KkzrP_MXQX/edit#bookmark=id.iffnit30nk7z
[2] Huge Pages and Transparent Huge Pages: Hugehttps://docs.redhat.com/en/documentation/red_hat_enterprise_linux/6/html/performance_tuning_guide/s-memory-transhuge
[3] effective_io_concurrency 参数基准性能测试: https://portavita.github.io/2019-07-19-PostgreSQL_effective_io_concurrency_benchmarked/
[4] SQL分析器: https://www.enterprisedb.com/docs/pem/latest/
[5] introduction-postgresql-performance-tuning-and-optimization: https://www.enterprisedb.com/postgres-tutorials/introduction-postgresql-performance-tuning-and-optimization

阅读原文：原文链接