完整清理#

為什麼例行清理還不夠？#

例行清理能釋放的空間比頁面修剪多，但有時仍嫌不足。

若表格或索引檔案已經長大，VACUUM 能清出頁面內部的一些空間，卻很少能減少頁面數量。回收的空間只有在檔案最末端出現數個空頁面時才能還給作業系統——而這種情況並不常發生。

過大的檔案會導致一連串不愉快的後果：

• 全表（或全索引）掃描耗時更久
• 可能需要更大的緩衝快取（頁面是整頁被快取的，資料密度下降）
• B-tree 可能多出一個層級，拖慢索引存取
• 檔案在磁碟與備份中都佔用額外空間

若檔案中有用資料的比例已跌破某個合理水準，管理者可執行 VACUUM FULL 進行完整清理。此時表格與其所有索引都會從頭重建，資料被盡可能緊密地打包（並考量 fillfactor 參數）。

完整清理時，PostgreSQL 先完整重建表格，再逐一重建每個索引。物件重建期間新舊檔案必須同時存放在磁碟上，因此這個過程可能需要大量可用空間。

還必須記得：這項操作完全阻擋對表格的存取，讀寫皆然。

估算資料密度#

pgstattuple 擴充可估算儲存密度：

=> CREATE EXTENSION pgstattuple;
=> SELECT * FROM pgstattuple('vac') \gx
−[ RECORD 1 ]−−−−−−+−−−−−−−−−
table_len          | 70623232
tuple_count        | 500000
tuple_len          | 64500000
tuple_percent      | 91.33
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 381844
free_percent       | 0.54

tuple_percent 顯示有用資料（heap 元組）所佔空間的百分比。由於頁面內有各種中繼資料，這個值必然低於 100%，但此例仍相當高。

索引顯示的資訊略有不同，但 avg_leaf_density 意義相同：顯示 B-tree 葉頁面中有用資料的百分比。

=> SELECT * FROM pgstatindex('vac_s') \gx
−[ RECORD 1 ]−−−−−−+−−−−−−−−−−
tree_level         | 3
index_size         | 114302976
leaf_pages         | 13576
avg_leaf_density   | 53.88
leaf_fragmentation | 10.59

上述 pgstattuple 函式會完整讀取表格或索引以取得精確統計。對大型物件而言這可能太昂貴，因此該擴充也提供 pgstattuple_approx，它會跳過可見性映射中已標記的頁面，給出近似數字。

更快但更不精確的方法，是用系統目錄粗估資料量與檔案大小的比值。

實驗：刪掉 90% 的列會發生什麼#

=> DELETE FROM vac WHERE id % 10 != 0;
DELETE 450000
=> VACUUM vac;
=> SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,
        pg_size_pretty(pg_indexes_size('vac')) AS index_size;
 table_size | index_size
−−−−−−−−−−−−+−−−−−−−−−−−−
 67 MB      | 109 MB
(1 row)

例行清理完全沒有縮小檔案，因為檔案末端沒有空頁面。然而資料密度掉了約 10 倍：

=> SELECT vac.tuple_percent, vac_s.avg_leaf_density
FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;
 tuple_percent | avg_leaf_density
−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−
          9.13 |             6.71
(1 row)

執行 VACUUM FULL 後，舊檔案被新檔案取代（relfilenode 改變），大小大幅縮減，密度也回升：

=> VACUUM FULL vac;
=> SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,
        pg_size_pretty(pg_indexes_size('vac')) AS index_size;
 table_size | index_size
−−−−−−−−−−−−+−−−−−−−−−−−−
 6904 kB    | 6504 kB
(1 row)

=> SELECT vac.tuple_percent, vac_s.avg_leaf_density
FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;
 tuple_percent | avg_leaf_density
−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−
         91.23 |            91.08
(1 row)

索引的密度甚至比原本更高：根據既有資料從頭建立 B-tree，比逐列插入既有索引更有效率。

執行期間可用 pg_stat_progress_cluster 視圖追蹤進度（類似 VACUUM 的 pg_stat_progress_vacuum），其階段名稱與例行清理不同。

完整清理時的凍結#

