Postgres에서 컬럼을 삭제하면 실제로 무슨 일이 일어나는가
(thenile.dev)- PostgreSQL에서 컬럼을 DROP 해도 실제로 데이터는 삭제되지 않음 — 단지 메타데이터에서 "숨김 처리" 됨
- DROP COLUMN 후에도 내부적으로 컬럼은 존재하기 때문에, 1600개 컬럼 제한에 도달할 수 있음
- 데이터를 완전히 제거하려면
VACUUM FULL또는 수동 테이블 재작성이 필요함 - 이는 성능 최적화를 위한 설계지만, GDPR과 같은 컴플라이언스 관점에서는 주의 필요
- "실제로 무슨 일이 일어나는가"를 이해하면 문제 해결, 성능 최적화, 데이터 관리 측면에서 유리함
PostgreSQL에서 DROP COLUMN의 실제 작동 방식
문제 상황: 컬럼을 반복해서 추가/삭제하면?
- 다음과 같은 코드로 컬럼을 2000번 추가 후 삭제:
ALTER TABLE t ADD COLUMN c1 int; ALTER TABLE t DROP COLUMN c1; ... - 최종적으로 테이블에 남은 컬럼은 2개뿐인데도, PostgreSQL은 1600컬럼 제한 오류를 발생시킴
- 이유는? 삭제한 컬럼도 내부적으로는 여전히 존재
PostgreSQL 내부에서 무슨 일이 벌어지나?
컬럼 삭제는 "진짜 삭제"가 아님
- PostgreSQL은 데이터를 8KB 페이지 단위로 저장
- 컬럼을 물리적으로 삭제하려면 전체 테이블을 다시 작성해야 하므로 비효율적
- 대신, 컬럼을 메타데이터에서 'dropped' 상태로 표시하고 무시함
pg_attribute 시스템 테이블로 확인 가능
SELECT attnum, attname, attisdropped FROM pg_attribute WHERE attrelid = 'test2'::regclass AND attnum > 0;
- 출력 예시:
attnum | attname | attisdropped --------+--------------------------+-------------- 1 | a | f 2 | ........pg.dropped.2.... | t 3 | c | f -
attisdropped = t인 컬럼은 쿼리에서는 무시되지만 내부적으로 남아 있음
데이터 파일에서 확인하기 (pg_filedump 활용)
- PostgreSQL 데이터 파일을 분석하면, 삭제된 컬럼의 값이 실제로 남아 있는 것을 확인 가능
- 예전 데이터(
Item 1)에는 3개의 컬럼 값이 존재 - 삭제 후 삽입된 데이터(
Item 3)에는 해당 컬럼 값이 없고,NULL로 처리됨
삭제된 컬럼의 실제 제거 방법
1. VACUUM FULL
- 전체 테이블을 재작성하며 삭제된 컬럼의 데이터도 제거
- 단점: 컬럼 자체는 여전히 pg_attribute에 존재하며 ‘dropped’ 상태
2. 수동 테이블 재작성
- 새로운 테이블을 생성하고 필요한 컬럼만 SELECT해서 복사
CREATE TABLE new_table AS SELECT a, c FROM old_table; - 제약조건, 인덱스, 트리거 등은 수동으로 다시 생성 필요
-
pg_dump로 백업 → 덤프 파일에서 수정 → 복원 방식도 가능
컬럼 DROP과 GDPR '잊혀질 권리' 문제
- 일부는 "컬럼이 실제 삭제되지 않으면 GDPR 위반 아닌가?"라는 우려 제기
- 하지만 개인 정보 삭제는 보통 행(row) 단위로 수행됨
DELETE FROM users WHERE id = <user_id>; -- 또는 관련 테이블 포함하여 삭제 - 컬럼 DROP은 GDPR과 직접 관련이 없으며, 개인 데이터를 제대로 모델링하고 삭제하는 것이 핵심
주의사항
- PostgreSQL은 MVCC 방식이기 때문에, 행 삭제 후에도 VACUUM이 완료되기 전까지 데이터가 남아있음
- 운영체제 레벨에서도 물리적 삭제가 아닌 "삭제 플래그" 처리일 수 있음
- 법적으로는 “합리적인 삭제 노력”이 중요하며, 물리 디스크를 완전히 지우는 수준은 대부분 요구되지 않음
결론: DROP COLUMN은 “숨기기”일 뿐 “삭제”가 아님
- 성능을 위한 설계지만, 컬럼이 쌓이면 1600개 제한에 걸릴 수 있음
- 필요한 경우
VACUUM FULL또는 테이블 재작성으로 데이터 정리 필요 - 시스템 설계나 컴플라이언스 관점에서 PostgreSQL의 내부 동작 이해는 매우 유용
참고 자료
postgresql이 요새 인기가 좋지만 MVCC 구현은 redo/undo 영역이 별도로 존재하는 방식을 더 좋아합니다.
redo/undo 영역은 실시간성을 어느정도 희생해도 괜찮으니 낮은 등급의 스토리지를 사용해서 비용 최적화할 여지도 있고
언젠가는 DB 전체에 락을 걸고 VACUUM FULL 해야만 한다는 점도 불호 요소입니다.
VACUUM FULL을 언젠가 반드시 해야 하는 것이 맞나요? 제가 봤던 문서들에서는 대부분 하지 말라고 하던데요.
제가 본 자료 중 하나:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/
성능 최적화를 위한 구현 선택이 GDPR 잊혀질 권리 문제와도 연관 지어서 생각할 수 있다는 관점이 통찰력있네요. 개인 데이터를 제대로 모델링하고 삭제하는 게 핵심이라 관련이 없다는 결론까지. 깔끔합니다.