PostgreSQL
PostgreSQL: 온라인 인덱스 작업(CONCURRENTLY)
dewstream
2025. 7. 14. 08:00
728x90
※ PostgreSQL: Performing Index Operations Online Using CONCURRENTLY.
안녕하세요. 듀스트림입니다.
또 한 주가 시작되었습니다.
오늘 포스팅은 운영 서비스 중에도 쓰기 중단 없이 인덱스를 추가·재구성·삭제할 수 있게 해주는 CONCURRENTLY 옵션에 대한 내용입니다.
CREATE INDEX
CREATE INDEX CREATE INDEX — define a new index Synopsis CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ …
www.postgresql.org
1. CONCURRENTLY 옵션?
- 목적: DDL 실행 중에도 INSERT/UPDATE/DELETE를 차단하지 않고 인덱스를 생성·재구성·삭제할 수 있게 합니다.
- 기본 동작: 표준 DDL이 잡는 ACCESS EXCLUSIVE 락 대신 짧게 SHARE UPDATE EXCLUSIVE → ACCESS SHARE 수준의 락만 사용합니다.
- 리스크: 두 번의 테이블-풀스캔, 오래 살아 있는 트랜잭션 대기, 추가 디스크 소비 등으로 작업 시간이 더 길어집니다.
두 번 스캔?
첫 스캔에서 인덱스를 구축, 두 번째 스캔에서 첫 스캔 중 놓친 변경 튜플을 보강합니다.
이후 old snapshot 소멸 대기 → indisvalid 전환.
2. 명령어별 상세 동작
▸ CREATE INDEX CONCURRENTLY
- INVALID 인덱스 메타데이터 작성
- 1차 스캔: 모든 튜플 인덱싱
- 트랜잭션 정리 대기
- 2차 스캔: 놓친 튜플 보강
- 과거 Snapshot 소멸 대기
- 인덱스 VALID 마킹
▸ REINDEX INDEX CONCURRENTLY (12+)
- 새 인덱스를 별도 OID로 작성 후 메타데이터 스왑 → old 인덱스 제거
▸ DROP INDEX CONCURRENTLY
- 장기 트랜잭션 종료 대기 → 인덱스 제거
+ PK·UNIQUE 제약 변경
- 직접 DROP CONSTRAINT CONCURRENTLY는 없습니다.
- 우회 방법은 아래와 같은 순서로 수행하시면 됩니다.
- CREATE UNIQUE INDEX CONCURRENTLY
- ALTER TABLE … ADD CONSTRAINT … USING INDEX
- 기존 제약 삭제
3. 단계별 흐름
▸ 락·동시성 메커니즘
| 단계 | 테이블에 허용되는 작업 |
| 인덱스 초기 등록 | 읽기·쓰기 모두 가능 (메타데이터 잠금만 짧게) |
| 1차 스캔 | 읽기·쓰기 모두 가능 |
| 대기 구간 (기존 트랜잭션 종료) | 새 쓰기는 허용, 단 대기 중 트랜잭션에 의해 완료가 지연될 수 있음 |
| 2차 스캔 | 읽기·쓰기 모두 가능 |
| VALID 전환 | 읽기·쓰기 모두 가능 (짧은 카탈로그 락) |
동시 실행 중인 긴 트랜잭션(ETL, 배치, VACUUM FULL 등)이 있으면 전체 작업이 오래 대기하게 됩니다.
작업을 미리 종료하거나 lock_timeout 조정이 필요합니다.
+ Autovacuum은 보통 Share update exclusive lock을 잡고 테이블을 처리합니다.
그래서 Autovacuum이 발생한 시점에 CONCURRENTLY INDEX도 Share update exclusive lock을 얻으려고 하면 PostgreSQL은 Lock 경합 때문에 대기 상태가 발생합니다. (이럴때 보통은 Autovacuum이 끝나면 바로 다시 진행됩니다.)
▸ pg_index 카탈로그 플래그 변화
| 단계 | indisready | indisvalid | 의미 |
| 메타데이터 생성 | FALSE | FALSE | 중간 객체, 매커니즘 준비 중 |
| 첫 스캔 완료 | TRUE | FALSE | 쿼리 플래너가 아직 사용 X |
| old snapshot 대기 후 | TRUE | TRUE | 정식 인덱스 활성화 |
4. 자원 비용
| 항목 | 영향 |
| CPU / I/O | 테이블 풀 스캔 2 회 + WAL 기록 증가 |
| 디스크 공간 | 빌드 중 새 인덱스와 구 인덱스(또는 INVALID 인덱스)가 동시에 존재 |
| 시간 | 일반 빌드 대비 수 배 길어질 수 있음 (테이블 크기·VACUUM 상태·트랜잭션 수명에 따라 상이) |
| 복구 전략 | 실패 시 DROP INDEX 후 재시도 또는 REINDEX INDEX CONCURRENTLY |
5. 테스트
▸ 모니터링 방법
-- INDEX CREATE
SELECT pid, phase, blocks_done, blocks_total
FROM pg_stat_progress_create_index
WHERE relid = 'table_name'::regclass;
-- TABLE LOCK
SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t
WHERE l.relation = t.relid
AND t.relname='table_name'
ORDER BY relation ASC;
-- idle in transaction
SELECT pid, state, query FROM pg_stat_activity WHERE state = 'idle in transaction';
▸ Case1: 정상 빌드
-- 환경 준비
CREATE TABLE cc_demo (
id bigserial PRIMARY KEY,
name text NOT NULL
);
INSERT INTO cc_demo (name)
SELECT 'user_' || gs FROM generate_series(1, 500000) gs;
-- 인덱스 온라인 생성
CREATE INDEX CONCURRENTLY idx_cc_demo_name ON cc_demo (name);
▸ Case2: INVALID INDEX
-- 중복 레코드 삽입
INSERT INTO cc_demo (name) VALUES ('dup'), ('dup');
-- UNIQUE 인덱스 생성 (실패)
CREATE UNIQUE INDEX CONCURRENTLY idx_cc_demo_name_u ON cc_demo (name);
-- 남은 INVALID 인덱스 확인
SELECT indexrelid::regclass, indisready, indisvalid
FROM pg_index
WHERE indexrelid = 'idx_cc_demo_name_u'::regclass;
-- + 중복 데이터 제거 후 아래 작업
REINDEX INDEX CONCURRENTLY idx_cc_demo_name_u; -- 17+ 버전
▸ Case3: Transaction Lock
-- Session 1: 장기 쓰기 트랜잭션
BEGIN;
UPDATE cc_demo SET name = name WHERE id = 1; -- row‑lock + snapshot 고정
-- Session 2: 온라인 인덱스 시도
CREATE INDEX CONCURRENTLY idx_cc_demo_name2 ON cc_demo (name);
-- pg_stat_activity 에서 state='idle in transaction' 세션 waiting 확인
-- Session 1: 커밋
COMMIT;
6. 모니터링 & 튜닝 팁
| 항목 | 방법 |
| 진행률 | pg_stat_progress_create_index (phase, blocks_done) |
| 대기 세션 | pg_locks, pg_stat_activity (wait_event_type = Lock) |
| 디스크 소비 | pg_total_relation_size() (old·new 인덱스 동시 존재) |
| 취소·재시도 | DROP INDEX CONCURRENTLY … 후 재시도 또는 REINDEX … CONCURRENTLY |
| 파티션 | 파티션 테이블(부모 테이블)에는 사용 불가 (각 파티션별 개별 생성 필요) |
• maintenance_work_mem, parallel_workers 조정으로 구축 속도 최적화
• idle in transaction 세션 사전 종료 (idle_in_transaction_session_timeout)
- 필요한 경우에만 사용: 대형 테이블, 24×7 시스템 등 쓰기 중단 비용 > 느려지는 비용 일 때.
- 테스트 환경에서 미리 소요 시간·디스크를 계측.
- 트래픽 적은 시간대 배치 + 모니터링 스크립트 자동화.
- 실패 대비: INVALID 인덱스 탐지 → 자동 DROP/REINDEX 재시도 루틴 구비.
- 복제 지연 주의: 대용량 WAL 발생으로 Hot‑Standby replica lag 체크.
7. PostgreSQL vs Oracle 온라인 인덱스 비교
▸ 문법
| 항목 | PostgreSQL | Oracle |
| 인덱스 생성 | CREATE [UNIQUE] INDEX CONCURRENTLY … | CREATE [UNIQUE] INDEX … ONLINE |
| 인덱스 재구성 | REINDEX INDEX CONCURRENTLY … (v17+) | ALTER INDEX … REBUILD ONLINE |
| 인덱스 삭제 | DROP INDEX CONCURRENTLY … | (온라인 삭제 미지원) |
| 최초 지원 버전 | 8.2 (CONCURRENTLY) / 12 & 17 (REINDEX) | 10gR2 (ONLINE=YES) |
▸ 내부 구현 차이
- DML 캡처 방식
- PostgreSQL: 두 번의 테이블 풀 스캔 + old snapshot 대기로 놓친 튜플 보강.
- Oracle: ONLINE 시 내부 journal table과 undo로 DML 로그를 버퍼링한 뒤 인덱스에 반영.
- 락 범위
- PostgreSQL: ACCESS SHARE (읽기)만 유지 → DML 완전 허용, 일부 DDL 대기.
- Oracle: ROW SHARE → SHARE 수준 잠금으로 DML 허용, DDL 전체 차단.
- PostgreSQL: ACCESS SHARE (읽기)만 유지 → DML 완전 허용, 일부 DDL 대기.
- 실패 시 처리
- PostgreSQL: indisvalid = FALSE INVALID 인덱스 보존 → DROP or REINDEX 필요.
- Oracle: 작업 롤백, 인덱스 생성 안 됨.
- 제약(UNIQUE/PK) 연계
- PostgreSQL: 인덱스 생성 → ALTER TABLE … ADD CONSTRAINT … USING INDEX
- Oracle: 인덱스 또는 제약을 ONLINE 으로 직접 생성 가능.
▸ 성능·운영상 고려 사항
| 항목 | PostgreSQL | Oracle |
| 병렬 처리 | parallel_workers / maintenance_work_mem | PARALLEL <n> 키워드 |
| 진행률 모니터링 | pg_stat_progress_create_index | V$SESSION_LONGOPS, DBA_INDEXES.STATUS |
| 디스크 소비 | new+old 인덱스 동시 보관 → index size ×2 | 임시 세그먼트 + online redo/undo 증가 |
| 중복 데이터 대처 | UNIQUE 빌드 중 오류 → INVALID 남음 | 즉시 실패 & 전체 롤백 |
PostgreSQL에서는 pg_repack, CLUSTER가 Rebuild 대안.
Oracle은 COALESCE, SHRINK SPACE, MOVE ONLINE 등 다단계 유지보수 옵션이 추가로 존재.
오늘은 여기까지~
728x90