pg_attribute 테이블이 과도하게 커질경우 조치방법

PostgreSQL 모니터링 중에 DB 파일이 저장되는 파일시스템의 사용량이 약 한달동안 400 GB나 증가한 것을 확인했습니다. 먼저 $PGDATA 내에서 커진 파일들을 조사해보니 실제로 base 디렉토리의 파일들이 대부분의 용량을 차지하고 있었습니다.

 

사용자 테이블은 사용량에 큰 변동이 없었습니다. 그래서 혹시나 하는 마음에 카탈로그 테이블 크기를 살펴보니 pg_catalog.pg_attribute 테이블의 크기가 600 GB에 육박하고 있었습니다.

pg_attribute 테이블은 '컬럼에 대한 정보'를 담고 있는 카탈로그 테이블인데 사용자 테이블처럼 INSERT, DELETE가 실행되면서 dead tuple이 발생하기도 하고 autovacuum이 실행되고 있는 점도 신기했습니다.

 

어쨌든 너무 과도한 dead tuple로 부풀어 오른 테이블에 VACUUM을 실행해 놓았습니다. dead tuple이 17억건으로 워낙 많다보니 백그라운드로 계속 autovacuum 프로세스가 vacuum을 실행하기 위해 대기하는 모습이 보였습니다.

아래는 pg_attribute 테이블에 VACUUM을 실행한 예시입니다.

postgres=# SELECT n_live_tup, n_dead_tup from pg_stat_sys_tables where relname = 'pg_attribute';
 n_live_tup | n_dead_tup
------------+------------
       2346 |         28
(1 row)

postgres=# vacuum verbose pg_attribute;
INFO:  vacuuming "pg_catalog.pg_attribute"
INFO:  scanned index "pg_attribute_relid_attnam_index" to remove 28 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  scanned index "pg_attribute_relid_attnum_index" to remove 28 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_attribute": removed 28 row versions in 3 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_attribute_relid_attnam_index" now contains 2394 row versions in 13 pages
DETAIL:  28 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_attribute_relid_attnum_index" now contains 2394 row versions in 9 pages
DETAIL:  28 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_attribute": found 28 removable, 139 nonremovable row versions in 4 out of 45 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 28 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
postgres=# SELECT n_live_tup, n_dead_tup from pg_stat_sys_tables where relname = 'pg_attribute';
 n_live_tup | n_dead_tup
------------+------------
       2276 |          0
(1 row)

pg_attribute테이블이 커지는 원인을 여러 방면으로 찾아보았는데 temporary table을 만들고 삭제하는 작업이 반복되면dead tuple이 증가한다는 내용의 글을 찾을 수 있었습니다. 아래는 제가 테스트해본 내용입니다.

postgres=# create temp table test_table_temp as select * from pg_catalog.pg_stat_user_tables where 1=0;
SELECT 0
postgres=# SELECT n_live_tup, n_dead_tup from pg_stat_sys_tables where relname = 'pg_attribute';
 n_live_tup | n_dead_tup
------------+------------
       2422 |         28
(1 row)

postgres=# drop table test_table_temp;
DROP TABLE
postgres=# SELECT n_live_tup, n_dead_tup from pg_stat_sys_tables where relname = 'pg_attribute';
 n_live_tup | n_dead_tup
------------+------------
       2394 |         56

해당 PostgreSQL을 사용중인 서비스는 프로시저를 통해 수시로 temporary table 생성과 삭제를 반복적으로 실행하고 있어 호출하는 빈도가 잦다면 위와 같이 dead tuple이 과도하게 늘어날 수 있는 원인이 될 가능성이 있습니다.

추측이지만 dead tuple이 늘어나는 것에 비해 autovacuum이 clean up 하는 속도가 느려서 결과적으로 pg_attribute 테이블의 용량이 많이 늘어나지 않았을까 생각이 듭니다.

 

테이블 별로 autovacuum 속성을 달리 설정할 수 있으나 pg_attribute는 카탈로그 테이블이므로, 서비스를 재시작하거나 별도의 옵션으로 autovacuum 속성을 지정할 수 있는 것 같습니다.

 

최종적으로는 VACUUM FULL을 실행해야겠는데, 현재는 VACUUM FULL이 수분내로 완료가 되지 않고 실행되는 동안 DB에 접속이 차단되어 버려서 VACUUM으로 dead tuple이 상당부분 제거된 후 다시 FULL 옵션으로 시도해볼 생각입니다.

추후에는 autovacuum 관련 파라미터를 조정하여 백그라운드에서 더 활발히 수행되도록 하거나, 자동으로 스케줄링된 vacuum 작업을 관리해 주는 것을 고려해봐야겠습니다.

 

https://stackoverflow.com/questions/50366509/temporary-tables-bloating-pg-attribute

Temporary tables bloating pg_attribute

https://stackoverflow.com/questions/65035281/bloating-of-pg-attribute-caused-by-repetitive-temporary-table-creations

Bloating of pg_attribute caused by repetitive temporary table creations

https://techblog.woowahan.com/9478/

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