블로트 방지: 성능을 위한 고급 PostgreSQL 진공 전략

PostgreSQL은 강력하고 다재다능한 오픈 소스 관계형 데이터베이스로, 데이터 무결성과 성능을 유지하기 위해 여러 내부 메커니즘에 의존합니다. 그중에서도 VACUUM 작업은 저장 공간을 회수하고 죽은 튜플로 인한 성능 저하를 방지하는 데 중요한 역할을 합니다. VACUUM은 종종 기본적인 용어로 논의되지만, 고급 진공 전략을 이해하고 구현하면 PostgreSQL 데이터베이스의 상태와 속도에 큰 영향을 미칠 수 있습니다.

바쁜 데이터베이스에서 흔히 발생하는 테이블 블로트는 삭제되거나 업데이트된 행이 즉시 제거되지 않는 죽은 튜플을 남길 때 발생합니다. 이러한 죽은 튜플은 디스크 공간을 소비하고 데이터베이스가 더 많은 데이터를 스캔해야 하므로 쿼리 실행 속도를 저하시킬 수 있습니다. PostgreSQL의 자동화된 백그라운드 프로세스인 Autovacuum은 이를 관리하는 것을 목표로 하지만, 기본 설정이 모든 워크로드에 항상 최적인 것은 아닙니다. 유용한 작업은 어떤 테이블이 더 적극적인 정리가 필요한지, 어떤 테이블은 그대로 둘 수 있는지, 그리고 수동 유지 관리 창이 언제 중단할 가치가 있는지 아는 것입니다.

테이블 블로트와 그 영향 이해하기

PostgreSQL은 MVCC(Multi-Version Concurrency Control) 시스템을 사용합니다. 행이 업데이트되면 행의 새 버전이 생성되고 이전 버전은 죽은 것으로 표시됩니다. 마찬가지로 행이 삭제되면 죽은 것으로 표시되지만 즉시 제거되지는 않습니다. 이러한 죽은 튜플은 VACUUM 작업이 정리할 때까지 테이블에 남아 있습니다. VACUUM이 충분히 자주 실행되지 않거나 충분히 적극적이지 않으면 죽은 튜플이 축적되어 테이블 블로트로 이어집니다.

테이블 블로트의 결과는 중요합니다:

디스크 사용량 증가: 블로트된 테이블은 필요한 것보다 더 많은 디스크 공간을 소비하여 저장 문제와 백업 시간 증가로 이어질 수 있습니다.
쿼리 성능 저하: 블로트된 테이블을 스캔하는 쿼리는 죽은 튜플을 포함한 더 많은 데이터를 처리해야 하므로 실행 시간이 길어집니다. 인덱스 블로트도 유사한 해로운 영향을 미칠 수 있습니다.
캐시 효율성 감소: 블로트된 테이블과 인덱스는 데이터베이스 캐시에서 더 많은 공간을 차지하여 메모리에 유지될 수 있는 활성 사용 데이터의 양을 잠재적으로 줄일 수 있습니다.
Autovacuum 오버헤드: Autovacuum이 튜플 업데이트 및 삭제 속도를 따라잡지 못하면 자체적으로 성능 병목 현상이 될 수 있습니다.

Autovacuum 튜닝: 첫 번째 방어선

Autovacuum은 상당한 변경이 발생한 테이블에서 VACUUM 및 ANALYZE 작업을 자동으로 실행하도록 설계된 백그라운드 프로세스입니다. 기본적으로 활성화되어 있지만, 그 효과는 적절한 구성에 크게 의존합니다. Autovacuum 매개변수를 튜닝하는 것은 과도한 시스템 부하를 유발하지 않으면서 블로트를 방지하는 데 중요합니다.

postgresql.conf에서 찾을 수 있는 주요 Autovacuum 구성 매개변수:

