PostgreSQLにおけるパーティショニング性能向上 - PostgreSQL Conference Japan 2024

Slide 1

Slide 1 text

PostgreSQLにおけるパーティショニング性能向上 2024年12月6日 PostgreSQL Conference Japan 2024 NTT OSSセンタ渡佑也

Slide 2

Slide 2 text

Slide 3

Slide 3 text

3 © 2024 NTT CORPORATION 概要キーワード  パーティショニング • テーブルを物理的に小さなパーティションに分割する機能トピック  パーティショニングされているときのプランニング性能を向上させる取り組み • 現在のPostgreSQLにはプランニング性能に課題がある  最大で22.5倍の高速化を実現  性能改善をPostgreSQLコミュニティに提案して議論中パーティションテーブルパーティションパーティションパーティション

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

6 © 2024 NTT CORPORATION テーブルパーティショニングテーブルを物理的に小さなパーティションに分割する機能  大量のデータを扱わなければならない今日において高いスケーラビリティと運用性を実現するうえで鍵となる  例：ログデータの管理継続的に発生するログデータを日ごとにパーティションに分割して保持論理的には一つの大きなテーブルとして扱うことができるパーティションテーブルパーティション 2024年1月1日パーティション 2024年1月2日パーティション 2024年12月31日

Slide 7

Slide 7 text

7 © 2024 NTT CORPORATION パーティショニングの歴史 PostgreSQL 10において宣言的パーティショニングが導入された  構文レベルでサポートされ簡単に利用できるように • それまではテーブルの継承とCHECK制約により実現していた • 使いやすさが格段に向上したほか、性能も向上  例：パーティションテーブルの定義 CREATE TABLE t1(id INT, name TEXT) PARTITION BY RANGE (id); CREATE TABLE t1_1 PARTITION OF t1 FOR VALUES FROM (0) TO (100); CREATE TABLE t1_2 PARTITION OF t1 FOR VALUES FROM (100) TO (200); 親テーブルの定義子パーティションの定義 t1 t1_1 0 <= id < 100 t1_2 100 <= id < 200

Slide 8

Slide 8 text

8 © 2024 NTT CORPORATION パーティショニングの高速化パーティション・プルーニング（PostgreSQL 10から）  クエリの結果に関係のないパーティションをプランから除外することで不要なアクセスを削減して実行速度を向上  例  PostgreSQL 11からは、実行時にしか分からない情報も活用してさらに高度なパーティション・プルーニングをするようになった SELECT * FROM t1 WHERE id < 100 t1 t1_1 0 <= id < 100 t1_2 100 <= id < 200 パーティションt1_2は結果に影響しないためスキャンしなくてよい  プランから除外

Slide 9

Slide 9 text

9 © 2024 NTT CORPORATION パーティショニングの高速化（続き）パーティション・ワイズ結合／集約（PostgreSQL 11から）  PostgreSQL 10でパーティションテーブルを結合・集約しようとすると一度パーティションの内容をすべてまとめてから（Append）演算していた t1_1 0 <= id < 100 t1_2 100 <= id < 200 t2_1 0 <= id < 100 t2_2 100 <= id < 200 Append Append 結合演算 SELECT * FROM t1, t2 WHERE t1.id = t2.id

Slide 10

Slide 10 text

10 © 2024 NTT CORPORATION パーティショニングの高速化（続き）パーティション・ワイズ結合／集約（PostgreSQL 11から）  パーティション・ワイズ結合／集約では、パーティションが同じキーを持つ場合、対応するパーティション同士で演算を行った後にAppend • 先ほどのクエリは次の図のように実行できるようになり効率化 t1_1 0 <= id < 100 t2_1 0 <= id < 100 t1_2 100 <= id < 200 t2_2 100 <= id < 200 結合演算結合演算 Append

Slide 11

Slide 11 text

Slide 12

Slide 12 text

12 © 2024 NTT CORPORATION プランニング性能の課題場合によってはプランニングに長時間かかるという課題が存在する  クエリはPostgreSQL内部で実行計画に変換されてから実行される • これをプランニングと呼び、プランニングを行うモジュールをプランナという  プランニングは一瞬で終わると思われがちだが、時には無視できないほど長くかかることもあるクエリプランナエグゼキュータ結果パーティション・プルーニングやパーティション・ワイズ結合／集約はエグゼキュータでの実行を高速化する技術プランニングそのもののパフォーマンスにも課題が存在！

