많은 분들이 ALTER TABLE ADD COLUMN을 가벼운 메타데이터 작업이라고 생각하시는데, 실제로는 운영 중 컬럼 추가가 테이블 전체를 잠그는 사고로 이어지는 경우가 많습니다. DEFAULT 값의 종류, NOT NULL 제약조건 유무, DB 엔진과 버전에 따라 잠금 범위와 지속 시간이 완전히 달라지기 때문입니다. 서비스 중단을 피하려면 nullable 컬럼을 먼저 추가하고, 데이터를 배치로 채운 다음, 제약조건은 검증 단계를 분리해서 거는 순서를 지켜야 합니다. 이 글에서는 그 순서를 PostgreSQL과 MySQL(InnoDB) 기준으로 구체적으로 정리해 보겠습니다.
왜 컬럼 하나 추가했는데 서비스 전체가 멈출까요?
컬럼 추가가 느려지는 가장 흔한 원인은 테이블 재작성입니다. DEFAULT 값을 지정하면 DB가 기존 행 전체에 그 값을 채워 넣느라 테이블을 통째로 다시 쓰는 작업이 발생할 수 있고, 이 과정에서 다른 트랜잭션이 같은 테이블에 접근하지 못하게 됩니다.
여기에 NOT NULL 제약조건이 겹치면 문제가 더 커집니다. 기존 행이 조건을 만족하는지 검증하려면 테이블 전체를 스캔해야 하는데, 수천만 건짜리 테이블이라면 이 스캔만으로도 몇 분에서 몇십 분이 걸립니다. 그 시간 동안 해당 테이블에 쓰는 쿼리는 전부 대기 상태에 빠지고, 커넥션 풀이 가득 차면서 서비스 전체 응답 지연으로 번지는 식입니다.
PostgreSQL과 MySQL의 잠금 방식이 근본적으로 다릅니다
PostgreSQL은 ADD COLUMN 자체는 짧게 걸리는 대신 잠금 수준이 엄격합니다. PostgreSQL 11부터는 상수 DEFAULT 값을 가진 컬럼을 추가해도 테이블을 재작성하지 않고 메타데이터만 바꾸기 때문에 순식간에 끝나지만, 그 짧은 순간에도 다른 트랜잭션이 테이블을 아예 건드릴 수 없는 ACCESS EXCLUSIVE 잠금이 걸립니다. 반면 NOT NULL을 바로 붙이거나 함수로 계산되는 DEFAULT를 쓰면 전체 스캔이 발생해 그 잠금이 오래 유지됩니다.
MySQL(InnoDB)은 접근이 다릅니다. 8.0.12부터 특정 조건(끝 컬럼에 추가, COMPRESSED 행 포맷이 아님 등)을 만족하면 ALGORITHM=INSTANT가 적용되어 테이블 복사 없이 메타데이터만 바뀝니다. 조건을 벗어나면 ALGORITHM=INPLACE로 떨어지는데, 이 경우 테이블 복사는 피하지만 여전히 짧은 메타데이터 잠금 구간이 생깁니다.
| 작업 | PostgreSQL(14 기준) 잠금 | MySQL 8.0(InnoDB) 알고리즘 |
|---|---|---|
| 끝에 nullable 컬럼 추가, DEFAULT 없음 | ACCESS EXCLUSIVE(매우 짧음) | INSTANT |
| 끝에 컬럼 추가, 상수 DEFAULT | ACCESS EXCLUSIVE(짧음, 재작성 없음) | INSTANT(조건 충족 시) |
| 컬럼 추가 + NOT NULL 동시 지정 | ACCESS EXCLUSIVE(전체 스캔 동안 유지) | INPLACE(테이블 복사는 없지만 스캔 발생) |
| 기존 컬럼 타입 변경 | ACCESS EXCLUSIVE(재작성, 오래 걸림) | COPY(테이블 전체 복사) |
테이블을 잠그지 않는 마이그레이션 순서
핵심은 “한 번에 끝내지 않는 것”입니다. 아래 순서로 나누면 각 단계가 짧게 끝나고, 실패해도 롤백 범위가 작습니다.
-- 1단계: nullable 컬럼만 추가 (메타데이터 작업, 거의 즉시 완료)
ALTER TABLE orders ADD COLUMN shipping_status text;
-- 2단계: 애플리케이션 배포 - 신규 쓰기에는 값을 채우되
-- 기존 코드에서도 NULL을 허용하도록 양쪽 호환 유지
-- 3단계: 배치로 기존 행 백필 (트랜잭션을 작게 쪼개서 반복 실행)
UPDATE orders
SET shipping_status = 'unknown'
WHERE id IN (
SELECT id FROM orders
WHERE shipping_status IS NULL
LIMIT 1000
);
-- 4단계: 제약조건은 NOT VALID로 걸어 즉시 반환
ALTER TABLE orders
ADD CONSTRAINT orders_shipping_status_not_null
CHECK (shipping_status IS NOT NULL) NOT VALID;
-- 5단계: 검증은 별도로, 약한 잠금으로 실행
ALTER TABLE orders VALIDATE CONSTRAINT orders_shipping_status_not_null;
4단계의 NOT VALID는 기존 데이터를 검사하지 않고 즉시 제약조건을 등록하기 때문에 짧은 잠금만 발생합니다. 5단계의 VALIDATE CONSTRAINT는 전체 스캔을 하지만 SHARE UPDATE EXCLUSIVE 수준의 약한 잠금만 걸어서, 다른 트랜잭션의 읽기와 쓰기를 막지 않습니다. 이 분리 자체가 운영 중 컬럼 추가가 테이블을 잠그는 문제를 피하는 핵심 트릭입니다.
NOT NULL 제약을 한 번에 걸면 안 되는 이유
ALTER TABLE ... ALTER COLUMN ... SET NOT NULL을 단독으로 실행하면 PostgreSQL이 테이블 전체를 스캔해서 NULL이 하나도 없는지 확인합니다. 이 스캔 동안에는 앞서 만든 CHECK 제약과 무관하게 추가로 전체 테이블을 다시 읽습니다.
다만 PostgreSQL 12 이상에서는 이미 검증된 CHECK (col IS NOT NULL) 제약이 있으면 옵티마이저가 이를 증거로 인정해서 재스캔을 건너뜁니다. 그래서 앞 단계처럼 CHECK 제약을 먼저 검증해 두면 마지막에 SET NOT NULL을 걸어도 순식간에 끝납니다. 이 순서를 모르고 처음부터 ADD COLUMN ... NOT NULL을 한 문장으로 실행하면, 트랜잭션이 끝나기 전까지 테이블 전체가 잠긴 채로 대기열이 쌓이게 됩니다.
MySQL에서는 같은 접근을 그대로 쓸 수 없고, 대신 큰 테이블이라면 pt-online-schema-change나 gh-ost 같은 온라인 스키마 변경 도구로 그림자 테이블을 만들어 트리거로 동기화한 뒤 교체하는 방식을 씁니다. 다만 이 도구들은 트리거 기반이라 쓰기량이 많은 테이블에서는 복제 지연과 추가 디스크 I/O를 유발할 수 있습니다.
이 순서가 통하지 않는 경우와 트레이드오프
이 방식은 공짜가 아닙니다. 백필을 배치로 나누면 전체 마이그레이션 기간이 하루 이상으로 늘어날 수 있고, 그동안 애플리케이션 코드는 NULL과 채워진 값 둘 다를 처리하는 임시 분기를 유지해야 합니다. 배치 쿼리가 커밋되는 시점마다 WAL(PostgreSQL) 또는 binlog(MySQL)가 늘어나서, 복제 서버나 CDC 파이프라인을 쓰는 환경이라면 지연이 커질 수 있습니다.
또한 MySQL에서 INSTANT 알고리즘 조건을 만족하지 못하는 경우(예: 압축 행 포맷, 특정 인덱스 조합)에는 결국 COPY 알고리즘으로 떨어져서 테이블 전체를 복사하게 됩니다. 이럴 때는 배치 분할보다 트래픽이 가장 적은 시간대에 메인터넌스 윈도우를 잡고 진행하는 쪽이 더 안전할 수 있습니다. 테이블이 작아서(수십만 건 이하) 전체 스캔이 1~2초 안에 끝나는 경우라면, 애초에 이렇게 단계를 나눌 필요 없이 한 문장으로 처리해도 됩니다.
배치 백필 중 레플리카 지연이 커지면 어떻게 하나요?
배치 크기를 줄이고 배치 사이에 짧은 대기 시간을 넣는 방식으로 먼저 대응합니다. 예를 들어 1,000건 단위 업데이트 사이에 복제 지연을 확인하는 체크를 넣고, 지연이 임계값을 넘으면 다음 배치 실행을 몇 초 미루는 식입니다.
근본적으로는 배치 쿼리 자체가 너무 큰 트랜잭션으로 묶이지 않도록 하는 것이 중요합니다. 하나의 트랜잭션이 오래 열려 있으면 PostgreSQL의 VACUUM이 지연되거나 MySQL의 undo 로그가 쌓여서, 디스크 사용량이 예상보다 빨리 늘어나는 부작용도 함께 생깁니다.
운영 중 컬럼 추가가 테이블을 잠그는 사고는 대부분 “한 문장으로 끝내려는 욕심”에서 시작됩니다. nullable 추가 → 배치 백필 → NOT VALID 제약 → 검증 → NOT NULL 전환이라는 다섯 단계로 나누면, 각 단계는 짧고 되돌리기도 쉽습니다. 다음 배포 전에는 바꾸려는 컬럼이 이 단계 중 어디서 멈출 수 있는지부터 점검해 보시길 권합니다.