autovacuum_vacuum_threshold: 테이블에서 VACUUM이 실행되기 전에 업데이트되거나 삭제된 튜플의 최소 수입니다. 기본값은 50입니다.
autovacuum_vacuum_scale_factor: VACUUM이 실행되기 전의 테이블 크기 비율입니다. 기본값은 0.2(20%)입니다.
- (죽은 튜플 수) > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * (살아있는 튜플 수)인 경우 VACUUM이 트리거됩니다.
autovacuum_analyze_threshold: ANALYZE가 실행되기 전에 삽입, 업데이트 또는 삭제된 튜플의 최소 수입니다. 기본값은 50입니다.
autovacuum_analyze_scale_factor: ANALYZE가 실행되기 전의 테이블 크기 비율입니다. 기본값은 0.1(10%)입니다.
- (변경된 튜플 수) > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * (살아있는 튜플 수)인 경우 ANALYZE가 트리거됩니다.
autovacuum_vacuum_cost_delay: 비용 한도를 초과한 경우 절전 모드로 전환되는 시간(밀리초)입니다. 기본값은 20ms입니다.
autovacuum_vacuum_cost_limit: 진공 프로세스가 절전 모드로 전환되기 전에 누적할 수 있는 최대 비용입니다. 기본값은 -1입니다(설정된 경우 vacuum_cost_limit를 사용하고, 그렇지 않으면 사실상 무제한이며 이는 이상적이지 않습니다).
autovacuum_max_workers: 동시에 실행할 수 있는 최대 백그라운드 진공 프로세스 수입니다. 기본값은 3입니다.
autovacuum_nap_time: autovacuum 작업 시작 사이의 최소 지연 시간입니다. 기본값은 1분입니다.

실용적인 Autovacuum 튜닝 시나리오:

높은 트랜잭션 속도 데이터베이스: 업데이트와 삭제가 빈번한 테이블의 경우 autovacuum_vacuum_threshold와 autovacuum_vacuum_scale_factor를 낮추어 진공이 더 자주 실행되도록 해야 할 수 있습니다. 예를 들어, 바쁜 테이블에서 다음과 같이 설정할 수 있습니다:
```
ALTER TABLE your_table SET (autovacuum_vacuum_threshold = 500, autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE your_table SET (autovacuum_analyze_threshold = 200, autovacuum_analyze_scale_factor = 0.02);
```
이렇게 하면 이 특정 테이블에서 진공이 더 적극적으로 이루어집니다.
가끔 업데이트되는 대규모 정적 테이블: 대부분 읽기 전용이고 거의 업데이트되지 않는 테이블의 경우 기본 설정이 괜찮을 수 있으며, 불필요한 진공 오버헤드를 줄이기 위해 scale_factor를 높일 수도 있습니다.
Autovacuum 영향 제어: Autovacuum이 너무 많은 리소스를 소비하지 않도록 하려면 autovacuum_vacuum_cost_delay와 autovacuum_vacuum_cost_limit를 조정할 수 있습니다. 올바른 값은 스토리지 속도와 워크로드에 따라 달라지므로 숫자를 맹목적으로 복사하지 말고 정상 트래픽 중에 테스트하십시오.
```
ALTER TABLE your_table SET (
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 5
);
```
session_replication_role은 autovacuum 튜닝 제어가 아닙니다. 트리거 및 규칙 동작에 영향을 미치며 블로트 관리 바로 가기로 사용해서는 안 됩니다.

수동 VACUUM 모범 사례

Autovacuum이 필수적이지만, 수동 VACUUM 작업이 필요하거나 유익한 상황이 있습니다:

대규모 데이터 로드/삭제 후: 대량 작업 후 수동 VACUUM을 수행하면 즉시 공간을 확보하고 블로트가 축적되는 것을 방지할 수 있습니다.
Autovacuum이 뒤처질 때: Autovacuum이 실행 중임에도 불구하고 상당한 블로트가 관찰되면 수동 VACUUM이 즉각적인 정리를 제공할 수 있습니다.
극심한 블로트에 대한 VACUUM FULL: 일반 VACUUM으로도 충분하지 않은 심각한 블로트의 경우 VACUUM FULL을 사용할 수 있습니다. 그러나 VACUUM FULL은 전체 테이블을 새 파일로 다시 쓰며, 이는 차단 작업(독점 잠금 필요)이며 대규모 테이블에서 매우 오래 걸릴 수 있습니다. 극도로 주의해서 사용해야 하며 이상적으로는 유지 관리 기간 동안 사용해야 합니다.
VACUUM (FREEZE): 이 옵션은 모든 미래 트랜잭션에 의해 영구적으로 표시될 수 있을 만큼 오래된 나머지 튜플을 고정하도록 VACUUM을 강제합니다. 이는 VACUUM 경고를 방지하고 트랜잭션 ID 랩어라운드 문제의 가능성을 줄이는 데 도움이 될 수 있습니다.

수동 VACUUM 명령:

