PostgreSQL에서 VACUUM을 사용한 데이터베이스 블로트 감지 및 제거

PostgreSQL 데이터베이스 블로트는 오래된 행 버전과 과도하게 큰 인덱스가 실제 필요한 데이터보다 더 많은 공간을 차지할 때 발생합니다. 백업이 커지거나, 스캔 속도가 느려지거나, 스토리지 비용이 증가하거나, 오토배큠이 계속 실행되는데도 테이블 디스크 크기가 줄어들지 않는 현상을 경험할 수 있습니다.

해결 방법은 회수해야 할 공간의 유형에 따라 다릅니다. 표준 VACUUM은 PostgreSQL 내부에서 죽은 튜플 공간을 재사용 가능하게 만듭니다. VACUUM FULL, REINDEX, 그리고 pg_repack과 같은 도구는 물리적으로 객체를 축소할 수 있지만, 각각 다른 잠금 및 운영 비용이 발생합니다.

PostgreSQL MVCC와 블로트 이해하기

블로트를 효과적으로 해결하려면 먼저 그 근본 원인을 이해해야 합니다. PostgreSQL의 MVCC 아키텍처는 읽기 작업이 쓰기 작업을 차단하지 않고, 쓰기 작업이 읽기 작업을 차단하지 않도록 보장합니다. 행이 업데이트되면 PostgreSQL은 기존 행을 덮어쓰지 않고 새 버전을 삽입하고 이전 버전을 죽은 상태로 표시합니다. 마찬가지로 삭제된 행은 죽은 튜플을 남깁니다.

블로트는 이러한 죽은 튜플이 유지 관리 프로세스(오토배큠 또는 수동 VACUUM)가 정리하거나 공간을 재사용할 수 있는 속도보다 빠르게 축적될 때 발생합니다.

데이터베이스 블로트의 결과

블로트는 여러 주요 영역에서 성능에 영향을 미칩니다.

디스크 공간 사용량 증가: 죽은 튜플이 물리적 공간을 차지하여 테이블과 인덱스가 필요한 것보다 더 많은 스토리지를 소비하게 됩니다.
순차 스캔 속도 저하: 데이터베이스 엔진은 테이블 스캔 중에 죽은 튜플을 읽어야 하므로 I/O 부하가 증가합니다.
비효율적인 인덱싱: 블로트된 인덱스는 더 커져서 인덱스 구조를 탐색하는 데 더 많은 디스크 읽기가 필요합니다.
오토배큠 노력 낭비: 오토배큠이 테이블을 정리하는 데 더 열심히 그리고 오래 작업해야 하므로 다른 테이블의 중요한 유지 관리가 지연될 수 있습니다.

데이터베이스 블로트 감지

감지는 카탈로그 통계와 객체 크기로 시작합니다. 테이블 레이아웃, fillfactor, TOAST 데이터 및 인덱스가 모두 실제 숫자에 영향을 미치기 때문에 간단한 쿼리를 정확한 블로트 측정이 아닌 분류(triage)로 취급하세요.

1. `pg_stat_user_tables`를 사용한 블로트 테이블 식별

pg_stat_user_tables 뷰는 사용자 정의 테이블에 대한 통계를 제공합니다. 테이블에 할당된 전체 크기와 라이브 데이터의 크기를 비교하여 대략적인 블로트를 계산할 수 있습니다.

모니터링할 주요 지표:

n_dead_tup: 죽은 튜플 수.
last_autovacuum, last_vacuum: 마지막 유지 관리 실행 시간.

단순한 개수는 배큠 압력이 어디에서 쌓이고 있는지 보여주기 때문에 유용합니다. n_live_tup과 비교하여 n_dead_tup이 많다면 해당 테이블을 더 자세히 검사해야 할 좋은 이유입니다.

예제 쿼리 (배큠 후보 찾기):

이 쿼리는 죽은 튜플이 많은 테이블을 강조하고 마지막 배큠 실행 시간을 보여줍니다.

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY
    n_dead_tup DESC
LIMIT 10;

2. 블로트된 인덱스 평가

블로트는 종종 인덱스에 상당한 영향을 미칩니다. 변경이 잦은 테이블에서 비정상적으로 큰 인덱스를 찾는 것부터 시작하세요.

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

크기만으로 블로트가 증명되지는 않지만, pgstattuple 확장이나 모니터링 플랫폼의 블로트 쿼리와 같은 심층 도구로 확인할 가치가 있는 인덱스를 알려줍니다.

블로트 관리: VACUUM의 역할

VACUUM은 죽은 튜플의 공간을 회수하고 가시성 맵을 업데이트하는 PostgreSQL의 주요 도구입니다.

오토배큠: 첫 번째 방어선

기본적으로 PostgreSQL은 autovacuum 프로세스를 자동으로 실행합니다. 오토배큠은 임계값이 충족되면 표준 VACUUM(내부적으로 공간을 재사용 가능으로 표시하지만 OS로 반환하지는 않음)을 수행합니다. 이 임계값은 autovacuum_vacuum_scale_factor(기본값 0.2 또는 테이블 크기의 20%)와 autovacuum_vacuum_threshold(기본값 50 튜플)로 정의됩니다.

