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의 내부 동작 이해는 매우 유용
참고 자료
성능 최적화를 위한 구현 선택이 GDPR 잊혀질 권리 문제와도 연관 지어서 생각할 수 있다는 관점이 통찰력있네요. 개인 데이터를 제대로 모델링하고 삭제하는 게 핵심이라 관련이 없다는 결론까지. 깔끔합니다.
postgresql이 요새 인기가 좋지만 MVCC 구현은 redo/undo 영역이 별도로 존재하는 방식을 더 좋아합니다.
redo/undo 영역은 실시간성을 어느정도 희생해도 괜찮으니 낮은 등급의 스토리지를 사용해서 비용 최적화할 여지도 있고
언젠가는 DB 전체에 락을 걸고 VACUUM FULL 해야만 한다는 점도 불호 요소입니다.