PostgreSQL在CentOS如何调优

在 CentOS 上部署 PostgreSQL 时，调优通常分为 系统层、数据库参数层、SQL 与应用层、监控与维护 四个方面。下面给你一套较完整、可落地的调优思路（适用于 PostgreSQL 12/13/14/15，CentOS 7/8/Stream）。

一、操作系统层（CentOS）调优

1. 内核参数（sysctl）

编辑 /etc/sysctl.conf 或 /etc/sysctl.d/99-postgres.conf：

# 内存与交换
vm.swappiness = 1
vm.overcommit_memory = 2
vm.overcommit_ratio = 80

# 共享内存
kernel.shmmax = 17179869184   # 16GB，按实际内存调整
kernel.shmall = 4194304

# 网络
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.ip_local_port_range = 1024 65535

# 文件句柄
fs.file-max = 2097152

应用：

sysctl -p

2. 文件描述符限制

编辑 /etc/security/limits.conf：

postgres soft nofile 65535
postgres hard nofile 65535

CentOS 8 / Stream 还要确保 systemd 生效：

systemctl edit postgresql

添加：

[Service]
LimitNOFILE=65535

3. 文件系统与磁盘

4. 关闭透明大页（THP）

echo never > /sys/kernel/mm/transparent_hugepage/enabled

加入开机自动执行。

二、PostgreSQL 核心参数调优（postgresql.conf）

1. 内存相关（最重要）

# 物理内存 的 25%~40%
shared_buffers = 4GB

# 用于排序、哈希等
work_mem = 16MB

# 维护操作（VACUUM、CREATE INDEX）
maintenance_work_mem = 512MB

# 大页支持（可选）
huge_pages = try

经验值（16GB 内存服务器）

参数	建议
shared_buffers	4GB
work_mem	8–32MB
maintenance_work_mem	512MB

⚠️ work_mem 不是全局，而是 每个操作 × 连接数，不要设太大。

2. WAL 与写入性能

wal_buffers = 16MB
checkpoint_timeout = 15min
max_wal_size = 2GB
min_wal_size = 512MB

高写入场景：

synchronous_commit = off

⚠️ 可能丢 1 秒内的事务（不推荐金融系统）

3. 连接数控制

max_connections = 200

强烈建议：

应用使用 连接池（pgbouncer）
实际 DB 连接控制在 100 以内

4. 并行查询（多核 CPU）

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

5. 统计与自动清理

track_io_timing = on
autovacuum = on
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

三、SQL 与应用层优化

1. 索引优化

避免全表扫描
联合索引顺序要符合查询条件
定期清理无用索引

SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0;

2. 避免常见坑

不要 SELECT *
不要在大表上 OFFSET 分页
使用 PREPARE / 绑定变量
避免 WHERE 中对字段使用函数

❌：

WHERE DATE(create_time) = '2024-01-01'

✅：

WHERE create_time >= '2024-01-01'
AND create_time < '2024-01-02'

四、连接池（强烈推荐）

pgbouncer 示例

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50

五、监控与维护

1. 查看性能瓶颈

-- 慢查询
SELECT * FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

启用：

shared_preload_libraries = 'pg_stat_statements'

2. 定期维护

vacuumdb -az

或设置合理的 autovacuum。

六、推荐配置速查（16G 内存示例）

shared_buffers = 4GB
work_mem = 16MB
maintenance_work_mem = 512MB
max_connections = 200
checkpoint_timeout = 15min
max_wal_size = 2GB
autovacuum = on

七、调优顺序建议

先 系统 + 基础参数
再 连接池
再 SQL / 索引
最后 参数微调

如果你愿意，我可以：

✅ 根据你 内存 / CPU / 磁盘类型 给出 精确参数
✅ 帮你分析 pg_stat_statements 的慢 SQL
✅ 给你一套 高并发 / 高写入 / 只读从库 的专用配置

你可以直接贴：

服务器配置 + PostgreSQL 版本 + 主要业务类型