PostgreSQL 数据库优化

PostgreSQL 在生产环境中要发挥最佳性能，需要从索引、统计信息、配置参数和 SQL 写法等多方面入手。本文结合常见场景，梳理一套可落地的优化思路。

1. 索引优化

• 合理建索引：对 WHERE、JOIN、ORDER BY、GROUP BY 中高频出现的列建 B-tree 索引；大表可考虑分区后按分区键建索引。
• 避免冗余索引：已有复合索引 (a, b) 时，仅查 a 的查询可复用，通常不必再单独建 (a)。
• 表达式索引与部分索引：对 WHERE lower(email) = ? 可建 CREATE INDEX ON t (lower(email))；只针对部分行查询时可建部分索引减小体积、加快写入。

2. 统计信息与执行计划

• 及时更新统计信息：ANALYZE（或 autovacuum 中的 analyze）保证规划器能做出正确选择；大表在批量导入或大变更后建议手动执行 ANALYZE。
• 读懂执行计划：使用 EXPLAIN (ANALYZE, BUFFERS) 查看实际执行时间、缓冲命中与顺序/索引扫描比例，重点看高代价节点和误估行数。

3. 配置参数

• 内存类：shared_buffers（建议约为总内存的 25%）、work_mem（排序/哈希用，可针对会话或单查询调大）、maintenance_work_mem（VACUUM/CREATE INDEX 等）。
• 检查点与 WAL：checkpoint_completion_target、wal_buffers、max_wal_size 影响写入与恢复表现。
• 并发与连接：max_connections 不宜过大，可配合连接池（如 PgBouncer）控制连接数，避免内存被连接占满。

4. 维护与监控

• VACUUM：定期回收死元组、更新可见性信息；大表或高更新表可调大 autovacuum_vacuum_scale_factor 或单独设置 autovacuum_vacuum_threshold。
• 监控：关注慢查询、锁等待、复制延迟（若使用主从）、表膨胀；可结合 pg_stat_statements、pg_stat_user_tables 做分析。

5. SQL 与架构

• 避免 SELECT *：只查需要的列，减小 I/O 与网络。
• 分页：大偏移量 LIMIT n OFFSET m 代价高，可改为「上一页/下一页」基于索引的游标分页。
• 读写分离与缓存：读多写少时用只读副本分担查询；热点数据可用应用层缓存降低数据库压力。

系统性的说明可参考 PostgreSQL 官方文档 中的“性能提示”“并行查询”“例行维护”等章节，再结合业务做针对性调优。