CREATE INDEX 하나에 주문이 몇 분 멈췄다

운영 중에 인덱스를 하나 추가했다. 천만 행 테이블이라 몇 분이 걸렸고, 그동안 그 테이블에 쓰기가 전부 막혔다. 주문은 한 건도 안 들어왔다.

읽기는 됐다. 그래서 모니터링 대시보드는 멀쩡해 보였고, 헬스체크도 통과했다. 장애를 안 건 CS 문의가 들어오고 나서였다.

스테이징에서는 이게 안 보인다. 데이터가 몇만 건이라 인덱스가 1초 만에 만들어지니까. 락은 똑같이 걸렸다. 1초짜리 락을 아무도 못 느꼈을 뿐이다.

그러니 마이그레이션 리뷰도 그냥 통과한다. 검토한 사람도 스테이징에서 돌려 봤고, 문제가 없었다.

SELECT 는 되는데 INSERT 만 막히는 이유

일반 CREATE INDEX 는 테이블에 SHARE 락을 건다. 이 락은 읽기끼리는 부딪치지 않지만 쓰기와는 부딪친다. 그래서 SELECT 는 지나가고 INSERT·UPDATE·DELETE 는 전부 막힌다. 인덱스가 다 만들어질 때까지.

막는 데는 이유가 있다. 인덱스를 만드는 동안 테이블이 바뀌면, 다 만든 인덱스가 실제 데이터와 어긋난다. 한 번 훑어서 만드는 방식이라면 스냅샷이 고정돼 있어야 한다.

CONCURRENTLY 는 같은 문제를 다르게 푼다. 테이블을 두 번 훑는다. 첫 번째에 인덱스를 만들고, 그 사이에 바뀐 것들을 두 번째에 따라잡는다. 그동안 쓰기는 계속 받아 준다. 대신 두 번 읽으니 시간이 더 걸리고, 두 번째 훑기 전에 그 시점에 열려 있던 트랜잭션이 전부 끝나길 기다려야 한다.

이 마지막 조건이 실무에서 자주 발목을 잡는다. 긴 트랜잭션 하나가 열려 있으면 CONCURRENTLY 가 그 앞에서 멈춰 선다. 락을 잡은 게 아니라 그냥 기다리는 거라, 겉으로는 아무 일도 없는 것처럼 보인다. 이게 꽤 얄밉다.

쓰기를 막지 않고 만드는 순서

쓰기를 막지 않으려면 이렇게 간다.

코드

✕ CREATE INDEX idx_a ON t(col);
✕ -- 완료까지 쓰기 전면 차단
✕ -- 천만 행이면 몇 분

✓ CREATE INDEX CONCURRENTLY idx_a ON t(col);
· 테이블을 두 번 훑어 느리지만 쓰기를 막지 않는다
· 트랜잭션 블록 안에서는 못 쓴다

· 만든 뒤에는 반드시 유효성을 확인한다
✓ SELECT indisvalid FROM pg_index
✓ WHERE indexrelid = 'idx_a'::regclass;

· 무효면 지우고 다시 만든다 — 지울 때도 CONCURRENTLY
✓ DROP INDEX CONCURRENTLY idx_a;

✕ 문제 지점 · ✓ 해법 · 그대로 돌리기 전에 자기 환경에 맞게 고친다.

실패하면 INVALID 인덱스가 남는다

CONCURRENTLY 에도 나름의 함정이 있다. 도중에 실패하면 INVALID 상태의 인덱스가 남는다.

이 인덱스는 최악의 조합이다. 쿼리 계획에는 안 쓰이면서 INSERT·UPDATE 때마다 갱신은 된다. 읽기 이득은 0 인데 쓰기 비용은 그대로다. 이름까지 차지하고 있어서 같은 이름으로 다시 만들려는 시도도 막는다.

더 나쁜 건 아무도 모른다는 점이다. 마이그레이션이 실패하면 로그에 에러는 찍힌다. 그런데 배포 파이프라인이 재시도로 넘어가거나, 사람이 "나중에 보자" 하고 넘기면 그대로 남는다. 그러다 몇 달 뒤 "INSERT 가 왜 느리지"로 돌아온다.

유니크 인덱스라면 실패하는 길이 하나 더 있다. CONCURRENTLY 로 유니크 인덱스를 만드는 중에 중복 데이터가 들어오면 실패하는데, 이때도 INVALID 인덱스가 남는다. 중복부터 정리하고 만들어야 한다.

시간도 따져 봐야 한다. 두 번 훑으니 대략 두 배 이상 걸린다. 천만 행에 몇 분이던 게 십여 분이 될 수 있다. 그 사이 autovacuum 이 같은 테이블에서 돌면 I/O 를 나눠 쓰느라 더 느려진다. 그래서 큰 테이블이라면 트래픽이 낮은 시간대에 돌리는 게 여전히 유리하다.

MySQL 은 기본 동작부터 다르다. InnoDB 의 온라인 DDL 이 대부분의 인덱스 생성에서 동시 DML 을 허용한다. ALGORITHM=INPLACE, LOCK=NONE 을 적어 두면 그게 안 되는 경우엔 아예 실패한다. 의도하지 않은 전면 차단을 미리 막을 수 있는 셈이다.

마이그레이션 규칙으로 박아 둘 것

운영에서 인덱스는 CONCURRENTLY 로만 만든다. 예외는 없다. "이 테이블은 작으니까" 하던 테이블도 반년 뒤엔 안 작다.
만든 뒤 유효성 확인은 꼭 거쳐야 한다. 마이그레이션 스크립트에 확인 단계를 넣고, 무효면 실패로 처리하게 하자. 사람 기억에 맡기면 남는다.
무효 인덱스를 주기적으로 훑는 점검이 있으면 좋다. 아래 쿼리를 모니터링이나 주간 리포트에 넣으면 된다.
마이그레이션 도구가 모든 걸 트랜잭션으로 감싸는 타입이면 그 설정을 꺼야 한다. CONCURRENTLY 는 트랜잭션 블록 안에서 못 쓴다. Rails 는 disable_ddl_transaction!, Django 는 atomic = False 다.
시작 전에 긴 트랜잭션이 있는지 본다. CONCURRENTLY 는 두 번째 단계에서 기존 트랜잭션을 기다린다. 유휴 트랜잭션 하나만 있어도 그 앞에서 멈춰 선다.
유니크 인덱스라면 중복부터 걷어낸다. 중간에 중복이 들어와 실패하면 INVALID 로 남는다.

남아 있는 무효 인덱스부터 찾기

무효 인덱스와 진행 상황은 이렇게 본다.

코드

· 무효 인덱스 — 주기적으로 훑어야 한다
SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
✕ WHERE NOT i.indisvalid;

· 지금 인덱스 생성이 어디까지 갔나 (PG12+)
SELECT phase, blocks_done, blocks_total,
       tuples_done, tuples_total
  FROM pg_stat_progress_create_index;

· 시작 전 게이트 — 긴 트랜잭션이 있으면 미룬다
✓ SELECT pid, now() - xact_start AS age, query
✓ FROM pg_stat_activity
✓ WHERE xact_start < now() - interval '30 seconds';

핵심 한 줄운영에서 인덱스는 CONCURRENTLY 로만. 그리고 만든 뒤 유효성을 확인하라.