Postgres 减少延迟的生产调整

该英文文本是根据法文原文自动生成的，尚未经过审查。

该页面已自动翻译。英文版具有权威性。

postgresql.conf 默认情况下不会被破坏，它是谨慎的：调整大小以在最小的计算机上运行而不会导致安装失败，而不是处理您的生产流量。从这些兼容性值到生产值是测量出来的，无法猜测。对于测量协议本身（实际负载、p50/p95/p99、可重现的结果），请参阅我们的后端基准测试方法。本文按最重要的顺序详细介绍了这些设置本身。

要点

五个项目，按顺序：连接/池、内存、autovacuum、索引/慢查询、检查点/WAL。
max_connections 和 shared_buffers 需要完全重新启动服务器；大多数其他设置都是热加载的。
切勿在生产中禁用 autovacuum：真正的风险不是缓慢，而是事务 ID 环绕。
在简单的 CREATE EXTENSION 收集任何内容之前，pg_stat_statements 必须列在 shared_preload_libraries 中。
集成到 Aurabase Studio 中的 Advisor 已经自动应用此清单的一部分：请求持续超过 150 毫秒、外键未索引、连接池超过 80% 饱和度。

起点

为什么 Postgres 默认值永远不够

postgresql.conf在其默认状态下，旨在保证安装永远不会失败，而不是吸收您的流量。 shared_buffers的历史值是 128 MB，允许 Postgres 在最小的计算机上启动，而无需保留任何关键资源。 max_connections 100 适合小型共享服务器。两者都不是根据您的实际负载选择的。

所以问题不在于 Postgres 默认设置不好：而是它从来没有为你设置过。以下部分按照最有效的顺序介绍这些设置，从最常见的瓶颈（连接）到出现最慢的瓶颈（WAL）。

连接

调整 max_connections 和池化优先

第一个项目不是记忆，而是联系。每个 Postgres 连接都会打开一个专用服务器进程，即使空闲，也会消耗 RAM 和上下文 CPU 时间。增加 max_connections 以避免“连接过多”类型错误会转移问题：超过一定数量的同时活动连接，CPU 争用会降低所有请求的延迟，包括最快的请求。

正确的方法颠倒通常的顺序：在实际服务器并发上扩展 max_connections ，然后使用像 PgBouncer 这样的池化器将应用程序端并发吸收到 pool_mode=transaction中。池化器将数百个客户端连接复用到少数实际活动的服务器连接上。我们的专题文章详细介绍了事务模式机制及其限制（准备好的语句，LISTEN/NOTIFY），以及 PgBouncer、Supavisor 和 PgCat 之间的比较来选择实现。

max_connections 是一个“postmaster”上下文参数：更改它需要完全重新启动服务器，而不是简单的重新加载。在 Aurabase 专用 Postgres 实例（Pro 和 Enterprise 计划，每个项目一个 CNPG 集群）上，此设置是按计划层配置的，而不是保留默认值。重新启动并不是在生产中重复的简单操作，这证明了分阶段选择而不是固定值的选择是合理的。选择自己的值的公式及其限制是另一篇文章的主题： size max_connections。

内存

shared_buffers、work_mem、 effective_cache_size：重要的设置

四种内存设置的重量比所有其他设置的总和还要重： shared_buffers、 effective_cache_size、 work_mem 和 maintenance_work_mem。前三个决定 Postgres 在返回磁盘之前在内存中保留多少数据；第四个决定 VACUUM 或索引创建的速度。

shared_buffers 设置所有连接共享的内部缓存。 PostgreSQL 项目通常记录的基准大约是专用数据库服务器上可用 RAM 的 25%。除此之外，增益会减少，操作系统的磁盘缓存会接管。 effective_cache_size 不分配任何内容：它是提供给查询规划器的可用于缓存的总内存（Postgres 和操作系统的总内存）的估计值。尺寸过小会促使调度程序进行顺序扫描，而索引则主要会进行缓存；当前的基准测试是 RAM 的 50% 到 75% 之间。

