[PostgreSQL] CREATE INDEX CONCURRENTLY — 운영 중 테이블에 인덱스를 무중단으로 건다

설치·접속: PostgreSQL 설치와 접속

부제: 실서비스 테이블에 인덱스를 걸어야 하는데, 일반 CREATE INDEX가 쓰기를 통째로 막아 장애가 날 때

-- (일반) 테이블에 ShareLock → INSERT/UPDATE/DELETE 대기
BEGIN;
CREATE INDEX idx_lock_demo ON m5_orders(product_id);
SELECT mode, granted FROM pg_locks l JOIN pg_class c ON c.oid=l.relation
 WHERE c.relname='m5_orders' AND locktype='relation';
ROLLBACK;

-- (무중단) 트랜잭션 밖에서, 약한 잠금으로
CREATE INDEX CONCURRENTLY idx_cc_pid ON m5_orders(product_id);

-- 일반 CREATE INDEX 가 잡는 잠금:
      mode       | granted
-----------------+---------
 ShareLock       | t         ← RowExclusiveLock(=INSERT/UPDATE/DELETE)와 충돌 → 쓰기 전면 대기

-- CONCURRENTLY 빌드 중 다른 세션에서 확인:
 ShareUpdateExclusiveLock | t   ← 쓰기 잠금과 충돌 안 함
INSERT 0 1
 write succeeded during CONCURRENTLY build   ← 빌드 도중에도 INSERT 성공

일반 CREATE INDEX는 테이블에 ShareLock을 건다. 이 잠금은 쓰기가 필요로 하는 RowExclusiveLock과 충돌하므로, 인덱스가 다 만들어질 때까지 그 테이블의 모든 INSERT/UPDATE/DELETE가 줄줄이 멈춘다 — 2백만 행이면 수 초, 큰 테이블이면 수 분간 서비스 정지다. CONCURRENTLY는 대신 ShareUpdateExclusiveLock만 잡는다. 이건 쓰기 잠금과 충돌하지 않아서, 위 출력처럼 인덱스를 만드는 도중에도 다른 세션의 INSERT가 그대로 성공한다. 대가는 셋이다 — (1) 테이블을 두 번 스캔하므로 느리다(같은 인덱스가 3초 vs 일반은 더 빠름), (2) 트랜잭션 블록 안에서 못 쓴다(그래서 BEGIN 없이 단독 실행), (3) 중간에 실패하면 INVALID 상태의 껍데기 인덱스가 남는다. 운영 원칙은 하나 — 실서비스 테이블 인덱스는 무조건 CONCURRENTLY. 시간이 더 걸려도 쓰기를 안 멈추는 게 압도적으로 중요하다.

이렇게도 쓴다

빌드가 실패해 남은 INVALID 인덱스를 찾아 정리한다. (조합: pg_index.indisvalid)

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
-- 나온 것을 DROP INDEX CONCURRENTLY 로 제거 후 재시도

 

지울 때도 잠금 없이 동시 삭제한다. (조합: DROP INDEX CONCURRENTLY)

DROP INDEX CONCURRENTLY idx_cc_pid;

 

기존 인덱스를 잠금 없이 새 것으로 교체(재구축)한다. (조합: REINDEX CONCURRENTLY)

REINDEX INDEX CONCURRENTLY idx_m5_us;

 

빌드 진행률을 실시간으로 지켜본다. (조합: pg_stat_progress_create_index)

SELECT phase, blocks_done, blocks_total FROM pg_stat_progress_create_index;

 

빌드 도중 실제로 쓰기가 되는지 다른 세션에서 확인한다. (조합: 동시 INSERT)

-- 빌드가 도는 동안
INSERT INTO m5_orders(id,user_id,product_id,status,amount,created_at) VALUES (...);  -- 성공

 

어떤 세션이 무슨 잠금을 잡고 있는지 들여다본다. (조합: pg_locks)

SELECT mode, granted FROM pg_locks WHERE relation='m5_orders'::regclass;