· 개발

PostgreSQL CREATE INDEX CONCURRENTLY: 쓰기를 막지 않는 대신 치르는 비용

CREATE INDEX CONCURRENTLY가 잡는 락, 두 번의 테이블 스캔과 트랜잭션 대기, 실패 후 남는 INVALID 인덱스 처리, 트랜잭션 블록과 파티션 테이블 제약을 PostgreSQL 18 문서 기준으로 정리합니다.

postgresqlindexlockingdatabasemigration

운영 중인 PostgreSQL 테이블에 인덱스를 추가할 때 흔히 CONCURRENTLY를 붙입니다. 이 옵션은 쓰기 차단을 없애 주지만, 그 대가로 작업 시간이 길어지고 다른 트랜잭션을 기다려야 하며 실패하면 정리할 것이 남습니다. 이 글은 PostgreSQL 18 문서(2026-10-13 기준 18.6)를 바탕으로 그 동작을 정리합니다.

일반 CREATE INDEX와 무엇이 다른가

일반 CREATE INDEX는 테이블에 SHARE 락을 잡습니다. 이 락은 ROW EXCLUSIVE와 충돌하므로 읽기는 계속되지만 INSERT, UPDATE, DELETE는 인덱스 생성이 끝날 때까지 기다립니다. 테이블은 한 번만 스캔합니다.

CREATE INDEX CONCURRENTLY는 SHARE UPDATE EXCLUSIVE 락을 잡습니다. 이 락은 ROW EXCLUSIVE와 충돌하지 않으므로 쓰기가 계속 진행됩니다. 대신 문서에 따르면 다음 비용이 따릅니다.

  • 테이블을 두 번 스캔합니다.
  • 인덱스를 수정하거나 사용할 수 있는 기존 트랜잭션이 모두 끝나기를 기다립니다.
  • 그래서 총 작업량이 많고 완료까지 훨씬 오래 걸리며, 추가 CPU와 I/O 부하가 다른 작업을 느리게 할 수 있습니다.

SHARE UPDATE EXCLUSIVE는 자기 자신과도 충돌합니다. 그래서 한 테이블에서 동시에 실행할 수 있는 concurrent 인덱스 빌드는 하나뿐입니다. 이 락은 VACUUM(FULL 제외), ANALYZE도 잡는 락이므로, 락 충돌표만 보면 같은 테이블의 수동 VACUUM이나 ANALYZE와도 서로 기다리게 됩니다(충돌표에서 도출한 추론입니다). 어느 방식이든 인덱스를 만드는 동안 테이블 스키마 변경은 허용되지 않습니다.

내부적으로 일어나는 일

문서가 설명하는 순서는 이렇습니다.

  1. 한 트랜잭션에서 인덱스를 “invalid” 상태로 시스템 카탈로그에 등록합니다.
  2. 이어지는 두 트랜잭션에서 테이블을 각각 한 번씩 스캔합니다. 각 스캔 전에는 해당 테이블을 수정한 기존 트랜잭션이 끝나기를 기다립니다.
  3. 두 번째 스캔 뒤에는 그 스캔보다 먼저 만들어진 스냅샷을 가진 트랜잭션이 끝나기를 기다립니다.
  4. 마지막으로 인덱스를 “valid”로 표시하고 명령이 끝납니다.

명령이 끝나도 바로 쿼리에 쓰인다는 보장은 없습니다. 최악의 경우 인덱스 빌드 시작보다 오래된 트랜잭션이 남아 있는 동안에는 사용되지 않습니다.

병렬 인덱스 빌드는 CONCURRENTLY에서도 가능하지만, 병렬로 수행되는 것은 첫 번째 테이블 스캔뿐입니다.

진행 상황 보기: pg_stat_progress_create_index

CREATE INDEX나 REINDEX가 실행되는 동안 pg_stat_progress_create_index 뷰에 백엔드별로 한 행이 나타납니다. phase 컬럼은 위 순서와 대응합니다.

phase 의미
waiting for writers before build 빌드 전, 쓰기 락을 가진 트랜잭션 종료 대기
building index 첫 번째 스캔과 인덱스 생성
waiting for writers before validation 검증 전, 쓰기 락을 가진 트랜잭션 종료 대기
index validation: scanning index / sorting tuples / scanning table 두 번째 단계, 빌드 중 들어온 변경을 검증
waiting for old snapshots 오래된 스냅샷을 가진 트랜잭션 종료 대기

