PHP小码农

Appearance

VACUUM 清理 ​

VACUUM 是 PostgreSQL 中用来回收死元组（dead tuple）占用空间、冻结老事务 ID、更新可见性映射（Visibility Map）的维护命令。PostgreSQL 的 MVCC 机制决定了 UPDATE 与 DELETE 不会原地修改数据，而是留下旧版本元组；这些元组必须由 VACUUM 清理，否则表会持续膨胀（bloat），最终拖慢扫描、浪费磁盘，甚至在极端情况下触发事务 ID 回卷（XID wraparound）导致数据库强制停机。

本节从命令与运维的角度讲 VACUUM：有哪些参数、怎么诊断膨胀、自动清理为什么没跑、什么情况必须手工介入、以及如何安全地在生产环境执行。MVCC 层面的机制细节请参考架构章节。

前置知识

阅读本节前，建议先了解：ANALYZE 统计更新

基础概念 ​

概念术语说明
死元组Dead Tuple对所有事务都不可见的旧版本行，等待回收
普通清理Plain VACUUM标记空间可复用，不缩小文件，不阻塞读写
完全清理VACUUM FULL整表重写，归还空间给操作系统，持排他锁
冻结Freeze将元组 xmin 置为特殊值，使其永不参与回卷判断
膨胀Bloat死元组与可复用空间占比过高导致表体积虚大
可见性映射VM（Visibility Map）标记「整页全可见」，是仅索引扫描的前提
空闲空间映射FSM（Free Space Map）记录每页剩余空间，供插入时复用
自动清理Autovacuum后台守护进程，按阈值自动触发
清理阈值autovacuum_vacuum_threshold触发自动清理的死元组绝对下限，默认 50
缩放因子autovacuum_vacuum_scale_factor触发自动清理的死元组比例，默认 0.2
事务 ID 回卷XID WraparoundXID 耗尽导致的保护性停机风险
冻结年龄age(relfrozenxid)表最老未冻结 XID 的距离，接近 20 亿需告警
清理进度pg_stat_progress_vacuum查看正在进行的清理进度
在线重整理pg_repack不持长时间排他锁的重整表方案

关于 VACUUM 需要先建立三个认识：

  • 它不是「删除垃圾」，而是「让空间可复用」。 普通 VACUUM 执行后文件大小往往不变，这是正常的。
  • 它必须定期执行。 不执行的后果不是表变大而已，而是冻结停止、回卷风险累积。
  • 它默认不阻塞业务。 普通 VACUUM 只取 ShareUpdateExclusiveLock，与读写兼容，可随时执行。
sql
-- 查看表的死元组与膨胀概况
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / greatest(n_live_tup + n_dead_tup, 1), 2) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;
text
 relname  | n_live_tup | n_dead_tup | dead_pct |         last_vacuum         |       last_autovacuum
----------+------------+------------+----------+-----------------------------+-----------------------------
 orders   |      11880 |       4210 |    26.17 | 2026-09-24 09:12:44.318+08  | 2026-09-24 10:05:19.771+08
 products |       2000 |         38 |     1.86 |                             | 2026-09-24 09:58:02.114+08
(2 rows)

语法与用法 ​

sql
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]

-- 常用选项
FULL [ boolean ]              -- 整表重写，归还空间（持排他锁）
FREEZE [ boolean ]            -- 强制冻结所有可见元组
VERBOSE [ boolean ]           -- 输出清理明细
ANALYZE [ boolean ]           -- 同时收集统计信息
DISABLE_PAGE_SKIPPING         -- 不做全可见页跳过（用于修复 VM 异常）
SKIP_LOCKED                   -- 跳过无法立即加锁的表（避免等待）
INDEX_CLEANUP { AUTO | ON | OFF }  -- 是否清理索引
TRUNCATE [ boolean ]          -- 是否尝试截断末尾空页
PARALLEL n                    -- 并行工作进程数（PostgreSQL 12+，仅普通 VACUUM）
PROCESS_MAIN [ boolean ]      -- 是否处理主表（PostgreSQL 13+）
PROCESS_TOAST [ boolean ]     -- 是否处理 TOAST 表（PostgreSQL 13+）
sql
-- 最小可运行示例：日常维护
VACUUM (VERBOSE, ANALYZE) orders;
text
INFO:  vacuuming "public.orders"
INFO:  launched 1 parallel vacuum worker
INFO:  scanned index "orders_pkey" to remove 4210 row versions
INFO:  "orders": removed 4210 dead row versions in 96 pages
INFO:  "orders": found 4210 removable, 11880 nonremovable row versions in 420 pages
DETAIL:  0 dead row versions cannot be removed yet.
INFO:  "orders": truncated 24 pages from 420 to 396
INFO:  analyzing "public.orders"
sql
-- 查看哪些表需要清理（含自动清理阈值对比）
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  50 + 0.2 * n_live_tup AS vacuum_threshold,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

