PostgreSQL 16~18 CREATE INDEX CONCURRENTLY 실패: invalid 인덱스 복구 순서

PostgreSQL 16~18에서 CREATE INDEX CONCURRENTLY가 오류로 끝났다면 같은 이름으로 곧바로 다시 만들지 마세요. 먼저 pg_index.indisvalid와 진행 중인 작업을 확인하고, 실패가 확정된 invalid 인덱스라면 원인을 제거한 뒤 DROP INDEX CONCURRENTLY로 정리하고 다시 생성하는 순서가 안전합니다.

invalid 인덱스는 조회 계획에는 쓰이지 않지만 INSERT·UPDATE의 인덱스 유지 비용을 만들 수 있습니다. 특히 UNIQUE 인덱스는 실패 단계에 따라 유일성 검사를 계속할 수 있으므로, 단순히 “사용되지 않는 인덱스”라고 보고 방치하면 안 됩니다.

invalid 인덱스를 발견했을 때 먼저 구분할 것

CREATE INDEX CONCURRENTLY는 카탈로그에 invalid 상태의 인덱스를 먼저 등록하고 두 차례 테이블 스캔과 검증을 거친 뒤 valid로 바꿉니다. 따라서 indisvalid=false는 실패의 증거일 수도 있고, 아직 정상 작업이 진행 중이라는 표시일 수도 있습니다. PostgreSQL 18 공식 문서도 이 생성 단계를 명시하고 있습니다.

적용 조건·영향 범위

왜 실패한 인덱스가 남는가

일반 CREATE INDEX는 한 번의 테이블 스캔으로 만들며 쓰기를 막습니다. 반면 동시 생성은 쓰기를 계속 허용하는 대신 여러 트랜잭션과 두 번의 스캔을 사용합니다. 기존 쓰기 트랜잭션과 오래된 스냅샷이 끝나기를 기다리는 단계도 있습니다. 시간이 더 걸리고 CPU·I/O 부담도 더 크지만 운영 중 쓰기를 장시간 막는 위험을 줄이는 방식입니다.

스캔 중 데드락, UNIQUE 중복, 표현식 평가 오류, lock_timeout, 디스크 부족 같은 런타임 오류가 생기면 명령은 실패해도 이미 만든 카탈로그 항목과 인덱스 파일은 남을 수 있습니다. PostgreSQL은 완전성을 보장할 수 없는 이 인덱스를 조회 계획에서 제외합니다. 그러나 변경되는 행의 인덱스 항목을 유지하는 비용은 발생할 수 있어 원인 확인 뒤 정리가 필요합니다.

UNIQUE 동시 인덱스는 더 조심해야 합니다. 두 번째 스캔이 시작되면 다른 트랜잭션에 대한 유일성 검사가 먼저 적용될 수 있고, 그 뒤 생성이 실패해도 invalid 인덱스가 유일성 검사를 계속할 수 있다고 공식 문서가 설명합니다. 삭제 전에 이 인덱스가 제약조건을 뒷받침하는지, 중복 데이터가 실제로 있는지부터 확인해야 합니다.

최소 재현 예제로 상태를 이해하기

아래 예제는 테스트 데이터베이스에서만 실행하세요. 중복값이 있는 컬럼에 UNIQUE 인덱스를 동시 생성해 실패 상태를 관찰하는 구조입니다.

