如何优化CentOS的PostgreSQL

下面从系统层、PostgreSQL 配置层、SQL/索引层、运维层四个维度，系统性地说明如何优化 CentOS 上的 PostgreSQL，适用于 OLTP / OLAP / 混合场景。

一、系统层优化（CentOS）

1. 内核参数（非常重要）

编辑 /etc/sysctl.conf：

# 内存相关
vm.swappiness = 1
vm.overcommit_memory = 2
vm.overcommit_ratio = 80

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

# 共享内存
kernel.shmmax = 68719476736
kernel.shmall = 16777216

生效：

sysctl -p

2. 关闭透明大页（THP）

PostgreSQL 不推荐 THP：

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

写入开机：

vi /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local

3. 文件系统 & 磁盘 IO

✅ 推荐：

文件系统：XFS
挂载参数：

noatime,nodiratime,data=writeback

示例：

mount -o noatime,nodiratime /dev/sdb /var/lib/pgsql

4. 关闭 SELinux（或设为 permissive）

setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=permissive/' /etc/selinux/config

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

假设服务器内存 16G，可按比例调整

1. 内存配置

shared_buffers = 4GB              # 25% 内存
work_mem = 32MB                   # 每个排序/哈希操作
maintenance_work_mem = 512MB
effective_cache_size = 12GB       # 给优化器看的

2. WAL & 写入优化

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

3. 连接 & 并发

max_connections = 200
superuser_reserved_connections = 5

⚠️ 高并发建议：

使用 pgbouncer
不建议 max_connections 超过 500

4. 查询优化器

random_page_cost = 1.1
seq_page_cost = 1.0

（SSD 场景）

5. 日志 & 审计

log_min_duration_statement = 500ms
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'

三、SQL & 索引优化（最关键）

1. 索引优化

✅ 原则：

WHERE / JOIN / ORDER BY 字段建索引
避免重复索引
使用 部分索引 / 复合索引

-- 复合索引
CREATE INDEX idx_user_status ON users(status, created_at);

-- 部分索引
CREATE INDEX idx_active_user ON users(id) WHERE status='active';

2. 避免常见慢 SQL

❌ 不推荐：

SELECT * FROM orders;
LIKE '%abc%'

✅ 推荐：

SELECT id, name FROM orders WHERE status = 'paid';

3. 使用 EXPLAIN 分析

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

关注：

Seq Scan（全表扫描）
实际时间 vs 估算时间
Buffer 命中率

四、表 & 数据库维护

1. 定期 VACUUM & ANALYZE

VACUUM ANALYZE;

或自动：

autovacuum = on
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

2. 大表分区（PostgreSQL 10+）

CREATE TABLE orders (
  id serial,
  created_at date
) PARTITION BY RANGE (created_at);

适合：

日志表
历史数据表

五、连接池 & 高并发

pgbouncer 推荐配置

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50

六、监控工具（强烈建议）

工具	用途
pg_stat_statements	SQL 性能
pg_top	实时
Prometheus + Grafana	长期
pgBadger	日志分析

启用：

shared_preload_libraries = 'pg_stat_statements'

七、快速优化清单（Checklist）

✅ 关闭 THP
✅ 调整 shared_buffers / work_mem
✅ SSD + XFS
✅ 使用连接池
✅ 慢 SQL 分析
✅ 定期 VACUUM

八、我可以继续帮你

你可以直接告诉我：

✅ 服务器内存 & CPU
✅ PostgreSQL 版本
✅ 是 OLTP / 报表 / 高并发
✅ 当前慢 SQL

我可以给你 一份精确参数配置表 或 针对某条 SQL 的优化方案。