work_mem 工作内存

work_mem 是 PostgreSQL 里最容易被误当成「每连接内存」的参数。它不是每个连接的内存上限，而是每一个排序（sort）或哈希（hash）操作的基准内存额度。一个复杂查询可能同时跑好几个排序/哈希，多个会话又在并发执行——所以实际内存消耗是 work_mem 的很多倍。把这一层关系搞清楚，才不会出现「设了 work_mem = 256MB 结果 OOM」。

前置知识

阅读本节前，建议先了解：effective_cache_size 优化器缓存估算

基础概念

它是什么

项目	说明
参数名	work_mem
默认值	4MB
单位	不写单位时按 kB 解释（注意与 shared_buffers 的「块」不同）
上下文	user（普通用户可 SET，reload 生效）
作用对象	单个排序 / 哈希 / 物化（memoize）操作
超限行为	溢出到临时文件（temp files），而非报错
是否全局共享	否，每个操作各自独立计数

SELECT name, setting, unit, context, boot_val, min_val, max_val
FROM pg_settings WHERE name = 'work_mem';

    name    | setting | unit | context | boot_val | min_val | max_val
------------+---------+------+---------+----------+---------+---------
 work_mem   | 4096    | kB   | user    | 4096     | 64      | 2147483647
(1 row)

默认 4096 kB = 4MB。

最致命的误解：work_mem 不是「每连接」

❌ 错误理解：max_connections = 100，work_mem = 256MB
             → 最坏情况 100 × 256MB = 25GB，够用

✅ 正确理解：每个连接里可能同时有多个排序/哈希操作
             每个排序/哈希各自可以用满 work_mem
             max_connections = 100，每个连接 3 个哈希操作
             → 最坏情况 100 × 3 × 256MB = 76.8GB

内存到底怎么算

work_mem 是「每个操作」的额度，不是「每个会话」的额度。

┌──────────────────────────────────────────────────────────────┐
│              work_mem 的实际内存消耗模型                        │
├──────────────────────────────────────────────────────────────┤
│                                                              │
│   会话 1          会话 2          会话 3      ...             │
│     │               │               │                        │
│     ├─ Sort         ├─ Hash Join    ├─ Sort                  │
│     ├─ HashAgg      ├─ Sort         ├─ HashAgg               │
│     └─ Memoize      └─ HashAgg      └─ Hash Join             │
│                                                              │
│   每个方框都各自可以用满 work_mem                              │
│   （哈希操作还能用 work_mem × hash_mem_multiplier）            │
│                                                              │
└──────────────────────────────────────────────────────────────┘

最坏内存 ≈ 并发会话数 × 每会话并发操作数 × work_mem

示例：max_connections = 200，work_mem = 64MB，每会话最多 4 个哈希操作
最坏内存 ≈ 200 × 4 × 64MB = 51.2GB

哈希操作还要再乘一次

哈希类操作的上限是 work_mem × hash_mem_multiplier（默认 hash_mem_multiplier = 2.0）。所以哈希操作的实际额度是 work_mem 的 2 倍。上面的估算若全是哈希，还要再翻倍。

哪些操作会用到 work_mem

操作类型	典型触发场景	是否受 hash_mem_multiplier 放大
排序（Sort）	ORDER BY、DISTINCT、归并连接（merge join）	❌ 否
哈希表（Hash）	哈希连接（hash join）、哈希聚合、Memoize 节点、IN 子查询的哈希处理	✅ 是

-- 排序操作：显著依赖 work_mem
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders ORDER BY created_at DESC;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Sort  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
   Sort Key: created_at DESC
   Sort Method: external merge  Disk: 98304kB     ← 溢出到磁盘了！
   Buffers: shared hit=... read=...

-- 哈希操作：额度是 work_mem × hash_mem_multiplier
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Hash Join  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o  (...)
   ->  Hash  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
         Buckets: 131072  Batches: 4  Memory Usage: 40960kB   ← Batches > 1 说明溢出了

关键判读信号

• 排序：Sort Method: external merge → 溢出磁盘；quicksort → 全在内存。
• 哈希：Batches: 1 → 全在内存；Batches > 1（如 4、8、16）→ 分批溢出。

溢出的代价

当操作所需内存超过额度，PostgreSQL 不会报错，而是切分成多批写临时文件，然后反复读写磁盘完成操作。

