PostgreSQL에서 무중단 인덱스 변경: 안전한 플레이북

인덱스 변경이 다운타임을 일으키는 이유(그리고 피하는 법)

인덱스 작업은 겉보기엔 무해해 보입니다. 단지 보조 구조를 하나 추가하는 것뿐이죠. 하지만 PostgreSQL에서는 인덱스를 만들거나 삭제하거나 교체할 때 다른 세션을 막는 락을 잡을 수 있습니다. 테이블이 바쁘다면 대기들이 쌓이고 애플리케이션이 망가진 것처럼 느껴지기 시작합니다.

다운타임은 깔끔한 서비스 중단 배너처럼 보이는 경우가 드뭅니다. 보통은 페이지가 멈추거나 백그라운드 잡이 밀리고, 데이터베이스를 기다리느라 요청 대기열이 커지는 식으로 드러납니다. 누군가 "검색"을 눌렀는데 타임아웃이 나고, 간단한 쿼리도 필요한 락을 얻지 못해 지원 도구나 관리자 화면이 갑자기 느려집니다.

"그냥 밤에 돌려라"는 두 가지 흔한 이유로 실패합니다. 많은 시스템은 진짜로 조용한 시간이 없고(글로벌 사용자, 배치 잡, ETL, 백업 등), 인덱스 작업은 많은 데이터를 읽고 CPU·디스크를 경쟁하기 때문에 예상보다 오래 걸릴 수 있습니다. 윈도우가 빌드 도중 끝나면 기다릴 것인지 작업을 중단할 것인지 선택해야 합니다.

무중단 인덱스 변경은 마법이 아닙니다. 가장 적게 차단하는 작업을 고르고, 보호 장치(타임아웃과 디스크 체크)를 설정하고, 실행 중인 데이터베이스를 지켜보는 일로 귀결됩니다.

이 플레이북은 실무에서 쓸 수 있는 습관에 초점을 맞춥니다:

• 읽기와 쓰기를 계속 유지해야 할 때는 동시성(concurrent) 빌드를 선호하세요.
• 락과 빌드 진행 상황을 모니터링해서 초기에 대응하세요.
• 변경이 문제를 일으키거나 너무 오래 걸리면 롤백 경로를 준비하세요.

다루지 않는 것: 심도 있는 인덱스 설계 이론, 폭넓은 쿼리 튜닝, 또는 많은 데이터를 다시 쓰는 스키마 리팩터입니다.

인덱스 작업 뒤에 있는 단순한 락 모델

PostgreSQL은 여러 세션이 같은 테이블을 건드릴 때 데이터 일관성을 유지하기 위해 락을 사용합니다. 락은 지금 누가 객체를 읽거나 쓸 수 있는지, 누가 기다려야 하는지에 대한 규칙일 뿐입니다.

대부분의 경우에는 락을 거의 느끼지 못합니다. PostgreSQL이 일반 쿼리가 실행되도록 가벼운 모드를 대부분 사용할 수 있기 때문입니다. DDL은 다릅니다. 인덱스를 만들거나 삭제할 때 PostgreSQL은 카탈로그와 데이터를 일관되게 유지하기 위해 테이블에 대해 더 많은 제어가 필요합니다. 더 많은 제어가 필요할수록 다른 세션은 더 오래 기다려야 할 수 있습니다.

인덱스를 사용하는 것 vs 인덱스를 만드는 것

인덱스를 사용하는 쿼리는 보통 락 관점에서 비용이 적습니다. SELECT, UPDATE, DELETE는 다른 세션이 같은 작업을 하는 동안에도 인덱스를 읽거나 유지할 수 있습니다.

인덱스를 만드는 것은 다릅니다. PostgreSQL은 테이블을 스캔하고, 키를 정렬하거나 해시하고, 새로운 구조를 디스크에 써야 합니다. 그 작업은 시간이 걸리고, 시간이 쌓이면 운영 환경에서 "작은 락"이 "큰 문제"로 변합니다.

CONCURRENTLY가 바꾸는 것(그리고 바꾸지 않는 것)

일반적인 CREATE INDEX는 쓰기를 장기간 차단하는 강한 락을 잡습니다. CREATE INDEX CONCURRENTLY는 인덱스를 빌드하는 동안 일반 읽기와 쓰기가 계속 흐르도록 설계되었습니다.

하지만 "concurrent"가 "락이 전혀 없다"는 뜻은 아닙니다. 시작과 끝에 짧은 락 창은 여전히 존재하고, 빌드가 실패하거나 다른 작업이 호환되지 않는 락을 잡고 있으면 대기할 수 있습니다.

