PostgreSQL 인덱스 재구성 시점 — 달력이 아니라 밀도로 정한다

PostgreSQL 인덱스는 주기적으로 재구성할 필요가 없다. 공식 문서도 B-tree에 정기 재구성이 필요하다고 말하지 않는다. 재구성이 값어치를 하는 건 특정 사용 패턴일 때다. 각 범위에서 대부분의 키가 지워지는데 몇 개만 남는 패턴이면 페이지가 계속 할당된 채 남아 공간이 낭비된다. 그러니 달력이 아니라 측정값으로 판단한다. pgstatindex의 avg_leaf_density를 보면 지금 재구성할 값어치가 있는지 나온다.

언제 재구성이 필요한가

문서가 드는 경우는 넷이다.

1. 인덱스가 손상됐을 때 — 소프트웨어 버그나 하드웨어 장애로 유효한 데이터를 잃은 경우
2. 인덱스가 부풀었을 때 — 비었거나 거의 빈 페이지가 많아진 경우
3. 저장 파라미터를 바꿨을 때 — fillfactor 같은 값을 바꾸고 완전히 반영하고 싶을 때
4. 동시 빌드가 실패해 무효 인덱스가 남았을 때

"몇 달에 한 번"은 목록에 없다. (문서)

부풀어 오르는 조건은 구체적이다

B-tree는 완전히 빈 페이지는 회수해서 다시 쓴다. 문제는 부분적으로 빈 페이지다. 그건 할당된 채로 남는다.

그래서 문서가 지목하는 패턴이 이렇다. 한 페이지의 인덱스 키가 몇 개만 남고 나머지가 지워지면 그 페이지는 계속 할당된 채로 남고, 각 범위에서 대부분의 키가 결국 지워지는 사용 패턴이면 공간 활용이 나빠진다. (문서)

"많이 지운다"가 아니라 "범위마다 조금씩 남기고 지운다"가 조건이다. 전부 지우면 페이지가 회수된다. 애매하게 남는 게 문제다.

기간별 로그를 지우는 테이블, 상태가 끝난 행만 골라 지우는 테이블이 여기 해당한다.

부수 효과가 하나 더 있다. 갓 만든 인덱스는 논리적으로 인접한 페이지가 물리적으로도 인접해서 접근이 조금 더 빠르다. 다만 문서도 이건 "조금"이라고만 한다.

B-tree가 아닌 인덱스는 다르다. 부풀음이 잘 연구되지 않았다고 문서가 밝히고 있어서, 물리적 크기를 주기적으로 보는 수밖에 없다.

그래서 재는 방법

pgstattuple 확장의 pgstatindex를 쓴다.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT index_size, leaf_pages, empty_pages, deleted_pages,
       avg_leaf_density, leaf_fragmentation
  FROM pgstatindex('idx_orders_created_at');

볼 값은 둘이다.

• avg_leaf_density — 리프 페이지가 평균 얼마나 차 있는지
• leaf_fragmentation — 리프 페이지가 얼마나 흩어져 있는지

갓 만든 인덱스의 밀도와 비교하는 게 가장 확실하다. 같은 인덱스를 다른 이름으로 하나 만들어 두 값을 나란히 보면 지금 것이 얼마나 빠졌는지 바로 나온다. 절대 기준을 외우는 것보다 이쪽이 정확하다.

empty_pages와 deleted_pages가 leaf_pages 대비 크면 위의 "조금씩 남기고 지우는" 패턴에 걸린 것이다.

재는 비용도 공짜가 아니다

pgstattuple은 관계 전체를 스캔한다. 큰 테이블에서는 비싸다. 읽기 락만 잡으므로 서비스를 막지는 않지만, 결과가 순간의 스냅샷은 아니다.

테이블 쪽 통계는 pgstattuple_approx로 대신할 수 있다. 가시성 맵으로 페이지를 건너뛰고 여유 공간 맵을 쓰므로 훨씬 싸다. 죽은 튜플 수치는 정확하고 살아 있는 쪽이 추정이다.

