PostgreSQL 如何查找需要收集的vacuum 表信息
前言
通常，在PostgreSQL中，由于经常需要对表进行UPDATE和DELETE，因此表会产生碎片空间。
在PostgreSQL中，使用VACUUM仅仅对需要执行VACUUM表将已删除的空间标识为未使用，以便以后重用这些空间，但是不能立即将占用的空间返还给操作系统，因此需要使用VACUUMFULL，才可以释放空间，并立即将空间返还给操作系统。
实现脚本
记录收集表创建
CREATETABLEIFNOTEXISTStab_vacuum_record (sqltexttext);
收集需要VACUUM表函数
CREATEORREPLACEFUNCTIONf_vacuum_tables()
RETURNSvoidAS
$FUNCTION$
DECLARE
v_tablenametext;
v_dead_condbigint;
v_sqltext;
cur_tablenameREFCURSOR;
v_vacuum_recordtext;
BEGIN
v_vacuum_record:='tab_vacuum_record';
OPENcur_tablenameFORSELECTtablenameFROMpg_tablesWHEREtablename!~'^pg|^sql';
LOOP
FETCHcur_tablenameINTOv_tablename;
SELECTn_dead_tupINTOv_dead_condFROMpg_stat_user_tablesWHERErelname=v_tablename;
IFv_dead_cond>0THEN
v_sql:='INSERTINTO'||v_vacuum_record||'VALUES('||chr(39)||'VACUUMFULL'||v_tablename||';'||chr(39)||')';
EXECUTEv_sql;
ENDIF;
EXITWHENNOTFOUND;
ENDLOOP;
CLOSEcur_tablename;
END;
$FUNCTION$
LANGUAGEPLPGSQL;
SHELL脚本
#!/bin/bash
#获取环境变量
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"
functioncheck_status()
{
echo"检查数据库服务器状态是否正常!"
stat=`$CONNINFO-Aqt-c'SELECT1'`
if["${stat}"=="1"];then
echo"服务器连接正常"
else
echo"服务器连接异常,退出"
exit-1;
fi
}
functioncreate_table()
{
echo"创建收集需要vacuum的表"
$CONNINFO-f$CT_FILE
}
functioncreate_function()
{
echo"创建收集需要vacuum表的函数"
$CONNINFO-f$CT_FUNCTION
}
check_status
create_table
create_function
执行方式
postgres=#SELECT*FROMf_vacuum_tables(); f_vacuum_tables ----------------- (1row) --创建测试表 postgres=#CREATETABLEtab_test(idint); --插入数据 postgres=#INSERTINTOtab_testSELECTidFROMgenerate_series(1,100000)asid; INSERT0100000 --删除数据 postgres=#DELETEFROMtab_TestWHEREid<=10000; DELETE10002 postgres=#SELECT*FROMtab_vacuum_record; sqltext ----------------------- VACUUMFULLtab_test; (1row)
该脚本也可以自己根据需要进行修改，详细见github
补充：PostgreSQL中Vacuum略谈
VACUUMdoc
路由清理
PostgreSQL需要定期维护清理，一般都是由守护进程自动清理的，我们只是需要参数调优，也可以执行脚本定时去清理回收。
VacuummingBasics
PG不得不对每张表进行Vacuum命令，原因如下：
1、为了回收和再利用通过更新或者删除行所占用的磁盘空间
2、为了更新被PG查询计划所使用的数据分析
3、为了更新只读索引扫描的可见的集合
4、避免由于事务ID或者混合事务ID丢失历史数据
由于这些原因，在进行频繁的VACUUM操作时进行规定：
标准VACUUM
进行回收时，生产环境不影响数据库库的正常使用(SELECT、INSERT、UPDATE、DELETE),并行使用，清理时不允许对表结构进行修改（ALTERTABLE）推荐使用该方案
VACUUMFULL
a、可以回收大量空间，但是比标准回收执行慢
b、运行时需要锁表
VACUUM运行会导致读写性能比较差，所以需要调整一些参数降低影响
temp_file_limit=-1#默认-1表示不限制每个进程可使用的最大临时文件限制，单位kb #max_files_per_process=1000#每个子进程允许同时打开文件的最大数量
在执行VACUUM和ANYLYZE期间，系统会维护一个用于估算各种I/O操作所消耗的内部计数器,当该值达到vacuum_cost_limit的值时，该进程会休眠vacuum_cost_delay指定的时间，并重置计数器的值，继续运行VACUM或者ANYLYZE操作
vacuum_cost_limit=200 vacuum_cost_delay=0#单位微秒，默认为0没有开启
该参数vacuum_cost_delay主要用于并发时降低I/O的影响,推荐为10
vacuum_cost_page_hit=1#代表从缓存池查找共享的hashtable并扫描该`页`的内容 #的估计值 vacuum_cost_page_miss=10#0-10000credits vacuum_cost_page_dirty=20
NOTE
当一张表中包含了大量数据时，同时进行删除或者更新操作时，VACUUM并不是最好的方案,
如果有该情况，则应该使用VACUUFULL,当执行ALTERTABLE时，会重新COPY整
个表和重新构建索引，会进行执行锁，临时占用和原始表大小的磁盘空间，直到新数据COPY完成。
升级执行计划
执行计划通过自己或者VACUUM调用命令ANALYZE收集统计，
创建表达式索引能够提高查询执行计划
default_statistics_target=100#提高查询的析计划
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。