중요한 결과들:

• 비동시(non-concurrent) 빌드는 테이블의 삽입, 업데이트, 삭제를 차단할 수 있습니다.
• 동시 빌드는 보통 읽기와 쓰기를 허용하지만 장기 트랜잭션에 의해 느려지거나 멈출 수 있습니다.
• 완료 단계는 여전히 잠깐의 락을 필요로 하므로 매우 바쁜 시스템에서는 짧은 대기가 발생할 수 있습니다.

올바른 접근법 고르기: 동시성 또는 일반 빌드

인덱스를 변경할 때 선택지는 두 가지입니다: 일반 빌드(빠르지만 차단함) 또는 CONCURRENTLY로 빌드(보통 애플리케이션 트래픽을 차단하지 않지만 느리고 장기 트랜잭션에 민감함).

CONCURRENTLY가 적절한 경우

테이블에 실제 트래픽이 있고 쓰기를 멈출 수 없다면 CREATE INDEX CONCURRENTLY를 사용하세요. 일반적으로 다음과 같은 경우 더 안전한 선택입니다:

• 일반 빌드가 몇 분 또는 몇 시간이 걸릴 만한 큰 테이블일 때.
• 테이블에 읽기뿐 아니라 일정한 쓰기가 있을 때.
• 진짜 유지보수 윈도우를 잡을 수 없을 때.
• 먼저 인덱스를 만들고 검증한 다음 오래된 인덱스를 제거해야 할 때.

일반 인덱스 빌드가 허용되는 경우

테이블이 작거나 트래픽이 적거나 제어된 윈도우가 있을 때는 일반 CREATE INDEX가 괜찮을 수 있습니다. 보통 더 빨리 끝나고 실행이 단순합니다.

스테이징에서 빌드가 꾸준히 빠르고 잠시나마 쓰기를 멈출 수 있다면 일반 방식을 고려하세요.

유니크가 필요하다면 초기에 결정하세요. CREATE UNIQUE INDEX CONCURRENTLY는 동작하지만 중복 값이 있으면 실패합니다. 많은 프로덕션 시스템에서는 중복을 찾고 고치는 일이 실제 프로젝트가 됩니다.

프로덕션에서 건드리기 전 체크리스트

대부분의 문제는 명령을 시작하기 전 단계에서 발생합니다. 몇 가지 점검으로 두 가지 큰 놀라움, 즉 예상치 못한 블로킹과 계획보다 훨씬 오래 또는 더 많은 공간을 쓰는 인덱스 빌드를 피할 수 있습니다.

1. 트랜잭션 내부에 있지 않은지 확인하세요. CREATE INDEX CONCURRENTLY는 BEGIN 이후에 실행하면 실패합니다. 일부 GUI 도구는 조용히 문장을 트랜잭션으로 감쌀 수 있으니 확실하지 않다면 새 세션을 열고 인덱스 명령만 실행하세요.

2. 시간과 디스크에 대한 기대를 설정하세요. 동시 빌드는 보통 일반 빌드보다 오래 걸리고 실행 중 추가 작업 공간이 필요합니다. 새 인덱스 크기와 임시 오버헤드를 계획하고, 편안한 여유 디스크 용량이 있는지 확인하세요.

3. 목표에 맞는 타임아웃을 설정하세요. 락을 얻지 못하면 빨리 실패하길 원하지만 공격적인 statement timeout 때문에 빌드 중간에 세션이 죽지 않게 해야 합니다.

4. 기준선을 캡처하세요. 변경이 도움이 되었는지 증거가 필요하고 회귀를 빠르게 발견할 방법이 필요합니다. 변경 전 스냅샷을 기록하세요: 느린 쿼리 타이밍, 대표적인 EXPLAIN (ANALYZE, BUFFERS), 그리고 CPU·IO·커넥션·여유 디스크의 빠른 상태 등.

많은 팀이 출발점으로 쓰는 안전한 세션 설정(자신의 규칙에 맞게 조정하세요):

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

단계별: CONCURRENTLY로 인덱스 생성하기

애플리케이션 트래픽을 계속 유지해야 하고 더 긴 빌드 시간을 감수할 수 있다면 CREATE INDEX CONCURRENTLY를 사용하세요.

먼저 정확히 무엇을 만들지 결정하세요:

• 컬럼 순서를 구체적으로 정하세요(중요합니다).
• 부분 인덱스(partial index)가 충분한지 고려하세요. 대부분의 쿼리가 active 행을 필터링한다면 부분 인덱스가 더 작고 빠르며 유지비가 적을 수 있습니다.

