[PostgreSQL, MySQL] ADD COLUMN Default

PostgreSQL은 대용량 테이블의 컬럼 추가에 Default 값을 설정하여도 메타데이터 변경 수준의 시간이 소요됩니다.

특정 값을 바꾸고 해당 값을 조회시에 데이터에 쓴다는 정도로만 이해하고 있었는데 그렇다면 아래의 단계에서 데이터가 어떻게 조회될지 확인이 필요했습니다.

1. Default 값 1인 컬럼 추가

2. 특정 작업 이후 Default 값을 2로 변경

3. 과거 데이터가 1로 조회될지 2로 조회될지 ? ?

 

내부 프로세스를 이해한 뒤에는 1로 조회되는게 당연하다는 것을 알게되었습니다.

Default 값을 가진 컬럼을 추가하면 PG_ATTRIBUTE 시스템 테이블에 attmissingval 에 값이 추가되고 기존 데이터는 해당 컬럼의 값을 참조하여 데이터가 설정됩니다.

즉 SELECT 할 때 attmissingval 값을 확인하고 메모리상에서 처리하며 UPDATE가 되어야 DATAPAGE에도 반영됩니다.

1. TABLEA 생성

2. 데이터 10건(1~10) 입력

3. 신규 컬럼 추가 및 Default  'TEST1' 설정

4. 데이터 5건 (1~5) 조회

5. DEFAULT 'TEST2' 로 변경

5. 데이터 5건 (6~10) 조회

이때 6~10도 TEST1로 조회가 되는데 이유는 Default  값을 바꿀 때 attmissingval 가 바뀌는건 아니기 때문입니다.

 

MySQL도 8버전 부터 Instant DDL을 통해 Default 값을 가진 컬럼을 추가할 때 빠르게 처리가 됩니다.

이는 DATA DICTONARY에 메타데이터만 수정하기 때문입니다. 마찬가지로 조회 때 DATA DICTONARY를 한번 거쳐야하는 로직이 추가되고 해당 컬럼이 UPDATE되면 DATA FILE에 반영됩니다.