PostgreSQL 실전 강좌 #1 스키마 마이그레이션: 서비스 중단 없이 테이블 바꾸기

인프라 PostgreSQL 데이터베이스
4 분 소요

기초 편이 “DB를 이해하는 개발자"를 만들었다면, 실전 편은 “운영 중인 DB를 다루는 개발자"가 목표입니다. 첫 주제는 가장 자주 하는 운영 작업, 스키마 변경입니다. 개발 환경에서는 아무 ALTER TABLE이나 즉시 끝나지만, 초당 수백 쿼리가 도는 운영 테이블에서는 같은 문장이 서비스를 세울 수 있습니다. 이 차이를 아는 것이 실전 편의 입구입니다.

마이그레이션은 코드입니다 #

전제부터 세우겠습니다. 스키마 변경은 psql에서 손으로 치는 것이 아니라 버전 관리되는 마이그레이션 파일로 합니다(Flyway, dbmate, Django·Rails·Alembic 등 무엇이든). 모든 환경(로컬·스테이징·운영)이 같은 순서로 같은 변경을 밟아야 “스테이징에서는 됐는데"가 사라집니다. PostgreSQL의 큰 강점이 여기서 나옵니다. DDL도 트랜잭션이 됩니다. 마이그레이션 하나(테이블 생성 + 인덱스 + 권한)를 BEGIN/COMMIT으로 묶으면, 중간에 실패해도 반쯤 적용된 상태가 남지 않습니다. 이것이 안 되는 DB가 많아서, 다른 DB에서 온 분들이 부러워하는 지점입니다.

위험한 변경과 안전한 변경 #

ALTER TABLE의 위험도는 두 가지 질문으로 정해집니다. 얼마나 강한 잠금을, 얼마나 오래 잡는가. 대부분의 ALTER TABLE은 테이블 전체의 배타 잠금(ACCESS EXCLUSIVE)을 잡습니다. 잠금을 잡는 것 자체는 피할 수 없고, 관건은 “잡은 채로 하는 일이 즉시 끝나는가"입니다.

변경위험도이유
컬럼 추가(DEFAULT 포함)안전메타데이터만 수정, 즉시 끝(PostgreSQL 11부터 DEFAULT도 재작성 없음)
CREATE INDEX위험빌드 내내 쓰기 차단
NOT NULL 추가위험전 행 검증 동안 잠금 유지
컬럼 타입 변경(재작성 필요)매우 위험테이블 전체 재작성

여기에 함정이 하나 더 있습니다. 잠금 대기 자체가 장애가 됩니다. 긴 SELECT 하나가 도는 중에 ALTER TABLE이 대기하면, 그 뒤의 모든 쿼리(평범한 SELECT까지)가 ALTER 뒤에 줄을 섭니다. 그래서 마이그레이션 세션에는 SET lock_timeout = '3s' 같은 상한을 걸어, 잠금을 못 잡으면 서비스를 세우는 대신 마이그레이션이 실패하도록 만드는 것이 정석입니다(잠금의 원리는 6편 예고대로 실전 6편에서 파겠습니다).

안전한 형태로 바꾸는 법 #

인덱스는 CONCURRENTLY로 만듭니다.

CONCURRENTLY 인덱스 생성 
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

쓰기를 막지 않고 인덱스를 만듭니다. 대신 두 가지 규칙이 따라옵니다. 트랜잭션 안에서는 못 쓰므로 마이그레이션 도구의 “트랜잭션 없이 실행” 옵션이 필요하고, 실패하면 INVALID 상태의 인덱스가 남으므로 확인 후 DROP하고 재시도합니다.

NOT NULL은 단계로 쪼갭니다. 운영 테이블에 ALTER TABLE ... SET NOT NULL을 바로 치면 전 행 검증 동안 잠금을 쥡니다. 안전한 순서는 이렇습니다.

NOT NULL을 3단계로 추가 
-- 1) 검증하지 않는 제약을 먼저 추가(즉시 끝)
ALTER TABLE orders ADD CONSTRAINT orders_email_not_null
    CHECK (email IS NOT NULL) NOT VALID;

-- 2) 기존 행 검증(약한 잠금으로 진행, 시간이 걸려도 서비스는 돕니다)
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_not_null;

-- 3) 이제 SET NOT NULL은 검증된 CHECK를 근거로 즉시 끝납니다
ALTER TABLE orders ALTER COLUMN email SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_email_not_null;

핵심 아이디어는 “오래 걸리는 일(검증)을 약한 잠금 구간으로 밀어내고, 강한 잠금 구간에는 즉시 끝나는 일만 남긴다"입니다. 외래 키 추가도 같은 모양(ADD CONSTRAINT ... NOT VALID → VALIDATE)으로 풉니다.

확장-이관-축소: 되돌릴 수 있는 변경 #

컬럼 개명이나 타입 변경처럼 “한 방에 하면 앱과 DB가 동시에 바뀌어야 하는” 변경은 expand-contract 패턴으로 풉니다. ① 새 컬럼을 추가하고(확장), ② 앱이 양쪽에 쓰면서 백필을 돌리고(이관), ③ 앱이 새 컬럼만 쓰게 된 뒤 옛 컬럼을 지웁니다(축소). 단계마다 배포와 롤백이 가능하다는 것이 이 패턴의 가치입니다. 백필 UPDATE는 한 방이 아니라 수천 행 단위 배치로 나눠 돌립니다(한 방 UPDATE는 6편의 MVCC대로 테이블 전체의 새 버전, 즉 dead tuple 폭탄이기도 합니다. 이 뒷수습이 실전 5편 VACUUM의 주제입니다).

정리 #

  • 마이그레이션은 버전 관리되는 코드입니다. PostgreSQL은 DDL 트랜잭션이 되므로 반쯤 적용된 상태를 남기지 않을 수 있습니다.
  • 위험도는 “얼마나 강한 잠금을 얼마나 오래"로 판단합니다. 컬럼 추가는 안전, 인덱스·NOT NULL·타입 변경은 요주의입니다.
  • 마이그레이션 세션에는 lock_timeout을 겁니다. 잠금 대기 줄이 장애의 실제 모습입니다.
  • 인덱스는 CONCURRENTLY, NOT NULL·FK는 NOT VALID → VALIDATE의 2단계가 정석입니다.
  • 개명·타입 변경은 expand-contract로, 백필은 배치로 나눠서 합니다. 다음 편은 커넥션 풀입니다.
X