안전한 실행 예시는 다음과 같습니다: 목표와 인덱스 이름을 적고, 트랜잭션 블록 밖에서 빌드를 실행하고, 완료될 때까지 관찰하고, 다른 것을 제거하기 전에 플래너가 해당 인덱스를 사용할 수 있는지 검증하세요.

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

감사용 메모(감사에 유용): 시작 시간, 종료 시간, 관찰된 대기 등을 기록하세요. 실행 중에는 다른 세션에서 pg_stat_progress_create_index를 조회할 수 있습니다.

검증은 단순히 "인덱스가 존재한다"가 아닙니다. 플래너가 선택할 수 있는지 확인하고, 배포 후 실제 쿼리 타이밍을 관찰하세요. 새 인덱스를 사용하지 않는다면 기존 인덱스를 급히 제거하지 마세요. 쿼리나 인덱스 정의를 먼저 고치세요.

단계별: 차단 없이 인덱스 교체 또는 제거하기

가장 안전한 패턴은 먼저 새 인덱스를 추가하고 트래픽이 새 인덱스를 사용하도록 한 뒤에 기존 인덱스를 제거하는 것입니다. 이렇게 하면 작동하는 롤백을 유지할 수 있습니다.

오래된 인덱스를 새 인덱스로 교체하는 안전한 순서

1. CREATE INDEX CONCURRENTLY로 새 인덱스를 만듭니다.

2. 그것이 사용되는지 검증합니다. 느린 쿼리에서 EXPLAIN을 확인하고, 인덱스 사용량을 시간 경과로 관찰하세요.

3. 그 후에만 오래된 인덱스를 동시성으로 삭제합니다. 위험이 높다면 한 비즈니스 사이클 동안 두 인덱스를 모두 유지하세요.

인덱스 삭제: 언제 CONCURRENTLY가 동작하고 언제 동작하지 않는가

직접 만든 일반 인덱스라면 DROP INDEX CONCURRENTLY가 보통 올바른 선택입니다. 두 가지 주의점: 트랜잭션 블록 안에서는 실행할 수 없고, 시작과 끝에 짧은 락이 필요하므로 장기 트랜잭션에 의해 지연될 수 있습니다.

인덱스가 PRIMARY KEY나 UNIQUE 제약을 위해 존재한다면 보통 직접 삭제할 수 없습니다. ALTER TABLE로 제약을 변경해야 하고 이 작업은 더 강한 락을 필요로 할 수 있으므로 별도로 계획하세요.

가독성을 위한 인덱스 이름 변경

ALTER INDEX ... RENAME TO ...는 보통 빠르지만 툴링이나 마이그레이션에서 인덱스 이름을 참조한다면 피하세요. 처음부터 명확한 이름을 정하는 것이 안전한 습관입니다.

오래된 인덱스가 여전히 필요할 때

때로는 서로 다른 두 쿼리 패턴이 두 개의 다른 인덱스를 필요로 합니다. 중요한 쿼리가 여전히 오래된 인덱스에 의존한다면 그것을 유지하세요. 새 인덱스를 강제로 드롭시키기보다(혹은 강제로 대체하기보다) 새 인덱스를 조정하는 것을 고려하세요(컬럼 순서, 부분 조건 등).

인덱스 빌드 중 락과 진행 상황 모니터링하기

CREATE INDEX CONCURRENTLY를 사용하더라도 실시간으로 무슨 일이 일어나는지 지켜봐야 합니다. 대부분의 놀라운 사고는 눈치채지 못한 블로킹 세션이나 빌드를 계속 대기시키는 장기 트랜잭션에서 옵니다.

누가 누구를 막는지 찾기

락을 기다리는 세션들을 먼저 찾으세요:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

정확한 블로커가 필요하면 blocked_pid에서 blocking_pid를 따라가세요:

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

빌드 진행과 "멈춤" 신호 보기

PostgreSQL은 인덱스 빌드 진행을 노출합니다. 오랜 시간 동안 진행이 전혀 없으면 보통 오래 실행 중인 트랜잭션(종종 오래된 스냅샷을 가진 idle 세션)을 찾아보세요.

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

또한 시스템 압력(디스크 IO, 복제 지연, 증가하는 쿼리 시간)을 주시하세요. 동시 빌드는 가용성을 더 잘 지켜주지만 많은 데이터를 읽습니다.

운영에서 잘 먹히는 간단한 규칙:

• 진행이 이루어지고 사용자 영향이 적으면 기다리세요.
• 빌드가 끝없이 장기 트랜잭션에 막혀 있고 안전하게 끝낼 수 없다면 취소하고 재일정 잡으세요.
• 피크 트래픽 동안 IO가 고객-facing 쿼리를 해친다면 중단하세요.
• 최후 수단으로만 종료하고, 해당 세션이 무엇을 하는지 확인한 뒤에 실행하세요.

