PostgreSQL VACUUM 之深入浅出 (一)

VACUUM 是 PostgreSQL MVCC (Multiversion concurrency control) 实现的核心机制之一，是 PostgreSQL 正常运行的重要保证。本文将通过实例演示 PostgreSQL 为什么需要做 VACUUM，以及一步一步精准触发 AUTOVACUUM， 到 VACUUM 优化实战，深入浅出，一看就懂。

以下测试是在 PostgreSQL 11 中进行。

通过以下 SQL 创建：

测试用户: alvin，普通用户，非 superuser

测试数据库: alvindb，owner 是 alvin

测试 schema: alvin，owner 也是 alvin

这里采用的是 user 与 schema 同名，结合默认的 search_path("$user", public)，这样操作对象(table, sequence, etc.)时就不需要加 schema 前缀了。

postgres=# CREATE USER alvin WITH PASSWORD "alvin";
CREATE ROLE
postgres=# CREATE DATABASE alvindb OWNER alvin;
CREATE DATABASE
postgres=# c alvindb
You are now connected to database "alvindb" as user "postgres".
alvindb=# CREATE SCHEMA alvin AUTHORIZATION alvin;
CREATE SCHEMA
alvindb=# c alvindb alvin
You are now connected to database "alvindb" as user "alvin".
alvindb=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

这要从 PostgreSQL MVCC UPDATE/DELETE 实现讲起。

下面通过简单演示 PostgreSQL 中 UPDATE/DELETE 时底层数据变化，揭秘其 MVCC 设计的艺术。

为了方便看其底层数据，通过 superuser postgres 创建 extension pageinspect:

$ psql -d alvindb -U postgres
alvindb=# CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION
alvindb=# dx pageinspect
                              List of installed extensions
    Name     | Version | Schema |                      Description                      
-------------+---------+--------+-------------------------------------------------------
 pageinspect | 1.7     | public | inspect the contents of database pages at a low level
(1 row)

首先，创建测试表

$ psql -d alvindb -U alvin
alvindb=> 
CREATE TABLE tb_test_vacuum (
    test_id BIGSERIAL PRIMARY KEY,
    test_num BIGINT
);
CREATE TABLE

插入 3 条测试数据

alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(1,3,1) gid;
INSERT 0 3
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
       3 |        3
       2 |        2
       1 |        1
(3 rows)

查看其底层数据。

alvindb=> SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;
ERROR:  must be superuser to use raw functions

可以看到底层数据只有 superuser 才可以查看，这里另打开一个窗口，用 superuser 用户 postgres 查看。

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

这里 t_xmin 为其插入时 transaction id。

下面删除 2 条数据:

alvindb=> DELETE FROM tb_test_vacuum WHERE test_id = 2;
DELETE 1
alvindb=> DELETE FROM tb_test_vacuum WHERE test_id = 3;
DELETE 1
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
       1 |        1
(1 row)

此时在第二个窗口再看其底层数据

alvindb=> SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

这时你会发现，实际数据并未被删除。只是修改了 t_xmax，t_infomask2 和 t_infomask。t_xmax 为删除时的 transaction id，t_infomask2 和 t_infomask 为各种标志位，这里显示的是其二进制转换后的十进制。

为什么不直接物理删除数据呢？

主要是出于以下考虑:

这些被删除的数据可能还在被其他事务访问，所以不能直接删除。这就是所谓的 MVCC 中的 multi version，即多版本，不同事务访问的可能是不同版本的数据。transaction id 可以理解为版本号。其他事务可能还在访问 t_xmax 为 15400741 或 15400742 的数据。

为什么有的其他数据库 MVCC 实现底层数据就不是这样呢？

Oracle 中将要删除数据转移到了 UNDO tablespace 中，供其他事务访问，以实现 MVCC。

PostgreSQL 为什么这么实现呢？

大家可以想一下，“转移数据” 与 “改标志位”，哪个 cost 高呢？当然是 “改标志位” 既简单又高效了！可见 PostgreSQL 设计之巧妙。

另外，PostgreSQL 这样做还有一个好处。

