인덱스 블로트는 왜 생기고 어떻게 없애는가

UPDATE는 사실 DELETE + INSERT다

PostgreSQL은 MVCC(다중 버전 동시성 제어)로 동시성을 처리합니다. 어떤 트랜잭션이 특정 행을 읽고 있는 동안 다른 트랜잭션이 그 행을 마음대로 지워버리면 안 되기 때문에, UPDATE는 기존 행을 즉시 지우지 않습니다. 대신 기존 행을 "죽은 행(dead tuple)"으로 표시만 하고, 변경된 내용을 담은 새 행을 별도로 씁니다. DELETE도 마찬가지로 즉시 공간을 회수하지 않고 표시만 합니다. 이 죽은 행들을 정리하는 게 VACUUM의 일입니다.

문제는 인덱스입니다. 행이 새로 쓰이면 그 행을 가리키는 인덱스 항목도 새로 추가돼야 합니다. 죽은 행을 가리키던 기존 인덱스 항목은 VACUUM이 돌기 전까지 그대로 남아 있습니다. UPDATE가 잦은 테이블은 인덱스 안에 이미 쓸모없어진 항목이 계속 쌓이고, 이게 인덱스 블로트(bloat)입니다.

블로트가 왜 성능에 직접 영향을 주는가

인덱스 크기가 실제 필요한 것보다 부풀면, 같은 조건을 조회해도 더 많은 페이지를 읽어야 합니다. 3장에서 BUFFERS: shared hit=4 로 확인했던 그 숫자가, 블로트가 심한 인덱스에서는 같은 조건인데도 수십, 수백으로 늘어날 수 있습니다. 트리 깊이가 그대로여도 리프 페이지 안에 죽은 항목이 섞여 있어 유효 데이터 밀도가 떨어지기 때문입니다.

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

정확한 블로트 비율은 별도 확장(pgstattuple)으로 확인하는 게 가장 정확합니다.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('idx_orders_customer_created');
-- avg_leaf_density 가 낮을수록(예: 30% 이하) 블로트가 심하다는 신호

autovacuum이 왜 못 따라가는가

기본적으로 autovacuum은 테이블의 죽은 행 비율이 일정 임계치(autovacuum_vacuum_scale_factor, 기본 20%)를 넘으면 돌기 시작합니다. 대형 테이블에서는 이 20%가 절대적인 행 수로는 수백만 건이 될 수 있어서, 그만큼 쌓일 때까지 기다렸다가 한 번에 처리하려니 오래 걸리고, 그동안 새로운 죽은 행은 계속 쌓입니다. 게다가 장시간 열린 트랜잭션이 하나라도 있으면 VACUUM은 그 트랜잭션이 시작되기 전 시점의 행을 죽은 것으로 확정 짓지 못해 회수를 미룹니다 — 흔히 놓치는 원인입니다.

-- 특정 대형 테이블만 더 자주 청소하도록 개별 설정
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_delay = 2
);

전역 설정을 낮추면 작은 테이블까지 불필요하게 자주 VACUUM이 돌아 오히려 부하가 늘 수 있으니, 쓰기가 몰리는 특정 테이블에만 좁혀서 조정하는 걸 권합니다.

이미 부푼 인덱스는 REINDEX로 되돌린다

VACUUM은 죽은 항목이 차지하던 공간을 재사용 가능하게 표시할 뿐, 인덱스 파일 자체를 줄여주지는 않습니다(대부분의 경우). 이미 심하게 부푼 인덱스는 통째로 다시 만들어야 합니다.

REINDEX INDEX CONCURRENTLY idx_orders_customer_created;

CONCURRENTLY 없이 REINDEX를 돌리면 그 인덱스가 걸린 테이블에 ACCESS EXCLUSIVE 락이 걸려 조회조차 막힙니다. 운영 중인 테이블이라면 CONCURRENTLY는 사실상 필수입니다. 다만 이 옵션은 일반 REINDEX보다 시간이 더 걸리고, 임시로 기존 인덱스와 새 인덱스가 동시에 존재하는 시간 동안 디스크 공간을 두 배 가까이 씁니다. 다음 장에서 CREATE INDEX CONCURRENTLY를 다룰 때 같은 원리를 더 자세히 짚겠습니다 — REINDEX CONCURRENTLY도 내부적으로 거의 같은 2단계 스캔 메커니즘을 씁니다.