既然講到 Visibility Map，就要提到 PostgreSQL 中的資料回收機制了，如果沒有定期將資料庫清理乾淨的話，可能會影響到 Visibility Map 以及 Index-Only Scan 的效能喔～

在 Postgresql 中當 UPDATE 或 DELETE 時，其實都不是去更新原本的 row，而是把原本的 row 標記為 dead tuple，再新增新的資料。所以在進行完 UPDATE 或 DELETE 後，這些 dead tuple 仍然存在在資料庫內，並且佔據空間的。

那要怎麼清理這些不會用到的 dead tuple 呢？

VACUUM

使用 VACUUM，也就是打掃我們的資料庫，可以把這些 dead tuple 清理乾淨。

但是為什麼 VACUUM 與 Visibility Map 有關呢？在PostgreSQL 中，如果有一頁（page）包含了 dead tuple，那麼他的 all_visible 就會是 false，當下了 VACUUM 指令後，他會去打掃那一頁，掃完之後就會把 all_visible 變成 true。

Vacuum maintains a visibility map for each table to keep track of which pages contain only tuples that are known to be visible to all active transactions.

看到這邊你會發現，如果沒有定期去打掃資料庫，導致 all_visible 很多是 false 的話，其實是會影響 Index-Only Scan 的。因為每次查詢時發現 all_visible = false，就會跑去 Heap Fetch，這樣就沒有辦法正常執行 Index-Only Scan 了。

This has two purposes. First, vacuum itself can skip such pages on the next run, since there is nothing to clean up. Second, it allows PostgreSQL to answer some queries using only the index.

查看 dead tuple

我們可以使用 pg_stat_all_tables 來查詢 n_dead_tup ，了解有多少 dead tuple 存在在 table 內。

SELECT relname, n_dead_tup, last_vacuum, last_analyze
FROM pg_stat_all_tables
WHERE relname = 'products';

autovacuum

在 PostgreSQL 中其實他定期會去做 autovacuum（預設為開啟），但如果有發現效能問題的話，也可以自己手動下 VACUUM 指令清理 dead tuple。

PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples.

VACCUM ANALYZE

VACUUM & ANALYZE 其實是在做不一樣的事，VACUUM 是在清除 dead tuple，ANALYZE 是用來讓 PostgreSQL 更新 table 的相關數據並且記錄在 pg_statistic，Query Planner 會透過 pg_statistic 找到最有效率的查詢方式。

如果想要定期清理和更新 PostgreSQL 內的資料，就可以使用這個指令。

ANALYZE collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.

看一下 pg_statistic table 是長什麼樣子，關於各欄位的意思可以看這篇文件。

SELECT *
FROM pg_statistic
WHERE starelid = 'products'::regclass;

VACUUM FULL

如果沒有加上 FULL，只有 VACUUM 的情況下，dead tuples 空出來的空間可以供 table 再存取其他的 data，但事實上並不會釋放 disk space。只有在使用 VACUUM FULL 的情況下，才可以看到 disk space 被釋放。

使用 VACUUM FULL 時會將 table lock 住，所以在 production 使用時需要注意，官方文件是建議在有大量的資料被刪除的情況下，再考慮使用。

Selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table.

Usually this should only be used when a significant amount of space needs to be reclaimed from within the table.

測試：觀察 PostgreSQL 的資料回收機制

1. 先檢查原本 products table 有三萬筆資料所使用的大小：

SELECT pg_size_pretty(pg_relation_size('products'))

2. 接下來刪除 10000 筆資料，會發現 dead tuple 出現 10000，但 pg_size_pretty 維持一樣。

DELETE FROM products WHERE id BETWEEN 1 AND 10000

3. 執行 VACUUM 之後，dead tuples 變為 0 了！但是空間還沒有被釋放，pg_size_pretty 還是維持一樣的大小。

VACUUM products

4. 執行 VACUUM FULL 之後，pg_size_pretty 變少了（1176 KB → 1184 KB），代表空間被釋放出來了。

整個流程會是這樣：

重點回顧

• Visibility Map 影響 Index-Only Scan：如果 page 內有 dead tuple，all_visible 會是 false，導致 Index-Only Scan 失效，必須回到 Heap Fetch。
• VACUUM 清除 dead tuple：PostgreSQL 使用 VACUUM 來清理 dead tuple，同時維護 Visibility Map，確保 all_visible 正確設置。
• autovacuum 可自動清理：PostgreSQL 內建 autovacuum，但在資料變更頻繁的情況下，可能需要手動執行 VACUUM。
• VACUUM ANALYZE 更新統計資訊：VACUUM ANALYZE 不僅清理 dead tuple，還會同時更新 table 的統計資訊，幫助 Query Planner 生成更準確、更有效率的查詢執行計畫。
• VACUUM FULL 徹底清理：VACUUM FULL 會釋放 disk space，但鎖定整個資料表，需要注意使用時機。

參考資料

https://www.postgresql.org/docs/current/sql-vacuum.html
https://www.postgresql.org/docs/current/routine-vacuuming.html