PostgreSQL11解体新書 / PostgreSQL11-Release

PostgreSQLカンファレンス 2018のチュートリアル枠の登壇資料です。
https://www.postgresql.jp/jpug-pgcon2018

みんな篠田の虎の巻を読みましょう。

Transcript

1. PostgreSQL 11 解体新書 PostgreSQL カンファレンス 2018
2. What is it? 2018/10/18 PostgreSQL 11 Release!
3. What is it? [PostgreSQL 11 新機能] [検索]
4. What is it? 『篠田の虎の巻』第10弾 [篠田の虎の巻 PostgreSQL] [検索]←
5. [今秋リリース予定のPostgreSQL11を徹底解説] [検索] https://www.slideshare.net/masahikosawada98/postgresql11
6. What is it? から一歩踏み込んだ話
7. あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ
8. あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ
9. 自己紹介 名前 : 曽根 壮大(そね たけとも) 年齢 : 33歳(3人の子供がいます) 職業 : 副社長/CTO 所属 : 株式会社 オミカレ 日本PostgreSQLユーザ会(JPUG) 勉強会担当 技術的にはLL系言語やRDBが好きです
10. 自己紹介 名前 : 曽根 壮大(そね たけとも) 年齢 : 33歳(3人の子供がいます) 職業 : 副社長/CTO 所属 : 株式会社 オミカレ 日本PostgreSQLユーザ会(JPUG) 勉強会担当 技術的にはLL系言語やRDBが好きです
11. スポンサーもしてます
12. 婚活といえばオミカレ https://party-calendar.net/
13. あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ
14. 目玉の新機能 三大目玉新機能 （そーだい調べ）
15. 目玉の新機能 1. パーティションの強化 2. パラレルクエリの強化 3. SQLの拡張
16. 目玉の新機能 その他の目玉
17. その他の新機能 1. JITコンパイルの導入 2. ストアドプロシージャの導入 3. レプリケーションの強化
18. 目玉の新機能 1. パーティションの強化 2. パラレルクエリの強化 3. SQLの拡張
19. パーティーションの強化 パーティーションは10の新機能
20. パーティーションの強化 パーティーションは10の新機能 ↓ 11で圧倒的強化
21. 子テーブル レコード4 レコード1 子テーブル レコード2 子テーブル レコード3 親テーブル レコード1 レコード2 レコード3 レコード4 … レコード5 レコード6 レコード7 レコード8 レコード9 … … … パーティーションとは？ 親テーブルにレコード の実体は無い 指定したルールに基づいて 子テーブルに分散される
22. パーティーションとは？ 4月テーブル レコード4 レコード1 5月テーブル レコード2 6月テーブル レコード3 親テーブル レコード1 レコード2 レコード3 月単位でデータを分散出来る … レコード5 レコード6 レコード7 レコード8 2018/6/18のデータ … … … 2018/6/18のデータ クライアント 2018/6/18のデータの挿入 3月テーブル クライアント 不要になった3月は他に影響 を与えずDROP出来る 4月の集計が他のテーブルを参照 しないので小さなデータを直接 集計できる
23. 3種類のパーティーション 親テーブル 子テーブル 親テーブル 子テーブル 親テーブル 子テーブル レンジ パーティーション リスト パーティーション ハッシュ パーティーション 1月,2月,3月 ～ など の範囲で分ける 東京,大阪,広島 ～ など の 項目で分ける Keyのハッシュ値に基づ いて分ける 新機能
24. リストパーティーション -- 親テーブル作成 demo=# CREATE TABLE public."販売履歴" ( "商品名" character varying(64) NOT NULL, "価格" numeric NOT NULL DEFAULT 0, "売上日時" timestamp NOT NULL DEFAULT now(), "売上月" character varying(6) NOT NULL ) PARTITION BY LIST ("売上月"); -- 文字列指定の場合のパーティション demo=# CREATE TABLE "2018年10月" PARTITION OF "販売履歴" FOR VALUES IN ('201810'); demo=# CREATE TABLE "2018年9月" PARTITION OF "販売履歴" FOR VALUES IN ('20189'); demo=# CREATE TABLE "2018年8月" PARTITION OF "販売履歴" FOR VALUES IN ('20188'); demo=# CREATE TABLE "2018年7月" PARTITION OF "販売履歴" FOR VALUES IN ('20187'); demo=# CREATE TABLE "2018年6月" PARTITION OF "販売履歴" FOR VALUES IN ('20186');
25. リストパーティーション -- データ投入 demo=# INSERT INTO "販売履歴" ( "売上日時“ , "商品名“ , "価格“ , "売上月“ ) VALUES ( '2018-10-21 21:12:00' , 'WEB+DB PRESS Vol.107’ , 1480 , '201810' ); -- データ確認 demo=# SELECT "商品名“ , "売上月“ FROM "販売履歴"; -- 出力結果 demo=# SELECT "商品名","売上月" FROM "販売履歴"; 商品名 | 売上月 ----------------------+-------- WEB+DB PRESS Vol.107 | 201810 (1 row) demo=# SELECT "商品名","売上月" FROM "2018年10月"; 商品名 | 売上月 ----------------------+-------- WEB+DB PRESS Vol.107 | 201810 (1 row) demo=# SELECT "商品名","売上月" FROM "2018年9月"; 商品名 | 売上月 --------+-------- (0 rows)
26. レンジパーティーション demo=# CREATE TABLE public."販売履歴2" ( "商品名" character varying(64) NOT NULL, "価格" numeric NOT NULL DEFAULT 0, "売上日時" timestamp NOT NULL DEFAULT now(), ) PARTITION BY RANGE ("売上日時"); demo=# CREATE TABLE "2018年10月RANGE" PARTITION OF "販売履歴2" FOR VALUES FROM ('2018-10-01 00:00:00') TO ('2018-11-1 00:00:00 ');
27. レンジパーティーション demo=# INSERT INTO "販売履歴2" ("売上日時", "商品名", "価格") VALUES ( '2018-10-21 21:12:00','WEB+DB PRESS Vol.107',1480 ); -- 確認 demo=# SELECT * FROM "2018年10月RANGE"; 商品名 | 価格 | 売上日時 ----------------------+------+--------------------- WEB+DB PRESS Vol.107 | 1480 | 2018-10-21 21:12:00 (1 row)
28. 子テーブル 子テーブル 子テーブル 親テーブル ID 1 ID 2 ID 3 ID 4 余り=0 指定したkey(int)÷テーブルの余りで分散 する … 余り=1 余り=2 ID 3 ID 1 ID 2 ID 4 ID 5 ID 6 … … … ハッシュパーティーション シーケンシャルなidの場合 INSERTが分散する
29. パーティーションの強化 • パーティション化されたテーブルにデフォルト のパーティションを指定する • パーティションキーが一意性を保証する場合は、 パーティションテーブルの一意制約を許可する • パーティション化されたテーブルのINDEXを任 意の子パーティションに自動的作成する 性能強化 その1
30. パーティーションの強化 • クエリ処理中にパーティションを素早く削除する • クエリ実行中にパーティションの削除を許可する • パーテーションのKeyが更新されるような行の更新 の際に、更新内容に基づいて自動的に対象のパー ティションに移動させる 性能強化 その2
31. パーティーションの強化 • パーティションテーブルからの外部キーを許可する • パーティション化されたテーブルに対してINSERT、 UPDATE、COPYを許可して、行を子パーティショ ンに適切にルーティングする • パーティション化されたテーブルに対してFOR EACH ROWトリガーを許可する 性能強化 その3
32. パーティーションの強化 • 同一のパーティション化された子テーブルを持つ パーティション化されたテーブル間のINNER JOIN を許可し、子テーブルを直接結合する • 各パーティションで集約クエリを実行し、結果を マージする • postgres_fdwがパーティションである外部表に集 約をプッシュダウンできるようにする 性能強化 その4
33. パーティーションの強化 パーティーションは実用レベル
34. 目玉の新機能 1. パーティションの強化 2. パラレルクエリの強化 3. SQLの拡張
35. 巨大なテーブル レコード レコード レコード レコード … レコード レコード レコード レコード レコード レコード レコード レコード CPU CPU CPU パラレル ワーカ パラレル ワーカ パラレル ワーカ スキャン SELECT * FROM tb_name; 実行 バックエンド プロセス パラレルクエリの強化 スキャン スキャン 分散してスキャンし、 集計結果を結合して返す
36. 巨大なテーブル レコード レコード レコード レコード … レコード レコード レコード レコード レコード レコード レコード レコード 部分集計 パラレル ワーカ スキャン SELECT count(*) FROM tb_name; 実行 パラレルクエリの強化 部分集計 パラレル ワーカ 部分集計 パラレル ワーカ 結果 スキャン 結果 スキャン 結果
37. パラレルクエリの強化 9.6からバージョンアップする度に 圧 倒 的 進 化
38. • Parallel Index Scan(b-treeのみ) • Parallel Index Only Scan(b-treeのみ) • サブクエリ • Merge Join • Parallel bitmap heap scan 10で追加された対象 パラレルクエリの強化
39. • Parallel Hash Join(9.6からより強化) • CREATE TABLE AS SELECT • CREATE MATERIALIZED VIEW • UNION ALLによるAPPEND • SELECT INTO • CREATE INDEX 11で追加された対象 パラレルクエリの強化
40. パラレルクエリの強化 多くの読み込みは並列化できる
41. パラレルクエリの強化 多くの読み込みは並列化できる ↓ 書き込みはまだ未対応
42. パラレルクエリの強化 基本的には Version upするだけで速くなる！
43. 目玉の新機能 1. パーティションの強化 2. パラレルクエリの強化 3. SQLの拡張
44. SQLの拡張 INDEX ONLY SCANの強化
45. SQLの拡張 INDEX ONLY SCANの強化 ↓ カバリングインデックス
46. SQLの拡張 INCLUDE句の追加 ↓ INDEXに参照用の列を指定
47. SQLの拡張 INCLUDE句の追加 ↓ INDEXに参照用の列を指定 SELECT indexed_col FROM table_name WHERE id in (1, 2, 3); このような時にINCLUDE句でindexed_colを INDEXに含めることが出来る
48. SQLの拡張 Window関数で 除外指定できるようになった
49. SQLの拡張 • EXCLUDE CURRENT ROW 自分の行を除外する • EXCLUDE GROUP 自分と同じ値の行を除外する • EXCLUDE TIES 重複した値の行を除外する • EXCLUDE NO OTHERS ウインドウフレーム以外の値を省略する EXCLUDE句
50. Window関数の強化 demo=# WITH t(id, value) AS (VALUES (1, 1), (2, 1), (3, 3), (4, 5), (5, 5), (6, 5), (7, 6)) SELECT id, value, array_agg(id) OVER ROWS as row_id, array_agg(value) OVER ROWS as row_value, array_agg(id) OVER RANGE as renge_id, array_agg(value) OVER RANGE as renge_value, array_agg(id) OVER GROUPS as groups_id, array_agg(value) OVER GROUPS as groups_value FROM t WINDOW obj AS (ORDER BY value), ROWS AS (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), RANGE AS (obj RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING), GROUPS AS (obj GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING); id | value | row_id | row_value | renge_id | renge_value | groups_id | groups_value ----+-------+---------+-----------+-----------+-------------+---------------+--------------- 1 | 1 | {1,2} | {1,1} | {1,2} | {1,1} | {1,2,3} | {1,1,3} 2 | 1 | {1,2,3} | {1,1,3} | {1,2} | {1,1} | {1,2,3} | {1,1,3} 3 | 3 | {2,3,4} | {1,3,5} | {3} | {3} | {1,2,3,4,5,6} | {1,1,3,5,5,5} 4 | 5 | {3,4,5} | {3,5,5} | {4,5,6,7} | {5,5,5,6} | {3,4,5,6,7} | {3,5,5,5,6} 5 | 5 | {4,5,6} | {5,5,5} | {4,5,6,7} | {5,5,5,6} | {3,4,5,6,7} | {3,5,5,5,6} 6 | 5 | {5,6,7} | {5,5,6} | {4,5,6,7} | {5,5,5,6} | {3,4,5,6,7} | {3,5,5,5,6} 7 | 6 | {6,7} | {5,6} | {4,5,6,7} | {5,5,5,6} | {4,5,6,7} | {5,5,5,6} (7 rows)
51. Window関数の強化 demo=# WITH t(value) AS (VALUES (1), (1), (3), (5), (5), (5), (6)) SELECT value, array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row, array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS group, array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties, array_agg(value) OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_others FROM t WINDOW obj AS (ORDER BY value); value | current_row | group | ties | no_others -------+-------------+-------+---------+----------- 1 | {1} | | {1} | {1,1} 1 | {1,3} | {3} | {1,3} | {1,1,3} 3 | {1,5} | {1,5} | {1,3,5} | {1,3,5} 5 | {3,5} | {3} | {3,5} | {3,5,5} 5 | {5,5} | | {5} | {5,5,5} 5 | {5,6} | {6} | {5,6} | {5,5,6} 6 | {5} | {5} | {5,6} | {5,6} (7 rows)
52. 目玉の新機能 強力な新機能と言うより PostgreSQL 10からの強化
53. 目玉の新機能 使い方を考えるより 同じ使い方で速くなる機能が多い
54. あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ
55. 嬉しいユースケース パーテーションの強化
56. 嬉しいユースケース パーテーションの強化 ↓ パーテーション同士の結合
57. パーテーション同士の結合 パーティション1 （id: 100000 ～ 199999） パーティション2 (id: 200000 ～ 299999） パーティション3 (id: 300000 ～ 399999） パーティション1 （id: 100000 ～ 199999） パーティション2 (id: 200000 ～ 299999） パーティション3 (id: 300000 ～ 399999） blog article
58. 嬉しいユースケース スキャンが多い
59. 嬉しいユースケース スキャンが多い ↓ パラレルクエリが効く
60. 嬉しいユースケース INDEXでも使うが、一番効くのは テーブルスキャン
61. パラレルクエリのユースケース 1. 集計系の分析クエリ 2. Hash Joinを使うような大きな TABLEのJOIN 3. CREATE TABLE AS SELECT文
62. 嬉しいユースケース 特に嬉しいのは！？
63. 嬉しいユースケース CREATE MATERIALIZED VIEW のリフレッシュでパラレルクエリ
64. 嬉しいユースケース B+Tree CREATE INDEX 作成がパラレルクエリ
65. 嬉しいユースケース そのほかにも
66. 嬉しいユースケース ALTER の際に DEFAULT NULLではないカラム の追加が高速に！
67. 嬉しいユースケース 特にパーティーションは 設計の幅が広がるので検討すべし
68. あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ
69. PostgreSQL 11の罠 パラレルクエリは万能ではない
70. PostgreSQL 11の罠 パラレルクエリは万能ではない ↓ Window関数、CTEでは使えない
71. PostgreSQL 11の罠 パーティーションに対して 外部キー制約は貼れない
72. PostgreSQL 11の罠 JIT compileは まず導入されただけ 高速化されるユースケースがかなり限定的
73. PostgreSQL 11の罠 ストアドプロシージャに 過度の期待をしない OracleDBのパッケージのような仕組みは無いので、 クラス継承のようなことは出来ない
74. PostgreSQL 11の罠 非互換な変更もある
75. PostgreSQL 11の罠 CREATE Functionで WITH句が禁止に
76. PostgreSQL 11の罠 RDSなどDBaaSに来るには まだ時間がかかりそう
77. PostgreSQL 11の罠 とはいえ、大きな罠は少なく 安心して使えるバージョン
78. あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ
79. まとめ PostgreSQLは魅力的な機能が豊富！
82. まとめ イチローのバットを使ったからといって 我々がイチローになれるわけでは無い
83. まとめ この二冊の内容を理解した上で 篠田の虎の巻を読む
84. まとめ 篠田の虎の巻を読む ↓ 過去のversionもあるので合わせて読む
85. まとめ 新規案件なら11を選ぶ価値がある
86. まとめ 既存システムでも新機能が活用できるなら 11に上げる価値がある
87. まとめ 最新版に追従していきましょう
88. ご清聴ありがとうございました