들어가며

운영 DB를 살펴보다가 이상한 인덱스를 하나 발견했습니다. 유니크 인덱스 xx_uniq가 분명히 있는데, 그 컬럼을 조건으로 조회하는 쿼리가 인덱스를 타지 않고 있었습니다.

\d로 테이블을 확인해보니 인덱스 이름 옆에 낯선 단어가 붙어 있었습니다.

"xx_uniq" UNIQUE, btree (...) INVALID

원인은 마이그레이션이었습니다. 이 인덱스를 만들던 마이그레이션이 도중에 타임아웃으로 실패했는데, 트랜잭션 없이 실행되는 구문이라 롤백되지 않았습니다. 마이그레이션은 실패했지만 DB에는 인덱스가 만들어진 것처럼 남아 있었던 겁니다. INVALID 상태로요.

해결은 명령 한 줄이었습니다.

REINDEX INDEX CONCURRENTLY xx_uniq;

명령은 한 줄이었지만 질문이 여럿 남았습니다.

• INVALID 인덱스는 정확히 어떤 상태인가?
• 실패한 인덱스가 왜 롤백되지 않고 남았나?
• 왜 그동안 아무도 몰랐나?
• 왜 REINDEX TABLE이 아니라 REINDEX INDEX여야 하나?

이 글의 출력은 전부 PostgreSQL 18.3에서 직접 재현한 결과입니다.

재현 환경

“한 사용자는 같은 쿠폰을 한 번만 받는다”는 규칙을 유니크 인덱스로 거는 상황을 가정합니다.

