인덱스는 왜 다시 만들어야 하나
PostgreSQL의 B-tree 인덱스는 대량의 UPDATE/DELETE가 반복되면 페이지 분할과 죽은 튜플로 인해 실제 데이터보다 훨씬 커지는 인덱스 블로트(bloat)가 발생한다. autovacuum이 죽은 튜플을 정리해도 이미 늘어난 인덱스 페이지는 자동으로 축소되지 않는다. 그 결과 인덱스 스캔 시 읽어야 할 페이지가 늘어 캐시 효율이 떨어지고, 디스크 사용량도 계속 증가한다.
또 다른 상황은 인덱스 정의 변경이다. 오래된 인덱스 정렬 방식이나 collation을 바꿔야 하거나, 손상된 인덱스를 복구해야 할 때도 재구축이 필요하다.
일반 REINDEX의 문제점
전통적인 REINDEX는 대상 인덱스에 대해 ACCESS EXCLUSIVE 락을 잡는다. 즉 재구축이 끝날 때까지 해당 테이블에 대한 읽기와 쓰기가 모두 차단된다. 수천만 건 규모의 테이블에서는 이 작업이 수 분에서 수십 분까지 걸릴 수 있어, 그 시간 동안 애플리케이션 쿼리가 전부 대기하게 된다.
-- 이 방식은 테이블 전체를 잠근다 (운영 중 위험)
REINDEX INDEX idx_orders_created_at;
운영 환경에서 이런 락은 사실상 짧은 장애와 같다. 그래서 PostgreSQL 12부터 도입된 REINDEX ... CONCURRENTLY가 필요하다.
REINDEX CONCURRENTLY 동작 방식
CONCURRENTLY 옵션은 기존 인덱스를 잠그는 대신, 내부적으로 새 인덱스를 별도로 만든 뒤 원본과 교체한다. 진행 중에도 테이블은 읽기·쓰기가 가능하다. 대략 다음 단계로 진행된다.
- 새 임시 인덱스를 생성하고 기존 데이터를 스캔해 채운다.
- 빌드 중 발생한 변경분을 반영하기 위해 테이블을 한 번 더 스캔한다.
- 진행 중인 트랜잭션이 끝나길 기다린 뒤, 짧은 락으로 새 인덱스와 기존 인덱스를 교체한다.
-- 무중단 재구축: 테이블 읽기/쓰기 차단 없음
REINDEX INDEX CONCURRENTLY idx_orders_created_at;
-- 테이블의 모든 인덱스를 한 번에
REINDEX TABLE CONCURRENTLY orders;
일반 REINDEX와의 비교
| 항목 | REINDEX | REINDEX CONCURRENTLY |
|---|---|---|
| 락 수준 | ACCESS EXCLUSIVE | 짧은 교체 락 외 없음 |
| 운영 중 쿼리 | 차단됨 | 정상 처리 |
| 소요 시간 | 상대적으로 짧음 | 테이블 2회 스캔으로 더 김 |
| 디스크 | 추가 공간 적음 | 일시적으로 인덱스 2배 |
| 트랜잭션 내 실행 | 가능 | 불가 |
실무 적용 스크립트
블로트가 심한 인덱스만 골라 재구축하는 것이 효율적이다. 아래는 재구축 후 크기 변화를 로깅하는 예시다.
import psycopg2
TARGETS = ["idx_orders_created_at", "idx_orders_user_id"]
conn = psycopg2.connect("dbname=app host=localhost")
conn.autocommit = True # CONCURRENTLY는 트랜잭션 블록 밖에서만 실행
cur = conn.cursor()
for idx in TARGETS:
cur.execute("SELECT pg_size_pretty(pg_relation_size(%s))", (idx,))
before = cur.fetchone()[0]
cur.execute(f"REINDEX INDEX CONCURRENTLY {idx}")
cur.execute("SELECT pg_size_pretty(pg_relation_size(%s))", (idx,))
after = cur.fetchone()[0]
print(f"{idx}: {before} -> {after}")
실패 시 남는 무효 인덱스 처리
CONCURRENTLY 작업이 중간에 실패하거나 취소되면, 이름 뒤에 _ccnew가 붙은 미완성 인덱스가 INVALID 상태로 남을 수 있다. 이 인덱스는 쿼리에 사용되지 않으면서 공간만 차지하므로 반드시 정리해야 한다.
-- 무효 인덱스 조회
SELECT n.nspname, c.relname
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indisvalid = false;
-- 남은 인덱스 제거 (이것도 무중단)
DROP INDEX CONCURRENTLY idx_orders_created_at_ccnew;
주의점과 운영 팁
- 트랜잭션 밖 실행: CONCURRENTLY는 트랜잭션 블록 안에서 실행할 수 없다. 마이그레이션 도구가 자동으로 트랜잭션을 감싸는 경우 별도 설정이 필요하다.
- 디스크 여유: 재구축 중에는 기존 인덱스와 새 인덱스가 공존하므로, 대상 인덱스 크기만큼의 추가 공간을 미리 확보해야 한다.
- 장기 트랜잭션: 오래 열려 있는 트랜잭션이 있으면 교체 단계에서 대기가 길어진다. 재구축 전
pg_stat_activity로 장기 트랜잭션을 확인하는 것이 좋다. - PK·UNIQUE 제약: 제약에 연결된 인덱스도
REINDEX INDEX CONCURRENTLY로 재구축 가능하다. - 부하 시간대 선택: 무중단이지만 테이블을 두 번 스캔하므로 I/O 부하가 있다. 트래픽이 낮은 시간대에 실행하는 편이 안전하다.