优化 `postgresql.conf` 参数以实现最佳读写性能

PostgreSQL 通常使用打包的默认设置即可正常运行，但一旦真实流量到来，“可接受”可能会变成缓慢的读取、突发的写入或随机出现的延迟。postgresql.conf 文件是您设置基本资源预算的地方：PostgreSQL 可用于共享缓存的内存量、每个查询操作在溢出到磁盘之前可以使用的内存量、检查点写入脏页的积极程度，以及规划器获得的关于底层机器的提示。

我最常见的错误是将 PostgreSQL 调优视为一串神奇数字。有人复制 shared_buffers = 25% 的 RAM，将 work_mem 设置为一个很大的值，加倍 max_connections，然后希望数据库变得更快。有时确实如此。有时它会在报告作业期间开始交换，或者在检查点期间遇到瓶颈。

更安全的方法是根据症状进行调优。读取慢是因为工作集没有被缓存吗？报告因为排序溢出到磁盘了吗？写入在检查点期间堆积了吗？太多应用程序连接在竞争内存吗？本指南将介绍通常首先重要的参数，并提供您可以调整而不是盲目复制的示例。

理解核心内存参数

高效的内存管理对于高性能数据库系统至关重要。PostgreSQL 使用各种内存区域，其中两个最关键的是用于缓存频繁访问数据的 shared_buffers 和用于内部查询操作的 work_mem。

`shared_buffers`

shared_buffers 可以说是最重要的内存调优参数之一。它定义了 PostgreSQL 用于缓存数据块的专用内存量。这些数据块包括表数据、索引数据和系统目录。当查询请求数据时，PostgreSQL 首先检查 shared_buffers。如果在那里找到数据（缓存命中），则检索速度比从磁盘读取快得多。

对性能的影响

读取性能： 较大的 shared_buffers 值会增加缓存命中的可能性，从而显著减少读取密集型工作负载的磁盘 I/O。这转化为更快的查询响应。
写入性能： shared_buffers 也保存“脏”页（已被修改但尚未写入磁盘的数据块）。更大的缓冲区可以吸收更多的写入，允许系统将它们批处理为更少、更大的磁盘写入，从而提高写入吞吐量。但是，如果太大，可能会导致更长的检查点时间和检查点期间 I/O 峰值增加。

调优指南

起点： 一个常见的建议是将 shared_buffers 设置为总物理 RAM 的 25%。例如，在具有 16GB RAM 的服务器上，shared_buffers 将为 4GB。
更大的 RAM 系统： 在具有 64GB+ RAM 的服务器上，分配 25% 可能过多。PostgreSQL 也依赖于操作系统的文件系统缓存。超过某个点后，增加 shared_buffers 可能会带来递减的回报，因为操作系统缓存可以有效地处理大部分剩余的缓存。在这种情况下，15-20% 可能就足够了，从而为操作系统缓存或 work_mem 留出更多 RAM。
监控： 密切关注 pg_stat_database 中的缓存命中率，但不要将一个百分比视为一切健康的证据。高命中率可能隐藏一些非常昂贵的查询，而较低的比率对于扫描大表一次的批处理作业可能是正常的。还要监控检查点行为和磁盘延迟。

示例配置

要在 postgresql.conf 中将 shared_buffers 设置为 4GB：

shared_buffers = 4GB

提示： 更改 shared_buffers 后，您必须重新启动 PostgreSQL 服务才能使更改生效。

更改后一个实用的检查：

SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

如果您增加了 shared_buffers 但应用程序仍然等待磁盘读取，问题可能在于查询形状、缺少索引、表膨胀或工作集大于内存。更多的缓存不能替代更好的执行计划。

`work_mem`

work_mem 指定查询操作（如排序或哈希表）在将临时数据写入磁盘之前可以使用的最大内存量。此内存是每个会话、每个操作分配的。如果一个复杂查询涉及多个排序或哈希操作，它可能在单个会话内多次消耗 work_mem。

对性能的影响

复杂查询： work_mem 显著影响涉及 ORDER BY、GROUP BY、DISTINCT、哈希连接和物化的查询。当排序或哈希操作超过 work_mem 限制时，PostgreSQL 会将多余的数据溢出到临时磁盘文件，导致执行速度慢得多。
并发性： 由于 work_mem 是每个操作、每个会话分配的，因此较高的全局 work_mem 值加上许多并发复杂查询会迅速耗尽可用 RAM，导致交换和严重的性能下降。

调优指南

