PostgreSQL ALTER TABLE, 다시 쓰는 DDL과 안 쓰는 DDL

한서윤 · 2026년 10월 30일 · 스키마 설계

컬럼 하나 붙이는 마이그레이션을 앞두고 점검 공지부터 쓰던 때가 있었다. 행이 수천만 건인 테이블에 DEFAULT가 붙은 컬럼을 추가하면 테이블 전체를 다시 쓰고, 그동안 읽기와 쓰기가 모두 멈춘다고 배웠다. 그래서 늘 세 단계로 나눴다. NULL을 허용하는 컬럼을 먼저 만들고, 밤새 배치로 값을 채우고, 마지막에 기본값과 NOT NULL을 걸었다.

그 습관은 PostgreSQL 13을 쓰던 서비스에서 깨졌다. 동료가 기본값이 붙은 컬럼 추가를 한 줄짜리 마이그레이션으로 올렸고, 나는 리뷰에서 막으려 했다. 확인하려고 스테이징에서 직접 돌려 봤는데, 수천만 건 테이블인데도 명령이 눈 깜짝할 새 끝났다. 쪼개서 밤새 돌렸던 배치들이 떠올라 조금 허탈했다.

그렇다고 모든 ALTER TABLE이 가벼워진 것은 아니다. 어떤 변경은 여전히 테이블을 통째로 새 파일에 옮겨 쓴다. 아래에는 둘을 가르는 기준과 그걸 직접 확인하는 방법을 적었다. 그리고 가벼운 명령조차 서비스를 멈출 수 있는 이유도 함께 적었다.

PostgreSQL 11이 바꾼 것: 기본값을 카탈로그에 적어 둔다

PostgreSQL 10까지는 NULL이 아닌 기본값으로 컬럼을 추가하면 기존 행마다 그 값을 실제로 써 넣어야 했다. 11부터는 방식이 다르다. ALTER TABLE을 실행하는 순간 기본값 식을 한 번만 계산하고, 그 결과를 컬럼 정보가 담긴 시스템 카탈로그 pg_attribute에 저장한다. 이때 atthasmissing이 참이 되고, attmissingval에 값이 들어간다.

기존 행은 디스크에서 건드리지 않는다. 옛날 행을 읽을 때 새 컬럼 자리가 비어 있으면 카탈로그에 적어 둔 값을 대신 돌려줄 뿐이다. 새로 들어오거나 수정된 행에는 값이 물리적으로 기록된다. 나중에 VACUUM FULL이나 타입 변경으로 테이블을 다시 쓰게 되면 그때 모든 행에 값이 실제로 채워지고, 카탈로그의 두 필드는 정리된다.

ALTER TABLE orders ADD COLUMN status smallint NOT NULL DEFAULT 0;

SELECT attname, atthasmissing, attmissingval
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attnum > 0;

status 줄에 atthasmissing이 t로 찍히면 기존 행을 건드리지 않고 끝났다는 뜻이다.

휘발성 기본값은 예외다

카탈로그에 값 하나를 적어 두는 방식이니, 행마다 값이 달라야 하는 기본값에는 쓸 수 없다. 공식 문서도 휘발성(volatile) 기본값을 주면 테이블과 인덱스 전체를 다시 쓴다고 적어 두었다. clock_timestamp(), random(), gen_random_uuid()가 여기에 속한다. 저장 생성 컬럼, identity 컬럼, 제약이 걸린 도메인 타입 컬럼을 추가할 때도 다시 쓴다.

헷갈리기 쉬운 쪽은 now()다. 이 함수는 휘발성이 아니라 STABLE이라서 빠른 경로를 탄다. 대신 기존 행은 모두 ALTER를 실행한 트랜잭션의 시각 하나를 똑같이 받는다. 나는 외부 노출용 public_id uuid DEFAULT gen_random_uuid()를 붙이면서 이 차이를 몸으로 배웠다. 스테이징에서 몇 초면 끝날 줄 알고 실행했는데 프롬프트가 한참 돌아오지 않았다. 행마다 다른 UUID가 필요하니 다시 쓰는 수밖에 없다는 걸 그제야 깨달았다.

타입 변경은 대개 다시 쓴다

컬럼 타입 변경은 반대다. 문서에 따르면 타입을 바꾸면 보통 테이블과 인덱스 전체를 다시 쓴다. 예외는 하나뿐이다. 기존 타입이 새 타입으로 이진 호환(binary coercible)되고, USING 절이 내용을 바꾸지 않을 때다. 이 규칙은 9.1에서 처음 들어왔고, 그때는 varchar를 text로 바꾸는 경우 정도만 해당했다. 9.2부터는 varchar나 varbit의 길이 제한을 늘리거나 아예 없애도 다시 쓰지 않는다.

