1. 概述

PostgreSQL 默认配置非常保守（为了能在小机器上启动），生产环境几乎必须调优。本篇按"内存 → WAL/检查点 → 并发 → autovacuum → 规划器"梳理最核心的参数，给出经验公式。

对比 MySQL：MySQL 调优的核心是 innodb_buffer_pool_size（通常设为 60-70% 内存，一块大缓冲池）；PostgreSQL 的内存模型不同——它依赖操作系统的页缓存，shared_buffers 只设中等大小（约 25% 内存），把剩余内存留给 OS 缓存。这是两者调优哲学最大的区别。

1.1 参数管理

-- 查看参数
SHOW shared_buffers;
SELECT name, setting, unit, context FROM pg_settings WHERE name = 'work_mem';

-- 修改（PG 支持在线改，部分需重启）
ALTER SYSTEM SET work_mem = '32MB';   -- 写入 postgresql.auto.conf
SELECT pg_reload_conf();              -- 重载（对需重启的参数无效）

context 列决定改后是否需重启：postmaster=需重启，sighup=reload 即可，user=会话级可随时改。

2. 内存参数（最重要）

2.1 shared_buffers

PostgreSQL 自己的共享缓冲池，缓存数据页。

• 经验值：物理内存的 25%（如 32GB 内存 → 8GB）。
• 不宜过大：PG 还依赖 OS 页缓存，设太高反而与 OS 缓存重复浪费，且加大检查点刷盘压力。

shared_buffers = 8GB

2.2 effective_cache_size

告诉优化器"整个系统大约有多少内存可用于缓存"（shared_buffers + OS 缓存的估计值）。它不实际分配内存，只影响优化器判断"索引扫描 vs 全表扫描"的成本。

• 经验值：物理内存的 50-75%。

effective_cache_size = 24GB

2.3 work_mem（易踩坑）

单个查询操作（排序 Sort、哈希 Hash Join、聚合）可用的内存。超出则用磁盘临时文件（慢）。

• ⚠️ 这是每操作、每连接的量！一个复杂查询可能有多个排序/哈希，一个高并发场景 = work_mem × 操作数 × 并发连接数，设太大易 OOM。
• 经验起点： (可用内存 - shared_buffers) / (max_connections × 2~3)，常见 16-64MB。
• 技巧：全局设小，对个别大查询会话级临时调大 SET work_mem='256MB'。

work_mem = 32MB

2.4 maintenance_work_mem

VACUUM、CREATE INDEX、ALTER TABLE 等维护操作的内存，可设较大加速这些操作。

maintenance_work_mem = 1GB

3. WAL 与检查点

详见 WAL与检查点机制，调优要点：

wal_buffers = 16MB
max_wal_size = 4GB                    # 调大→检查点更少→吞吐更好，代价是恢复更慢
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9    # 几乎必调：把刷盘摊平避免IO尖峰

4. 并发连接

max_connections = 200                 # 不宜过大！每连接是一个进程，开销不小

• PostgreSQL 每个连接是一个独立进程（不是线程），连接数过多会消耗大量内存和上下文切换。
• 强烈建议前面加连接池（PgBouncer），而不是一味调大 max_connections。详见 连接池与连接管理。
• 经验：max_connections 控制在 100-300，通过连接池复用。

对比 MySQL：MySQL 是线程模型，单连接开销比 PG 进程模型小，所以 MySQL 能扛更多直连；PG 更依赖连接池。这也是 PG 面试常问的架构差异。

5. autovacuum

详见 VACUUM与表膨胀：

autovacuum = on                       # 生产绝不关闭
autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 2000   # 默认较保守，调大让清理更快
# 大表单独降低触发阈值:
# ALTER TABLE big SET (autovacuum_vacuum_scale_factor = 0.02);

6. 并行查询

PG 9.6+ 支持并行执行（多个 worker 并行扫描/聚合大表）。

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4   # 单个查询最多用几个并行worker

OLAP/大表聚合场景收益明显；纯 OLTP 短查询用处不大。

7. 规划器/成本参数

random_page_cost = 1.1     # SSD 上从默认4.0调到1.1！让优化器更愿意用索引扫描
effective_io_concurrency = 200   # SSD 可调高，机械盘保持较低

random_page_cost 是 SSD 环境最值得调的参数之一：默认值 4.0 假设机械硬盘随机 IO 很贵，SSD 上应降到 1.1 左右，否则优化器可能错误地偏向全表扫描而不用索引。

8. 一个 32GB 内存 OLTP 服务器的示例配置

shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 32MB
maintenance_work_mem = 1GB
max_connections = 200
max_wal_size = 4GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
autovacuum_vacuum_cost_limit = 2000

工具推荐：PGTune 可根据机器规格和负载类型自动生成推荐配置，作为起点很方便。

9. 面试高频问答

Q1：PostgreSQL 和 MySQL 的内存调优有什么本质区别？ MySQL 靠一块大 innodb_buffer_pool（60-70% 内存）；PG 的 shared_buffers 只设约 25%，其余内存交给操作系统页缓存，effective_cache_size 告知优化器总可用缓存。PG 更依赖 OS 缓存。

Q2：work_mem 设置要注意什么？ 它是每操作每连接的内存，不是全局的。总消耗 ≈ work_mem × 并发 × 每查询操作数，设太大会 OOM。建议全局设小，大查询会话级临时调大。

Q3：为什么 PostgreSQL 建议用连接池？ PG 每个连接是一个独立进程，连接数多则内存和切换开销大。用 PgBouncer 复用连接，把 max_connections 控制在合理范围。

Q4：SSD 环境下哪个参数最该调？ random_page_cost，从默认 4.0 降到 1.1 左右，让优化器正确评估 SSD 的随机 IO 成本，避免该用索引却全表扫描。

Q5：max_wal_size 调大有什么影响？ 检查点触发更少、写入吞吐更好，但崩溃恢复要重放更多 WAL、恢复时间变长，需权衡。

10. 下一步

学习 慢查询分析，定位并优化性能瓶颈。