PostgreSQL 参数介绍与调优

PostgreSQL 调优最像什么？我觉得像给一个正在发烧的人调空调：温度计都没看，先把旋钮从 26°C 拧到 16°C，动作很果断，病因却可能毫无变化。

网上流传着许多「生产环境必改参数」：shared_buffers 设为内存的四分之一，work_mem 往大了加，机械盘把 random_page_cost 保持在 4.0，SSD 就改成 1.1。这些建议并非全错，但一脱离工作负载、并发数、存储、数据规模和可接受的恢复时间，就只剩下一串看似专业的数字。

数据库调优不是寻找一份神奇配置，而是用观测证明瓶颈在哪里，再用最小的改动换取可验证的改善。 参数只是工具。慢查询可能缺索引，也可能是统计信息过期、锁等待、连接风暴、检查点写入尖峰，甚至是应用一次取回了根本用不完的几十万行。若 SQL 本身有问题，给数据库多分内存通常只是让错误跑得更快一点。

这篇文章以 PostgreSQL 18 为参考，介绍最常碰到的参数及其相互作用。版本不同，默认值和能力可能变化，实际修改前应以正在运行的实例和对应版本文档为准。

一、先别改：弄清参数从哪里来

PostgreSQL 的参数不只存在于 postgresql.conf。它们可以由启动命令、ALTER SYSTEM、数据库、角色、会话乃至单个事务覆盖。排查时只打开配置文件，很容易看到一份「明明写对了，却没有生效」的配置。

先问数据库本人：

SELECT name,
       setting,
       unit,
       source,
       sourcefile,
       sourceline,
       pending_restart
FROM pg_settings
WHERE name IN (
  'shared_buffers',
  'work_mem',
  'effective_cache_size',
  'max_connections',
  'max_wal_size'
)
ORDER BY name;

source 表明当前值来自默认配置、文件、命令行还是会话；pending_restart 则告诉你修改是否正在等待重启。不同参数的生效范围并不一样：

• shared_buffers、max_connections 等参数只能在服务器启动时确定
• 一些参数修改文件后可以通过 SELECT pg_reload_conf(); 重新载入
• work_mem 等参数可以在会话或事务中使用 SET 临时调整
• ALTER SYSTEM 会写入 postgresql.auto.conf，其设置通常覆盖 postgresql.conf

这也是我的第一个原则：能在局部验证的参数，不要一上来全局修改；需要重启的参数，更要准备回滚与维护窗口。

调优之前至少记录一份基线：CPU 使用率、内存和 Swap、存储延迟与吞吐、连接数、事务速率、缓存命中、WAL 生成速率、检查点、锁等待，以及请求延迟的分位数。只看平均延迟会把偶发但致命的长尾藏起来。改动前后用相同流量或可重复压测比较，否则所谓「快了」更可能只是今天用户少。

二、内存参数：最容易调，也最容易把机器调死

shared_buffers：PostgreSQL 的共享缓存

shared_buffers 是 PostgreSQL 自己管理的数据页缓存。官方文档对内存不少于 1 GB 的专用数据库服务器给出的合理起点是物理内存的 25%，同时指出超过 40% 往往不会比更小的设置更好。原因很简单：PostgreSQL 并不独占缓存，它还依赖操作系统页缓存。

这只是起点，不是圣旨。容器的内存上限、同机运行的服务、备份与监控进程都要占空间。设得太小会增加读盘，设得太大则挤压操作系统缓存和每个连接使用的内存，甚至把实例送进 OOM。扩大 shared_buffers 后，脏页规模可能一起增长，通常还要观察检查点行为，并评估 max_wal_size 是否合适。

work_mem：不是每个连接只有一份

work_mem 是排序、哈希、物化等执行节点在写临时文件前可以使用的基础内存额度。最危险的误解是把它理解成「每个连接最多使用这么多」。实际上，一条查询的执行计划中可能同时出现多个排序和哈希节点，多个并行工作进程也可能各自使用额度；服务器上又可能有许多查询并发执行。

粗略的风险模型更接近：

潜在内存 ≈ 活跃查询数 × 每条查询的内存节点数 × work_mem × 并行进程影响

它不是精确公式，却足够提醒我们：把 work_mem 从 4 MB 全局改到 256 MB，并不只是「多用一点内存」。50 条并发复杂查询，各自出现几个内存节点，理论压力很快就能越过机器上限。

