PostgresのDELETE操作は、高パフォーマンスな環境においてスケーラビリティが低いという評判があります。この評判は、Postgresがトランザクションの隔離を保証するために使用するマルチバージョン並行制御（MVCC）に起因しています。Postgresは行を物理的に削除する代わりに、トランザクションIDをスタンプしてそれを「デッド（死）」としてマークします。これにより、他の同時実行中のトランザクションはデータの整合性のあるスナップショットを見ることができます。しかし、これは「チャーン（入れ替わり）」を生み出し、デッドタプルが蓄積され、VACUUMと呼ばれるプロセスによって回収される必要があります。異なる種類のデータを異なる期間保持する必要があるような複雑なデータ保持を必要とするシステムでは、データベースパーティション全体を削除する硬直した構造よりも、標準的なDELETEの柔軟性が好まれることがよくあります。
このグラフは、削除速度に対するパフォーマンスのボトルネックとMVCCチャーンの影響を視覚化したものです。
DBOSのエンジニアは、耐久性の高いワークフローシステムの保持ポリシーを実装する際に、重大なパフォーマンスのボトルネックに直面しました。主な問題はキャッシュ局所性の欠如でした。ワークフローは個別のテーブルに記録された多くのステップで構成されているため、標準的な「ON DELETE CASCADE」外部キーを使用すると、Postgresは各ステップの削除ごとにディスクから離れたデータページを取得せざるを得ませんでした。これを解決するために、チームは外部キーを削除し、段階的な削除戦略へと移行しました。行を物理的な作成順序で削除することで、データベースがキャッシュされたページを再利用できるようにし、パフォーマンスを大幅に向上させました。また、これらのバッチ操作中にまだアクティブなワークフローに関連するデータが誤って削除されないように、アンチジョインを実装しました。
この図は、チームが最適化したワークフローとステップの構造的な関係を説明しています。
このSQLスニペットは、チームが移行を検討した標準的な「ON DELETE CASCADE」アプローチを表しています。
これらの改善にもかかわらず、チームは単純なクエリの完了に20分かかるという「病的な」インデックスルックアップのバグを発見しました。これは、PostgresがDELETE中にインデックスエントリを同期的に更新しないために発生しました。インデックスを走査するクエリは、autovacuumによってまだクリーンアップされていない数百万のデッドタプルに遭遇する可能性があるのです。これを解決するために、チームは各保持パスの前後に手動のVACUUM操作を導入しました。キャッシュ局所性を向上させるための外部キーの削除、安全性のためのアンチジョインの使用、そしてインデックスパスをクリアするための手動VACUUMの組み合わせにより、彼らのシステムは厳格なデータ保持ポリシーを維持しながら、毎秒数万のワークフローとステップを処理することが可能になりました。