CREATE INDEX CONCURRENTLY는 일반 CREATE INDEX보다 얼마나 느린가?

테이블을 두 번 스캔하고 동시 변경을 반영하는 추가 작업 때문에 일반 빌드보다 오래 걸리며 CPU와 I/O 부담도 더 크다. 정확한 시간은 테이블 크기, 동시 쓰기량, 디스크 성능에 따라 달라지므로 환경에 따라 다르지만 대략 일반 빌드의 두세 배 수준으로 보고 잡는 편이 안전하다. 대신 그동안 쓰기가 막히지 않는다.

INVALID 인덱스를 그냥 두면 어떤 문제가 생기나?

조회에는 쓰이지 않아 성능 이득은 없는데, INSERT/UPDATE 때 인덱스 갱신 부담은 그대로 진다. 유니크 인덱스였다면 INVALID 상태에서도 유니크 제약을 계속 강제하므로 예상치 못한 중복 에러의 원인이 될 수 있다. 발견하면 DROP INDEX CONCURRENTLY로 지우는 것이 맞다.

남아 있는 INVALID 인덱스는 어떻게 찾나?

pg_index 카탈로그를 조회한다. SELECT indexrelid::regclass, indrelid::regclass FROM pg_index WHERE NOT indisvalid; 로 유효하지 않은 인덱스와 소속 테이블을 한 번에 확인할 수 있다.

REINDEX 실패 후 남은 _ccnew와 _ccold는 어떻게 다르게 처리하나?

_ccnew는 재빌드가 완성되지 못한 임시 인덱스이므로 DROP INDEX CONCURRENTLY로 지우고 REINDEX CONCURRENTLY를 다시 실행한다. _ccold는 원본 인덱스가 제거되지 못하고 남은 경우로, 재빌드 자체는 성공한 것이니 그대로 지우면 된다.

마이그레이션 도구에서 cannot run inside a transaction block 에러가 나는 이유는?

CREATE INDEX CONCURRENTLY와 REINDEX CONCURRENTLY는 트랜잭션 블록 안에서 실행할 수 없는데, 많은 마이그레이션 도구가 각 변경을 BEGIN/COMMIT으로 감싸기 때문이다. Flyway는 executeInTransaction=false, Liquibase는 runInTransaction="false"로 해당 작업의 트랜잭션 래핑을 꺼야 한다.

락 없이 PostgreSQL 인덱스 추가하기

운영 중인 테이블에 인덱스 하나 추가하려다 서비스가 멈춘 경험이 한 번쯤 있을 것이다. 가입자가 늘면서 느려진 쿼리를 잡으려고 인덱스를 거는데, 그냥 CREATE INDEX를 때리면 테이블 쓰기가 전부 잠긴다. 트래픽이 있는 시간대에 하면 그대로 장애다. 그래서 야간 점검 창을 잡느냐, 아니면 무중단으로 거느냐를 매번 고민하게 된다.

결론부터 말하자면, PostgreSQL에서는 CREATE INDEX CONCURRENTLY로 쓰기를 막지 않고 인덱스를 빌드할 수 있다. 대신 느리고, 중간에 실패하면 쓸모없는 INVALID 인덱스가 남는다. 이 글에서는 CONCURRENTLY로 거는 방법, 남은 INVALID 인덱스를 찾아 지우는 법, 그리고 기존 인덱스를 무중단으로 다시 만드는 REINDEX CONCURRENTLY를 차례로 다룬다. 환경은 RDS PostgreSQL과 EC2 자체 설치 모두를 염두에 둔다.

1. 왜 일반 CREATE INDEX는 위험한가

인덱스는 테이블의 특정 컬럼 값을 미리 정렬해 둔 보조 자료 구조다. 조회 속도를 올리는 대신 빌드할 때 테이블 전체를 한 번 읽어야 한다. 기본 CREATE INDEX는 이 작업 동안 대상 테이블에 SHARE 락을 건다. 읽기는 되지만 INSERT, UPDATE, DELETE가 전부 대기한다. 수백만 행짜리 테이블이면 빌드가 수 분에서 수십 분 걸릴 수 있고, 그 시간 내내 쓰기가 막힌다.

예를 들어 게시글 500만 건이 쌓인 posts 테이블에 author_id 인덱스를 일반 방식으로 걸면, 빌드가 끝날 때까지 새 글 작성과 댓글 저장이 전부 멈춘다. 애플리케이션 쪽에서는 커넥션이 쌓이다 타임아웃으로 터진다.

2. CREATE INDEX CONCURRENTLY로 거는 방법

