[PostgreSQL] AutoVacuum 동작 테스트

개요

• postgresql.conf 에서 AutoVacuum 관련 설정을 하였다고 가정을 하고, 실제 AutoVacuum 이 정상적으로 동작하는지 테스트 진행합니다.

데이터 삽입 및 수정

• 테스트 진행을 위해, test 테이블 하나를 생성하고 데이터 Insert 및 Update 를 진행하였습니다.

create table test (a int, b int);
alter table test set (autovacuum_enabled = false); -- autovacuum 비활성화
insert into test select generate_series, generate_series from generate_series(1, 100000);
\dt+ test -- table 크기 : 3568 kB(10만건) / 총 10만개(10만:live tuple)의 dead tuple 생성
update test set b = b + 1 ;
\dt+ test -- table 크기 : 7104 kB(20만건) / 총 20만개(10만:dead tuple + 10만:live tuple)의 dead tuple 생성
update test set b = b + 1 ;
\dt+ test -- table 크기 : 10 MB(30만건) / 총 30만개((10만 + 10만):dead tuple + 10만:live tuple)의 dead tuple 생성

Dead Tuple, Live Tuple 확인

• Dead Tuple, Live Tuple 확인을 다음 SQL 구문을 통해 확인할 수 있습니다.
• 쿼리 조회 결과, 현재 test 테이블에 Dead Tuple 이 698159 인것을 확인할 수 있습니다.

adc=# SELECT relname, n_live_tup, n_dead_tup, n_dead_tup / (n_live_tup::float) as ratio
FROM pg_stat_user_tables
WHERE n_live_tup > 0 AND n_dead_tup > 1000
ORDER BY ratio DESC;

 relname | n_live_tup | n_dead_tup |  ratio
---------+------------+------------+---------
 test    |     100000 |     698159 | 6.98159
(1 row)

AutoVacuum 동작 쿼리 확인

• 이제 어느정도 시간이 지나, AutoVacumm 동작이 이루어 졌는지 확인합니다.
• AutoVacuum 동작 확인하는 쿼리는 다음과 같습니다.

adc=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
order by relname;

  relname  | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze
-----------+-------------+-------------------------------+--------------+------------------------------
 test      |             | 2022-07-07 16:07:23.746402+09 |              | 2022-07-07 16:07:23.82043+09
(1 rows)

• 위와 같이 2022-07-07 16:07:23.746402+09 시간에 AutoVacuum 이 실행된 것을 확인할 수 있습니다.
• 이제 다시 test 테이블의 Dead Tuple 을 확인하여 정상적으로 AutoVacuum 이 이루어 졌는지 확인합니다.

Dead Tuple, Live Tuple 확인

• 앞서 test 테이블의 Dead Tuple 이 698159 였습니다.
• 하지만, AutoVacuum 이 동작한 후에는 Dead Tupe 이 0 으로 된 것을 확인할 수 있습니다.

adc=# SELECT relname, n_live_tup, n_dead_tup, n_dead_tup / (n_live_tup::float) as ratio
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY ratio DESC;
 relname | n_live_tup | n_dead_tup | ratio
---------+------------+------------+-------
 test    |     100000 |          0 |     0
(1 row)