云贝教育 |【PostgreSQL】Vacuum详解：深入理解与实践

注: 本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

引言 

PostgreSQL作为一款功能强大、开源的关系型数据库管理系统，其性能优化机制中，“VACUUM”命令扮演着至关重要的角色。本文将对PostgreSQL的VACUUM操作进行全面解析，探讨其工作原理、类型以及如何在实际环境中合理应用。

一、VACUUM基础概念

1.1 VACUUM的作用

在PostgreSQL中，当数据被更新或删除时，系统并不会立即释放物理空间，而是将其标记为“可重用”。随着时间推移，表中的死元组（已删除或已被新版本覆盖的数据）会越来越多，这不仅会导致存储空间利用率下降，也可能影响查询性能，因为查询需要遍历这些无用的元组。而VACUUM命令的主要任务就是清理这些死元组，并回收相应的存储空间。

1.2 VACUUM的工作方式

执行VACUUM命令后，PostgreSQL会扫描表并：

• 删除无效的行版本

• 更新表的统计信息以提高查询计划器的准确性

• 如果启用了autovacuum_freeze_max_age参数，还会防止事务ID过多导致的老化问题

对于支持MVCC（多版本并发控制）的PostgreSQL，VACUUM还负责冻结旧的事务ID，确保事务可见性信息的有效管理。

二、VACUUM类型

2.1 VACUUM常规操作

普通的VACUUM命令仅能清除无效行版本和更新统计信息，但不能完全压缩表以释放磁盘空间。

VACUUM table_name;

2.2 VACUUM FULL操作

VACUUM FULL则更为激进，它不仅执行常规VACUUM的功能，还能彻底整理表，使其占用的空间最小化，类似于重建表的效果。但需要注意的是，VACUUM FULL会锁定整个表，在大表上可能造成较长时间的阻塞。

VACUUM FULL table_name;

2.3 自动Vacuum（Autovacuum）

为了减轻手动执行VACUUM的压力，PostgreSQL提供了Autovacuum后台进程，可以根据配置自动定期执行VACUUM。Autovacuum能够监控表的更新情况，并在满足一定条件时自动触发VACUUM操作。

三、VACUUM实验过程

为了直观地理解PostgreSQL的VACUUM操作，我们可以进行以下实验：

实验1：创建并填充测试表

-- 创建一个用于实验的表，并插入数据

CREATE TABLE test_vacuum (
    id SERIAL PRIMARY KEY,
    data TEXT
);

-- 填充10,000行数据

DO
$$
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO test_vacuum (data) VALUES (md5(random()::text));
  END LOOP;
END;
$$;

或

INSERT INTO test_vacuum (data)
SELECT md5(random()::text) FROM generate_series(1, 100000);

-- 记录初始表大小（以字节为单位）

SELECT pg_total_relation_size('test_vacuum') AS initial_table_size;

或

postgres=# \dt+ test_vacuum 

实验2：执行更新和删除操作以产生死元组

-- 执行大量更新操作，模拟事务更新

UPDATE test_vacuum SET data = md5(random()::text) WHERE id % 10 = 0;

-- 执行删除操作，生成被删除但未释放空间的数据

DELETE FROM test_vacuum WHERE id % 100 = 0;

-- 检查表中当前的行数与之前的总数做对比

SELECT COUNT(*) AS current_rows FROM test_vacuum;

实验3：执行常规VACUUM

-- 执行常规VACUUM命令

VACUUM test_vacuum;

-- 检查表的最新大小，常规VACUUM可能不会减少物理文件大小

SELECT pg_total_relation_size('test_vacuum') AS after_vacuum_size;

实验4：查询pg_stat_user_tables

视图观察影响

-- 查看`n_dead_tup`字段的变化，它反映了表中的死元组数量

SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'test_vacuum';

-- 同时也可以查看其他与vacuum相关的统计信息

\x
SELECT * FROM pg_stat_user_tables WHERE relname = 'test_vacuum';

实验5：执行VACUUM FULL

注意：在生产环境中应谨慎使用VACUUM FULL，因为它可能导致表锁定并在大表上消耗较长时间。

-- 执行VACUUM FULL，此操作会整理表空间并回收所有废弃空间

VACUUM FULL test_vacuum;

-- 再次检查表的大小，这次应该能看到明显的减小

SELECT pg_total_relation_size('test_vacuum') AS after_vacuum_full_size;

实验6：配置和观察Autovacuum行为

• 首先，确保Autovacuum已启用且设置合理：

SHOW autovacuum;
ALTER TABLE test_vacuum SET (autovacuum_enabled = ON,
                            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;

• 监控Autovacuum活动和表状态：

-- 观察当前正在进行的Autovacuum进程

SELECT datname, pid, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';  

-- 或者定期检查`pg_stat_user_tables`视图，查看n_dead_tup是否在减少

SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'test_vacuum';

通过以上步骤，您将更加深入地了解PostgreSQL VACUUM的执行过程、效果以及Autovacuum如何根据参数设置自动进行清理工作。实际应用中，请结合实际情况调整Vacuum相关参数，优化数据库性能。

四、VACUUM最佳实践

定期执行VACUUM：根据业务负载情况，设置合理的VACUUM策略，尤其针对频繁更新的大表。

• 启用并调优Autovacuum：大部分情况下，应依赖于Autovacuum来维护数据库健康。通过调整autovacuum_vacuum_threshold、autovacuum_vacuum_scale_factor等参数，可以更精确地控制何时启动自动VACUUM。

• 考虑使用VACUUM FULL的情况：虽然VACUUM FULL可以最大程度地释放磁盘空间，但由于其可能导致表锁定且执行耗时较长，因此建议在业务低峰期执行，并非作为日常维护工具。

• 监控Vacuum活动：利用pg_stat_user_tables视图或其他监控工具，了解Vacuum操作的状态和效果，以便及时调整相关参数。

总结来说，PostgreSQL的VACUUM是数据库维护和性能优化的重要组成部分，正确理解和运用VACUUM命令及其变种，对于保持数据库的良好运行状态具有重要意义。同时，随着数据库技术的发展和PostgreSQL新特性的引入，例如FREEZE窗口期、并行Vacuum等，VACUUM在处理大型数据集时的效率也在不断提升。