執行TRUNCATE或DROP TABLE命令後磁碟空間未釋放 - ApsaraDB RDS

在PostgreSQL中，使用TRUNCATE命令清理表資料，或使用DROP TABLE刪除表時，可能出現磁碟空間未立即釋放的問題。本文將介紹該現象的原因，並提供排查和解決方案。

問題原因

PostgreSQL執行TRUNCATE和DROP TABLE操作時，系統會在事務提交時對每一個要刪除的檔案進行unlink調用。unlink調用會解除inode的引用，但是如果此時有其他進程開啟了該檔案，檔案的內容將會保留在磁碟上，磁碟空間不會立即回收。只有當滿足以下條件之一時，系統才會釋放磁碟空間：

終止所有開啟了需要刪除檔案的進程。
開啟檔案的進程中，對檔案描述符（file descriptor，fd）進行了close調用來關閉對應檔案。

如果在執行TRUNCATE和DROP TABLE之前已經有串連開啟了表檔案，並且該串連在TRUNCATE和DROP TABLE執行後一直處於空閑（idle）狀態，那麼由於這個串連仍然持有對原始檔案的開啟引用，實際的磁碟空間不會立即被釋放。

解決方案

在出現執行TRUNCATE或DROP TABLE命令後未釋放磁碟空間的問題時，可以採用以下方法進行解決。

清理所有空間的串連。

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state='idle' AND backend_type='client backend';

在自建資料庫情境中，需要找到已經開啟但已被unlink的檔案的進程，並終止這些串連。
1. 在目標資料庫伺服器上執行lsof命令，尋找已經被刪除但仍被進程開啟的檔案。
```
lsof +L1
```
2. 執行如下SQL，終止對應串連。
```
SELECT pg_terminate_backend(pid);
```
  其中參數pid是要終止會話的進程ID。