RDS PostgreSQL支援使用pg_squeeze外掛程式,該外掛程式提供了一種線上重新組織和清理表的功能,在不影響線上讀寫的前提下,減少表和索引佔用的空間,提高空間利用率。
前提條件
背景資訊
PostgreSQL使用MVCC(Multi-Version Concurrency Control)來允許多個事務同時讀取資料庫中的同一資料而不發生衝突。在此模型中,當資料被更新(UPDATE)或刪除(DELETE)時,未經處理資料不會被直接覆蓋或刪除,而是會被標記為死元組(dead tuple)。這些死元組對於新事務是不可見的,但是它們仍舊佔據著磁碟空間,導致了表的膨脹。
使用autovacuum
等方式清理完死元組後,表膨脹並不會完全消失,因為死元組之前佔用的空間並未立即被重新使用。當這些空閑空間非常多時,也會引起表膨脹。目前社區提供了vacuum full
命令來回收這些空閑空間,然而這一操作會使用最進階別的互斥鎖來鎖定表,導致在vacuum full
期間無法訪問該表。
功能介紹
pg_squeeze外掛程式通過建立表的一個壓縮副本來重建表,可以去除表中未使用的磁碟空間,具有如下優勢:
線上清理,不阻塞讀寫。
無需通過用戶端,直接執行SQL即可操作。
應用情境
對錶進行了頻繁的增刪改操作,導致表的膨脹率較高。
通過以下SQL語句,可以檢測表的膨脹率:
CREATE EXTENSION pgstattuple; --create extension
select *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('your_relation');
表膨脹率指表中未使用空間佔總空間的比例,計算方式為:
1 - 活元組空間/表的總空間
。執行此操作會進行全表掃描。
注意事項
被清理的表必須具備唯一鍵。
執行清理時會消耗大量的IO,建議在業務低峰期運行。
建立和刪除外掛程式
建立外掛程式:
CREATE EXTENSION pg_squeeze;
說明如果需要清理多個資料庫下的表,需要在每個目標資料庫中建立該外掛程式。
刪除外掛程式:
DROP EXTENSION pg_squeeze;
使用樣本
清理前,需要在目標資料庫中建立外掛程式。
臨時清理
例如,清理public schema下的表bar
。
SELECT squeeze.squeeze_table('public', 'bar');
自動檢測並清理
建立自動清理任務。
例如,在每周三和周五的22:30,自動清理public schema下的表
foo
。說明建立自動清理任務的詳細文法請參見pg_squeeze。
只能清理當前資料庫下的表。
INSERT INTO squeeze.tables (tabschema, tabname, schedule) VALUES ('public', 'foo', ('{30}', '{22}', NULL, NULL, '{3, 5}'));
開啟自動清理任務。
手動開啟自動清理任務
執行以下SQL手動開啟自動清理任務,只能清理當前資料庫下的表。
SELECT squeeze.start_worker(); -- 開啟
關閉自動清理任務。
SELECT squeeze.stop_worker(); -- 關閉
自動開啟自動清理任務
在RDS控制台配置參數並重啟資料庫,自動開啟清理任務。配置參數的詳細操作請參見設定執行個體參數。
例如,以rds_superuser的身份(必須是高許可權使用者)清理資料庫
database1
和database2
:說明清理前,已分別在資料庫
database1
和database2
中建立了pg_squeeze外掛程式,詳情請參見建立和刪除外掛程式。squeeze.worker_autostart = 'database1 database2' squeeze.worker_role = rds_superuser
相關文檔
更多資訊請參見pg_squeeze。