PostgreSQL: ALTER TABLE ...

※ PostgreSQL: ALTER TABLE ... .

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 PostgreSQL에서 ALTER TABLE ... (특히 ALTER COLUMN)을 할 때, 내부적으로 어떤 일이 일어나는지에 대한 내용입니다.

 

운영하다 보면 그냥 한번씩 별일이 있습니다.

---

먼저, REWRITE에 대해 알아야합니다.

1. REWRITE란 무엇이며, 왜 위험할까요?

 

• Rewirte(테이블 재작성): 테이블의 새 사본을 만들어 데이터를 모두 재기록한 뒤 원자적으로 교체하는 동작입니다.
  이때 ACCESS EXCLUSIVE 급의 강한 락이 걸리고, 일시적으로 테이블 크기만큼 추가 디스크가 필요할 수 있습니다.
• MVCC 주의: Rewrite형 ALTER TABLE은 MVCC-세이프가 아니어서, 커밋 직후 이전 스냅샷을 들고 있는 트랜잭션에서 테이블이 비어 보일 수 있습니다(정상 동작). 새 스냅샷부터는 정상입니다. (PostgreSQL)

---

2. 어떤 ALTER가 REWRITE를 유발할까요?

변경	테이블 재작성	인덱스 재구축	설명
ADD COLUMN (DEFAULT 없음/상수 DEFAULT)	아니오	아니오	상수 DEFAULT 또는 DEFAULT가 없으면 Rewrite 아님. (PG11 도입)
ADD COLUMN (VOLATILE DEFAULT, STORED GENERATED, IDENTITY, “제약 있는 도메인”)	예	경우에 따라	명시적 Rewrite 대상. VIRTUAL GENERATED는 예외(Rewrite 아님).
DROP COLUMN	아니오	아니오	"컬럼은 논리적으로 숨겨지고 공간은 추후 REWRITE/VACUUM FULL/CLUSTER로 회수."
ALTER COLUMN TYPE (일반적)	예	예(예외있음)	"보통 테이블·인덱스 Rewrite."
ALTER COLUMN TYPE (예외: binary-coercible, USING 없이 내용 불변)	아니오	경우에 따라	"테이블 Rewrite는 생략되나, 인덱스는 재구축될 수 있음."
PK/UNIQUE 추가·삭제	아니오	예	"PK/UNIQUE는 유니크 인덱스 생성/삭제."
NOT NULL/ CHECK 추가	아니오(단, 검증 스캔 필요)	아니오	NOT VALID/VALIDATE로 분리 가능, 검증 시 락 완화.
COLLATION 변경	-	REINDEX 필요	콜레이션 변경 시 의존 객체 재구축 권고.
파티션 구조 변경 제약	-	-	부모·파티션 열 일치 필요 등 제약.

---

 

3. 케이스별 상세 내용

3.1 ADD COLUMN: Rewrite 트리거

• 빠른 경로: 상수 DEFAULT 또는 DEFAULT 없음 → Rewrite 없음. 기존 행은 읽을 때 기본값/NULL을 논리적으로 적용합니다. (PG11부터 최적화)
• Rewrite 유발: DEFAULT clock_timestamp() 같은 VOLATILE, GENERATED … STORED, IDENTITY, 제약 있는 도메인 → 전체 테이블 & 인덱스 Rewrite. VIRTUAL GENERATED는 예외(Rewrite 없음).
• 복제 엣지(참고): PG 15.6 릴리스 노트에는 상수 DEFAULT 최적화가 논리복제 초기 동기화에서 NULL 전송 이슈를 일으킬 수 있었음을 수정했다고 기록되어 있습니다. 대규모 DDL/복제 환경에선 반드시 버전/패치 수준 확인이 필요합니다.

---

3.2 DROP COLUMN: 논리 삭제와 공간 회수

