maintenance_work_mem 维护内存

maintenance_work_mem 是「维护类操作」专用的内存额度——VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY、REINDEX 等都靠它。它和 work_mem 名字很像但分工完全不同：维护操作每个会话同一时刻只跑一个，所以可以设得比 work_mem 大得多。但要注意一个坑：autovacuum 会同时开多个工作进程，每个都能用满这个值，所以全局设太大反而危险。

前置知识

阅读本节前，建议先了解：work_mem 工作内存

基础概念

它是什么

项目	说明
参数名	maintenance_work_mem
默认值	64MB
单位	不写单位时按 kB 解释
上下文	user（普通用户可 SET，reload 生效）
作用对象	维护操作：VACUUM、CREATE INDEX、REINDEX、ALTER TABLE ADD FOREIGN KEY、CLUSTER 等
超限行为	溢出到临时文件 / 分多趟扫描
是否可设很大	✅ 可以（每会话同时只有一个维护操作）
关键风险	autovacuum 会并发多个工作进程，各自一份

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

          name          | setting | unit | context | boot_val | min_val | max_val
------------------------+---------+------+---------+----------+---------+---------
 maintenance_work_mem   | 65536   | kB   | user    | 65536    | 1024    | 2147483647
(1 row)

默认 65536 kB = 64MB。最小值 1024 kB = 1MB。

它与 work_mem 的本质区别

┌───────────────────────────────────────────────────────────────┐
│  work_mem                        maintenance_work_mem          │
├───────────────────────────────────────────────────────────────┤
│  服务「查询执行」                  服务「维护操作」                │
│  · Sort                          · VACUUM                     │
│  · Hash Join                     · CREATE INDEX               │
│  · HashAgg                       · REINDEX                    │
│  · Memoize                       · ALTER TABLE ADD FK         │
│                                  · CLUSTER                    │
│                                                                │
│  一个查询可同时有多个操作            每个会话同一时刻只有一个        │
│  → 每操作一份，并发乘数大           → 每会话一份，可设大得多        │
│                                                                │
│  例：3 个哈希 × 64MB = 192MB       例：1 个索引 × 1GB = 1GB     │
└───────────────────────────────────────────────────────────────┘

-- 两者并存时的默认值对比
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('work_mem', 'maintenance_work_mem')
ORDER BY name;

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

为什么可以比 work_mem 大得多

官方明确说明：「由于一个数据库会话同一时刻只能执行一个这样的操作，而且通常不会有很多这样的操作并发执行，所以可以安全地把这个值设得明显大于 work_mem。」较大的值能显著加快 VACUUM 和数据库恢复（restore）的速度。

哪些操作会用到它

操作	典型命令	说明
清理	VACUUM（手动/VACUUM FULL 前段）	收集死元组、更新可见性映射
建索引	CREATE INDEX / REINDEX	排序索引项；越大越快
外键校验	ALTER TABLE ... ADD FOREIGN KEY	验证引用完整性
聚簇	CLUSTER / ALTER TABLE ... CLUSTER ON	按索引重排物理顺序
分析	ANALYZE	采样与统计计算（同时受 vacuum_buffer_usage_limit 影响）
备份恢复	pg_restore 建索引阶段	恢复速度的关键参数

-- CREATE INDEX 会用到 maintenance_work_mem
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders ORDER BY created_at;   -- 对比：排序用 work_mem

-- 建索引（真实执行）
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

autovacuum 的并发放大

这是 maintenance_work_mem 最重要的风险点。

autovacuum_max_workers = 3（默认）
maintenance_work_mem = 2GB

最坏内存 = 3 × 2GB = 6GB   ← 自动清理同时开 3 个进程，各用一份

SELECT name, setting, unit FROM pg_settings
WHERE name IN ('autovacuum_max_workers', 'autovacuum_work_mem', 'maintenance_work_mem')
ORDER BY name;

         name          | setting | unit
-----------------------+---------+------
 autovacuum_max_workers| 3       |
 autovacuum_work_mem   | -1      | kB
 maintenance_work_mem  | 65536   | kB
(3 rows)

autovacuum_work_mem 默认 -1 表示「跟随 maintenance_work_mem」

