PostgreSQL 안전한 스키마 마이그레이션

상위: Database MOC

개요

운영 중인 데이터베이스에 DDL을 거는 것은 조용한 지뢰다. CREATE INDEX, ALTER TABLE은 종류에 따라 강한 락을 잡고, 그 락은 뒤이어 오는 읽기·쓰기를 줄줄이 대기시켜 순식간에 전면 장애로 번진다(lock convoy). PostgreSQL은 이를 피할 두 가지 도구를 준다. 하나는 테이블을 막지 않고 인덱스를 만드는 CREATE INDEX CONCURRENTLY, 다른 하나는 락을 오래 기다리지 않고 빠르게 실패하게 만드는 lock_timeout/statement_timeout 가드다. 이 문서는 둘의 동작 원리와 마이그레이션 스크립트에 넣을 안전 패턴을 정리한다.

원리

왜 DDL이 위험한가

기본 CREATE INDEX는 대상 테이블에 SHARE 락을 걸어 그 인덱스가 만들어지는 동안 모든 쓰기(INSERT/UPDATE/DELETE)를 막는다. 큰 테이블이면 이 시간이 수 분~수십 분이 될 수 있고, 그동안 쓰기 트랜잭션이 대기 큐에 쌓인다. ALTER TABLE의 상당수는 더 강한 ACCESS EXCLUSIVE 락을 잡아 읽기까지 막는다.

lock convoy: 하나가 막히면 전부 막힌다

PostgreSQL의 락 요청은 대체로 큐 순서를 따른다. 오래 도는 읽기 트랜잭션(리더) 뒤에서 DDL이 ACCESS EXCLUSIVE를 요청하며 대기하면, 그 DDL 뒤에 도착한 일반 읽기 쿼리들까지 DDL이 잡으려는 락과 충돌해 함께 대기하게 된다. 즉 오래된 리더 하나 → 대기하는 DDL 하나 → 그 뒤로 밀리는 수많은 정상 쿼리, 이렇게 줄줄이 막히는 것이 lock convoy다. 지표상으로는 커넥션 수 급증과 pg_stat_activity에 Lock 대기가 폭증하는 형태로 나타난다.

CONCURRENTLY의 원리와 제약

CREATE INDEX CONCURRENTLY는 테이블을 두 번 스캔하며 쓰기를 막지 않고 인덱스를 만든다. 대신 트랜잭션을 스스로 여러 번 커밋해야 하므로 명시적 트랜잭션 블록(BEGIN/COMMIT) 안에서는 실행할 수 없다.

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

많은 마이그레이션 툴(예: 일부 ORM 마이그레이션 러너)은 각 마이그레이션을 자동으로 트랜잭션으로 감싸므로, CONCURRENTLY 문장은 그 자동 트랜잭션을 꺼야(문장 단위 autocommit) 동작한다.

실전

CONCURRENTLY는 트랜잭션 밖에서

-- 트랜잭션으로 감싸지 말 것. 문장 단위 autocommit으로 실행한다.
CREATE INDEX CONCURRENTLY idx_chunks_doc
  ON document_chunks (doc_id);

마이그레이션 툴별로 “이 마이그레이션은 트랜잭션 없이”를 지정하는 옵션이 있다.

# Alembic 예시: op 실행을 autocommit 블록으로
with op.get_context().autocommit_block():
    op.create_index(
        "idx_chunks_doc", "document_chunks", ["doc_id"], postgresql_concurrently=True,
    )

CONCURRENTLY 인덱스는 실패 시 INVALID 상태로 남을 수 있다. 반드시 사후 검증하고, 무효 인덱스는 지운 뒤 다시 만든다.

-- 무효 인덱스 탐지
SELECT indexrelid::regclass AS index, indrelid::regclass AS table
FROM pg_index WHERE NOT indisvalid;
 
-- 무효면 CONCURRENTLY로 삭제 후 재생성
DROP INDEX CONCURRENTLY idx_chunks_doc;

lock convoy 가드: 빠르게 실패시키기