구성 팁: 변경이 잦은 테이블의 경우 scale_factor를 낮추어 유지 관리를 더 일찍 트리거하여 큰 블로트 축적을 방지하는 것을 고려하세요.

-- 예: 중요한 테이블 'orders'에 대해 공격적인 오토배큠 매개변수 설정
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 100);

표준 VACUUM vs. VACUUM FULL

두 가지 주요 정리 모드가 있습니다.

표준 `VACUUM`

표준 VACUUM은 죽은 튜플을 기존 물리적 파일 내에서 재사용할 수 있도록 표시합니다. 디스크의 테이블 파일 크기를 줄이지 않습니다. 이는 비차단 방식이며 트래픽이 많은 테이블에 안전합니다.

VACUUM table_name;
VACUUM (VERBOSE) table_name; -- 정리된 튜플에 대한 통계 표시

`VACUUM FULL` (공간 회수 도구)

VACUUM FULL은 테이블 파일 전체를 다시 작성하여 죽은 튜플을 물리적으로 제거하고 공간을 운영 체제로 회수합니다.

경고: VACUUM FULL은 실행 기간 동안 테이블에 대해 ACCESS EXCLUSIVE 잠금이 필요합니다. 즉, VACUUM FULL이 완료될 때까지 해당 테이블에 대한 모든 읽기 및 쓰기 작업이 차단됩니다. 크고 사용량이 많은 테이블에서는 이 명령을 신중하게 사용하세요.

VACUUM FULL table_name;

모범 사례: 블로트가 심각하고 다운타임을 감당할 수 있거나 예정된 유지 관리 기간 동안에만 VACUUM FULL을 사용하세요.

고급 안티 블로트 전략

VACUUM FULL이 너무 파괴적일 때, 다운타임을 줄이면서 공간을 회수할 수 있는 대체 방법이 있습니다.

1. 인덱스 재구축

인덱스 블로트가 주요 문제인 경우 개별 인덱스를 재구축할 수 있습니다. 바쁜 시스템의 경우 대부분의 작업 동안 읽기와 쓰기가 계속될 수 있도록 동시 형태를 선호하세요.

REINDEX INDEX CONCURRENTLY index_name;

일반 REINDEX INDEX index_name;은 더 빠르지만 더 강력한 잠금이 필요하므로 유지 관리 기간 동안 사용하세요.

2. 온라인 테이블 재작성을 위한 `pg_repack` 사용

pg_repack 유틸리티는 최소한의 다운타임으로 테이블 블로트를 제거하는 선호되는 방법입니다. 이는 기존 테이블 옆에 테이블 구조와 데이터의 새롭고 깨끗한 복사본을 만들고, 변경 사항을 동기식으로 적용한 다음, 테이블을 원자적으로 교체하는 방식으로 작동합니다.

pg_repack 작동 방식:

원본을 미러링하는 임시 테이블(_new)을 생성합니다.
트리거를 사용하여 원본 테이블의 변경 사항을 지속적으로 모니터링합니다.
최종 동기화된 복사 및 교체를 수행합니다.

설치 및 사용법 (일반적인 흐름):

PostgreSQL 버전에 맞는 확장 및 CLI 패키지를 설치하고, 데이터베이스에서 확장을 활성화한 다음, 셸에서 pg_repack 명령을 실행하세요.

CREATE EXTENSION pg_repack;

pg_repack --table=public.critical_table --dbname=mydb

pg_repack 참고 사항: VACUUM FULL에 비해 잠금을 크게 줄이지만, 트리거 생성 및 데이터 복사가 필요하므로 일시적으로 추가 I/O와 스토리지를 소비합니다.

결론

데이터베이스 블로트는 장애로 이어지기 전에 모니터링하면 관리할 수 있습니다. 튜닝된 오토배큠을 통한 예방이 긴급 재작성보다 낫습니다. 블로트가 발생하면 다음 계층 구조를 따르세요.

모니터링: pg_stat_user_tables에서 높은 n_dead_tup 수를 정기적으로 확인하세요.
오토배큠 튜닝: 활성 테이블의 경우 scale factor를 낮추어 표준 VACUUM이 더 자주 실행되도록 하세요.
복구: 블로트가 경미하고 테이블 활동이 줄어든다면 표준 VACUUM table_name으로 충분할 수 있습니다.
적극적 복구 (낮은 다운타임): pg_repack을 사용하여 온라인으로 테이블 구조를 재작성하세요.
긴급 복구 (높은 다운타임): 다운타임이 허용되는 경우에만 최후의 수단으로 VACUUM FULL을 사용하세요. 이는 독점 잠금을 보유하기 때문입니다.

재작성 전에 장기 실행 트랜잭션을 확인하고 작업에 충분한 여유 디스크 공간이 있는지 확인하세요.