PostgreSQL 인덱스가 느릴 때 - Bloat·VACUUM·REINDEX

서버가 바쁜 것도 아닌데 특정 테이블/인덱스만 유독 느려지는 순간이 있습니다. EXPLAIN을 보면 여전히 인덱스를 타는데도 I/O가 늘고, 캐시 히트율이 떨어지며, 예전보다 같은 쿼리가 오래 걸립니다. 이때 많은 경우 문제는 **쿼리 자체가 아니라 “저장 구조의 팽창(bloat) + 청소(VACUUM) 부족 + 통계/가시성(visibility) 저하”**에서 시작합니다.

이 글에서는 PostgreSQL에서 인덱스가 느려졌을 때 가장 흔한 원인인 **bloating(테이블/인덱스 팽창)**을 어떻게 확인하고, **VACUUM(특히 VACUUM (ANALYZE))**과 **REINDEX(특히 CONCURRENTLY)**를 어떤 기준으로 적용할지, 그리고 운영에서 안전하게 실행하는 체크리스트를 정리합니다.

> 운영 장애의 원인이 “리소스 고갈”로 보일 때도, 근본은 DB 내부 청소/유지보수 부재인 경우가 많습니다. 애플리케이션 레벨 병목과 함께 점검하려면 Spring Boot HikariCP 커넥션 고갈 원인 8가지도 같이 참고하면 원인 분리가 빨라집니다.

1) 증상 패턴: 인덱스를 타는데도 느린 이유

인덱스가 느릴 때 자주 보이는 패턴은 다음과 같습니다.

• 인덱스 스캔 페이지 수 증가: 인덱스가 커져서(팽창) 필요한 키를 찾기 위해 읽는 페이지가 늘어남
• Heap fetch 증가: 인덱스만으로 결과를 못 내고 테이블(Heap)을 더 많이 읽음
• Index-only scan이 index scan으로 강등: visibility map이 충분히 세팅되지 않아 index-only scan이 성립하지 않음
• Dead tuple 증가: 업데이트/삭제가 많고 vacuum이 따라가지 못함
• Planner 통계가 낡음: 잘못된 카디널리티 추정으로 비효율적인 플랜 선택

핵심은 PostgreSQL이 MVCC 기반이라 UPDATE는 사실상 DELETE+INSERT에 가깝고, 그 결과로 **죽은 튜플(dead tuple)**이 쌓이며, 이걸 vacuum이 정리하지 않으면 테이블과 인덱스가 함께 비대해질 수 있다는 점입니다.

2) 가장 먼저 할 것: 쿼리 플랜과 “읽는 양” 확인

느린 쿼리를 잡을 때는 시간(ms)보다 읽은 블록 수, heap fetch, rows 추정/실제 차이를 봐야 합니다.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT *
FROM orders
WHERE user_id = 42
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

여기서 체크 포인트:

• Buffers: shared read/hit에서 read가 과도하면 디스크 I/O가 늘어난 상태
• Index Only Scan인데 Heap Fetches가 많으면 visibility map이 덜 채워졌거나 최근 변경이 많음
• rows= 추정치와 실제가 크게 다르면 ANALYZE 부족/통계 부정확 가능성

이 단계에서 “인덱스 구조가 커져서 더 읽는다”인지, “플랜이 잘못됐다”인지 방향이 잡힙니다.

3) bloat 진단 1: 테이블/인덱스 크기와 사용량

3.1 테이블/인덱스 크기 빠르게 보기

SELECT
  n.nspname AS schema,
  c.relname AS table,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
  pg_size_pretty(pg_relation_size(c.oid)) AS heap_size,
  pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS index_toast_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

• total_size 대비 heap_size가 작고 index_toast_size가 비정상적으로 크면 인덱스가 과도하게 커진 상황일 수 있습니다.

3.2 인덱스별 크기

SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 30;

3.3 “큰 인덱스가 실제로 쓰이나?” (사용 통계)

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

• idx_scan이 낮은데 인덱스가 큰 경우: 불필요한 인덱스일 수 있고, 유지비(쓰기 비용, vacuum 부담)만 키웁니다.
• 다만 통계는 리셋될 수 있으니(재시작/수동 reset) 기간을 고려하세요.

4) bloat 진단 2: dead tuple과 vacuum 상태

4.1 dead tuple(추정) 확인

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