integer를 bigint로 바꾸는 일은 피할 수 없다. 저장 크기가 4바이트에서 8바이트로 달라지니 모든 행을 새로 써야 한다. 로그 테이블의 id가 21억 한도에 가까워져서 이 변경을 처음 검토했을 때, 스테이징에서 걸린 시간을 보고 운영에서 한 번에 돌리는 안은 바로 접었다. 재작성이 없는 변경이라도 인덱스는 다시 만들 수 있다는 점도 기억해 둘 만하다. 새 인덱스가 기존과 논리적으로 같다는 걸 확인하지 못하면 인덱스는 다시 만든다.

길이를 늘린 varchar 변경이 이상하게 오래 걸린다는 메일링 리스트 글도 본 적이 있다. 버전 하나만 믿기보다 자기 테이블에서 직접 확인하는 편이 낫다.

직접 재 보기: 파일 번호와 잠금

테이블을 다시 썼는지는 데이터 파일 번호로 확인할 수 있다. 재작성이 일어나면 새 파일이 생기고 pg_relation_filenode의 값이 바뀐다. 나는 3천만 건짜리 실험 테이블을 만들고 DDL마다 앞뒤 값을 비교했다.

CREATE TABLE t AS
SELECT g::integer AS id, md5(g::text)::varchar(40) AS code
FROM generate_series(1, 30000000) AS g;
CREATE INDEX ON t (id);

\timing on
SELECT pg_relation_filenode('t');
ALTER TABLE t ADD COLUMN flag boolean NOT NULL DEFAULT false;
SELECT pg_relation_filenode('t');   -- 그대로
ALTER TABLE t ALTER COLUMN id TYPE bigint;
SELECT pg_relation_filenode('t');   -- 바뀜

내 테스트 서버에서는 기본값 컬럼 추가와 varchar 길이 늘리기가 밀리초 단위로 끝났고 파일 번호도 그대로였다. clock_timestamp() 기본값과 bigint 변경은 분 단위로 걸렸고 파일 번호가 바뀌었다. 정확한 시간은 디스크와 행 크기에 따라 달라지니 숫자보다 자릿수 차이를 보면 된다.

잠금은 다른 세션에서 본다. DDL을 BEGIN 안에서 실행해 두고 아래 쿼리를 돌리면 된다. 네 경우 모두 AccessExclusiveLock이 잡혀 있었다. 빠르냐 느리냐는 그 잠금을 얼마나 오래 쥐고 있느냐의 차이일 뿐이다.

SELECT locktype, mode, granted, pid
FROM pg_locks
WHERE relation = 't'::regclass;

빠른 DDL도 서비스를 멈춘다: 잠금 대기열

빠른 경로를 믿고 낮 시간에 컬럼 추가를 배포한 날이 있었다. 명령 자체는 순식간에 끝나야 정상인데 마이그레이션이 응답하지 않았고, 곧이어 API 응답 시간 그래프가 치솟았다. 꽤 당황했다. 원인은 같은 테이블을 몇 분째 읽던 통계 쿼리였다. 그 쿼리가 쥔 ACCESS SHARE 잠금 때문에 ALTER가 기다렸고, 그 뒤로 들어온 평범한 SELECT까지 전부 ALTER 뒤에 줄을 섰다.

PostgreSQL의 잠금 대기열은 먼저 온 순서를 지킨다. 대기 중인 ACCESS EXCLUSIVE 뒤에 선 요청은 서로 충돌하지 않는 읽기라도 앞질러 갈 수 없다. 그래서 DDL 앞에는 항상 lock_timeout을 건다.

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN memo text;

2초 안에 잠금을 못 얻으면 에러를 내고 물러나니 대기열이 길어지지 않는다. 실패하면 간격을 두고 다시 시도하게 마이그레이션 도구 쪽에 재시도를 붙였다.

지금 마이그레이션을 쓰는 방식

요즘은 DDL을 올리기 전에 두 가지를 먼저 묻는다. 이 변경이 테이블을 다시 쓰는가, 그리고 잠금을 얼마나 오래 쥐는가. 첫 질문은 스테이징에서 파일 번호를 비교하면 답이 나온다. 기본값에 함수가 들어가면 그 함수의 휘발성부터 확인한다. 두 번째 질문 앞에는 습관처럼 lock_timeout을 둔다.

재작성이 피할 수 없는 타입 변경은 한 번에 하지 않는다. 새 컬럼을 만들고, 작은 묶음으로 값을 옮기고, 트리거로 새 쓰기를 따라가게 한 뒤 짧은 잠금 안에서 이름을 바꾼다. NOT NULL을 걸 때는 CHECK (col IS NOT NULL) NOT VALID를 먼저 추가한다. 그다음 VALIDATE CONSTRAINT로 검증하는데, 이 단계는 SHARE UPDATE EXCLUSIVE만 잡아 쓰기를 막지 않는다. 유효한 CHECK가 있으면 SET NOT NULL은 테이블을 훑지 않고 넘어간다.

세 단계로 쪼개던 예전 습관이 다 틀린 건 아니었다. 버전이 올라가며 필요 없어진 경우가 생겼을 뿐이다. 그래서 요즘은 예전에 배운 규칙을 그대로 따르기 전에 지금 버전의 문서를 다시 연다.