更稳妥的做法是保留克制的全局值，再为报表、批处理或已验证会落盘的事务局部提高：

BEGIN;
SET LOCAL work_mem = '128MB';

-- 已确认需要较大排序或哈希空间的报表查询
SELECT ...;

COMMIT;

hash_mem_multiplier 会在 work_mem 基础上决定哈希操作可用的上限。发现哈希批次数量多、频繁落盘时，可以一起评估，但仍要算并发总账。调内存不是比谁写的数字大，而是减少临时 I/O 又不制造内存尖峰。

maintenance_work_mem 与 autovacuum_work_mem

maintenance_work_mem 供 VACUUM、CREATE INDEX 等维护操作使用，通常可以比 work_mem 大。问题在于自动清理可能有多个 worker 同时运行；若 autovacuum_work_mem 保持 -1，它会继承 maintenance_work_mem。于是一个对手工建索引很舒服的超大值，可能被多个 autovacuum worker 同时采用。

因此可以分开考虑：让手工维护拥有较大的 maintenance_work_mem，再用 autovacuum_work_mem 限制单个自动清理进程。不要忘了监控实际并发与内存，而不是假设所有维护永远错峰。

effective_cache_size：它不分配内存

这个名字很容易骗人。effective_cache_size 只是查询规划器对「单条查询大约能利用多少 PostgreSQL 缓存和操作系统缓存」的估计，不会预留、申请或限制任何内存。值较高时，规划器更相信索引访问的数据能命中缓存；值较低时，顺序扫描相对更有吸引力。

设置时要考虑 shared_buffers、可用于数据库文件的操作系统缓存，以及并发查询之间的竞争。它表达的是规划假设，不是给 PostgreSQL 发内存红包。

三、连接数：能连上，不等于扛得住

max_connections 决定并发连接上限。把它从 100 改到 2000，看起来像解决了「连接已满」，实际上可能只是允许更多请求同时争夺 CPU、缓存、锁和 I/O。PostgreSQL 还会按照这个值调整部分资源，连接本身也有进程和内存成本。

真正应该问的是：业务需要多少同时执行查询的连接，而不是有多少应用实例。大多数 Web 请求都不是持续访问数据库，连接池可以让大量客户端复用有限的数据库连接。连接池也不能无限加大：若数据库只有 16 个 CPU 核心，把池设成 1000 往往不会产生 1000 份吞吐，只会产生更长的队列和更剧烈的上下文切换。

一个可操作的顺序是：

1. 观察峰值活跃连接，而非只看已建立连接
2. 分离在线请求、后台任务和管理连接的配额
3. 为查询和事务设置合理超时，避免失控任务长期占坑
4. 保留 reserved_connections 与超级用户槽位，确保事故时仍能进入实例
5. 通过 PgBouncer 等连接池控制后端并发，再根据吞吐和等待逐步调整

别把连接池当成性能魔法。事务池模式可能与会话级状态、临时表、部分预处理语句用法产生冲突。选型之前要确认应用依赖了哪些会话语义。

四、WAL 与检查点：性能和恢复时间在拔河

PostgreSQL 通过 WAL（预写式日志，Write-Ahead Log）保证崩溃恢复与复制。事务修改的数据页可以稍后落盘，但相应 WAL 必须先持久化。检查点则建立一个新的恢复起点，并推动脏页写回存储。

max_wal_size 与 checkpoint_timeout

检查点通常在达到 checkpoint_timeout，或 WAL 接近 max_wal_size 时触发，以先到者为准。max_wal_size 是软限制，在归档失败、复制槽积压等情况下 WAL 仍可能超过它。

检查点太频繁，会反复写脏页，并因 full_page_writes 在每次检查点后第一次修改页面时产生更多 WAL，形成周期性的 I/O 尖峰。增大 max_wal_size、适当延长 checkpoint_timeout 可以让写入更平滑，却也意味着崩溃后可能需要重放更多 WAL，占用更多磁盘，并拉长恢复时间。

checkpoint_completion_target 用于把检查点写入摊在整个间隔内，PostgreSQL 18 默认值 0.9 已经相当克制。盲目调低会让写入更集中，通常不是好主意。

如果日志频繁出现检查点过密的警告，应同时查看 pg_stat_checkpointer、WAL 生成速率和磁盘延迟，再决定是否扩大 max_wal_size。只关掉 checkpoint_warning，属于把烟雾报警器拆了，然后宣布厨房很安全。

