(PostgreSQL) Default 값이 있는 새로운 필드 추가하기

Default 값이 있는 새로운 필드 추가하기

ALTER TABLE orders 

ADD COLUMN price bigint NOT NULL DEFAULT 0;

PostgreSQL 10 이하 버전에서는 테이블에 Default 값이 있는 필드를 추가할 땐 주의해야한다. 

Default값이 있는 필드를 추가할 때, 테이블을 다시 만들어내기 때문이다!

그러면 시간도 오래걸리고, ACCESS EXCLUSIVE LOCK도 잡게 된다. 

( 참고 - Fast Column Creation with Defaults )

ACCESS EXCLUSIVE LOCK이 걸려있는 동안, 이 테이블에 대한 모든 트랜잭션은 BLOCK된다! SELECT 쿼리까지 막힌다! 

( ACCESS EXCLUSIVE LOCK LEVEL은 LOCK LEVEL 중 가장 높은 레벨이다 )

필드를 추가할 때, Default 값이 없는 경우는 좀더 간단하다.

시스템카탈로그에 필드를 추가해주는 개념이여서, 테이블을 복제하지 않는다.

그래서 ALTER DDL이 완료되는 데에도 오랜시간이 걸리지 않는다. 

필드를 추가할 땐 항상 Lock이 걸린다. 그런데 Default 값이 없는 경우, 엄청 금방 처리되기 때문에 Lock이 문제되지 않는다. 

로우수가 200만개가 넘는 테이블에 Default 값이 있는 필드를 추가했을 때, 40초정도 걸렸는데, Default 값을 빼니 1초도 안걸렸었다. 

( 참고 - Does adding a null column to a postgres table cause a lock? ) 

Not Null 필드를 어떻게 추가하지? 

위에서 언급한 것처럼 기본값을 추가하는건 테이블을 다시 쓰는 작업이 발생한다. 그래서 데이터가 많은 테이블을 변경하는건 리스크가 있는 작업이다.

이 말은 Not Null 옵션을 적용해야하는 케이스에도 동일하게 적용된다. Not Null이려면, 항상 값이 있어야하기 때문이다. 

우회하는 방법으로는 우선 Null을 허용하는 기본 필드를 만들고 -> 데이터를 마이그레이션해주고 -> SET 명령어로 Not Null을 설정해주는거다.

SET NOT NULL은 테이블을 전체 스캔한다. 테이블을 다시 쓰는것보단 빨리 처리되지만, ACCESS EXCLUSIVE LOCK를 잡는다는 점에서 리스크가 있다. 그리고 테이블 크기가 크면, 시간도 오래걸린다. 

BUT, PostgreSQL 11버전에서는 이런 문제가 없다! 

2018년 5월에 출시된 Postgresql 11 버전에서는 기본값을 추가할 때, 테이블을 다시 쓰는 작업을 하지 않는다.

버전업되면서 성능 최적화가 되어서, ACCESS EXCLUSIVE LOCK도 발생하지 않는다. ( POSTGRESQL 11 BETA 1 RELEASED! /  Fast Column Creation with Defaults )

어떤 사람이 성능 테스트 후기 쓴걸 봤는데, 27초 정도 걸리던게, 1초도 안걸리게 개선되었다고 써뒀다. 대박!  

POSTGRESQL 11 – FAST ALTER TABLE ADD COLUMN WITH A NON-NULL DEFAULT

PostgreSQL 11에서는 기본값을 어떻게 처리하지?

PostgreSQL은 시스템 카탈로그의 pg_attribute 속성에 테이블 컬럼 정보를 저장한다. 

PostgreSQL 11버전에서는 여기에 atthasmissing, attmissingval 속성값을 추가적으로 저장하는데,

Default 값이 있는 필드가 새로 추가되기 전에 저장되었던 속성들은, attmissingval에 설정된 기본값을 내려주도록 하는거다 ㅎㅎ

( 그래서 테이블을 새로 쓰는 작업을 안해도 되는듯! )

기타

이 글은 postgresweekly에서 보내준 메일에서 발견한 Fast Column Creation with Defaults을 보고 작성했습니다 ㅎㅅㅎ/