Debian PostgreSQL怎样优化

在 Debian 上优化 PostgreSQL，通常可以从系统层面、数据库配置、SQL 与索引、运维监控四个维度来做。下面给你一个实用、可落地的优化清单（以 Debian + PostgreSQL 12/13/14/15 为例）。

一、系统层面优化（Debian）

1. 内存与 Swap

free -h
swapon --show

• 不建议过多依赖 swap
• 服务器建议 至少 2~4GB 内存起步

2. 内核参数（/etc/sysctl.conf）

# 提高共享内存
kernel.shmmax = 17179869184
kernel.shmall = 4194304

# 网络
net.core.somaxconn = 1024
net.ipv4.tcp_tw_reuse = 1

sysctl -p

3. 文件系统

• 推荐使用 ext4 / xfs
• 挂载选项：

noatime,nodiratime

4. 禁用 Transparent Huge Pages（很重要）

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

永久生效（Debian）：

systemctl edit postgresql

加入：

[Service]
Nice=-5

二、PostgreSQL 核心参数优化

配置文件位置：

/etc/postgresql/<version>/main/postgresql.conf

1. 内存相关（最关键）

shared_buffers = 25% ~ 40% 物理内存
work_mem = 16MB ~ 64MB
maintenance_work_mem = 512MB ~ 1GB
effective_cache_size = 50% ~ 75% 物理内存

示例（16GB 内存）：

shared_buffers = 4GB
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 12GB

2. 并发与连接

max_connections = 100 ~ 300

⚠️ 不建议盲目调大，推荐用 pgbouncer

3. WAL 与写入

wal_buffers = 16MB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

4. 日志（非常重要）

logging_collector = on
log_min_duration_statement = 500
log_line_prefix = '%t [%p]: [%l] user=%u,db=%d '

三、SQL 与索引优化

1. 必做

• 合理建索引（B-tree 最常用）
• 复合索引顺序很重要
• 避免 SELECT *
• 避免函数包裹索引字段

2. 慢查询分析

EXPLAIN ANALYZE SELECT ...;

3. 常用优化手段

• 分区表（大表）
• 覆盖索引（INCLUDE）
• 物化视图
• 定时 VACUUM & ANALYZE

四、Debian 特有优化

1. 使用 pgbouncer

apt install pgbouncer

模式推荐：

pool_mode = transaction

2. 自动维护

apt install postgresql-autodoc

或设置 cron：

0 3 * * * psql -c "VACUUM ANALYZE;"

3. 使用 timescaledb / pg_stat_statements

CREATE EXTENSION pg_stat_statements;

查看慢 SQL：

SELECT * FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

五、监控与压测

监控工具

• pg_top
• prometheus + postgres_exporter
• pgAdmin

压测

pgbench -i -s 50 testdb
pgbench -c 32 -j 4 -T 60 testdb

六、典型优化顺序（建议）

1. 先看慢 SQL（EXPLAIN）
2. 再调 shared_buffers / work_mem
3. 加索引
4. 上 pgbouncer
5. 最后才动内核参数

如果你能告诉我：

• PostgreSQL 版本
• 服务器内存
• 业务类型（Web / 报表 / 写入多）

我可以给你一套直接可抄的配置。