synchronous_commit：别误解成关掉一致性

将 synchronous_commit 设为 off，事务提交可以在 WAL 真正刷入持久存储前向客户端返回成功。数据库崩溃后，最近一小段已返回成功的事务可能丢失，但数据库不会因此出现物理损坏。它适合能够接受少量数据回退的日志或批量任务，不适合支付、余额和必须兑现的业务承诺。

这里换取的是提交延迟，支付的是持久性窗口。必须由业务明确接受，不能由 DBA 觉得「应该没事」就全局打开。更好的方式往往是对特定事务或角色局部设置。

fsync、full_page_writes 等参数则属于数据安全底座。为了跑分关闭它们，可能得到一张漂亮的图和一份无法恢复的数据库。除非是随时可重建的临时实例，否则别自作聪明。

五、Autovacuum：不是清理垃圾那么简单

PostgreSQL 使用 MVCC（多版本并发控制）。UPDATE 通常会产生新版本，旧版本在不再对任何事务可见后成为死元组。普通 VACUUM 标记空间以供后续复用，更新可见性映射，并参与防止事务 ID 回卷；ANALYZE 则收集统计信息，帮助规划器估算行数和数据分布。

所以 autovacuum 既关系空间，也关系查询计划和数据库能否继续安全接受事务。关闭 autovacuum 几乎从来不是调优。 它偶尔占用 I/O，只说明需要按负载调度和限速，不代表这项工作可以消失。

触发清理的基本思路是「固定阈值 + 表行数 × 比例」。对一张十亿行的大表，默认比例可能意味着积累大量变化后才触发。热点大表常需要按表降低 autovacuum_vacuum_scale_factor，并配合合适的固定阈值；小表则未必需要同样激进。

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

上面的数字只是语法示例，不是推荐值。真正的设置要根据表大小、更新率、清理耗时、死元组增长和存储能力计算。可以从这些视图开始观察：

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count,
       autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

还要排查长期事务。只要一个老快照仍可能看见旧版本，VACUUM 就不能回收它们。此时继续增加 autovacuum worker，像是派更多清洁工去一间被人从里面顶住门的屋子。idle_in_transaction_session_timeout 可以限制长时间「事务内空闲」的会话，但启用前必须确认应用行为。

普通 VACUUM 通常不会把空间归还操作系统，而是留给表内复用。VACUUM FULL 会重写表并取得更强的锁，不适合作为每日保健品。只有在确有必要物理收缩、能够安排锁和额外磁盘空间时，才应考虑它或在线重组方案。

六、规划器参数：不要逼它走索引

遇到慢查询，有人第一反应是：为什么不用索引？然后执行 SET enable_seqscan = off，查询果然走了索引，案件告破。

可顺序扫描不一定慢。需要读取表中大部分行时，连续扫描可能比大量随机索引访问更便宜。真正值得检查的是：规划器估算的行数与实际行数差多大，统计信息是否准确，数据分布是否存在相关性，索引是否匹配筛选、连接和排序方式。

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT ...;

ANALYZE 会真实执行语句。对 UPDATE、DELETE、INSERT 和会产生外部影响的函数，不能在生产库里随手运行。必要时使用只读副本、事务回滚或先执行不带 ANALYZE 的 EXPLAIN。即使是 SELECT，它也会消耗真实资源。

阅读计划时先看几件事：

• rows 估算与 actual rows 是否相差几个数量级
• 某节点执行了多少次 loops，单次不慢是否被循环放大
• BUFFERS 显示的命中与读取是否符合预期
• 排序或哈希是否出现磁盘批次、临时文件
• 时间耗在扫描、连接、排序，还是等待锁和 I/O

估算失真时，先 ANALYZE，再评估列的统计目标和扩展统计信息。若两列高度相关，规划器单独统计它们后可能仍会误判组合条件。

random_page_cost 和 seq_page_cost 表示规划器对随机、顺序页面读取的相对成本。SSD 的随机访问差距通常比机械盘小，但不能因为「用了 SSD」就机械地复制某个数。数据是否常驻缓存、存储是否为网络块设备、并发下延迟是否稳定，都会改变真实代价。成本参数应该用于刻画实测硬件和负载，而不是强迫某条 SQL 选择你喜欢的计划。