autovacuum_work_mem 默认 -1，意味着 autovacuum 工作进程使用 maintenance_work_mem 的值。如果你把 maintenance_work_mem 设成 2GB 而没单独设置 autovacuum_work_mem，那么 每个 autovacuum 工作进程都能用 2GB，3 个进程就是 6GB。

与相关参数的关系

参数	默认值	上下文	作用
maintenance_work_mem	64MB	user	维护操作内存额度
autovacuum_work_mem	-1	sighup	autovacuum 专用额度；-1 = 用 maintenance_work_mem
autovacuum_max_workers	3	sighup	autovacuum 并发工作进程数
vacuum_buffer_usage_limit	2MB	user	VACUUM/ANALYZE 的缓冲环大小
work_mem	4MB	user	查询执行内存额度
max_parallel_maintenance_workers	2	user	维护操作可用并行工作进程数

SELECT name, setting, unit FROM pg_settings
WHERE name IN ('max_parallel_maintenance_workers', 'vacuum_buffer_usage_limit', 'autovacuum_max_workers');

                name                | setting | unit
------------------------------------+---------+------
 autovacuum_max_workers             | 3       |
 max_parallel_maintenance_workers   | 2       |
 vacuum_buffer_usage_limit          | 2048    | kB
(3 rows)

并行建索引会叠加内存

PG11+ 的 CREATE INDEX 支持并行。max_parallel_maintenance_workers（默认 2）个并行工作进程各自可用 maintenance_work_mem，所以总内存可能是 (1 + 并行数) × maintenance_work_mem。

语法与用法

配置写法

# postgresql.conf
maintenance_work_mem = '64MB'        # 默认值

# 专用数据库服务器可放大
maintenance_work_mem = '1GB'

# 若想控制 autovacuum，单独设置它
autovacuum_work_mem = '256MB'

单位同样是 kB

maintenance_work_mem = 65536        # 65536 kB = 64MB
maintenance_work_mem = '1GB'        # 显式单位，推荐

修改与生效

-- 全局修改（reload 生效）
ALTER SYSTEM SET maintenance_work_mem = '1GB';
SELECT pg_reload_conf();

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

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

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

会话级临时提高（推荐做法）

-- 只对本次建索引会话提高，不影响 autovacuum
SET maintenance_work_mem = '2GB';
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- 用完还原
RESET maintenance_work_mem;

-- 事务级（更安全）
BEGIN;
SET LOCAL maintenance_work_mem = '2GB';
CREATE INDEX idx_orders_status ON orders (status);
COMMIT;

为特定角色设置

-- 给 DBA/ETL 角色统一放大（他们常做建索引、批量导入）
ALTER ROLE etl_user SET maintenance_work_mem = '2GB';

-- 查看
SELECT rolname, rolconfig FROM pg_roles WHERE rolname = 'etl_user';

  rolname  |           rolconfig
-----------+-------------------------------
 etl_user  | {maintenance_work_mem=2GB}
(1 row)

控制 autovacuum 的内存

-- 单独给 autovacuum 设一个较小的值，避免放大
ALTER SYSTEM SET autovacuum_work_mem = '256MB';
SELECT pg_reload_conf();

-- 核验（注意这个值是 sighup 上下文）
SELECT name, setting, unit, context, source
FROM pg_settings WHERE name = 'autovacuum_work_mem';

        name         | setting | unit | context |       source
---------------------+---------+------+---------+--------------------
 autovacuum_work_mem | 262144  | kB   | sighup  | configuration file
(1 row)

autovacuum_work_mem 只能在配置文件或命令行设置

官方说明：autovacuum_work_mem「只能在 postgresql.conf 文件或服务器命令行中设置」。它不能在会话里 SET。用 ALTER SYSTEM 是写进 postgresql.auto.conf，属于配置文件范畴，可行。

调整 VACUUM / ANALYZE 的缓冲环

-- 全局：VACUUM/ANALYZE 使用的缓冲环大小（默认 2MB）
ALTER SYSTEM SET vacuum_buffer_usage_limit = '8MB';

-- 单条命令覆盖
VACUUM (BUFFER_USAGE_LIMIT '64MB') orders;
ANALYZE (BUFFER_USAGE_LIMIT '64MB') orders;