详细说明 ​

VACUUM 的四种执行形态 ​

不同参数组合解决不同问题，运维时必须区分：

形态命令空间归还 OS锁级别典型用途
普通清理VACUUM否（仅截断尾部）ShareUpdateExclusiveLock日常维护，防膨胀
清理 + 分析VACUUM ANALYZE否同左大批量变更后的标准动作
强制冻结VACUUM FREEZE否同左回卷告警时紧急降低冻结年龄
完全清理VACUUM FULL是AccessExclusiveLock表已严重膨胀且可接受停机
sql
-- 日常：不阻塞业务，标记空间可复用
VACUUM orders;

-- 大批量导入/删除后：连带更新统计
VACUUM (ANALYZE, VERBOSE) orders;

-- 紧急：冻结老事务 ID
VACUUM (FREEZE, VERBOSE) orders;

-- 高风险：整表重写
VACUUM FULL orders;

普通 VACUUM 与 VACUUM FULL 的差别必须牢记：

text
普通 VACUUM：
  死元组 → 空间标记为「可复用」→ 文件不缩小
  后续 INSERT 复用这些空间，表体积停止增长

VACUUM FULL：
  新建一张表副本 → 把活元组搬过去 → 删除原表 → 重命名
  文件真正缩小，但期间持 ACCESS EXCLUSIVE 锁，业务全面阻塞

参数详解与资源控制 ​

VACUUM 通过「代价延迟（cost-based delay）」控制对 I/O 的冲击：

sql
SHOW vacuum_cost_delay;
SHOW vacuum_cost_limit;
SHOW maintenance_work_mem;
text
 vacuum_cost_delay
-------------------
 2ms
(1 row)

 vacuum_cost_limit
-------------------
 200
(1 row)

 maintenance_work_mem
----------------------
 64MB
(1 row)

含义是：清理过程中每累计消耗 vacuum_cost_limit 个代价点，就休眠 vacuum_cost_delay 毫秒。这能避免 VACUUM 挤占业务 I/O，但也让清理变慢。

sql
-- 临时加速一次手工清理（仅当前会话有效）
SET vacuum_cost_delay = 0;
SET maintenance_work_mem = '512MB';
VACUUM (VERBOSE, ANALYZE) orders;

并行清理可以显著提速：

sql
-- 启动 4 个并行工作进程（仅普通 VACUUM 支持）
VACUUM (PARALLEL 4, VERBOSE) orders;

注意：并行清理的加速来自「并行扫描索引」，主表扫描仍是单进程。若表上索引很少，提速有限。

SKIP_LOCKED 在多表批量清理时很有用：

sql
-- 不等待被占用的表，直接跳过
VACUUM (SKIP_LOCKED, VERBOSE) orders;
text
WARNING:  skipping "orders" --- lock not available

自动清理的触发条件 ​

自动清理由 autovacuum 守护进程执行，触发条件是：

text
触发阈值 = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples
        = 50 + 0.2 × 表当前行数
sql
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_naptime;
text
 autovacuum
------------
 on
(1 row)

 autovacuum_vacuum_threshold
-----------------------------
 50
(1 row)

 autovacuum_vacuum_scale_factor
--------------------------------
 0.2
(1 row)

 autovacuum_naptime
--------------------
 1min
(1 row)

举例：一张 100 万行的表，需要约 50 + 0.2 × 1000000 = 200050 个死元组才会触发自动清理。这意味着大表在小比例删除后可能长期不清理，而 20% 的膨胀比例对大表已是巨大浪费。

