PostgreSQL VACUUM 之深入淺出 (一)

2022 年 2 月 24 日
筆記
PostgreSQL

前言

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 為什麼需要做 VACUUM

這要從 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 吧？

有沒有自動的方式呢？當然！