Slide 13

Slide 13 text

13 © 2024 NTT CORPORATION プランニングはどんなとき時間がかかるかパーティション数が多いときプランニング時間は増加する  冒頭のログデータを管理する例では、3年のデータに対して1000以上のパーティションを持つ必要がある  パーティション数が多いとプランニング時間が急激に増加する可能性があるパーティションテーブルパーティション 2022年1月1日パーティション 2022年1月2日パーティション 2022年1月3日パーティション 2024年12月31日 1000個以上の大量の子パーティション

Slide 14

Slide 14 text

14 © 2024 NTT CORPORATION プランニング性能改善の取り組みこれまでのバージョンアップでもプランニング性能の改善はなされてきた  パーティション・プルーニングはPostgreSQL 10で導入されたときパーティション数が多いとプランニングに時間がかかる問題を抱えていた  PostgreSQL 11でこの問題が改善次のプランニング性能改善は？  PostgreSQL 18向けに提案している最新の改善を紹介

Slide 15

Slide 15 text

15 © 2024 NTT CORPORATION 簡単なクエリで検証 3つのテーブルを結合するシンプルな例でプランニング時間を計測  クエリは次の通りシンプル  テーブルt1、t2、t3はパーティショニングされている  パーティション・ワイズ結合は無効化して検証各テーブル（t1、t2、t3）が多数の子パーティションに分割されているときのプランニング時間（≒EXPLAINコマンドの実行時間）はどうなる？ SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.id = t3.id

Slide 16

Slide 16 text

16 © 2024 NTT CORPORATION 簡単なクエリで検証 — 結果各テーブルのパーティションの数が多いとプランニング時間は大幅に増加  1024パーティション存在するときプランニング時間は0.3秒 • 環境によってはこれより長くかかることも • 十分体感できる程度の遅さとなる  さらに多くのパーティションが存在する場合、プランニング時間は急激に増加すると見込まれる 0 0.05 0.1 0.15 0.2 0.25 0.3 0.35 0 200 400 600 800 1000 プランニング時間（秒）各テーブルのパーティション数 PostgreSQL 17 better

Slide 17

Slide 17 text

Slide 18

Slide 18 text

18 © 2024 NTT CORPORATION なぜこのような問題が生じるのかパーティション数が多いとき結合演算の等価性を管理する機構での処理がボトルネックとなっている  先ほどのクエリには3つの結合順序が存在する • ここでは内部表と外部表の入れ替えは無視する  最後の結合条件「t2.id = t3.id」はクエリに出現しないため自明ではないが結合演算の等価性を管理する機構により導出されている SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.id = t3.id t1 t2 t3 ⋈ ⋈ t1 t3 t2 ⋈ ⋈ t2 t3 t1 ⋈ ⋈ t1.id = t2.id t1.id = t3.id t1.id = t2.id t1.id = t2.id t1.id = t3.id t2.id = t3.id

Slide 19

Slide 19 text

19 © 2024 NTT CORPORATION 原因を特定して改善を提案結合演算の等価性を管理する機構が持つデータ構造内の要素数が増えて探索に時間がかかることが原因この問題を解決する改善をコミュニティに提案 PostgreSQL 18でのコミットを目指して議論中  問題に気付いたきっかけ • パーティション数が多い状況下でのパーティション・プルーニングなどの性能を調べていたが、異常にプランニングに時間がかかることに気づく • 問題の切り分けを行っていったところ、パーティション・プルーニングなどに関係なく、多数のパーティションを持つテーブル同士の結合演算であれば広く一般に起こる問題であることを確認 • PostgreSQLのソースコードレベルでの調査により、結合演算の等価性を管理する機構に直接的な問題個所を発見

Slide 20

Slide 20 text

Slide 21

Slide 21 text

