0Pricing
SQL Academy · 강의

동시 인덱스 생성(CREATE INDEX CONCURRENTLY)

CREATE INDEX CONCURRENTLY를 사용해 쓰기를 차단하지 않고 인덱스를 만들고, 중단된 생성 작업에서 복구해보세요.

동시 인덱스 생성(CREATE INDEX CONCURRENTLY)은(는) CoddyKit의 무료 SQL Academy 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Academy 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

CREATE INDEX의 문제

일반적인 CREATE INDEX는 SHARE 잠금을 사용하므로 인덱스를 만드는 전체 시간 동안 쓰기를 차단하지만 조회는 차단하지 않습니다. 10억 개의 행이 있는 테이블에서는 쓰기 중단이 몇 분 동안 발생할 수 있습니다.

CONCURRENTLY: 해결 방법

CREATE INDEX CONCURRENTLY는 쓰기를 차단하지 않고 인덱스를 만듭니다.

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

작동 방식

테이블을 두 번 스캔합니다.

  1. 첫 번째 처리: 현재 행을 기준으로 인덱스를 만듭니다.
  2. 두 번째 처리: 첫 번째 처리 중에 변경된 행을 반영합니다.

또한 인덱스 생성 전에 시작된 동시 트랜잭션이 있다면 잠시 기다립니다.

트랜잭션 내부에서는 실행할 수 없음

CONCURRENTLY는 두 번 COMMIT하므로 명시적인 BEGIN/COMMIT 블록 안에서 실행할 수 없습니다.

BEGIN;
CREATE INDEX CONCURRENTLY ...;  -- ERROR
COMMIT;

느리지만 온라인으로 실행

CONCURRENTLY는 일반적인 CREATE INDEX보다 오래 걸립니다(두 번의 스캔과 잠금 대기 때문입니다). 대신 중단 시간이 전혀 없습니다.

실패 복구

CONCURRENTLY가 중단이나 시스템 오류로 실패하면 인덱스가 INVALID 상태로 남습니다.

SELECT relname FROM pg_class
WHERE relkind = 'i' AND NOT pg_index.indisvalid;

-- Fix: drop and re-create:
DROP INDEX CONCURRENTLY my_idx;
CREATE INDEX CONCURRENTLY my_idx ON ...;

REINDEX CONCURRENTLY

PG 12부터는 차단 없이 다시 만들 수 있습니다.

REINDEX INDEX CONCURRENTLY orders_user_id_idx;
REINDEX TABLE CONCURRENTLY orders;

DROP INDEX CONCURRENTLY

이 작업도 차단 없이 실행됩니다.

DROP INDEX CONCURRENTLY old_idx;

고유 인덱스

UNIQUE 인덱스도 CONCURRENTLY 방식으로 만들 수 있습니다. 온라인으로 UNIQUE 제약 조건을 추가할 때 유용합니다.

CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON users(email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_uidx;

외래 키 인덱스

큰 테이블에 FK를 추가할 때는 다음과 같은 표준 패턴을 사용합니다.

-- 1. Build the index online:
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders(user_id);

-- 2. Add the FK with NOT VALID (instant):
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- 3. Validate in the background:
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

동시 인덱스 생성 비용

테이블 전체를 두 번 스캔하고 부가 정보를 관리해야 합니다. CPU와 IO를 많이 사용하므로 가능하면 트래픽이 적은 시간에 실행하십시오.

진행 상황 모니터링

PG 12 이상에서는 pg_stat_progress_create_index를 통해 진행 상황을 확인할 수 있습니다.

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

요약

CONCURRENTLY는 운영 환경에서 인덱스를 관리하기에 적합한 방식입니다.

  • CREATE/DROP/REINDEX CONCURRENTLY는 쓰기를 차단하지 않습니다.
  • 트랜잭션 내부에서 실행할 수 없습니다.
  • 실패하면 INVALID 인덱스가 남으므로 정리해야 합니다.
  • pg_stat_progress_create_index로 모니터링할 수 있습니다.

빠른 확인

CREATE INDEX CONCURRENTLY는 왜 명시적인 BEGIN/COMMIT 블록 안에서 실행할 수 없습니까?

자주 묻는 질문

“동시 인덱스 생성(CREATE INDEX CONCURRENTLY)” 강의는 무료인가요?

네 — “동시 인덱스 생성(CREATE INDEX CONCURRENTLY)” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 SQL Academy 강의 전체를 잠금 해제할 수 있습니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

“동시 인덱스 생성(CREATE INDEX CONCURRENTLY)”에서 뭘 배우나요?

CREATE INDEX CONCURRENTLY를 사용해 쓰기를 차단하지 않고 인덱스를 만들고, 중단된 생성 작업에서 복구해보세요. 브라우저에서 직접 실행하는 실습 코드로 SQL Academy을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

SQL Academy을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 SQL Academy은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“동시 인덱스 생성(CREATE INDEX CONCURRENTLY)” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 SQL Academy 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 SQL Academy 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. 온라인 마이그레이션: ALTER TABLE이 잠금을 사용하는 이유
  2. 동시 인덱스 생성(CREATE INDEX CONCURRENTLY)
  3. 다운타임 없는 열 이름 변경
  4. 도구: Flyway, Liquibase, Sqitch
← SQL Academy(으)로 돌아가기