SQL PostgreSQL 的 shared_buffers vs work_mem vs maintenance_work_mem 的内存分配比例原则

shared_buffers并非越大越好，25%是起点而非上限；work_mem是每个操作而非每个连接的内存；maintenance_work_mem可设高但需防并发；effective_cache_size不分配内存却影响执行计划。

shared_buffers 不是越大越好，25% 是起点不是上限

postgresql 的 shared_buffers 是所有连接共用的数据页缓存，但它和操作系统缓存（OS Page Cache）是并存关系，不是替代关系。盲目设成 50% 或 80%，反而会挤压 OS 缓存空间，导致预读失效、页面置换变慢，整体 I/O 效率不升反降。

• ≤4GB 内存：设为 25%，但绝对值别超 1GB（比如 2GB 机器设 512MB）
• 4–64GB 内存：统一按 25% 算，64GB 就是 16GB；但超过 32GB 后，再往上加收益极低，还加重缓冲区管理锁开销
• >64GB 内存：仍建议 25%，但硬上限设为 32GB；多出来的内存留给 OS 缓存更划算
• OLTP 场景可微调到 30%，但仅限专用数据库 + 连接数稳定；混合负载或小表高频更新时，25% 更稳

work_mem 是“每个操作”的内存，不是“每个连接”

很多人误以为 work_mem 是单个连接能用的总内存，结果设成 256MB，100 个活跃连接就吃掉 25GB——还没算 shared_buffers 和系统开销，直接触发 OOM。

• work_mem 是每个排序（ORDER BY）、每个哈希连接（HASH JOIN）、每个聚合（GROUP BY）各自能用的上限
• 一个查询可能同时触发多个操作：比如 select ... JOIN ... ORDER BY ... GROUP BY，最多可能消耗 3×work_mem
• 安全估算公式：work_mem ≤ (总内存 − shared_buffers − 8GB OS预留) ÷ (并发连接数 × 2~3)
• 查是否溢出：看 EXPLAIN ANALYZE 输出里有没有 sort Method: external merge Disk: 12345kB 或 Hash Join: 12345kB；有就说明该调大

maintenance_work_mem 可大胆设高，但得防并发维护任务

maintenance_work_mem 只在 VACUUM、CREATE INDEX、ANALYZE 等后台维护动作中生效，平时不占内存，所以可以比 work_mem 高得多——但前提是别让多个维护任务同时跑满。

• 单次维护（如重建大索引）：设 1GB～2GB 显著提速；32GB 机器设 1.5GB 是常见选择
• 别无脑设 4GB+：如果开了 max_parallel_maintenance_workers = 4，4 个并行 VACUUM 就可能瞬间吃掉 16GB
• 日常运维建议：配合 vacuum_cost_delay 控制节奏，避免维护期间拖垮线上查询
• 临时提值更安全：需要加速某次 CREATE INDEX，直接在会话里 SET maintenance_work_mem = '2GB';，完事即丢

effective_cache_size 不分配内存，但影响执行计划是否靠谱

effective_cache_size 是个“告诉优化器 OS 缓存有多大”的提示参数，不实际分配内存，但设错会导致优化器误判成本，选错索引或放弃嵌套循环——尤其在 SSD + 大内存机器上容易翻车。

• 它代表的是“PostgreSQL 自己的缓存 + OS Page Cache”合计可用量，不是只算 shared_buffers
• 专用数据库：设为总内存的 50%～75%（例如 32GB 机器设 effective_cache_size = 24GB）
• 混部环境（DB + 应用同机）：保守点，设为总内存的 40%～50%，留足给应用和内核
• 改完不用重启，但要等后续查询重新生成执行计划；可通过 EXPLAIN 对比前后是否用了更优路径

真正卡住性能的，往往不是 shared_buffers 没设够，而是 work_mem 在复杂查询里反复写临时文件，或者 effective_cache_size 把优化器带偏了——这些细节没监控、光靠拍脑袋调参，调得越猛，越容易在线上突然抖三抖。