Oracle DBA 都非常熟悉 ORA-01555: snapshot too old，其原因是 UNDO tablespace 大小毕竟是有限的，存储的老版本数据也是有限的，Oracle 中解决 snapshot too old 一个办法就是增大 UNDO tablespace。PostgreSQL 中这样保留老版本数据，可以说磁盘有多大，“UNDO tablespace” 就有多大，就不会出现类似类似 snapshot too old 这样的问题。

但凡事都有两面性。

PostgreSQL 中这样保留老版本数据有什么弊端呢？

老版本的数据是可能有其他事务需要访问，但随着时间的推移，这些事务终将结束，对应老版本的数据终将不被需要，它们将不断占用甚至耗尽磁盘空间，使数据访问变得很慢，这就是 PostgreSQL 中的 Bloat ，即膨胀。

PostgreSQL 中的 bloat 问题如何解决呢？

就是 VACUUM。可以理解为“回收空间”。

现在对表 alvin.tb_test_vacuum 进行 VACUUM 操作。

alvindb=> VACUUM VERBOSE tb_test_vacuum;
INFO:  vacuuming "alvin.tb_test_vacuum"
INFO:  scanned index "tb_test_vacuum_pkey" to remove 2 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  "tb_test_vacuum": removed 2 row versions in 1 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "tb_test_vacuum_pkey" now contains 1 row versions in 2 pages
DETAIL:  2 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "tb_test_vacuum": found 2 removable, 1 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 15400744
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

可以看到，VACUUM 不仅针对表数据，还包括索引。即不仅表数据可造成 Bloat (膨胀)，索引也会。

pageinspect extension 除了可以用 heap_page_items 看底层数据，也可以通过 bt_page_items 看其索引底层数据。在此不再查看索引底层数据，感兴趣可以执行如下 function 自行测试。

SELECT * FROM bt_page_items("index_name", 1);

在第二个窗口重新查看表底层数据:

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

可以看到，老版本数据已被清除。此时回收的空间新插入的数据使用，但并未返回给操作系统。

如何将回收的空间真正返回给操作系统呢？

就是 VACUUM FULL 操作:

alvindb=> VACUUM FULL VERBOSE tb_test_vacuum;
INFO:  vacuuming "alvin.tb_test_vacuum"
INFO:  "tb_test_vacuum": found 0 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.01 s, system: 0.01 s, elapsed: 0.08 s.
VACUUM

在第二个窗口查看表底层数据:

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

可以看到，老版本数据已彻底回收了。

但要注意，生产环境需要谨慎使用 VACUUM FULL，因为它将在表上加 ACCESS EXCLUSIVE 锁，即连 SELECT 也不可以。除非应用端可以计划不访问该表。

上面通过 DELETE 演示了为什么需要做 VACUUM。

那么 UPDATE 在 PostgreSQL 中是如何实现的呢？它会不会产生 Bloat (膨胀) 呢？

执行 UPDATE 操作如下:

alvindb=> UPDATE tb_test_vacuum SET test_num = 1 WHERE test_id = 1;
UPDATE 1

在第二个窗口查看表底层数据:

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

可以看到，UPDATE 其实是 DELETE + INSERT。

为什么 PostgreSQL 如此实现 UPDATE 呢？

是因为 DELETE + INSERT 执行效率高？直接修改原数据不可以么？

因为老版本数据有可能还被其他事务需要！这是 MVCC 实现所需要的。

当然，相比 Oracle 中将老版本数据转移到 UNDO tablespace， DELETE + INSERT 中的 DELETE 减少了 I/O，因为其只修改了标志位而已。

那么只有 UPDATE 和 DELETE 会产生 Bloat (膨胀) 吗？ INSERT 会吗？

INSERT 不是只插入数据吗？它怎么会产生 Bloat (膨胀) 呢？

接下来看下面的 case。

在事务中，ROLLBACK INSERT 的数据:

alvindb=> TRUNCATE tb_test_vacuum;
TRUNCATE TABLE
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(1,1,1) gid;
INSERT 0 1
alvindb=> BEGIN;
BEGIN
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(2,3,1) gid;
INSERT 0 2
alvindb=> ROLLBACK;
ROLLBACK
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
       8 |        1
