極光真空不刪除凍結的空白頁面？

我最近故意刪除了我們一張表中的大部分數據。通常，該表主要是追加的，有一個短視窗，在此期間可能會在原始插入之後發生更新。但是，最近大部分數據都被孤立了，我們決定將其刪除。

our_db=> delete from my_schema.table where a_column not in (select distinct value from my_schema.another_table);
DELETE 6418263

我希望 VACUUM 能夠處理現在大部分為空的頁面，但它沒有這樣做！

our_db=> vacuum(analyze, verbose, disable_page_skipping) my_schema.table;
INFO:  vacuuming "my_schema.table"
INFO:  index "table_pkey" now contains 2 row versions in 46486 pages
DETAIL:  0 index row versions were removed.
46481 index pages have been deleted, 46481 are currently reusable.
CPU: user: 0.21 s, system: 0.00 s, elapsed: 2.76 s.
INFO:  index "table_idx1" now contains 2 row versions in 98919 pages
DETAIL:  0 index row versions were removed.
98914 index pages have been deleted, 98914 are currently reusable.
CPU: user: 0.47 s, system: 0.00 s, elapsed: 5.50 s.
INFO:  index "table_idx2" now contains 2 row versions in 22324 pages
DETAIL:  0 index row versions were removed.
22317 index pages have been deleted, 22317 are currently reusable.
CPU: user: 0.10 s, system: 0.00 s, elapsed: 1.63 s.
INFO:  index "table_idx3" now contains 2 row versions in 22252 pages
DETAIL:  0 index row versions were removed.
22245 index pages have been deleted, 22245 are currently reusable.
CPU: user: 0.11 s, system: 0.00 s, elapsed: 1.68 s.
INFO:  index "table_idx4" now contains 2 row versions in 22373 pages
DETAIL:  0 index row versions were removed.
22366 index pages have been deleted, 22366 are currently reusable.
CPU: user: 0.09 s, system: 0.01 s, elapsed: 1.39 s.
INFO:  index "table_idx5" now contains 2 row versions in 69761 pages
DETAIL:  0 index row versions were removed.
69755 index pages have been deleted, 69755 are currently reusable.
CPU: user: 0.32 s, system: 0.00 s, elapsed: 4.98 s.
INFO:  index "table_idx6" now contains 2 row versions in 71399 pages
DETAIL:  0 index row versions were removed.
71388 index pages have been deleted, 71388 are currently reusable.
CPU: user: 0.31 s, system: 0.02 s, elapsed: 5.55 s.
INFO:  "table": found 0 removable, 2 nonremovable row versions in 754123 out of 754123 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6840941
There were 6439255 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 4.55 s, system: 0.20 s, elapsed: 53.89 s.
INFO:  vacuuming "pg_toast.pg_toast_16458"
INFO:  index "pg_toast_16458_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: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_toast_16458": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6840969
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.
INFO:  analyzing "my_schema.table"
INFO:  "table": scanned 30000 of 754123 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM

只有兩行是活動的，但仍有超過 750000 頁被佔用，如果需要順序掃描，則進行掃描：

our_db=> SELECT count(id) FROM my_schema.table;
count 
-------
    2
(1 row)

our_db=> explain analyze SELECT count(content) FROM my_schema.table;
                                                       QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=754123.00..754123.01 rows=1 width=8) (actual time=26795.676..26795.676 rows=1 loops=1)
  ->  Seq Scan on table  (cost=0.00..754123.00 rows=1 width=1036) (actual time=25683.699..26795.666 rows=2 loops=1)
Planning time: 0.052 ms
Execution time: 26795.703 ms
(4 rows)

啟用 disable_page_skipping 後，為什麼 VACUUM 不擺脫空的凍結頁面？

這與凍結無關。

VACUUM不會縮小表格，只是它會截斷表格末尾的任何空頁。顯然 autovacuum 作業比你的 manual 快VACUUM，因為沒有死元組了。

要縮小和重組表，請執行

VACUUM (FULL) my_schema.table;

但請注意，這將在操作期間以獨占方式鎖定表。

引用自：https://dba.stackexchange.com/questions/267970