Ubuntu 上 PostgreSQL 性能优化实战指南
一 基线评估与监控
- 明确硬件与负载特征：记录 CPU 核数、内存大小、存储类型与RAID级别、典型查询的 QPS/延迟、读写比例与峰值并发。
- 建立监控与基线：启用并定期导出 pg_stat_statements（SQL 热点与成本）、pg_stat_activity（活跃会话与阻塞）、pg_stat_bgwriter（刷脏与检查点）、以及操作系统层面的 iostat -x 1、vmstat 1、sar -d。
- 建立可重复的基准：使用 pgbench 做前后对比（示例：pgbench -i -s 20 初始化；pgbench -c 并发数 -j 线程数 -T 时长 运行），每次只变更一个变量，便于归因。
- 例行维护：在业务低峰执行 VACUUM ANALYZE，对大表按需分区/分片，保持统计信息与可见性映射健康，避免膨胀拖累扫描与索引效率。
二 配置参数优化
- 配置文件位置与生效方式：Ubuntu 常见路径为 /etc/postgresql/{version}/main/postgresql.conf；修改后执行
sudo systemctl reload postgresql 使其生效；用 SHOW parameter; 在 psql 内核对值。
- 内存与优化器参数（按内存总量 T 规划，示例为 T=16GB）：
- shared_buffers：建议 T 的 25%，本例 4GB。过大反而增加检查点与后台写压力。
- effective_cache_size：优化器假设可用缓存，建议 T 的 50%，本例 8GB（不占用实际内存，仅影响成本估算）。
- work_mem：每个排序/哈希操作可用内存，按并发与操作类型精细设置。示例：若峰值排序并发约 10，可先设 128MB，则总排序内存约 10 × 128MB = 1.25GB（还需考虑哈希、去重等），避免超过物理内存与过度换页。
- maintenance_work_mem：维护类操作（VACUUM/创建索引/导入）建议 1–2GB，可显著加速维护任务。
- WAL 与检查点（写密集型关键）：
- min_wal_size：1GB；max_wal_size：4GB（或更高以拉长检查点间隔，降低写放大）。
- checkpoint_completion_target：0.9，平滑刷脏，降低 I/O 抖动。
- wal_buffers：16MB（大多数场景足够，极高并发短事务可适当上调）。
- 并发与连接：
- 避免盲目拉高 max_connections，连接开销大；使用 连接池（PgBouncer 事务级/会话级） 将应用连接收敛到 几十到数百 的稳定连接池规模。
- 其他通用项：
- default_statistics_target：100（提升计划质量，必要时对热点表单独
ALTER TABLE ... SET (autovacuum_vacuum_cost_limit = ...) 等）。
- random_page_cost：在 SSD/NVMe 或一致性内存环境可下调至 1.0–1.1，让优化器更倾向索引扫描。
三 Ubuntu 与存储层面的优化
- 调度器与 I/O 策略：对数据库盘设置 elevator=deadline（GRUB 内核参数），减少抖动、提升顺序写与合并写能力。
- 文件系统与挂载选项：
- 选择 ext4/xfs 等成熟文件系统；PostgreSQL 默认 8KB 块大小，文件系统块大小与对齐一致更优。
- 数据目录建议使用 noatime（减少元数据写入），WAL 与数据可分离到不同物理盘/阵列，WAL 顺序写为主，数据盘承载随机读写。
- 分离与布局：将 WAL（pg_wal） 与 数据（base） 分离到不同磁盘/RAID；若使用电池/超级电容保护的 RAID 控制器，可启用 writeback 策略（需充分评估数据安全边界）。
四 查询与索引优化
- 利用扩展与统计：启用 pg_stat_statements，定位高耗时/高调用 SQL；按需提高相关列的统计目标，收集更细粒度统计以优化执行计划。
- 索引策略：为高频过滤/排序/关联列建立合适索引；避免过多或重复索引；对大表按时间/租户等做 分区，减少扫描范围；结合 VACUUM/ ANALYZE 保持索引与统计健康。
- 执行计划治理：关注 seq scan 与 nested loop 的异常使用，结合 work_mem、统计信息与索引设计进行矫正；必要时使用 partial index/expression index 与 covering index 减少回表与 I/O。
五 安全与风险提示
- 数据安全优先：切勿为性能关闭 fsync 或将 synchronous_commit=off 用于生产关键数据；这类设置会显著增加崩溃/断电时的数据丢失风险。
- 变更流程：任何参数调整遵循“评估—灰度—回滚预案—压测验证—上线”的闭环；变更前备份 postgresql.conf/pg_hba.conf 与关键数据。
- 连接池与超时：合理配置 连接池大小、超时、重试，避免级联雪崩；对长事务设置 statement_timeout/lock_timeout，保护系统稳定性。