sql
-- 查看表的清理阈值与当前死元组数
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  50 + 0.2 * n_live_tup AS threshold,
  CASE WHEN n_dead_tup > 50 + 0.2 * n_live_tup THEN '应触发' ELSE '未达阈值' END AS status
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 5;
text
 relname  | n_live_tup | n_dead_tup | threshold |  status
----------+------------+------------+-----------+----------
 orders   |     998420 |      98210 |    199734 | 未达阈值
 products |       2000 |       4210 |       450 | 应触发
(2 rows)

对频繁更新的大表，推荐按表降低缩放因子：

sql
-- 只对大表精细化调整
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 0
);

诊断膨胀：找到真正需要清理的表 ​

死元组数只反映「未清理的旧版本」，不等于物理膨胀。要看真实膨胀率需要 pgstattuple 扩展或估算查询：

sql
-- 安装扩展（需要超级用户）
CREATE EXTENSION IF NOT EXISTS pgstattuple;
sql
-- 精确统计：扫描全表，代价较高
SELECT
  table_len,
  tuple_count,
  dead_tuple_count,
  round(dead_tuple_len * 100.0 / greatest(table_len, 1), 2) AS dead_pct,
  round(free_space * 100.0 / greatest(table_len, 1), 2) AS free_pct
FROM pgstattuple('orders');
text
 table_len | tuple_count | dead_tuple_count | dead_pct | free_pct
-----------+-------------+------------------+----------+----------
  34603008 |       11880 |             4210 |    18.12 |     0.00
(1 row)
sql
-- 快速估算膨胀（不扫全表，基于统计信息）
SELECT
  relname,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / greatest(n_live_tup + n_dead_tup, 1), 2) AS est_bloat_pct
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 0
ORDER BY est_bloat_pct DESC
LIMIT 10;
text
 relname  | table_size | n_live_tup | n_dead_tup | est_bloat_pct
----------+------------+------------+------------+---------------
 orders   | 33 MB      |      11880 |       4210 |         26.17
 products | 312 kB     |       2000 |         38 |          1.86
(2 rows)

autovacuum 为什么没跑：常见阻塞原因 ​

自动清理不执行的典型原因有六类：

原因判断方法处置
未达阈值对比 n_dead_tup 与阈值降低 scale_factor 或手工 VACUUM
长事务阻塞查 pg_stat_activity 中 xact_start 很早的会话终止或优化长事务
复制槽阻塞查 pg_replication_slots 的 active = false删除无用槽或推进 confirmed_flush_lsn
表被显式禁用查 pg_class.reloptions 中 autovacuum_enabled改回 on
工作进程不足查 pg_stat_activity 中 autovacuum 数量调大 autovacuum_max_workers
清理成本限速过强查 vacuum_cost_delay / vacuum_cost_limit适度降低延迟
sql
-- 检查长事务（最容易导致清理无法推进）
SELECT pid, now() - xact_start AS xact_age, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;
text
  pid  |     xact_age      | state  |                            query
-------+-------------------+--------+-------------------------------------------------------------
 10422 | 02:41:18.73211    | idle in transaction | SELECT * FROM orders WHERE id = 1
(1 row)
sql
-- 检查失效的复制槽
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
WHERE active = false;
text
 slot_name | active | restart_lsn | confirmed_flush_lsn
-----------+--------+-------------+---------------------
 standby1  | f      | 3/8A0002C8  | 3/8A0002C8
(1 row)
sql
-- 检查是否有表被禁用自动清理
SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT NULL AND relname IN ('orders', 'products', 'users');
text
 relname |          reloptions
---------+------------------------------
 orders  | {autovacuum_enabled=false}
(1 row)

冻结与事务 ID 回卷 ​

PostgreSQL 的事务 ID 是 32 位循环使用的，当一个数据库最老未冻结的事务距当前超过 20 亿时会触发保护性停机。VACUUM 的隐藏职责就是冻结老元组，推进 relfrozenxid。

sql
-- 查看各库与各表的冻结年龄
SELECT datname, age(datfrozenxid) AS db_age FROM pg_database ORDER BY db_age DESC LIMIT 3;
text
  datname  |  db_age
-----------+----------
 demo      | 62104512
 postgres  | 42018873
 template1 | 41839201
(1 row)
sql
-- 表级别：找出冻结年龄最大的表
SELECT
  relname,
  age(relfrozenxid) AS xid_age,
  pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