팀 커뮤니케이션은 짧게 유지하세요: 시작 시간, 현재 단계, 무엇이 막고 있는지(있다면), 다음 확인 시점을 적으세요.

롤백 계획: 안전하게 되돌리는 방법

인덱스 변경은 시작 전에 나올 방법을 계획할 때만 저위험으로 머물 수 있습니다. 가장 안전한 롤백은 극적인 되돌림이 아닐 때가 많습니다. 새 작업을 중단하고 오래된 인덱스를 유지하는 것이 가장 안전한 경우가 많습니다.

인덱스 작업이 실패하는 일반적 원인

대부분의 프로덕션 실패는 예측 가능합니다: 빌드가 타임아웃에 걸리거나, 누군가 사고 중에 취소하거나, 서버 디스크가 부족해지거나, 빌드가 정상 트래픽과 경쟁해서 사용자 지연이 커지는 경우 등입니다.

CREATE INDEX CONCURRENTLY의 경우 빌드를 취소해도 앱에는 보통 안전합니다(쿼리는 계속 실행됨). 대신 정리 작업이 필요합니다: 취소되거나 실패한 동시 빌드는 유효하지 않은 인덱스를 남길 수 있습니다.

안전한 취소 및 정리 규칙

동시 빌드를 취소해도 일반 트랜잭션처럼 롤백되지 않습니다. PostgreSQL은 존재하지만 플래너에 유효하지 않은 인덱스를 남길 수 있습니다.

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(<pid>);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

삭제하기 전에 상황을 확인하세요:

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'your_index_name';

indisvalid = false이면 플래너가 사용하지 않으므로 안전하게 드롭할 수 있습니다.

기존 인덱스를 교체할 때의 현실적인 롤백 체크리스트:

• 새 인덱스가 완전히 빌드되고 유효해질 때까지 기존 인덱스를 유지하세요.
• 새 빌드가 실패하거나 취소되면, 불완전한 새 인덱스를 DROP INDEX CONCURRENTLY로 삭제하세요.
• 이미 오래된 인덱스를 삭제했다면 이전 상태로 복원하려면 CREATE INDEX CONCURRENTLY로 다시 만드세요.
• 디스크 부족이 실패 원인이면 먼저 공간을 확보한 뒤 재시도하세요.
• 타임아웃이 실패 원인이면 조용한 시간대를 다시 잡으세요.

예: 관리자 검색을 위한 새 인덱스를 시작했는데 20분 동안 실행된 뒤 디스크 경고가 떴다고 합시다. 빌드를 취소하고 불완전한 인덱스를 동시성으로 삭제한 뒤 오래된 인덱스를 그대로 두면 사용자에게 보이는 가용성 문제 없이 공간 확보 후 다시 시도할 수 있습니다.

놀라운 장애를 만드는 흔한 실수

인덱스 관련 장애의 대부분은 PostgreSQL 자체가 "느리다"는 이유가 아니라, 작은 세부 사항 하나가 안전한 변경을 차단하는 변경으로 바꿀 때 발생합니다.

1) 동시 빌드를 트랜잭션 안에 넣는 것

CREATE INDEX CONCURRENTLY는 트랜잭션 블록 내에서 실행할 수 없습니다. 많은 마이그레이션 도구가 기본적으로 모든 변경을 하나의 트랜잭션으로 묶습니다. 결과는 최선의 경우에는 단순한 오류지만, 배포 중 재시도와 혼란을 초래할 수 있습니다.

마이그레이션을 실행하기 전에 도구가 외부 트랜잭션 없이 문장을 실행할 수 있는지 확인하거나, 해당 마이그레이션을 특별한 비트랜잭션(non-transactional) 단계로 분리하세요.

2) 피크 트래픽 중에 시작하는 것

동시 인덱스 빌드는 차단을 줄이지만 부하를 추가합니다: 추가 읽기, 추가 쓰기, autovacuum 압력 증가 등. 배포 창에서 트래픽이 급증하는 시점에 빌드를 시작하면 느려짐이 발생해 마치 장애처럼 느껴질 수 있습니다.

조용한 기간을 선택하고 다른 프로덕션 유지보수처럼 다루세요.

3) 장기 트랜잭션 무시하기

하나의 장기 트랜잭션이 동시 빌드의 정리 단계(cleanup phase)를 막을 수 있습니다. 인덱스가 진행되는 것처럼 보이다가 끝 부분에서 오래 멈추는 경우가 많습니다.