재구성은 CONCURRENTLY로

그냥 REINDEX는 대상 인덱스에 ACCESS EXCLUSIVE 락을 잡는다. 부모 테이블의 쓰기가 막히고, 그 인덱스를 쓰는 조회도 사실상 전부 막힌다.

REINDEX INDEX CONCURRENTLY idx_orders_created_at;

CONCURRENTLY는 동시 INSERT·UPDATE·DELETE를 막는 락을 잡지 않는다. 대신 테이블을 인덱스마다 두 번 스캔하고 CPU·메모리·I/O를 더 쓴다. 느리지만 서비스가 돈다.

제약도 있다.

• 트랜잭션 블록 안에서 못 쓴다
• 한 테이블에 동시 빌드는 하나만 가능하다
• REINDEX SYSTEM은 CONCURRENTLY를 지원하지 않는다
• 배제 제약(exclusion constraint)의 인덱스는 건너뛴다
• 오래 돌면 다른 테이블의 VACUUM이 정리할 수 있는 튜플에도 영향을 준다

실패하면 찌꺼기가 남는다

여기가 실무에서 제일 중요한 부분이다. REINDEX CONCURRENTLY가 중간에 실패하면 무효 인덱스가 남는다. 접미어로 구분한다.

접미어	정체	처리
_ccnew	작업 중 만들어진 임시 인덱스	지우고 다시 REINDEX CONCURRENTLY
_ccold	지우지 못한 원본 인덱스	그냥 지운다. 재구성은 이미 성공한 것

이름이 겹치면 _ccnew1, _ccold2처럼 숫자가 붙는다.

무효 인덱스는 조회에는 안 쓰이는데 갱신 부하는 그대로 받는다. 즉 이득은 없고 비용만 낸다. 남아 있는 걸 모르면 "재구성했는데 왜 더 느리지"가 된다.

재구성 뒤에는 반드시 확인한다.

SELECT indexrelid::regclass AS index_name, indisvalid
  FROM pg_index
 WHERE NOT indisvalid;

FAQ

VACUUM을 돌리면 인덱스도 정리되나요

완전히는 아니다. 완전히 빈 B-tree 페이지는 회수되어 재사용되지만, 부분적으로 빈 페이지는 그대로 남는다. 부풀음의 원인이 그쪽이라 VACUUM만으로는 안 준다.

정기적으로 REINDEX를 걸어 두면 안 되나요

걸 수는 있는데 근거가 없다. 문서가 권하는 건 해당 패턴을 가진 인덱스에 대해서다. 전부 돌리면 비용만 커진다. 재 보고 값이 나쁜 것만 고르는 게 맞다.

MySQL에서 하던 감각을 그대로 쓰면 되나요

엔진이 다르므로 그대로는 안 된다. 다만 접근 방식은 같다 — 재기 전에 정하지 않는다. 잠기는 범위든 부푼 정도든, 돌리기 전에 숫자를 보는 쪽이 싸게 먹힌다.

확인한 것과 확인하지 못한 것

이 글의 동작은 PostgreSQL 공식 문서에서 확인했다. 재구성이 필요한 네 가지 경우, 부분적으로 빈 페이지가 남는 조건, REINDEX의 ACCESS EXCLUSIVE 락과 CONCURRENTLY의 차이, _ccnew·_ccold 접미어와 처리법, CONCURRENTLY의 제약, pgstatindex의 출력 컬럼, pgstattuple이 전체 스캔이라는 점이 그렇다.

확인하지 못한 것은 "밀도가 얼마 아래면 재구성한다"는 기준값이다. 문서가 숫자를 주지 않는다. 그래서 이 글에도 적지 않았다. 대신 갓 만든 인덱스와 비교하라고 썼다 — 테이블 구조와 채움 비율에 따라 기준이 달라지므로, 남의 숫자보다 자기 인덱스의 기준선이 정확하다.