인덱스 팽창(bloat) 확인하고 REINDEX CONCURRENTLY 로 줄이기

테이블은 청소가 잘 되고 있는데 인덱스만 계속 커져요. 데이터는 그대로인데 인덱스 크기가 테이블보다 커진 것도 있어요.

인덱스가 커지면서 조회도 조금씩 느려져요. 캐시에 안 들어가니 디스크를 더 읽게 되고요.

그런데 이건 어느 날 터지는 문제가 아니라 몇 달에 걸쳐 서서히 진행돼요. 그래서 원인으로 지목되는 일이 거의 없어요.

서버를 키우거나 캐시를 늘리는 걸로 대응하다가, 어느 순간 인덱스를 다시 만들어 보고서야 알게 됩니다. 그리고 그때 크기가 절반으로 줄어드는 걸 보고 놀라죠. 그동안 낸 서버 비용이 그 빈 공간 값이었던 거예요.

왜 그런가

인덱스도 테이블처럼 갱신되면 죽은 항목이 생겨요. 행을 지우거나 갱신하면 인덱스의 옛 항목도 더 이상 필요 없어지죠.

VACUUM 이 그걸 회수해요. 그런데 회수된 자리는 재사용 가능해질 뿐이지 페이지가 합쳐지지는 않아요. 그래서 페이지 안에 빈 공간이 많은 상태가 유지될 수 있어요. 항목 수는 그대로인데 차지하는 페이지 수는 많은 상태, 이게 인덱스 팽창이에요.

특히 나빠지는 패턴이 있어요.

무작위 키. UUID 처럼 순서가 없는 값을 기본키로 쓰면 삽입이 인덱스 전체에 흩어져요. 페이지가 자주 쪼개지고, 쪼개진 페이지는 절반만 차 있어요. 순차적으로 증가하는 키는 마지막 페이지에만 붙으니 이 문제가 훨씬 적습니다.

넓은 범위의 갱신. 자주 바뀌는 컬럼에 인덱스가 걸려 있으면 매 갱신마다 인덱스 항목이 옮겨 다녀요. 상태 컬럼이 대표적이에요.

그리고 대량 삭제 뒤. 지운 자리가 비지만 페이지는 남아요. 앞서 본 소프트 삭제나 큐 테이블에서 이 현상이 두드러집니다.

정리하면 팽창은 삽입 위치가 흩어지거나 갱신이 잦을 때 빨라져요. 어느 쪽이든 원인은 인덱스 자체가 아니라 그 인덱스를 쓰는 방식에 있습니다.

코드로 보면

다시 만들어야 줄어든다:

코드

· 인덱스 크기와 항목 수를 견줘 본다
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
  FROM pg_stat_user_indexes
 WHERE relname = 'orders'
 ORDER BY pg_relation_size(indexrelid) DESC;

· 서비스를 멈추지 않고 다시 만든다 (PG12+)
✓ REINDEX INDEX CONCURRENTLY idx_orders_status;

✕ REINDEX INDEX idx_orders_status;
✕ -- CONCURRENTLY 없으면 쓰기가 막힌다

· 애초에 덜 부풀게 — 순차 증가 키를 쓴다
✓ 무작위 UUID 대신 시간 순서가 있는 식별자

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

팽창을 실제로 측정하는 게 첫 단계예요. 크기만 봐서는 부푼 건지 원래 큰 건지 알 수 없거든요.

가장 확실한 방법은 새로 만들어 보고 비교하는 거예요. 같은 정의로 인덱스를 하나 더 만들어 크기를 재면, 그 차이가 팽창분이에요. 디스크 여유가 있어야 하지만 가장 정확합니다.

추정 도구도 있어요. 확장을 설치하면 페이지의 채움 정도를 직접 볼 수 있어요. 다만 큰 인덱스에서는 이 검사 자체가 부하라 조심해야 해요.

대응은 다시 만드는 거예요. PostgreSQL 12 부터 서비스를 멈추지 않고 할 수 있어요. 앞서 본 CONCURRENTLY 의 특성이 그대로 적용돼서, 시간이 더 걸리고 실패하면 정리가 필요합니다.

예방 쪽이 더 중요해요.

키 선택. 순차적으로 증가하는 키는 마지막 페이지에만 삽입되니 페이지 분할이 적어요. 무작위 UUID 를 기본키로 쓰면 삽입이 흩어져 페이지 분할이 잦고, 캐시 효율도 나빠요. 시간 순서가 포함된 식별자를 쓰면 두 이점을 다 얻을 수 있습니다.

채움 비율 조정. 인덱스를 만들 때 페이지를 얼마나 채울지 정할 수 있어요. 갱신이 잦은 인덱스는 여유를 두면 페이지 분할이 줄어요. 대신 처음부터 인덱스가 커집니다.

그리고 안 쓰는 인덱스를 없애는 게 가장 확실한 예방이에요. 부풀 인덱스가 애초에 없으면 팽창도 없죠. 앞서 본 "안 쓰는 인덱스는 세금" 편과 이어지는 얘기예요.

청소가 제때 도는 것도 전제 조건이에요. 회수가 밀리면 재사용도 안 되니 팽창이 가속됩니다.

처방

인덱스 크기를 정기적으로 보세요. 테이블 대비 인덱스 합계 비율을 지표로 두면 서서히 나빠지는 걸 잡을 수 있어요.
부풀었다고 판단되면 CONCURRENTLY 로 다시 만드세요. 만든 뒤 유효성 확인도 잊지 마세요.
무작위 키를 기본키로 쓰고 있다면 순서가 있는 식별자로 바꾸는 걸 검토하세요. 신규 설계라면 특히요.
갱신이 잦은 컬럼의 인덱스는 채움 비율에 여유를 주세요. 페이지 분할이 줄어듭니다.
안 쓰는 인덱스를 없애세요. 가장 확실한 예방이에요.
청소가 제때 도는지 확인하세요. 회수가 밀리면 팽창이 가속됩니다.
큐 테이블이나 소프트 삭제처럼 쓰기가 몰리는 테이블은 따로 관리하세요. 이 테이블들의 인덱스가 가장 빨리 부풉니다.
다시 만드는 작업을 정기 점검에 넣으세요. 일회성 대응으로 끝나지 않아요.
다시 만들기 전에 디스크 여유를 확인하세요. 원본과 새 인덱스가 동시에 존재하는 구간이 있어서, 빠듯하면 작업이 실패합니다.

지금 확인해 보세요

정말 부푼 게 맞는지:

코드

· 테이블 대비 인덱스 크기 — 지표로 두면 추세가 보인다
SELECT relname,
       pg_size_pretty(pg_table_size(relid)) AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS index_size,
       round(pg_indexes_size(relid)::numeric
             / nullif(pg_table_size(relid),0), 2) AS ratio
  FROM pg_stat_user_tables
 ORDER BY pg_indexes_size(relid) DESC LIMIT 10;

· 확실한 방법 — 새로 만들어 크기를 비교한다
✓ CREATE INDEX CONCURRENTLY idx_test ON orders(status);
✓ 두 인덱스의 크기 차이가 곧 팽창분이다
✓ DROP INDEX CONCURRENTLY idx_test;

· 청소가 도는지 같이 본다 (근본 원인일 수 있다)
SELECT relname, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables WHERE relname='orders';

핵심 한 줄다시 만들면 줄어든다. 그런데 원인을 안 고치면 또 부푼다.

왜 그런가

코드로 보면

실전에서는

처방

지금 확인해 보세요

이어서 읽기