21 © 2024 NTT CORPORATION 性能検証プランニング時間改善の効果を確認する性能検証を実施 1. 3つのテーブルを結合するときのプランニング時間 • パーティション・ワイズ結合なし • 冒頭に示したグラフと同じ実験条件 2. 3つのテーブルを結合するときのプランニング時間 • パーティション・ワイズ結合あり • それ以外は1. と同じ 3. 結合するテーブル数を2個～6個に変化させたときのプランニング時間 4. Join Order Benchmarkにおけるプランニング時間

Slide 22

Slide 22 text

Slide 23

Slide 23 text

23 © 2024 NTT CORPORATION 検証1 — 結果プランニング時間が劇的に改善  1024パーティション存在するとき 6.9倍の高速化 • 0.3秒かかっていたものが 0.05秒以下に短縮  増加の仕方も二次関数的なものから直線的なものになっている 0 0.05 0.1 0.15 0.2 0.25 0.3 0.35 0 200 400 600 800 1000 プランニング時間（秒）各テーブルのパーティション数 PostgreSQL 17 改善後 6.9倍の高速化 better

Slide 24

Slide 24 text

24 © 2024 NTT CORPORATION 0 0.1 0.2 0.3 0.4 0.5 0.6 PostgreSQL 17 改善後 PostgreSQL 17 改善後パーティション・ワイズ結合なしパーティション・ワイズ結合ありプランニング時間（秒）各テーブルのパーティション数が1024のとき検証2 同様の検証をパーティション・ワイズ結合ありでも実施  各テーブルのパーティション数は 1024に固定高い性能改善を確認 6.9倍の高速化 2.6倍の高速化 better

Slide 25

Slide 25 text

Slide 26

Slide 26 text

26 © 2024 NTT CORPORATION 多くのテーブルを結合するほど高い効果を示した  6つのテーブルを結合する場合 5.5秒が0.2秒まで短縮 • 22.5倍の大幅な高速化 2 3 4 5 6 PostgreSQL 17 0.06 0.30 1.12 2.70 5.50 改善後 0.02 0.04 0.08 0.14 0.24 0 1 2 3 4 5 6 プランニング時間（秒）結合するテーブル数各テーブルのパーティション数が1024のとき PostgreSQL 17 改善後検証3 — 結果 2.9倍 6.9倍 14.1倍 19.0倍 22.5倍 better

Slide 27

Slide 27 text

27 © 2024 NTT CORPORATION 検証4 Join Order Benchmarkにおいても高い性能向上を実現  Join Order Benchmark • OLAPのワークロードを評価する有名なベンチマーク  1024パーティション存在する状況でプランニング時間の高速化率を計測 0 5 10 15 20 25 高速化率クエリ 1 平均で14.2倍の高速化最大で22.3倍の高速化 better

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

30 © 2024 NTT CORPORATION 今年の5月にカナダ・バンクーバーで開催されたPostgreSQL開発者向けの国際会議「PGConf.dev 2024」にて本取り組みを紹介  本改善の詳細な実装デザインや検証結果を紹介し有用性をアピール  コミッターやパッチのレビュアーの方とも議論 PGConf.dev 2024で講演講演の様子主催者公開の動画より（https://youtu.be/TapL9PRvNLM）

Slide 31

Slide 31 text

Slide 32

Slide 32 text

32 © 2024 NTT CORPORATION まとめ  テーブルパーティショニングは大量のデータを扱わなければならない今日において非常に重要な機能  PostgreSQLには多数のパーティションが存在するテーブルを結合しようとしたときプランニングに非常に長い時間がかかるという問題が存在  結合演算の等価性を管理する機構が原因  この問題を改善してPostgreSQLコミュニティに提案中  改善によって最大で22.5倍の高速化が実現

Slide 33

Slide 33 text

33 © 2024 NTT CORPORATION 参考文献本改善の議論やソースコードは次の場所から参照可能  https://commitfest.postgresql.org/50/3701/  https://github.com/postgresql-cfbot/postgresql/tree/cf/3701  https://www.postgresql.org/message-id/flat/CAJ2pMkZNCgoUKSE+_5LthD+Kb XKvq6h2hQN8Esxpxd+cxmgomg@mail.gmail.com Join Order Benchmark  Leis, Viktor, et al. "How good are query optimizers, really?." Proceedings of the VLDB Endowment 9.3 (2015): 204-215.  https://github.com/winkyao/join-order-benchmark