RDS PostgreSQL支援通過外掛程式pg_repack線上清理資料表空間,有效解決因對全表大量更新等操作引起的表膨脹問題。pg_repack無需擷取排它鎖,相比CLUSTER或VACUUM FULL更加輕量化。
前提條件
執行個體大版本為RDS PostgreSQL 10或以上。
執行個體小版本為20240229及以上。如需升級核心小版本,請參見升級核心小版本。
說明當執行個體的小版本低於20240229時,使用該外掛程式可能會導致出現報錯:
ERROR: must be superuser to use xxx function
。
注意事項
pg_repack需要額外的儲存空間。全表repack時,剩餘儲存空間大小需要至少是待repack表大小的2倍。
pg_repack無法操作暫存資料表。
pg_repack無法操作GiST索引。
pg_repack運行時無法對repack操作中的表執行DDL。pg_repack會持有ACCESS SHARE鎖,禁止DDL執行。
重建表和索引時會佔用較多的磁碟IO,使用時請提前評估是否影響業務。以ESSD PL1雲端硬碟為例,repack 100GB的表時,會達到IO吞吐上限(250MB/s)。
功能介紹
pg_repack外掛程式支援全表repack和索引repack。
對整表資料進行repack的實現原理如下:
建立日誌表記錄對原表的變更。
在原表建立觸發器,將原表的INSERT、UPDATE和DELETE操作記錄到日誌表中。
建立新表,與原表的行列相同。
在新表中建立索引。
將日誌表裡的變更應用到新表。
在system catalog交換新舊錶。
刪除舊錶。
說明pg_repack會在第a、b步和第f、g步短暫持有ACCESS EXCLUSIVE鎖。其餘步驟pg_repack只需要持有原表的ACCESS SHARE鎖,不影響原表的INSERT、UPDATE和DELETE。
對錶中索引進行repack的實現原理如下:
以CONCURRENTLY方式建立新索引。
在system catalog交換新舊索引。
刪除舊索引。
外掛程式開啟與關閉
請使用高許可權帳號執行如下命令。
開啟外掛程式。
CREATE EXTENSION pg_repack;
關閉外掛程式。
DROP EXTENSION pg_repack;
安裝用戶端
pg_repack外掛程式需要配合用戶端使用,以ECS Alibaba Cloud Linux 3.2104及以上版本為例,參考以下命令:
安裝環境依賴。
sudo yum install postgresql* redhat-rpm-config libpq* openssl-devel readline-devel -y
添加環境變數。
export PATH=$PATH:/usr/lib64/pgsql/postgresql-12/bin
說明命令中的路徑及PostgreSQL版本僅為樣本,請根據實際情況進行替換。
下載pg_repack用戶端並編譯安裝。
wget https://github.com/reorg/pg_repack/archive/refs/tags/ver_1.4.6.tar.gz tar zxvf ver_1.4.6.tar.gz cd pg_repack-ver_1.4.6 make && make install
使用樣本
-- 檢查但不執行:--dry-run
pg_repack --dry-run --no-superuser-check --echo --no-order -h 串連串 -p 連接埠 -d database1 -U user --table schema1.table1
-- 檢查並執行
pg_repack --no-superuser-check --echo --no-order -h 串連串 -p 連接埠 -d database1 -U user --table schema1.table1
常見問題
Q:報錯“ERROR: pg_repack failed with error: You must be a superuser to use pg_repack”。
A:請使用-k或--no-superuser-check,避免沒有許可權。
全表repack時,無法通過此方式擁有許可權。
相關參考
pg_repack的詳細說明可參考pg_repack官方協助文檔。