PostgreSQL の update の性能を環境ごとに比較する

以下のような環境差によって、Update を 1 万回実行した時にどのような性能差が生まれるかを検証しました。
この検証結果を共有します。

- 明示的なトランザクション (BEGIN/COMMIT) の有無
- ORM 利用時と生 SQL 実行時の差
- AWS 環境とローカル環境での差

※ 以下の記事とほぼ同じ内容です

https://qiita.com/yy_at_here/items/0b8b3482f69adc500e90
https://qiita.com/yy_at_here/items/18da763ab9eb6e04194a

https://github.com/yy-at-here/psql_update/tree/master で、検証環境を用意しています。よければ

Transcript

1. PostgreSQL の update の性能 を環境ごとに比較する 市川 裕也
2. 自己紹介 ▪ 名前: 市川 裕也 ▪ 経歴 • 2024/11~ アプリケーションエンジニア @FutureVuls ▪ 現在主に取り組んでいること 現在は、主にアプリのパフォーマンス関連の問題 に取り組んでいます。 (SRE と SWE の中間的な) 前回のアンカンファレンスでは、 n_distinct の 話をしました。 前回の発表内容
3. 本日の発表内容 以下のような環境差によって、Update を 1 万回実行した時にどのような性能差が生まれ るかを検証しました。 この検証結果を共有します。 • 明示的なトランザクション (BEGIN/COMMIT) の有無 • ORM 利用時と生 SQL 実行時の差 • AWS 環境とローカル環境での差 ※ 以下の記事とほぼ同じ内容です PostgreSQL Update パフォーマンス検証 〜AWS 編〜 PostgreSQL: Update 時、トランザクション有無や ORM 利用でどれくらい性能差が出るかを検証する https://github.com/yy-at-here/psql_update/tree/master で、検証環境を再現可能にしています。よければ
4. 検証のモチベ 右のように、for 文の中で毎回トランザ クションを張るコードを AWS fargate 上で動かしていたが、 大量 Commit によって、実行時間が 長くなってしまっていた。 何がボトルネックなのか (通信時間な のか、アプリケーション処理なのか、 commit 自体の遅さなのか...) を検証 したくなったのがモチベ。
5. 内容 • 検証環境 • 比較① : 明示的なトランザクションの有無による差 (@ローカル) • 比較② : アプリケーションと生 SQL の差 (@ローカル) • 比較③ : AWS とローカル環境の差 • まとめ
6. 検証環境
7. 検証環境 (ローカル) • OS: MacOS Sequoia v15.5 • PostgreSQL: postgres:17 コンテナ@ローカル • Go バージョン: 24
8. 検証に使用したテーブル 以下のように、 benchmark_accounts というテーブルを作成し、検証に使用した。 1万レコードをテーブルに追加した状態で、各検証で Update を 1 万回実行している。
9. 比較① : 明示的なトランザクションの 有無による差 (@ローカル)
10. 比較したケース ① 明示的なトランザクションを張らない (=毎回オートコミット ) ケース BEGIN / COMMIT 無しのため、毎 SQL ごとにオートコミットが走ります。 ② トランザクションを張るケース
11. 比較結果 トランザクションありの方が、1秒弱実行時間が短くなった。 想像していたよりも差は小さかった。 ① トランザク ションなし ② トランザク ションあり 生 SQL 10.28 s 9.42 s
12. トランザクションあり /なし で時間差が生まれた理由 (1/2) pg_stat_wal: WAL (ログ先行書き込み) の活動状況に関する統計情報を表示する ビュー (詳細は ドキュメント) このビューのうち、以下の 2 つを取得することで、WAL の fsync にかかる時間と回数を 取得可能 • wal_sync_time : WAL ファイルのディスクに書き込むのに費やされた合計時間 • wal_sync : WAL ファイルがディスクに同期された回数 COMMIT 回数と fsync の時間の関係を確認するため、この統計情報についてトランザク ションあり/なしの違いを調べた。
13. トランザクションあり /なし で時間差が生まれた理由 (2/2) WAL の fsync 時間・回数を計測した 結果は右の表のとおり。 トランザクション無しの場合とありの 場合で、1.9 秒程度 WAL 同期時間に 差が生まれた。 よって、WAL 同期のオーバーヘッド が時間差の主要因と結論づけられ る。 (※ 実行時間の差分と WAL 同期時 間の差分が一致しない原因は不明 ) ケース 実行時間 WAL 同期 時間 (wal_sync _time) WAL 同期 回数 (wal_sync ) ① トランザクショ ンなし 10.28 s 1.99 s 10,000 回 ② トランザクショ ンあり 9.42 s 0.05 s 47 回 差:1.9s
14. 比較② : アプリケーション と生 SQL の差 (@ ローカル)
15. 比較したケース 以下の 3 つのケースを比較した。 ① 直接 SQL を Exec するケース ② ORM で生成したコードを利用する ケース ③ 一度 Prepare するケース Go で SQL を実行するツールとして、 bob を使用した。 ① ② ③ 直接Exec ORM のモデル を使用 Prepare
16. 比較結果 • Go で生 SQL を Execute した場合、生 SQL を直接実行する場合に比べ約 2 倍の時 間がかかった。 • 一方、事前に Prepare した上で、プリペアードステートメントを Execute した場合、そ の約半分の時間で終了した。 実行時間 [参考] 生 SQL 9.42 s ① Go (生 SQL Execute) 17.2 s ② Go (生成された ORM コード) 18.1 s ③ Go (Prepare → Execute) 8.5s 約半分 2倍弱
17. 考察: Prepare した場合に時間が半減した理由 右のコードは、 ExecContext の中身。 ExceContext 時は以下の 2 つの処理が走る。 1. Prepare 2. 作成された stmt をもちいて Exec つまり、 ExceContext 時は2回 DB と通信する 必要がある。 Prepare して事前に作った statement を使い回す 場合は、DB との通信が 1 回で済む。 そのため、 通信回数が半減し、結果として実行時 間も半減したと考えられる。 ① Prepare ② stmt を Exec ① (再掲)
18. 比較③ : AWS 環境とローカルの差
19. 検証環境（ AWS） Aurora PostgreSQL + EC2（同一 AZ 内） で検証を行った。 • Aurora PostgreSQL: 17.6 （db.r6g.large） • ベンチマーク実行：EC2 (t3.medium) • EC2 と Aurora を同一 AZ （ap-northeast-1a）に配置 この環境とローカル環境で、実行時間を比 較した。
20. 比較したケース (再掲) ① 明示的なトランザクションを張らない (=毎回オートコミット ) ケース BEGIN / COMMIT 無しのため、毎 SQL ごとにオートコミットが走ります。 ② トランザクションを張るケース
21. 比較結果 ローカルと Aurora で、トランザクションあり/なしでの性能差が 全く異なる 結果に。 • ローカル：約 1 秒 の差 • Aurora：約 23 秒 の差（トランザクション活用で 25秒 → 3秒 に改善） 環境 トランザク ションなし トランザク ションあり 差分 ローカル 10.79 s 9.86 s 0.93 s Aurora (同 一 AZ) 25.45 s 2.84 s 22.61 s
22. Aurora におけるコミット時間の計測 Aurora には、 aurora_stat_get_db_commit_latency() という、コミット時間を計測できる関数が用 意されている。 こちらを用いて、コミット時間を計測した。 ▪ 結果 トランザクションなしの方は、コミットに合計 21.5 s かかっていた。 ローカルではせいぜい 2 s 程度だったため、 Aurora ではコミットのコストが大きい ことがわかる。 Aurora で は、コミットの乱発を避ける ことが特に重要。 環境 トランザクションなし トランザクションあり 実行時間 25.45 s 2.84 s コミット時間 21.5 s 0.02 s 実行時間 - コミット時間 3.95 s 2.82 s
23. 考察: なぜ Aurora のコミットは重いか (実際に検証した訳ではないので、推測の域ですが ...) Aurora では、複数 AZ の合計 6 個のストレージにデータを複製し ている。 デフォルト設定だと、COMMIT 時に WAL が fsync される際、 4/6 のストレージからの ACK を待ってから返却されるようになってい る。 (fsync の ack を待つかは、 synchronous_commit というパラ メータで制御されている) この、「分散ストレージへの同期処理」の部分のコスト が、コミット のレイテンシに影響を与えているのではないかと推測した。
24. まとめ
25. 検証から得られた学び ① 明示的なトランザクションの有無による処理時間差 • ローカルでは差は小さい（約 1 秒） ◦ 差分の主な要因は、主に WAL の fsync にかかる時間によって生じる • クラウド (Aurora) では差が非常に大きくなった (約 20 秒) ◦ 「分散ストレージへの同期処理」の部分のコストに起因すると予想される ② アプリケーション vs 生 SQL • Go の ExecContext は内部で毎回 Prepare するため、DB 通信量、通信時間が 2 倍に • 事前に Prepare してステートメントを使い回すことで、生 SQL 実行と同等の速度にな る
26. ご清聴いただき ありがとうございました