PG实战笔记 | 08 PostgreSQL性能基础调优参数配置
上一篇我们聊了编译选项。这一篇，我们进入安装后真正影响数据库性能的核心环节：参数调优。
shared_buffers、work_mem、maintenance_work_mem、max_connections、wal_level——这五个参数几乎决定了 PostgreSQL 的默认性能上限。但调参不是“越大越好”，每一个参数背后都有代价和风险。
一、调参的基本逻辑：先理解，再动手
PostgreSQL 有几百个配置参数，但真正需要安装后立即调整的并不多。以下五个参数是社区和云厂商公认的“必调项”。
但在动手之前，先记住一条原则：调参的依据是你的工作负载，不是别人的推荐值。一个 OLTP 系统和 OLAP 系统的 optimal 配置完全不同。下面我们逐一来拆。
二、shared_buffers：数据库的“私有缓存”
shared_buffers决定 PostgreSQL 用于共享内存缓冲区的内存量，默认值通常是 128MB。它缓存表数据和索引页，是数据库最重要的内存参数之一。
该设多少？
PostgreSQL 官方文档给出的指导是：对于 1GB 或以上内存的专用数据库服务器，合理起始值是系统内存的 25%。对于某些工作负载，更大的设置也可能有效，但由于 PostgreSQL 也依赖操作系统缓存，将超过 40% 的 RAM 分配给 shared_buffers 不可能比分配较少量的 RAM 效果更好。
这背后的逻辑是：PostgreSQL 采用“双缓存”架构。数据既存在于 shared_buffers，也存在于操作系统的文件系统缓存中。如果 shared_buffers 过大，操作系统缓存会被挤压，反而降低整体效率。较大的 shared_buffers 设置通常还需要相应增加max_wal_size，以分散写操作。
生产建议
- 32GB 内存的服务器，shared_buffers 建议设为8GB（25%）；
- 使用
huge_pages = try减少页表开销，Linux 上可提升性能； - 该参数只能在服务器启动时设置，修改后需要重启。
三、work_mem：排序与哈希的“临时工作台”
work_mem控制每个查询用于排序和哈希操作的内存量，默认值仅为 4MB。
最容易被误解的参数
work_mem 最大的认知陷阱是：它按“每个排序/哈希操作”分配，而不是“每个查询”或“每个连接”。一个复杂查询可能同时包含多个 sort 或 hash 操作，每个操作都可以用到完整的 work_mem 额度。这意味着，一个查询实际可能消耗work_mem × N（N 为操作数）的内存。
更危险的是并发场景。如果 100 个连接同时执行包含 3 个哈希操作的查询，总内存需求可能达到100 × 3 × work_mem。这就是为什么把 work_mem 调大“往往不是优化，而是把单点慢 SQL 升级成整库级故障”。
该设多少？
一个常用的初始估算公式是：work_mem = 总内存 × 0.25 / max_connections。例如 16GB 内存、50 个连接，work_mem 约 80MB。但实际值应该根据查询复杂度和并发量调整。
更安全的做法是保持全局 work_mem 保守（如 16MB 或 32MB），在确实需要大内存的特定查询中用SET LOCAL work_mem = '256MB'临时提升。
风险提示
work_mem 设置过高且并发量大时，Linux OOM killer 可能直接杀掉 PostgreSQL 进程，导致整个数据库崩溃。如果必须设置较激进的值，可以考虑将vm.overcommit_memory设为 2，防止内核过度承诺内存，但这需要权衡 swap 和 sidecar 的影响。
四、maintenance_work_mem：维护操作的“加速器”
maintenance_work_mem控制 VACUUM、CREATE INDEX、ALTER TABLE 等维护操作可用的内存上限。与 work_mem 不同，它影响的是维护任务，而不是用户查询。
该设多少？
默认值因环境而异。AWS RDS 上默认 64MB，Azure 根据实例内存计算。一般建议设置为512MB 到 1GB，内存充足的服务器可以更高。
一个重要的限制：VACUUM 收集死元组标识符有内置的 1GB 上限，即使 maintenance_work_mem 设得更大，VACUUM 最多也只能用到 1GB。但 CREATE INDEX 没有这个限制，对大表建索引时，提高 maintenance_work_mem 效果显著。
并行注意事项
maintenance_work_mem 是每个维护操作的上限。如果有多个并行维护任务（如 autovacuum worker 并发运行），总内存消耗会成倍增加。Autovacuum 可以使用独立的autovacuum_work_mem来隔离配置。
五、max_connections：连接的“天花板”
max_connections决定数据库允许的最大并发连接数，默认值通常是 100。
为什么不应该盲目上调？
每个 PostgreSQL 连接对应一个独立的后端进程，消耗真实的内存（通常 4-10MB 不等）和 CPU 调度开销。把 max_connections 从 100 提到 1000，意味着数据库可能同时运行 1000 个进程，内存和调度压力剧增。
生产环境的最佳实践是：数据库侧的 max_connections 保持合理值（通常 100-200），在应用和数据库之间加一层连接池（如 PgBouncer）。连接池复用少量真实连接，服务大量应用请求，避免连接风暴。
连接风暴的典型场景
一个常见的现象是：应用在短时间内打开大量连接，即使配置了数据库级别的连接限制，全局 max_connections 仍可能被短暂打满，因为新连接在确定目标数据库之前就已经计入全局计数。这种情况下，连接池是根本解决方案。
六、wal_level：复制与恢复的“详细程度”
wal_level决定写入 WAL（预写日志）的信息量，只能在服务器启动时设置。
| 级别 | 写入的信息 | 适用场景 |
|---|---|---|
minimal | 仅崩溃恢复所需 | 无需复制或归档的临时数据库 |
replica | 支持 WAL 归档和流复制（默认） | 生产环境标准配置 |
logical | 额外支持逻辑解码 | 逻辑复制、CDC |
从minimal改为minimal以上级别，会显著增加 WAL 写入量，尤其在使用REPLICA IDENTITY FULL的表上执行大量 UPDATE/DELETE 时。
生产环境推荐使用replica，它是 WAL 归档、流复制和只读备库的基础。如果你需要逻辑复制或变更数据捕获（CDC），则设为logical。minimal仅适合完全不需要恢复和复制的场景，因为切换回replica后，之前基于minimal的备份将无法用于 PITR。
七、参数调整的依据与风险
回到核心问题：调参的依据是什么？
依据是观测数据，不是猜测。在调整任何参数之前，先收集以下信息：
- 当前内存使用情况（
free -h、pg_stat_bgwriter）； - 缓存命中率（
pg_stat_database中的blks_hit与blks_read比值）； - 连接数峰值（
pg_stat_activity）； - 排序/哈希操作是否频繁溢写到磁盘（慢查询日志中的
Sort Method: external merge）。
风险在于：每个参数都有其“隐藏成本”。
- shared_buffers 过大 → 挤压 OS 缓存，写放大加剧；
- work_mem 过大 → 并发时 OOM 风险，整库崩溃；
- maintenance_work_mem 过大 → 并发维护任务耗尽内存；
- max_connections 过大 → 进程调度开销，内存碎片；
- wal_level 过高 → WAL 写入放大，I/O 压力增加。
一句话：调参是在“性能”和“稳定性”之间找平衡，而不是单纯追求“更大更快”。
八、总结
| 参数 | 作用 | 初始建议 | 关键风险 |
|---|---|---|---|
| shared_buffers | 共享缓存 | 内存的 25% | 超过 40% 收益递减，挤压 OS 缓存 |
| work_mem | 排序/哈希 | 总内存 × 0.25 / 连接数，保持保守 | 并发时 OOM，整库崩溃 |
| maintenance_work_mem | 维护操作 | 512MB–1GB | 并发维护任务内存耗尽 |
| max_connections | 最大连接 | 100–200，配合连接池 | 盲目上调导致调度和内存压力 |
| wal_level | WAL 详细度 | replica | logical增加 WAL 写入量 |
下一篇，我们聊聊psql客户端的那些高效操作——不只是\d和\l，还有\timing、\x、\watch、\copy等真正提升日常工作效率的技巧。
思考题：
你在生产环境中调整过哪些 PostgreSQL 参数？有没有遇到过因为 work_mem 设置过高导致 OOM，或者 shared_buffers 调大后性能反而下降的情况？欢迎在评论区分享你的调参经历。
