대용량 테이블에 NOT NULL·외래 키를 무중단으로 추가하는 순서
배송 테이블의 store_id는 처음부터 NULL을 허용했다. 입점 매장 기능이 붙기 전에 만든 테이블이라 매장 없는 배송도 있었다. 몇 해가 지나 모든 배송이 매장에 묶이게 됐고, 애플리케이션 코드에서는 NULL이 들어올 길이 막혔다. 남은 일은 DB에도 그 사실을 적어 두는 것이었다. NOT NULL 하나, 그리고 stores를 가리키는 외래 키 하나다.
행은 4천만 건에 가까웠다. 그만한 테이블에 ALTER TABLE ... SET NOT NULL 한 줄을 실행하는 일이 왜 위험한지, 그때 나는 반만 알고 있었다. 테이블을 훑는 동안 가장 강한 잠금이 걸린다는 건 알았다. 하지만 잠금을 얻기 전, 기다리는 동안에도 서비스가 멈출 수 있다는 건 몰랐다.
아래는 그 뒤로 PostgreSQL에서 쓰는 순서다. 제약을 거는 일과 기존 행을 확인하는 일을 나누고, 단계마다 어떤 잠금을 잡는지 같이 적었다.
SET NOT NULL 한 줄이 위험한 이유
처음에는 별생각 없이 평일 오후에 실행했다. 주문이 뜸한 시간이니 몇십 초면 끝날 줄 알았다. 그런데 명령이 돌아오지 않았고, 2분쯤 지나 API 응답 시간 알람이 울렸다. pg_stat_activity를 열어 보니 내 ALTER는 잠금을 기다리는 중이었다. 그 앞에는 정산용 리포트 쿼리가 수십 분째 같은 테이블을 읽고 있었다. 내 명령을 취소하자 쌓여 있던 요청이 한꺼번에 풀렸다. 등이 서늘했다.
SET NOT NULL은 ACCESS EXCLUSIVE 잠금을 잡는다. 이 잠금은 단순한 SELECT와도 충돌한다. 게다가 PostgreSQL의 잠금 대기열은 먼저 온 순서대로 처리된다. 그래서 ALTER가 리포트 쿼리를 기다리는 동안 뒤에 들어온 평범한 SELECT까지 ALTER 뒤에 줄을 섰다. 잠금을 얻고 나면 이번에는 NULL이 있는지 확인하려고 테이블 전체를 읽는다. 대기열 문제와 스캔 시간 문제가 겹친 셈이다.
대기열 문제는 lock_timeout으로 막는다. 정해 둔 시간 안에 잠금을 못 얻으면 명령이 실패하고 대기열에서 빠진다. 실패하면 잠시 뒤 다시 시도하면 된다.
SET lock_timeout = '2s';스캔 시간 문제는 명령을 쪼개서 푼다.
NOT VALID로 걸고 VALIDATE로 확인하기
PostgreSQL 17까지는 NOT NULL 제약을 바로 NOT VALID로 걸 수 없다. 그래서 같은 뜻의 CHECK 제약을 빌려 쓴다.
SET lock_timeout = '2s';
-- 1단계: 기존 행은 검사하지 않고 제약만 건다
ALTER TABLE deliveries
ADD CONSTRAINT deliveries_store_id_not_null
CHECK (store_id IS NOT NULL) NOT VALID;
-- 2단계: 기존 행 검사 (읽기·쓰기를 막지 않는다)
ALTER TABLE deliveries
VALIDATE CONSTRAINT deliveries_store_id_not_null;
-- 3단계: 유효한 CHECK가 있으니 테이블 스캔 없이 끝난다
ALTER TABLE deliveries
ALTER COLUMN store_id SET NOT NULL;
-- 4단계: 역할이 끝난 CHECK 제거
ALTER TABLE deliveries
DROP CONSTRAINT deliveries_store_id_not_null;1단계도 ACCESS EXCLUSIVE를 잡지만 기존 행을 읽지 않으니 순식간에 끝난다. 2단계 VALIDATE CONSTRAINT는 SHARE UPDATE EXCLUSIVE만 잡는다. 이 잠금은 SELECT, INSERT, UPDATE, DELETE와 충돌하지 않는다. 그러니 몇 분이 걸려도 서비스는 그대로 돈다. 3단계가 빨리 끝나는 건 PostgreSQL 12부터다. 공식 문서에 따르면 NULL이 없음을 증명하는 유효한 CHECK 제약이 있으면 SET NOT NULL은 테이블 스캔을 건너뛴다. 3단계와 4단계는 다시 ACCESS EXCLUSIVE를 잡지만 둘 다 금방 끝난다.
여기서 한 번 크게 헛걸음을 했다. 네 단계를 마이그레이션 파일 하나에 넣었는데, 우리가 쓰던 마이그레이션 도구는 파일 하나를 트랜잭션 하나로 감쌌다. 잠금은 트랜잭션이 끝날 때 풀린다. 결국 1단계에서 잡은 ACCESS EXCLUSIVE를 쥔 채로 2단계의 긴 검사를 돌린 꼴이 됐다. 쪼갠 의미가 통째로 사라진 것이다. 그날은 꽤 허탈했다. 그 뒤로는 단계마다 파일을 나누거나 해당 파일만 트랜잭션 감싸기를 끈다.
NOT VALID를 건 순간부터 새 행은 검사받는다
NOT VALID는 검사를 미룬다는 뜻이 아니다. 기존 행 검사만 건너뛰고, 새로 들어오거나 수정되는 행에는 그 즉시 제약이 적용된다. 이걸 가볍게 넘겼다가 1단계를 배포한 직후 에러 알림을 받았다. 한쪽 구석에서 돌던 오래된 배송 동기화 배치가 여전히 store_id 없이 INSERT를 하고 있었다. 코드 검색에서 빠졌던 별도 저장소의 배치였다.
그래서 순서는 이렇게 굳었다.
- NULL을 쓰는 코드를 모두 고쳐서 먼저 배포한다.
- 남은 NULL 행을 작은 묶음으로 나눠 채운다.
- 그다음에 제약을 건다.
UPDATE deliveries
SET store_id = 0 -- 실제로는 업무 규칙에 맞는 값
WHERE id IN (
SELECT id FROM deliveries
WHERE store_id IS NULL
LIMIT 5000
);백필이 덜 끝난 채 2단계로 넘어가면 VALIDATE가 위반 행을 만나 실패한다. 실패해도 데이터는 그대로지만, 몇 분 동안 읽은 작업이 헛수고가 된다. 실행 전에 SELECT 1 FROM deliveries WHERE store_id IS NULL LIMIT 1로 한 번 더 확인해 둔다. NULL 하나가 얼마나 조용히 일을 망치는지는 NOT IN 서브쿼리 글에서도 겪은 적이 있다.
외래 키도 둘로 나눈다
외래 키는 원래 NOT VALID를 지원하니 CHECK를 빌릴 필요가 없다.
SET lock_timeout = '2s';
ALTER TABLE deliveries
ADD CONSTRAINT deliveries_store_id_fkey
FOREIGN KEY (store_id) REFERENCES stores (id) NOT VALID;
ALTER TABLE deliveries
VALIDATE CONSTRAINT deliveries_store_id_fkey;이번에는 잠금이 양쪽 테이블에 걸린다는 점을 따로 봐야 한다.
| 단계 | deliveries (참조하는 쪽) | stores (참조되는 쪽) | 막히는 작업 |
|---|---|---|---|
| ADD FOREIGN KEY … NOT VALID | SHARE ROW EXCLUSIVE | SHARE ROW EXCLUSIVE | 두 테이블의 쓰기 (짧게) |
| VALIDATE CONSTRAINT | SHARE UPDATE EXCLUSIVE | ROW SHARE | 일반 읽기·쓰기는 안 막힘 |
| (참고) ADD CHECK … NOT VALID | ACCESS EXCLUSIVE | 없음 | 읽기·쓰기 모두 (짧게) |
SHARE ROW EXCLUSIVE는 SELECT는 통과시키지만 INSERT, UPDATE, DELETE와 충돌한다. 순간이라도 두 테이블의 쓰기를 멈추는 셈이라, 매장 테이블처럼 자주 바뀌지 않는 쪽이라도 lock_timeout은 똑같이 건다. 그리고 PostgreSQL은 외래 키를 만들 때 참조하는 쪽 컬럼에 인덱스를 자동으로 만들지 않는다. 매장을 지우거나 키를 바꿀 때 배송 테이블을 통째로 읽지 않도록 CREATE INDEX CONCURRENTLY로 미리 만들어 둔다.
PostgreSQL 18과 MySQL에서는
PostgreSQL 18부터는 NOT NULL 제약 자체를 NOT VALID로 걸 수 있다. 공식 문서도 NOT VALID를 쓸 수 있는 제약으로 외래 키, CHECK와 함께 not-null 제약을 꼽는다. CHECK를 빌렸다가 지우는 단계가 사라진다.
ALTER TABLE deliveries
ADD CONSTRAINT deliveries_store_id_not_null
NOT NULL store_id NOT VALID;
ALTER TABLE deliveries
VALIDATE CONSTRAINT deliveries_store_id_not_null;MySQL은 사정이 다르다. InnoDB에서 컬럼을 NOT NULL로 바꾸는 작업은 ALGORITHM=INPLACE, LOCK=NONE으로 동시 DML을 허용하지만, 테이블을 다시 만드는 무거운 작업이다. strict SQL 모드가 켜져 있어야 하고 NULL이 하나라도 남아 있으면 실패한다. 외래 키 추가는 foreign_key_checks를 끈 상태에서만 INPLACE로 돌고, 그 경우 기존 데이터는 검사하지 않는다. 검사를 원하면 COPY 방식으로 테이블을 복사해야 한다. PostgreSQL처럼 검사만 따로 떼어 내는 길은 없다.
지금 쓰는 방식
요즘 제약을 추가하는 마이그레이션은 늘 같은 모양이다. 세션 맨 위에 lock_timeout을 걸고, 무거운 잠금이 필요한 단계는 짧게, 오래 걸리는 단계는 가벼운 잠금으로 나눈다. 단계마다 따로 커밋하고, 실패하면 몇 초 쉬었다가 다시 돌리는 재시도 스크립트를 곁에 둔다. 실행 직전에는 pg_stat_activity에서 그 테이블을 오래 붙잡고 있는 쿼리가 없는지 본다.
돌아보면 처음 그날 문제는 명령어를 몰라서 생긴 게 아니었다. 잠금이 언제 잡히고 언제 풀리는지, 누가 누구를 기다리는지 그림을 그려 보지 않은 탓이 컸다. 그래서 지금은 마이그레이션 리뷰를 할 때 SQL보다 잠금 표를 먼저 적는다. 단계마다 어떤 잠금을 얼마 동안 쥐는지 한 줄씩 써 보면, 위험한 줄은 대개 거기서 먼저 드러난다.