PostgreSQL 优化

解决什么问题

PostgreSQL 默认配置能跑起来，但数据量和查询复杂度上升后，性能问题往往集中在几个固定位置：本该走索引的查询在执行全表扫描；大表上的排序和聚合消耗大量内存与磁盘；JSONB 被当作万能存储；连接数上涨后内存被迅速吃掉。这个技能把排查这些问题的思路整理成一份可执行的优化指南。

核心能力与关键步骤

• 索引策略：从查询计划里定位 Seq Scan、高代价的 Nested Loop 和缺失排序索引的 Sort，并依据 pg_stat_statements 判断真实查询模式，而不是凭猜测建索引。
• 查询计划解读：关注行数估算是否失真、Sort 发生在内存还是磁盘，以及 Buffers 中 shared hit 与 shared read 的比例，判断缓存是否有效。
• 分区：对超过 1000 万行、且查询稳定过滤分区键的表引入分区。
• JSONB 操作：给出 JSONB 的常规使用边界，避免把大对象塞进字段。
• 连接池：Web 应用使用事务级池化；依赖预编译语句或临时表的应用使用会话级池化。
• 参数调优与反模式：指出常见做法为什么有害，例如每列都建索引、滥用 SELECT *、忽略 EXPLAIN ANALYZE、缺少连接池导致每个连接占用约 10MB 内存、以及在高峰期执行 VACUUM FULL 锁住整表。

适用边界

• 技能提供的是优化框架和核对清单，不会自动修改数据库，需要自己运行 EXPLAIN ANALYZE 验证效果。
• 组合索引的列顺序建议遵循等值、排序、范围的顺序，但最终要回归真实执行计划确认。
• 建议先在测试库验证再上线，避免把调优变成新的不稳定因素。
• 末尾的检查清单可以直接作为 code review 或运维巡检的核对项使用。