Linux上提升 PostgreSQL 查询性能的系统化做法
一 硬件与存储布局
- 优先选择多核高频 CPU 与更大的 L3 缓存，对并行查询与大数据集更友好；充足且可扩展的 内存 能显著提升 OS 页缓存命中率，减少磁盘 I/O。
- 使用 SSD/NVMe 或高性能磁盘阵列；将 WAL 与数据分离，至少 RAID 1，高写入场景建议 RAID 10；为索引和数据使用独立表空间/磁盘在 SATA 上更有收益。
- 在虚拟化环境中，尽量做 CPU/磁盘绑定，预分配磁盘；在 COW 文件系统 上可考虑关闭 wal_recycle 与 wal_init_zero 降低 WAL 分配开销。
- 更快的 网卡/链路聚合 能加速备份、复制与大规模数据传输。
二 Linux 操作系统调优
- 使用 tuned 创建性能导向的 profile（如设置 CPU 调节器为 performance、关闭透明大页 transparent_hugepage=never、降低 vm.swappiness=10、调整脏页刷新参数），并让数据库服务在 tuned 之后启动。
- 文件系统挂载选项为 noatime（必要时 nodiratime），减少元数据写入；对数据库数据/WAL 所在分区使用 XFS/ext4 并合理设置对齐与条带。
- 可选：启用 Huge Pages 减少 TLB 缺失（计算实例峰值内存后配置 vm.nr_hugepages，并在 postgresql.conf 设置 huge_pages=on）。
- I/O 调度与预读：SSD 优先 noop/deadline；顺序读多可调大预读（如 blockdev --setra）。
- 网络：可按需优化 tcp_tw_reuse/tcp_tw_recycle、somaxconn、tcp_max_syn_backlog、rmem/wmem 等以支撑高并发连接与备份吞吐。
三 PostgreSQL 参数与资源配置
- 连接与并发：避免过高的 max_connections，建议不超过 4 × CPU 核心数 或至少 100，高并发使用 PgBouncer 等连接池。
- 内存关键参数（需结合实际负载与监控逐步调优）：
- shared_buffers：起步可用 RAM/2（上限约 10GB），再按命中率与检查点压力微调。
- work_mem：起步可用 ((Total RAM − shared_buffers) / (16 × CPU 核心数))，注意每个排序/哈希算子都可能消耗多份 work_mem。
- maintenance_work_mem：维护类操作（VACUUM/建索引等）起步 1GB；autovacuum_work_mem 可单独放大以提升清理效率。
- effective_io_concurrency：旋转盘按磁盘数设置，SSD 可设为 200 以启用更积极预读。
- 其他常用：开启 wal_compression（在 full_page_writes 开启或备份期间有效）降低 WAL 写入量。
四 索引与 SQL 优化
| 索引类型 |
典型场景 |
关键要点 |
| B-Tree |
等值/范围/排序 |
通用首选，支持 =、<、>、BETWEEN、LIKE ‘prefix%’ |
| Hash |
仅等值 |
不支持范围/排序，使用场景窄 |
| GIN |
JSONB/数组/全文 |
支持包含、存在、重叠等“多值”查询 |
| GiST |
地理/文本/范围/KNN |
支持重叠、包含、邻近等多样查询 |
| SP-GiST |
IP/树形/稀疏 |
非平衡数据结构更高效 |
| BRIN |
超大且有序（时间序列） |
体积极小、维护低，乱序效果差 |
- 索引创建与维护要点
- 为高频出现在 WHERE/JOIN/ORDER BY/GROUP BY 的列建索引；复合索引遵循“最左前缀”，选择性高的列放前。
- 使用 部分索引 与 表达式索引 精准覆盖业务；需要覆盖查询列时用 INCLUDE 创建覆盖索引以减少回表。
- 生产环境优先 CREATE INDEX CONCURRENTLY 避免写阻塞；定期 REINDEX/VACUUM 控制膨胀。
- 监控索引使用：查询 pg_stat_user_indexes.idx_scan，删除长期低使用的索引。
- 执行计划与 SQL 写法
- 用 EXPLAIN 看预估、EXPLAIN ANALYZE 看实际（生产慎用），关注 Seq Scan/Index Scan、Rows 预估偏差、Sort/Materialize/Hash Join 成本。
- 避免对索引列使用函数导致失效；分页深翻用“游标分页”（记住上一页最后值做 WHERE 条件）替代大 OFFSET。
五 维护、监控与进阶
- 统计信息与 VACUUM：定期 ANALYZE 保证计划质量；确保 autovacuum 正常运行，避免表膨胀导致扫描与索引效率下降。
- 表设计与分区：对 时间序列/超大表 按时间或业务键分区，查询可显著减少扫描范围。
- JIT 加速：安装 LLVM 并在编译时启用 –with-llvm，在 CPU 受限且表达式复杂/行数多的查询中可缩短执行时间。
- 连接治理：高并发以 PgBouncer 等连接池控制连接数，避免连接风暴与上下文切换开销。
- 监控与容量：持续观察 pg_stat_activity、慢查询日志、检查点抖动、WAL/数据吞吐、I/O 等待与缓存命中率，按指标驱动参数与索引迭代。
