九、 PostgreSQL VACUUM
1. 死元组的产生
- Delete：将删除的元组标记为死元组
- Update：PG的update操作 = delete + insert。即先将原本的元组标记为delete，然后再插入一条新的元组
2. VACUUM概述
主要作用：磁盘清理（清理 dead tuple）、更新统计信息、重组数据、解决事务ID回卷问题。
语法结构
VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE [table [ (column [, ...]) ]]说明：
- vacuum：不要求获得排它锁，找到那些旧的"死"数据，标记为不可用状态，不进行空间合并
- vacuum full：就是除了 vacuum，进行空间合并，它需要 lock table
- vacuum analyze：更新统计信息，使得优化器能够选择更好的方案执行 sql
- vacuum freeze：表记录冻结，可解决事务 id 回卷的问题
VACUUM的作用
1. 删除死元组
- 删除每个页面的死元组并对活动元组进行碎片整理。
- 删除指向死元组的索引元组。
2. 冻结旧的 txids
- 如有必要，冻结元组的旧 txids。
- 更新冻结的 txid 相关系统目录（pg_database 和 pg_class）。
- 如果可能的话，去除堵塞物中不必要的部分。
3. 其他的
- 更新已处理表的 FSM 和 VM。
- 更新一些统计信息（pg_stat_all_tables等）。
VACUUM处理流程
VACUUM根据可见行地图选择执行或跳过page
VACUUM ANALIZE
-
统计信息由ANALYZE命令收集，它除了直接被调用之外还可以作为VACUUM的一个可选步骤被调用。如VACUUM ANAYLYZE table_name，该命令将会先执行VACUUM再执行ANALYZE。
-
自动清理守护进程如果被启用，当一个表的内容被改变得足够多时，它将自动发出ANALYZE命令。
-
与回收空间(VACUUM)一样，对数据更新频繁的表保持一定频度的ANALYZE，从而使该表的统计信息始终处于相对较新的状态，对于更新并不频繁的数据表，则不需要执行该操作。
-
可以为特定的表，甚至是表中特定的字段运行ANALYZE命令，可以只对更新比较频繁的部分信息执行ANALYZE操作，不仅可以节省统计信息所占用的空间，也可以提高本次ANALYZE操作的执行效率。
-
自动清理守护进程不会为外部表发出ANALYZE命令，因为无法确定一个合适的频度。
可以通过下面的命令来调整指定字段的抽样率，如：
ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200;注意：该值的取值范围是 0-1000，其中值越低采样比例就越低，分析结果的准确性也就越低，但是 ANALYZE 命令执行的速度却更快。如果将该值设置为 -1，那么该字段的采样比率将恢复到系统当前默认的采样值。通过下面的命令获取当前系统的缺省采样值。
show default_statistics_target;
default_statistics_target
---------------------------
100 # 代表 100/1000，也就是10%
(1 row)VACUUM FREEZE
- PostgreSQL 目前默认的存储引擎，事务可见性需要依赖行头的事务号，因为事务号是32位的，会循环使用；
- 事务ID由32位数保存，而事务ID递增，当事务ID用完时，会出现事务id回卷问题，可以通过VACUUM FREEZE来解决该问题；
- 在一条记录产生后，如果再次经历了20亿个事务，必须对其进行freeze，否则数据库会认为这条记录是未来事务产生的（可见性判断）。
回卷问题：其中需要注意的是，XID 是用32位无符号数来表示的，也就是说如果不引入特殊的处理，当 PostgreSQL 的 XID 到达40亿，会造成溢出，从而新的 XID 为0。而按照 PostgreSQL 的 MVCC 机制实现，之前的事务就可以看到这个新事务创建的元组，而新事务不能看到之前事务创建的元组，这违反了事务的可见性。
VACUUM FULL
- VACUUM FULL 通过把死元组之外的内容写成一个完整的新版本表文件来主动紧缩表。
- 这将最小化表的尺寸，但是要花较长的时间。
- 其实质是将当前删除记录后面的数据进行移动，使得整体的记录连贯起来，降低了"高水位标记"。
- vacuum full 会把空间返回给 OS，但是会有一些弊端：
- 排他的锁表，阻塞相关表的所有操作；
- 会创建一个表的副本，所以会将使用的磁盘空间加倍，最大可能达到两倍，如果磁盘空间不足，不要执行；
VACUUM
VACUUM FULL
什么情况下做 VACUUM？
- 不锁表回收空间，只能回收部分空间。
- 频率：对于有较多实时更新的表，每天做一次。如果更新是每天一次批量进行的，可以在每天批量更新后做一次。
- 对系统影响：不会锁表，表可以正常读写。会导致 CPU、I/O 使用率增加，可能影响查询的性能。
什么情况下做 VACUUM FULL？
- 锁表，通过重建表，回收所有空洞空间。对做了大量更新后的表，建议尽快执行 VACUUM FULL。
- 频率：至少每周执行一次。如果每天会更新几乎所有数据，需要每天做一次。
- 对系统影响：会对正在进行 vacuum full 的表锁定，无法读写。会导致 CPU、I/O 使用率增加。建议在维护窗口进行操作。可选择 pg_repack 工具操作。
AUTOVACUUM
autovacuum 是 postgresql 里非常重要的一个服务端进程，在一定条件下自动触发执行。autovacuum 参数 和 track_counts 参数默认为"on"，主要作用包括：
- 清理"死元组"（UPDATE 或 DELETE 操作后留下的）并对表进行分析
- 更新可用空间映射（free space map），以跟踪表块中的可用空间
- 更新仅索引扫描所需的可见性图（visibility map）
- "冻结"（freeze）表行，以便事务 ID 计数器可以安全地环绕
track_counts 参数
track_counts 是 PostgreSQL 的一个配置参数。它控制的是 stats collector（统计信息收集器）是否收集表和索引的访问统计数据。
作用
当 track_counts = on（默认值）时，PostgreSQL 会跟踪并记录：
- 每张表的插入、更新、删除行数
- 顺序扫描和索引扫描次数
- 死元组数量（
n_dead_tup） - 磁盘块读取和缓存命中次数
这些数据就是你在 pg_stat_all_tables、pg_stat_user_tables 等视图中看到的内容。
为什么和 autovacuum 绑定在一起？
autovacuum 依赖 track_counts 收集的统计数据来判断何时触发自动清理。如果 track_counts = off，PostgreSQL 就不会统计死元组数量，autovacuum 无法判断触发时机，实际上等于 autovacuum 废了。
VACUUM使用建议
-
开启全局自动 vacuum：修改配置文件 postgresql.conf，设置参数
autovacuum=on -
持续关注表中 dead tuple 的状况、表级计划性的执行 vacuum。查询需要 vacuum 的表，即表 dead tuple 的量或比例，默认情况下可能有少于 20% 的 dead tuple。可通过以下 sql 命令查询表的空间使用情况：
select relname, n_live_tup, n_dead_tup from pg_stat_all_tables where n_dead_tup <> 0 order by n_dead_tup desc;
- 适当调大参数
maintenance_work_mem，可加快 vacuum 的执行速度 - PostgreSQL 9.5 引入了一个新的参数：jobs 参数，可以并行的运行 VACUUM。
- 对于有大量 update 的表，vacuum full 是没有必要的，因为它的空间还会再次增长
- 定期监控数据量变化较大的表，确认其磁盘页面占有量接近临界值时，可考虑 vacuum full
注意：
① vacuum 只会删除那些已经结束的事务所关联到的旧有的已经不用的数据，如果一个事务还在运行，autovacuum 就不会处理这个事务相关的数据了，如果一个事务长时间运行而没有结束，就会导致最终 autovacuum 停止在那里。
② 如果应用中大量使用了 table lock，会导致 autovacuum 没有机会执行。
1. 死元组比例临界值
当表中死元组占比过高时，说明表膨胀严重。一般经验值：
| 死元组比例 | 状态 | 建议操作 |
|---|---|---|
| < 10% | 正常 | autovacuum 自动处理 |
| 10%–20% | 关注 | 确认 autovacuum 是否正常工作 |
| 20%–50% | 警告 | 手动执行 VACUUM，排查原因 |
| > 50% | 严重膨胀 | 考虑 VACUUM FULL 或 pg_repack |
查询命令：
SELECT
relname,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2)
ELSE 0
END AS dead_ratio_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;2. 实际磁盘占用 vs 有效数据比例
表的物理文件大小远超实际有效数据量，说明存在大量空洞页面。这是判断是否需要 VACUUM FULL 的关键依据。
查询表的实际磁盘占用：
SELECT
schemaname,
relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_size,
pg_size_pretty(pg_indexes_size(relid)) AS index_size,
n_live_tup,
n_dead_tup
FROM pg_stat_all_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;3. 更精确的膨胀率查询（pgstattuple 扩展）
如果安装了 pgstattuple 扩展，可以获得精确的膨胀数据：
-- 需要先安装扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- 查询指定表的精确膨胀信息
mydb=# SELECT
table_len, -- 表物理大小（字节）
tuple_count, -- 活元组数
tuple_percent, -- 活元组占比
dead_tuple_count, -- 死元组数
round(dead_tuple_len * 100.0 / table_len, 2) AS dead_space_pct, -- 死空间占比
free_space, -- 可复用空间
round(free_space * 100.0 / table_len, 2) AS free_space_pct -- 可复用空间占比
FROM pgstattuple('test_auto_vacuum');
table_len | tuple_count | tuple_percent | dead_tuple_count | dead_space_pct | free_space | free_space_pct
------------+-------------+---------------+------------------+----------------+------------+----------------
2984763392 | 100000 | 0.11 | 0 | 0.00 | 2970960520 | 99.54
(1 row)当 dead_space_pct + free_space_pct 超过 40%–50% 时，通常就是做 VACUUM FULL 的时机。
4. 事务 ID 回卷临界值
还有一个更危险的临界值——事务 ID 年龄。当接近 20 亿时必须 freeze：
-- 查看各数据库的事务 ID 年龄
SELECT datname, age(datfrozenxid) AS xid_age,
round(age(datfrozenxid) * 100.0 / 2000000000, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- 查看各表的事务 ID 年龄
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;| XID 年龄 | 状态 | 说明 |
|---|---|---|
| < 2 亿 | 安全 | 正常 |
| 2 亿–10 亿 | 关注 | 确认 autovacuum freeze 正常 |
| > 10 亿 | 危险 | 立即手动 VACUUM FREEZE |
| 接近 20 亿 | 紧急 | PostgreSQL 会强制进入单用户模式拒绝新事务 |
3. AUTOVACUUM 设置
触发条件
- autovacuum_vacuum_threshold：默认50，autovacuum_vacuum_scale_factor 默认值为20%，两者配合使用。
- autovacuum_vacuum_insert_threshold：默认1000， autovacuum_vacuum_insert_scale_factor 默认值为20%，两者配合使用。
- 当update，delete的tuples数量超过
autovacuum_vacuum_scale_factor * table_tuples + autovacuum_vacuum_threshold时，进行vacuum。如果要使vacuum工作勤奋点，则将此值改小。 - 当insert的tuples数量超过
autovacuum_vacuum_insert_scale_factor * table_tuples + autovacuum_vacuum_insert_threshold时，进行vacuum。如果要使vacuum工作勤奋点，则将此值改小。
autovacuum时，收集统计信息触发条件
- autovacuum_analyze_threshold：默认50，autovacuum_analyze_scale_factor 默认10%，两者配合使用。
- 当 update、insert、delete 的 tuples 数量超过
autovacuum_analyze_scale_factor * table_tuples + autovacuum_analyze_threshold时，进行 analyze。
AUTOVACUUM参数
基础参数
1. autovacuum
-
默认值： on
-
说明： 是否开启 autovacuum。默认开启。特别的，当需要冻结 XID 时，即使此值为 off，PostgreSQL 也会强制进行 vacuum。
2. autovacuum_naptime
-
默认值： 1min
-
说明： 两次 autovacuum 运行之间的间隔时间。这个 naptime 会被 vacuum launcher 平均分配到每个数据库上，即实际间隔为
autovacuum_naptime / num_of_db。
3. autovacuum_max_workers
-
默认值： 3
-
说明： 最大同时运行的 autovacuum worker 数量，不包含 launcher 本身。
4. log_autovacuum_min_duration
-
默认值： -1
-
说明： 记录 autovacuum 动作到日志文件的阈值。当 vacuum 动作超过此值时记录。
-1表示不记录，0表示每次都记录。
XID 冻结参数
1. autovacuum_freeze_max_age
- 默认值： 200 million（2亿）
- 说明： 离下一次进行 XID 冻结的最大事务数。当表的
relfrozenxid年龄超过此值时，即使 autovacuum 关闭也会强制触发 vacuum 以防止事务 ID 回卷。
2. autovacuum_multixact_freeze_max_age
- 默认值： 400 million（4亿）
- 说明： 与上面类似，但针对的是 MultiXact ID 的冻结。
成本控制参数（Cost-Based Vacuum Delay）
这组参数用于控制 vacuum 的 IO 速率，防止 vacuum 操作过度占用系统 IO 资源。
1. vacuum_cost_page_hit
- 默认值： 1
- 说明： vacuum 时，page 在 buffer（共享缓存池）中命中时的代价。
2. vacuum_cost_page_miss
- 默认值： 2
- 说明： vacuum 时，page 不在 buffer 中、需要从磁盘读入时的代价。
3. vacuum_cost_page_dirty
- 默认值： 20
- 说明： vacuum 时修改了 clean 的 page（将其变脏），需要额外 IO 将脏块刷到磁盘的代价。
4. vacuum_cost_limit
- 默认值： 200
- 说明： 累计代价超过此值时，vacuum 进程会 sleep（休眠）。
5. autovacuum_vacuum_cost_delay
- 默认值： 2ms（如果为 -1，取
vacuum_cost_delay的值） - 说明： 每次 vacuum 达到 cost_limit 后的休眠时间。
6. autovacuum_vacuum_cost_limit
- 默认值： -1（取
vacuum_cost_limit的值，即 200） - 说明： autovacuum 可达到的总成本限制。这个值是所有 worker 的累加值。
成本调优建议
把每个 cost 值调小，然后把 limit 值调大，可以延长每次 vacuum 的工作时间。这样做在高负载系统中可能会对 IO 有所影响，但对于表物理存储空间的增长会有所减缓。
深入总结
1. autovacuum_naptime 的意义
Autovacuum 确实是阈值驱动的，但 PostgreSQL 不会实时监控每张表的状态，而是靠 autovacuum launcher 定期轮询来检查。autovacuum_naptime（默认 1min）就是这个轮询周期——launcher 每隔这段时间醒来一次，检查哪些表满足触发条件，再派 worker 去执行。多数据库场景下，实际每个 DB 的检查间隔为 autovacuum_naptime / num_of_db。Launcher 不扫描表数据，代价很小。它依赖 track_counts（默认 on）
2. Vacuum Launcher 与 Worker
Autovacuum 子系统有两种进程：
-
Launcher（1个）：常驻后台的调度器，定期醒来检查各表是否需要 vacuum/analyze，不干实际清理工作。
-
Worker（最多
autovacuum_max_workers个）：实际执行 vacuum/analyze 的进程，每个处理一张表，完成后退出。
3. autovacuum_work_mem 与 maintenance_work_mem
两个独立参数，作用于不同场景：
|
参数 |
使用者 |
场景 |
|---|---|---|
|
|
用户会话 |
手动 VACUUM、CREATE INDEX 等 |
|
|
autovacuum worker |
自动 vacuum |
autovacuum_work_mem = -1 的意思是"取 maintenance_work_mem 的当前值"，只是借值的语法糖，并不是说每个用户会话里都有 autovacuum_work_mem。一旦显式设置了 autovacuum_work_mem，两者就完全独立。
这块内存用于 worker 在内存中维护死元组 TID 数组，是每个 worker 进程的私有内存，从操作系统申请，不属于 shared_buffers。shared_buffers 是全局共享的页面缓存，两者性质完全不同。实践中建议给 autovacuum 设一个较小的值（如 256MB ~ 512MB），避免多个 worker 同时跑时占用过多内存。
4. Vacuum 为什么会把 page 变 dirty？
Vacuum 虽然不是 DML，但确实在物理上修改 page 内容。这里的 dirty 不是"写入新业务数据"，而是 page 字节被修改，与磁盘副本不再一致。具体修改包括：
-
Line Pointer 状态变更：
LP_DEAD→LP_UNUSED，标记槽位可被后续 INSERT 复用 -
Page Header 更新：修改
pd_lower/pd_upper等空闲空间字段，可能还会整理压缩碎片 -
Hint Bits 设置：标记元组事务可见性（committed/aborted）
-
Freeze 操作：将老元组的
t_xmin替换为FrozenTransactionId，防止 XID 回卷
对 shared buffer 而言，不管是 DML、vacuum 还是 hint bit 设置，只要 page 内容和磁盘不一致就是 dirty，最终都由 bgwriter/checkpointer 刷回磁盘。
5. vacuum_cost_page_dirty 为什么代价最高？
三种代价场景对比：
|
场景 |
page 原状态 |
vacuum 操作后 |
代价 |
|---|---|---|---|
|
page 在 buffer 中，已经是 dirty |
dirty |
还是 dirty（不增加额外刷盘） |
|
|
page 不在 buffer 中 |
需从磁盘读入 |
增加一次读 IO |
|
|
page 在 buffer 中，是 clean 的 |
clean → dirty |
增加一次写 IO（原本不需要刷盘，现在需要了） |
|
page_dirty 代价最高的原因：一个 clean page 本可以被 buffer manager 直接淘汰而不写磁盘，vacuum 把它改脏后，淘汰前必须先刷盘。不是 vacuum 自己刷盘，而是它把"不需要刷盘的 page"变成了"需要刷盘的 page"，间接增加了系统写 IO。
6. cost_delay 的工作机制
Worker 在扫描表的过程中，每访问一个 page 都会累加代价：
- 命中 buffer：+1（
vacuum_cost_page_hit） - 从磁盘读：+10（
vacuum_cost_page_miss） - 把 clean page 改脏：+20（
vacuum_cost_page_dirty）
当累计代价达到 vacuum_cost_limit（默认 200）时，worker 暂停，睡眠 autovacuum_vacuum_cost_delay（默认 2ms），然后代价清零，继续扫描，直到再次达到 limit 又睡一次……如此反复，直到整张表扫完。主要是为了防止连续IO影响服务使用。
实战计算示例
示例：1秒内 autovacuum 能清理多少块？
1）1秒内 autovacuum 可以工作的次数
1s = 1000ms
autovacuum_vacuum_cost_delay = 2ms
每秒唤醒次数 = 1000 / 2 = 500 次2）读操作 — 缓存命中
vacuum_cost_page_hit = 1
每次唤醒可读块数 = 200 / 1 = 200 块
每秒总读块数 = 500 × 200 = 100,000 块
IO 吞吐 = 100,000 × 8KB / 1024 ≈ 781 MB/s3）读操作 — 缓存未命中（磁盘读取）
vacuum_cost_page_miss = 2
每次唤醒可读块数 = 200 / 2 = 100 块
每秒总读块数 = 500 × 100 = 50,000 块
IO 吞吐 = 50,000 × 8KB / 1024 ≈ 390 MB/s4）写操作（脏页刷盘）
vacuum_cost_page_dirty = 20
每次唤醒可处理块数 = 200 / 20 = 10 块
每秒总处理块数 = 500 × 10 = 5,000 块
4. 实验
4.1 VACUUM实验
# autovacuum = off 并重启
alter system set autovacuum = off ;
pg_ctl restart
---
-- 创建一个用于实验的表，并插入数据
mydb=# CREATE TABLE test_vacuum (id SERIAL PRIMARY KEY, data TEXT);
CREATE TABLE
mydb=# INSERT INTO test_vacuum (data) SELECT md5(random()::text) FROM generate_series(1, 100000);
INSERT 0 100000
# 查看表大小
mydb=# \dt+ test_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------+-------+----------+-------------+---------------+---------+-------------
public | test_vacuum | table | postgres | permanent | heap | 6704 kB |
(1 row)
# 查看行数
mydb=# select count(1) from test_vacuum;
count
--------
100000
(1 row)
# 查看死元组
mydb=# SELECT
relname,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2)
ELSE 0
END AS dead_ratio_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'test_vacuum';
relname | n_live_tup | n_dead_tup | dead_ratio_pct | last_autovacuum | last_vacuum
-------------+------------+------------+----------------+-----------------------------+-------------------------------
test_vacuum | 100000 | 0 | 0.00 | 2026-09-02 14:45:03.1292+08 | 2026-09-02 14:36:15.996667+08
(1 row)
---
# 更新 10000 行
mydb=# UPDATE test_vacuum SET data = md5(random()::text) WHERE id % 10 = 0;
UPDATE 10000
# 删除 1000行
mydb=# DELETE FROM test_vacuum WHERE id % 100 = 0;
DELETE 1000
# 查看表大小
mydb=# \dt+ test_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------+-------+----------+-------------+---------------+---------+-------------
public | test_vacuum | table | postgres | permanent | heap | 7368 kB |
(1 row)
# 查看行数
mydb=# select count(1) from test_vacuum;
count
-------
99000
(1 row)
# 查看死元组
mydb=# SELECT
relname,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2)
ELSE 0
END AS dead_ratio_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'test_vacuum';
relname | n_live_tup | n_dead_tup | dead_ratio_pct | last_autovacuum | last_vacuum
-------------+------------+------------+----------------+-----------------------------+-------------------------------
test_vacuum | 99000 | 11000 | 10.00 | 2026-09-02 14:45:03.1292+08 | 2026-09-02 14:36:15.996667+08
---
# 执行 vacuum
mydb=# vacuum test_vacuum;
VACUUM
# 查看表大小
mydb=# \dt+ test_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------+-------+----------+-------------+---------------+---------+-------------
public | test_vacuum | table | postgres | permanent | heap | 7376 kB |
(1 row)
mydb=# select count(1) from test_vacuum;
count
-------
99000
(1 row)
mydb=# SELECT
relname,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2)
ELSE 0
END AS dead_ratio_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'test_vacuum';
relname | n_live_tup | n_dead_tup | dead_ratio_pct | last_autovacuum | last_vacuum
-------------+------------+------------+----------------+-----------------------------+-------------------------------
test_vacuum | 99000 | 0 | 0.00 | 2026-09-02 14:45:03.1292+08 | 2026-09-02 14:49:23.888561+08
(1 row)
---
# 执行 vacuum full
mydb=# vacuum full test_vacuum;
VACUUM
mydb=# \dt+ test_vacuum
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------+-------+----------+-------------+---------------+---------+-------------
public | test_vacuum | table | postgres | permanent | heap | 6608 kB |
(1 row)
mydb=# select count(1) from test_vacuum;
count
-------
99000
(1 row)
mydb=# SELECT
relname,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2)
ELSE 0
END AS dead_ratio_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'test_vacuum';
relname | n_live_tup | n_dead_tup | dead_ratio_pct | last_autovacuum | last_vacuum
-------------+------------+------------+----------------+-----------------------------+-------------------------------
test_vacuum | 99000 | 0 | 0.00 | 2026-09-02 14:45:03.1292+08 | 2026-09-02 14:49:23.888561+08
(1 row)
4.2 AUTOVACUUM实验
# autovacuum = on 并重启
alter system set autovacuum = on ;
pg_ctl restart
---
SHOW autovacuum;
autovacuum
------------
on
(1 row)
# 这里可以为单张表设置autovacuum参数
ALTER TABLE test_vacuum SET (autovacuum_vacuum_threshold = 50,autovacuum_vacuum_scale_factor = 0.1);
# 查看test_vacuum的参数
\d+ test_vacuum
Table "public.test_vacuum"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+-----------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('test_vacuum_id_seq'::regclass) | plain | | |
data | text | | | | extended | | |
Indexes:
"test_vacuum_pkey" PRIMARY KEY, btree (id)
Access method: heap
Options: autovacuum_vacuum_threshold=50, autovacuum_vacuum_scale_factor=0.1
#进行更多的更新和删除操作后，不手动执行 VACUUM，而是等待 Autovacuum 自动运行。
UPDATE test_vacuum SET data = md5(random()::text) WHERE id % 10 = 1;
DELETE FROM test_vacuum WHERE id % 100 = 1;
# 查看pg_stat_user_tables视图
SELECT
relname,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2)
ELSE 0
END AS dead_ratio_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'test_vacuum';
5. 表膨胀
表膨胀对数据库性能的影响
表膨胀对数据库性能的影响可以从以下几个方面进行详细描述：
1. 磁盘空间浪费： 表膨胀意味着表中的物理数据文件包含大量不再使用的死元组（已删除或被新版本覆盖的旧记录）。这些无效数据占用着宝贵的磁盘空间，可能导致存储资源紧张，增加存储成本，并在磁盘容量接近上限时影响到数据库整体的可用性。
2. 查询性能下降： 查询性能受到表膨胀的影响主要体现在以下几点：
- 额外 I/O 开销： 由于查询不仅需要读取有效数据，还需要扫描和过滤掉无效的数据行，这会增加磁盘 I/O 操作，降低查询速度。
- 缓存效率低下： PostgreSQL 缓冲池用于缓存常用数据页。如果表存在大量死元组，那么缓存中可能会填充较多无用的数据页，导致实际需要的热点数据无法高效利用缓存，从而降低缓存命中率。
- 索引膨胀： 索引可能指向已经删除但未清理的数据行，导致索引大小增大，且查询时可能需要处理更多无效的索引项。
3. 事务 ID 管理复杂化： 在 MVCC 模型下，每个事务都有一个事务 ID（XID），当表膨胀严重时，可能导致事务 ID 空间耗尽，因为即使事务已经结束，其对应的旧版本数据仍占用了事务 ID。为了避免这种情况，PostgreSQL 必须频繁地进行检查点和冻结旧事务 ID，这也是一种性能开销。
4. 并发性能影响： 对于高并发场景，表膨胀可能导致锁争用加剧，特别是当执行 VACUUM FULL 时，它会锁定整个表以重组并压缩数据，阻塞其他事务对这个表的操作。
5. 统计信息不准确： 表膨胀还可能导致统计信息过时，因为 ANALYZE 通常基于当前表的实际数据计算统计数据，而含有大量死元组的表会使统计信息偏离实际情况，进而影响查询优化器生成的执行计划，降低查询效率。
如何发现表膨胀
一般在大量删改后会生成很多死元组，如果死元组比例过多就认为发生了表膨胀。但是如果我们设置了AUTOVACUUM，那么会自动清理死元组，这时候我们查询就发现死元组的比例并不高，这时可以用 EXTENSION pgstattuple查询表的碎片率，如果有大量碎片那么这时候就需要执行pg_repack，如下，活元组占比太低，可复用空间占比太高则说明需要有大量碎片。
SELECT
table_len, -- 表物理大小（字节）
tuple_count, -- 活元组数
tuple_percent, -- 活元组占比
dead_tuple_count, -- 死元组数
round(dead_tuple_len * 100.0 / table_len, 2) AS dead_space_pct, -- 死空间占比
free_space, -- 可复用空间
round(free_space * 100.0 / table_len, 2) AS free_space_pct -- 可复用空间占比
FROM pgstattuple('test_auto_vacuum');如何解决表膨胀
在发现表膨胀的问题之后，我们更推荐使用 pg_repack 来处理，而不是 vacuum，这是什么原因？
使用 pg_repack 来处理表膨胀的原因在于传统的 PostgreSQL 维护命令在处理表空间碎片和回收已删除或更新后不再使用的空间时，存在一定的限制：
1. VACUUM FULL：
- VACUUM FULL 可以压缩表并回收空间，但它需要对表进行独占锁定，这意味着在执行期间，所有其他针对该表的读写操作都将被阻塞，这对于生产环境中的大型、高并发访问的表来说是不可接受的。
2. CLUSTER：
- CLUSTER 通过重新组织表数据以按照索引顺序排列，可以在一定程度上解决表膨胀问题。然而，它同样要求对表进行独占锁定，并且仅能根据一个特定的索引来整理表，而且这个过程不总是最有效的空间回收方法。
pg_repack 的优点包括：
- 在线操作： pg_repack 能够在几乎不停止应用读写操作的情况下进行表重建，它采用的是两阶段提交的方式，创建一个新的结构紧凑的临时表，然后将数据迁移到新表，并替换旧表，这一过程中避免了长时间的锁表操作，极大地减少了对业务的影响。
- 空间回收： pg_repack 能够有效地回收由于频繁插入、更新和删除导致的表空间碎片，使得数据库更加紧凑，减少磁盘空间占用。
- 事务安全： pg_repack 的操作是在事务中完成的，如果过程中出现任何错误，可以回滚到原始状态，确保数据一致性。
因此，在面对表膨胀问题时，pg_repack 提供了一个更高效且对业务影响较小的解决方案，尤其适用于无法容忍长时间锁定表的生产环境。
-- 在数据库中创建pg_repack扩展
CREATE EXTENSION pg_repack;
-- 使用pg_repack重组织表,避免锁表
-- 表必须有主键或者非空唯一键
/usr/local/postgresql/bin/pg_repack \
-d mydb \
-t public.test_auto_vacuum \
--wait-timeout=60 \
--no-kill-backend
浙公网安备 33010602011771号