PostgreSQL 테이블 부풀림 현상 대처 방안

PostgreSQL 테이블 부풀림 현상 대처 방안


테이블이 자꾸 커져요

PostgreSQL 질문 답변 게시판에서 꾸준히 올라오는 질문 가운데 하나다.
테이블에 보관된 자료의 실제 크기는 그 테이블을 pg_dump로 덤프해 보면 1MB 밖에 안되는데, psql 에서 \dt+ 명령으로 테이블 크기를 살펴보면, 100MB나 된다.  이 일을 어쩔꺼나. 이런 식으로 하소연(?)을 한다.

결론. PostgreSQL에서 해당 테이블 대상으로 UPDATE, DELETE 명령을 이용해서 자료 조작이 일어난다면, 그 테이블 크기는 실 자료 크기보다 커질 수 밖에 없다. 물론 사용자의 자료 조작 뿐만 아니라, 트리거나, 함수를 통해서 일어나는 자료 변경, 삭제도 마찬가지다.

이 테이블 부풀림 현상은 다양한 모습으로 이상 동작을 한다.  가장 대표적인 것이 테이블 전체 탐색을 하면서 과도한 자료 블럭 읽기로 SELECT 쿼리 실행 속도가 현저하게 떨어지는 현상이다.  좀 더 깊게 살펴보면, 데이터베이스의 나이가 줄어들지 않아, 트랜잭션 ID 겹침 방지 autovacuum worker가 계속 freeze 작업만 하고 있는 현상도 나타난다. 이 현상을 방치하고 있으면 결국 트랜잭션ID 재활용을 하지 못해 데이터베이스가 중지 되는 사태까지 발생하기도 한다.
 

테이블이 커질 수 밖에 없는 이유

PostgreSQL 다중 버전 동시성 제어(MVCC) 기법 때문이다. UPDATE나 DELETE 작업이 일어날 경우 기존 자료(old version)와 새 자료(new version, UPDATE 작업으로 생긴 자료)를 해당 테이블에 모두 두기 때문이다. 

극단적인 예로 해당 테이블을 autovacuum 데몬이 관리하지 않도록 설정하고, insert & rollback 을 무한 반복한다면, 해당 테이블은 디스크 공간이 허용하는 한, 트랜잭션 겹침 방지를 위한 vacuum 작업이 생기지 않는한 영원히 그 테이블은 커지게 된다. 설령 그 테이블 안에 모든 자료가 rollback 되어 한 건도 없다 하더라도.

앞에서 다룬 이야기는 정말 극단적인 상황이고, 단순히 일반적인 상황을 고려해도 마찬가지다.  autovacuum 데몬이 해당 테이블을 관리한고 있다고 하더라도(기본값), 해당 테이블 전체 UPDATE 작업을 하나의 명령으로 실행한 경우라면, 정확히 두 배의 공간이 필요하게 된다.

postgres@postgres=# create table test (a int, b int);
CREATE TABLE
postgres@postgres=# alter table test set (autovacuum_enabled = false); -- autovacuum 비활성화
ALTER TABLE
postgres@postgres=# insert into test 
select generate_series, generate_series from generate_series(1, 100000);
INSERT 0 100000
postgres@postgres=# \dt+ test
              릴레이션(relation) 목록
 스키마 | 이름 |  종류  |  소유주  |  크기   | 설명 
--------+------+--------+----------+---------+------
 public | test | 테이블 | postgres | 3568 kB | 
(1개 행)

postgres@postgres=# update test set b = b + 1 ;
UPDATE 100000
postgres@postgres=# \dt+ test
              릴레이션(relation) 목록
 스키마 | 이름 |  종류  |  소유주  |  크기   | 설명 
--------+------+--------+----------+---------+------
 public | test | 테이블 | postgres | 7104 kB | 
(1개 행)

postgres@postgres=# update test set b = b + 1 ;
UPDATE 100000
postgres@postgres=# \dt+ test
             릴레이션(relation) 목록
 스키마 | 이름 |  종류  |  소유주  | 크기  | 설명 
