주문 조회 화면의 응답이 느려져 고객별 최근 주문을 찾는 쿼리에 인덱스를 추가하려고 합니다. 변경 작업은 조회를 위한 것이지만, 대상인 orders 테이블에는 신규 주문과 배송 상태 변경도 계속 들어옵니다. 배포 계획에는 조회 쿼리의 개선 여부와 함께 인덱스를 만드는 동안 주문 쓰기가 대기할 수 있는지도 적어야 합니다.
PostgreSQL 18의 일반 테이블을 기준으로 구성한 설명용 업무 장면입니다. 일반 CREATE INDEX로 진행할 수 있는 시간대가 있는지, 쓰기를 유지해야 한다면 CONCURRENTLY를 어떤 조건으로 실행할지 살펴봅니다.
일반 생성과 동시 생성의 쓰기 잠금 차이
일반 CREATE INDEX는 테이블에 SHARE 잠금을 잡습니다. 일반 SELECT는 이 잠금과 충돌하지 않지만, INSERT·UPDATE·DELETE가 요구하는 ROW EXCLUSIVE 잠금은 충돌합니다. 먼저 시작한 쓰기 트랜잭션이 끝날 때까지 인덱스 생성이 기다릴 수 있고, 생성 작업이 잠금을 잡은 뒤에는 새 쓰기가 기다립니다. PostgreSQL 18 — 테이블 잠금
CREATE INDEX CONCURRENTLY의 SHARE UPDATE EXCLUSIVE 잠금은 일반 쓰기와 호환됩니다. 대신 테이블을 두 번 읽고, 단계에 따라 기존 쓰기 트랜잭션과 오래된 스냅샷이 정리되기를 기다립니다. 같은 테이블의 일반 VACUUM이나 ANALYZE와는 잠금이 충돌하므로 유지보수 일정도 함께 확인합니다. PostgreSQL 18 — 인덱스 동시 생성 · PostgreSQL 18 — 테이블 잠금
배포 전 트랜잭션 설정과 작업 시간 확인
쓰기 중단이 가능한 점검 시간이 있고 예상 잠금 시간을 감당할 수 있다면 일반 생성을 검토합니다. 주문 접수를 계속 받아야 한다면 동시 생성을 검토하고, 디스크 I/O와 주문 쓰기 응답시간을 작업 중에 관찰합니다. 테이블을 두 번 읽는 작업이므로 실행 시간과 부하를 같은 규모의 검증 환경에서 먼저 살펴보면 작업 창을 잡는 데 도움이 됩니다. PostgreSQL 18 — 인덱스 동시 생성
아래는 고객별 최근 주문을 조회하는 쿼리를 위한 비고유 인덱스 예시입니다. 마이그레이션 도구가 스크립트 전체를 한 트랜잭션으로 묶는다면 이 문장을 트랜잭션 블록 밖에서 실행할 수 있도록 별도 단계로 분리합니다. 같은 테이블에서 동시 생성은 한 번에 하나만 가능하며, 파티션 테이블의 부모에는 이 명령을 직접 적용할 수 없습니다. PostgreSQL 18 — 인덱스 동시 생성
CREATE INDEX CONCURRENTLY orders_customer_created_at_idx
ON public.orders (customer_id, created_at DESC);진행 뷰에서 작업과 대기 구분
대상 데이터베이스에 접속해 별도 세션에서 pg_stat_progress_create_index를 조회합니다. phase가 waiting for writers before build 또는 waiting for writers before validation이면 해당 단계의 쓰기 트랜잭션 종료를 기다리는 중입니다. waiting for old snapshots라면 검증 이후에도 이전 스냅샷을 유지하는 트랜잭션이 있는 상태입니다. 스냅샷은 트랜잭션이 볼 수 있는 데이터의 범위를 정합니다. PostgreSQL 18 — 인덱스 생성 진행 상태
SELECT pid, phase, blocks_done, blocks_total, current_locker_pid
FROM pg_stat_progress_create_index
WHERE datname = current_database()
AND relid = to_regclass('public.orders');대기 단계에서 current_locker_pid가 표시되면 해당 세션의 업무와 트랜잭션 상태를 확인합니다. 스캔 단계에서는 블록 수의 변화를 봅니다. 블록 집계는 단계마다 기준이 달라 전체 완료율로 환산하기 어렵습니다. 진행 뷰에서 행이 사라지면 실행 결과와 인덱스 상태를 따로 확인합니다. PostgreSQL 18 — 인덱스 생성 진행 상태
실패 후 남은 인덱스의 상태와 재시도
동시 생성은 완료 전에 인덱스를 시스템 카탈로그에 등록합니다. 따라서 실행이 끝났거나 실패한 뒤에도 이름이 남을 수 있습니다. indisvalid가 false이면 조회에 사용할 수 없고, 이때 indisready가 true라면 새 쓰기에서 인덱스를 갱신하는 비용은 남습니다. PostgreSQL 18 — 인덱스 동시 생성 · PostgreSQL 18 — pg_index
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indrelid = to_regclass('public.orders')
AND indexrelid = to_regclass('public.orders_customer_created_at_idx');작업이 끝났는데 무효 인덱스가 남았다면 먼저 실패 메시지와 인덱스 정의를 확인합니다. 이름이 같은 객체가 있으면 IF NOT EXISTS는 생성을 건너뛸 수 있으므로 재시도 전에 상태를 확인합니다. 위 예시처럼 제약조건에 연결되지 않은 일반 인덱스라면 원인을 정리한 뒤 DROP INDEX CONCURRENTLY로 제거하고 다시 생성하는 방법을 검토합니다. 동시 삭제도 트랜잭션 블록 밖에서 별도로 실행해야 합니다. PostgreSQL 18 — 인덱스 동시 생성 · PostgreSQL 18 — DROP INDEX
결론
변경 계획서에 허용할 쓰기 대기 시간, 마이그레이션의 트랜잭션 설정, 작업 중 확인할 진행 단계를 적어 둡니다. 완료 후에는 인덱스 유효 상태를 확인하고, 실제 조회와 같은 조건의 EXPLAIN으로 실행 계획을 비교합니다. 인덱스를 정상 생성했더라도 조회 조건에 따라 플래너가 다른 경로를 고를 수 있습니다.