PostgreSQL 컬럼 변경 영향도

개요
운영 작업 간 테이블 컬럼 추가, 컬럼 타입 변경, 인덱스 작업이 필요하여 영향도 확인 테스트
 
내용
테이블은 2.1G / 680 만건  db.r6g.xlarge 인스턴스 타입
해당 컬럼은 인덱스 O, VARCHAR(255) 상태
테스트 아래와 같이 진행

1. 컬럼 타입 변경 (varchar(255) → varchar(1000), varchar(100) → text, text → varchar(1000), varchar(1000) → varchar(255) 4개 케이스 경우 테스트)
2. 인덱스 삭제
3. 컬럼 삭제 
4. 신규 컬럼 추가
5. 온라인 인덱스 생성

 
테스트 스크립트 및 결과
LOCK 조회 쿼리 

SELECT  t.relname,
        l.locktype,
        page,
        virtualtransaction,
        pid,
        mode,
        granted
FROM pg_locks l,
     pg_stat_all_tables t
WHERE l.relation = t.relid
and t.relname = 
ORDER BY relation ASC;

 
작업 쿼리

--케이스 1 VARCHAR(255) → VARCHAR(1000)
ALTER TABLE TABLEA ALTER COLUMN error_log TYPE VARCHAR(1000)
 
--케이스 2 VARCHAR(1000) → TEXT
ALTER TABLE TABLEA ALTER COLUMN error_log TYPE TEXT
 
--케이스 3 TEXT → VARCHAR(1000)
ALTER TABLE TABLEA ALTER COLUMN error_log TYPE VARCHAR(1000)
 
--케이스 4 VARCHAR(1000) → VARCHAR(255)
ALTER TABLE TABLEA ALTER COLUMN error_log TYPE VARCHAR(255)
 
--만약 지연이 있다면 인덱스 제거 후 동일하게 진행
 
--지연이 없다면
 
 
--ADD COLUMN IS_ERROR BOOLEAN DEFAULT FALSE
ALTER TABLE TABLEA ADD COLUMN is_error BOOLEAN NOT NULL DEFAULT FALSE
 
--DROP VARCHAR
ALTER TABLE TABLEA DROP COLUMN error_log
 
--ADD COLUMN TEXT DEFAULT
ALTER TABLE TABLEA ADD COLUMN  error_log TEXT NOT NULL DEFAULT '{}'
 
--CREATE INDEX
CREATE INDEX CONCURRENTLY IF NOT EXISTS INDEX_TABLEA ON TABLEA (is_error, created_at);



--케이스 1 VARCHAR(255) → VARCHAR(1000)
0초 미만
데드 튜플 증가 X
 
--케이스 2 VARCHAR(1000) → TEXT
0초 미만
데드 튜플 증가 X
 
--케이스 3 TEXT → VARCHAR(1000)
64초
TABLEA relation    ShareLock
TABLEA relation    AccessExclusiveLock
 
특이사항
table rebuild가 일어나는 것으로 보임 DDL 과정 중 데이터 사이즈 2배로 증가
SELECT relfilenode FROM pg_class WHERE relname = 'TABLEA'; 값이 바뀜
테이블 리빌드로 인한 테이블 나이 초기화
 
--케이스 4 VARCHAR(1000) → VARCHAR(255)
60초
TABLEA relation    ShareLock
TABLEA relation    AccessExclusiveLock
 
특이사항
table rebuild가 일어나는 것으로 보임 DDL 과정 중 데이터 사이즈 2배로 증가
SELECT relfilenode FROM pg_class WHERE relname = 'TABLEA'; 값이 바뀜
테이블 리빌드로 인한 테이블 나이 초기화
 
--인덱스 삭제 이후 작업도 동일한 결과 확인
인덱스 삭제 소요 시간 0초 미만
데드 튜플 증가 X
 
--ADD COLUMN IS_ERROR BOOLEAN DEFAULT FALSE
0초 미만 (조회 하여도 사이즈 증가 X)
데드 튜플 증가 X
 
--DROP VARCHAR
0초 미만
데드 튜플 증가 X
 
--ADD COLUMN TEXT DEFAULT
0초 미만
데드 튜플 증가 X
 
--CREATE INDEX
5초 소요됨
데이터 입력 지연 없음
TABLEA relation    ShareUpdateExclusiveLock

 
 
9.2 버전 이후 일부 컬럼 수정에 대하여는 테이블 재작성이 발생하지 않음.

 
11 버전 이후 정적인 default value를 포함한 컬럼 추가에 대하여 성능 개선 (메타데이터 수정)
 
CONCURRENTLY 인덱스 생성
MySQL의 온라인 인덱스 변경처럼 별도의 메모리 버퍼에 작업간 추가, 변경되는 데이터를 저장하지 않고 2번의 테이블 스캔을 사용하여 인덱스를 생성합니다. 
테이블 스캔 → 인덱스 생성 → 1차 인덱스 빌드 완료 → 테이블 스캔 → 변경, 누락된 데이터 인덱스에 병합 → 인덱스 생성 완
https://medium.com/@zl2-/concurrently-index-creation-9fd89ebd2bce