常见的 PostgreSQL 性能优化技巧

以下是一些常见的 PostgreSQL 性能优化技巧，涵盖了索引优化、查询优化、配置调优、硬件优化等多个方面：

### 索引优化
1. **选择合适的索引类型**：
- **B-Tree 索引**：适用于大多数场景，尤其是范围查询。
- **GIN/GiST 索引**：GIN 适用于 JSONB、数组和全文搜索，GiST 适用于地理空间数据。
- **BRIN 索引**：对按时间或数值顺序排列的大表（如日志表）效果显著。
- **部分索引**：仅索引数据子集，例如对状态为“未完成”的订单建索引。
2. **避免索引滥用**：
- 删除冗余索引，通过 `pg_stat_user_indexes` 表分析索引使用率。
- 合并复合索引，将高频查询的多个单列索引合并为复合索引。

### 查询优化
1. **分析慢查询**：
- 启用慢查询日志，设置 `log_min_duration_statement` 参数。
- 使用 `EXPLAIN ANALYZE` 解析执行计划，重点关注全表扫描（Seq Scan）和哈希连接（Hash Join）。
2. **优化复杂查询**：
- 将 CTE 改写为子查询，避免多次执行。
- 避免 `SELECT *`，仅查询必要字段。
- 使用游标或基于 ID 范围的分页替代 `LIMIT/OFFSET`。

### 配置调优
1. **关键参数调整**：
- **`shared_buffers`**：通常设为物理内存的 25%~40%。
- **`work_mem`**：影响排序和哈希操作，建议根据并发量调整。
- **`maintenance_work_mem`**：用于 VACUUM 和 CREATE INDEX 等操作，设为 1GB~4GB。
- **`effective_cache_size`**：设为系统缓存的 70%~80%，帮助优化器选择更优执行计划。
2. **并发连接控制**：
- 使用连接池工具（如 PgBouncer）减少连接开销。
- 设置 `idle_in_transaction_session_timeout` 自动终止空闲事务。

### 分区与并行化
1. **表分区**：
- 按时间分区（如日志表按月分区）。
- 按哈希分区，分散数据到不同分区，提升写入并发性。
2. **并行查询**：
- 启用并行执行，设置 `max_parallel_workers_per_gather` 参数。

### 存储与硬件优化
1. **存储层优化**：
- 使用 SSD 替代 HDD，提升随机读写性能。
- 优先选择 XFS 或 ZFS 文件系统。
2. **RAID 配置**：
- 使用 RAID 10 兼顾性能与可靠性。

### 日常维护
1. **定期 VACUUM 与 ANALYZE**：
- 开启 `autovacuum`，设置合理的清理和分析阈值。
- 对大表手动执行 `VACUUM FULL ANALYZE`。
2. **监控膨胀与锁争用**：
- 查询表膨胀率，避免表膨胀。
- 使用 `pg_locks` 视图分析锁等待，避免长事务阻塞。

### 扩展与工具
1. **性能监控工具**：
- 使用 `pg_stat_statements` 插件统计 SQL 执行时间。
- 使用 `pgBadger` 分析日志生成可视化报告。
2. **扩展插件**：
- 使用 `pg_partman` 自动化分区管理。
- 使用 `TimescaleDB` 优化时序数据。

通过以上优化措施，可以显著提升 PostgreSQL 的性能。在实际应用中，需要根据具体的业务场景和硬件环境进行针对性的调整和优化。