대기 단계에서는 lockers_total, lockers_done, current_locker_pid가 채워집니다. 아래 쿼리는 지금 무엇을 기다리는지 pg_stat_activity와 묶어서 봅니다.

SELECT p.pid,
       p.phase,
       p.lockers_done,
       p.lockers_total,
       p.current_locker_pid,
       a.state,
       a.xact_start,
       left(a.query, 60) AS locker_query
FROM pg_stat_progress_create_index AS p
LEFT JOIN pg_stat_activity AS a
       ON a.pid = p.current_locker_pid;

current_locker_pid가 가리키는 세션의 state가 idle in transaction이고 xact_start가 오래전이라면, 아무 쿼리도 실행하지 않는 열린 트랜잭션이 인덱스 빌드를 붙잡고 있을 가능성이 큽니다. 이는 문서의 대기 조건과 pg_stat_activity 컬럼 정의를 조합한 해석이며, 해당 세션을 끝낼지는 애플리케이션 영향을 보고 판단해야 합니다.

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

스캔 중 교착 상태나 유니크 위반 같은 문제가 생기면 명령은 실패하지만 “invalid” 인덱스가 남습니다. 이 인덱스는 불완전할 수 있어 쿼리에는 쓰이지 않지만, INSERT나 이 인덱스에 영향을 주는 UPDATE가 일어날 때마다 계속 갱신해야 하므로 그 비용은 그대로 듭니다. psql의 \d에서는 INVALID로 표시됩니다.

카탈로그에서 찾으려면 pg_index.indisvalid를 봅니다. 이 값이 false면 쿼리에는 안전하게 쓸 수 없지만 INSERT/UPDATE로는 계속 수정되어야 하는 인덱스라는 뜻입니다.

SELECT i.indexrelid::regclass AS index_name,
       i.indrelid::regclass   AS table_name
FROM pg_index AS i
WHERE NOT i.indisvalid;

문서가 권하는 복구는 인덱스를 지우고 CREATE INDEX CONCURRENTLY를 다시 시도하는 것입니다. REINDEX INDEX CONCURRENTLY로 다시 만드는 방법도 있습니다.

-- 예시 이름입니다. 실행해 보지 않은 예제입니다.
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;
CREATE INDEX CONCURRENTLY orders_customer_id_idx
    ON orders (customer_id);

DROP INDEX CONCURRENTLY는 테이블에 ACCESS EXCLUSIVE 락을 잡는 대신 충돌하는 트랜잭션이 끝나기를 기다립니다. 다만 인덱스 이름은 하나만 줄 수 있고 CASCADE를 쓸 수 없으며, 파티션 테이블의 인덱스에는 쓸 수 없습니다.

REINDEX CONCURRENTLY가 실패했을 때는 이름 접미사를 확인합니다. _ccnew로 끝나는 invalid 인덱스는 새로 만들던 임시 인덱스이므로 지우고 다시 시도합니다. _ccold로 끝나면 재빌드는 성공했고 기존 인덱스를 지우지 못한 것이므로 그 인덱스만 지우면 됩니다. 접미사 뒤에 _ccnew1처럼 숫자가 붙을 수 있습니다.

재시도 스크립트의 IF NOT EXISTS 함정

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx ...는 같은 이름의 relation이 이미 있으면 알림만 내고 넘어갑니다. 문서는 기존 인덱스가 만들려던 인덱스와 같다는 보장이 없다고 명시합니다. 그래서 이전 시도가 남긴 invalid 인덱스가 있으면, 재시도가 성공한 것처럼 보이는데 실제로는 쿼리에 쓰이지 않는 인덱스만 남을 수 있습니다(문서 동작에서 도출한 추론). 재시도 전에 위 indisvalid 쿼리로 확인하는 편이 안전합니다.

유니크 인덱스에서 주의할 점

