PostgresのDELETEは本当にスケールする
How Postgres DELETE can actually scale
PostgresのDELETEは高コストでスケールしないとされ、多くのシステムがパーティション削除などで回避してきた。本記事は、MVCCによる削除の仕組みを解説したうえで、キャッシュとインデックスの性能をチューニングし、毎秒数万のワークフローを処理するDBOSの永続ワークフロー基盤でデータ保持ポリシーを適用した事例を紹介する。DELETEの柔軟性を活かしつつ大規模運用する方法を示す。
PostgresのDELETEは高くつく仕組みを多用して削除のトランザクション安全性を確保しているため、テーブルの全行を削除するのはテーブルをDROPするより桁違いに遅いのは確かだ。
- mike_hearn
MVCCはPostgresのやり方で動作する必要はない。他のデータベースの中には、DELETEやUPDATEのような書き込みがディスクブロックを直接上書きするものもある。行のコピーやバキュームは存在しない。MVCCを提供するために、データベースは「undoログ」を生成する。これは名前の通りで、以前のスナップショットでデータベースを読む必要がある場合、ディスクからデータを読み取り、その後undoデータを使ってMVCCスナップショット以降に行われた書き込みを元に戻す。undoデータは、それを必要とするライブトランザクションがなくなると定期的に削除される。
Postgresをこの種のモデルに変更するパッチが存在し、多くの利点がある。autovacuumの遅れによって引き起こされるすべての停止や問題がなくなる。しかし、パッチはマージされなかった。
Oracleには関連する機能がいくつかある：
- undoデータを保持し、タイムトラベルクエリ（SELECT .. AS OF）に使用できる。
- DMLを並列化でき、書き込みマスターは水平方向にスケールするため、大規模なDELETEを複数のマシンで同時に実行できる。ただし、手動でオプトインする必要がある：
ALTER SESSION ENABLE PARALLEL DML;
DELETE /*+ PARALLEL(t, 8) */
FROM t
WHERE created_at < :cutoff;
COMMIT;
https://docs.oracle.com/en/database/oracle/oracle-database/2...
とはいえ、ブログ記事で議論されている問題は、DELETE自体の処理速度に直接関係するものではなく、その後のデータベースのパフォーマンスに与える影響に関するものである。
開示：私はOracle DBグループでパートタイムの役割を担っている。
- bob1029
> パーティションは、それが保存するすべてのデータが不要になるまで削除できない。これは、メッセージキューなど、データが必要な期間が予測可能なシステムには適している。しかし、長時間実行されるワークフローをサポートするワークフローシステムには適していない。
ほとんどのワークフローが数日以内に完了するのであれば、長時間実行されるアイテムを明示的に前方にコピーし、その後パーティション全体を再利用することができる。ユースケースの1%未満を正規化する手間を惜しむために、極めて優れた技術的ソリューションを犠牲にするのはかなり性急に思える。特に、この技術的シンプルさの拒否がしばしばどこに導くかを考えるとそうだ。