(PostgreSQL) 테이블에 제약사항이 있는 컬럼 추가하기

Postgresql 테이블에 컬럼을 추가하는 작업은 신중하게 처리해야한다. 

사이즈가 큰 테이블에 기본값이 있거나, 제약사항이 있는 컬럼을 추가하는 작업은 리스크가 있기 때문이다.

이번 포스트에서는 제약사항이 있는 컬럼을 추가할 때 발생하는 리스크와 이를 분산시키는 방법에 대해서 정리해보고자 한다.

PostrgreSQL 컬럼 추가 동작 방식

ALTER ADD COLUMN 명령문을 실행하면, PostgreSQL은 아래와 같은 순서로 테이블을 변경한다.

1. 컬럼 추가하기

2. 신규 컬럼에 default 값 추가하기 ( Option )

3. 컬럼 제약사항 ( constraint ) 확인하기

PostgreSQL은 3번째 단계에서, 값의 유효성을 확인하기 위해 테이블을 풀스캔한다. 

이 때, 테이블 사이즈가 크다면, 컬럼을 추가하는 쿼리가 오래 걸리게 된다. 

테이블을 변경하는 작업은  ACCESS EXCLUSIVE LOCK을 잡는다. 이 LOCK은 INSERT / UPDATE / DELETE는 물론, SELECT 문까지 막는다. 

그래서 컬럼이 추가되는 동안, 서비스 다운타임이 발생할 가능성이 높다. 

만약 테이블에 초당 100개의 커넥션이 접근한다면, 컬럼 추가가 완료될 때까지 100개의 커넥션이 행걸리는 상황이 발생하게 된다. 

이러한 리스크를 분산시키는 방법은 컬럼 추가와 제약사항 추가 작업을 분리하는거다.

제약사항 추가 쿼리 분리 방법

ALTER TABLE orders ALTER ADD COLUMN price bigint CONSTRAINT positive_price CHECK (price > 0);

위의 DDL처럼 컬럼 추가와 제약사항 추가를 동시에 하던걸, 아래와 같이 쪼개줘야한다. 

1. 필드 추가하기 

ALTER TABLE orders ALTER ADD COLUMN price;

2. CONSTRAINT 추가할 때, NOT VALID 옵션주기

ALTER TABLE orders ADD CONSTRAINT positive_price CHECK (price > 0) NOT VALID;

NOT VALID 옵션은 기존 레코드가 유효한지 체크하지 않는다. 이 제약사항이 추가된 이후에 INSERT / UPDATE 된 레코드에 대해서만 유효성 체크를 한다.

3. VALIDATE CONSTRAINT - 기존 레코드가 유효한지 확인하기 

ALTER TABLE orders VALIDATE CONSTRAINT positive_price;

VALIDATE 명령문을 추가해주면, SHARE UPDATE EXCLUSIVE LOCK을 잡는다. 이 경우, ALTER Table 명령문만 막는다. 읽기/쓰기 쿼리를 막지않는다.

끝

 

ALTER QUERY는 항상 경건하게 날려야한다.  (Default 값이 있는 컬럼을 추가할 때에도, 갈레라 클러스터에서도 ) 

참고 링크

PostgreSQL ALTER TABLE

Use Not Valid To Immediately Enforce A Constraint