-- 0 表示允许使用任意数量的 shared_buffers（不设限，谨慎）
VACUUM (BUFFER_USAGE_LIMIT 0) orders;

与相关命令的对照

-- 全局
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- 库级
ALTER DATABASE myapp SET maintenance_work_mem = '1GB';

-- 角色级
ALTER ROLE etl_user SET maintenance_work_mem = '2GB';

-- 会话级
SET maintenance_work_mem = '2GB';

-- 事务级
SET LOCAL maintenance_work_mem = '2GB';

-- 重置
ALTER SYSTEM RESET maintenance_work_mem;
ALTER DATABASE myapp RESET maintenance_work_mem;
ALTER ROLE etl_user RESET maintenance_work_mem;
RESET maintenance_work_mem;

详细说明

一、名字里的 maintenance 指什么

「维护（maintenance）」在 PostgreSQL 语境下特指不产生新数据、只维护已有数据结构的操作：清理死行、重建索引、校验约束、重排物理存储。这些操作都需要对大量数据进行排序或哈希处理，因此都需要一块工作内存——这就是 maintenance_work_mem。

查询执行（work_mem）          数据维护（maintenance_work_mem）
      │                                │
      ├─ ORDER BY                      ├─ VACUUM 收集死元组
      ├─ GROUP BY                       ├─ CREATE INDEX 排序索引项
      └─ JOIN                           ├─ ALTER TABLE ADD FK 校验
                                        └─ CLUSTER 按索引重排

二、CREATE INDEX 为什么需要大内存

建索引的核心是对索引键排序。内存越大，排序越快、溢出越少。

maintenance_work_mem 小（如 64MB）
     │
     ▼
排序溢出 → 写临时文件 → 多趟归并 I/O
     │
     ▼
建索引慢（可能数小时）

─────────────────────────────────────

maintenance_work_mem 大（如 2GB）
     │
     ▼
排序尽量在内存完成
     │
     ▼
建索引快（显著缩短）

-- 建索引前临时放大（不影响其他会话）
SET maintenance_work_mem = '2GB';
\timing on
CREATE INDEX idx_orders_created_at ON orders (created_at);
RESET maintenance_work_mem;

Time: 18422.317 ms (00:18.422)

三、VACUUM 的内存行为

普通 VACUUM 并不像 CREATE INDEX 那样需要大块排序内存，它更依赖缓冲环（buffer access strategy）——记在维护内存里，但实际由 vacuum_buffer_usage_limit 控制。

-- 观察 VACUUM 进度（PG12+）
SELECT * FROM pg_stat_progress_vacuum;

  pid  | datid | datname | relid | phase              | heap_blks_total | heap_blks_scanned
-------+-------+---------+-------+--------------------+-----------------+------------------
 12873 | 16384 | myapp   | 24576 | vacuuming heap     |         1048576 |           245760

-- 手动 VACUUM 并用大缓冲环加速
VACUUM (VERBOSE, BUFFER_USAGE_LIMIT '128MB') orders;

INFO:  vacuuming "public.orders"
INFO:  "orders": found 0 removable, 1284000 nonremovable row versions in 1048576 pages

VACUUM FULL 不走 maintenance_work_mem

VACUUM FULL 会重写整表（相当于 CREATE TABLE AS + 替换），它受 maintenance_work_mem 影响很小，但会导致表级排他锁、产生大量 I/O。日常应优先用普通 VACUUM。

四、ALTER TABLE 的约束校验

添加外键或 CHECK 约束时，PostgreSQL 需要全表扫描 + 排序/哈希来校验。

-- 添加外键需校验全表，maintenance_work_mem 越大越快
SET maintenance_work_mem = '1GB';
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);

ALTER TABLE

-- 监控这类长时间维护操作
SELECT pid, now() - query_start AS duration, left(query, 80)
FROM pg_stat_activity
WHERE query ILIKE '%ALTER TABLE%ADD CONSTRAINT%'
ORDER BY duration DESC;

五、autovacuum 的放大效应详解