--------+------+--------+----------+-------+------
 public | test | 테이블 | postgres | 10 MB | 
(1개 행)

이렇게 update 작업을 할 때마다 사용 공간은 전체 자료 공간 만큼 늘어나게 된다. 이 문제는 delete 인 경우도 마찬가지다. 

vacuum 명령어

PostgreSQL에서는 이 문제를 vacuum 명령으로 처리한다.  vacuum 명령은 크게 다음과 같은 일을 한다.
  • dead tuple 들을 free space로 바꾸는 작업
    dead tuple이란 delete 작업으로 commit 된 자료나 update 작업으로 commit 된 old version 자료, 즉 어떤 세션에서 보이지 않는 이미 지워진 자료를 말한다. 이 dead tuple은 vacuum 작업으로 free space를 바뀌기 전까지는 그 자리에 새 자료가 저장될 수 없다. 
    윗 예제로 설명하면, 첫 update 작업에서 10만개의 dead tuple이 생기고, 그것이 vacuum 작업으로 free space로 바뀌지 않았기 때문에, 두 번 째 update 작업으로 총 20만개의 dead tuple이 생기고, 10만개의 live tuple이 한 테이블 안에 있게 된다. 이 때 해당 테이블을 vacuum 명령으로 청소를 하면, 그 20만개의 dead tuple을 free space로 만든다. 즉, 테이블의 크기는 총 10MB지만, 실자료는 3.5MB가 있고 나머지는 빈 공간이 되며, 다음 update 작업에서는 new version row가 이 빈 공간에 저장된다. 이렇게 해서 총 10MB 테이블 크기는 바뀌지 않게 된다.
  • 테이블의 Visibility Map, Free Space Map 파일의 내용을 갱신한다. 이 파일은 해당 데이터베이스 자료 파일이 있는 디렉터리안에 있는 NNNN_vm, NNNN_fsm 파일이다. NNNN은 숫자. vm 파일은 해당 블럭의 해당 자료가 실자료인지, 그 자료가 영구보관처리가 되었는지에 대한 정보를 담고 있으며, fsm 파일은 해당 블럭에 어느 영역이 빈공간으로 처리되었는지에 대한 정보를 담고 있다.
  • 테이블의 자료 통계 정보도 갱신한다. 이것은 pg_class에 담기는 각종 테이블 통계 정보와, 실시간 통계 정보 파일(pg_stat_tmp 디렉터리에 담기는 각 데이터베이스별 통계정보)의 내용을 갱신한다. (각 칼럼별 자료 분포 통계를 담고 있는 pg_statistic 테이블의 내용은 analyze 명령으로 갱신된다.)
  • 마지막으로 PostgreSQL 특유의 트랜잭션 겹침 방지 작업도 vacuum 명령에서 처리한다. 이 트랜잭션 겹침 방지 작업에 대한 이야기는 이 글의 내용과 별개여서 이 글에서는 깊게 다루지 않는다. 중요한 이야기이긴하나 내용이 너무 길어져서 따로 다룰 예정이다.
앞에 예제를 기반으로 vacuum 작업과 그 영향을 살펴본다.

postgres@postgres=# vacuum test;
VACUUM
postgres@postgres=# update test set b = b + 1;
UPDATE 100000
postgres@postgres=# \dt+ test
             릴레이션(relation) 목록
 스키마 | 이름 |  종류  |  소유주  | 크기  | 설명 
--------+------+--------+----------+-------+------
 public | test | 테이블 | postgres | 10 MB | 
(1개 행)
이처럼 vacuum 작업 뒤 update 작업은 빈공간을 사용하기 때문에 더 이상 테이블이 부풀려지지 않는다. 윗 경우라면, 이론상 한 번 더 update 작업이 있어도 10개의 new version 자료를 저장할 수 있는 빈공간이 아직 있기 때문에 여전히 테이블 크기는 유지될 것이다. 확인은 직접 해보시길.

autovacuum에게 맡기기

autovacuum의 한계

테이블 부풀림 조사

대처 방안

마무리