VACUUM 將磁碟空間返回給作業系統

VACUUM通常不會將磁碟空間歸還給作業系統，除非在某些特殊情況下。

從文件：

VACUUM刪除表和索引中的死行版本並標記可用空間以供將來重用的標準形式。但是，它不會將空間返回給作業系統，除非在特殊情況下，表末尾的一個或多個頁面變得完全空閒並且可以輕鬆獲得排他表鎖。相反，VACUUM FULL通過編寫沒有死空間的完整新版本的表文件來主動壓縮表。這可以最小化表的大小，但可能需要很長時間。它還需要額外的磁碟空間來儲存表的新副本，直到操作完成。

問題是：如何實現這個數據庫狀態one or more pages at the end of a table become entirely free？這可以通過 來完成VACUUM FULL，但我沒有足夠的空間來實現它。那麼還有其他可能嗎？

要將空間返回給作業系統，請使用VACUUM FULL. 在這期間，我想你會跑VACUUM FULL ANALYZE。我引用手冊：

FULL

選擇“full”vacuum，可以回收更多空間，但需要更長的時間並且會獨占鎖定表。此方法還需要額外的磁碟空間，因為它會寫入表的新副本並且在操作完成之前不會釋放舊副本。通常這應該只在需要從表中回收大量空間時使用。

大膽強調我的。

CLUSTER作為附帶效應，也實現了這一點。

PlainVACUUM通常無法實現您的目標（“表格末尾的一個或多個頁面完全免費”）。當機會出現時，它不會重新排序行，只會從文件的物理末尾修剪空頁面 - 就像您從手冊中引用的那樣。

當您在附加其他元組之前獲得INSERT一批行和它們時，您可以在物理文件的末尾獲得空頁。DELETE或者，如果刪除了足夠多的行，這可能是巧合。

還有一些特殊設置可能會阻止VACUUM FULL回收空間。看：

• 如何針對非常高頻的更新優化表？

在表格末尾準備空白頁以進行測試

系統列ctid代表一行的物理位置。您需要了解該列：

• 如何將 ctid 分解為頁碼和行號？

我們可以使用它並通過從最後一頁刪除所有行來準備一個表：

DELETE FROM tbl t
USING (
  SELECT (split_part(ctid::text, ',', 1) || ',0)')::tid     AS min_tid
       , (split_part(ctid::text, ',', 1) || ',65535)')::tid AS max_tid
  FROM   tbl
  ORDER  BY ctid DESC
  LIMIT  1
  ) d
WHERE  t.ctid BETWEEN d.min_tid AND d.max_tid;

現在，最後一頁是空的。這忽略了並發寫入。要麼您是唯一一個寫入該表的人，要麼您需要獲取寫鎖以防止出現競爭條件。

該查詢經過優化，可以快速辨識符合條件的行。a 的第二個數字tid是儲存為 unsigned 的元組索引int2，並且65535是該類型 ( 2^16 - 1) 的最大值，因此這是安全的上限。

db<>fiddle here （重用不同情況下的簡單表。）

舊的 sqlfiddle

測量行/表大小的工具：

• 測量 PostgreSQL 表行的大小

磁碟已滿

對於任何這些操作，您都需要在磁碟上留出迴旋餘地。還有社區工具pg_repack可以替代VACUUM FULL/ CLUSTER。它避免了獨占鎖，但也需要可用空間來使用。手冊：

需要兩倍於目標表和索引的可用磁碟空間。

作為最後的手段，您可以執行轉儲/恢復週期。這也消除了表和索引中的所有膨脹。密切相關的問題：

• 我需要在沒有可用磁碟空間的情況下執行 VACUUM FULL

那邊的答案相當激進。如果您的情況允許（沒有外鍵或其他引用阻止行刪除），並且沒有對錶的並發訪問），您可以：

將表轉儲到從具有大量磁碟空間的****遠端電腦連接的磁碟（for ）：-a``--data-only

從遠端 shell，轉儲表數據：

pg_dump -h <host_name> -p <port> -t mytbl -a mydb > db_mytbl.sql

在 pg 會話中，TRUNCATE該表：

-- drop all indexes and constraints here for best performance
TRUNCATE mytbl;

從遠端 shell，恢復到同一個表：

psql -h <host_name> -p <port> mydb -f db_mytbl.sql
-- recreate all indexes and constraints here

它現在沒有任何死行或膨脹。

但也許你可以更簡單？

• 您可以通過刪除（移動）不相關的文件在磁碟上騰出足夠的空間嗎？
• 您可以VACUUM FULL先一張一張地縮小表，從而釋放足夠的磁碟空間嗎？
• 您可以執行REINDEX TABLE或REINDEX INDEX從臃腫的索引中釋放磁碟空間嗎？

無論做什麼，都不要輕舉妄動。如有疑問，請先將所有內容備份到安全位置。

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