(1 row)

在第二个窗口查看表底层数据：

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

可以看到，在事务中，PostgreSQL 中 ROLLBACK 时并未删除已 INSERT 的数据。

进一步测试 ROLLBACK UPDATE。

alvindb=> TRUNCATE tb_test_vacuum;
TRUNCATE TABLE
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(1,1,1) gid;
INSERT 0 1
alvindb=> BEGIN;
BEGIN
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
      12 |        1
(1 row)
alvindb=> UPDATE tb_test_vacuum SET test_num = test_num + 1 WHERE test_id = 12; 
UPDATE 1
alvindb=> SELECT clock_timestamp();
        clock_timestamp        
-------------------------------
 2021-11-14 18:25:11.651518+08
(1 row)

此时在第二个窗口查看表底层数据：

接下来在第一个窗口 ROLLBACK：

alvindb=> ROLLBACK;
ROLLBACK
alvindb=> SELECT clock_timestamp();
        clock_timestamp        
-------------------------------
 2021-11-14 18:25:35.948455+08
(1 row)
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
      12 |        1
(1 row)

再在第二个窗口查看表底层数据：

如果反复测试会发现，如果 COMMIT，其会修改标志位；如果 ROLLBACK ，PostgreSQL 什么也不做，因为标志位未修改，其仍不可见，即使 t_xmax 为 0。

相比 Oracle 中的 UPDATE 先将老版本中数据转移到 UNDO，ROLLBACK 再利用 UNDO 中原数据恢复，PostgreSQL 中的 ROLLBACK 避免了两次不必要的 IO，既提高了性能，又节省了时间。

根据上面实验，可以看到 UPDATE/DELETE/ROLLBACK 都有可能造成 Bloat (膨胀)。如果频繁更新的表长时间未做 VACUUM，VACUUM 完之后仍会占用很大空间，Bloat (膨胀) 仍然存在。生产又不能随便做 VACUUM FULL 回收空间 。

那么如何有效减少 Bloat (膨胀)?

在计划内大量更新数据等情况，可以根据需要手动 VACUUM，这样回收的空间可供下次大量更新数据使用，这样可以有效减少 Bloat (膨胀)。

VACUUM 除了回收空间，还有其他作用吗？

transaction id (事务 id) 是 32 位的，即最多有 2 的 32 次方，即 4294967296 个事务 id。中国人口按 14 亿算，一人也就能分配 3 个事务 id。所以 transaction id 范围是非常有限的，那么 PostgreSQL 是如何解决这个问题的呢？

从下图可以看出，PostgreSQL 是循环利用 transaction id 的，这样，transaction id 就无穷无尽的了。

以当前 transaction id 是 100 为例，大于 100 的约 21 亿 个事务对事务 100 不可见，小于 100 的约 21 亿 个事务对事务 100 可见。如果 transaction id 一直没有回收，直至 transaction id 耗尽，就会产生 wraparound (回卷) 问题，原来可见的突然变得不可见了，数据就“凭空消失”了。

那么 VACUUM 是如何回收 transaction id 的？是通过 FREEZE 对所有事务可见的数据。由于篇幅有限，且实际工作中基本不需要对 FREEZE 相关参数进行优化，FREEZE 将通过另外一篇文章单独讲述，本文不对 FREEZE 展开。

应用程序一般会有频繁的更新，不断造成 Bloat (膨胀) 及消耗 transaction id，总不能都手动 VACUUM 吧？

有没有自动的方式呢？当然！

PostgreSQL VACUUM 之深入浅出

华山论剑之 PostgreSQL sequence

[PG Upgrade Series] Extract Epoch Trap

[PG Upgrade Series] Toast Dump Error

GitLab supports only PostgreSQL now

MySQL or PostgreSQL?

PostgreSQL hstore Insight

ReIndex 失败原因调查

PG 数据导入 Hive 乱码问题调查

原文地址：https://www.cnblogs.com/dbadaily/archive/2022/02/24/vacuum1.html