PostgreSQL 数据库优化

PostgreSQL 数据库优化可以从多维度进行，以下是关键的参数调整方向与优化策略：

一、核心参数调优

1. 内存相关

   • shared_buffers：
     建议设为系统内存的15%-25%（OLTP 场景）。若内存 > 64GB，可适当降低比例。
   • work_mem：
     排序/哈希操作的内存，计算公式参考：work_mem = (总内存 - shared_buffers) / max_connections / 2。避免过大导致内存溢出。
   • maintenance_work_mem：
     VACUUM、CREATE INDEX 等维护操作的内存，可设为work_mem的 2-3 倍。
   • effective_cache_size：
     优化器估算的磁盘缓存大小，通常设为系统内存的50%-75%。

2. WAL 与恢复

   • wal_buffers：
     默认为 -1（自适应），通常 16-64MB 足够。高写入场景可设为 64MB。
   • max_wal_size：
     控制 WAL 日志最大尺寸（默认 1GB），建议设为shared_buffers的 1-2 倍。
   • min_wal_size：
     建议设为max_wal_size的 1/3。

3. 连接与并发

   • max_connections：
     根据实际连接数设置，避免过高（默认 100）。每连接会占用内存，可配合连接池（如 PgBouncer）使用。
   • max_parallel_workers_per_gather：
     并行查询的 worker 数，通常设为 CPU 核数的 1/2 到 2/3。

二、查询与索引优化

1. 启用高级特性

   -- 启用并行查询SETmax_parallel_workers=8;SETmax_parallel_workers_per_gather=4;-- 启用 JIT 编译（OLAP 场景）jit=on

2. 统计信息与成本估算
   • default_statistics_target：
     列统计信息精度，默认 100。复杂查询可提高到 200-500。
   • random_page_cost：
     SSD 环境建议设为1.0-1.2（默认 4.0 针对机械硬盘）。

三、存储与 I/O 优化

1. 表空间分离
   • 将 WAL 日志、数据、索引分配到不同物理磁盘。
2. 分区表
   • 对大表按时间/范围分区，减少单表体积。
3. 调整页大小
   • 若记录较大（> 8KB），可考虑编译时设置--with-blocksize=16或 32。

四、维护与监控

1. 定期维护

   -- 开启自动 vacuumautovacuum=onautovacuum_vacuum_scale_factor=0.1autovacuum_analyze_scale_factor=0.05-- 定期重建索引REINDEXINDEXCONCURRENTLY idx_name;

2. 监控工具
   • 使用pg_stat_statements分析慢查询。
   • 通过EXPLAIN (ANALYZE, BUFFERS)查看执行计划。

五、示例配置（32GB 内存，SSD 磁盘）

shared_buffers = 8GB effective_cache_size = 24GB work_mem = 32MB maintenance_work_mem = 1GB random_page_cost = 1.1 max_connections = 200 wal_buffers = 16MB max_wal_size = 2GB checkpoint_completion_target = 0.9

六、注意事项

1. 使用pg_tune工具生成基础配置。
2. 每次只调整 1-2 个参数，通过pgbench或真实业务测试效果。
3. 关注pg_stat_activity与pg_stat_bgwriter视图，定位瓶颈。

通过综合参数调整、查询优化与存储策略，可显著提升 PostgreSQL 性能。建议结合具体业务负载进行针对性调优。