1 min read273 words

postgresql.conf 关键参数调优

PostgreSQL 默认配置面向最小内存的通用场景，生产服务器需要根据硬件和工作负载调整关键参数。本节提供一套系统性的调优框架——不是盲目套公式，而是理解每个参数的作用原理后按场景调整。

调优前：了解你的硬件

-- 查看 PostgreSQL 服务器的当前配置
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN (
'max_connections', 'shared_buffers', 'effective_cache_size',
'work_mem', 'maintenance_work_mem', 'wal_buffers',
'checkpoint_completion_target', 'random_page_cost'
)
ORDER BY name;
-- 查看服务器内存总量（PostgreSQL 视角）
SELECT pg_size_pretty(setting::bigint * 8192) AS shared_buffers_size
FROM pg_settings WHERE name = 'shared_buffers';

# 查看系统资源
free -h          # 总内存
nproc            # CPU 核心数
df -h /var/lib/postgresql  # 磁盘类型
# SSD vs HDD 对 random_page_cost 参数影响很大

内存相关参数

shared_buffers（最重要）

# shared_buffers：PostgreSQL 的缓冲区大小
# 作用：缓存数据页，减少磁盘 I/O
# 规则：设为系统总内存的 25%（不超过 8GB）
# 为什么不设更大？PostgreSQL 还依赖 OS Page Cache
# 8GB 内存的服务器：
shared_buffers = 2GB
# 32GB 内存的服务器：
shared_buffers = 8GB  # 上限约 8GB，更多交给 OS cache
# 修改后需要重启：
# sudo systemctl restart postgresql

effective_cache_size（查询规划器提示）

# effective_cache_size：告诉查询规划器可用的总缓存大小
# 作用：影响规划器是否选择索引扫描（值越大，越倾向于索引）
# 规则：设为 shared_buffers + OS Page Cache（约系统内存的 50-75%）
# 注意：这只是提示，不分配实际内存
# 8GB 内存服务器：
effective_cache_size = 6GB
# 32GB 内存服务器：
effective_cache_size = 24GB

work_mem（排序和哈希操作）

# work_mem：每个排序/哈希操作可用的内存
# 作用：避免排序落盘（Disk Sort），提升 ORDER BY、GROUP BY、JOIN 性能
# 危险：每个连接每个操作都可能使用 work_mem
#       500 连接 × 10 个并发排序 × 16MB = 80GB！！
# 规则：(总内存 × 25%) / (max_connections × 平均并发查询数)
# 保守设置（高并发 OLTP）：
work_mem = 4MB     # 默认值，避免内存溢出
# 中等设置（普通 Web 应用）：
work_mem = 16MB    # 大多数应用的合理值
# 激进设置（低并发分析查询）：
work_mem = 256MB   # 报表/分析场景，连接数少
# 按会话临时调整（最安全）：
SET work_mem = '64MB';  -- 只对当前会话生效

maintenance_work_mem（维护操作）

# maintenance_work_mem：VACUUM、CREATE INDEX、ALTER TABLE 等维护操作的内存
# 规则：设为系统内存的 5-10%（上限 2GB 左右）
# 8GB 内存：
maintenance_work_mem = 512MB
# 32GB 内存：
maintenance_work_mem = 2GB

WAL 与检查点参数

# wal_buffers：WAL 缓冲区大小
# 规则：通常 16MB-64MB 就够了
wal_buffers = 16MB  # auto（默认）通常是 1/32 of shared_buffers，最大 16MB
# checkpoint_completion_target：检查点期间刷盘的时间比例
# 默认 0.9（在下一个检查点开始前完成 90% 的时间内完成刷盘）
# 越高 = 刷盘越平滑，I/O 越均匀
checkpoint_completion_target = 0.9
# max_wal_size：触发检查点的 WAL 大小阈值
# 增大可以减少检查点频率（写入密集型应用）
max_wal_size = 4GB  # 默认 1GB，写密集型应用可调大
# min_wal_size：最小保留 WAL 大小
min_wal_size = 1GB  # 默认 80MB

并行查询参数

# max_parallel_workers_per_gather：每个查询最多用几个并行 worker
# 规则：不超过 CPU 核心数的 1/2
max_parallel_workers_per_gather = 4  # 8 核服务器
# max_parallel_workers：全局并行 worker 总数
max_parallel_workers = 8
# parallel_tuple_cost：并行扫描的单元代价（影响规划器是否启用并行）
# 默认 0.1；降低可让规划器更积极地使用并行
parallel_tuple_cost = 0.1

I/O 代价参数（SSD vs HDD）

# random_page_cost：随机 I/O 的相对代价（影响索引选择）
# 默认 4.0（面向 HDD，随机 I/O 比顺序 I/O 慢 4 倍）
# SSD 上随机/顺序差异小，应降低此值
# HDD（机械硬盘）：
random_page_cost = 4.0  # 默认值
# SSD（固态硬盘）：
random_page_cost = 1.1  # 接近顺序 I/O 代价
# NVMe SSD（超快）：
random_page_cost = 1.0
# 效果：降低 random_page_cost 后，规划器更倾向于使用索引

连接参数

# max_connections：最大连接数
# 注意：PostgreSQL 每个连接是独立进程，连接数不是越多越好
# 规则：使用 pgBouncer 后，max_connections 可以设小
# （pgBouncer 只用 20-50 个实际连接）
# 没有连接池时：
max_connections = 200
# 有 pgBouncer 时：
max_connections = 100  # pgBouncer 的实际连接数 × 2

完整调优配置示例

# 场景：16GB 内存，SSD，8 核，Web 应用（有 pgBouncer）
# 内存
shared_buffers = 4GB                    # 25% of 16GB
effective_cache_size = 12GB             # 75% of 16GB
work_mem = 32MB                         # (16GB × 25%) / (100 × 1.25) ≈ 32MB
maintenance_work_mem = 1GB              # 约 6% of 16GB
# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
# I/O
random_page_cost = 1.1                  # SSD
# 并行
max_parallel_workers_per_gather = 4     # 8 核的一半
max_parallel_workers = 8
# 连接
max_connections = 100                   # 配合 pgBouncer
# 日志
log_min_duration_statement = 1000       # 记录超过 1 秒的查询
log_line_prefix = '%t [%p] %u@%d '
log_checkpoints = on
log_connections = off                   # 高并发下关闭，避免日志刷盘
log_lock_waits = on
deadlock_timeout = 1s

在线调整配置（无需重启）

-- 某些参数可以用 ALTER SYSTEM 修改后 reload（无需重启）
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET log_min_duration_statement = 500;
-- 重新加载配置（无需重启，秒级生效）
SELECT pg_reload_conf();
-- 查看哪些参数需要重启
SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart = true;
-- 参数的 context 决定是否需要重启：
-- postmaster：需要重启（如 shared_buffers、max_connections）
-- sighup：reload 即可（如 work_mem、random_page_cost）
-- user：每个会话可以 SET
SELECT name, context FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'random_page_cost');

PGTune：自动生成配置

https://pgtune.leopard.in.ua/
输入：
- 数据库版本
- 操作系统
- 内存大小
- CPU 核心数
- 连接数
- 工作负载类型（Web、OLTP、DW、Desktop）
自动输出优化后的 postgresql.conf 配置
（作为参考起点，不能完全替代理解）

下一节：pg_stat 视图监控系统——配置调优后，如何知道有没有效果？pg_stat_* 系列视图是 PostgreSQL 内置的实时监控系统，让你看到每张表的缓存命中率、索引使用情况、等待事件等。