ORDER BY xid_age DESC
LIMIT 5;
text
 relname  | xid_age  |  size
----------+----------+--------
 orders   | 62104000 | 33 MB
 products | 62103000 | 312 kB
(2 rows)

关键参数：

ini
-- postgresql.conf
autovacuum_freeze_max_age = 200000000   # 超过此年龄强制触发 anti-wraparound 清理（2 亿）
vacuum_freeze_min_age = 50000000        # 小于此年龄的元组不冻结（5 千万）
vacuum_freeze_table_age = 150000000     # 超过此年龄触发完整冻结扫描（1.5 亿）
autovacuum_max_workers = 3              # autovacuum 工作进程上限
sql
-- 紧急处置：手工冻结（会在 age 高时消耗较多 I/O）
VACUUM (FREEZE, VERBOSE) orders;

XID 回卷是停机级风险

当 age(datfrozenxid) 逼近 20 亿，PostgreSQL 会拒绝新事务并强制停机，只能以单用户模式清理。必须监控 age(datfrozenxid) 并提前告警，用 VACUUM (FREEZE) 或确保 autovacuum 正常运行来降低年龄。

观察清理进度 ​

大表清理可能持续数分钟，pg_stat_progress_vacuum 可查看实时进度（PostgreSQL 12+）：

sql
SELECT
  p.pid,
  p.datname,
  c.relname,
  p.phase,
  p.heap_blks_total,
  p.heap_blks_scanned,
  p.heap_blks_vacuumed,
  round(p.heap_blks_scanned * 100.0 / greatest(p.heap_blks_total, 1), 2) AS scanned_pct
FROM pg_stat_progress_vacuum p
JOIN pg_class c ON c.oid = p.relid;
text
  pid  | datname | relname |      phase       | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | scanned_pct
-------+---------+---------+------------------+-----------------+-------------------+--------------------+-------------
 20118 | demo    | orders  | vacuuming indexes|            4224 |              4224 |               3802 |       100.00
(1 row)

常见 phase 取值：

phase含义
initializing准备阶段
scanning heap扫描堆表，检查死元组
vacuuming indexes清理索引中的死条目
vacuuming heap回收堆表空间
cleaning up indexes索引清理收尾
truncating heap截断表尾空页
performing final cleanup最终清理

VACUUM FULL 与 pg_repack 的取舍 ​

VACUUM FULL 能真正缩小文件，但代价极高：

text
VACUUM FULL 的代价：
  ① 持 ACCESS EXCLUSIVE 锁 —— 期间所有读写阻塞
  ② 需要约等于表大小的额外磁盘空间
  ③ 中断后回滚成本高
  ④ 阻塞期间同样阻塞了 autovacuum 与其他维护
sql
-- 危险操作：先确认业务可停机，并用会话级 lock_timeout 兜底
SET lock_timeout = '3s';
VACUUM FULL orders;
text
ERROR:  canceling statement due to lock timeout
sql
-- 错误！VACUUM 不能在事务块中执行，无法用 BEGIN/COMMIT 控制提交时机
BEGIN;
VACUUM FULL orders;
-- ERROR:  VACUUM cannot run inside a transaction block
COMMIT;

更安全的替代方案是 pg_repack：它以触发器 + 日志的方式在线重整理，只在切换的瞬间持锁。

bash
# pg_repack 需先安装扩展
psql -d demo -c "CREATE EXTENSION pg_repack"

# 在线重整单表（仅最后切换瞬间短暂持锁）
pg_repack -d demo -t orders
text
INFO: repacking table "public.orders"
INFO:  rebuilding indexes
INFO:  swapping table

方案对比：

方案锁影响额外空间是否需停机适用场景
普通 VACUUM几乎无无否日常维护
VACUUM FULL持续排他锁约 1 倍表大小是小表、可停机窗口
pg_repack仅切换瞬间约 1 倍表大小否大表在线重整
重建表切换切换瞬间约 1 倍表大小短需自定义转换逻辑时

实战示例 ​

以下示例统一使用 demo 库。

1. 准备数据 ​

sql
CREATE DATABASE demo;
\c demo