• dead_pct가 높고 last_autovacuum이 오래됐으면 autovacuum이 못 따라가는 상태입니다.
• last_autoanalyze가 오래됐으면 플래너 통계가 낡았을 가능성이 큽니다.

4.2 autovacuum이 왜 못 따라가나 (진행 중 작업 확인)

SELECT
  pid,
  datname,
  relid::regclass AS relation,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  max_dead_tuples,
  num_dead_tuples
FROM pg_stat_progress_vacuum;

• vacuum이 느리게 진행되거나 멈춘 듯 보이면 I/O, lock, cost 설정, freeze 이슈 등을 의심할 수 있습니다.

5) 인덱스가 느려지는 핵심 메커니즘 3가지

5.1 인덱스 bloat: 페이지가 늘어 탐색 비용이 증가

B-tree 인덱스는 분할/병합이 반복되며 빈 공간이 생기고, 업데이트가 잦으면 인덱스 페이지가 커집니다. 결과적으로 같은 조건을 찾는데도 더 많은 페이지를 읽게 됩니다.

5.2 visibility map 저하: index-only scan이 효율을 잃음

Index-only scan은 “해당 heap 페이지가 모두 visible”일 때 heap 접근을 생략합니다. 하지만 vacuum이 충분히 돌지 않으면 visibility map이 채워지지 않아 Heap Fetches가 늘고, 결국 랜덤 I/O가 증가합니다.

5.3 통계 부정확: 플래너가 잘못된 플랜 선택

테이블이 커지고 분포가 바뀌었는데 analyze가 오래되면, 선택도가 크게 틀어져 인덱스를 타야 할 곳에서 seq scan을 하거나, 반대로 비효율적인 인덱스를 고집할 수 있습니다.

6) 처방 1: VACUUM / VACUUM (ANALYZE) 제대로 쓰기

6.1 VACUUM은 “용량을 줄이지 않는다”

중요한 오해: 일반 VACUUM은 dead tuple을 재사용 가능하게 만들고 visibility map을 개선하지만, 파일 크기를 OS에 반환(축소)하지는 않습니다.

• 공간 회수가 필요하면 VACUUM FULL 또는 CLUSTER 또는 테이블 재작성(CTAS 등)이 필요
• 대신 VACUUM FULL은 **강한 락(AccessExclusive)**을 잡아 운영에 위험

6.2 우선순위 높은 기본 처방: VACUUM (ANALYZE)

VACUUM (ANALYZE) orders;

• ANALYZE로 통계를 갱신해 플랜을 안정화
• vacuum으로 visibility map을 채워 index-only scan 효율을 회복

운영 팁:

• 특정 테이블만 비정상적으로 느리면 그 테이블부터 수동 vacuum/analyze를 걸어 즉시 효과를 확인합니다.
• 대형 테이블은 트래픽 저점 시간에 실행하고, I/O가 빡빡하면 vacuum_cost_limit, vacuum_cost_delay 튜닝이 필요할 수 있습니다.

6.3 autovacuum 튜닝 포인트(테이블 단위)

쓰기 많은 테이블은 전역 설정만으로 부족한 경우가 많습니다.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000
);

• scale_factor는 “테이블 크기에 비례”하므로 대형 테이블일수록 트리거가 늦어집니다.
• 변경이 폭발적으로 많은 테이블은 scale_factor를 낮춰 더 자주 청소/분석하게 만듭니다.

7) 처방 2: REINDEX가 필요한 순간과 안전한 실행

7.1 REINDEX가 특히 효과적인 경우

• 인덱스 bloat가 심해져 인덱스 크기 대비 성능이 떨어질 때
• VACUUM으로도 성능이 회복되지 않고, 인덱스 페이지 읽기가 과도할 때
• 인덱스가 손상되었거나(드문 케이스) 의심될 때

7.2 운영에서 권장: REINDEX CONCURRENTLY

PostgreSQL은 인덱스 재구성 시 락이 관건입니다.

REINDEX INDEX CONCURRENTLY idx_orders_user_created_at;

• 동시성 모드로 재구성하면 읽기/쓰기 영향이 훨씬 적습니다.
• 대신 시간이 더 걸리고, 추가 디스크 공간이 필요하며, 트랜잭션 블록 안에서 실행할 수 없습니다.

테이블 전체를 하고 싶다면:

REINDEX TABLE CONCURRENTLY orders;

7.3 REINDEX 전 체크리스트