内存充足（Batches: 1 / quicksort）
     │
     ▼
纯内存操作，延迟低（微秒~毫秒级）
     │
     ▼
─────────────────────────────────────
     │
内存不足（Batches: 4 / external merge）
     │
     ▼
写临时文件 → 读回 → 再写 → 再读（多趟 I/O）
     │
     ▼
延迟可能放大 10~100 倍

-- 观察临时文件总量（全库级）
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
WHERE datname = current_database();

 datname | temp_files | temp_size
---------+------------+-----------
 myapp   |      18422 | 21 GB
(1 row)

与相关参数的关系

参数	默认值	上下文	作用
work_mem	4MB	user	单个排序/哈希操作的基准额度
hash_mem_multiplier	2.0	user	哈希操作的倍数，最终额度 = work_mem × 该值
maintenance_work_mem	64MB	user	维护操作（VACUUM、CREATE INDEX）的内存
temp_buffers	8MB	user	每个会话访问临时表用的本地缓冲
log_temp_files	-1	superuser	记录超过阈值的临时文件
max_connections	100	postmaster	并发会话上限，参与最坏内存估算

语法与用法

配置写法

# postgresql.conf —— 全局保守值，4MB~16MB 起步
work_mem = '4MB'

# 哈希操作倍数（默认 2.0）
hash_mem_multiplier = 2.0

work_mem 的单位是 kB

work_mem 若不写单位，按 kB 解释：

work_mem = 4096        # 4096 kB = 4MB
work_mem = '4MB'       # 显式单位，推荐

而 shared_buffers 不写单位时按 块（8kB） 解释。两者规则不同，务必写显式单位。

修改与生效

-- 全局修改（写 postgresql.auto.conf，reload 生效）
ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

-- 核验
SELECT name, setting, unit, source, sourcefile
FROM pg_settings WHERE name = 'work_mem';

   name   | setting | unit |       source       |                   sourcefile
----------+---------+------+--------------------+-------------------------------------------------
 work_mem | 16384   | kB   | configuration file | /var/lib/postgresql/18/main/postgresql.auto.conf
(1 row)

会话级与事务级调整

-- 会话级：整个会话有效
SET work_mem = '64MB';

-- 事务级：事务结束自动失效（推荐用于偶发的大查询）
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT count(*) FROM big_table GROUP BY category_id;
COMMIT;

-- 查看当前会话的实际值
SHOW work_mem;

 work_mem
----------
 64MB
(1 row)

为特定角色 / 数据库设置

-- 给报表角色统一提高（报表查询常有大排序）
ALTER ROLE report_user SET work_mem = '256MB';

-- 只在报表库里生效
ALTER DATABASE analytics SET work_mem = '256MB';

-- 角色 + 库的组合（最精确）
ALTER ROLE report_user IN DATABASE analytics SET work_mem = '512MB';

-- 查看角色级设置
SELECT rolname, rolconfig FROM pg_roles WHERE rolname = 'report_user';

   rolname   |          rolconfig
-------------+-----------------------------
 report_user | {work_mem=256MB}
(1 row)

开启临时文件日志

-- 记录超过 64MB 的临时文件（-1 表示关闭）
ALTER SYSTEM SET log_temp_files = '64MB';
SELECT pg_reload_conf();

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp12345.0", size 98304000
STATEMENT:  SELECT ... ORDER BY created_at DESC;

与相关命令的对照

-- 方式一：全局（影响所有会话，谨慎）
ALTER SYSTEM SET work_mem = '16MB';

-- 方式二：库级
ALTER DATABASE myapp SET work_mem = '32MB';

-- 方式三：角色级
ALTER ROLE app_user SET work_mem = '32MB';

-- 方式四：会话级（最灵活）
SET work_mem = '256MB';

-- 方式五：事务级（最安全）
SET LOCAL work_mem = '256MB';

-- 清除各级设置
ALTER SYSTEM RESET work_mem;
ALTER DATABASE myapp RESET work_mem;
ALTER ROLE app_user RESET work_mem;
RESET work_mem;

详细说明

一、work_mem 的名字由来

work_mem 在 PostgreSQL 8.3 之前叫 sort_mem。改名是因为它已经不只服务于排序——哈希连接、哈希聚合、物化节点都依赖它。「工作内存」这个名字才准确描述了它的语义：为「查询执行过程中的工作」预留的内存额度。