• DROP COLUMN은 데이터 파일을 바로 줄이지 않습니다.
• 컬럼을 숨김 처리하며(pg_attribute 관점), 공간은 VACUUM FULL/CLUSTER/REWRITE형 ALTER로 회수합니다.

---

3.3 ALTER COLUMN TYPE: 일반 원칙과 예외

• 일반 원칙: 테이블·인덱스 Rewrite가 "보통" 필요합니다. USING으로 값 변환이 있거나 내부 저장 포맷이 바뀌면 100% 그렇습니다.
• 예외(핵심): 기존 타입이 새 타입에 binary-coercible 이고, USING 없이 값이 바뀌지 않으면 테이블 Rewrite는 생략됩니다(인덱스는 재구축될 수 있음). → varchar(50)→varchar(100), text↔varchar 같은 케이스가 여기에 해당합니다.
• Default와 USING의 상호작용: USING은 기존 DEFAULT에 적용되지 않을 수 있어, 필요 시 기존 DEFAULT를 먼저 DROP 후 타입 변경, 이후 새 DEFAULT를 설정하는 절차를 따릅니다.

---

3.4 CONSTRAINT : 검증 스캔, 락 완화

• NOT NULL / CHECK 추가는 테이블 스캔이 필요하지만 Rewrite는 아닙니다.
• 대형 테이블이면 NOT VALID → VALIDATE CONSTRAINT 로 나눠 락을 완화할 수 있습니다.
• VALIDATE는 대상 테이블에 SHARE UPDATE EXCLUSIVE 정도로 수행됩니다. (외부 FK는 ROW SHARE 추가 요구)

---

3.7 PK/UNIQUE/INDEX/COLLATION

• PK/UNIQUE 추가는 유니크 인덱스 생성(테이블 Rewrite 없음), 삭제는 해당 인덱스 제거입니다.
• 컬럼 타입 자체를 바꾸면 그 변경 규칙을 따르므로 Rewrite가 필요할 수 있습니다.
• 콜레이션 변경·업그레이드가 있으면 의존 객체(특히 인덱스)를 REINDEX 해야 합니다.

---

3.6 파티션/상속과 컬럼/제약의 정합

• 파티션 테이블은 부모와 컬럼 구성이 동일해야 하며, CHECK/NOT NULL은 상속 규칙이 적용됩니다.
• 일부 제약은 부모에만 추가/삭제하는 것이 제한될 수 있습니다.
• 파티션/부모 혼합 환경에서 DDL 적용 전 정합성 규칙을 반드시 확인하세요. 

---

4. LOCK·DISK·MVCC

항목	설명
락 수준	다수의 ALTER TABLE은 ACCESS EXCLUSIVE 급 락을 사용(동시 읽기/쓰기 차단).
디스크 공간	REWRITE/VACUUM FULL/CLUSTER 는 테이블 크기 상당의 임시 공간을 필요로 할 수 있음.
MVCC Caveats	TRUNCATE/REWRITE형 ALTER는 NOT MVCC-SAFE → 커밋 직후 이전 스냅샷 트랜잭션에서 빈 테이블처럼 보일 수 있음.

---

5. 런북

5.1 사전 체크리스트

1. 변경 분류: 위 결정표로 리라이트 여부와 인덱스 재구축 여부를 판정. (특히 VOLATILE DEFAULT/GENERATED STORED/IDENTITY/도메인 제약/타입 변경)
2. 락 허용도: 비즈니스 창구에서 ACCESS EXCLUSIVE 허용 가능한가? 대기/블로킹 시나리오를 정리.
3. 디스크 여유: 리라이트/VACUUM FULL/CLUSTER 시 일시적 2배 가까운 공간이 필요한지 점검.
4. 검증 전략: NOT VALID → VALIDATE 로 나눌 수 있는지, 검증 스캔 시간·락 수준을 계획.
5. 콜레이션/인덱스: 콜레이션 영향이 있으면 REINDEX 계획 수립.
6. 복제/서브스크라이버: 논리복제/리플리케이션 동작과 버전 이슈 확인.