work_mem 是最常见的陷阱。这不是全球限制。查询中的每个排序或散列操作都可以消耗自己的份额，并且具有多个联接的查询可以多次保留自己的份额。太大的值与较高的 max_connections 相结合可能会在并发负载下耗尽服务器 RAM，即使单独采取的每个请求看起来都是合理的。相反， maintenance_work_mem可以保持更加慷慨：它仅适用于维护操作（VACUUM、CREATE INDEX），这些操作很少彼此并发。

只有 shared_buffers 需要重新启动。其他三个是热重载的，包括用于隔离会话的： SET work_mem = '64MB'; 在单个贪婪请求的持续时间内，而不影响全局设置。

维护保养

Autovacuum：调整阈值，切勿禁用它

切勿在生产中禁用 autovacuum，即使是在高峰负载期间暂时“释放资源”。 Postgres 使用 MVCC：每个 UPDATE 和每个 DELETE 都会留下一条死线，只有真空可以恢复。如果没有它，表会膨胀，索引会降级，执行计划也会逐渐恶化，直到很晚才出现可见的错误。

真正的风险不是缓慢

禁用或尺寸过小的 autovacuum 最严重的危险不是性能，而是事务 ID 环绕。在达到阈值后，Postgres 将整个数据库切换为只读以避免数据损坏，直到执行手动 VACUUM。这是一个生产事故，通过正确的配置是完全可以避免的。

autovacuum_vacuum_scale_factor的默认值（触发前20%死行）适用于小表，而不是写入密集型的数百万行表。在包含 1000 万行的表上，这 20% 表示在第一次传递之前累积了 200 万个死行。逐表降低这个阈值，而不是改变整个数据库的整体价值。

psqlsql

-- 降低写入频繁的表的阈值，而不是整体降低阈值
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

集成到 Aurabase Studio 中的 Advisor 在每个项目分析时都会检查此配置，就像没有主键或非索引外键的表一样。这是一个明确的信号，而不是发现得太晚的无声退化。

查询速度慢

添加RAM之前的索引

生产中的大多数延迟问题既不是来自 CPU，也不是来自 RAM：它们来自索引缺失或选择不当。在接触 postgresql.conf的单个参数之前，相关查询上的 EXPLAIN (ANALYZE, BUFFERS) 仍然是最可靠的诊断。它在添加资源之前就减少了 Postgres 请求延迟。

psqlsql

-- 在接触配置之前诊断慢速查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

数百万行表上的 Seq Scan（预期为 Index Scan）几乎总是表示存在索引问题。最常见的原因有三个：缺少索引、列类型与现有索引不兼容，或者在没有 ANALYZE的情况下进行大规模导入后统计信息过时。添加 RAM 或增加 work_mem 有时会在少量数据上隐藏此症状；一旦表增长，问题就会再次出现。

为了定位这些请求而不需要一一搜索，pg_stat_statements 聚合了所有服务器请求的执行统计信息。一个常见的陷阱：扩展必须首先列在 shared_preload_libraries中，这是一个需要重新启动的“postmaster”上下文参数。如果没有这一步，CREATE EXTENSION pg_stat_statements; 会默默地成功，但不会收集任何信息。

postgresql.conf → psqlsql

# 需要完全重新启动（“postmaster”参数）
shared_preload_libraries = 'pg_stat_statements'

-- 服务器重新启动后，在每个数据库中进行审核：
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

这正是当此扩展不存在时 Aurabase 后端返回的错误：显式消息而不是静默空列表，这可能与“无慢速查询”混淆。 Studio Advisor 更进一步：它会自动将平均时间超过 150 毫秒的任何请求分类为警告，将超过 500 毫秒的请求分类为严重。这些阈值基于相同的 pg_stat_statements统计数据。

磁盘写入

检查点和 WAL：平滑负载而不是承受负载

检查点强制 Postgres 将自上一页以来内存中修改的所有页面写入磁盘。默认情况下，本文可能关注的时间窗口太短。结果是应用程序端的磁盘延迟明显增加，这种周期性的减速很难与特定请求相关。