시작 전에 장기 실행 트랜잭션을 확인하고, 진행이 지연되면 다시 확인하는 습관을 들이세요.

4) 잘못된 객체를 삭제하거나 제약 조건을 깨뜨리는 것

팀은 종종 기억만으로 인덱스 이름을 입력해 삭제하거나 고유 제약을 지원하는 인덱스를 실수로 제거합니다. 잘못된 객체를 삭제하면 제약(유니크 등)을 잃거나 즉시 쿼리 성능이 악화될 수 있습니다.

빠른 안전 체크리스트: 카탈로그에서 인덱스 이름을 확인하고, 제약을 지원하는지 여부를 확인하고, 스키마와 테이블을 재확인하세요. "새로 만들기"와 "옛 것 삭제"를 분리하고 시작 전에 롤백 명령을 준비하세요.

현실적인 예: 관리자 검색 속도 향상

스테이징에서는 즉각적인 관리자 검색이 프로덕션에서는 느리게 동작하는 경우가 흔합니다. 예를 들어 내부 관리자 패널 뒤에 수천만 행의 tickets 테이블이 있고, 상담원들은 종종 "한 고객의 열린 티켓 최신순"을 검색한다고 합시다.

쿼리는 다음과 같습니다:

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

(customer_id, status, created_at)에 대한 전체 인덱스는 도움이 되지만 모든 티켓 업데이트마다 쓰기 오버헤드가 생깁니다(닫힌 티켓도 포함). 대부분의 행이 open이 아니라면 부분 인덱스가 더 간단한 해법이 되는 경우가 많습니다:

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

프로덕션에서 안전한 타임라인:

• 사전 점검: 쿼리 형태가 안정적인지, 새 인덱스 빌드를 위한 충분한 디스크 여유가 있는지 확인합니다.
• 빌드: 별도 세션에서 타임아웃 설정을 명확히 하고 CREATE INDEX CONCURRENTLY를 실행합니다.
• 검증: ANALYZE tickets;를 실행하고 플래너가 새 인덱스를 사용하는지 확인합니다.
• 정리: 확신이 서면 DROP INDEX CONCURRENTLY로 중복된 오래된 인덱스를 제거합니다.

성공 기준:

• 관리자 검색 시간이 수초에서 수십 밀리초로 내려갑니다.
• 빌드 중에도 일반 읽기·쓰기는 계속 동작합니다.
• 빌드 동안 CPU와 디스크 IO는 상승하지만 정상 안전 한계 내에 있습니다.
• 쿼리 시간, 스캔된 행 수, 락 이력 등 전후 비교 가능한 수치가 있습니다.

빠른 체크리스트와 다음 단계

인덱스 작업은 작은 프로덕션 릴리스처럼 준비하고, 실행 중 지켜보고, 결과를 검증한 뒤 정리할 때 가장 안전합니다.

시작 전에:

• 락이 영원히 걸리지 않게 타임아웃을 설정하세요.
• 새 인덱스 빌드를 위한 충분한 여유 디스크를 확인하세요.
• 빌드를 지연시킬 수 있는 장기 트랜잭션을 확인하세요.
• 트래픽이 적은 윈도우를 선택하고 "완료" 기준을 정의하세요.
• 지금 롤백 계획을 적어두세요.

실행 중:

• 블로킹과 락 대기 체인을 관찰하세요.
• pg_stat_progress_create_index로 진행 상황을 추적하세요.
• 앱 증상(오류율, 타임아웃, 테이블에 연결된 느린 엔드포인트)을 주시하세요.
• 락 대기가 늘거나 사용자 타임아웃이 급증하면 취소할 준비를 하세요.
• 무슨 일이 있었는지(시작 시간, 종료 시간, 경고)를 기록하세요.

완료 후에는 인덱스가 유효한지 확인하고 핵심 쿼리 몇 개를 실행해 플랜과 타이밍이 개선되었는지 확인한 뒤에만 옛 인덱스를 비차단 방식으로 제거하세요.

이 작업을 여러 번 한다면 반복 가능한 전달 단계로 만드세요: 작은 런북, 프로덕션과 유사한 데이터로 스테이징 리허설, 빌드를 관찰할 명확한 담당자.

AppMaster (appmaster.io)로 내부 도구나 관리자 패널을 만들 경우, 데이터베이스 변경을 인덱스 빌드처럼 백엔드 업데이트와 동일한 릴리스 체크리스트의 일부로 다루면 도움이 됩니다: 측정되고, 모니터링되며, 빠르게 실행할 수 있는 롤백이 준비된 상태로요.