表格被重建時，PostgreSQL 會順便凍結其元組——相較於其餘工作，這項操作幾乎不花成本：

=> SELECT * FROM heap_page('vac',0,0) LIMIT 5;
 ctid | state | xmin | xmin_age | xmax
−−−−−−−+−−−−−−−−+−−−−−−−+−−−−−−−−−−+−−−−−−
 (0,1) | normal | 861 f |        5 | 0 a
 ...

但這些頁面既未登記在可見性映射中，也未登記在凍結映射中，頁首也沒有取得可見性屬性（不像帶 FREEZE 選項的 COPY 那樣）。

情況只有在 VACUUM 被呼叫（或 autovacuum 被觸發）之後才會改善。這實質上意味著：即使某頁的所有元組都已在資料庫水平線之外，該頁仍必須被重寫一次。

其他重建方法#

完整清理的替代方案#

除了 VACUUM FULL，還有數個指令能完整重建表格與索引。它們全都會排他鎖住表格，全都會刪除舊資料檔並重新建立。

就程式而言，VACUUM FULL 只是 CLUSTER 指令一個「不需重新排序元組」的特例。

REINDEX：事實上 VACUUM FULL 與 CLUSTER 在重建索引時，底層用的就是這個指令。

TRUNCATE：它是「不帶 WHERE 子句的 DELETE」的邏輯等價物。但 DELETE 只是把 heap 元組標記為已刪除（之後仍須清理），TRUNCATE 則建立一個新的空檔案，通常快得多。

降低重建期間的停機時間#

有兩類外部方案：

• pg_repack 之類的擴充，能以近乎零停機重建表格與索引。仍需排他鎖，但只在流程的開頭與結尾、且只鎖很短時間。做法較複雜：重建期間對原表格所做的所有變更由觸發器保存下來，再套用到新表格；最後在系統目錄中把一張表換成另一張。
• pgcompacttable 工具提供非傳統解法：它執行多輪假更新（不改變任何資料），讓目前的列版本逐步往檔案開頭移動。在各輪更新之間，清理會移除過期元組並一點一點截斷檔案。

預防措施#

唯讀查詢#

檔案膨脹的原因之一，是把持資料庫水平線的長時間交易與密集資料更新同時發生。

長時間的唯讀交易本身不會造成任何問題。因此常見做法是把負載分散到不同系統：主伺服器上保留快速的 OLTP 查詢，把所有 OLAP 交易導向備援節點。

儘管這讓方案更昂貴、更複雜，這類措施有時是不可或缺的。

某些情況下，長交易是應用程式或驅動程式的 bug 所致，而非必要。若問題無法用文明的方式解決，管理者可訴諸兩個參數：

• old_snapshot_threshold：定義快照的最長存活時間。時間一到，伺服器就有權移除過期元組；若長交易仍需要它們，就會得到 snapshot too old 錯誤。
• idle_in_transaction_session_timeout：限制閒置交易的存活時間，達到門檻即中止該交易。

資料更新#

膨脹的另一個原因是同時修改大量元組。

若表格所有列都被更新，元組數量可能翻倍，而清理來不及介入。頁面修剪能減輕這個問題，但無法完全解決。

實測：一張 6936 kB 的表格在全表更新後直接漲到 14 MB。

對策：減少單筆交易所做的變更數量，把它們分散到不同時間；如此清理就能刪除過期元組，並在既有頁面內釋出空間給新元組。

假設每次列更新可以獨立提交，可用以下查詢作為挑選指定大小批次的模板：

SELECT ID
FROM table
WHERE filtering the already processed rows
LIMIT batch size
FOR UPDATE SKIP LOCKED

這段程式碼選出並立即鎖住一組不超過指定大小的列。已被其他交易鎖住的列會被跳過，它們下次會進入另一個批次。

這是相當彈性且方便的解法：批次大小容易調整，失敗時也容易重啟操作。

實測驗證：第一批更新後表格從 6904 kB 微增至 7064 kB；此後只要在批次之間執行 VACUUM，大小就幾乎維持不變（7072 kB）——因為新元組取代了被移除的元組。