-- 老配置里的 sort_mem 已被移除，写了会报错
-- work_mem = ...   ← 正确
-- sort_mem = ...   ← 错误！参数不存在

二、排序为何需要内存

排序算法主要有两类：

┌───────────────────────────────────────────────────────┐
│                    排序算法的选择                       │
├───────────────────────────────────────────────────────┤
│  数据能放进 work_mem                                   │
│  → quicksort（快排，纯内存，最快）                       │
│                                                       │
│  数据放不进 work_mem                                    │
│  → external merge（外部归并排序）                        │
│     · 先排序若干批，各自写临时文件                        │
│     · 再做多路归并                                      │
│     · 可能经历多个 pass（每 pass 一次全量 I/O）           │
└───────────────────────────────────────────────────────┘

-- 小排序：纯内存
SET work_mem = '64MB';
EXPLAIN (ANALYZE) SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;

 Sort Method: top-N heapsort  Memory: 32kB

-- 大排序：溢出
SET work_mem = '1MB';
SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN (ANALYZE) SELECT * FROM orders ORDER BY created_at DESC;

 Sort Method: external merge  Disk: 98304kB

三、哈希操作与 hash_mem_multiplier

哈希操作的上限不是 work_mem，而是：

哈希操作内存额度 = work_mem × hash_mem_multiplier

SELECT name, setting, unit, boot_val FROM pg_settings WHERE name = 'hash_mem_multiplier';

        name          | setting | unit | boot_val
----------------------+---------+------+----------
 hash_mem_multiplier  | 2       |      | 2
(1 row)

work_mem = 64MB, hash_mem_multiplier = 2.0
→ 排序操作额度 = 64MB
→ 哈希操作额度 = 128MB

什么时候该调高 hash_mem_multiplier

官方建议：当溢出现象频繁、且单纯提高 work_mem 会带来内存压力（表现为间歇性 OOM）时，可以调高它。在 work_mem 已提高到 40MB 以上的环境中，2.0 ~ 8.0 甚至更高都可能更有效。

四、如何判断是否溢出

方法一：EXPLAIN 看 Sort Method / Batches。

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- 排序：Sort Method: external merge  Disk: NkB   → 溢出
-- 哈希：Batches: 8                                → 溢出

方法二：看临时文件统计。

-- 全库累计
SELECT datname, temp_files, pg_size_pretty(temp_bytes)
FROM pg_stat_database WHERE datname = current_database();

-- 单条语句累计（需 pg_stat_statements）
SELECT queryid, calls, temp_blks_written, temp_blks_read, left(query, 60)
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC LIMIT 10;

方法三：看日志。

-- log_temp_files 记录每条超阈值临时文件
ALTER SYSTEM SET log_temp_files = 0;   -- 记录所有临时文件（调试期）

五、临时文件写在哪

临时文件默认写在 base/pgsql_tmp/ 下，由 temp_tablespaces / temp_file_limit 控制。

SELECT name, setting, unit FROM pg_settings
WHERE name IN ('temp_tablespaces', 'temp_file_limit', 'temp_buffers');

       name       | setting | unit
------------------+---------+------
 temp_buffers     | 8192    | 8kB
 temp_file_limit  | -1      |
 temp_tablespaces |         |
(3 rows)

-- 限制单个会话的临时文件总量为 10GB（防止失控查询写爆磁盘）
ALTER SYSTEM SET temp_file_limit = '10GB';

六、并发放大效应

这是 work_mem 调优的核心难点：你无法预知并发峰值。

单个查询：3 个哈希操作 × 128MB = 384MB
100 个并发：384MB × 100 = 38.4GB

-- 看当前并发活跃查询数与可能的内存占用
SELECT count(*) AS active_queries,
       count(*) * 64 AS mb_if_64mb_each
FROM pg_stat_activity
WHERE state = 'active' AND query_start IS NOT NULL;

 active_queries | mb_if_64mb_each
----------------+-----------------
             47 |            3008
(1 row)

不要在 OLTP 高并发库上把全局 work_mem 调得很大

OLTP 场景并发连接数高（几百到几千），全局 work_mem 每提高一点都会被并发放大。这时应该：

• 全局保持较小值（4MB~16MB）；
• 只对报表类角色或个别大查询用 ALTER ROLE / SET LOCAL 临时提高。

七、并行查询：每个 worker 再算一份