CONCURRENTLY 옵션을 붙이면 PostgreSQL이 테이블을 두 번 스캔하는 방식으로 인덱스를 만든다. 첫 스캔으로 기존 행을 색인하고, 두 번째 스캔으로 그 사이 변경된 행을 반영한 뒤 인덱스를 유효 상태로 전환한다. 이 과정에서 쓰기를 막는 강한 락 대신 SHARE UPDATE EXCLUSIVE 락만 잡으므로 INSERT/UPDATE/DELETE가 계속 돌아간다.

sarc=> CREATE INDEX CONCURRENTLY idx_posts_author ON posts (author_id);
CREATE INDEX

대가는 시간이다. 두 번 스캔하는 만큼 일반 빌드보다 오래 걸리고 CPU와 I/O를 더 쓴다. 대신 그동안 서비스는 정상 동작한다.

주의할 제약이 하나 있다. CREATE INDEX CONCURRENTLY는 트랜잭션 블록 안에서 실행할 수 없다. psql에서 손으로 치면 기본이 autocommit이라 문제없지만, 마이그레이션 도구가 변경 하나하나를 BEGIN ... COMMIT으로 감싸면 다음 에러가 난다.

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Flyway라면 해당 마이그레이션에 -- flyway:executeInTransaction=false를, Liquibase라면 runInTransaction="false"를 지정해 트랜잭션 래핑을 꺼야 한다. 파티션된 테이블은 상위 테이블에 바로 concurrently 빌드를 못 건다. 파티션별로 각각 건 뒤 상위에 일반 CREATE INDEX로 묶는 방식으로 돌아가야 한다.

3. 진행 상황 지켜보기

대용량 테이블에서는 빌드가 지금 어디쯤인지 확인하고 싶어진다. PostgreSQL 12부터 pg_stat_progress_create_index 뷰로 단계와 진척도를 볼 수 있다. 빌드를 실행한 창은 그대로 두고, 다른 세션에서 조회한다.

sarc=> SELECT relid::regclass AS table, phase,
          round(100.0 * blocks_done / NULLIF(blocks_total,0), 1) AS pct
       FROM pg_stat_progress_create_index;
 table |              phase               | pct
-------+----------------------------------+------
 posts | building index: scanning table   | 42.7

phase는 waiting for writers before build, building index, waiting for old snapshots 같은 값으로 바뀐다. 여기서 한 가지 함정을 자주 만난다. CONCURRENTLY 빌드는 자기보다 먼저 시작된 트랜잭션이 끝나기를 기다린다. 오래 열려 있는 트랜잭션이나 idle in transaction 세션이 하나라도 있으면 waiting 단계에서 하염없이 멈춰 보인다. 빌드가 안 끝난다 싶으면 pg_stat_activity에서 장수 트랜잭션부터 찾아 정리한다.

sarc=> SELECT pid, state, now() - xact_start AS age, query
       FROM pg_stat_activity
       WHERE state <> 'idle' AND xact_start IS NOT NULL
       ORDER BY age DESC;

4. 실패하면 INVALID 인덱스가 남는다

CONCURRENTLY 빌드는 중간에 깨질 수 있다. 유니크 제약 위반, 데드락, 세션 강제 종료, 디스크 부족 같은 상황이다. 이때 명령은 실패하지만 미완성 인덱스가 테이블에 그대로 남는다. \d로 보면 INVALID 꼬리표가 붙어 있다.

sarc=> \d posts
...
Indexes:
    "idx_posts_author" btree (author_id) INVALID

이 INVALID 인덱스는 조회에 쓰이지 않는다. 미완성이라 결과가 틀릴 수 있어 플래너가 무시한다. 문제는 그냥 두면 손해만 본다는 점이다. INSERT/UPDATE 때 갱신 부담은 그대로 지면서 조회 성능 이득은 없다. 유니크 인덱스였다면 INVALID 상태에서도 유니크 제약은 계속 강제하므로, 엉뚱한 중복 에러의 원인이 되기도 한다.

남은 INVALID 인덱스는 카탈로그에서 한 번에 찾는다. pg_index.indisvalid가 유효 여부 컬럼이다.

sarc=> SELECT indexrelid::regclass AS index,
          indrelid::regclass  AS table
       FROM pg_index
       WHERE NOT indisvalid;
       index       | table
-------------------+-------
 idx_posts_author  | posts

정리는 지우고 다시 거는 것이다. 이때도 락을 피하려면 DROP INDEX에 CONCURRENTLY를 붙인다. 역시 트랜잭션 블록 밖에서 실행해야 한다.

