PostgreSQL 性能调优入门：五个高性价比动作就够了

1) 连接池与并发

  • 使用 PgBouncer/pgpool 做会话池，避免过多连接拖垮实例。
  • 建议 max_connections 控制在合理范围（小型实例 <300），让连接池吸收高并发。
  • 只在需要时开启 SSL/压缩，减少 CPU 开销。

2) 内存与并行

  • 合理设置：
    • shared_buffers: 25% 内存起步
    • work_mem: 视单查询排序/哈希需要，避免过大导致 OOM
    • maintenance_work_mem: 提升索引重建/批量导入速度
  • 并行查询：max_parallel_workers_per_gather 适度打开；过度并行会导致争用。

3) 索引与执行计划

  • 按查询模式设计索引：等值 → B-Tree，范围/排序 → B-Tree，模糊/全文 → GIN/GiST。
  • 避免过多冗余索引：影响写入与 Autovacuum。
  • 用 EXPLAIN (ANALYZE, BUFFERS) 观察执行计划和缓存命中，定位慢点。
  • 对热点表考虑 covering index（含查询字段），减少回表。

4) Autovacuum 与膨胀

  • 保持 Autovacuum 开启，适当调快：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
      autovacuum_vacuum_scale_factor = 0.1
      autovacuum_analyze_scale_factor = 0.05
      autovacuum_vacuum_cost_limit = 4000
      autovacuum_max_workers = 5
      ```  
    - 膨胀过高时，用 `VACUUM (FULL)` 或 `pg_repack` 处理；避免频繁全表锁。  
    - 监控 `pg_stat_all_tables` 的 `n_dead_tup`、`last_autovacuum`。

    ## 5) 慢查询治理

    - 打开慢查询日志：`log_min_duration_statement = 500ms`（视情况调整）。  
    - 定期聚合 TopN 慢 SQL，优先优化高频/高耗资源的语句。  
    - 缓存/分片：热点读多可加缓存层；大表按时间/范围分区。  
    - 参数快速兜底：`effective_cache_size` 反映可用 OS 缓存，帮助优化器估算。

    ## 示例基础配置（参考）

    ```conf
    max_connections = 200
    shared_buffers = 4GB
    work_mem = 16MB
    effective_cache_size = 12GB
    maintenance_work_mem = 512MB
    max_parallel_workers_per_gather = 2
    autovacuum_vacuum_scale_factor = 0.1
    autovacuum_analyze_scale_factor = 0.05
    log_min_duration_statement = 500

监控与告警

  • 采集关键指标：连接数、TPS/QPS、缓存命中率、慢查询、膨胀、检查点、WAL 量。
  • 工具：pg_stat_statements、pgBadger、Prometheus + Grafana（pg_exporter）。
  • 告警阈值：连接利用率 >80%、命中率骤降、慢查询激增、磁盘逼近上限。

结语

先把连接池、内存与并行、索引策略、Autovacuum、慢查询这五件事做好，常见性能问题能消掉大半。再根据业务场景做缓存、分区、读写分离等扩展，循序渐进比盲目调参更高效。***