┌──────────────────────────────────────────────────────────────┐
│  自动清理进程组（autovacuum_max_workers = 3）                   │
├──────────────────────────────────────────────────────────────┤
│                                                              │
│    worker 1              worker 2              worker 3      │
│   [ 维护内存 ]           [ 维护内存 ]           [ 维护内存 ]    │
│      64MB                   64MB                   64MB       │
│                                                              │
│   若 maintenance_work_mem = 2GB 且 autovacuum_work_mem = -1  │
│   → 每个 worker 各拿 2GB → 合计 6GB                            │
└──────────────────────────────────────────────────────────────┘

-- 安全做法：给 autovacuum 单独一个值
ALTER SYSTEM SET maintenance_work_mem = '2GB';       -- DBA 手动操作用
ALTER SYSTEM SET autovacuum_work_mem = '256MB';      -- autovacuum 专用，可控
SELECT pg_reload_conf();

-- 核验两者
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('maintenance_work_mem', 'autovacuum_work_mem', 'autovacuum_max_workers')
ORDER BY name;

        name         | setting | unit
---------------------+---------+------
 autovacuum_max_workers| 3      |
 autovacuum_work_mem | 262144  | kB
 maintenance_work_mem| 2097152 | kB
(3 rows)

六、并行维护操作的内存

PG11+ 支持并行建索引和并行 VACUUM（部分场景）。

-- 当前并行维护工作进程上限
SHOW max_parallel_maintenance_workers;

 max_parallel_maintenance_workers
----------------------------------
 2

总内存 ≈ (1 leader + max_parallel_maintenance_workers) × maintenance_work_mem
       ≈ (1 + 2) × 1GB = 3GB

-- 建索引时禁用并行（内存受限时）
SET max_parallel_maintenance_workers = 0;
CREATE INDEX idx_orders_x ON orders (customer_id);

七、恢复（restore）场景的取值

从 pg_dump 恢复时，建索引是耗时大头。官方建议恢复时可以临时调大。

# pg_restore 恢复期间，在目标库上临时提高
psql -c "ALTER SYSTEM SET maintenance_work_mem = '2GB'; SELECT pg_reload_conf();"
pg_restore -d myapp backup.dump
psql -c "ALTER SYSTEM RESET maintenance_work_mem; SELECT pg_reload_conf();"

-- 或者只在恢复会话里设
SET maintenance_work_mem = '2GB';
-- 然后运行 pg_restore（同一会话）

恢复时也别忘了 max_wal_size

大批量写入（含建索引产生的大量 WAL）会频繁触发检查点。恢复前把 max_wal_size 也调大，效果更明显。

八、REINDEX 与 CLUSTER

-- REINDEX：重建索引，受 maintenance_work_mem 影响
SET maintenance_work_mem = '2GB';
REINDEX TABLE CONCURRENTLY orders;

-- CLUSTER：按索引物理重排表，也用它
SET maintenance_work_mem = '1GB';
CLUSTER orders USING idx_orders_created_at;

-- 监控 CLUSTER 进度
SELECT * FROM pg_stat_progress_cluster;

九、与 vacuum_buffer_usage_limit 的分工

参数	控制的资源	默认值
maintenance_work_mem	维护操作的工作内存（排序/哈希）	64MB
vacuum_buffer_usage_limit	VACUUM/ANALYZE 使用的共享缓冲环大小	2MB

VACUUM 执行时的两块内存：
  ① 工作内存（maintenance_work_mem / autovacuum_work_mem）
     → 存放待处理的元组、排序结构
  ② 缓冲环（vacuum_buffer_usage_limit）
     → 限制 VACUUM 从 shared_buffers 里「圈走」多少页，
       避免把热点数据挤出缓存

-- 典型搭配
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET vacuum_buffer_usage_limit = '8MB';
SELECT pg_reload_conf();

十、cost-based vacuum delay：给维护操作「踩刹车」

maintenance_work_mem 决定的是维护操作能用多少内存；而 VACUUM / ANALYZE 对系统的另一种冲击是 I/O。「基于代价的清理延迟（cost-based vacuum delay）」就是用来限制这份 I/O 冲击的节流机制。

┌────────────────────────────────────────────────────────────────┐
│  机制：系统内部维护一个「代价计数器」                              │
│                                                                │
│  每处理一个页，按类型累加代价：                                   │
│    命中 shared_buffers  → vacuum_cost_page_hit   （默认 1）      │
│    需从磁盘读取          → vacuum_cost_page_miss  （默认 2）      │
│    把脏页写回            → vacuum_cost_page_dirty （默认 20）     │
│                                                                │
│  累计代价 ≥ vacuum_cost_limit（默认 200）                        │
│     → 进程休眠 vacuum_cost_delay（默认 0 = 关闭）                  │
│     → 计数器归零，继续                              │
└────────────────────────────────────────────────────────────────┘