마이그레이션 스크립트/트랜잭션 상단에 타임아웃 가드를 둔다. 락을 정해진 시간 안에 못 잡으면 전체를 막는 대신 이 문장만 즉시 실패하게 만들어 폭주를 예방한다.

-- 이 트랜잭션 한정 (SET LOCAL은 트랜잭션 종료 시 원복)
SET LOCAL lock_timeout = '3s';       -- 3초 안에 락 못 잡으면 실패
SET LOCAL statement_timeout = '30s'; -- 문장이 30초 넘으면 취소
 
-- 짧게 끝나는 DDL (테이블을 잠깐 잠그는 종류)
ALTER TABLE documents ADD COLUMN source text;

lock_timeout은 “락을 기다리는 시간”의 한도이고, statement_timeout은 “문장 실행 전체”의 한도다. 둘을 함께 걸어야 락 대기와 장기 실행 모두를 막을 수 있다. 실패하면 재시도(백오프)하는 편이, 무한정 대기하며 컨보이를 만드는 것보다 낫다.

안전한 마이그레이션 순서 (예: NOT NULL 컬럼 추가)

한 번에 강한 락으로 처리하지 말고 단계를 쪼갠다.

-- 1) nullable로 컬럼만 추가 (짧은 락)
ALTER TABLE documents ADD COLUMN lang text;
 
-- 2) 배치로 백필 (한 번에 다 UPDATE 하지 않는다 -> dead tuple/락 폭발 방지)
UPDATE documents SET lang = 'ko' WHERE lang IS NULL AND id BETWEEN $1 AND $2;
 
-- 3) 제약을 NOT VALID로 먼저 달고 (전체 스캔 없이 짧게)
ALTER TABLE documents ADD CONSTRAINT lang_not_null CHECK (lang IS NOT NULL) NOT VALID;
 
-- 4) 별도로 검증 (SHARE UPDATE EXCLUSIVE, 읽기/쓰기 대부분 허용)
ALTER TABLE documents VALIDATE CONSTRAINT lang_not_null;

함정·트러블슈팅

증상	원인	대응
`CONCURRENTLY cannot run inside a transaction block`	마이그레이션 러너가 자동 트랜잭션으로 감쌈	autocommit 블록/옵션으로 실행
배포 중 커넥션 급증 + Lock 대기 폭증	오래된 리더 뒤 DDL이 컨보이 유발	`lock_timeout` 가드 + 재시도
CONCURRENTLY 후 인덱스가 안 쓰임	빌드 실패로 INVALID 상태	`pg_index.indisvalid` 확인, 재생성
대량 백필로 테이블 붓고 락 김	단일 거대 UPDATE	배치 백필 + autovacuum와 bloat 튜닝

오래된 트랜잭션을 먼저 정리 — DDL을 걸기 전 pg_stat_activity에서 오래 열려 있는 idle in transaction을 확인한다. 이들이 CONCURRENTLY의 완료를 지연시키거나 락 대기를 유발한다.
백필과 vacuum — 대량 백필 UPDATE는 dead tuple을 남긴다. 마이그레이션 후 ANALYZE와 vacuum 튜닝을 함께 고려한다(autovacuum와 bloat).

정리

기본 CREATE INDEX는 쓰기를 막는다. 운영 중이면 CREATE INDEX CONCURRENTLY를 쓰되, 트랜잭션 블록 밖(문장 단위 autocommit)에서 실행한다. 실패 시 INVALID 인덱스를 정리한다.
오래 도는 리더 뒤의 DDL은 lock convoy를 만든다. SET LOCAL lock_timeout/statement_timeout 가드로 빠르게 실패시키고 재시도한다.
NOT NULL·제약 추가 같은 무거운 변경은 단계(추가 → 배치 백필 → NOT VALID 제약 → VALIDATE)로 쪼갠다.

도형준 지식 볼트

탐색기

PostgreSQL 안전한 스키마 마이그레이션: CONCURRENTLY와 lock_timeout으로 무중단 DDL