PostgreSQL에서의 ALTER TABLE 구문 정리

업무 진행 중 공유가 필요하다 보니 정리하게 된 내용을 블로그에 포스팅.

PostgreSQL 버전별 ALTER TABLE 구문 수행시 Table re-write 발생하는 경우 정리

** 정리 **

• ALTER TABLE ADD COLUMN... ; + ALTER TABLE ALTER COLUMN DEFAULT ... ;
  • 해당 구문 수행의 경우 기존 데이터는 두고 새롭게 생성되는 데이터에만 DEFAULT 적용
• ALTER TABLE ADD COLUMN ... DEFAULT ... ;
  • 해당 구문 수행시에는 기존 존재하던 null 데이터들도 DEFAULT 데이터 적용

 

• ALTER TABLE ADD COLUMN ... DEFAULT 관련
  • 11 버전에서는 기존 데이터 DEFAULT 값 설정 시에 table rewrite 발생
  • 12 버전부터는 기존 데이터 DEFAULT 값 설정 시에 table rewrite 발생하지 않음.

• 컬럼 데이터타입 변경시에는 케이스 별로 확인해야 함
  • 일반적으로 같은 종류의 더 큰 타입으로 갈 때에는 발생하지 않음.(varchar → text)
  • 다른 타입으로 변경 시에는 발생(varchar → integer)
• UNLOGGED 관련 변경 시에도 발생(해당 테이블에 대한 WAL 변경분 기록하지 않는 테이블)
  • 해당 부분 관련하여 속도를 위해 unlogged 테이블로 작업하는 경우 알아둬야 함.
  • 결국 unlogged 관련한 부분도 변경 일어나는 시점에서 WAL에 기록되어야 하기 때문
• 클러스터 명령어 수행시(특정 인덱스 기준으로 clustring factor를 위한 테이블 재작성)
• VACUUM FULL 시에는 발생(해당 명령어는 테이블을 재작성 하는 명령어)

Table Rewrite Scenarios for Community PostgresQL Version	v11	v12	v13	v14	v15	v16
ALTER TABLE ADD COLUMN INTEGER	NO	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN INTEGER NULL	NO	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN INTEGER NOT NULL	NO	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN INTEGER DEFAULT 100000	YES	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN INTEGER DEFAULT 100000 NULL	YES	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN INTEGER DEFAULT 100000 NOT NULL	YES	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN TEXT COLLATE german	NO	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN TEXT COLLATE german NULL	NO	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN TEXT COLLATE german NOT NULL	NO	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT 'ß'	YES	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT'ß'	YES	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT 'ß' NULL	YES	NO	NO	NO	NO	NO
ALTER TABLE ADD COLUMN TEXT COLLATE german DEFAULT 'ß' NOT NULL	YES	NO	NO	NO	NO	NO
ALTER TABLE DROP COLUMN	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN TYPE TEXT -- Binary compatible (for e.g. Varchar -> Text)	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN TYPE TEXT -- Binary incompatible (for e.g. Int -> Text)	YES	YES	YES	YES	YES	YES
ALTER TABLE ALTER COLUMN TYPE BIGINT -- Commonly sought after (for e.g. Int -> Bigint)	YES	YES	YES	YES	YES	YES
ALTER TABLE ALTER COLUMN TYPE TEXT COLLATE german; Previous column type Int	YES	YES	YES	YES	YES	YES
ALTER TABLE ALTER COLUMN TYPE TEXT COLLATE german;- Previous column type Text	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN TYPE TEXT COLLATE german; -- Previous column type Varchar()	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET DEFAULT 10000	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN DROP DEFAULT	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET NOT NULL	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN DROP NOT NULL	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN ADD GENERATED ALWAYS AS IDENTITY	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN DROP IDENTITY	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET STATISTICS -1 -- Change from non-deafult	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET STATISTICS 1000 -- Change from non-default	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET STATISTICS 1000 -- Change from default	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET (n_distinct=100)	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN RESET __distinct)	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET STORAGE MAIN	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET STORAGE PLAIN	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET STORAGE EXTERNAL	NO	NO	NO	NO	NO	NO
ALTER TABLE ALTER COLUMN SET STORAGE EXTENDED	NO	NO	NO	NO	NO	NO
ALTER TABLE CLUSTER ON	NO	NO	NO	NO	NO	NO
ALTER TABLE SET WITHOUT CLUSTER	NO	NO	NO	NO	NO	NO
ALTER TABLE SET WITH OIDS	YES	YES	---	---	---	---
ALTER TABLE SET WITHOUT OIDS	YES	YES	YES	YES	YES	YES
ALTER TABLE SET TABLESPACE tmp_tblspc	YES	YES	YES	YES	YES	YES
ALTER TABLE SET LOGGED	YES	YES	YES	YES	YES	YES
ALTER TABLE SET UNLOGGED	YES	YES	YES	YES	YES	YES
ALTER TABLE SET (FILLFACTOR=10)	NO	NO	NO	NO	NO	NO
ALTER TABLE RESET (FILLFACTOR)	NO	NO	NO	NO	NO	NO
ALTER TABLE INHERIT	NO	NO	NO	NO	NO	NO
ALTER INDEX set tablespace tmp_tblspc	NO	NO	NO	NO	NO	NO
DELETE FROM	NO	NO	NO	NO	NO	NO
CLUSTER	YES	YES	YES	YES	YES	YES
COMMENT ON TABLE IS testing	NO	NO	NO	NO	NO	NO
TRUNCATE TABLE	YES	YES	YES	YES	YES	YES
VACUUM FULL	YES	YES	YES	YES	YES	YES

출처: https://www.thatguyfromdelhi.com/