前面的估算还漏了一层：并行查询。并行计划里的每个 worker 都是一个独立的后端进程，各自拥有完整的 work_mem 额度。

┌──────────────────────────────────────────────────────────────┐
│  一个并行查询的内存                                       │
├──────────────────────────────────────────────────────────────┤
│   Leader（执行用户会话的那个进程）                              │
│     └─ 也要执行并行部分的计划 → 一份 work_mem                  │
│                                                              │
│   Worker 1  → 一份 work_mem                                   │
│   Worker 2  → 一份 work_mem                                   │
│   Worker 3  → 一份 work_mem                                   │
│                                                              │
│   合计 = (1 + Worker 数) × work_mem（每个 worker 各自的        │
│          排序/哈希操作还会各自翻倍）                            │
└──────────────────────────────────────────────────────────────┘

示例：max_parallel_workers_per_gather = 4，work_mem = 64MB
单个查询的排序内存 ≈ (1 + 4) × 64MB = 320MB
若同时有 10 个这样的查询 → 3.2GB

-- 查看并行相关的参数
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('max_parallel_workers_per_gather','max_parallel_workers',
               'max_worker_processes','parallel_setup_cost','parallel_tuple_cost')
ORDER BY name;

             name              | setting | unit
-------------------------------+---------+------
 max_parallel_workers          | 8       |
 max_parallel_workers_per_gather| 2      |
 max_worker_processes          | 8       |
 parallel_setup_cost           | 1000    |
 parallel_tuple_cost           | 0.1     |
(5 rows)

max_parallel_workers_per_gather 默认 2，即默认情况下单查询最多 1 个 leader + 2 个 worker

-- 从执行计划确认实际启用了几个 worker
EXPLAIN (ANALYZE, VERBOSE) SELECT customer_id, count(*) FROM orders GROUP BY customer_id;

 Finalize GroupAggregate  (cost=... rows=... width=...)
   Group Key: customer_id
   ->  Gather Merge  (cost=... rows=... width=...)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial GroupAggregate  (...)
               ->  Sort  (...)
                     Sort Method: quicksort  Memory: 892kB

Workers Launched 才是真实启动的 worker 数——内存按它算，不是按 Workers Planned 算。

并行 worker 数量受 worker 池限制，可能少于计划值

并行 worker 是后台工作进程（background worker）：它不占用用户连接的 max_connections 槽位，但受 max_worker_processes 与 max_parallel_workers 限制。如果 worker 池被占满，计划里的并行会退化——查询会用更少的 worker（甚至 0 个）执行，性能与内存占用都会和预期不同。

内存受限时优先降并行，而不是砍全局 work_mem

若某个报表查询因并行导致内存峰值过高，可以只针对该查询临时禁用并行，而不必牺牲全库的 work_mem：

SET max_parallel_workers_per_gather = 0;
-- 执行该查询
RESET max_parallel_workers_per_gather;

八、OLTP 与 OLAP 的取值差异

场景	典型并发	建议 work_mem	说明
OLTP（事务型）	高（数百）	4MB~16MB	查询小，靠并发放大风险高
混合负载	中（数十）	16MB~64MB	折中
OLAP（分析型）	低（个位数）	256MB~1GB	大排序/聚合多，并发低
报表专用角色	低	256MB+	用 ALTER ROLE 单独设

-- OLTP 库：全局保守
ALTER SYSTEM SET work_mem = '8MB';

-- 报表角色：单独放大
ALTER ROLE report_user SET work_mem = '512MB';

九、work_mem 与 maintenance_work_mem 的分工

这两个名字像，但作用完全不同：

┌───────────────────────────────────────────────────────┐
│  work_mem                                              │
│  · 服务于「查询执行」                                    │
│  · 排序、哈希连接、哈希聚合                              │
│  · 每个操作各一份，并发放大严重                          │
│                                                        │
│  maintenance_work_mem                                  │
│  · 服务于「维护操作」                                    │
│  · VACUUM、CREATE INDEX、ALTER TABLE ADD FK             │
│  · 每个会话一次只有一个维护操作，可设得更大               │
└───────────────────────────────────────────────────────┘

SELECT name, setting, unit FROM pg_settings
WHERE name IN ('work_mem', 'maintenance_work_mem');

         name          | setting | unit
-----------------------+---------+------
 maintenance_work_mem  | 65536   | kB
 work_mem              | 4096    | kB
(2 rows)