-- 查看全部节流相关参数
SELECT name, setting, unit, context FROM pg_settings
WHERE name IN (
  'vacuum_cost_delay','vacuum_cost_page_hit','vacuum_cost_page_miss',
  'vacuum_cost_page_dirty','vacuum_cost_limit',
  'autovacuum_vacuum_cost_delay','autovacuum_vacuum_cost_limit'
)
ORDER BY name;

             name              | setting | unit |  context
-------------------------------+---------+------+-----------
 autovacuum_vacuum_cost_delay  | 2       | ms   | sighup
 autovacuum_vacuum_cost_limit  | -1      |      | sighup
 vacuum_cost_delay             | 0       | ms   | superuser
 vacuum_cost_limit             | 200     |      | superuser
 vacuum_cost_page_dirty        | 20      |      | superuser
 vacuum_cost_page_hit          | 1       |      | superuser
 vacuum_cost_page_miss         | 2       |      | superuser
(7 rows)

参数	默认值	说明
vacuum_cost_delay	0	达到代价上限后的休眠时长；默认 0 表示关闭手动 VACUUM 的节流
vacuum_cost_page_hit	1	处理「在共享缓冲中命中」的页的估算代价
vacuum_cost_page_miss	2	处理「需从磁盘读取」的页的估算代价
vacuum_cost_page_dirty	20	处理「会被修改为脏页、需要回写」的页的额外代价
vacuum_cost_limit	200	累计代价达到该值就休眠一次
autovacuum_vacuum_cost_delay	2（ms）	autovacuum 专用的延迟值；-1 表示改用 vacuum_cost_delay
autovacuum_vacuum_cost_limit	-1	autovacuum 专用的代价上限；-1 表示改用 vacuum_cost_limit，且在多个 worker 间按比例分配

默认只对 autovacuum 生效，手动 VACUUM 默认不节流

官方明确说明：cost-based vacuum delay 对手动执行的 VACUUM 默认是关闭的（因为 vacuum_cost_delay = 0）。要让手动 VACUUM 也节流，必须把 vacuum_cost_delay 设为非零值。

反过来说：autovacuum_vacuum_cost_delay 默认是 2ms，所以 autovacuum 是默认节流的——它不会把 I/O 打满，也就解释了为什么有时 autovacuum 看起来「跑得很慢」。

实际休眠时长 = vacuum_cost_delay × 累计余额 / vacuum_cost_limit
              上限为 vacuum_cost_delay × 4

为什么要有「×4」的上限：某些持有关键锁的操作不能被延迟，导致代价可能
远超过 limit。官方据此把实际延迟封顶为 vacuum_cost_delay × 4，
避免出现毫无意义的超长休眠。

# postgresql.conf —— 让自动清理更「温和」，减少对在线业务的影响
autovacuum_vacuum_cost_delay = 10ms     # 默认 2ms，调大 = 更温和但更慢
autovacuum_vacuum_cost_limit = 2000     # 默认 -1（用 vacuum_cost_limit = 200）

# postgresql.conf —— 反过来：希望 autovacuum 尽快跑完（I/O 富余时）
autovacuum_vacuum_cost_delay = 0        # 关闭节流，让 autovacuum 全速跑
autovacuum_vacuum_cost_limit = 10000    # 或大幅提高上限

-- 单表级别覆盖（仅适用于 autovacuum 触发时）
ALTER TABLE big_orders SET (
  autovacuum_vacuum_cost_delay = 0,
  autovacuum_vacuum_cost_limit = 8000
);

调优思路：先看 autovacuum 到底是「慢」还是「弱」

如果表膨胀持续增长而 autovacuum 一直在跑，要区分两种原因：

• 被节流：autovacuum_vacuum_cost_delay 太大 / cost_limit 太小 → 调这两个参数（pg_stat_progress_vacuum 能看到进度慢）；
• 内存不足：autovacuum_work_mem 太小导致多次扫描 → 调内存参数。