CREATE TABLE products (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku      text NOT NULL UNIQUE,
  name     text NOT NULL,
  category text NOT NULL,
  price    numeric(10, 2) NOT NULL,
  stock    integer NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   text NOT NULL,
  product_id integer NOT NULL REFERENCES products (id),
  qty        integer NOT NULL,
  amount     numeric(12, 2) NOT NULL,
  status     text NOT NULL,
  created_at timestamp NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_status ON orders (status);

2. 造数据并制造死元组 ​

sql
INSERT INTO products (sku, name, category, price, stock)
SELECT
  'SKU-' || lpad(g::text, 4, '0'),
  '商品 ' || g,
  (ARRAY['数码', '家居', '服饰', 'food', '美妆', '运动'])[1 + (g % 6)],
  round((50 + random() * 450)::numeric, 2),
  (random() * 500)::integer
FROM generate_series(1, 2000) AS g;

INSERT INTO orders (order_no, product_id, qty, amount, status)
SELECT
  'ORD-' || lpad(g::text, 6, '0'),
  1 + (g % 2000),
  1 + (g % 5),
  round((50 + random() * 450)::numeric, 2),
  (ARRAY['paid', 'pending', 'cancelled', 'refunded'])[1 + (g % 4)]
FROM generate_series(1, 12000) AS g;

-- 制造死元组：大量 UPDATE 会留下旧版本
UPDATE orders SET status = 'paid' WHERE status = 'pending';
UPDATE orders SET amount = amount * 1.1 WHERE status = 'cancelled';

3. 观察膨胀 ​

sql
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  pg_size_pretty(pg_relation_size(relid)) AS size,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';
text
 relname | n_live_tup | n_dead_tup |  size  |       last_autovacuum
---------+------------+------------+--------+-----------------------------
 orders  |      12000 |       5976 | 1256 kB|
(1 row)

4. 执行清理 ​

sql
VACUUM (VERBOSE, ANALYZE) orders;
text
INFO:  vacuuming "public.orders"
INFO:  launched 1 parallel vacuum worker
INFO:  scanned index "orders_pkey" to remove 5976 row versions
INFO:  scanned index "idx_orders_status" to remove 5976 row versions
INFO:  "orders": removed 5976 dead row versions in 82 pages
INFO:  "orders": found 5976 removable, 12000 nonremovable row versions in 157 pages
DETAIL:  0 dead row versions cannot be removed yet.
INFO:  "orders": truncated 0 pages from 157 to 157
INFO:  analyzing "public.orders"

truncated 0 pages 说明表尾没有空页，文件大小不变 —— 这正是普通 VACUUM 的正常表现。

sql
-- 死元组已归零，但文件大小基本不变
SELECT
  relname,
  n_dead_tup,
  pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE relname = 'orders';
text
 relname | n_dead_tup |  size
---------+------------+--------
 orders  |          0 | 1256 kB
(1 row)

5. 对比 VACUUM FULL 的空间归还 ​

sql
-- 记录清理前大小
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS before_full;
text
 before_full
-------------
 2232 kB
(1 row)
sql
-- 注意：持排他锁，生产环境需评估
VACUUM FULL orders;
text
VACUUM
sql
-- 空间已归还
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS after_full;
text
 after_full
------------
 1216 kB
(1 row)

6. 检查冻结年龄与自动清理状态 ​

sql
SELECT
  relname,
  age(relfrozenxid) AS xid_age,
  n_dead_tup,
  50 + 0.2 * n_live_tup AS threshold,
  last_autovacuum
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE relname = 'orders';
text
 relname | xid_age | n_dead_tup | threshold |       last_autovacuum
---------+---------+------------+-----------+-----------------------------
 orders  | 62104810|          0 |   24050.0 | 2026-09-24 10:41:02.882+08
(1 row)
sql
-- 库级冻结年龄，建议纳入监控
SELECT datname, age(datfrozenxid) AS db_xid_age
FROM pg_database
WHERE datname = 'demo';
text
 datname | db_xid_age
---------+------------
 demo    |   62104810
(1 row)

注意事项 ​

  • 普通 VACUUM 不会缩小表文件。 它只是让空间可复用，看到文件大小不变不要以为没生效。
sql
-- 错误理解：跑完 VACUUM 表就变小了
VACUUM orders;   -- 文件大小通常不变

-- 需要真正缩小时用 VACUUM FULL（有代价）或 pg_repack
  • VACUUM FULL 会阻塞所有读写。 不要在日常维护脚本里无条件使用。
sql
-- 不推荐：直接在业务高峰执行
VACUUM FULL orders;

-- 推荐：加锁超时兜底，避免无限等待
SET lock_timeout = '3s';
VACUUM FULL orders;
  • VACUUM 不能在事务块中执行。
sql
-- 错误！VACUUM 不能出现在事务块内
BEGIN;
VACUUM orders;
COMMIT;

-- 错误信息
-- ERROR:  VACUUM cannot run inside a transaction block
  • autovacuum = off 是极危险的配置。 关闭后死元组永不回收、冻结永不推进，最终导致回卷停机。
sql
-- 错误！除非有完整的手工维护流程，否则不要关闭
ALTER SYSTEM SET autovacuum = off;

-- 正确：保持开启，只做精细化参数调整
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);
  • 长事务是清理的最大敌人。 一个持续数小时的空闲事务会阻止所有更早死元组的回收。
