Postgresql
為什麼 VACUUM ANALYZE 不清除所有死元組？
在對一些較大的表進行重大更改
VACUUM ANALYZE VERBOSE後，我們會在它們上執行“手冊” 。DELETE/INSERT這似乎沒有問題，儘管有時一張桌子的VACUUM工作會執行幾個小時（有關類似問題和推理，請參閱這篇文章）。在進行更多研究時，我發現即使在執行後我們也有包含大量死元組的大型表
VACUUM。例如，以下是此響應中的查詢產生的一些統計資訊。-[ RECORD 50 ]--+--------------------------- relname | example_a last_vacuum | 2014-09-23 01:43 last_autovacuum | 2014-08-01 01:19 n_tup | 199,169,568 dead_tup | 111,048,906 av_threshold | 39,833,964 expect_av | * -[ RECORD 51 ]--+--------------------------- relname | example_b last_vacuum | 2014-09-23 01:48 last_autovacuum | 2014-08-30 12:40 n_tup | 216,596,624 dead_tup | 117,224,220 av_threshold | 43,319,375 expect_av | * -[ RECORD 52 ]--+--------------------------- relname | example_c last_vacuum | 2014-09-23 01:55 last_autovacuum | 2014-09-23 18:25 n_tup | 309,831,136 dead_tup | 125,047,233 av_threshold | 61,966,277 expect_av | *最後一個欄位表明這些（以及大多數表）將滿足 autovacuum 的門檻值。但是，剛剛
VACUUM ANALYZE VEBOSE在每個表上執行，死元組計數不應該為 0（或接近 0，而不是 300M 的 125M）嗎？該文件指出：
VACUUM 回收死元組佔用的儲存空間。
這是否意味著我們
VACUUM的工作不正常？更新
根據響應中的請求，這裡有一些來自
VERBOSE作業的日誌：INFO: vacuuming "public.example_1" INFO: scanned index "idx_example_1_on_gp_id_and_dd_id" to remove 378386 row versions DETAIL: CPU 1.83s/3.42u sec elapsed 23.01 sec. INFO: scanned index "index_example_1_on_q_id" to remove 378386 row versions DETAIL: CPU 2.10s/3.91u sec elapsed 18.92 sec. INFO: "example_1": removed 378386 row versions in 7085 pages DETAIL: CPU 0.09s/0.05u sec elapsed 0.19 sec. INFO: index "idx_example_1_on_gp_id_and_dd_id" now contains 30347438 row versions in 291065 pages DETAIL: 378386 index row versions were removed. 165587 index pages have been deleted, 164287 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "index_example_1_on_q_id" now contains 30347438 row versions in 333287 pages DETAIL: 378386 index row versions were removed. 152773 index pages have been deleted, 152757 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "example_1": found 1773 removable, 401984 nonremovable row versions in 14438 out of 1493006 pages DETAIL: 0 dead row versions cannot be removed yet. There were 10567 unused item pointers. 0 pages are entirely empty. CPU 4.26s/7.51u sec elapsed 46.10 sec. INFO: vacuuming "pg_toast.pg_toast_17917" INFO: index "pg_toast_17917_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_17917": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.example_1" INFO: "example_1": scanned 30000 of 1493006 pages, containing 611502 live rows and 0 dead rows; 30000 rows in sample, 40563141 estimated total rows該表現在在統計資訊中顯示 0 個死元組。今天早上大多數表都是低得多的死元組，所以我們的
VACUUM或 autovacuum 都在工作。我們確實有一些不輸出任何內容但仍顯示死元組的表：
-[ RECORD 49 ]--+--------------------------- relname | example_2 last_vacuum | 2014-09-23 02:23 last_autovacuum | 2014-09-02 14:30 n_tup | 117,914,944 dead_tup | 34,507,388 av_threshold | 23,583,039 expect_av | *有幾次我在日誌中看到索引會被一遍又一遍地檢查。這似乎對應於長期執行的
VACUUM工作。知道為什麼嗎？這是否只是解決記錄鎖定問題（我認為在此作業執行期間沒有發生任何寫入。）INFO: vacuuming "public.example_2" ... INFO: scanned index "index_example_2_on_gsg_id_and_dd_id" to remove 2795959 row versions DETAIL: CPU 3.88s/16.54u sec elapsed 23.09 sec. INFO: scanned index "index_example_2_on_q_id" to remove 2795959 row versions DETAIL: CPU 6.74s/21.13u sec elapsed 84.64 sec. INFO: "example_2": removed 2795959 row versions in 48214 pages DETAIL: CPU 0.71s/0.32u sec elapsed 33.65 sec. INFO: scanned index "index_example_2_on_gsg_id_and_dd_id" to remove 2591011 row versions DETAIL: CPU 2.84s/16.11u sec elapsed 19.28 sec. INFO: scanned index "index_example_2_on_q_id" to remove 2591011 row versions DETAIL: CPU 5.46s/22.70u sec elapsed 130.57 sec. INFO: "example_2": removed 2591011 row versions in 45539 pages DETAIL: CPU 0.67s/0.38u sec elapsed 15.16 sec. INFO: index "index_example_2_on_gsg_id_and_dd_id" now contains 123807784 row versions in 1560915 pages DETAIL: 108836958 index row versions were removed. 1100790 index pages have been deleted, 718471 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.25 sec. INFO: index "index_example_2_on_q_id" now contains 123807784 row versions in 1886087 pages DETAIL: 110336259 index row versions were removed. 1058063 index pages have been deleted, 266983 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.07 sec. INFO: "example_2": found 124808 removable, 1355901 nonremovable row versions in 2086343 out of 6966379 pages DETAIL: 0 dead row versions cannot be removed yet. There were 7858495 unused item pointers. 0 pages are entirely empty. CPU 595.49s/2130.13u sec elapsed 5656.34 sec. INFO: vacuuming "pg_toast.pg_toast_18079" INFO: index "pg_toast_18079_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_18079": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.example_2" INFO: "example_2": scanned 30000 of 6966379 pages, containing 528443 live rows and 522 dead rows; 30000 rows in sample, 152953760 estimated total rows
VACUUM 只能刪除長期死的死元組，即對所有可能的用途都死了。如果您有長期事務，它們可能會阻止刪除最近死亡的元組。
這是一個長期事務阻止刪除的範例：
INFO: "pgbench_accounts": found 0 removable, 2999042 nonremovable row versions in 49181 out of 163935 pages DETAIL: 2999000 dead row versions cannot be removed yet.它不是真正長期存在的事務，而是長期存在的快照。當然，長時間執行的 select 或 insert 語句會做到這一點。對於高於 read-committed 的隔離級別，整個事務將保留快照直到它關閉，所以如果有些人打開一個可重複的讀取事務，然後在沒有送出的情況下休假，那將是一個問題。掛起的準備事務也會（如果您不知道準備事務是什麼，那麼您可能沒有使用它們）。
您展示的範例並不表明存在問題，但您還說問題已經解決。如果這是一個反復出現的問題，您可能應該開始記錄 VACUUM VERBOSE 語句的輸出，以便您可以找到涵蓋問題存在期間的資訊。
多次通過索引是由於您的 maintenance_work_mem 設置。每次遍歷索引時，它只能為每 6 個字節的記憶體刪除一個元組，如果需要刪除更多，則需要進行多次遍歷。所以增加maintenance_work_mem 會有所幫助。