운영 중인 테이블에 인덱스를 무중단으로 추가하기

평범한 CREATE INDEX가 운영 환경에서 위험한 이유

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);

이 한 줄이 로컬이나 스테이징에서는 문제없이 끝나지만, 트래픽이 있는 운영 테이블에서 돌리면 사고로 이어집니다. 기본 CREATE INDEX는 인덱스를 만드는 동안 테이블에 SHARE 락을 겁니다. SELECT는 계속 되지만, 그 테이블에 대한 INSERT/UPDATE/DELETE는 인덱스 생성이 끝날 때까지 전부 대기 상태로 막힙니다. 천만 행짜리 테이블이면 이 시간이 수 분에서 수십 분까지 갈 수 있고, 그동안 애플리케이션의 쓰기 요청이 전부 쌓이다가 타임아웃으로 죽습니다.

CONCURRENTLY는 락 대신 시간을 쓴다

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at);

CONCURRENTLY를 붙이면 테이블을 막는 강한 락 없이 인덱스를 만듭니다. 대신 테이블을 두 번 스캔합니다. 첫 번째 스캔으로 현재 존재하는 행을 인덱싱하고, 그 사이 들어온 변경분을 따라잡기 위해 두 번째 스캔을 한 번 더 돕니다. 이 두 번 스캔 방식 때문에 일반 CREATE INDEX보다 시간이 몇 배 더 걸리고, 트랜잭션 하나로 묶이지 않아서(CONCURRENTLY는 여러 개의 짧은 트랜잭션으로 나뉩니다) 도중에 실패해도 자동으로 롤백되지 않는다는 대가가 따릅니다.

sequenceDiagram
  participant App as 애플리케이션 (쓰기 계속)
  participant PG as PostgreSQL
  App->>PG: CREATE INDEX CONCURRENTLY 시작
  PG->>PG: 1차 스캔 - 기존 행 인덱싱
  App->>PG: 그 사이에도 INSERT/UPDATE 계속 허용
  PG->>PG: 2차 스캔 - 누락된 변경분 반영
  PG->>App: 인덱스 valid 상태로 전환

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

CONCURRENTLY로 인덱스를 만들다가 중간에 실패하면(예: UNIQUE 제약을 위반하는 중복 값이 뒤늦게 발견되거나, 세션이 끊기거나) 자동 롤백이 안 되고 INVALID 상태의 인덱스가 그대로 남습니다.

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indisvalid = false;

이 INVALID 인덱스는 조회에는 절대 쓰이지 않으면서도 INSERT/UPDATE 때마다 갱신 비용만 계속 발생시킵니다. 있으나 마나 한 정도가 아니라 순수하게 손해인 상태입니다. 발견하면 지우고 원인을 해결한 뒤 다시 만드세요.

DROP INDEX CONCURRENTLY idx_orders_customer_created;

DROP INDEX도 CONCURRENTLY 없이 돌리면 똑같이 강한 락이 걸립니다. 운영 중인 테이블에서는 인덱스를 만들 때든 지울 때든 항상 CONCURRENTLY를 기본값으로 삼으세요.

트랜잭션 블록 안에서는 못 쓴다

BEGIN;
CREATE INDEX CONCURRENTLY idx_a ON orders (status);
-- ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
COMMIT;

CONCURRENTLY는 여러 개의 독립적인 트랜잭션으로 내부적으로 쪼개 실행되기 때문에, 바깥에서 또 하나의 트랜잭션으로 감쌀 수 없습니다. 마이그레이션 도구 중에는 모든 DDL을 기본적으로 하나의 트랜잭션으로 묶는 것들이 있는데(Rails의 마이그레이션이 대표적입니다), 이런 도구를 쓴다면 CONCURRENTLY를 쓰는 마이그레이션만 트랜잭션을 끄는 별도 옵션이 있는지 반드시 확인하세요. 모르고 넣었다가 마이그레이션 자체가 에러로 실패하는 경우를 자주 봤습니다.

그래도 완전히 공짜는 아니다

CONCURRENTLY가 강한 락을 피한다고 해서 부하 자체가 없어지는 건 아닙니다. 두 번 스캔하는 만큼 디스크 I/O와 CPU는 오히려 일반 방식보다 더 씁니다. 트래픽이 가장 적은 시간대를 골라 실행하고, 대형 테이블이라면 maintenance_work_mem을 세션 단위로 늘려서 스캔 시간 자체를 줄이는 것도 고려할 만합니다.

SET maintenance_work_mem = '1GB';
CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at);

이 책에서 다룬 인덱스 종류와 설계 원칙이 아무리 정확해도, 그걸 운영 환경에 적용하는 마지막 단계에서 테이블을 잠가버리면 그동안의 분석이 무의미해집니다. CONCURRENTLY는 이론과 운영 사이의 마지막 다리입니다.