PostgreSQL DB에서 테이블 Vacuum 처리

회사에서 postgreSQL DB를 사용한 프로젝트 개발 중 특정 테이블에서 데이터를 가져올 때 속도저하 문제가 발생하는 경우가 있었다.
DB를 확인해 본 결과 테이블의 용량이 증가해 있음을 확인할 수 있었다.
해당 테이블은 수정/삭제가 빈번히 발생하는 테이블이었는데, 결국 원인은 postgreSQL에서 MVCC(다중 버전 동시성 제어)를 하며 생성된 데드튜플에 따른 디스크 I/O에서의 성능저하였다.
 
해당 DB에는 AutoVacuum 설정이 되어있지 않아 수동으로 vacuum 처리를 하여 용량을 확보해 성능을 개선한 경험이 있는데, 그 과정에서 운영 DB에 직접 바로 vacuum 처리를 하기에는 데이터의 손실위험이 있어 임의로 테이블을 만들어 실습을 진행한 경험이 있다. 
 
실행계획은 아래와 같이 계획했었다.

1. 도커에 동일한 환경의 DB 구축
2. 임의의 테이블 생성 후 더미 데이터를 넣어 건수확인
3. vacuum 세팅 후 데이터 조회 시 결과를 비교확인해 봄
4. 동작이 이상없이 수행되면, 실서버에서 동일한 명령어를 이용해 적용

 
1. 도커에 동일한 환경의 DB 구축
- 아래의 명령어로 dockerhub에서 postgresql 이미지를 받아온다.

docker pull postgres
** 이미지가 잘 받아졌는지 확인하려면 'docker images'

 
-  도커 컨테이너를 생성한다.

docker run -d -p 5432:5432 -e POSTGRES_PASSWORD="postgres" --name PostgreSQL01 postgres

 
- 도커 내부에 진입한다.

docker exec -it '컨테이너 명' bin/bash

 
- 도커 내부에서 postgresql을 실행시키고, 테스트용 데이터 베이스를 생성한다.

psql -U postgres
CREATE DATABASE postgresTestDB;

 
 
2. 더미 데이터를 넣고, vacuum 명령어 실행해 결과를 확인해봄
- 테이블 생성

create table public.dummy_table( 
	no integer not null primary key , 
	title varchar(300) not null , 
	content text null , 
	create_dt timestamp(0) not null default current_timestamp , 
	update_dt timestamp(0) null 
);

 
- 더미데이터 INSERT

INSERT into public.dummy_table 
(no, title, content) 
select series as no 
	, md5(trunc(random() * 40)::varchar) as title 
	, substr('가나다라마바사아자차카타파하ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 
	trunc(random() * 40)::integer + 1, trunc(random() * 10)::integer) as content 
	from generate_series(1, 10000) series;

 
- 건수 조회

SELECT count(*) FROM public.dummy_table;

 
3. vacuum 세팅 후 데이터 조회 시 결과를 비교확인해 봄
- autovacuum off 처리

ALTER TABLE public.dummy_table SET (autovacuum_enabled = off);

 
- DELETE 전 count 조회 - 시간체크

SELECT count(*) FROM public.dummy_table;
** \timing 옵션을 활성화 시킨 후 건수 조회

 
- DELETE 후 count 조회 - 시간체크 

DELETE FROM public.dummy_table WHERE no != 1;
SELECT count(*) FROM public.dummy_table;

 
- dead Tuple과 테이블 사이즈 확인

SELECT relname, n_live_tup, n_Dead_tup, n_Dead_tup / (n_live_tup::float) as ratio FROM pg_stat_user_tables;

SELECT pg_size_pretty(pg_total_relation_size('public.dummy_table'));

 
- 테이블에 베큠 적용 후 dead Tuple 삭제 확인

vacuum dummy_table;
SELECT relname, n_live_tup, n_Dead_tup, n_Dead_tup / (n_live_tup::float) as ratio FROM pg_stat_user_tables;

 
- 베큠 처리 완료 후 테이블 사이즈 변화와 데이터 조회 시 시간 단축 관측

SELECT pg_size_pretty(pg_total_relation_size('public.dummy_table'));
SELECT count(*) FROM public.dummy_table;

 
 
=> 결과적으로 베큠 실행 후 데드튜플들이 삭제되면서 테이블 사이즈는 1222kB에서 288kB로 조회시간은 1.418ms에서 0.495ms로 감소했음을 확인할 수 있다. 
 
 
** 베큠처리 시 AutoCommit 기능과 같이 자동으로 AutoVacuum을 설정할 수도 있다.
무턱대고 잦은 vacuum을 자동으로 실행하게 되면 Cpu, 메모리와 같은 자원을 과하게 사용하면서 시스템 전체에 과도한 부하를 가져올 수 있고, vacuum이 실행되는 동안  Lock이 걸려 결과적으로 시스템이 다운되는 상황이 발생할 수 있다.
따라서 AutoVacuum을 실행하려 한다면 운영환경을 명확히 파악하고 실행계획을 상세히 세운 상태에서 실행해야 한다.
 
 
- 참고한 글
https://techblog.woowahan.com/9478/

PostgreSQL Vacuum에 대한 거의 모든 것 | 우아한형제들 기술블로그

 
https://mangkyu.tistory.com/53

[Database] MVCC(다중 버전 동시성 제어)란?