避免过高的全局值： 不要盲目地将 work_mem 设置为一个非常大的全局值。相反，要考虑应用程序的典型并发性以及最资源密集型查询的内存占用。
监控磁盘溢出： 对有问题的查询使用 EXPLAIN ANALYZE。查找类似 Sort Method: external merge Disk: NkB 或 HashAggregate batches: N (disk) 的行，这表明 work_mem 不足并且数据已溢出到磁盘。
针对性调优： 对于特定的长时间运行的报告或批处理作业，考虑在执行查询之前在会话级别设置 work_mem，而不是全局设置。这允许该特定查询使用更高的内存，而不会影响其他并发会话。

示例配置

要在 postgresql.conf 中将 work_mem 全局设置为 16MB：

work_mem = 16MB

要为特定会话（例如，在 psql 或应用程序连接中）设置 work_mem：

SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY some_column;

警告： 增加 work_mem 时要小心。如果 100 个并发查询每个需要 1GB work_mem，那就是 100GB 的 RAM！始终在暂存环境中测试更改并监控系统的内存使用情况。

一种更实际的使用 work_mem 的方法是保持全局值适中，然后仅为已知的报告会话提高它：

BEGIN;
SET LOCAL work_mem = '256MB';

SELECT customer_id, sum(total_amount)
FROM orders
WHERE created_at >= current_date - interval '90 days'
GROUP BY customer_id
ORDER BY sum(total_amount) DESC;

COMMIT;

该模式比提高每个 Web 请求的全局值更安全。具有许多短查询的 Web 应用程序需要可预测的内存使用。夜间报告可以承受更大的每个查询预算。

使用检查点管理写入性能和持久性

检查点是 PostgreSQL 中确保数据持久性和管理事务日志（WAL - 预写日志）的关键机制。它们定期将修改后的数据块从 shared_buffers 同步到磁盘，标记所有先前更改已写入永久存储的点。

`checkpoint_timeout`

checkpoint_timeout 定义自动 WAL 检查点之间的最长时间。如果自上次检查点以来生成的 WAL 段数量超过 max_wal_size，也会发生检查点。

对性能的影响

频繁检查点（短 checkpoint_timeout）： 导致更频繁的 I/O 峰值，因为脏页被刷新到磁盘。虽然这减少了崩溃后的恢复时间（需要重放的 WAL 更少），但由于集中的写入活动，可能会对活动工作负载的性能产生负面影响。
不频繁的检查点（长 checkpoint_timeout）： 减少 I/O 峰值的频率，从而在正常操作期间实现更平滑的性能。但是，这意味着在崩溃的情况下可能需要从 WAL 重放更多数据，从而导致更长的数据库恢复时间。它还需要更大的 max_wal_size 来存储累积的 WAL 段。

调优指南

平衡： 目标是在平滑的持续性能和可接受的恢复时间之间找到平衡。许多生产系统从大约 5-15 分钟开始，然后根据 WAL 容量和恢复目标进行调整。
与 max_wal_size 的相互作用： 这两个参数协同工作。如果 checkpoint_timeout 很长但 max_wal_size 太小，则检查点将由 max_wal_size 触发，而不是由 checkpoint_timeout 触发。调整 max_wal_size 使其足够大，以允许 checkpoint_timeout 成为主要触发器。
监控： 使用 pg_stat_bgwriter 观察 checkpoints_timed 和 checkpoints_req 计数器。如果 checkpoint_timeout 是主要触发器，则 checkpoints_timed 应显著高于 checkpoints_req（由于 WAL 大小限制而请求的检查点）。

示例配置

要在 postgresql.conf 中将 checkpoint_timeout 设置为 10 分钟：

checkpoint_timeout = 10min
# 同时考虑相应调整 max_wal_size
max_wal_size = 4GB # 示例，根据工作负载调整

最佳实践： 目标是让检查点主要由 checkpoint_timeout 触发，而不是 max_wal_size。这提供了更可预测的 I/O 模式。如果 max_wal_size 频繁触发检查点，请增加其值。

使用以下命令检查模式：

SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_bgwriter;

如果 checkpoints_req 快速增加，则 PostgreSQL 正在检查点，因为 WAL 增长超过了 max_wal_size，而不是因为计时器到期。这通常表现为写入 I/O 的突发。增加 max_wal_size 可以平滑工作负载，但也可能增加崩溃恢复时间，因为可能需要重放更多的 WAL。

值得检查的计划器和 WAL 设置

三个设置通常位于大的内存和检查点参数旁边。

effective_cache_size 不是 PostgreSQL 分配的内存。它是规划器对 PostgreSQL 共享缓冲区和操作系统文件缓存中可能可用的缓存量的估计。如果设置得太低，规划器可能会避免索引扫描，因为它假设读取成本很高。在专用数据库服务器上，一个常见的起点是 RAM 的很大一部分，但正确的值取决于主机上运行的其他内容。

effective_cache_size = 12GB

maintenance_work_mem 影响维护操作，例如 CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 和 VACUUM。它不像 work_mem 那样用于普通查询排序。如果在维护窗口期间索引构建非常缓慢，则为会话提高此值会有所帮助：

