Postgresで最もスケーラブルな削除手法？それは「DROP TABLE」一択だ

Postgresにおいて、膨大なデータを効率的かつ確実に削除する唯一のスケーラブルな手段、それは「DROP TABLE」を実行することに他ならない。インデックスの肥大化やVACUUMのオーバーヘッドに頭を抱える必要はもうありません。

MySQL / MariaDBでも程度の差こそあれ同じ状況。tupleの古いものから新しいものへのチェーンをたどる形式ではないからマシだけど、それでもDBには無視できない負荷がかかってる。削除（あるいは削除予定）のtupleが他のトランザクションからどう見えるかを気にしないなら、ほとんど無駄な作業だし。Planetscaleの取り組みが、RDBMSの運用に対する開発者の認識や理解を長期的に変えていくことを心から期待してる。彼らのブログやドキュメントはかなり質が高いからね。自分の経験だと、開発者（Ops寄りチームでさえも）はこの辺りを全く気にせず、DBの機能を理解していないか、パーティション化されたスキーマに伴う[わずかな]複雑さを避けるために、わざわざ手間のかかるカスタムツールを作って一括DELETEを走らせてしまう。そして、その結果生じるコストやレイテンシの増加を喜んで受け入れているんだ。

「スケーラブル」という言葉の使い方が変だよ。最初の文に「直感に反して、大量のDELETEはデータベースの負荷を増やす」とあるけど、これのどこが直感に反するの？行を削除するのも挿入するのも同じだけの負荷がかかるんだから当然でしょ。ログの書き込み、削除の書き込み、インデックスの更新、レプリケーションなど、ほとんど同じ操作が必要になるのは当たり前。それから、これは（Postgresだけでなく）すべての主要なリレーショナルデータベースで有名なテクニックだけど、巨大なテーブルから90%の行を削除したいなら、残したい行を新しいテーブルにコピーして、古いテーブルをDROP TABLEして、新しいテーブルをリネームする方がずっと速い。DROP TABLEはテーブルレベルのメタデータを操作するだけだから、ほぼ一瞬で終わるからね。DELETE自体は、行を1つずつ挿入・削除する限りにおいてはINSERTと同じようにスケールするよ。データベースの基本的な機能は、大量の小さなトランザクションを前提に設計されている。数百万行を一気に扱うような操作が必要なときは、「バルク」処理が得意な解決策を探す必要がある。例えば、SQL経由ではなくファイルから直接データをロードしたり、インデックスをロード後に追加したり、制約が有効だと分かっているなら大規模操作時に外部キーチェックを無効にしたり、そしてもちろんDELETEの代わりにDROP TABLEを活用したりね。これは小さなトランザクションがスケーラブルじゃないという意味ではなく、バルク操作は性質が異なるから専用の解法が必要だということ。DELETEもその点ではINSERTと同じだよ。

これはほとんど（あるいはすべて？）のデータベースに当てはまる一般論だね。大規模環境において、削除したリソースを解放する形の選択的削除は、未だに解決されていない大きな課題だ。データベースの内部では、これをより効率的にスケールするリソースの切り捨てに変換しようとするけれど、データモデルを慎重に設計しない限り、ほとんどの場合は不可能。同様に、多くのデータベースにおいてUPDATEは実質的にINSERT + DELETEであり、それに伴うスケールの問題もついて回ることを開発者に何度も思い出させる必要があるんだ。

CRUDアプリは通常、バルクで削除なんてしないよね。それに、数ヶ月分の重要なビジネスデータを消さずにパーティションを構成するのは難しい。だからチームはDBをSnowflakeやClickHouseにETLして、それからパーティションをドロップすることが多いわけ。そうすると、アプリ側でまたそのデータを使うのが難しくなるんだけどね。より良いアプローチは、ストレージエンジンを変える（例えばOrioleDBはPgにundoログを追加しようとしている）か、シャード化してvacuumの負荷を複数のサーバーに分散させることだね。

pg_partmanについては今日初めて知った。
https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md

部分的には正しいけど、あまりに一般化しすぎだし、クリックベイト的だね。適切にチューニングされたautovacuumがあれば、DELETEはかなりうまく動作する。TBスケールでも何の問題もなく動いているのを見たことがあるよ。もしDELETEが大規模なら、将来のために空き領域を即座に再利用できるよう、手動でVACUUMを実行するよう顧客には勧めていた。DROP TABLEはリスクがあって、ACCESS EXCLUSIVE LOCKが必要になる。Postgresのロックキューの仕様上、もし前の処理が待機状態なら、その後の処理もすべてブロックされてしまうんだ。それに、高負荷なCRUDアプリでDROP TABLEを高頻度で繰り返す運用は無理があるよ。

驚くことに、複数のテーブルから少数の行を削除する場合（例えば自動テスト間のクリーンアップなど）、TRUNCATEよりもDELETEの方が速いことが多いよ！直感に反するけど、自分で計測してみれば分かるはず。ちなみに、CTEを使えば1つのステートメントで複数のテーブルからDELETEできるから、外部キーの依存順序を気にする必要もなくなるよ。

個人的には、テーブル全体をクリアする必要があるっていうのは、設計のどこかが間違っているという兆候だと思う。誤解しないでほしいのは、自分もこれまで確かにやってきたし、決して悪ではないということ。ただ、自分の中ではVACUUMと同じ分類なんだよね。自分のミスを修正するための、影響の大きい介入であって、「日常的な業務」で行うアクションじゃない。

DROP TABLEを何度も呼ぶのがオーバーヘッドだと感じた時のために、DROP DATABASEがあるんだよ……

何年も前に、DELETEに関してはOracleのDBの方がPGより一日の長があるって聞いたな。たぶん今でもそうなんだろうね……