PostgresのDELETEは実際にスケールさせることができる方法

PostgresのDELETE処理は実際にスケールさせることができる

PostgresのDELETE処理は悪い評判があり、多くの高性能システムではDELETEを完全に避けるために、テーブル全体をTRUNCATEしたり、パーティション全体をDROPしたりしてデータをクリーンアップしています。もしDELETEがスケールしないのであれば、それは残念なことです。なぜなら、DELETEはパーティションやテーブルをDROPするよりもはるかに柔軟であり、特にデータが異種混合で、一部のアイテムを他のアイテムよりも長く保存する必要がある場合には、その柔軟性が重要になります。その非難は間違っていません。PostgresのDELETEは、DELETEがトランザクション的に安全であることを保証するために多くの高コストな仕組みを使用しているため、テーブルの全行を削除することは、テーブルをDROPするよりも桁違いに遅くなります。しかし、DELETEは依然としてスケールさせることができ、特にその柔軟性と強力な保証を必要とするワークロードにおいては可能です。

この記事では、適切な最適化を行うことで、PostgresのDELETEを大規模に利用する方法を示します。ここでは、Postgresベースの耐久性のあるワークフローおよびキューのワークロードでDELETEをどのようにチューニングし、キャッシュとインデックスのパフォーマンスを最適化して、毎秒数万件のワークフローとステップを処理するシステムにデータ保持ポリシーを適用したかに焦点を当てます。

Postgresはどのように行を削除するか

Postgresでの削除がなぜそれほどコストがかかるのかを理解するには、その仕組みを理解する必要があります。Postgresで行を削除するとき、データベースは実際には行を物理的に削除しません。これは、Postgresがデータベーストランザクション間の分離性（isolation）を提供する必要があるためです。あなたのトランザクションが行を削除した場合、その削除はコミットされるまで表示されるべきではありません。もしPostgresが行を直接削除した場合、それらの削除は他のトランザクションに即座に（高コストなロックを伴わない限り）見えてしまい、分離性を侵害します。

代わりに、PostgresはMVCC（Multi-Version Concurrency Control）を実装しています。行が更新されるたびに、Postgresは更新されたデータを含む行の新しいバージョンをテーブルとインデックスに挿入します。各バージョンには、それを生成したトランザクションのトランザクションIDと、それを置き換えたトランザクションのIDがタイムスタンプとして記録されます。行を削除すると、Postgresはその最終バージョンにあなたのトランザクションIDをタイムスタンプとして付け、その行を後続のトランザクションから見えなくします。この設計はトランザクション分離性を提供します。トランザクションによって行われた更新は、トランザクションがコミットされるまで表示されず、長時間実行されるトランザクションは古いバージョンにアクセスしてデータの整合性の取れた「スナップショット」を表示できます。

MVCCの結果の1つは、チャーン（churn）です。各更新と削除は「デッドタプル（dead tuple）」、つまり行の古いバージョンを作成し、Postgresはディスクスペースを再利用するために、いつかは実際に削除する必要があります。PostgresはVACUUM操作（autovacuumによって定期的に実行される）を通じてデッドタプルをクリーンアップします。VACUUMは、アクティブなトランザクションから見えなくなったすべてのデッドタプルを見つけて削除します。数百万ものワークフローとそのステップのデータを定期的に削除する必要がある耐久性のある実行システムを構築する際、このMVCCチャーンはPostgresのキャッシュとインデックスと微妙に相互作用し、削除を見た目よりもコストのかかるものにします。

［画像: Diagram - how Postgres executes a DELETE statement］

ローカリティとの格闘

ワークフローの削除は通常、保持ポリシーに従います。ユーザーはポリシー（例：「1週間以上前に完了したすべてのワークフローを削除する」）を設定し、ワークフローエンジンは定期的に期限切れのワークフローをすべて削除することでそれを強制します。DELETEはサイズNのバッチで発行され、残りのワークフローが保持カットオフよりも古くなるまで、N個の最も古いワークフローを繰り返し削除します。

［画像: Workflow deletion SQL statement］

興味深いことに、パフォーマンスの課題はワークフロー自体を削除することではなく、他のテーブルにある関連データを削除することです。例えば、ワークフローは通常いくつかのステップで構成され、それらのステップはstep_outputsテーブルにその出力を書き込みます。ステップデータをワークフローデータと同期させるために、当初は外部キーを使用していました。step_outputsの各行はworkflow_statusのワークフロー行を参照し、ON DELETE CASCADEが設定されていたため、ワークフローを削除するとそのステップも自動的に削除されました。

［画像: Cascading DELETE SQL statement to delete workflow steps］

これはデータ整合性の観点からは理にかなった教科書的な設計です。ワークフローのステップはワークフローと一緒に削除されるべきです。しかし、スケールさせるとパフォーマンスはひどいものになります。その理由はキャッシュの局所性です。ワークフローが削除されるたびに、Postgresはそれに属する各ステップを見つけて削除する必要がありました。ワークフローは通常、長期間にわたって多くのステップを実行するため、それらのステップレコードはディスク上で離れた場所に配置されます。そのため、各ステップの削除はディスクからメモリへのページ全体のフェッチ、更新、そしてディスクへの書き戻しを必要とします。このキャッシュチャーンは、ワークフローを実行するよりもワークフローを削除する方がはるかに遅いことを意味しました。

［画像: Diagram - DBOS Workflow and workflow step deletion in Postgres］

外部キーの削除