sql
-- 排查并处置长时间 idle in transaction 的会话
SELECT pid, now() - xact_start AS age, state
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - xact_start > interval '5 minutes';
  • 不要因为「表变大」就立刻 VACUUM FULL。 先确认是死元组膨胀，还是正常数据增长；后者重写表毫无意义。
sql
-- 先诊断，再决定
SELECT n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / greatest(n_live_tup + n_dead_tup, 1), 2) AS dead_pct
FROM pg_stat_user_tables WHERE relname = 'orders';
  • VACUUM 不更新统计信息。 需要统计信息时显式加 ANALYZE，两者是不同的任务。
sql
-- 只要统计
ANALYZE orders;
-- 两者都要
VACUUM ANALYZE orders;
  • 禁用表的 autovacuum 前必须有替代方案。 autovacuum_enabled = false 常见的「优化」其实是把风险推迟到未来。
sql
-- 不推荐：禁用后必须自己保证定期手工清理与冻结
ALTER TABLE orders SET (autovacuum_enabled = false);

  • VACUUM FULL 期间不可中断回滚廉价。 中断会丢弃已完成的重写，代价与重新执行接近，且期间一直阻塞。

  • 复制槽会「钉住」xmin 使清理停滞。 无消费者却处于非激活状态的槽必须及时清理。

sql
-- 检查长期非激活的槽
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
WHERE active = false;

最佳实践 ​

  1. 保持 autovacuum 开启，并优先做「按表精细化调参」而非全局关闭。
sql
-- 高频更新的大表：降低缩放因子、去掉清理延迟
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_delay = 0
);
  1. 把自动清理的状态纳入监控，关注 n_dead_tup 比例与 last_autovacuum 时间。
sql
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;
  1. 监控事务 ID 年龄，把它当成一级告警指标。
sql
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;
ini
-- 建议阈值：超过 10 亿告警，超过 15 亿必须处置
autovacuum_freeze_max_age = 200000000
  1. 大批量 DELETE / UPDATE 之后立即手工 VACUUM ANALYZE，不要等自动清理。
sql
DELETE FROM orders WHERE created_at < now() - interval '1 year';
VACUUM (ANALYZE, VERBOSE) orders;
  1. 用 SKIP_LOCKED 做批量维护，避免被个别繁忙表拖住整个脚本。
sql
VACUUM (SKIP_LOCKED, ANALYZE, VERBOSE) orders;
  1. 需要真正回收大表空间时优先选 pg_repack，而不是 VACUUM FULL。
bash
pg_repack -d demo -t orders --no-order
  1. 定期检查并清理失效的复制槽与长事务，这两者是清理停滞最常见的根因。
sql
SELECT pid, now() - xact_start AS age, state, left(query, 50) AS query
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes';
  1. 在维护脚本中设置 lock_timeout，让 VACUUM FULL 等在拿不到锁时快速失败而不是排队阻塞后续业务。
sql
SET lock_timeout = '3s';
SET statement_timeout = '30min';
VACUUM FULL orders;
  1. 用 pg_stat_progress_vacuum 观察长任务，并把 heap_blks_total 与扫描进度一起记录，便于判断是否需要干预。
sql
SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

下一节

继续学习：内连接 INNER JOIN

参考链接 ​