CREATE INDEX CONCURRENTLY가 마이그레이션에서 25001로 막힐 때

마이그레이션에 넣은 CREATE INDEX CONCURRENTLY가 cannot run inside a transaction block으로 거부되면, 실행기가 그 DDL을 트랜잭션으로 감쌌는지 확인한다. SQL 파일에 BEGIN이 없어도 실행기가 먼저 보냈을 수 있다. 해당 인덱스 생성 단계를 트랜잭션 블록 밖에서 독립 실행하도록 분리해야 한다. 파일만 나누고 실행기가 다시 한 트랜잭션으로 묶으면 같은 오류가 난다.

PostgreSQL 18 문서↗ (새 탭에서 열림)는 concurrent 생성이 내부적으로 여러 트랜잭션을 거치며, 트랜잭션 블록 안에서는 실행할 수 없다고 설명한다. 독립 실행은 PostgreSQL의 트랜잭션 기능을 끈다는 뜻이 아니다. 클라이언트가 이 DDL을 다른 작업과 하나의 BEGIN·COMMIT 사이에 묶지 않는다는 뜻이다.

최초 오류와 같은 연결의 BEGIN을 확인한다

먼저 마이그레이션 로그에서 실패한 DDL의 원래 오류와 SQLSTATE를 찾는다. 별도 실습에서 실제로 받은 오류는 다음과 같았다.

ERROR: 25001: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

25001의 공식 코드 이름↗ (새 탭에서 열림)은 active_sql_transaction이다. 이 메시지와 함께 나왔다면 인덱스 생성이 허용되지 않는 경계에서 제출됐다는 단서다. 테이블을 줄이거나 대기 시간을 늘려도 이 제한은 풀리지 않는다.

SQL 실행 로그에서 같은 연결의 BEGIN → CREATE INDEX CONCURRENTLY → 25001 → ROLLBACK 순서를 찾고, 실행기의 파일별·묶음별 트랜잭션 설정과 드라이버의 자동 커밋 설정을 대조한다. 다른 연결의 BEGIN은 근거가 되지 않는다. 일부 클라이언트가 BEGIN·COMMIT을 자동으로 보낼 수 있다는 점도 트랜잭션 문서↗ (새 탭에서 열림)에 설명돼 있다.

psql의 -1·--single-transaction도 확인할 대상이다. BEGIN이 없는 파일이라도 이 옵션으로 실행하면 바깥에 트랜잭션이 추가된다. 이번 비교에서는 오류 즉시 중단을 켠 같은 SQL 파일이 -1을 붙이면 25001과 종료 코드 3으로 실패했고, 옵션을 빼자 성공했다. 서버 로그에서도 psql이 추가한 BEGIN과 ROLLBACK을 확인했다. 옵션과 종료 코드↗ (새 탭에서 열림)

BEGIN 로그가 안 보인다고 독립 실행으로 단정할 수도 없다. 여러 SQL을 하나의 psql -c 문자열로 보내면 한 요청의 트랜잭션으로 처리된다. 실행기가 실제로 어떻게 SQL을 보내는지까지 확인한다. psql의 전송 단위↗ (새 탭에서 열림)

인덱스 생성 한 문장을 별도 단계로 보낸다

실행기에서 해당 DDL 단계에 트랜잭션을 적용하지 않는 지원 기능을 확인한다. 선행 테이블·열 변경은 먼저 커밋하고, 인덱스 생성은 블록 밖에서 한 문장으로 보낸다. SQL 파일에 COMMIT을 끼워 넣는 것만으로 실행기의 경계와 이력 관리가 맞아졌다고 판단하지 않는다. 실제 도구의 옵션 이름과 동작은 도구·버전별로 확인해야 한다. 이번에는 실제 마이그레이션 설정을 변경하거나 실행하지 않았다.