两者症状相似但解法相反，先看 pg_stat_progress_vacuum 的推进速度再决定。

-- 观察当前 autovacuum 的进度与阶段
SELECT pid, datname, relid::regclass AS table_name, phase,
       heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

failsafe 触发时会强制关闭节流

官方说明：当 vacuum_failsafe_age（默认 16 亿事务）触发时，任何生效中的 cost-based delay 都会被取消，跳过非必要的维护任务（如索引清理），并禁用缓冲环——让 VACUUM 尽快完成以避免事务 ID 回卷。这种情况下内存与 I/O 占用会突然升高，是预期行为。

十一、参数生效与快照

-- maintenance_work_mem 是 user 上下文，reload 生效
ALTER SYSTEM SET maintenance_work_mem = '1GB';
SELECT pg_reload_conf();

-- 查看来源
SELECT name, setting, unit, source, sourcefile, sourceline
FROM pg_settings WHERE name = 'maintenance_work_mem';

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

-- autovacuum_work_mem 是 sighup 上下文，同样 reload 生效
SELECT name, context, pending_restart FROM pg_settings
WHERE name IN ('maintenance_work_mem', 'autovacuum_work_mem');

        name         | context | pending_restart
---------------------+---------+-----------------
 autovacuum_work_mem | sighup  | f
 maintenance_work_mem| user    | f
(2 rows)

实战示例

场景一：大表建索引慢，临时放大加速

-- 步骤 1：观察现状（表大小）
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

 table_size | total_size
------------+------------
 42 GB      | 68 GB
(1 row)

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

 maintenance_work_mem
----------------------
 64MB

表 42GB，排序数据远超 64MB → 必然大量溢出。

-- 步骤 3：会话内放大后建索引（并发建，不锁表）
SET maintenance_work_mem = '2GB';
\timing on
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);
RESET maintenance_work_mem;

Time: 218440.912 ms (03:38.441)

-- 步骤 4：监控并行与进度
SELECT pid, phase, blocks_done, blocks_total,
       round(100.0 * blocks_done / nullif(blocks_total,0), 1) AS pct
FROM pg_stat_progress_create_index;

-- 步骤 5：核验索引已建立
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE relname = 'orders';

      indexrelname       |  size
-------------------------+--------
 orders_pkey             | 1856 MB
 idx_orders_created_at   | 2144 MB
(2 rows)

场景二：autovacuum 内存放大导致 OOM，定位并修复

现象：服务器内存告警，load 飙升，dmesg 出现 OOM killer 记录。

-- 步骤 1：检查当前内存相关配置
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('maintenance_work_mem', 'autovacuum_work_mem',
               'autovacuum_max_workers', 'shared_buffers')
ORDER BY name;

         name          | setting | unit
-----------------------+---------+------
 autovacuum_max_workers| 5       |
 autovacuum_work_mem   | -1      | kB      ← 跟随 maintenance_work_mem
 maintenance_work_mem  | 4194304 | kB      ← 4GB！
 shared_buffers        | 8388608 | kB
(4 rows)

计算最坏内存：
  autovacuum = 5 × 4GB = 20GB
  + shared_buffers 8GB
  + work_mem 并发
  远超服务器物理内存 → OOM

-- 步骤 2：确认 autovacuum 进程确实在跑
SELECT pid, backend_type, query_start, left(query, 60)
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

  pid  |    backend_type    |         query_start          |   left
-------+--------------------+------------------------------+------------------
 12873 | autovacuum worker  | 2026-09-29 03:12:44.123+08   | autovacuum: VACUU
 12874 | autovacuum worker  | 2026-09-29 03:12:45.221+08   | autovacuum: VACUU
 12875 | autovacuum worker  | 2026-09-29 03:12:46.019+08   | autovacuum: VACUU

-- 步骤 3：修复——拆分「手动维护内存」与「autovacuum 内存」
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET autovacuum_work_mem = '256MB';   -- autovacuum 专用，可控
SELECT pg_reload_conf();

-- 步骤 4：核验
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('maintenance_work_mem', 'autovacuum_work_mem')
ORDER BY name;

        name         | setting | unit
---------------------+---------+------
 autovacuum_work_mem | 262144  | kB
 maintenance_work_mem| 2097152 | kB