十、临时文件与 temp_buffers 的区别

概念	参数/机制	作用
排序/哈希溢出	work_mem	控制内存额度，超出写临时文件
临时文件写入	temp_file_limit	限制单会话临时文件总量
临时表访问缓冲	temp_buffers	每个会话访问临时表的本地缓冲
临时文件落盘位置	temp_tablespaces	指定临时文件/临时表的表空间

-- temp_buffers 只影响「临时表」的访问，不影响排序溢出
SET temp_buffers = '64MB';

别把 temp_buffers 当成 work_mem 的补充

temp_buffers 解决的是「临时表页缓存」，对「排序溢出到临时文件」没有帮助。排序溢出仍然只看 work_mem。

十一、参数生效与快照

work_mem 是 user 上下文，意味着：

-- 1. 全局改（reload 生效）
ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

-- 2. 已有会话不会自动重新读取？—— 会。user 上下文在 reload 后新查询会取新值
--    但已经被 SET 过的会话保持自己的值
SHOW work_mem;

-- 3. 会话内被 SET 过的，需要 RESET 才回到全局值
RESET work_mem;
SHOW work_mem;

-- 查看值来自哪里
SELECT name, setting, unit, source, sourcefile, sourceline
FROM pg_settings WHERE name = 'work_mem';

   name   | setting | unit |       source       |    sourcefile     | sourceline
----------+---------+------+--------------------+-------------------+------------
 work_mem | 16384   | kB   | configuration file | postgresql.auto.conf |          1
(1 row)

用 pg_settings 定位「谁改的」

source 会告诉你当前值来自 default、configuration file、database、role、session 还是 client。排查「这个会话的 work_mem 怎么这么大」时非常有用。

实战示例

场景一：报表查询溢出磁盘，定位并修复

-- 步骤 1：复现并观察
EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', created_at) AS d,
       status,
       count(*) AS cnt,
       sum(amount) AS total
FROM orders
WHERE created_at >= now() - interval '90 days'
GROUP BY 1, 2
ORDER BY 1 DESC;

                                  QUERY PLAN
-----------------------------------------------------------------------------
 GroupAggregate  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
   Group Key: (date_trunc('day'::text, created_at)), status
   ->  Sort  (cost=... rows=... width=...)
         Sort Key: (date_trunc('day'::text, created_at)) DESC, status
         Sort Method: external merge  Disk: 172032kB      ← 溢出 168MB

-- 步骤 2：当前 work_mem
SHOW work_mem;

 work_mem
----------
 4MB

-- 步骤 3：用 SET LOCAL 验证提高后是否还溢出
BEGIN;
SET LOCAL work_mem = '256MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', created_at) AS d, status, count(*), sum(amount)
FROM orders
WHERE created_at >= now() - interval '90 days'
GROUP BY 1, 2
ORDER BY 1 DESC;
ROLLBACK;

   Sort Method: quicksort  Memory: 186400kB      ← 全在内存了

-- 步骤 4：只给报表角色固化（不影响 OLTP 会话）
ALTER ROLE report_user SET work_mem = '256MB';

-- 步骤 5：核对
SELECT rolname, rolconfig FROM pg_roles WHERE rolname = 'report_user';

   rolname    |          rolconfig
--------------+-----------------------------
 report_user  | {work_mem=256MB}
(1 row)

场景二：哈希连接批次过多，定位并修复

-- 步骤 1：观察 Batches
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days';

 Hash Join  (cost=... rows=...) (actual time=... rows=... loops=1)
   ...
   ->  Hash  (cost=... rows=...)
         Buckets: 131072  Batches: 8  Memory Usage: 8192kB   ← 分了 8 批

-- 步骤 2：Batches > 1 说明哈希表超出 work_mem × hash_mem_multiplier
SHOW work_mem;
SHOW hash_mem_multiplier;

  work_mem
-----------
 4MB

 hash_mem_multiplier
---------------------
 2

额度 = 4MB × 2 = 8MB。哈希表需要 64MB 左右 → 分 8 批。

-- 步骤 3：验证提高 work_mem 后的效果
BEGIN;
SET LOCAL work_mem = '128MB';   -- 哈希额度变 256MB
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days';
ROLLBACK;

   ->  Hash  (...)
         Buckets: 131072  Batches: 1  Memory Usage: 67328kB   ← 单批，无溢出

