Postgresでカラムを削除すると実際には何が起きるのか

 (thenile.dev)
22 ポイント 投稿者 GN⁺ 2025-04-25 | 3件のコメント | WhatsAppで共有
  • 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が完了するまではデータが残る
  • OSレベルでも 物理削除ではなく「削除フラグ」処理の可能性がある
  • 法的には「合理的な削除努力」が重要であり、物理ディスクを完全に消去するレベルまでは通常求められない

結論: DROP COLUMNは「隠す」だけで「削除」ではない

  • 性能のための設計だが、カラムが蓄積すると 1600個制限に達する可能性がある
  • 必要に応じて VACUUM FULL または テーブル再書き込みでデータ整理 が必要
  • システム設計やコンプライアンスの観点で PostgreSQLの内部動作を理解することは非常に有用

参考資料

関連記事

3件のコメント

 
ohyecloudy 2025-04-30

パフォーマンス最適化のための実装上の選択が、GDPRの忘れられる権利の問題とも関連づけて考えられるという視点が洞察に富んでいますね。個人データを適切にモデリングして削除することが核心であり、そこまで関連はないという結論に至る点も含めて、すっきりしています。
 
click 2025-04-25

PostgreSQL は最近かなり人気ですが、MVCC の実装については redo/undo 領域が別個に存在する方式のほうが好みです。
redo/undo 領域はある程度リアルタイム性を犠牲にしても構わないので、低グレードのストレージを使ってコスト最適化の余地がありますし、
いつかは DB 全体にロックをかけて VACUUM FULL しなければならないという点も好みではありません。
 
salsa 2025-04-26

VACUUM FULL はいずれ必ず実行しなければならないものなのでしょうか？ 私が見たドキュメントのほとんどでは、しないほうがよいとされていました。

私が見た資料の一つ:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/