一、现象与背景
一台 PostgreSQL 15.4 实例（32C 128G，NVMe SSD 2TB，shared_buffers=48GB），承载两个业务：
- 在线查询服务：HikariCP
maximumPoolSize=64，典型 SQL 含 1~2 个 Sort/Hash 节点（单表点查 + 小范围 ORDER BY）； - 离线 ETL 批处理：12 个并行 worker，每条 SQL 含 5~8 个 Sort/Hash 节点（多表 JOIN + 窗口函数 + 多路 CTE）。
work_mem 被前任 DBA 设为 256MB，理由是"256MB 够排序 2000 万行了，肯定够用"。
事故表现：
- 每天 02:00 ETL 启动后 8~15 分钟，在线查询 P99 从稳态的 5.2ms 飙到 38~52ms，持续 20~40 分钟；
vmstat显示si/so（swap in/out）从 0 涨到 1200~4500 页/秒；pg_stat_database中temp_blks_written在事故窗口内增长 18.6 亿块（约 29GB 临时文件）；- 没有 OOM Killer 日志（离 OOM 还差 12GB），但内核已在积极回收 shared_buffers 页，大量随机读落到 NVMe。
二、原理：work_mem 的分配粒度是「每个算子节点 × 每条查询」
这是最容易被误读的一个参数：
- work_mem 不是"每条 SQL 的内存上限"，而是每个内存密集算子（Sort、Hash、Merge Join、Materialize）的独立配额。一条 SQL 里有 6 个 Sort 节点，就会同时申请 6 × work_mem。
- 并发是乘法，不是除法。64 个在线连接 × 平均 2 个节点 + 12 个 ETL worker × 平均 7 个节点 = 128 + 84 = 212 个并发 work_mem 分配。
- 峰值内存需求 = 212 × 256MB ≈ 53.5GB。加上 shared_buffers 48GB，合计 101.5GB / 128GB，只剩 26.5GB 给 OS 页缓存、socket buffer、以及 PG 自身的 per-process 开销（每个 backend 还有约 10~20MB 的 statement_mem、plan cache 等）。
当实际工作集超出物理内存，内核的 page reclaim 开始把 shared_buffers 的热页换出到 NVMe。原本 buffer pool 命中率 99.2% 的查询，在事故窗口内掉到 94.7%，每次 miss 就多一次 0.1~0.3ms 的随机读，叠加在 P99 上就是 30~50ms 的额外延迟。
更隐蔽的是：部分 ETL 的 Sort 节点实际数据量超过 256MB（排序 8000 万行 × 平均行宽 96B ≈ 7.5GB），这些节点直接溢出到临时文件（/var/lib/postgresql/15/main/base/.../pgsql_tmp/），NVMe 的写放大进一步挤占了 IO 带宽。
三、具体做法：从「拍脑袋定 work_mem」到「按查询画像倒推」
3.1 量化每条 SQL 的 work_mem 实际消耗
开启慢查询日志（log_min_duration_statement=500ms），用 EXPLAIN (ANALYZE, BUFFERS) 跑 Top 20 慢 SQL：
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM t_order
JOIN t_user ON ...
JOIN t_product ON ...
WHERE dt >= '2025-09-01'
ORDER BY created_at DESC
LIMIT 1000;
关注输出中每个 Sort / Hash / Merge Join 节点的 Memory: xxxkB。我们实测：
| 节点类型 | 实际内存 | 是否溢出 256MB | |---------|---------|---------| | Sort (80M rows) | 7,432,896 kB ≈ 7.1GB | 是 | | Hash Join (22M rows) | 2,154,304 kB ≈ 2.1GB | 是 | | Sort (3M rows) | 287,424 kB ≈ 281MB | 是（刚好超） | | Hash Join (500K rows) | 48,212 kB ≈ 47MB | 否 |
3.2 倒推 work_mem 的合理值
公式：work_mem ≤ (总内存 - shared_buffers - OS预留) / (峰值并发 work_mem 节点总数)
- 总内存 128GB - shared_buffers 48GB - OS 预留 16GB = 64GB 预算
- 峰值节点总数：在线 64×2=128 + ETL 12×8=96 → 224（取保守上限）
- 理论上限：64GB / 224 ≈ 291MB
看起来 256MB 刚好在边界上。但关键是不是所有节点同时跑满：在线查询的 Sort 通常 < 10MB，真正吃内存的只有 ETL 的 96 个大节点。更精确的估算：
- 在线：64 × 2 × 10MB（实际 P50）= 1.28GB
- ETL：12 × 8 × 512MB（取 P99 节点大小）= 48GB
- 合计：~50GB < 64GB 预算 ✓
最终决策：把 work_mem 从 256MB 降到 128MB（让大多数节点留在内存），同时对 ETL 中那 3 个 7GB 级别的 Sort 改为分桶物化（先 INSERT INTO pg_temp 分桶，再逐桶排序合并），避免单节点撑爆。
3.3 配置变更与验证
-- postgresql.conf（PG 15.4）
work_mem = '128MB'
log_temp_files = '10MB' -- 任何单文件 > 10MB 的临时文件都记日志
重启后观察 7 天：
pg_stat_database.temp_blks_written从日均 2.4 亿块 降到 1800 万块（降 93%）；- 在线 P99 在 ETL 窗口期从 38~52ms 回落到 7~11ms；
vmstat si/so回到 0。
3.4 给 ETL 加一个「内存护栏」
在调度层（Airflow DAG）加 pre-check：
# 启动前检查当前 PG 实例的活跃连接数
active = query("SELECT count(*) FROM pg_stat_activity WHERE state = 'active'")
if active > 80:
raise AirflowSkipException(f"Active connections {active} > 80, delay ETL")
避免 ETL 和在线高峰撞车。
四、踩过的坑与反直觉
-
work_mem 不是越大越好。我们最初想调到 1GB 来"彻底解决排序溢出"，压测发现 12 个 ETL worker 同时跑时，12 × 8 × 1GB = 96GB 直接打穿内存，P99 飙到 2.1s，比 256MB 时更惨。正确方向是减少单节点工作量（分桶/物化），而不是给每个节点更多内存。
-
log_temp_files默认是 -1（不记录）。排查时翻遍了pg_stat_activity、慢查询日志，都没找到临时文件的直接证据，最后靠pg_stat_database.temp_blks_written的增量才定位。建议生产环境默认开启log_temp_files=10MB，成本最低的可观测性手段。 -
shared_buffers 和 work_mem 是叠加关系，不是分配关系。很多团队把 128GB 拆成"shared_buffers 64GB + work_mem 吃剩下 64GB"，实际上 work_mem 是 per-process 堆外内存，不经过 shared_buffers。两者同时消耗物理内存，不是此消彼长。
-
track_io_timing有性能代价。开启后每次 IO 多一次clock_gettime()，在 NVMe 50 万 IOPS 场景下额外消耗 3~5% CPU。建议只在排查期间开，不要长期开。
五、结论与可复用清单
核心认知：work_mem 的内存消耗 = 并发连接数 × 每查询内存密集节点数 × work_mem。它是乘法关系，不是"设个值就完事"的开关。
可直接抄的检查清单：
- [ ] 对 Top 20 慢 SQL 跑
EXPLAIN (ANALYZE, BUFFERS)，记录每个 Sort/Hash 节点的实际Memory值； - [ ] 统计峰值并发时的 work_mem 节点总数（连接数 × 平均节点数），乘以 work_mem，与
(总内存 - shared_buffers - OS预留)比较； - [ ] 开启
log_temp_files=10MB，监控pg_stat_database.temp_blks_written日增量； - [ ] 对单节点 > 1GB 的排序，改用分桶 + 物化，而不是加大 work_mem；
- [ ] ETL 与在线服务错峰，或在调度层加 active connections 护栏；
- [ ] work_mem 调参后，必须跑 7 天以上 P99 趋势，不要只看单次压测。
林砚舟在连接池调参一文中写道："连接池大小不是性能参数，是「上游并发能压到下游多少」的阀门。" work_mem 同理——它不是"给 SQL 多少内存"的参数，而是"给每个内存密集节点多少配额"的阀门。阀门调大，下游（物理内存）先崩。