PostgreSQL 性能优化

PostgreSQL 在业务增长中容易出现几类典型性能问题：接口响应变慢但无法确定是查询逻辑、索引设计还是参数配置的锅；为复杂 SQL 建了索引却没提速，甚至拖慢写入；CPU 飙升或连接数突然打满却找不到根因；表数据量上来后写入变慢，VACUUM 跟不上死元组增长。

核心能力

• 基于 EXPLAIN (ANALYZE, BUFFERS) 执行计划与表结构精准定位瓶颈，拒绝拍脑袋猜测
• 覆盖 B-Tree/GIN/BRIN/部分索引/覆盖索引的设计，识别低基数列单列索引等常见陷阱
• 改写 LIMIT+OFFSET 大偏移分页、COUNT(*) 全表计数、OR 条件导致索引失效等低效 SQL
• 用 PgBouncer 连接池与参数调优解决连接数打满、内存分配不合理问题
• 规划 VACUUM 策略，避免死元组堆积与高峰 VACUUM FULL 锁表

关键步骤

1. 问题定位：收集执行计划、表结构、行数估算、当前连接数与慢查询日志片段
2. 索引优化：按查询模式选择索引类型，用 pg_stat_user_indexes 清理未使用索引
3. 查询重写：大偏移分页改 keyset 分页，OR 条件拆分用 UNION ALL，COUNT(*) 用估算或物化视图
4. 连接池与参数调优：设置 shared_buffers 为物理内存 25%、effective_cache_size 75%，用 statement_timeout 防雪崩
5. VACUUM 维护：开启 autovacuum 并调整高频更新表的 autovacuum_vacuum_scale_factor，用 pg_repack 在线重建替代高峰 VACUUM FULL

适用边界

• 不适用分布式数据库架构（如 Citus）
• 不处理 ORM 层面的 N+1 查询问题（可配合 prisma-workflows 使用）
• 覆盖索引 INCLUDE 语法需要 PostgreSQL 11+ 支持