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

데이터베이스 블로트(Database bloat)는 PostgreSQL에서 흔하지만 종종 눈에 잘 띄지 않는 성능 저하의 주범입니다. PostgreSQL은 다중 버전 동시성 제어(MVCC) 데이터베이스로서, 트랜잭션이 참조한 이전 버전의 행을 트랜잭션이 완료될 때까지 사용할 수 있도록 유지하여 동시성을 확보합니다. 행이 업데이트되거나 삭제될 때, 이전 버전(죽은 튜플, dead tuples)은 재사용을 위해 표시되지만 디스크에 물리적으로 남아 있게 되어 저장 공간 사용량이 증가하고, 인덱스 스캔 속도가 느려지며, 쿼리 성능이 저하됩니다. 이 종합 가이드에서는 이러한 블로트를 감지하는 방법과 PostgreSQL의 핵심 유지 관리 도구인 VACUUM을 사용하는 실용적이고 실행 가능한 전략을 탐구합니다.

블로트를 이해하고 관리하는 것은 높은 처리량의 PostgreSQL 인스턴스의 건강과 효율성을 유지하는 데 매우 중요합니다. 블로트를 무시하면 시간이 지남에 따라 불필요한 저장 공간 소비와 쿼리 지연 시간 증가를 초래할 수 있으며, 이에 따라 선제적인 모니터링과 정기적인 유지 관리가 필요합니다.

PostgreSQL MVCC 및 블로트 이해하기

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

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

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

블로트는 몇 가지 주요 영역에서 성능에 영향을 미칩니다.

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

데이터베이스 블로트 감지

감지는 시스템 통계 뷰를 쿼리하여 유용한 데이터의 양 대비 테이블의 물리적 크기를 추정하는 데 의존합니다.

1. `pg_stat_user_tables`를 사용하여 블로트된 테이블 식별

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

모니터링할 주요 지표:

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

단순 개수도 유용하지만, 더 정확한 계산에는 크기 차이 추정이 포함됩니다. 보편적인 내장 공식은 없지만, 커뮤니티 주도 스크립트는 블로트를 상당히 효과적으로 추정합니다.

예제 쿼리 (블로트 비율 추정):

이 예제는 죽은 튜플과 전체 튜플의 비율을 추정하여 적극적인 vacuuming 대상 테이블을 강조합니다.

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    pg_size_pretty(pg_relation_size(oid)) AS total_size,
    pg_size_pretty(pg_relation_size(oid) - (n_live_tup * (23 + avg_row_size))::bigint) AS estimated_bloat_size
FROM
    pg_stat_user_tables
WHERE
    n_dead_tup > 1000 -- 무시할 수 있는 노이즈 필터링
ORDER BY
    n_dead_tup DESC
LIMIT 10;

2. 블로트된 인덱스 평가

블로트는 종종 인덱스에 상당한 영향을 미칩니다. PostgreSQL은 pg_stat_user_indexes 뷰를 제공하지만, 인덱스 블로트는 인덱스 크기를 보유하고 있는 항목 수와 비교하여 분석함으로써 더 잘 정량화됩니다. 블로트된 인덱스에는 죽은 튜플에 대한 많은 포인터가 포함될 수 있어 탐색 시간이 증가합니다.

블로트 관리: VACUUM의 역할

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

Autovacuum: 첫 번째 방어선

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

구성 팁: 변경이 잦은 테이블의 경우 scale_factor를 낮추어 유지 관리를 더 일찍 트리거하도록 고려하여 대규모 블로트 축적을 방지하십시오.

-- 예: 중요한 테이블 'orders'에 대한 적극적인 autovacuum 매개변수 설정
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. 인덱스 재구축 (인덱스 `VACUUM FULL`의 대안)

개별 인덱스는 전체 테이블을 너무 오래 잠그지 않고 재구축할 수 있습니다. 단, 최종 전환 중에는 짧은 시간 동안 잠금이 필요합니다.

REINDEX INDEX index_name;
-- 또는 전체 테이블 재작성 없이 테이블의 모든 인덱스를 재구축하려면:
REINDEX TABLE table_name;

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

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

pg_repack 작동 방식:

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

설치 및 사용 (개념적 예제):

먼저 확장 기능을 설치합니다 (종종 OS 패키지 관리자를 통해).

-- PostgreSQL 데이터베이스에 연결
CREATE EXTENSION pg_repack;

-- 블로트된 테이블을 온라인으로 재구축
SELECT pg_repack.repack('public', 'critical_table', 'ddl_concurrency=none');

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

요약 및 다음 단계

데이터베이스 블로트는 PostgreSQL에서 제어 가능한 문제입니다. 최적화된 Autovacuum 설정을 통한 예방이 항상 사후 처치보다 선호됩니다. 블로트가 발생하면 다음 계층 구조를 따르십시오.

모니터링: pg_stat_user_tables에서 n_dead_tup 값이 높은지 정기적으로 확인합니다.
Autovacuum 튜닝: 활성 테이블의 경우, 표준 VACUUM이 더 자주 실행되도록 scale factor를 낮춥니다.
복구: 블로트가 경미한 경우, 테이블 활동이 줄어들면 표준 VACUUM table_name으로 충분할 수 있습니다.
적극적인 복구 (짧은 다운타임): 테이블 구조를 온라인으로 재작성하려면 pg_repack을 사용합니다.
긴급 복구 (긴 다운타임): 다운타임이 허용되는 경우에만 마지막 수단으로 VACUUM FULL을 사용하십시오. 이 명령은 독점 잠금을 유지합니다.

이러한 감지 및 복구 단계를 일상적인 유지 관리 계획에 통합함으로써 PostgreSQL 환경이 슬림하고 빠르며 효율적으로 유지되도록 보장합니다.