Debian PostgreSQL性能优化技巧

以下是针对 Debian 系统上 PostgreSQL 性能优化的详细技巧，分步骤说明：

1. 硬件与系统层优化

内存与磁盘
- 内存分配：确保系统有足够内存，避免频繁交换（Swap）。建议 PostgreSQL 专用服务器内存 ≥ 4GB。
- 磁盘优化：
  - 使用 SSD 替代机械硬盘。
  - 将 WAL 日志 (pg_wal) 和数据目录分离到不同磁盘。
  - 文件系统推荐 XFS 或 ext4，挂载时添加优化选项（如 noatime,data=writeback）：
```
# /etc/fstab 示例
/dev/sdb1 /var/lib/postgresql xfs defaults,noatime,nodiratime 0 0
```

内核参数调整
修改 /etc/sysctl.conf，优化内存与网络：

# 共享内存大小（建议为系统内存的70%~80%）
kernel.shmmax = 17179869184  # 16GB 示例
kernel.shmall = 4194304       # 计算方式: shmmax / PAGE_SIZE (通常 4096)

# 虚拟内存与网络
vm.swappiness = 1            # 减少交换
vm.dirty_background_ratio = 1
vm.dirty_ratio = 3
net.core.somaxconn = 4096

执行 sysctl -p 生效。

2. PostgreSQL 配置调整

配置文件路径：/etc/postgresql/{版本}/main/postgresql.conf

核心参数

shared_buffers = 4GB          # 建议系统内存的25%~40%
work_mem = 16MB               # 每个查询操作的内存，按并发数调整
maintenance_work_mem = 1GB    # VACUUM 等维护任务的内存
effective_cache_size = 12GB   # 系统内存的50%~75%

max_connections = 100         # 避免过高，建议配合连接池（如pgbouncer）
wal_buffers = 16MB            # 默认 3% shared_buffers，通常无需修改

# 并行查询优化（多核CPU）
max_parallel_workers_per_gather = 4   # 并行线程数
max_worker_processes = 8

WAL 与检查点

wal_level = replica           # 非高可用环境可设为 minimal
checkpoint_timeout = 15min    # 延长检查点间隔
checkpoint_completion_target = 0.9  # 平滑写入

自动维护

autovacuum = on
autovacuum_max_workers = 4    # 根据负载调整
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

3. 查询与索引优化

分析慢查询
- 启用慢查询日志：
```
log_min_duration_statement = 1000  # 记录超过1秒的查询
```
- 使用 EXPLAIN ANALYZE 分析执行计划。
索引策略
- 对高频查询的 WHERE 和 JOIN 字段创建索引。
- 定期重建索引：
```
REINDEX INDEX index_name;
```

统计信息更新

ANALYZE table_name;  -- 手动更新统计信息

4. 连接池与扩展工具

使用 PgBouncer
```
sudo apt install pgbouncer  # Debian 安装
```
配置连接池模式（pool_mode=transaction）并限制连接数。

安装性能监控扩展

CREATE EXTENSION pg_stat_statements;  -- 追踪SQL性能

5. 定期维护任务

手动清理与重建

VACUUM FULL ANALYZE table_name;  -- 回收空间并更新统计信息

监控工具
- 使用 pg_top 或 pg_activity 实时监控。
- 部署 Prometheus + Grafana 可视化监控。

6. Debian 特有优化

软件版本管理

默认仓库可能版本较低，可通过官方仓库升级：

sudo apt install curl ca-certificates
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install postgresql-15  # 示例安装PG 15