postgres 表膨胀处理2（转）
一，关于vacuum
进行delete操作之后，表和索引的大小没有发生变化
vacuum 表名字 不能回收空间,运行时可以正常访问数据表（共享锁）。执行效率高 作用是把表中的dead tuples进行删除标记变成可以使用的状态，并没有真正的物理删除，并且会更新统计信息和相关系统表
vacuum full 表名字 可以回收空间，运行时不允许访问数据表（排他锁）。执行效率低 作用是物理删除dead tuples，并且把释放的空间重新交给操作系统。
本质时生成一个新的数据文件，然后把原来的表的live tuples 存放到新的数据文件中
autovacuum进程 只是个监控作用是用来启动自动清理进程的，autovacuum然后autovacuum worker然后vacuum+analyze，可以同时启动多个
表中不能被回收的dead tuple在索引页里是作为正常tuple而不是dead tuple记录的。考虑到这一点，索引的实际膨胀要乘以对应表的膨胀率
二，表膨胀的原因
1 autovacuum清理速度赶不上dead元组产生速度
2 由于以下因素导致dead元组无法被回收
◦ 主库或备库存在长事务（长时间的更新操作或者只读操作都会造成表膨胀)
◦ 主库或备库存在未处理的未决事务
◦ 主库或备库存在断开的复制槽
3 表膨胀和索引膨胀的对比
三，表膨胀的预防
１适当调整autovacuum参数，使得事务或者表，索引等能得到相对及时的清理
select *
from pg_settings ps
where 1=1
and lower(ps.name) like '%autovacuum%'
order by ps.name;
比如：autovacuum_vacuum_cost_limit参数
２关注长时间运行的事务
select extract(epoch FROM (clock_timestamp() - xact_start )) as longtrans, ----xact_start 这个进程的当前事务被启动的时间
extract(epoch FROM (clock_timestamp() - query_start )) as longquery ----query_start当前活动查询被开始的时间
from pg_stat_activity -----pg_stat_activity视图将为每一个服务器进程有一行，显示与该进程的当前活动相关的信息
where 1=1 and state <> 'idle';
或者
select * from pg_stat_activity where state<>'idle' and pg_backend_pid() != pid and (backend_xid is not null or backend_xmin is not null ) and extract(epoch from (now() - xact_start)) > <时间阈值，单位秒> ;
３，关注表的膨胀率
select schemaname||'.'||relname,
n_dead_tup, ----n_dead_tup的数据量是未回收的空间
n_live_tup,------n_live_tup的数量是当前表的数据量
coalesce(round(n_dead_tup * 100 / (case when n_live_tup + n_dead_tup = 0 then null else n_live_tup + n_dead_tup end ),2),0.00) as dead_tup_ratio ----dead_tup占所有有数据的行的百分比
from pg_stat_all_tables
where 1=1
and n_dead_tup >= 10000
order by dead_tup_ratio desc
limit 10;
４这些表要警惕，进行清理
SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824 ORDER BY age(relfrozenxid) DESC LIMIT 20;
四，查看清理后的效果
语句一:表及的大小
select pt.schemaname||'.'||pt.tablename as table_name, ----表名字
pg_relation_filepath(pt.schemaname||'.'||pt.tablename) as table_path, ----指定表的文件路径名
pg_table_size(pt.schemaname||'.'||pt.tablename) as table_size, -----指定表的大小（不包括索引）
pg_relation_size(pt.schemaname||'.'||pt.tablename) as relation_size, ----指定表使用的磁盘空间（包括索引） pg_relation_size不加参数，默认就是查的表的数据文件大小，单位字节
pg_total_relation_size(pt.schemaname||'.'||pt.tablename) as totalrelation_size, ---指定表所用的总磁盘空间，包括所有的索引和TOAST数据
pi.schemaname||'.'||pi.indexname as index_name, ---指定索引的名称
pg_relation_filepath(pi.schemaname||'.'||pi.indexname) as index_path, ----指定索引的文件路径名
pg_relation_size(pi.schemaname||'.'||pi.indexname) as index_size,--指定的单个索引
pg_indexes_size(pi.schemaname||'.'||pi.tablename) as index_total_size,--表上的所有索引
pi.indexdef ---索引的定义
from pg_tables pt
left outer join pg_indexes pi
on pt.schemaname||'.'||pt.tablename = pi.schemaname||'.'||pi.tablename
where 1=1
and pt.schemaname='public'
and pt.tablename='tmp_t0';
语句二:看表的统计信息
select * from pg_stat_all_tables where relname='tmp_t0';
五，一些额外的了解
psql的\watch １ 作用反复查看语句执行结果（每一秒钟一次）；
fsm结尾的文件是主文件的空闲空间映射文件－－－只跟踪可用空间的页面
vm结尾的文件是主文件的可见性映射文件－－－只跟踪包含冻结元组（死元组）的页面