SET maintenance_work_mem = '1GB';
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

wal_buffers 控制在写出之前用于 WAL 记录的内存。默认值通常没问题，因为 PostgreSQL 可以自动调整其大小，但具有大事务的写入密集型工作负载可能会受益于在更改之前检查 WAL 写入是否是瓶颈。不要仅仅因为它出现在清单中就调整它。

不同工作负载的不同起点

对于 OLTP Web 应用程序，优先级是在并发下保持稳定的延迟。保持 work_mem 保守，使用连接池而不是允许数千个直接连接，并在归咎于 shared_buffers 之前注意锁等待和糟糕的计划。一个典型的问题如下：一个版本添加了一个跨数百万行使用 ORDER BY created_at DESC 的仪表板查询，该查询溢出到磁盘，并且突然每个请求都变慢，因为数据库正在执行临时文件 I/O。解决方法可能是索引或更窄的查询，而不是更大的全局 work_mem。

对于分析或报告数据库，大型排序和哈希聚合是正常的。您可以为报告角色提高 work_mem，为批量索引工作增加 maintenance_work_mem，并接受运行时间更长的查询。风险在于并发性。十个分析师同时运行内存密集型报告可能会消耗比一个成功的测试查询所建议的更多的内存。

对于写入密集型系统，检查点和 WAL 更重要。如果应用程序有周期性的写入停顿，请检查它们是否与检查点一致。还要查看存储延迟、WAL 磁盘饱和、自动清理活动以及长事务是否阻止了清理。单独增加 checkpoint_timeout 不会修复无法跟上平均写入量的磁盘。

一个简单的调优工作流程

首先记录当前配置：

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'checkpoint_timeout',
    'max_wal_size',
    'wal_buffers',
    'max_connections'
)
ORDER BY name;

然后在更改任何内容之前捕获症状。使用 EXPLAIN (ANALYZE, BUFFERS) 保存一个或两个慢查询计划。如果您怀疑溢出，请检查临时文件日志记录：

log_temp_files = 0

该设置会记录每个临时文件，因此在繁忙系统上要小心使用，或者将其设置为阈值，例如 64MB。如果您看到来自相同查询形状的许多大型临时文件，请调整查询、添加索引或为该工作负载提高 work_mem。

一次更改一件事。某些设置需要重新启动，某些只需要重新加载，某些可以在每个会话中设置。PostgreSQL 会告诉您哪个是哪个：

SELECT name, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'checkpoint_timeout', 'max_wal_size');

postmaster 上下文意味着重新启动。sighup 意味着重新加载。user 意味着可以进行会话级更改。

通用调优技巧和最佳实践

迭代调优： 从小的、递增的更改开始。一次更改一个参数，观察影响，然后根据需要进一步调整。调优不是一次性的任务，而是一个持续的过程。
监控一切： 利用 PostgreSQL 的内置统计视图（pg_stat_database、pg_stat_bgwriter、pg_stat_activity）、操作系统级监控工具（例如 iostat、vmstat、top）和外部监控解决方案来收集有关 CPU、内存、磁盘 I/O 和查询性能的数据。
了解您的工作负载： 您的应用程序是读取密集型还是写入密集型？它执行复杂的分析查询还是简单的事务操作？根据您特定的工作负载特征定制您的配置。
考虑其他参数： 虽然 shared_buffers、work_mem 和 checkpoint_timeout 至关重要，但许多其他参数也会影响性能。例如，effective_cache_size（向查询规划器提示可用的操作系统缓存）和 wal_buffers（刷新前用于 WAL 记录的内存）通常与这些参数一起调整。
使用 EXPLAIN ANALYZE： 这个宝贵的工具可以帮助您理解 PostgreSQL 如何执行查询，识别瓶颈，并可以揭示 work_mem 是否不足。

最好的 PostgreSQL 调优工作以一种好的方式很无聊：测量，更改一个设置，再次测量，并保留回滚路径。shared_buffers、work_mem 和检查点设置可以产生真正的差异，但它们与查询计划、索引、自动清理、连接数和存储协同工作。如果这些部分不健康，仅靠配置无法拯救数据库。

优化 postgresql.conf 参数以实现最佳读写性能

理解核心内存参数

shared_buffers

对性能的影响

调优指南

示例配置

work_mem

对性能的影响

调优指南

示例配置

使用检查点管理写入性能和持久性

checkpoint_timeout

对性能的影响

调优指南

示例配置

值得检查的计划器和 WAL 设置

不同工作负载的不同起点

一个简单的调优工作流程

通用调优技巧和最佳实践

优化 `postgresql.conf` 参数以实现最佳读写性能

`shared_buffers`

`work_mem`

`checkpoint_timeout`