(2 rows)

新最坏内存：5 × 256MB = 1.25GB → 安全

-- 步骤 5：确认无 OOM 相关日志
-- 检查 postgresql.log 与 dmesg

场景三：数据库恢复提速

# 步骤 1：恢复前调整目标实例参数
psql -d postgres <<'SQL'
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET max_wal_size = '16GB';
ALTER SYSTEM SET autovacuum = off;          -- 恢复期间关闭自动清理
ALTER SYSTEM SET synchronous_commit = off;  -- 恢复期间可关闭同步提交
SELECT pg_reload_conf();
SQL

# 步骤 2：执行恢复
pg_restore -d myapp -j 4 --no-owner myapp.dump

# 步骤 3：恢复后还原参数
psql -d postgres <<'SQL'
ALTER SYSTEM RESET maintenance_work_mem;
ALTER SYSTEM RESET max_wal_size;
ALTER SYSTEM RESET autovacuum;
ALTER SYSTEM RESET synchronous_commit;
SELECT pg_reload_conf();
SQL

-- 步骤 4：恢复后执行一次全库 ANALYZE，更新统计
ANALYZE;

恢复期间不要忘记还原参数

autovacuum = off、synchronous_commit = off 只适合恢复期间临时使用。恢复完成必须还原，否则生产环境会失去自动清理保护、或降低持久性保证。

注意事项

不要把 maintenance_work_mem 设得很大却忽略 autovacuum 的放大。 它同时是 autovacuum 工作进程的额度（当 autovacuum_work_mem = -1 时）。

# 危险：autovacuum_max_workers=5，等于最多 5 × 4GB = 20GB
maintenance_work_mem = '4GB'
# 推荐：拆分
maintenance_work_mem = '2GB'
autovacuum_work_mem = '256MB'

不要期望 SET maintenance_work_mem 能影响 autovacuum。 autovacuum 是独立后台进程，不受会话级 SET 影响。

-- 无效！autovacuum 不会有任何变化
SET maintenance_work_mem = '2GB';
-- 要影响 autovacuum 必须改 autovacuum_work_mem（配置文件或 ALTER SYSTEM）

不要试图在会话里 SET autovacuum_work_mem。 官方规定它只能在配置文件或命令行设置。

-- 错误！会报错或无效
SET autovacuum_work_mem = '256MB';
-- 正确：
ALTER SYSTEM SET autovacuum_work_mem = '256MB';

不要忘记并行维护操作会叠加内存。 max_parallel_maintenance_workers 个并行进程各自一份。

-- 总内存可能 = (1 + max_parallel_maintenance_workers) × maintenance_work_mem
SHOW max_parallel_maintenance_workers;

不要把 VACUUM FULL 当成日常维护。 它重写整表、持排他锁，且几乎不受 maintenance_work_mem 帮助。

-- 不推荐日常使用
VACUUM FULL orders;
-- 推荐：普通 VACUUM + 必要时 pg_repack
VACUUM (VERBOSE) orders;

不要在没有监控的情况下长时间运行维护操作。 建索引、加外键可能持锁并长时间运行。

-- 监控长时间维护操作
SELECT pid, now() - query_start AS duration, state, left(query, 80)
FROM pg_stat_activity
WHERE query ILIKE '%CREATE INDEX%' OR query ILIKE '%VACUUM%'
ORDER BY duration DESC;

不要在 shared_buffers 已占大比例内存时还把 maintenance_work_mem 设得极大。 两者之和不能超过物理内存。

内存预算示例（64GB 机器）：
  shared_buffers        16GB
  maintenance_work_mem   4GB
  autovacuum_work_mem    1GB（× workers）
  work_mem 最坏情况      若干
  操作系统 + 其他        剩余

不要把 maintenance_work_mem 与 vacuum_buffer_usage_limit 混为一谈。 前者是工作内存，后者是共享缓冲环。

-- 两者独立
maintenance_work_mem        -- 工作内存
vacuum_buffer_usage_limit   -- 缓冲环

不要忘记 ALTER SYSTEM 需要 reload 才生效。

ALTER SYSTEM SET maintenance_work_mem = '2GB';
SELECT pg_reload_conf();   -- 别忘了这步