CREATE TABLE lab_order (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_no text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

INSERT INTO lab_order (external_no)
VALUES ('A-100'), ('A-100'), ('B-200');

CREATE UNIQUE INDEX CONCURRENTLY ux_lab_order_external_no
    ON lab_order (external_no);

실행 전 확인은 네 단계로 나눈다

1. 아직 생성 중인지 먼저 확인

SELECT p.pid,
       p.datname,
       p.relid::regclass AS table_name,
       p.index_relid::regclass AS index_name,
       p.command,
       p.phase,
       p.lockers_total,
       p.lockers_done,
       p.current_locker_pid,
       p.blocks_total,
       p.blocks_done
FROM pg_stat_progress_create_index AS p
WHERE p.index_relid = 'public.ux_lab_order_external_no'::regclass;

진행 중인 작업이 기다리는 세션을 찾아야 한다면 pg_locks와 pg_stat_activity로 차단 세션을 확인하는 순서를 먼저 적용하세요. 기다린다는 이유만으로 세션을 종료하면 업무 트랜잭션을 잃을 수 있습니다.

2. valid·ready·live 상태와 정의를 함께 읽기

SELECT n.nspname AS schema_name,
       t.relname AS table_name,
       i.relname AS index_name,
       x.indisvalid,
       x.indisready,
       x.indislive,
       x.indisunique,
       pg_get_indexdef(i.oid) AS index_definition,
       pg_size_pretty(pg_relation_size(i.oid)) AS index_size
FROM pg_index AS x
JOIN pg_class AS i ON i.oid = x.indexrelid
JOIN pg_class AS t ON t.oid = x.indrelid
JOIN pg_namespace AS n ON n.oid = i.relnamespace
WHERE n.nspname = 'public'
  AND i.relname = 'ux_lab_order_external_no';

indisvalid 하나만 뽑아 자동 삭제 목록을 만들지 마세요. 파티션 부모 인덱스를 ONLY로 만든 경우처럼 의도적으로 invalid인 상태도 있고, 동시 생성 중인 짧은 구간도 있습니다. 진행 뷰, 오류 로그, 객체 정의, 파티션 관계를 한 번에 판단해야 합니다.

3. 실패 원인을 제거했는지 확인

UNIQUE 인덱스였다면 중복을 먼저 찾아야 합니다. 아래 쿼리는 삭제 대상을 정하는 쿼리가 아니라 중복 그룹을 확인하는 진단입니다.

SELECT external_no,
       count(*) AS duplicate_count
FROM lab_order
GROUP BY external_no
HAVING count(*) > 1
ORDER BY duplicate_count DESC, external_no;

deadlock이나 timeout이었다면 당시 서버 로그의 SQLSTATE와 시간대를 확인하세요. 디스크 부족이었다면 테이블스페이스의 여유 공간뿐 아니라 기존 invalid 인덱스와 새 인덱스가 한동안 함께 존재할 공간도 계산해야 합니다. 표현식·부분 인덱스라면 정의에 사용한 함수가 immutable 조건을 지키는지도 확인합니다.

4. 의존성과 복구 기준을 확인

인덱스가 제약조건에 연결되어 있는지, 애플리케이션 배포가 특정 이름을 기대하는지, 모니터링이 해당 인덱스를 기준으로 하는지 확인하세요. 삭제 후 재생성이 실패하면 그동안 조회 성능을 보장할 대체 인덱스가 있는지도 봅니다. UNIQUE라면 중복 유입을 막을 별도 통제가 없는 시간 구간을 만들어서는 안 됩니다.

실패가 확정되면 동시 삭제 후 재생성한다

DROP INDEX CONCURRENTLY IF EXISTS public.ux_lab_order_external_no;

CREATE UNIQUE INDEX CONCURRENTLY ux_lab_order_external_no
    ON public.lab_order (external_no);

실행 후에는 사용 가능 상태까지 확인한다

삭제·재생성 전후 검증표

SELECT i.relname AS index_name,
       x.indisvalid,
       x.indisready,
       pg_get_indexdef(i.oid) AS index_definition
FROM pg_index AS x
JOIN pg_class AS i ON i.oid = x.indexrelid
JOIN pg_namespace AS n ON n.oid = i.relnamespace
WHERE n.nspname = 'public'
  AND i.relname = 'ux_lab_order_external_no';

카탈로그 통과만으로 성능 검증이 끝나는 것은 아닙니다. 대표 SELECT를 EXPLAIN (ANALYZE, BUFFERS)로 실행할지는 실제 부하와 쿼리 안전성을 보고 결정하세요. 쓰기 SQL이나 오래 걸리는 쿼리에 무심코 ANALYZE를 붙이지 말고, 운영에서 허용된 읽기 쿼리와 바인드값으로 계획을 확인합니다.

DROP 대신 REINDEX CONCURRENTLY를 선택할 때

PostgreSQL 공식 문서는 실패한 동시 생성의 기본 복구법으로 삭제 후 다시 CREATE INDEX CONCURRENTLY를 권하며, REINDEX INDEX CONCURRENTLY도 대안으로 제시합니다. REINDEX는 기존 인덱스 정의를 그대로 다시 만들고 이름 전환을 관리해 주므로 정의를 보존하고 싶을 때 유용합니다.

다만 REINDEX도 추가 디스크 공간, 여러 대기 단계, 작업 시간을 요구합니다. 제약조건에 연결된 인덱스, 파티션 인덱스, 시스템 카탈로그처럼 대상 종류에 따라 CONCURRENTLY 제한이 달라집니다. “invalid면 모두 REINDEX”라는 자동 규칙 대신 대상 객체 종류와 버전별 REINDEX 공식 제한을 확인한 뒤 고르세요.

자주 생기는 실수

• invalid를 보자마자 삭제: 아직 생성 중일 수 있으므로 진행 뷰를 먼저 확인합니다.
• 같은 CREATE만 반복: 이름이 이미 남아 실패하거나, 중복·디스크·timeout 같은 원인이 그대로라 다시 실패합니다.
• IF NOT EXISTS로 덮기: 같은 이름 객체가 있다는 알림만 내며 기존 정의나 유효성을 보장하지 않습니다.
• UNIQUE의 잔여 효과 무시: invalid UNIQUE 인덱스가 유일성 검사를 계속할 수 있어 업무 오류와 연결될 수 있습니다.
• 배포 트랜잭션에 포함: CONCURRENTLY 명령은 트랜잭션 블록 안에서 실행할 수 없습니다.
• 일반 DROP 사용: 일반 삭제는 테이블에 ACCESS EXCLUSIVE 잠금을 잡아 다른 접근을 막을 수 있습니다.
• 재생성 완료를 명령 종료만으로 판단: valid·ready, 정의, 대표 쿼리 계획과 서비스 지표를 함께 확인해야 합니다.

정리 기준

가장 안전한 출발점은 “invalid니까 삭제”가 아니라 “진행 중인가, 실패했는가”를 가르는 것입니다. 진행 행이 없고 오류가 확인됐으며 실패 원인을 제거했다면, 정의와 의존성을 기록하고 동시 삭제와 동시 재생성을 차례로 실행하세요. UNIQUE, 파티션, 제약조건 연결 인덱스는 일반 인덱스와 같은 자동 절차로 묶지 않는 편이 안전합니다.

작업이 끝난 뒤에는 indisvalid와 indisready가 모두 true인지, 대표 쿼리의 접근 경로가 데이터 규모와 조건에 적절한지, 쓰기 지연과 디스크가 정상 범위로 돌아왔는지 확인합니다. 이 마지막 검증이 있어야 invalid 잔재를 지운 일이 서비스 성능 복구로 이어졌다고 판단할 수 있습니다.

공식 출처

• PostgreSQL 18 CREATE INDEX — Building Indexes Concurrently
• PostgreSQL 18 DROP INDEX
• PostgreSQL 18 REINDEX
• PostgreSQL 18 pg_index
• PostgreSQL 18 Progress Reporting