아래 DDL은 이미 존재하는 일반 테이블 public.concurrent_index_demo의 lookup_key 열을 대상으로 한다. 자기 테이블에 적용할 때는 스키마·테이블·열·인덱스 이름을 바꾸고, 대상 DB 연결과 인덱스 생성 권한을 확인한다. 예제에서는 테이블 소유자로 실행했으며 같은 이름의 인덱스가 없었다. psql이라면 올바른 호스트·포트·사용자·DB로 새 연결을 열고 -X로 시작 설정을 제외한다. 기본 자동 커밋을 유지하고 -1 없이, 다음 한 문장을 입력한다.

CREATE INDEX CONCURRENTLY concurrent_index_demo_key_idx
ON public.concurrent_index_demo (lookup_key);

별도 실습은 PostgreSQL 서버와 psql 18.6, 소유 DB의 100행짜리 일반 테이블에서 진행했다. 같은 DDL은 BEGIN 안에서 25001로 거부됐고, ROLLBACK 뒤 해당 인덱스는 없었다. 이어 블록 밖에서 실행하자 CREATE INDEX로 끝났으며 SQLSTATE는 00000이었다. 임시 테이블은 인덱스를 항상 non-concurrent로 만들기 때문에 비교 대상으로 쓰지 않았다. 임시 테이블 처리↗ (새 탭에서 열림)

생성이 끝나면 다음 조회로 해당 테이블의 인덱스가 유효하고 쓰기를 반영할 준비가 됐는지 확인한다. 이름은 앞 DDL과 같은 대상으로 맞춘다.

SELECT indisvalid, indisready
FROM pg_index
WHERE indrelid = 'public.concurrent_index_demo'::regclass
  AND indexrelid = to_regclass('public.concurrent_index_demo_key_idx');

실습에서는 두 값이 모두 t였고 새 연결에서도 같았다. pg_index 문서↗ (새 탭에서 열림)에서 indisvalid는 쿼리에 사용할 수 있는 유효성, indisready는 INSERT·UPDATE 반영 준비 상태를 나타낸다. 조회가 0행이면 연결한 DB와 테이블·인덱스 이름부터 확인한다. 값이 f이면 완료로 처리하지 않는다. 둘 다 t여도 특정 쿼리의 실행 계획이 이 인덱스를 선택했다는 뜻은 아니다.

재시도 전에 인덱스 상태와 마이그레이션 이력을 대조한다

이번 25001은 생성 전 거부돼 인덱스를 남기지 않았다. 반면 공식 문서는 concurrent 생성이 스캔 도중 실패하면 invalid 인덱스가 남을 수 있다고 설명한다. 같은 이름이 있다는 이유만으로 성공으로 넘기지 않는다. 상태와 함께 대상 열·조건 등 정의가 의도와 같은지도 확인한다. psql에서는 \d public.concurrent_index_demo로 인덱스 목록과 정의를 볼 수 있다. 실습에서는 lookup_key의 B-tree 인덱스로 확인했다. 스캔 도중 실패와 invalid 잔존은 이번에 재현하지 않았다. 생성 실패 후 상태↗ (새 탭에서 열림)

분리한 단계는 앞뒤 변경과 한 번에 롤백되지 않는다. 인덱스 생성 뒤 후속 단계나 이력 기록이 실패하면, 재시도 전에 DB의 실제 상태와 실행기 이력을 대조해야 한다. 전체 변경의 일괄 롤백이 필수라면 트랜잭션 안에서 일반 CREATE INDEX를 쓰는 선택을 검토한다. 다만 일반 생성은 완료까지 대상 테이블의 쓰기를 막는다. concurrent 생성에도 기존 트랜잭션을 기다리는 과정과 CPU·I/O 비용이 있다. 두 방식의 차이↗ (새 탭에서 열림)

직접 확인한 범위는 트랜잭션 경계에 따른 거부·성공과 최종 인덱스 상태다. 100행의 성공으로 무잠금·무부하를 보장할 수는 없다. 동시 쓰기, 장기 트랜잭션, 대규모 테이블의 대기·성능과 실제 실행기의 이력 기록·재시도는 확인하지 않았다.