표준 VACUUM: 공간을 회수하고 재사용할 수 있도록 만듭니다. TRUNCATE가 사용되지 않는 한 디스크의 파일 크기를 크게 줄이지는 않습니다.
```
VACUUM your_table;
VACUUM VERBOSE your_table; -- 더 많은 출력 제공
```
VACUUM ANALYZE: VACUUM을 수행한 다음 테이블 통계를 업데이트합니다. 이는 쿼리 플래너에 중요합니다.
```
VACUUM ANALYZE your_table;
```
VACUUM FULL: 테이블을 다시 작성하여 사용되지 않는 모든 공간을 회수하고 파일을 축소합니다. 독점 잠금이 필요합니다.
```
VACUUM FULL your_table;
```
VACUUM (FREEZE): 오래된 튜플의 고정을 강제합니다.
```
VACUUM (FREEZE) your_table;
```
VACUUM (TRUNCATE): PostgreSQL 13+에서 사용 가능한 이 옵션은 전체 작업에 대한 독점 잠금 없이 TRUNCATE와 유사하게 테이블 파일 끝에서 공간을 회수할 수 있습니다. 여전히 마지막에 짧은 독점 잠금이 필요합니다.
```
VACUUM (TRUNCATE) your_table;
```

고급 전략 및 고려 사항

기본 Autovacuum 튜닝 및 수동 VACUUM 명령 외에도 진공을 더욱 최적화할 수 있는 몇 가지 고급 기술이 있습니다:

블로트 모니터링: 정기적으로 테이블의 블로트를 모니터링하십시오. SQL 쿼리를 사용하여 블로트를 추정하거나 모니터링 도구를 활용할 수 있습니다.

-- 블로트 추정 쿼리(pgstattuple 확장 필요)
-- CREATE EXTENSION pgstattuple;
SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
    pg_size_pretty(pg_table_size(oid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(oid) - pg_table_size(oid)) AS index_size,
    CASE WHEN dead_tuples > 0 THEN round(100.0 * dead_tuples / (live_tuples + dead_tuples), 2) ELSE 0 END AS percent_bloat
FROM (
    SELECT
        schemaname,
        relname,
        n_live_tup AS live_tuples,
        n_dead_tup AS dead_tuples,
        c.oid
    FROM pg_stat_user_tables s JOIN pg_class c ON s.relid = c.oid
) AS stats
WHERE live_tuples + dead_tuples > 0
ORDER BY percent_bloat DESC;

-- 확장 없이 블로트를 추정하는 대체 쿼리
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) ELSE 0 END AS percent_bloat
FROM pg_stat_user_tables
ORDER BY percent_bloat DESC;

인덱스 유지 관리: 인덱스도 블로트될 수 있습니다. 필요한 경우 REINDEX를 사용하여 다시 빌드하십시오. 일반 REINDEX는 정상 작업을 차단할 수 있습니다. REINDEX CONCURRENTLY는 중단을 줄이지만 시간이 더 오래 걸리고 여전히 계획이 필요합니다.
```
REINDEX INDEX CONCURRENTLY your_index_name;
```
트랜잭션 ID 랩어라운드 방지: PostgreSQL은 트랜잭션 ID를 재사용합니다. ID가 최대값에 도달하면 랩어라운드됩니다. 데이터 손상을 방지하기 위해 PostgreSQL은 오래된 튜플을 고정합니다. VACUUM(특히 FREEZE 사용)이 중요한 역할을 합니다. Autovacuum의 freeze_max_age 매개변수는 다른 임계값이 충족되지 않더라도 Autovacuum이 강제로 실행되기 전에 트랜잭션 ID가 얼마나 오래될 수 있는지를 결정합니다.
```
-- 트랜잭션 ID 기간 모니터링
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC LIMIT 10;
```
매우 큰 기간이 표시되면 진공이 따라잡지 못하고 있음을 나타냅니다.
파티셔닝 전략: 매우 큰 테이블의 경우 파티셔닝을 고려하십시오. 더 작은 파티션을 진공하는 것은 거대한 단일 테이블을 진공하는 것보다 훨씬 빠르고 리소스 집약적이지 않습니다.
연결 풀링: 직접적인 진공 전략은 아니지만, 효율적인 연결 풀링(PgBouncer 사용 등)은 데이터베이스 연결 설정의 오버헤드를 줄여 전체 데이터베이스 성능에 간접적으로 이점을 주고 Autovacuum과 같은 백그라운드 유지 관리 작업이 더 원활하게 실행되도록 할 수 있습니다.
긴 트랜잭션 제어: 단일 오래된 트랜잭션이 정리를 방해할 수 있습니다. 특히 idle in transaction 세션과 같이 오랫동안 열려 있는 세션을 확인하십시오. 오래된 행 버전을 계속 표시하고 블로트가 증가하도록 강제할 수 있기 때문입니다.
```
SELECT pid, state, now() - xact_start AS transaction_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
```

실용적인 진공 튜닝 워크플로