sarc=> DROP INDEX CONCURRENTLY idx_posts_author;
DROP INDEX
sarc=> CREATE INDEX CONCURRENTLY idx_posts_author ON posts (author_id);
CREATE INDEX

다시 걸기 전에 실패 원인을 먼저 본다. 유니크 인덱스 빌드가 깨졌다면 테이블에 이미 중복 값이 있다는 뜻이므로, 중복을 정리하지 않고 재시도하면 같은 자리에서 또 실패한다.

5. REINDEX CONCURRENTLY로 기존 인덱스 재빌드

인덱스를 새로 거는 게 아니라 이미 있는 인덱스를 다시 만들어야 할 때도 있다. 인덱스가 비대해져(bloat) 디스크를 과하게 먹거나, 통계와 물리 구조가 어긋나 효율이 떨어진 경우다. 일반 REINDEX는 또 테이블을 잠그므로, 운영 중이라면 REINDEX CONCURRENTLY를 쓴다.

sarc=> REINDEX INDEX CONCURRENTLY idx_posts_author;
REINDEX

sarc=> REINDEX TABLE CONCURRENTLY posts;
REINDEX

동작 방식은 CONCURRENTLY 빌드와 비슷하다. 새 인덱스를 백그라운드로 만들어 유효 상태로 전환한 뒤 옛 인덱스를 떨어뜨린다. 이것도 트랜잭션 블록 안에서는 못 돌리고, 배제(exclusion) 제약용 인덱스나 시스템 카탈로그에는 쓸 수 없다.

6. REINDEX가 중단되면 _ccnew와 _ccold가 남는다

REINDEX CONCURRENTLY가 중간에 실패하면 꼬리표 붙은 인덱스가 남는다. 임시로 만들던 새 인덱스에는 _ccnew, 미처 못 지운 원본에는 _ccold 접미사가 붙는다. 이름 충돌을 피하려고 _ccnew1, _ccold2처럼 숫자가 더 붙기도 한다.

sarc=> \d posts
...
Indexes:
    "idx_posts_author" btree (author_id)
    "idx_posts_author_ccnew" btree (author_id) INVALID

처리 방침은 접미사로 갈린다.

_ccnew (INVALID): 재빌드가 완성되지 못한 임시 인덱스다. DROP INDEX CONCURRENTLY로 지우고 REINDEX CONCURRENTLY를 다시 실행한다.
_ccold: 원본 인덱스인데, 이게 남았다는 것은 재빌드 자체는 성공했고 원본 제거만 안 끝났다는 뜻이다. 그대로 지우면 된다.

sarc=> DROP INDEX CONCURRENTLY idx_posts_author_ccnew;
DROP INDEX

어느 쪽이든 pg_index에서 NOT indisvalid 조건으로 남은 것들을 먼저 확인한 다음 지우는 순서가 안전하다.

7. RDS와 운영에서 챙길 점

RDS PostgreSQL에서도 이 명령들은 그대로 동작한다. 슈퍼유저가 아니어도 인덱스를 소유한 역할이면 실행할 수 있다. 다만 psql 접속이 세션 풀러나 프록시를 거치면 autocommit이 꺼져 있을 수 있으니, \echo :AUTOCOMMIT로 on인지 확인하고 시작한다.

작업 전에 lock_timeout을 짧게 걸어 두면, CONCURRENTLY가 초반에 잡는 가벼운 락조차 오래 대기할 때 일찍 포기하게 만들 수 있다. 빌드 도중 세션이 끊기면 INVALID 인덱스가 남으므로, 긴 빌드는 screen이나 tmux 안에서 돌려 SSH가 끊겨도 세션이 유지되게 한다. 리더 인스턴스에서 돌아가는 장수 분석 쿼리도 빌드를 지연시킬 수 있으니, 대규모 배치 시간대는 피해서 거는 편을 권한다.

8. 정리

운영 중인 PostgreSQL 대용량 테이블에 인덱스를 손대야 한다면 기본은 CONCURRENTLY다. 새로 걸 때는 CREATE INDEX CONCURRENTLY, 기존 것을 다시 만들 때는 REINDEX CONCURRENTLY를 쓰고, 둘 다 트랜잭션 블록 밖에서 실행한다. 실패하면 INVALID 인덱스나 _ccnew/_ccold 잔재가 남으니, 작업 뒤 pg_index를 한 번 훑어 남은 것을 DROP INDEX CONCURRENTLY로 정리하는 것까지가 한 세트다.