-- 步骤 4：若并发不高，适度提高全局值并监控临时文件
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

场景三：全库临时文件暴涨的排查

-- 步骤 1：确认临时文件总量
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database ORDER BY temp_bytes DESC LIMIT 5;

 datname | temp_files | temp_size
---------+------------+-----------
 myapp   |      92144 | 118 GB        ← 异常
(1 row)

-- 步骤 2：定位「谁在写临时文件」（需 pg_stat_statements）
SELECT queryid,
       calls,
       temp_blks_written,
       pg_size_pretty(temp_blks_written::bigint * 8192) AS temp_written,
       left(query, 80) AS query_snippet
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

   queryid   | calls | temp_blks_written | temp_written |           query_snippet
-------------+-------+-------------------+--------------+----------------------------------
 7234951002  |  1840 |            482100 | 3766 MB      | SELECT ... ORDER BY embedding <-> ...
(1 row)

-- 步骤 3：看看这条查询由谁在跑
SELECT pid, usename, state, query_start, left(query, 60)
FROM pg_stat_activity
WHERE query ILIKE '%ORDER BY embedding%' AND state = 'active';

-- 步骤 4：针对性治理（会话级或角色级，不动全局）
-- 方案 A：给对应角色提高 work_mem
ALTER ROLE search_user SET work_mem = '256MB';

-- 方案 B：给该 SQL 的执行事务用 SET LOCAL（应用侧改写）
-- BEGIN; SET LOCAL work_mem = '256MB'; ...; COMMIT;

-- 步骤 5：设置临时文件总量保护，防止写爆磁盘
ALTER SYSTEM SET temp_file_limit = '50GB';
SELECT pg_reload_conf();

-- 步骤 6：重置统计基线，验证改善
SELECT pg_stat_reset();
-- 观察一段时间后重新查询 temp_files

注意事项

不要把 work_mem 理解成「每个连接的内存上限」。 它是每个排序/哈希操作的额度。一个会话可以有多个操作，每个都能用满。

-- 错误估算：max_connections=500, work_mem=128MB → 以为最多 64GB
-- 实际：每个查询可能有 5 个哈希操作 → 500 × 5 × 128MB = 320GB

不要在 OLTP 高并发库上盲目调大全局 work_mem。 并发是乘数，会放大到 OOM。

# 不推荐：500 连接的 OLTP 库设 256MB
work_mem = '256MB'
# 推荐：全局保守 + 角色级放大
work_mem = '8MB'
# ALTER ROLE report_user SET work_mem = '256MB';

不要忘记哈希操作还有 hash_mem_multiplier 这个倍数。 哈希的实际额度是 work_mem × hash_mem_multiplier。

-- 错误！以为哈希只用 work_mem
SET work_mem = '64MB';
-- 实际哈希可用 64MB × 2.0 = 128MB

不要漏算并行 worker 的那几份 work_mem。 每个并行 worker 各自拥有一份完整额度。

-- 单查询内存 ≈ (1 + Workers Launched) × work_mem
EXPLAIN (ANALYZE) SELECT count(*) FROM big_table WHERE x > 0;
-- 看输出里的 Workers Launched

不要用 SET（非 LOCAL）在长连接里临时调大。 连接池复用的会话可能一直保留这个错误值。

-- 危险：连接池里的会话被污染
SET work_mem = '1GB';
-- 推荐：只影响当前事务
SET LOCAL work_mem = '1GB';

不要把「没有临时文件」当作「work_mem 设对了」。 可能只是当前查询恰好不大，或统计没重置。

-- 需要重置统计后再观察
SELECT pg_stat_reset();
-- 或者看单条语句的 temp_blks_written

不要忽略 temp_file_limit 的兜底作用。 失控查询可能写满磁盘，导致整个实例不可写。

# 推荐：给单会话临时文件设上限
temp_file_limit = '20GB'

不要把 temp_buffers 和 work_mem 混为一谈。 前者管临时表页缓存，后者管排序/哈希内存，互不替代。

-- 错误！以为调大 temp_buffers 能解决排序溢出
SET temp_buffers = '256MB';
-- 排序溢出仍由 work_mem 决定

不要在容器里忽略内存限额。 容器的 cgroup 限制可能远小于宿主机内存，work_mem 按宿主机算会 OOM。

# 先看容器真实限额
cat /sys/fs/cgroup/memory.max