同理，effective_io_concurrency、PostgreSQL 18 的异步 I/O 相关设置也与内核、文件系统和存储栈有关。默认值已经随版本演进，不应把十年前的调优清单直接搬来。升级大版本后要重新做基线，别让历史配置压过新版本更合理的默认值。

七、先建立观测，再谈参数

没有慢查询清单，调优就只能靠猜。pg_stat_statements 能按规范化 SQL 聚合执行次数、总耗时、平均耗时、I/O 和 WAL 等信息。它需要加入 shared_preload_libraries，并在数据库中创建扩展：

shared_preload_libraries = 'pg_stat_statements'
compute_query_id = 'auto'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       total_exec_time,
       mean_exec_time,
       rows,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

按总耗时排序能找到吞吐杀手，按平均或最大耗时排序更容易看到单次长查询。两者都要看：每次 10 毫秒却执行一亿次的 SQL，可能比偶尔运行 2 秒的后台报表更值得优化。

日志侧可以设置 log_min_duration_statement 捕获慢语句；高流量环境可使用采样，避免日志 I/O 和敏感参数泄露。auto_explain 能自动记录慢语句的执行计划，但启用 log_analyze、逐节点计时等能力会产生额外开销，必须小流量验证并控制采样率。

监控至少要覆盖：

参数调优的价值不在「改过多少项」，而在一条完整证据链：观察到什么，提出什么假设，修改什么，哪个指标改善了，有没有新副作用。

八、一套克制的调优流程

如果接手一个完全陌生的实例，我会按下面的顺序推进。

第一步，确认约束。记录 PostgreSQL 版本、CPU、内存限制、存储类型、数据规模、读写比例、峰值并发、复制与备份方式，以及业务对延迟、数据持久性和恢复时间的要求。没有这些，任何推荐值都缺少分母。

第二步，收集稳定基线。至少覆盖一个完整业务周期，保留峰值和长尾，而不只截取凌晨的安静十分钟。确认监控本身没有制造不可接受的开销。

第三步，按总成本排序问题。先修执行最频繁、总耗时最高或直接影响核心链路的 SQL；再处理锁、连接和清理；最后才考虑成本参数等全局旋钮。一个正确索引或一次批量访问改写，经常比修改二十个配置更有效。

第四步，一次改变一组有因果关系的参数。扩大 shared_buffers 时观察内存与检查点；调整 autovacuum 时观察死元组、I/O 和完成时间。若同时魔改三十项，即使性能提升，也不知道是谁立了功，更不知道出问题该退哪一个。

第五步，写下回滚条件。比如 P99 延迟、磁盘使用、WAL 积压或 OOM 风险超过阈值就恢复原值。配置纳入版本控制，记录修改理由、负责人和验证日期。参数不是祖传秘方，硬件、数据和版本变化后应该重新评估。

最后，做故障演练。调大 WAL 与检查点间隔后，恢复时间是否仍满足目标？提高连接并发后，数据库过载时应用能否限流？调优只看正常路径，事故迟早会替你补考。

九、那些我不会直接给出的「推荐值」

我不会给出一份适用于所有 PostgreSQL 的配置模板。不是故作高深，而是这些关键问题不能被一个数字抹平：

• 同样是 64 GB 内存，数据库是否独占机器？容器上限是多少？
• 同样是 SSD，本地 NVMe 与高延迟云盘的随机读取代价一样吗？
• 同样有 500 个客户端，是 20 条活跃 SQL，还是 500 条同时计算？
• 同样是写多，能否丢失最近几十毫秒的数据？崩溃恢复允许多久？
• 同样是一亿行表，是只追加，还是持续更新同一批热点行？

可以使用 25% 内存作为 shared_buffers 的测试起点，可以参考官方默认值，也可以借助配置生成器形成第一版草案。但起点不是结论，模板不能替代测量。

PostgreSQL 的默认配置需要适应广泛环境，因此不会替你的具体业务做到最优；但它通常比一份来源不明、年代久远的「神级配置」安全。真正成熟的调优，不是把每个参数都改一遍，而是知道哪些参数应该保持默认。

说到底，数据库性能不是配置文件里某一行的功劳。模式设计、索引、SQL、事务边界、连接管理、维护、硬件和业务流量共同决定了结果。参数能放大正确设计，也能放大错误设计。

先找到瓶颈，再动旋钮。改完测量，随时能退。调优做到这个份上，虽然没有一键起飞那么刺激，却更像工程，而不是玄学。