こんにちは!アスクルのうっしーです。
社内業務をシステム化するチームに所属しており、要件定義からアプリケーション開発まで幅広く業務を担当させてもらってます。
本記事では、私たちのチームでも使用しているPostgreSQLの論理削除と物理削除について、少しお話しします。
PostgreSQLとは？
↓以下公式ドキュメントまま引用。
PostgreSQLは、カリフォルニア大学バークレイ校のコンピュータサイエンス学科で開発されたPOSTGRES, Version 4.2をベースにしたオブジェクトリレーショナルデータベース管理システム（ORDBMS）です。 POSTGRESは後からいくつかの商用データベースで利用できるようになった、多くの概念についての先駆となりました。 PostgreSQLはオリジナルのバークレイ校のソースコードを引き継ぐオープンソースのデータベースで、標準SQLの大部分やその他の最新の機能をサポートしています。 さらに自由主義的ライセンス条件により、PostgreSQLは誰にでも、その使用、変更 、配布を個人使用、商用、学術など目的に限らず無償で可能です。
参考：https://www.postgresql.jp/document/17/html/intro-whatis.html
論理削除と物理削除
PostgreSQLの論理削除と物理削除の話をする前に、まず論理削除と物理削除とは？ というところからお話しさせていただきます。
論理削除とは？
表面的にはデータを削除したように見せるが、データベース内にはデータを残す削除方法のことを論理削除といいます。
たとえば「削除フラグ」というカラムをもつようにし、そこにフラグをたてることで削除されたデータとして扱うが実際にはデータとして保持している、といったものが論理削除にあたります。
postgres=# SELECT * FROM testtable1; id | name | age | delete_flag ----+--------+-----+------------ 1 | taro | 18 | f 2 | jiro | 19 | f 3 | saburo | 20 | t
あくまでデータベース内でデータを削除したようにみせるだけなので、データの復元が容易に行える一方で、データとしては残り続けるため、データベースの容量を圧迫する可能性があるのが論理削除になります。
わかりやすいイメージでいうと、書類をゴミ箱には入れるけど、ゴミ箱の中にはまだ書類が残っているので、取り出せることができる状態をイメージしていただければ。
物理削除とは？
実際にデータベースからデータそのものを削除することを物理削除といいます。
論理削除と違い、物理削除したデータは基本的に参照、復元が行えない一方で、削除したデータがデータベースの容量を圧迫することがありません。
不要になった書類はゴミ箱ではなく、焼却場にもっていって燃やしちゃう、みたいなことがデータベースの物理削除にあたります。
PostgreSQLの論理削除と物理削除について
ここまででなんとなく論理削除と物理削除の違いについて、理解いただけたと思うので、本題のPostgreSQLにおける論理削除と物理削除についてお話しします。
postgres=# SELECT * FROM testtable2; id | name | age ----+--------+----- 1 | taro | 18 2 | jiro | 19 3 | saburo | 20
このようなテーブルがある時に次のクエリを実行するとどうなるでしょうか？
DELETE FROM testtable2 WHERE id = 3;
当然、DELETEされたので、SELECTした結果は次になります。
postgres=# SELECT * FROM testtable2; id | name | age ----+------+----- 1 | taro | 18 2 | jiro | 19
しかしヒープページ上の行ポインタを確認すると、次のように表示されます。
postgres=# SELECT * FROM heap_page_item_attrs(get_raw_page('testtable2', 0), 'testtable2'); lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_attrs ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------------------------------- 1 | 8152 | 1 | 40 | 784 | 0 | 0 | (0,1) | 3 | 2306 | 24 | | | {"\\x01000000","\\x0b7461726f","\\x12000000"} 2 | 8112 | 1 | 40 | 785 | 0 | 0 | (0,2) | 3 | 2306 | 24 | | | {"\\x02000000","\\x0b6a69726f","\\x13000000"} 3 | 8072 | 1 | 40 | 786 | 787 | 0 | (0,3) | 8195 | 1282 | 24 | | | {"\\x03000000","\\x0f73616275726f","\\x14000000"}
上記出力結果のt_xminにはデータが作成されたトランザクションIDが、t_xmaxにはデータ削除がされたトランザクションIDが記載されてます。
そのため、lpが1、２のデータではt_xmaxが0(未削除)であるのに対し、lpが3のデータはt_xmaxが787となっており、トランザクションIDが787で、lpが3のデータが見た目上は削除されていることがわかります。
このようにテーブル上では2レコードしかないが、deleteした分も含まれているのはPostgreSQLが追記型アーキテクチャである1つの特徴になります。
参考：https://www.postgresql.org/docs/current/ddl-system-columns.html
そのため、実はDELETEしてしまったレコードに対しても次のようにすれば復元できたりもします。
postgres=# SELECT heap_force_freeze('testtable2'::regclass, ARRAY['(0, 3)']::tid[]); postgres=# SELECT * FROM testtable2; id | name | age ----+--------+----- 1 | taro | 18 2 | jiro | 19 3 | saburo | 20
ただし、この方法はレコードの状態に関係なく強制的にFREEZE済の状態に戻すもののため、使用する際は十分な注意が必要となります。
(※本来の使用用途はデータ破損などでデータにアクセスできなくなったり、可視性が破壊されたタプルによってテーブルがバキュームできなくなった時に使用するものを想定されているもののため、データ復旧方法として常に推奨するわけではないです。)
実際に以下で試したところ、t_xminやt_infomaskなどが元の値と変わっていることがわかります。
postgres=# SELECT * FROM heap_page_item_attrs(get_raw_page('testtable2', 0), 'testtable2'); lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_attrs ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------------------------------- 1 | 8152 | 1 | 40 | 784 | 0 | 0 | (0,1) | 3 | 2306 | 24 | | | {"\\x01000000","\\x0b7461726f","\\x12000000"} 2 | 8112 | 1 | 40 | 785 | 0 | 0 | (0,2) | 3 | 2306 | 24 | | | {"\\x02000000","\\x0b6a69726f","\\x13000000"} 3 | 8072 | 1 | 40 | 2 | 0 | 0 | (0,3) | 3 | 2818 | 24 | | | {"\\x03000000","\\x0f73616275726f","\\x14000000"}
参考：https://www.postgresql.jp/document/17/html/pgsurgery.html
以上からわかるようにPostgreSQLでは、DELETEやUPDATEをしてもデータベース上からデータは完全削除されない、論理削除となってます。
vacuumについて
ここまで見ていただくと分かるとおり、PostgreSQLではDELETEやUPDATEをしてもデータベース上にはデータが残っているため、実際には不要なデータが蓄積され続けます。
そこでPostgreSQLではvacuumという処理を行うことで、論理削除されている不要なデータを物理削除しています。
実際に次の例でvacuumをしてみるとどうなるのかを示します。
まずはDELETEをします。
postgres=# DELETE FROM testtable2 WHERE id=3; DELETE 1 postgres=# SELECT * FROM testtable2; id | name | age ----+------+----- 1 | taro | 18 2 | jiro | 19
ここでヒープページ上のデータを確認してみましょう。
postgres=# SELECT * FROM heap_page_item_attrs(get_raw_page('testtable2', 0), 'testtable2'); lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_attrs ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------------------------------- 1 | 8152 | 1 | 40 | 784 | 0 | 0 | (0,1) | 3 | 2306 | 24 | | | {"\\x01000000","\\x0b7461726f","\\x12000000"} 2 | 8112 | 1 | 40 | 785 | 0 | 0 | (0,2) | 3 | 2306 | 24 | | | {"\\x02000000","\\x0b6a69726f","\\x13000000"} 3 | 8072 | 1 | 40 | 2 | 791 | 0 | (0,3) | 8195 | 1794 | 24 | | | {"\\x03000000","\\x0f73616275726f","\\x14000000"}
先ほどもお見せしたように、lp=3のデータは見た目上削除されており、ヒープ上にデータとしては残っていることがわかります。
この状態でvacuumを行い、再度ヒープページ上のデータを確認してみましょう。
postgres=# vacuum testtable2; VACUUM postgres=# SELECT * FROM heap_page_item_attrs(get_raw_page('testtable2', 0), 'testtable2'); lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_attrs ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------------------- 1 | 8152 | 1 | 40 | 784 | 0 | 0 | (0,1) | 3 | 2306 | 24 | | | {"\\x01000000","\\x0b7461726f","\\x12000000"} 2 | 8112 | 1 | 40 | 785 | 0 | 0 | (0,2) | 3 | 2306 | 24 | | | {"\\x02000000","\\x0b6a69726f","\\x13000000"}
vacuum処理によって、lp=3のデータがヒープ上からも削除されました。
PostgreSQLではこのようにして不要なデータが物理削除されます。
autovacuumについて
では不要なデータが溜まってきたら開発者は毎回vacuumを手動実行しなければならないのか、と言われるとそうではありません。
PostgreSQLには autovacuum という機能が備わっており、これが有効であれば特定の条件を満たした時にvacuumが自動で実行されるようになります。
以下はpostgreSQL 17.5でデフォルトとして入っていた内容になります。
postgres=# SELECT name, setting, unit, short_desc FROM pg_settings WHERE name like '%autovacuum%'; name | setting | unit | short_desc ---------------------------------------+-----------+------+------------------------------------------------------------------------------------------- autovacuum | on | | Starts the autovacuum subprocess. autovacuum_analyze_scale_factor | 0.1 | | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. autovacuum_analyze_threshold | 50 | | Minimum number of tuple inserts, updates, or deletes prior to analyze. autovacuum_freeze_max_age | 200000000 | | Age at which to autovacuum a table to prevent transaction ID wraparound. autovacuum_max_workers | 3 | | Sets the maximum number of simultaneously running autovacuum worker processes. autovacuum_multixact_freeze_max_age | 400000000 | | Multixact age at which to autovacuum a table to prevent multixact wraparound. autovacuum_naptime | 60 | s | Time to sleep between autovacuum runs. autovacuum_vacuum_cost_delay | 2 | ms | Vacuum cost delay in milliseconds, for autovacuum. autovacuum_vacuum_cost_limit | -1 | | Vacuum cost amount available before napping, for autovacuum. autovacuum_vacuum_insert_scale_factor | 0.2 | | Number of tuple inserts prior to vacuum as a fraction of reltuples. autovacuum_vacuum_insert_threshold | 1000 | | Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums. autovacuum_vacuum_scale_factor | 0.2 | | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. autovacuum_vacuum_threshold | 50 | | Minimum number of tuple updates or deletes prior to vacuum. autovacuum_work_mem | -1 | kB | Sets the maximum memory to be used by each autovacuum worker process. log_autovacuum_min_duration | 600000 | ms | Sets the minimum execution time above which autovacuum actions will be logged.
参考：https://www.postgresql.jp/document/17/html/runtime-config-autovacuum.html
以下のPostgreSQLの公式ドキュメントを確認すると、以下の文言が記載されています。
参考：https://www.postgresql.jp/document/17/html/routine-vacuuming.html
テーブルのrelfrozenxid値がautovacuum_freeze_max_ageトランザクション年齢よりも古い場合、そのテーブルは常にバキュームされます （これはfreeze max ageがストレージパラメータにより変更されたテーブルに対しても適用されます。以下を参照）。 さもなければ、直前のVACUUMの後に不要となったタプル数が「バキューム閾値」を超えると、テーブルはバキュームされます。 このバキューム閾値は以下のように定義されます。 バキューム閾値 = バキューム基礎閾値(autovacuum_vacuum_threshold) + バキューム規模係数(autovacuum_vacuum_scale_factor) * タプル数(pg_class.reltuples)
※pg_class.reltuples：テーブル内のdeleteされてない行数
参考：https://www.postgresql.org/docs/current/catalog-pg-class.html
上記の式からわかるように、vacuumはテーブル行数に比例する形で実行可否を決めています。
したがって、テーブルが肥大化すればするほどautovacuumが発動するまでの閾値は大きくなるため、特に大規模テーブルの場合は一括で大量データをvacuumしようとした結果、大量のI/Oトラフィックが発生し他の実行中のセッションの性能劣化につながることもあります。
そのため、特に大きなテーブルを扱う際にはvacuumの設定を見直してみたりするのがよいかなと思います。
さいごに
今回はPostgreSQLにおける論理削除と物理削除についてご紹介しました。
これは個人的な意見ですが、AIがコードもすべて書いてくれる時代になったからこそ、私たち開発者はこのような基本的なことを押さえておくことで、AIが正しいことを言っているかどうかを評価することがこれから求められると考えています。
私自身も自身の勉強と知識の整理のためにこのテーマを選ばせていただきましたが、色々調べていて、改めて「へぇー」ってなる部分も多くとても有意義なものとなりました。
この記事によって、みなさんの知識の引き出しが1つ増えることを願って、本記事を締め括らせていただきます。 ここまでお付き合いいただきありがとうございました！
参考資料
https://www.postgresql.jp/document/17/html/intro-whatis.html https://www.postgresql.org/docs/current/ddl-system-columns.html https://www.postgresql.jp/document/17/html/pgsurgery.html https://www.postgresql.jp/document/17/html/runtime-config-autovacuum.html https://www.postgresql.jp/document/17/html/routine-vacuuming.html https://www.postgresql.org/docs/current/catalog-pg-class.html
