PostgreSQL 인덱스를 락 없이 만들기: CREATE INDEX CONCURRENTLY와 실패 시 남는 invalid 인덱스

운영 중인 테이블에 인덱스를 걸 때 그냥 CREATE INDEX를 쓰면 해당 테이블에 쓰기 락(SHARE)이 걸려 INSERT/UPDATE/DELETE가 인덱스 생성이 끝날 때까지 막힙니다. 무정지로 걸려면 CONCURRENTLY를 붙이면 됩니다. 대신 대가가 있습니다. 공식 문서 표현으로 "PostgreSQL은 테이블을 두 번 스캔하고, 인덱스를 쓰거나 수정할 수 있는 기존 트랜잭션이 모두 끝나기를 기다려야" 하므로 일반 방식보다 오래 걸립니다. 또 하나, 트랜잭션 블록 안에서는 못 씁니다. 마이그레이션 툴이 모든 DDL을 트랜잭션으로 감싼다면 이 문장만 따로 빼야 합니다.

CREATE INDEX CONCURRENTLY idx_topic_created ON topic (created_at);

주의할 점은 실패 처리입니다. 데드락이나 유니크 위반으로 도중에 실패하면 명령은 실패하지만 "invalid" 인덱스가 남습니다. 이 인덱스는 조회에는 안 쓰이면서 갱신 오버헤드만 잡아먹습니다. \d 테이블명으로 보면 인덱스 옆에 INVALID로 표시됩니다. 공식 권고 복구법은 그 인덱스를 지우고 다시 만드는 것입니다. 지울 때도 락을 피하려면 DROP INDEX CONCURRENTLY를 쓰는데, 한 번에 하나만, CASCADE 없이, 트랜잭션 밖에서만 됩니다. 재생성은 REINDEX INDEX CONCURRENTLY도 가능합니다.

-- 실패로 남은 invalid 인덱스 정리 후 재시도
DROP INDEX CONCURRENTLY idx_topic_created;
CREATE INDEX CONCURRENTLY idx_topic_created ON topic (created_at);

🔗 https://www.postgresql.org/docs/current/sql-createindex.html

🔗 https://www.postgresql.org/docs/current/sql-dropindex.html