【PG】autovacuum vs vacuum

select relname,relpages, reltuples from pg_class where relname='t1';

-- vacuum 清除 【每个块】的dead tuples，且不会将块的【位置】进行重组，不会更新表page的信息
vacuum t1;

analyze t1;

explain (analyze,buffers) select count(1) from t1;

-- vacuum full清除 【每个块】的dead tuples，并且将块的【位置】进行重组,并且更新表page的信息，同时vacuum会锁表。

select oid,relfilenode from pg_class where relname='t1';
-- oid 类似于oracle ， object_id
-- relfilenode 类似于oracle , object_xxx_id

select pg_relation_filepath('t1');

vacuum full;

select pg_relation_filepath('t1');

--
create extension pg_freespacemap;

select count() as "number of pages",
pg_size_pretty(cast(avg(avail) as bigint)) as "AV. freespace size",
round(100avg(avail)/8192,2) as "AV. freespace ratio"
from pg_freespace('table_name');

-- autovacuum - ，后台核心进程之一

1. autovacuum = on
2. track_counts = on

-- autovacuum_vacuum 触发条件
当update, delete的tuples数量超过
autovacuum_vacuum_scale_factor * table_size + autovacuum_vaccum_threshold时,进行vacuum.

-- autovacuum_analyze 触发条件
当update, delete的tuples数量超过autovacuum_analyze_scale_factor * table_size + autovacuum_analyze_threshold时,进行analyze.