CREATE TABLE user_coupon (
  id        bigserial PRIMARY KEY,
  user_id   bigint NOT NULL,
  coupon_id bigint NOT NULL,
  issued_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO user_coupon (user_id, coupon_id)
SELECT g, g % 1000 FROM generate_series(1, 2000000) g;

-- 인덱스와 상관없는 테이블. 뒤에서 "다른 테이블만 읽는 트랜잭션"을 만들 때 쓴다
CREATE TABLE coupon (
  id   bigint PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO coupon SELECT g, 'coupon-' || g FROM generate_series(1, 1000) g;

운영 중인 테이블이라는 가정이므로 쓰기를 막지 않는 CONCURRENTLY로 인덱스를 만듭니다.

CREATE UNIQUE INDEX CONCURRENTLY user_coupon_uniq ON user_coupon (user_id, coupon_id);

CONCURRENTLY는 어떻게 쓰기를 막지 않는가

INVALID를 이해하려면 CONCURRENTLY의 동작부터 봐야 합니다.

일반 CREATE INDEX는 테이블에 SHARE 잠금을 걸고 테이블을 한 번 읽어서 인덱스를 만듭니다. 읽기는 되지만 INSERT/UPDATE/DELETE는 인덱스가 다 만들어질 때까지 대기합니다. 큰 테이블이라면 수십 분 동안 쓰기가 멈출 수 있습니다.

CONCURRENTLY는 쓰기와 충돌하지 않는 SHARE UPDATE EXCLUSIVE 잠금만 겁니다. 이 잠금은 같은 잠금끼리는 충돌해서, 한 테이블에서 CONCURRENTLY 빌드는 한 번에 하나만 돌고 그동안 그 테이블의 VACUUM·ANALYZE도 돌지 못합니다.

쓰기를 막지 않는 대가로 작업을 트랜잭션 네 개로 쪼개고, 테이블을 두 번 읽고, 중간에 세 번 기다립니다.

ready·valid는 pg_index의 indisready·indisvalid 플래그입니다. ②에서 ready가 켜지면 그때부터 INSERT/UPDATE가 이 인덱스도 갱신하고, ④에서 valid가 켜져야 쿼리에 쓰입니다.

두 번의 스캔은 각각 이런 일을 합니다.

• 1차 스캔(빌드): 스냅샷을 하나 잡고, 그 순간 보이는 행만으로 인덱스를 만듭니다. 테이블을 처음부터 끝까지 읽어(scanning table) 키 순서로 정렬하고(sorting live tuples) B-tree에 채웁니다(loading tuples in tree). 이렇게 읽고 만드는 동안 들어온 행은 인덱스에 없습니다. 아직 ready가 꺼져 있어 쓰는 쪽도 이 인덱스를 갱신하지 않기 때문입니다.
• 2차 스캔(검증): 그렇게 빠진 행을 채웁니다. 인덱스를 읽어 들어 있는 행 위치(TID)를 모아 정렬해 두고(scanning index → sorting tuples), 새 스냅샷으로 테이블을 다시 읽으며 대조합니다(scanning table). 테이블에는 있는데 인덱스에는 없는 행을 찾으면 그 자리에서 인덱스에 넣습니다. 이름은 검증(validation)이지만 하는 일은 대조와 보충입니다.

핵심은 ①에서 인덱스가 이미 카탈로그에 등록된다는 점입니다. 처음부터 invalid 상태로 등록해두고, 모든 단계를 통과한 뒤에야 valid로 바꿉니다.

그런데 이 과정은 트랜잭션 하나가 아닙니다. 중간에 실패해도 ①을 되돌려 줄 롤백이 없습니다. 그래서 실패하면 등록된 인덱스가 invalid 상태 그대로 남습니다. 이것이 INVALID 인덱스의 정체입니다.

반대로 일반 CREATE INDEX는 트랜잭션 하나로 끝나는 작업이라, 타임아웃으로 실패하면 통째로 롤백되고 아무것도 남지 않습니다. 일반 테이블에서 INVALID 인덱스를 발견했다면 CONCURRENTLY 작업이 도중에 실패했다는 뜻입니다.

여러 트랜잭션을 스스로 커밋하며 진행하는 구조라서, CONCURRENTLY는 트랜잭션 블록 안에서 실행할 수도 없습니다.

BEGIN;
CREATE INDEX CONCURRENTLY user_coupon_tmp ON user_coupon (coupon_id);

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

세 번의 대기는 무엇을 기다리나

위 그림의 점선 상자 세 개는 모두 다른 트랜잭션을 기다리는 구간입니다. 진행 상황 뷰 pg_stat_progress_create_index에서는 각각 다른 phase로 보입니다.

• ①→② waiting for writers before build: 이 테이블을 수정 중인 트랜잭션을 기다립니다. 인덱스가 있는 줄 모르는 트랜잭션은 새 인덱스의 컬럼을 바꾸는 UPDATE도 HOT 업데이트로 처리할 수 있어서, 그대로 빌드하면 인덱스와 테이블이 어긋납니다.
• ②→③ waiting for writers before validation: indisready가 true가 되기 전에 시작한 쓰기 트랜잭션을 기다립니다. 이 대기가 끝나면 이후의 모든 쓰기가 인덱스에도 기록된다는 보장이 생기고, 그 사이에 빠진 행은 ③의 2차 스캔이 채웁니다.
• ③→④ waiting for old snapshots: 검증 시점보다 오래된 스냅샷을 가진 트랜잭션을 기다립니다. 오래된 스냅샷은 인덱스에 없는 옛 행을 아직 볼 수 있어서, 그런 트랜잭션이 인덱스를 쓰면 결과가 틀립니다.

마지막 대기가 함정입니다. 스냅샷은 테이블이 아니라 트랜잭션에 딸려 있어서, PostgreSQL은 오래된 스냅샷이 언제 이 테이블을 읽을지 알 수 없습니다. 그래서 대상 테이블과 상관없이 같은 DB의 모든 트랜잭션을 봅니다. 아래 타임아웃 절에서 직접 재현합니다.

pg_index의 세 가지 플래그

인덱스의 상태는 pg_index 카탈로그의 불리언 컬럼으로 표현됩니다.

\d가 보여주는 INVALID는 indisvalid = false를 뜻합니다. 그런데 어느 단계에서 실패했느냐에 따라 indisready 값이 달라지고, 이 차이가 꽤 중요합니다. 뒤에서 다시 다룹니다.

INVALID 인덱스는 언제 생기나

1. 타임아웃

이번에 겪은 케이스가 여기에 해당합니다. 마이그레이션에 statement_timeout이나 lock_timeout을 거는 건 좋은 습관입니다. 그런데 CONCURRENTLY와 만나면 INVALID 인덱스를 남깁니다.

SET statement_timeout = '300ms';
CREATE UNIQUE INDEX CONCURRENTLY user_coupon_uniq ON user_coupon (user_id, coupon_id);

ERROR:  canceling statement due to statement timeout

\d user_coupon

Indexes:
    "user_coupon_pkey" PRIMARY KEY, btree (id)
    "user_coupon_uniq" UNIQUE, btree (user_id, coupon_id) INVALID

lock_timeout은 조금 더 의외입니다. 위 그림의 대기(점선 상자)도 락 대기라서 lock_timeout의 적용을 받습니다. 그래서 테이블을 수정한 채 커밋하지 않고 열려 있는 트랜잭션이 하나만 있어도 실패합니다.

-- 다른 세션: BEGIN; UPDATE user_coupon SET ... WHERE id = 1;  (커밋하지 않고 대기 중)

SET lock_timeout = '2s';
CREATE UNIQUE INDEX CONCURRENTLY user_coupon_uniq ON user_coupon (user_id, coupon_id);

ERROR:  canceling statement due to lock timeout

결과는 똑같이 INVALID 인덱스입니다.

가장 찾기 어려운 경우는 따로 있습니다. 마지막 대기(waiting for old snapshots)는 앞에서 본 것처럼 DB 전체를 봅니다. user_coupon에는 아무도 쓰지 않아도, 다른 테이블만 읽는 REPEATABLE READ 트랜잭션 하나가 열려 있으면 멈춥니다.

-- 다른 세션: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM coupon;  (커밋하지 않고 대기 중)

SET statement_timeout = '5s';
CREATE UNIQUE INDEX CONCURRENTLY user_coupon_uniq ON user_coupon (user_id, coupon_id);

ERROR:  canceling statement due to statement timeout

기다리는 동안 진행 상황 뷰에 pg_stat_activity를 붙여 보면 누가 막고 있는지 나옵니다.

SELECT p.phase, a.pid, a.state, left(a.query, 40) AS query
FROM pg_stat_progress_create_index p
LEFT JOIN pg_stat_activity a ON a.pid = p.current_locker_pid;

           phase           |  pid  |        state        |            query
---------------------------+-------+---------------------+------------------------------
 waiting for old snapshots | 73948 | idle in transaction | SELECT count(*) FROM coupon;

user_coupon과 상관없는 coupon 조회가 범인입니다. 같은 트랜잭션을 READ COMMITTED로 열어 두면 막지 않습니다. 같은 조건에서 인덱스가 1초 안에 만들어졌습니다. READ COMMITTED는 문장이 끝나면 스냅샷을 내려놓기 때문입니다. 반대로 오래 도는 분석 쿼리나 pg_dump처럼 스냅샷을 오래 쥐는 작업은 같은 DB 어디에서 돌든 CONCURRENTLY를 붙잡습니다.

이때 남은 인덱스는 indisready = true입니다. ③을 지나 마지막 대기에서 멈췄기 때문입니다. 이 차이가 왜 중요한지는 아래 4번에서 다룹니다.

2. 데이터가 유니크하지 않을 때

CREATE UNIQUE INDEX CONCURRENTLY user_coupon_uniq ON user_coupon (user_id, coupon_id);

ERROR:  could not create unique index "user_coupon_uniq"
DETAIL:  Key (user_id, coupon_id)=(1, 1) is duplicated.

이 경우는 INVALID 인덱스를 다시 빌드해도 똑같이 실패합니다. 중복부터 정리해야 합니다(아래 복구 절차 참고).

3. 작업 중인 세션이 중단될 때

공식 문서는 데드락, 그리고 표현식·부분 인덱스의 식 평가 오류도 원인으로 꼽습니다. 그 밖에 pg_cancel_backend·pg_terminate_backend로 작업 세션이 중단되거나, DB가 재시작·페일오버되는 경우도 같습니다. 어떤 이유든 ①~④ 사이에서 멈추면 결과는 INVALID 인덱스입니다.

왜 아무도 몰랐을까

INVALID 인덱스가 정말 곤란한 이유는 조용하다는 데 있습니다. 재현하면서 확인한 이유가 네 가지 있습니다.

1. 마이그레이션은 실패했는데, 인덱스는 “있다”

CREATE INDEX CONCURRENTLY는 트랜잭션 블록 안에서 실행할 수 없으니, 마이그레이션 도구도 이 구문만큼은 트랜잭션 없이 실행합니다.

• Django: AddIndexConcurrently는 트랜잭션 안에서 동작하지 않으므로 마이그레이션을 non-atomic(atomic = False)으로 선언해야 합니다.
• Flyway: PostgreSQL 파서가 CREATE INDEX CONCURRENTLY 같은 구문을 감지해 트랜잭션 밖에서 실행합니다.

평소의 마이그레이션은 실패하면 트랜잭션째 롤백되니 “실패 = 아무 일도 없었음”입니다. 이 구문은 다릅니다. 마이그레이션 도구는 실패를 보고하는데, DB에는 ①에서 등록된 인덱스가 그대로 남습니다. \d나 GUI 도구로 보면 인덱스가 있는 것처럼 보이고, 이름 옆의 INVALID 한 단어는 놓치기 쉽습니다.

이 상태에서 같은 마이그레이션을 재실행하면 이름이 겹쳐서 실패합니다.

CREATE UNIQUE INDEX CONCURRENTLY user_coupon_uniq ON user_coupon (user_id, coupon_id);

ERROR:  relation "user_coupon_uniq" already exists

인덱스가 “이미 있으니” 적용된 것으로 처리하고 넘어가고 싶어지는 순간입니다(Django라면 migrate --fake). 그렇게 넘어가면 INVALID 인덱스는 그대로 굳어버립니다.

2. IF NOT EXISTS 재시도는 “성공”한다

마이그레이션은 재실행에 안전하도록 IF NOT EXISTS를 붙이곤 합니다. 실패한 뒤 재시도하면 이렇게 됩니다.

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS user_coupon_uniq ON user_coupon (user_id, coupon_id);

NOTICE:  relation "user_coupon_uniq" already exists, skipping
CREATE INDEX

같은 이름의 인덱스가 “이미 있으니” 건너뛰고, 명령은 성공으로 끝납니다. 마이그레이션 도구 입장에서도 성공이라 이력에 성공으로 남습니다. 그런데 인덱스는 여전히 INVALID입니다.

공식 문서도 IF NOT EXISTS는 이름만 볼 뿐이라고 경고합니다.

Note that there is no guarantee that the existing index is anything like the one that would have been created.

3. 플래너는 무시만 할 뿐, 아무것도 알려주지 않는다

EXPLAIN SELECT * FROM user_coupon WHERE user_id = 12345 AND coupon_id = 345;

 Gather  (cost=1000.00..28206.10 rows=1 width=32)
   Workers Planned: 2
   ->  Parallel Seq Scan on user_coupon  (cost=0.00..27206.00 rows=1 width=32)
         Filter: ((user_id = 12345) AND (coupon_id = 345))

인덱스가 있는데도 Seq Scan입니다. 플래너는 INVALID 인덱스를 조용히 건너뛸 뿐 경고를 남기지 않습니다. 데이터가 적을 땐 티가 안 나다가, 테이블이 커지면서 서서히 느려집니다.

seq scan을 꺼서 인덱스를 쓰도록 몰아도 결과는 같습니다.

SET enable_seqscan = off;
EXPLAIN SELECT * FROM user_coupon WHERE user_id = 12345 AND coupon_id = 345;

 Gather  (cost=1000.00..28206.10 rows=1 width=32)
   Workers Planned: 2
   ->  Parallel Seq Scan on user_coupon  (cost=0.00..27206.00 rows=1 width=32)
         Disabled: true
         Filter: ((user_id = 12345) AND (coupon_id = 345))

Disabled: true가 붙은 채로 Seq Scan을 고릅니다. 플래너에게 INVALID 인덱스는 아예 후보가 아닙니다.

4. 유니크 제약은 여전히 걸려 있을 수 있다

가장 헷갈리는 부분입니다. ③ 검증 단계에서 실패하면 그 시점에 indisready는 이미 true입니다. 즉 인덱스가 쓰기에는 참여합니다. 검증 단계에서 pg_cancel_backend로 작업을 취소해 재현해봤습니다.

      index       | indisvalid | indisready | indislive
------------------+------------+------------+-----------
 user_coupon_uniq | f          | t          | t

이 상태에서 중복 INSERT를 시도하면 막힙니다.

INSERT INTO user_coupon (user_id, coupon_id) VALUES (1, 1);

ERROR:  duplicate key value violates unique constraint "user_coupon_uniq"
DETAIL:  Key (user_id, coupon_id)=(1, 1) already exists.

애플리케이션 입장에서는 유니크 제약이 잘 걸려 있는 것처럼 보입니다. 하지만 같은 조건의 조회는 여전히 Seq Scan이고, 인덱스는 디스크를 차지하면서(300만 건 기준 90MB) INSERT마다 갱신됩니다.

쓰기 비용은 내는데 읽기 이득은 없는 상태입니다.

공식 문서에도 이 동작이 적혀 있습니다.

if a failure does occur in the second scan, the “invalid” index continues to enforce its uniqueness constraint afterwards.

다만 제약이 “걸려 있다”는 것이지 유일성이 “보장된다”는 뜻은 아닙니다. 검증을 끝내지 못했으니 pg_index 문서의 설명대로 기존 데이터의 유일성은 보장되지 않습니다.

반대로 ① 직후나 ② 빌드 도중에 실패하면 indisready = false입니다. 이때는 쓰기에도 참여하지 않으니 비용은 없지만, 유니크 제약도 없습니다. 같은 INVALID 표시라도 속사정이 다릅니다.

indisready = false 쪽도 그냥 두면 안 됩니다. 유니크 검사를 하지 않으니 방치하는 동안 중복이 그대로 들어옵니다. user_coupon을 수정 중인 트랜잭션 때문에 첫 대기(waiting for writers before build)에서 타임아웃이 난 인덱스로 확인해봤습니다.

      index       | indisvalid | indisready
------------------+------------+------------
 user_coupon_uniq | f          | f

INSERT INTO user_coupon (user_id, coupon_id) VALUES (1, 1);

INSERT 0 1

indisready = true였다면 거부됐을 중복이 들어갔습니다. 이렇게 쌓인 중복은 나중에 인덱스를 고칠 때 REINDEX를 실패하게 만듭니다(복구 절차 1번).

찾는 법

테이블 하나라면 \d 테이블명으로 충분하지만, 전체를 훑으려면 카탈로그를 조회합니다.

SELECT c.relname AS index_name,
       t.relname AS table_name,
       i.indisvalid,
       i.indisready,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

    index_name    | table_name  | indisvalid | indisready | size
------------------+-------------+------------+------------+-------
 user_coupon_uniq | user_coupon | f          | t          | 90 MB

결과가 나왔다고 무조건 고장은 아닙니다. 두 가지 예외가 있습니다.

• 지금 만들어지고 있는 인덱스: CONCURRENTLY 작업이 진행 중이면 당연히 invalid입니다. pg_stat_progress_create_index에 해당 작업이 있는지 먼저 확인합니다.
• 파티션 테이블에 ONLY로 만든 부모 인덱스: 모든 파티션에 인덱스가 붙기 전까지는 설계상 invalid입니다.

이 쿼리를 마이그레이션 직후 검증이나 정기 점검에 넣어두면 좋습니다. 결과가 0건이어야 정상입니다.

고치는 법

선택지

DROP 후 CREATE와 REINDEX INDEX CONCURRENTLY 둘 다 쓰기를 막지 않습니다. 정의를 다시 적을 필요 없이 명령 하나로 끝난다는 점에서 이번엔 REINDEX를 택했습니다. 반대로 인덱스 정의 자체를 바꿔야 한다면 DROP 후 CREATE가 맞습니다. REINDEX는 기존 정의를 그대로 다시 만들 뿐입니다.

REINDEX TABLE로는 안 된다

테이블 단위로 다시 만들면 되지 않을까 싶지만, invalid 인덱스는 건너뜁니다.

REINDEX TABLE CONCURRENTLY user_coupon;

WARNING:  skipping reindex of invalid index "public.user_coupon_uniq"
HINT:  Use DROP INDEX or REINDEX INDEX.
REINDEX

에러가 아니라 WARNING이고, 명령은 REINDEX로 성공 처리됩니다. 테이블의 다른 인덱스는 다시 만들어지는데, 정작 고치려던 인덱스는 그대로입니다. 공식 문서에도 명시돼 있습니다.

Note that only REINDEX INDEX is able to perform a concurrent build on an invalid index.

REINDEX INDEX CONCURRENTLY가 하는 일

REINDEX CONCURRENTLY도 단계마다 별도 트랜잭션으로 진행합니다. 기존 인덱스를 고치는 게 아니라 옆에 새로 만들고, 다 만들어지면 맞바꿉니다. 새로 만드는 ①~③은 앞의 CREATE INDEX CONCURRENTLY와 같습니다.

그래서 작업 내내 쓰기가 막히지 않습니다. 대신 일반 REINDEX보다 오래 걸리고, 작업 중에는 인덱스 두 벌만큼 디스크가 필요합니다.

복구 절차

1. 유니크 인덱스라면 중복부터 확인합니다.

SELECT user_id, coupon_id, count(*)
FROM user_coupon
GROUP BY user_id, coupon_id
HAVING count(*) > 1;

중복이 남아 있으면 REINDEX도 똑같이 실패하고, invalid 인덱스가 하나 더 생깁니다. indisready = false인 인덱스를 방치한 사이 들어온 중복도 마찬가지입니다.

REINDEX INDEX CONCURRENTLY user_coupon_uniq;

ERROR:  could not create unique index "user_coupon_uniq_ccnew"
DETAIL:  Key (user_id, coupon_id)=(1, 1) is duplicated.

\d user_coupon

Indexes:
    "user_coupon_pkey" PRIMARY KEY, btree (id)
    "user_coupon_uniq" UNIQUE, btree (user_id, coupon_id) INVALID
    "user_coupon_uniq_ccnew" UNIQUE, btree (user_id, coupon_id) INVALID

REINDEX가 실패하며 남긴 인덱스는 접미사로 구분합니다. 이름이 겹치면 _ccnew1, _ccold2처럼 숫자가 붙습니다.

2. 오래 열린 트랜잭션을 확인합니다.

REINDEX CONCURRENTLY도 같은 방식으로 트랜잭션이 끝나길 기다립니다. 오래 열린 트랜잭션이 있으면 그만큼 지연되고, lock_timeout이 걸려 있다면 다시 실패할 수 있습니다.

SELECT pid, now() - xact_start AS duration, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

3. 트랜잭션 밖에서 실행합니다.

REINDEX INDEX CONCURRENTLY user_coupon_uniq;

CREATE INDEX CONCURRENTLY와 마찬가지로 트랜잭션 블록 안에서는 실행되지 않습니다.

ERROR:  REINDEX CONCURRENTLY cannot run inside a transaction block

4. 큰 테이블이라면 진행 상황을 지켜봅니다.

SELECT pid, command, phase, blocks_done, blocks_total, tuples_done, tuples_total
FROM pg_stat_progress_create_index;

phase는 이 순서로 바뀝니다. waiting for로 시작하는 단계가 앞의 CONCURRENTLY 그림에 있는 점선 상자, 즉 세 번의 대기입니다.

대기 단계에서 오래 멈춰 있다면 앞의 타임아웃 절에서 본 current_locker_pid 쿼리로 막고 있는 세션을 찾습니다. REINDEX CONCURRENTLY는 여기에 옛 인덱스를 정리하는 waiting for readers before marking dead, waiting for readers before dropping 단계가 더 붙습니다.

5. 결과를 확인합니다.

SELECT indexrelid::regclass AS index, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'user_coupon_uniq'::regclass;

      index       | indisvalid | indisready
------------------+------------+------------
 user_coupon_uniq | t          | t

EXPLAIN SELECT * FROM user_coupon WHERE user_id = 12345 AND coupon_id = 345;

 Index Scan using user_coupon_uniq on user_coupon  (cost=0.43..8.45 rows=1 width=32)
   Index Cond: ((user_id = 12345) AND (coupon_id = 345))

예상 비용이 28206.10에서 8.45로 내려갔습니다.

재발을 막으려면

• 실패한 CONCURRENTLY 마이그레이션은 “부분 적용” 상태로 본다. 롤백이 없으니 실패해도 무언가 남아 있습니다. 재실행하거나 적용 처리(--fake 등)하기 전에 invalid 인덱스부터 확인하고 지웁니다. IF NOT EXISTS 재시도로는 고쳐지지 않습니다.
• 마이그레이션 직후 invalid 인덱스 탐지 쿼리를 돌린다. 결과가 0건인지 확인하는 단계를 배포 절차에 넣어두면, 조용히 남는 문제를 그 자리에서 잡을 수 있습니다.
• CONCURRENTLY 구문은 별도 마이그레이션으로 분리한다. 같은 마이그레이션의 다른 DDL까지 트랜잭션 보호를 잃지 않도록, 한 파일에 하나만 둡니다.
• 유니크 인덱스는 중복 검사를 먼저 한다. 중복이 있으면 생성도, REINDEX도 실패합니다.
• timeout을 구문 성격에 맞게 건다. CONCURRENTLY는 원래 오래 걸리고, 다른 트랜잭션을 기다리기도 하는 작업입니다. 일반 DDL용으로 짧게 잡은 timeout을 그대로 적용하면 실패 → INVALID로 이어지기 쉽습니다.
• CONCURRENTLY 전에 오래 열린 트랜잭션부터 확인한다. 마지막 대기는 대상 테이블과 상관없는 트랜잭션도 기다립니다. 같은 DB에 REPEATABLE READ 트랜잭션, pg_dump, 오래 도는 분석 쿼리가 있으면 그것이 끝날 때까지 멈추고, timeout이 걸려 있으면 INVALID로 이어집니다. 복구 절차 2번의 pg_stat_activity 쿼리를 그대로 쓰면 됩니다.
• Flyway라면 flyway.postgresql.transactional.lock을 확인한다. Flyway 문서는 CREATE INDEX CONCURRENTLY 같은 구문에는 이 값을 false(기본값 true)로 두라고 안내합니다.

정리

1. CONCURRENTLY는 여러 트랜잭션으로 나눠 진행하므로, 중간에 실패해도 롤백되지 않고 INVALID 인덱스가 남는다. 마이그레이션 도구도 이 구문은 트랜잭션 없이 실행하니, 마이그레이션이 실패해도 인덱스는 “적용된 것처럼” 보인다.
2. 마지막 대기(waiting for old snapshots)는 대상 테이블이 아니라 같은 DB 전체를 본다. 다른 테이블만 읽는 REPEATABLE READ 트랜잭션이나 pg_dump도 CONCURRENTLY를 붙잡아 타임아웃을 낸다.
3. 플래너는 INVALID 인덱스를 쓰지 않는다. 그런데 검증 단계 이후에 실패했다면 쓰기 비용과 유니크 검사는 계속되고, 그 전에 실패했다면 유니크 검사 없이 중복이 들어온다. 어느 쪽이든 눈치채기 어렵다.
4. IF NOT EXISTS 재시도는 성공으로 끝나고, REINDEX TABLE CONCURRENTLY는 invalid 인덱스를 건너뛴다. 고치려면 REINDEX INDEX CONCURRENTLY, 또는 DROP 후 CREATE.
5. 유니크 인덱스라면 중복부터 정리한다. 아니면 _ccnew 찌꺼기가 하나 더 생긴다.

참고자료

• PostgreSQL — CREATE INDEX: Building Indexes Concurrently
• PostgreSQL — REINDEX: Rebuilding Indexes Concurrently
• PostgreSQL — pg_index
• PostgreSQL — CREATE INDEX Progress Reporting
• PostgreSQL — Explicit Locking: Table-Level Locks
• PostgreSQL 소스 — indexcmds.c (DefineIndex의 CONCURRENTLY 단계별 주석)
• Django — Concurrent index operations
• Flyway — PostgreSQLParser (트랜잭션 밖에서 실행할 구문 감지)
• Flyway — PostgreSQL Transactional Lock Setting