큰 테이블에 인덱스 추가하다 쓰기를 막은 뒤 배운 3원칙

로컬에서 0.2초 만에 끝나던 마이그레이션이 운영 DB에서는 왜 다른가. 이걸 몸으로 배운 적이 있어요. 플랜비(plan-b)의 출전 기록 테이블에 조회 인덱스를 하나 붙이려고 했는데, 로컬 더미 데이터 500건에서는 눈 깜빡할 새에 끝났거든요. 데이터가 쌓인 쪽에서는 그 시간이 길어지고, 그동안 해당 테이블로 들어오는 INSERT가 줄을 섭니다.

왜 멈추는가: 락 레벨을 먼저 보세요

CREATE INDEX는 대상 테이블에 SHARE 락을 겁니다. 읽기는 통과하지만 쓰기는 전부 대기예요. 인덱스를 만드는 동안 테이블을 통째로 스캔해야 하니, 행이 많을수록 대기 시간도 같이 늘어납니다.

직접 재현해 보면 감이 확 옵니다. 터미널 두 개를 띄우고 한쪽에서 인덱스를 만드는 동안 다른 쪽에서 INSERT를 날려 보세요.

-- 터미널 A
CREATE INDEX idx_logs_created ON game_logs (created_at);
 
-- 터미널 B (A가 도는 동안)
INSERT INTO game_logs (member_id, created_at) VALUES (1, now());
-- 커서만 깜빡이며 대기

락 상태는 이렇게 확인했어요.

SELECT pid, mode, granted, relation::regclass
FROM pg_locks WHERE NOT granted;

granted = false 행이 보이면 그게 지금 멈춰 있는 쿼리입니다.

원칙 1: 인덱스는 CONCURRENTLY로

CONCURRENTLY 옵션을 붙이면 쓰기를 막지 않습니다. 대신 테이블을 두 번 스캔하기 때문에 전체 시간은 더 걸려요. 쓰기가 살아 있는 쪽을 택하는 거죠.

CREATE INDEX CONCURRENTLY idx_logs_created ON game_logs (created_at);

주의할 점 두 가지가 있어요. 트랜잭션 블록 안에서 실행할 수 없습니다. 마이그레이션 도구가 자동으로 BEGIN으로 감싸는 경우가 많으니, 해당 마이그레이션만 트랜잭션을 끄는 설정을 찾아 두세요. 그리고 중간에 실패하면 INVALID 상태의 인덱스가 남습니다. 돌려 보니 이 상태 확인이 제일 자주 빠뜨리는 단계였어요.

SELECT indexrelid::regclass, indisvalid
FROM pg_index WHERE NOT indisvalid;
-- 나오면 DROP INDEX 후 재시도

원칙 2: 제약 추가는 NOT VALID로 쪼개기

ALTER TABLE ... ADD CONSTRAINT도 같은 함정입니다. 기존 행 전부를 검사하느라 그동안 테이블이 잠겨요. 이건 두 단계로 나눌 수 있습니다.

-- 1단계: 새로 들어오는 행만 검사 (즉시 끝남)
ALTER TABLE game_logs
  ADD CONSTRAINT fk_member FOREIGN KEY (member_id)
  REFERENCES members(id) NOT VALID;
 
-- 2단계: 기존 행 검증 (쓰기를 막지 않음)
ALTER TABLE game_logs VALIDATE CONSTRAINT fk_member;

1단계는 메타데이터만 바꾸니 거의 즉시 끝나고, 2단계는 약한 락으로 돌아서 쓰기가 살아 있어요. 테스트해 보면 두 단계로 나눈 쪽이 총 소요 시간은 비슷한데 서비스 중단이 사라집니다.

원칙 3: backfill은 긴 트랜잭션 금지

컬럼을 추가하고 값을 채우는 backfill을 UPDATE 한 방으로 처리하고 싶어지는데, 이게 세 번째 함정이에요. 긴 트랜잭션이 열려 있으면 autovacuum이 그동안 생긴 죽은 튜플을 정리하지 못합니다. 테이블이 계속 부풀어요.

배치로 쪼개는 게 정답입니다.

UPDATE game_logs SET season_id = 3
WHERE id IN (
  SELECT id FROM game_logs
  WHERE season_id IS NULL LIMIT 1000
);

1000건씩 끊어서 돌리고 사이사이 잠깐 쉬어 주면, autovacuum이 틈틈이 일할 수 있어요. 진행률은 WHERE season_id IS NULL 카운트로 확인합니다.

적용 전 체크리스트

마이그레이션 스크립트를 쓰기 전에 세 가지만 물어보세요.

1. 이 구문이 어떤 락을 얼마나 오래 잡나
2. 실패했을 때 되돌릴 수 있나
3. 대상 테이블이 지금 몇 행인가

3번을 모르면 나머지 판단이 전부 추측이 됩니다. SELECT reltuples FROM pg_class WHERE relname = 'table_name'으로 대략치를 먼저 잡아 두세요. 정확한 카운트가 아니라 통계 기반 추정치라 큰 테이블에서도 즉시 나와요.

사이드 프로젝트라 사용자가 적으니 괜찮다고 넘기기 쉬운데, 쓰기가 막히는 몇 분 동안 앱 화면에서는 저장 버튼이 그냥 안 눌립니다. 플랜씨(plan-c) 같은 계산기는 DB 쓰기가 거의 없어 영향이 적지만, 회비와 출전 기록을 계속 쓰는 플랜비는 사정이 달라요. 서비스 성격에 따라 같은 마이그레이션이 다른 무게를 가집니다.

세 원칙을 스크립트 템플릿에 박아 두고 나니 마이그레이션 전에 고민할 게 줄었어요. 인덱스는 CONCURRENTLY, 제약은 두 단계, backfill은 1000건 배치. 이 셋만 지켜도 운영 중 테이블 변경이 훨씬 편해집니다.