不要在恢复期间忘记还原 autovacuum、synchronous_commit 等临时参数。

-- 恢复完成后
ALTER SYSTEM RESET autovacuum;
ALTER SYSTEM RESET synchronous_commit;
SELECT pg_reload_conf();

不要忽略 min_val 限制。 maintenance_work_mem 最小值是 1MB，设更小会被拒绝或截断。

-- 会被拒绝/调整
SET maintenance_work_mem = '512kB';

不要在 OLTP 高峰期做重维护操作。 建索引虽然 CONCURRENTLY 不锁写，但仍消耗大量 I/O 与内存。

-- 选择低峰期
CREATE INDEX CONCURRENTLY idx_orders_x ON orders (customer_id);

最佳实践

全局取适中值（如 256MB~1GB），不要一上来就设几个 GB。

# postgresql.conf
maintenance_work_mem = '512MB'

单独设置 autovacuum_work_mem，把 autovacuum 的内存控制在可预期范围。

maintenance_work_mem = '1GB'
autovacuum_work_mem = '256MB'

大表建索引、加约束时用会话级 SET 临时放大。

SET maintenance_work_mem = '2GB';
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);
RESET maintenance_work_mem;

为 ETL / DBA 角色固化较大的值。

ALTER ROLE etl_user SET maintenance_work_mem = '2GB';

把「手动维护内存」与「autovacuum 内存」纳入内存预算表，留足余量。

内存预算（64GB 机器）：
  shared_buffers         16GB
  maintenance_work_mem    2GB（同时最多 1~2 个手动维护）
  autovacuum_work_mem     256MB × 3 workers
  work_mem 峰值余量        4GB
  操作系统 + 其他          剩余

恢复（restore）前临时调大 maintenance_work_mem 与 max_wal_size、关闭 autovacuum。

ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET max_wal_size = '16GB';
ALTER SYSTEM SET autovacuum = off;
SELECT pg_reload_conf();

用 pg_stat_progress_create_index / pg_stat_progress_vacuum 监控长维护操作。

SELECT * FROM pg_stat_progress_create_index;
SELECT * FROM pg_stat_progress_vacuum;

用 CREATE INDEX CONCURRENTLY 替代普通 CREATE INDEX，避免长时间锁写。

CREATE INDEX CONCURRENTLY idx_orders_x ON orders (customer_id);

建索引完成后跑一次 ANALYZE，让优化器立刻用上新索引的统计。

CREATE INDEX CONCURRENTLY idx_orders_x ON orders (customer_id);
ANALYZE orders;

内存受限时可临时禁用并行维护。

SET max_parallel_maintenance_workers = 0;
CREATE INDEX idx_orders_x ON orders (customer_id);

把关键维护参数纳入监控，防止被误改。

SELECT name, setting, unit, source FROM pg_settings
WHERE name IN ('maintenance_work_mem', 'autovacuum_work_mem',
               'autovacuum_max_workers', 'max_parallel_maintenance_workers')
ORDER BY name;

把参数写进 postgresql.conf 并纳入版本库。

# postgresql.conf（纳入 git）
# ---- 维护内存 ----
maintenance_work_mem = '1GB'
autovacuum_work_mem = '256MB'
# ---- VACUUM 缓冲环 ----
vacuum_buffer_usage_limit = '8MB'

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

SELECT name, pending_restart FROM pg_settings WHERE pending_restart;
-- maintenance_work_mem（user）、autovacuum_work_mem（sighup）均应为 0 行

下一节

继续学习：其它内存参数（temp_buffers、后端缓冲等）

参考链接

• PostgreSQL 官方文档：Resource Consumption / Memory（runtime-config-resource）
• PostgreSQL 官方文档：Routine Vacuuming / Autovacuum（routine-vacuuming）
• PostgreSQL 官方文档：VACUUM（sql-vacuum）
• PostgreSQL 官方文档：CREATE INDEX（sql-createindex）
• PostgreSQL 官方文档：The pg_settings View（view-pg-settings）
• PostgreSQL 官方文档：Progress Reporting（progress-reporting）
• PostgreSQL 官方文档：Monitoring Database Activity（monitoring-stats）
• PostgreSQL 官方文档：pg_restore（app-pgrestore）