CREATE INDEX IF NOT EXISTS가 성공해도 인덱스 열은 바뀌지 않는다

CREATE INDEX IF NOT EXISTS의 대상 열을 a에서 b로 고쳤는데도 실제 인덱스가 a에 남아 있을 수 있다. 같은 이름의 인덱스가 이미 있으면 생성을 건너뛰기 때문이다. 명령이 오류 없이 끝났다는 사실과 원하는 정의가 만들어졌다는 사실을 따로 확인해야 한다.

PostgreSQL 18의 CREATE INDEX 문서↗ (새 탭에서 열림)는 같은 이름의 객체가 있으면 오류 대신 NOTICE를 내며, 기존 인덱스가 새로 요청한 정의와 같다는 보장은 없다고 설명한다. 이 옵션을 기존 정의를 갱신하는 기능으로 사용하면 열을 바꾼 SQL도 성공한 것처럼 보인다.

지금 있는 정의부터 확인한다

원래 명령을 실행한 DB에 접속해 아래 조회를 실행한다. index_demo와 sample_lookup_idx는 이 글의 실습 이름이므로 자신의 스키마·인덱스 이름으로 바꾼다. psql이나 SQL 클라이언트에서 실행할 수 있는 읽기 전용 조회다.

SELECT c.oid, i.indrelid::regclass AS indexed_table,
       pg_get_indexdef(c.oid) AS definition
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_index AS i ON i.indexrelid = c.oid
WHERE n.nspname = 'index_demo'
  AND c.relname = 'sample_lookup_idx';

indexed_table로 대상 테이블을, definition으로 현재 인덱스의 열과 조건을 읽는다. pg_index 문서↗ (새 탭에서 열림)에서 indexrelid는 인덱스의 OID, indrelid는 그 인덱스가 속한 테이블의 OID다. 스키마까지 지정하는 이유는 이름이 같은 다른 스키마의 객체와 혼동하지 않기 위해서다. 인덱스는 대상 테이블과 같은 스키마에 만들어진다. 인덱스 이름 규칙↗ (새 탭에서 열림)

조회가 0행이면 원하는 인덱스를 확인하지 못한 상태다. 연결한 DB, 스키마와 이름, 같은 이름의 객체가 실제 인덱스인지부터 확인한다. 행이 있어도 definition이 (a)인데 필요한 정의가 (b)라면 생성 완료로 처리하지 않는다.

pg_get_indexdef는 보관된 SQL 파일을 읽는 함수가 아니다. 카탈로그에 있는 현재 인덱스 정의를 CREATE 명령 형태로 재구성한다. 따라서 원래 SQL의 공백이나 표기와 다를 수 있다. 이름·문자열 전체가 같은지만 비교하기보다 대상 테이블, 키 열의 순서, 유일성, 조건절 등 의도한 속성을 읽어야 한다. 함수의 반환값 설명↗ (새 탭에서 열림)

a를 만든 뒤 같은 이름으로 b를 요청했다

PostgreSQL 서버와 psql 18.6에서 소유한 작은 테이블로 따로 재현했다. 아래 입력을 따라 하려면 스키마를 만들 권한이 있는 실습용 DB와 아직 없는 index_demo 이름이 필요하다. 같은 psql 연결에서 순서대로 실행하며, 끝의 ROLLBACK까지 한 트랜잭션으로 진행한다. 이 예제는 일반 CREATE INDEX를 사용한다.

먼저 a를 가리키는 인덱스를 만든다.

BEGIN;
CREATE SCHEMA index_demo;
CREATE TABLE index_demo.sample (a integer, b integer);
CREATE INDEX sample_lookup_idx ON index_demo.sample (a);

앞의 정의 조회를 실행했을 때 OID는 16388이었고 정의는 USING btree (a)로 끝났다. 이어 인덱스 이름은 유지하고 열만 바꿔 요청했다.

CREATE INDEX IF NOT EXISTS sample_lookup_idx
ON index_demo.sample (b);

서버의 NOTICE와 psql의 완료 태그는 다음과 같았다. 마지막의 CREATE INDEX만 읽으면 새 인덱스를 만들었다고 오해하기 쉽다.

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

실습에서는 SET client_min_messages = notice;로 NOTICE를 표시하도록 했다. 클라이언트 로그에 NOTICE가 안 보이더라도 완료 태그만으로 정의가 바뀌었다고 판단하지 말고 같은 조회로 확인한다.

정의 조회를 다시 실행한 결과는 그대로였다. 아래는 psql의 비정렬 출력이다.

oid|indexed_table|definition
16388|index_demo.sample|CREATE INDEX sample_lookup_idx ON index_demo.sample USING btree (a)
(1 row)

요청한 (b)는 반영되지 않았다. 실행 전후 OID도 같고 재구성한 정의도 같았다. 여기서 OID는 같은 객체를 보고 있는지 대조하는 보조 정보다. 이 숫자는 환경마다 달라지며, OID가 같다는 사실만으로 모든 정의 변경이 없었다고 판단해서는 안 된다.

이번에는 같은 테이블의 b에 다른 이름을 주어 생성하고, 두 인덱스의 첫 번째 키를 조회했다.

CREATE INDEX sample_b_idx ON index_demo.sample (b);

SELECT c.relname AS index_name,
       pg_get_indexdef(c.oid, 1, false) AS first_key
FROM pg_class AS c
JOIN pg_index AS i ON i.indexrelid = c.oid
WHERE i.indrelid = 'index_demo.sample'::regclass
ORDER BY c.relname;

pg_get_indexdef에 열 위치 1을 주면 첫 번째 인덱스 열의 정의를 반환한다. 같은 실습에서 이 조회는 다음 두 행을 반환했다.

index_name|first_key
sample_b_idx|b
sample_lookup_idx|a
(2 rows)

b 인덱스 생성 자체는 가능했다. 새 이름으로 만든 인덱스와 기존 a 인덱스가 함께 존재했다. 원래 명령에서 b를 만들지 않은 이유는 기존 이름 때문에 생성을 건너뛴 데 있었다.

실습을 마친 연결에서 ROLLBACK;을 실행하면 이번 트랜잭션에서 만든 스키마·테이블·인덱스를 되돌린다. 실제 실행에서도 스키마가 남지 않았음을 확인했다.

정의가 다르면 별도 변경으로 다룬다

이미 적용한 SQL에서 (a)를 (b)로 고친 뒤 같은 IF NOT EXISTS 명령을 반복해도 이 불일치는 해결되지 않는다. 우선 현재 정의와 원하는 정의의 차이를 기록하고, 별도로 검토한 마이그레이션으로 필요한 인덱스를 만든다. 생성 후에는 같은 카탈로그 조회로 실제 결과를 다시 확인한다.

실습의 새 이름은 두 정의를 비교하기 위한 대조군이다. 운영에서도 기존 인덱스를 바로 삭제하거나 이름만 바꾸라는 절차로 옮기면 안 된다. 새 인덱스의 생성 방식과 기존 인덱스의 유지·정리는 잠금, 제약조건 의존성, 배포 순서를 함께 검토해 결정한다. 이번 실습은 운영 인덱스를 교체하지 않았고, 특정 마이그레이션 도구의 이력 처리도 확인하지 않았다.

직접 확인한 범위는 같은 테이블에서 이름을 유지한 요청이 NOTICE로 건너뛰어지는 것과, 새 이름의 b 인덱스가 별도로 만들어지는 것이다. 실행 계획이나 처리 속도는 측정하지 않았다. b 인덱스를 만들었다는 사실만으로 쿼리가 빨라졌다고 판단할 수는 없다.