checkpoint_completion_target 控制此写入在两个检查点之间的间隔上的传播。旧检查表中缺少一个细节：2021 年发布的 PostgreSQL 14 将其默认值从 0.5 更改为 0.9。因此，在 PostgreSQL 16 实例（例如专用 Aurabase 租户集群）上，默认情况下此设置已经正确；手动调整仅对 14 之前的版本有意义。请参阅我们的比较 PostgreSQL 16 vs 17 vs 18 了解影响调整的其他版本更改。

max_wal_size 的作用方向相同：太低的值会比预期更频繁地触发检查点，即使尚未达到 checkpoint_timeout 也是如此。增加它会减少检查点的频率，但代价是崩溃后需要更长的恢复时间，因为有更多的 WAL 需要重放。根据您对不可用的容忍度来决定的折衷方案，而不是普世价值。

连续

监控不是一个步骤，而是关闭清单的循环

该清单不是在投入生产之前检查一次的一次性审核。体积或流量翻倍的基础使得启动时选择的基准变得过时，通常没有明确的错误，只是 p95 延迟的逐渐退化。

三个信号值得继续监测。 pg_stat_statements 标识随时间推移而降级的请求。 pg_stat_activity 报告阻塞或异常长时间运行的查询，并且活动连接与 max_connections 的比率在产生应用程序端错误之前预测饱和。

Studio 的可观察性选项卡涵盖了任何 Aurabase 项目的部分基础，无需安装第三方工具。它列出了缓慢的请求，允许您通过 PID 取消或终止活动请求，并显示池饱和指示器，当使用率超过 80% 时会发出警告。在自托管实例上，相同的监控是手动构建的，激活 pg_stat_statements 并插入外部监控工具。

总结

备忘单：完整的清单

八种设置（按最有效的顺序排列）以及您在接触它们之前需要了解的内容。

最大连接数	重新启动	根据实际比赛情况确定尺寸，而不是整数；通过交易模式下的池化器吸收其余部分。
共享缓冲区	重新启动	大约 25% 的 RAM 专用于 Postgres。
有效缓存大小	热门	≈ RAM 的 50% 到 75%（Postgres + 操作系统缓存组合）。
工作内存	热点/场次	默认谨慎；使用 SET 在逐个查询的基础上向上测试。
维护工作内存	热门	比work_mem更慷慨；加速 VACUUM 和 CREATE INDEX。
autovacuum_vacuum_scale_factor	热，每桌	在大型写入量大的表上较低，但绝不是全局的。
检查点完成目标	热门	从 PostgreSQL 14 开始默认为 0.9；特别是检查早期版本。
共享预加载库	重新启动	在任何慢速查询分析之前必须包含 pg_stat_statements。

这里引用的阈值，例如 Aurabase Studio Advisor 监控的 150 毫秒和 80% 池饱和度，是经过代码验证的起点，而不是普遍真理。您的实际指控仍然是唯一的最终法官。为了精确确定 max_connections 的大小而不是遵循一般规则，专门文章详细介绍了公式及其限制。

常见问题解答

常见问题解答

第一次应用清单后会系统地出现三个问题。

更改 postgresql.conf 值后是否应该重新启动 Postgres？+

这取决于设置。 max_connections 和 shared_buffers 位于“postmaster”上下文中：需要完全重新启动。 work_mem、 effective_cache_size、 maintenance_work_mem 和大多数 autovacuum 阈值可以使用 SELECT pg_reload_conf(); 或 pg_ctl reload进行热充电，而不会中断服务。

我们可以停用 autovacuum 以提高性能吗？+

不，从未投入生产。关闭它并不会让清洁工作消失：它会累积。如果不进行干预，它最终会强制执行手动清理，或者更糟糕的是，触发事务 ID 环绕，从而使数据库变为只读。逐表调整阈值，而不是削减机制。

您应该多久查看一次此清单？+

在容量或负载的每次重大变化时，而不仅仅是在初始部署时。规模或流量翻倍或乘以三的模式使得上述起点变得过时。我们的后端基准测试方法详细介绍了如何构建随时间重复的测量协议，而不是孤立的审计。