https://www.okb-shelf.work/entry/estimate_update

実行時間を知りたい

サービスの運用中には、テーブル構造を変更するマイグレーションがよく発生します。
頻出なのは新規カラムを追加するケースでしょうか。テーブルに新たなカラムを nullable で追加して、DDL の完了後に手動でUPDATEを実行して null を埋めるような対応です。その際に、対象となるレコードが 100万件を超えるような場合、どれぐらいクエリの実行時間が必要なのかを事前に把握して、ユーザー影響が少ないように実行タイミングの判断をする必要があります。

考えられる選択肢

何も気にせず実行する
アクセスの少ない時間帯（夜間）に実行する
バッチ分割して少しずつ実行する
メンテナンスモード中に実行する

コストを知ることはできる

クエリ実行のコストを分析するにはEXPLAINを使います。
しばしばSELECTのクエリに対してEXPLAINを実行してきましたが、実はUPDATEやDELETEであってもEXPLAINは有効であり、実際にはデータの更新、削除はされないため、安心して実行をすることができます。そのため、大量のレコードに対して更新クエリを実行したい場合、事前に副作用なく安心してコストを知ることが可能です。

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

EXPLAIN

しかし、実行計画で知れるのはコストであって、実際の実行時間の情報ではありません。 EXPLAIN ANALYZE UPDATE...とすれば、実際の実行時間を知ることができますが、実際にデータが更新されてしまうため採用できません。

仮説: 実行時間は対象レコード数に比例する

どうすれば更新クエリの実行時間を知ることができるでしょうか。
実際に実行してみる以外で、よく見かけるアプローチは、事前に任意件を更新対象としたクエリを実行・計測をして、件数を増やした場合の実行時間を予測するというものです。何となく良さそうに聞こえますが、この予測は「実行時間が対象レコード数に比例する」という前提の元で成り立っています。本当にそうなのでしょうか。

データベースにもよりますが、更新といっても実際にはWALへの書き込み、VACUUMの実行などの処理が実行されており、これらを無視して比例を前提とするのは、やや楽観的だと感じました。

計測する

ということで、実際に計測してみたいと思います。
更新対象となるデータ件数を 10倍ずつ増やしながら、実際の実行時間を信頼性を高めるため、EXPLAIN ANALYZEを用いて5回ずつ計測します。完全にクリーンなテーブルに対して実行するケースと、既存のテーブルに対して実行するケースでは結果に差異がありそうなので、合わせて検証してみます。

毎回テーブル再作成: 計測ごとにTRUNCATEを実行して計測する
VACUUM FULL: データはそのままでVACUUM FULLを実行後、計測する
クリーンアップなし: 何もせずデータを増やして計測する

シナリオ

RLS(RowLevelSecurity) を導入するため、全てのテーブルに組織IDを定義したい
ユーザーが投稿した記事を管理するテーブルに組織IDがないため、DDL で organization_id: nullable を追加した
手動でorganization_idを埋めるクエリの実行をする

UPDATE user_posts
SET organization_id = users.organization_id
FROM users
WHERE
  user_posts.user_id = users.id AND user_posts.organization_id IS NULL
;

詳細なスペック

マシン: MacBook Air (Apple M1, 8コア, 16GB RAM)
- Docker Desktop for Mac
- PostgreSQL 16 (公式Dockerイメージ)
- ストレージ: tmpfs (RAM上, 4GB)
  - ディスクI/Oノイズ排除のため
- 共有メモリ: 512MB (shm_size)
テーブル構成
- organizations: 10件 (固定)
- users: 1,000件 (固定, 1組織あたり約100人)
- user_posts: 100 / 1,000 / 10,000 / 100,000 / 1,000,000 件 (可変)
  - organization_id に関するインデックスはなし

結果と考察

図1より、実行時間とデータ件数には、ほぼ比例（原点を通っていないですが、0件で計測すれば0となる）の関係があることが分かります。実行ケースによる違いもほとんどなかったのは思わぬ結果でした。一方で、図2からはクリーンアップなしのケースでは実行時間に悪化があり、何らかの影響を受けていると考えられますが、データ件数が要因だと結論を出すことはできません。1件あたりの実行時間はデータ件数との明確な関係性はなさそうです。

あまり考察しがいのない結果となりました...
今回の計測結果からは「実行時間は対象レコード数にほぼ比例する」と判断できそうです。

注意点

今回の計測はtmpfs(RAM)で行っており、HDD / SSD では1件あたりの実行コストは大きくなります。件数に対する増加パターン（線形性）はリソース境界を超えない限り同様と考えられますが、同じような結果になる保証はありません。

あくまで理想状態での計測

今回は、データベースに対して他のアクセスがない、言ってみれば理想状態での計測を行いました。実際の本番環境では、複数プロセスからテーブルへの読み込み・書き込みやロックを扱っていたり...と複雑な状況があります。より信頼性のある計測をするには、同じような複雑な状態を再現したい気持ちはありますが、手元で簡単に再現できるものではありません。結局、稼働状況によっては比例の関係を覆す変数が容易に存在するし、その影響はデカいという事実からは逃げられません。

なので、参考値程度に比例を前提に予測をするのが、現実的な受け止め方でしょうか。

実行時間は予測できないもの

そもそも、計測できないものを予測しようという考えが良くないかもしれません。
実行時間は予測できないものとして制約を受け入れてアプローチを検討した方が良いのではないでしょうか。たとえば、1つのクエリとして実行するのではなく、データ整合性に注意しつつ、バッチ単位で小さく更新を行うといった方針は検討できます。
nullable のカラムを足すようなテーブル変更の段階で求められる整合性は、外部キー制約ぐらいな気がするので、前提を切り替えてこのアプローチを第一に考えた方が良さそうです。

ランキング参加中

プログラミング