Postgres에서 컬럼을 삭제하면 실제로 무슨 일이 일어나는가

 (thenile.dev)
22P by GN⁺ | ★ favorite | 댓글 3개
  • 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의 내부 동작 이해는 매우 유용

참고 자료

GeekNews Weekly에 포함된 글입니다. 에디터 코멘트 보기

함께 보면 좋은 글 β

ohyecloudy   [-]

성능 최적화를 위한 구현 선택이 GDPR 잊혀질 권리 문제와도 연관 지어서 생각할 수 있다는 관점이 통찰력있네요. 개인 데이터를 제대로 모델링하고 삭제하는 게 핵심이라 관련이 없다는 결론까지. 깔끔합니다.

답변달기
click   [-]

postgresql이 요새 인기가 좋지만 MVCC 구현은 redo/undo 영역이 별도로 존재하는 방식을 더 좋아합니다.
redo/undo 영역은 실시간성을 어느정도 희생해도 괜찮으니 낮은 등급의 스토리지를 사용해서 비용 최적화할 여지도 있고
언젠가는 DB 전체에 락을 걸고 VACUUM FULL 해야만 한다는 점도 불호 요소입니다.

답변달기
salsa   [-]

VACUUM FULL을 언젠가 반드시 해야 하는 것이 맞나요? 제가 봤던 문서들에서는 대부분 하지 말라고 하던데요.

제가 본 자료 중 하나:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/

답변달기