保持パフォーマンスを改善するために、workflow_statusテーブルを参照する外部キーを削除し、各テーブルからデータを個別に削除しました。外部キーがなくなったことで、保持処理は段階的に進みます。まず、以前と同様にワークフロー行を削除します。外部キーによるカスケードがないため、これは高速です。次に、step_outputsのようなワークフローデータを格納するテーブルで同様のバッチ削除を実行します。これらのバッチ削除は、workflow_statusのバッチ削除が高速であるのと同じ理由で高速です。それらは物理的な作成順に行を削除するため、キャッシュの局所性が高くなります。

この設計における一つの問題は、ワークフローは完了時間に基づいて削除されますが、データテーブルには作成時間のみが格納されていることです。まだ実行中の長時間実行ワークフローに関連するデータを誤って削除しないようにするために、バッチ削除にアンチジョインを含め、関連するワークフローが既に削除されているデータのみを削除するようにしています。

［画像: SQL code: a batch delete that finds the created_at cutoff of the batch-size-th oldest row, then deletes older rows using NOT EXISTS so data for still-present workflows is kept］

20分かかるインデックス検索のデバッグ

この新しい削除メカニズムは理論的には理にかなっていましたが、実装中に真に奇妙なパフォーマンスバグに遭遇しました。それは、20分もかかるインデックス検索でした。問題は、バッチ削除の最初のクエリにありました。このクエリは、テーブル内のバッチサイズ番目に古い行のcreated_atタイムスタンプを見つけます。このクエリはcreated_atのインデックスによって正しくサポートされているため、高速であるはずです。そして通常は高速でした。しかし、データベースが飽和状態に近いとき、そのパフォーマンスは崩壊し、この無害なクエリがクエリプランが正しくインデックスを使用しているにもかかわらず、完了するのに20分以上かかることがありました。

このクエリのパフォーマンスの問題は、PostgresのMVCCが削除をどのように処理するかに関連していることが判明しました。行を削除するために、Postgresはその行を削除したトランザクションのトランザクションIDでスタンプします。これにより、後続のトランザクションはそれを無視するように、そしてautovacuumがそれをクリーンアップできるようにします。しかし、このスタンプは行自体にのみ適用され、その行を参照するインデックスには適用されません。これは、インデックスを通じて行を検索しようとするクエリが、行がデッドタプルでないことを確認するために、行自体（インデックスエントリだけでなく）をチェックする必要があることを意味します。

通常、これは問題ではありません。なぜなら、デッドタプルはインデックス全体に均等に分散されているからです。しかし、この特定のSELECTクエリは最悪のシナリオに遭遇しました。テーブル内のバッチサイズ番目に古い行を見つけるために、クエリはcreated_atインデックスを古い方から新しい方へと辿ります。通常、これはO(バッチサイズ)の時間で完了し、十分に高速です。しかし、問題は、行が古い方から新しい方へと削除されることです。したがって、最近別の削除が実行され、autovacuumが追いついていない場合、インデックスの先頭に数百万ものデッドタプルが存在する可能性があり、クエリはそれらすべてをフェッチしてデッドタプルであることを確認してから続行する必要があります。これらの数百万ものデッドタプルチェックにより、クエリは爆発的に遅くなり、20分かかりました。

［画像: Benchmark - why Deletions can take much longer than you expect］

幸いなことに、原因がわかると修正は簡単でした。保持パスの前後で手動のテーブルVACUUMを挿入しました。これにより、デッドタプルは将来の保持期間を遅延させる前にクリーンアップされます。キャッシュの局所性を改善し、病的なインデックススキャンを回避することで、手動VACUUMと外部キーの削除の組み合わせにより、削除パフォーマンスが1桁向上しました。

なぜパーティショニングではないのか？

終わりに近づく前に、パーティション（またはテーブルのTRUNCATE）をDROPするという代替ソリューションに戻り、なぜそれを使用しないのかを説明したいと思います。繰り返しますが、一部の高性能Postgresベースシステム（pgmqやPgQueなど）は、データを時間でパーティション化し、不要になった古いパーティションをDROPすることで、削除を完全に回避しています。これはSQL DELETEよりもはるかに高速です。なぜなら、MVCCを全く経由せず、Postgresは古いデータを含むディスク上のファイルを文字通り削除するからです。

パーティショニングの問題は、その柔軟性のなさです。パーティションは、それが格納するすべてのデータが不要になるまで削除できません。これは、メッセージキューのように、データが予測可能な期間必要とされるシステムには適しています。しかし、長時間実行されるワークフローをサポートするワークフローシステムには適していません。数週間または数ヶ月続く少数の長時間実行ワークフローが、短期間で終了するワークフローに属する数十億もの行の削除をブロックする可能性があります。さらに、パーティションベースのシステムは、一部のユーザーが必要とする複雑な保持ポリシーをサポートできません。例えば、異なるクラスのワークフローを異なる期間保持したり、失敗したワークフローを成功したワークフローよりも長く保持したりすることです。

さらに学ぶ

スケーラブルで信頼性の高いシステムを構築することに興味があるなら、ぜひお声を聞かせてください。DBOSでは、Postgresベースの耐久性のある実行を可能な限りシンプルかつ高性能にすることを目指しています。ぜひご覧ください。

• クイックスタート: https://docs.dbos.dev/quickstart
• GitHub: https://github.com/dbos-inc
• Discordコミュニティ: https://discord.gg/eMUHrvbu67