PostgreSQL 如何查找需要收集的vacuum 表信息
前言
通常，在PostgreSQL中，由于經(jīng)常需要對表進行 UPDATE 和 DELETE，因此表會產(chǎn)生碎片空間。
在 PostgreSQL中，使用VACUUM 僅僅對需要執(zhí)行 VACUUM 表將已刪除的空間標(biāo)識為未使用，以便以后重用這些空間，但是不能立即將占用的空間返還給操作系統(tǒng)，因此需要使用 VACUUM FULL，才可以釋放空間，并立即將空間返還給操作系統(tǒng)。
實現(xiàn)腳本
記錄收集表創(chuàng)建
CREATE TABLE IF NOT EXISTS tab_vacuum_record (sqltext text);
收集需要VACUUM 表函數(shù)
CREATE OR REPLACE FUNCTION f_vacuum_tables()
RETURNS void AS
$FUNCTION$
DECLARE
v_tablename text;
v_dead_cond bigint;
v_sql text;
cur_tablename REFCURSOR;
v_vacuum_record text;
BEGIN
v_vacuum_record := 'tab_vacuum_record';
OPEN cur_tablename FOR SELECT tablename FROM pg_tables WHERE tablename !~ '^pg|^sql';
LOOP
FETCH cur_tablename INTO v_tablename;
SELECT n_dead_tup INTO v_dead_cond FROM pg_stat_user_tables WHERE relname = v_tablename;
IF v_dead_cond > 0 THEN
v_sql := 'INSERT INTO ' || v_vacuum_record || ' VALUES(' || chr(39) ||'VACUUM FULL ' || v_tablename ||';'|| chr(39) ||')';
EXECUTE v_sql;
END IF;
EXIT WHEN NOT FOUND;
END LOOP;
CLOSE cur_tablename;
END;
$FUNCTION$
LANGUAGE PLPGSQL;
SHELL腳本
#!/bin/bash
#獲取環(huán)境變量
CURRDIR=$(cd "$(dirname $0)";pwd)
TOPDIR=$(cd $CURRDIR/..;pwd)
CONFIG=$TOPDIR/conf/host.ini
CT_FILE=${TOPDIR}/sql/CREATE_VACCUM_TABLE_RECORD.sql
CT_FUNCTION=${TOPDIR}/sql/CHECK_NEEDS_VACUUM_TABLE_FUNCTION.sql
source $CONFIG
CONNINFO="psql -U $USER -d $DBNAME -h $HOSTADDR -p $PORT"
function check_status()
{
echo "檢查數(shù)據(jù)庫服務(wù)器狀態(tài)是否正常 !"
stat=`$CONNINFO -Aqt -c 'SELECT 1'`
if [ "${stat}" == "1" ];then
echo "服務(wù)器連接正常"
else
echo "服務(wù)器連接異常,退出"
exit -1;
fi
}
function create_table()
{
echo "創(chuàng)建收集需要vacuum的表"
$CONNINFO -f $CT_FILE
}
function create_function()
{
echo "創(chuàng)建收集需要 vacuum 表的函數(shù)"
$CONNINFO -f $CT_FUNCTION
}
check_status
create_table
create_function
執(zhí)行方式
postgres=# SELECT * FROM f_vacuum_tables();
f_vacuum_tables
-----------------
(1 row)
--創(chuàng)建測試表
postgres=# CREATE TABLE tab_test(id int);
--插入數(shù)據(jù)
postgres=# INSERT INTO tab_test SELECT id FROM generate_series(1,100000) as id;
INSERT 0 100000
--刪除數(shù)據(jù)
postgres=# DELETE FROM tab_Test WHERE id <= 10000;
DELETE 10002
postgres=# SELECT * FROM tab_vacuum_record ;
sqltext
-----------------------
VACUUM FULL tab_test;
(1 row)
該腳本也可以自己根據(jù)需要進行修改，詳細(xì)見github
補充：PostgreSQL中 Vacuum 略談
VACUUM doc
路由清理
PostgreSQL 需要定期維護清理，一般都是由守護進程自動清理的，我們只是需要參數(shù)調(diào)優(yōu)，也可以執(zhí)行腳本定時去清理回收。
Vacuumming Basics
PG不得不對每張表進行 Vacuum 命令，原因如下：
1、為了回收和再利用通過更新或者刪除行所占用的磁盤空間
2、為了更新被PG查詢計劃所使用的數(shù)據(jù)分析
3、為了更新只讀索引掃描的可見的集合
4、避免由于事務(wù)ID或者混合事務(wù)ID丟失歷史數(shù)據(jù)
由于這些原因，在進行頻繁的 VACUUM 操作時進行規(guī)定：
標(biāo)準(zhǔn) VACUUM
進行回收時，生產(chǎn)環(huán)境不影響數(shù)據(jù)庫庫的正常使用(SELECT、INSERT、UPDATE、DELETE),并行使用，清理時不允許對表結(jié)構(gòu)進行修改（ALTER TABLE）推薦使用該方案
VACUUM FULL
a、可以回收大量空間，但是比標(biāo)準(zhǔn)回收執(zhí)行慢
b、運行時需要鎖表
VACUUM 運行會導(dǎo)致讀寫性能比較差，所以需要調(diào)整一些參數(shù)降低影響
temp_file_limit = -1 #默認(rèn)-1表示不限制每個進程可使用的最大臨時文件限制，單位kb #max_files_per_process = 1000 #每個子進程允許同時打開文件的最大數(shù)量
在執(zhí)行 VACUUM 和 ANYLYZE 期間，系統(tǒng)會維護一個用于估算各種I/O操作所消耗的內(nèi)部計數(shù)器,當(dāng)該值達(dá)到vacuum_cost_limit的值時，該進程會休眠 vacuum_cost_delay 指定的時間，并重置計數(shù)器的值，繼續(xù)運行 VACUM 或者 ANYLYZE 操作
vacuum_cost_limit = 200 vacuum_cost_delay = 0 # 單位微秒，默認(rèn)為 0 沒有開啟
該參數(shù) vacuum_cost_delay 主要用于并發(fā)時降低I/O的影響,推薦為10
vacuum_cost_page_hit = 1 # 代表從緩存池查找共享的hash table并掃描 該`頁`的內(nèi)容
#的估計值
vacuum_cost_page_miss = 10 # 0-10000 credits
vacuum_cost_page_dirty = 20
NOTE
當(dāng)一張表中包含了大量數(shù)據(jù)時，同時進行刪除或者更新操作時，VACUUM 并不是最好的方案,
如果有該情況，則應(yīng)該使用 VACUU FULL ,當(dāng)執(zhí)行 ALTER TABLE 時，會重新 COPY整
個表和重新構(gòu)建索引，會進行執(zhí)行鎖，臨時占用和原始表大小的磁盤空間，直到新數(shù)據(jù)COPY完成。
升級執(zhí)行計劃
執(zhí)行計劃通過自己或者 VACUUM調(diào)用命令 ANALYZE 收集統(tǒng)計，
創(chuàng)建 表達(dá)式索引 能夠提高查詢執(zhí)行計劃
default_statistics_target = 100 #提高查詢的 析計劃
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
在我們?nèi)粘ｉ_發(fā)中,在設(shè)計數(shù)據(jù)庫字段的時候不可避免的都要created_time以及updated_time兩個時間戳字段,作用大家也都一目了然,下面將為大家詳細(xì)介紹PostgreSQL設(shè)置時間自動更新的方法,需要的朋友可以參考下2025-03-03
這篇文章主要介紹了PostgreSQL11修改wal-segsize的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PGSQL 實現(xiàn)查詢今天,昨天的數(shù)據(jù),一個月之內(nèi)的數(shù)據(jù)
這篇文章主要介紹了PGSQL 實現(xiàn)查詢今天,昨天的數(shù)據(jù),一個月之內(nèi)的數(shù)據(jù)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL是一種功能強大的開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng),以其穩(wěn)定性、高性能、擴展性和復(fù)雜查詢能力在眾多項目中得到廣泛應(yīng)用,本文將從基礎(chǔ)概念講起,逐步深入到高級特性、性能優(yōu)化和實戰(zhàn)應(yīng)用,幫助讀者全面掌握PostgreSQL,感興趣的朋友跟隨小編一起學(xué)習(xí)吧2025-08-08
這篇文章主要介紹了postgresql 查詢字符串中是否包含某字符的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Postgresql的select優(yōu)化操作(快了200倍)
這篇文章主要介紹了Postgresql的select優(yōu)化操作(快了200倍)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
本文主要介紹了PostgreSQL 六大索引的方法小結(jié),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2025-11-11
本文主要介紹了一主一從PostgreSQL架構(gòu),配置流復(fù)制賬戶及主從參數(shù)文件,執(zhí)行基礎(chǔ)備份,檢查復(fù)制槽和節(jié)點狀態(tài)以確認(rèn)搭建成功,下面就來具體介紹一下,感興趣的可以了解一下2025-07-07
這篇文章主要介紹了postgresql 導(dǎo)出建表語句的命令操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
今天小編就為大家分享一篇關(guān)于Ubuntu PostgreSQL安裝和配置的介紹，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03