PostgreSQL에서 Vacuum은 MVCC를 구현하기 위해 PostgreSQL에서 사용하는 동작을 이야기 합니다.
Vacuum 명령어나 AutoVacuum은 다음과 같은 작업을 수행합니다.
- Maintenance 를 위해 임계치 이상의 Dead tuple을 정리하고 FSM으로 반환
- Transaction ID Wraparound 방지
- Statistics Plan update
- Visibility map update를 통한 index scan 성능 향상
오늘은 Dead tuple 정리와 관련 된 Vacuum 작업을 살펴보도록 하겠습니다.
Vacuum Test
Vaccum을 확인하기 위해 test 테이블을 만들고, update하면서 Vacuum이 어떻게 영향을 미치는지 확인해 보도록 하겠습니다.
1. PostgreSQL에 test 테이블을 AutoVacuum 이 활성화 되지 않은 상태로 만들어 줍니다.
2. 그리고 해당 테이블에 데이터를 insert 합니다.
3. 데이터 insert 후, 테이블의 사이즈를 확인합니다. (약 35MB 사용)
3. 다시 해당 테이블의 데이터 전체를 업데이트 합니다.
4. 업데이트 한 후에는 변경을 위해 진행했던 Transaction 정보가 남으며, 테이블 사이즈가 커지게 됩니다. (총 약 69MB 사용)
5. Vacuum을 진행합니다.
6. Vacuum 후에도 테이블 사이즈 자체는 변경이 되지 않습니다. (시스템에 반환되지 않습니다.)
7. 테이블에 데이터를 업데이트 합니다.
8. 반환된 테이블의 공간을 재사용할 수 있기 때문에 테이블 사이즈 자체는 변경되지 않습니다. (FSM을 활용합니다.)
Vacuum을 한 후에 FSM을 활용하게 됨으로서 새로운 데이터를 위해 테이블에 디스크 할당 하는 대신 이미 테이블에 할당된 공간을 재사용하게 되는 것입니다.
Vacuum 확인
Dead tuple을 삭제하는 Vacuum 을 실행할 경우, 또는 AutoVacuum이 일어났을 때,
Dead tuple이 정리되면 더 많은 페이지를 읽지 않아도 되기 때문에 성능 향상이 일어납니다.
테이블의 Dead tuple은 다음 쿼리로 확인할 수 있습니다.
WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM
pg_settings WHERE name = 'autovacuum_vacuum_threshold'),
vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM
pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'),
fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'),
sto AS (select opt_oid, split_part(setting, '=', 1) as param,
split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt)
SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
age(relfrozenxid) as xid_age,
coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age,
(coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples)
AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM
pg_class c join pg_namespace ns on ns.oid = c.relnamespace
join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1)
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid
left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid
left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid
WHERE c.relkind = 'r' and nspname <> 'pg_catalog'
AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) +
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) *
c.reltuples <= n_dead_tup)
ORDER BY age(relfrozenxid) DESC LIMIT 50;
결과
Vacuum/ Vacuum Full
Vacuum Full이 물리디스크까지 할당 된 테이블 증가 사이즈까지 회수를 할 수 있다면,
일반적으로 Vacuum을 했을 때는 회수가 되지 않습니다.
ref: https://postgresql.kr/docs/13/routine-vacuuming.html
"일반적인 vacuum 전략은 주기적인 표준 VACUUM 작업을 해서, 꾸준히 빈 공간을 확보해서 디스크가 어느 정도 커지지만, 더 이상 크지지 않게 해서, 최대한 VACUUM FULL 작업을 해야하는 상황을 방지하는 것이다. autovacuum 데몬이 이런 전략으로 작업을 한다. 즉, autovacuum 기능을 사용한다면, VACUUM FULL 작업을 하지 않는 것을 기본 정책으로 하면 된다.
비정기적인 VACUUM FULL 작업보다, 정기적인 표준 VACUUM 작업이 운영상 더 낫다."