# 按限额规划，而非宿主机总内存
work_mem = '8MB'

不要指望 ALTER SYSTEM 后已存在的会话立刻变小。 若该会话此前被 SET 过，会保留自己的值。

-- 会话内需显式 RESET
RESET work_mem;
SHOW work_mem;

不要在未验证的情况下就改生产配置。 先用 SET LOCAL 在事务里跑 EXPLAIN (ANALYZE, BUFFERS) 验证。

BEGIN;
SET LOCAL work_mem = '256MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
ROLLBACK;

不要只盯着 work_mem 而忽略查询本身。 有些溢出其实是查询写法导致的（如巨大 IN、笛卡尔积、缺失索引），提高内存只是掩盖问题。

-- 先看是否缺索引
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- 再看 Batches / Sort Method，判断是内存问题还是计划问题

最佳实践

全局取保守值（OLTP 4MB~16MB，混合负载 16MB~64MB），按角色差异化放大。

# postgresql.conf
work_mem = '8MB'

ALTER ROLE report_user SET work_mem = '256MB';
ALTER ROLE etl_user    SET work_mem = '512MB';

用 SET LOCAL 处理偶发大查询，避免污染连接池会话。

BEGIN;
SET LOCAL work_mem = '256MB';
SELECT ...;   -- 大报表查询
COMMIT;

把 work_mem 的估算纳入「最坏内存」公式，并留安全余量。

最坏内存 ≈ 峰值并发会话数 × 每会话并发操作数 × work_mem × 哈希倍数
建议：最坏内存 ≤ 物理内存（或容器限额）的 60%

开启 log_temp_files，用日志持续发现溢出。

# 记录所有超过 64MB 的临时文件
log_temp_files = '64MB'

用 pg_stat_statements 的 temp_blks_written 排序，优先治理写临时文件最多的 SQL。

SELECT queryid, calls, temp_blks_written, left(query, 80)
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

哈希溢出优先考虑调 hash_mem_multiplier（在 work_mem 已较大时），避免全局内存压力。

work_mem = '64MB'
hash_mem_multiplier = 4.0     # 哈希额度变 256MB，排序额度仍 64MB

设置 temp_file_limit 兜底，防止单会话写爆磁盘。

temp_file_limit = '20GB'

临时文件与临时表放在独立高速盘（temp_tablespaces），降低溢出代价。

-- 创建 SSD 表空间用于临时对象
CREATE TABLESPACE temp_ssd LOCATION '/data/ssd/pg_temp';
ALTER SYSTEM SET temp_tablespaces = 'temp_ssd';

定期重置统计、建立基线，观察 temp_bytes 趋势。

SELECT pg_size_pretty(temp_bytes) AS temp_size FROM pg_stat_database
WHERE datname = current_database();
-- 定期记录，观察是否恶化

容器环境按 cgroup 限额核算，而非宿主机内存。

cat /sys/fs/cgroup/memory.max

# 按限额的较小比例设置
work_mem = '8MB'

把 work_mem 的关键改动纳入版本库与变更评审。

# postgresql.conf（纳入 git）
# ---- 查询工作内存 ----
work_mem = '8MB'
hash_mem_multiplier = 2.0
# ---- 保护 ----
temp_file_limit = '20GB'
log_temp_files = '64MB'

用 EXPLAIN (ANALYZE, BUFFERS) 验证改动前后临时文件是否消失。

-- 改动前：Sort Method: external merge
-- 改动后：Sort Method: quicksort  Memory: ...
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

改动后用 pending_restart 确认无需重启。

SELECT name, pending_restart FROM pg_settings WHERE pending_restart;
-- work_mem 为 user 上下文，应为 0 行

下一节

继续学习：maintenance_work_mem 维护内存

参考链接

• PostgreSQL 官方文档：Resource Consumption / Memory（runtime-config-resource）
• PostgreSQL 官方文档：Using EXPLAIN（using-explain）
• PostgreSQL 官方文档：The pg_settings View（view-pg-settings）
• PostgreSQL 官方文档：The Statistics Collector（monitoring-stats）
• PostgreSQL 官方文档：Monitoring Database Activity（monitoring）
• PostgreSQL 官方文档：Error Reporting and Logging（runtime-config-logging）
• PostgreSQL 官方文档：pg_stat_statements（pgstatstatements）
• PostgreSQL 官方文档：Disk Usage / temp_files（diskusage）