• 디스크 여유: 재구성 중 임시로 공간이 더 필요
• 장시간 트랜잭션: 오래 열린 트랜잭션이 vacuum/cleanup을 방해할 수 있음
• 배포/배치 시간 조정: 인덱스 재구성은 I/O를 크게 씀

8) “VACUUM FULL vs REINDEX vs CLUSTER” 선택 가이드

8.1 VACUUM (ANALYZE)

• 목적: dead tuple 정리, visibility map 개선, 통계 갱신
• 장점: 상대적으로 안전, 온라인 영향 적음
• 한계: 파일 크기 축소는 안 됨

8.2 REINDEX (CONCURRENTLY)

• 목적: 인덱스 bloat 제거, 인덱스 탐색 비용 감소
• 장점: 인덱스 성능 직접 개선
• 한계: 시간/디스크 소모, 완전 무중단은 아님(짧은 메타데이터 락은 존재)

8.3 VACUUM FULL

• 목적: 테이블/인덱스 물리적 재작성으로 디스크 반환
• 장점: 공간 회수 확실
• 치명적 단점: 강한 락으로 운영 영향 큼

8.4 CLUSTER

• 목적: 특정 인덱스 순서로 테이블 재정렬(지역성 개선)
• 장점: 범위 조회/정렬 패턴에서 큰 이득 가능
• 단점: 테이블 재작성 + 락 부담, 이후 삽입/업데이트로 다시 흐트러짐

정리하면:

• “느려졌다”의 1차 대응은 VACUUM (ANALYZE)
• “인덱스 자체가 비대/비효율”이면 REINDEX CONCURRENTLY
• “디스크를 반드시 줄여야” 하고 다운타임 허용이면 VACUUM FULL/CLUSTER

9) 실전 진단 시나리오: index-only scan인데 Heap Fetches가 폭증

다음과 같은 플랜을 봤다고 가정해봅시다.

• 노드: Index Only Scan using idx_xxx
• Heap Fetches: 120000 (비정상적으로 큼)

이 경우의 접근 순서:

1. pg_stat_user_tables에서 해당 테이블 n_dead_tup, last_autovacuum 확인
2. 수동 VACUUM (ANALYZE) 실행 후 동일 쿼리 재측정
3. 개선이 제한적이면 인덱스 크기/스캔량을 보고 REINDEX CONCURRENTLY 검토

측정은 반드시 같은 조건에서:

-- 캐시 영향까지 보려면 pg_stat_statements와 함께
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

10) 운영 자동화: bloat/청소 지표를 “지속 관측”하기

bloat는 한 번 해결해도 워크로드가 같으면 다시 생깁니다. 따라서 다음을 주기적으로 관측하는 것이 좋습니다.

• 상위 테이블/인덱스 크기 Top N
• n_dead_tup, dead_pct Top N
• autovacuum 지연(마지막 실행 시각)
• index-only scan의 heap fetch 추세

애플리케이션에서 “갑자기 느려짐”이 터졌을 때, DB 외부 요인(커넥션 풀, 스레드 고갈, 네트워크)과 함께 교차 검증하면 원인 규명이 빨라집니다. 예를 들어 커넥션 대기열이 길어져 느려 보이는 케이스는 DB 튜닝과 별개로 풀 설정 문제일 수 있으니, 필요하면 앞서 언급한 커넥션 풀 글(Spring Boot HikariCP 커넥션 고갈 원인 8가지)처럼 애플리케이션 계층도 같이 확인하세요.

11) 마무리: 빠른 체크리스트

• EXPLAIN (ANALYZE, BUFFERS)로 읽기량(read/hit), Heap Fetches, rows 추정 오차를 확인
• pg_stat_user_tables로 n_dead_tup, 마지막 vacuum/analyze 시각 확인
• 크기 상위 인덱스의 idx_scan으로 “비대 + 미사용” 인덱스 후보 제거 검토
• 1차: VACUUM (ANALYZE)로 통계/가시성 회복
• 2차: 인덱스 bloat가 의심되면 REINDEX ... CONCURRENTLY
• 공간 회수(디스크 축소)가 목표면 VACUUM FULL/CLUSTER를 다운타임 포함해 계획적으로

인덱스가 느려졌을 때 “인덱스를 더 추가”하는 것은 종종 문제를 악화시킵니다. 먼저 bloat와 vacuum 상태를 수치로 확인하고, VACUUM으로 회복 가능한 문제인지, REINDEX가 필요한 수준인지를 단계적으로 판별하는 것이 가장 안전하고 재현 가능한 접근입니다.