---

5.2 적용 예시

▸ ADD COLUMN

ALTER TABLE t ADD COLUMN status text;
ALTER TABLE t ADD COLUMN status text DEFAULT 'active';  -- 상수 DEFAULT

→ Rewrite 없음. (대량 업데이트 예정이라면 DEFAULT 없이 추가 후 UPDATE/SET DEFAULT 순서도 고려)

---

▸ ADD COLUMN(Rewrite)

ALTER TABLE t ADD COLUMN created_at timestamptz DEFAULT clock_timestamp(); -- VOLATILE

→ Rewrite. (서비스 창구 확보 및 스토리지·락 점검 필수)

---

▸ ALTER TYPE(예외 케이스)

ALTER TABLE t ALTER COLUMN name TYPE varchar(200);  -- AS-IS varchar(50)

→ binary-coercible + USING X → 테이블 Rewrite 생략(인덱스는 상황 따라 재구축).

---

▸ DROP COLUMN + VACUUM FULL

ALTER TABLE t DROP COLUMN old_col;   -- DROP COLUMN은 공간 회수를 하지 않음
VACUUM FULL t;                       -- Rewrite를 통해 공간 회수

---

▸ ADD CONSTRAINT

ALTER TABLE t ADD CONSTRAINT t_chk NOT VALID CHECK (amount >= 0);
ALTER TABLE t VALIDATE CONSTRAINT t_chk;  -- SHARE UPDATE EXCLUSIVE 수준

---

5.3 중단(취소/오류/크래시) 시 동작

• ALTER TABLE은 트랜잭션 내에서 실행됩니다.
• 커밋 전 중단이면 전부 롤백되어 원본 유지 → 새 파일은 폐기되고, 원본 파일(relfilenode)은 그대로 유지됩니다. (Rewrite는 Atomic swap 모델)
• 중단/실패 시 롤백 과정에서는 MVCC Caveats이 발생하지 않습니다.
• Rewrite형 DDL은 커밋 직후 MVCC Caveats이 발생하여, 이전 스냅샷에서 테이블이 빈 것처럼 보일 수 있습니다.

MVCC Caveats?
아직 커밋 이전의 스냅샷을 들고 있는 다른 트랜잭션이 "이 테이블의 relfilenode가 바뀌었지만, 자신은 새 버전을 모르는 상태"이기 때문에 해당 세션에서 새 파일을 못 보고 빈 테이블처럼 인식하는 것입니다.

 

• 요약

단계	relfilenode 상태	MVCC 영향	테이블 가시성
Rewrite 중 (데이터 복사 진행 중)	새 relfilenode 생성, 커밋 전	없음	다른 트랜잭션은 여전히 원본 파일을 읽음
중단/롤백 시	새 파일 삭제, 원본 유지	없음	원본 그대로 유지
커밋 직후	relfilenode 교체 완료	있음 (기존 스냅샷만 비어 보임)	새 스냅샷에서는 정상
커밋 이후 새로운 트랜잭션	새 파일 읽음	없음	정상

---

5.4 사후 작업

 

• ANALYZE: 타입 변경 후 해당 컬럼의 통계가 제거되므로, 한번 수행해 주시는 게 좋습니다.
• REINDEX: 콜레이션 작업 시 세트로 생각하시면 됩니다.

---

그래서 초기 설계가 중요합니다. 운영 중 변경은 리스크도 크고 힘들어요.

 

아래는 참고용 공식 문서입니다.

 

ALTER TABLE

 

 

5.7. Modifying Tables

---

아.. 그리고 이게 진짜 중요한데... Rewrite 발생 시 테이블/인덱스 사이즈에 따라 대량의 WAL이 생성됩니다.

모든 영속적 변경은 WAL에 기록됩니다.

---

오늘은 여기까지~