Postgresql에서 Vacuum은 중요하고 AutoVacuum은 2가지 type으로 구분 할 수 있습니다.

테이블이  Age가 Vacuum_freeze_min_age(0.5억), vacuum_freeze_table_age (1.5억) 이상 되서 실행되는 경우와,  삭제된 행수가 임계값 이상 넘었을때

(vacuum threshold >= vacuum base threshold + vacuum scale factor * number of tuples) 실행 되는 경우로  나눌  수 있습니다.

그러나, 이 2개는가 처리되는 프로세스가 다릅니다. 크게는 공간을 재활용하는것과 못하는 것으로 구분 할 수 있습니다.

공식 메뉴얼에는 이 2개의 AutoVacuum을 구분하지 않아 제가 임의로 t_infomask 9bit를 1로만 하는 경우를 Semi Vacuum이라 명칭했습니다.

/*
Semi-Vacuum
특징 : 공간 재활용 못함, t_infomask의 9 bit를 1로 변경
발생 시점 :
Table Age >= Vacuum_freeze_min_age(0.5억)

만약 Vacuum_freeze_min_age(0.5억) 이내에 Manual Vacuum을 실행하면
vacuum_freeze_table_age (1.5억) 에서 실행된다.

*/

drop table t_test;

CREATE TABLE t_test (
i1 int,
v1 char(1000)
);

insert into t_test
SELECT i,'abcd'
FROM generate_series(1, 250) a(i);

SELECT pg_relation_size('t_test');
--294912

--AutoVacuum되지 않게 20% 이내로 삭제
DELETE from t_test
where i1  <= 45;

SELECT pg_relation_size('t_test');
--294912

SELECT COUNT(*) FROM T_TEST

select dblink_connect('myconn', 'dbname=postgres port=5432 user=postgres password=0152');

--Vacuum_freeze_min_age(0.5억) 이상 트랜잭션 발생
--테스트로 50000 설정
select loop_insert_txid_bump_t(1, 50000);

--Table Age
select relname as name, age(relfrozenxid)
from pg_class
where relname = 't_test';

--1분 기다림.

--Vacuum모니터링
--이 경우는 pg_stat_all_tables의 last_autovacuum 에 AutoVacuum이 반영되지 않고,
-- t_infomask의 9번째 bit가 1로 변경 됐는지 확인해야 한다.
SELECT last_autovacuum FROM pg_stat_all_tables
WHERE RELNAME = 't_test'

--AutoVacuum발생함.
--트랜잭션이 > Vacuum_freeze_min_age 되어

--page 수량, relpage : 885
select relpages from  pg_class
where relname ='t_test'

--Scan해야 할 Page : 조회된 (relpage -1)
--배열 0부터 시작 하기 때문에 relpages에서 하나를 빼준다.

--9번째 bit 1로 변경 됐는지 확인
select
    substr(cast(b.t_infomask::bit(16) as text), 7, 1) Freeze_flag,
    substr(cast(b.t_infomask::bit(16) as text), 8, 1) Vacuum_flag,
    count(*)
from (select i from generate_series(0,35) a(i)) a,
LATERAL(select * from heap_page_items(get_raw_page('t_test', a.i))) b
group by
    substr(CAST(b.t_infomask::bit(16) as text),7,1) --Freeze, 10번째 Bit
,    substr(CAST(b.t_infomask::bit(16) as text),8,1); --Vacuum, 9번째 Bit

--삭제한 만큼 입력
insert into t_test
SELECT i,'abcd'
FROM generate_series(1, 45) a(i);

--공간 재활용 못함.
SELECT pg_relation_size('t_test');
--352256

/*
AutoVacuum
특징 : 공간 재활용, t_infomask의 9 bit를 1로 변경
발생 시점 :
vacuum threshold >= vacuum base threshold + vacuum scale factor * number of tuples
*/

drop table t_test;

CREATE TABLE t_test (
i1 int,
v1 char(1000)
);

insert into t_test
SELECT i,'abcd'
FROM generate_series(1, 250) a(i);

SELECT pg_relation_size('t_test');
--294912

--AutoVacuum되게 20% 삭제
DELETE from t_test
where i1  <= 100;

select count(*) from t_test;

--1분 기다림.

--Vacuum모니터링
SELECT * FROM pg_stat_all_tables
WHERE RELNAME = 't_test'

--page 수량, relpage : 885
select relpages from  pg_class
where relname ='t_test'

--Scan해야 할 Page : 조회된 (relpage -1)
--배열 0부터 시작 하기 때문에 relpages에서 하나를 빼준다.
select
    substr(cast(b.t_infomask::bit(16) as text), 7, 1) Freeze_flag,
    substr(cast(b.t_infomask::bit(16) as text), 8, 1) Vacuum_flag,
    count(*)
from (select i from generate_series(0,35) a(i)) a,
LATERAL(select * from heap_page_items(get_raw_page('t_test', a.i))) b
group by
    substr(CAST(b.t_infomask::bit(16) as text),7,1) --Freeze, 10번째 Bit
,    substr(CAST(b.t_infomask::bit(16) as text),8,1); --Vacuum, 9번째 Bit

insert into t_test
SELECT i,'abcd'
FROM generate_series(1, 100) a(i);

--공간 재활용 가능.
SELECT pg_relation_size('t_test');
--294912