유니크 인덱스를 concurrent로 만들면 두 번째 스캔이 시작되는 시점부터 다른 트랜잭션에 대해 유니크 제약이 이미 적용됩니다. 그래서 인덱스가 쓸 수 있는 상태가 되기 전에, 또는 빌드가 결국 실패하는 경우에도 다른 쿼리에서 제약 위반 오류가 날 수 있습니다. 두 번째 스캔에서 실패하면 남은 invalid 인덱스가 이후에도 유니크 제약을 계속 강제합니다. 표현식 인덱스나 부분 인덱스에서 식 평가 오류가 날 때도 비슷한 일이 생길 수 있습니다.

pg_index.indisvalid 설명도 같은 점을 말합니다. invalid 상태의 유니크 인덱스는 유니크 속성도 참이라고 보장되지 않습니다.

실행 위치와 대상의 제약

  • 트랜잭션 블록 안에서는 실행할 수 없습니다. 일반 CREATE INDEX는 BEGIN ... COMMIT 안에서 실행할 수 있지만 CONCURRENTLY는 안 됩니다. 마이그레이션 도구가 각 마이그레이션을 트랜잭션으로 감싸는 방식이라면 이 문장은 별도로 다뤄야 합니다(도구별 설정은 이 글의 범위 밖입니다). DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY도 같은 제약이 있습니다.
  • 파티션 테이블에는 직접 쓸 수 없습니다. 문서가 제시하는 우회 방법은 각 파티션에 인덱스를 concurrent로 만든 뒤, 마지막에 파티션 테이블 인덱스를 non-concurrent로 만드는 것입니다. 이때 파티션 테이블 인덱스 생성은 메타데이터만 바꾸는 작업이 되어 쓰기가 막히는 시간이 줄어듭니다.
  • 임시 테이블에서는 항상 non-concurrent로 동작합니다. 다른 세션이 접근할 수 없으므로 일반 방식이 더 저렴하다는 이유입니다.
-- 파티션 테이블 예시. 실행해 보지 않은 예제입니다.
CREATE INDEX CONCURRENTLY events_2026_09_created_at_idx
    ON events_2026_09 (created_at);
CREATE INDEX CONCURRENTLY events_2026_10_created_at_idx
    ON events_2026_10 (created_at);
-- 모든 파티션에 같은 인덱스가 생긴 뒤 부모 인덱스를 만듭니다.
CREATE INDEX events_created_at_idx
    ON events (created_at);

버전 참고: 14.0-14.3

PostgreSQL 14.4(2022-06-16 릴리스) 노트에 따르면, v14에서 추가된 최적화 때문에 CREATE INDEX CONCURRENTLY와 REINDEX CONCURRENTLY가 빌드 중 갱신된 행을 인덱스에서 빠뜨리는 경우가 있었고 14.4에서 그 최적화를 되돌렸습니다. 릴리스 노트는 14.4를 설치하기 전의 PostgreSQL 14, 즉 14.0-14.3에서 CONCURRENTLY로 만든 인덱스를 업데이트 후 다시 만들라고 권합니다.

2026-10-13에 확인한 공식 지원 버전 표에서 지원 중인 메이저 버전은 14부터 18까지이고, 각 최신 마이너는 18.6, 17.11, 16.15, 15.19, 14.24입니다. 이 글에서 인용한 동작은 PostgreSQL 18 문서 기준이며, 14 문서의 “Building Indexes Concurrently” 절에서도 두 번 스캔, invalid 인덱스, 테이블당 하나의 concurrent 빌드, 트랜잭션 블록 제약, 파티션 테이블 미지원이 같은 내용으로 적혀 있습니다. 15-17 문서는 따로 열어 보지 않았습니다.

관련 글: HOT 업데이트는 테이블 인덱스(BRIN 같은 요약 인덱스 제외)가 참조하는 컬럼을 바꾸지 않을 때만 가능합니다. 그래서 새 인덱스를 추가하면 그 컬럼을 바꾸는 UPDATE는 HOT 대상에서 빠집니다. 조건은 PostgreSQL HOT 업데이트에서 정리했습니다.

이 글의 SQL 예제는 설명용으로 작성한 것이며 실제 데이터베이스에서 실행하지 않았습니다. 시각 자료는 넣지 않았습니다. 단계 순서와 phase 표만으로 흐름을 설명할 수 있다고 판단했습니다.

출처

아래 링크와 내용은 2026-10-13에 확인했습니다.

블로그로