전체 서버가 아닌 문제가 있는 테이블부터 시작하십시오. 900GB 주문 테이블이 블로트되고 20MB 조회 테이블이 깨끗하다면 전역 변경은 실제 문제를 해결하지 못하고 소음만 발생시킬 수 있습니다. 먼저 pg_stat_user_tables를 살펴보십시오:

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

그런 다음 이를 워크로드와 비교하십시오. 상태를 지속적으로 업데이트하는 큐와 유사한 테이블은 낮은 autovacuum_vacuum_scale_factor가 필요할 수 있습니다. 거대한 테이블의 20%가 죽을 때까지 기다리는 것은 너무 늦기 때문입니다. 월별 아카이브 파티션은 적극적인 설정이 전혀 필요하지 않을 수 있습니다. 테이블별 설정을 사용하면 이러한 경우를 다르게 처리할 수 있습니다.

무거운 업데이트 테이블의 경우 일반적인 패턴은 다음과 같습니다:

ALTER TABLE job_events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 1000
);

이 숫자는 시작점일 뿐 보편적인 진리는 아닙니다. autovacuum 실행 사이에 죽은 튜플이 더 이상 증가하지 않는지, 쿼리 대기 시간이 개선되는지, autovacuum이 피크 시간에 허용할 수 없는 I/O를 생성하는지 관찰하십시오.

블로트가 이미 심각한 경우 일반 VACUUM은 출혈을 멈출 수 있지만 관계 파일을 축소하지는 않습니다. 이는 많은 팀을 놀라게 합니다. 일반 VACUUM은 테이블 내부에서 공간을 재사용 가능하게 만듭니다. 일반적으로 대부분의 공간을 운영 체제에 반환하지 않습니다. 대규모 테이블을 물리적으로 축소하려면 VACUUM FULL, 테이블 재구축, 파티션 순환 또는 허용되는 경우 pg_repack과 같은 도구와 같은 중단 옵션 중에서 선택해야 합니다. 각 옵션에는 잠금, 디스크 공간 및 운영상의 절충점이 있습니다.

가장 고통스럽지 않은 수정 선택

테이블이 적당히 블로트되었지만 여전히 꾸준한 쓰기를 받고 있다면 autovacuum 튜닝과 오래된 트랜잭션 정리부터 시작하십시오. 업무 시간에 대규모 테이블을 다시 작성하는 대신 PostgreSQL이 공간을 자연스럽게 재사용하도록 하려는 것입니다.

테이블이 일회성 제거를 거쳐 이제 훨씬 작아진 경우 일반 VACUUM은 빈 공간을 향후 삽입 및 업데이트에 재사용할 수 있게 만듭니다. 해당 공간을 운영 체제에 반환해야 하는 경우 다시 쓰기 옵션을 계획하십시오. VACUUM FULL은 간단하지만 차단됩니다. pg_repack은 덜 방해가 될 수 있지만 추가 확장이며 대체 구조를 구축하기에 충분한 여유 디스크 공간이 여전히 필요합니다. 파티션된 테이블은 다른 옵션을 제공합니다. 하나의 거대한 테이블에서 수백만 행을 삭제하는 대신 이전 파티션을 삭제하거나 분리하십시오.

인덱스가 문제라면 습관적으로 모든 인덱스를 다시 빌드하지 마십시오. 크고 사용되지 않거나 중복된 인덱스를 확인하십시오. pg_stat_user_indexes는 인덱스 스캔 수를 표시할 수 있으며 스키마 검토는 (user_id) 및 (user_id, created_at)과 같이 하나만 필요할 수 있는 중복 인덱스를 드러낼 수 있습니다. 실제로 사용되지 않는 인덱스를 제거하면 쓰기 성능이 향상되고 향후 진공 작업이 줄어들 수 있습니다.

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

재시작 또는 통계 재설정 후 "사용되지 않는" 인덱스는 카운터가 다시 시작되므로 주의하십시오. 무언가를 삭제하기 전에 충분한 기록을 살펴보십시오.

좋은 진공 전략은 효과가 있을 때 지루합니다. Autovacuum은 죽은 튜플이 쌓이지 않을 정도로 자주 실행되고, 수동 유지 관리는 알려진 이벤트를 위해 예약되며, 오래된 트랜잭션은 무해한 유휴 세션이 아닌 프로덕션 문제로 취급됩니다. 목표는 가능한 한 많이 진공하는 것이 아닙니다. 목표는 애플리케이션에 필요한 I/O를 도용하지 않으면서 정리가 변동을 앞서도록 유지하는 것입니다.