PostgreSQL 11 解体新書 PostgreSQL カンファレンス 2018

What is it? 2018/10/18 PostgreSQL 11 Release!

What is it? [PostgreSQL 11 新機能] [検索]

What is it? 『篠田の虎の巻』第10弾 [篠田の虎の巻 PostgreSQL] [検索]←

[今秋リリース予定のPostgreSQL11を徹底解説] [検索] https://www.slideshare.net/masahikosawada98/postgresql11

What is it? から一歩踏み込んだ話

あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ

あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ

自己紹介 名前 : 曽根 壮大(そね たけとも) 年齢 : 33歳(3人の子供がいます) 職業 : 副社長/CTO 所属 : 株式会社 オミカレ 日本PostgreSQLユーザ会(JPUG) 勉強会担当 技術的にはLL系言語やRDBが好きです

自己紹介 名前 : 曽根 壮大(そね たけとも) 年齢 : 33歳(3人の子供がいます) 職業 : 副社長/CTO 所属 : 株式会社 オミカレ 日本PostgreSQLユーザ会(JPUG) 勉強会担当 技術的にはLL系言語やRDBが好きです

スポンサーもしてます

婚活といえばオミカレ https://party-calendar.net/

あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ

目玉の新機能 三大目玉新機能 （そーだい調べ）

目玉の新機能 1. パーティションの強化 2. パラレルクエリの強化 3. SQLの拡張

目玉の新機能 その他の目玉

その他の新機能 1. JITコンパイルの導入 2. ストアドプロシージャの導入 3. レプリケーションの強化

目玉の新機能 1. パーティションの強化 2. パラレルクエリの強化 3. SQLの拡張

パーティーションの強化 パーティーションは10の新機能

パーティーションの強化 パーティーションは10の新機能 ↓ 11で圧倒的強化

子テーブル レコード4 レコード1 子テーブル レコード2 子テーブル レコード3 親テーブル レコード1 レコード2 レコード3 レコード4 … レコード5 レコード6 レコード7 レコード8 レコード9 … … … パーティーションとは？ 親テーブルにレコード の実体は無い 指定したルールに基づいて 子テーブルに分散される

パーティーションとは？ 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月の集計が他のテーブルを参照 しないので小さなデータを直接 集計できる

3種類のパーティーション 親テーブル 子テーブル 親テーブル 子テーブル 親テーブル 子テーブル レンジ パーティーション リスト パーティーション ハッシュ パーティーション 1月,2月,3月 ～ など の範囲で分ける 東京,大阪,広島 ～ など の 項目で分ける Keyのハッシュ値に基づ いて分ける 新機能

リストパーティーション -- 親テーブル作成 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');

リストパーティーション -- データ投入 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)

レンジパーティーション 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 ');

レンジパーティーション 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)

子テーブル 子テーブル 子テーブル 親テーブル 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が分散する

パーティーションの強化 • パーティション化されたテーブルにデフォルト のパーティションを指定する • パーティションキーが一意性を保証する場合は、 パーティションテーブルの一意制約を許可する • パーティション化されたテーブルのINDEXを任 意の子パーティションに自動的作成する 性能強化 その1

パーティーションの強化 • クエリ処理中にパーティションを素早く削除する • クエリ実行中にパーティションの削除を許可する • パーテーションのKeyが更新されるような行の更新 の際に、更新内容に基づいて自動的に対象のパー ティションに移動させる 性能強化 その2

パーティーションの強化 • パーティションテーブルからの外部キーを許可する • パーティション化されたテーブルに対してINSERT、 UPDATE、COPYを許可して、行を子パーティショ ンに適切にルーティングする • パーティション化されたテーブルに対してFOR EACH ROWトリガーを許可する 性能強化 その3

パーティーションの強化 • 同一のパーティション化された子テーブルを持つ パーティション化されたテーブル間のINNER JOIN を許可し、子テーブルを直接結合する • 各パーティションで集約クエリを実行し、結果を マージする • postgres_fdwがパーティションである外部表に集 約をプッシュダウンできるようにする 性能強化 その4

パーティーションの強化 パーティーションは実用レベル

目玉の新機能 1. パーティションの強化 2. パラレルクエリの強化 3. SQLの拡張

巨大なテーブル レコード レコード レコード レコード … レコード レコード レコード レコード レコード レコード レコード レコード CPU CPU CPU パラレル ワーカ パラレル ワーカ パラレル ワーカ スキャン SELECT * FROM tb_name; 実行 バックエンド プロセス パラレルクエリの強化 スキャン スキャン 分散してスキャンし、 集計結果を結合して返す

巨大なテーブル レコード レコード レコード レコード … レコード レコード レコード レコード レコード レコード レコード レコード 部分集計 パラレル ワーカ スキャン SELECT count(*) FROM tb_name; 実行 パラレルクエリの強化 部分集計 パラレル ワーカ 部分集計 パラレル ワーカ 結果 スキャン 結果 スキャン 結果

パラレルクエリの強化 9.6からバージョンアップする度に 圧 倒 的 進 化

• Parallel Index Scan(b-treeのみ) • Parallel Index Only Scan(b-treeのみ) • サブクエリ • Merge Join • Parallel bitmap heap scan 10で追加された対象 パラレルクエリの強化

• Parallel Hash Join(9.6からより強化) • CREATE TABLE AS SELECT • CREATE MATERIALIZED VIEW • UNION ALLによるAPPEND • SELECT INTO • CREATE INDEX 11で追加された対象 パラレルクエリの強化

パラレルクエリの強化 多くの読み込みは並列化できる

パラレルクエリの強化 多くの読み込みは並列化できる ↓ 書き込みはまだ未対応

パラレルクエリの強化 基本的には Version upするだけで速くなる！

目玉の新機能 1. パーティションの強化 2. パラレルクエリの強化 3. SQLの拡張

SQLの拡張 INDEX ONLY SCANの強化

SQLの拡張 INDEX ONLY SCANの強化 ↓ カバリングインデックス

SQLの拡張 INCLUDE句の追加 ↓ INDEXに参照用の列を指定

SQLの拡張 INCLUDE句の追加 ↓ INDEXに参照用の列を指定 SELECT indexed_col FROM table_name WHERE id in (1, 2, 3); このような時にINCLUDE句でindexed_colを INDEXに含めることが出来る

SQLの拡張 Window関数で 除外指定できるようになった

SQLの拡張 • EXCLUDE CURRENT ROW 自分の行を除外する • EXCLUDE GROUP 自分と同じ値の行を除外する • EXCLUDE TIES 重複した値の行を除外する • EXCLUDE NO OTHERS ウインドウフレーム以外の値を省略する EXCLUDE句

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)

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)

目玉の新機能 強力な新機能と言うより PostgreSQL 10からの強化

目玉の新機能 使い方を考えるより 同じ使い方で速くなる機能が多い

あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ

嬉しいユースケース パーテーションの強化

嬉しいユースケース パーテーションの強化 ↓ パーテーション同士の結合

パーテーション同士の結合 パーティション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

嬉しいユースケース スキャンが多い

嬉しいユースケース スキャンが多い ↓ パラレルクエリが効く

嬉しいユースケース INDEXでも使うが、一番効くのは テーブルスキャン

パラレルクエリのユースケース 1. 集計系の分析クエリ 2. Hash Joinを使うような大きな TABLEのJOIN 3. CREATE TABLE AS SELECT文

嬉しいユースケース 特に嬉しいのは！？

嬉しいユースケース CREATE MATERIALIZED VIEW のリフレッシュでパラレルクエリ

嬉しいユースケース B+Tree CREATE INDEX 作成がパラレルクエリ

嬉しいユースケース そのほかにも

嬉しいユースケース ALTER の際に DEFAULT NULLではないカラム の追加が高速に！

嬉しいユースケース 特にパーティーションは 設計の幅が広がるので検討すべし

あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ

PostgreSQL 11の罠 パラレルクエリは万能ではない

PostgreSQL 11の罠 パラレルクエリは万能ではない ↓ Window関数、CTEでは使えない

PostgreSQL 11の罠 パーティーションに対して 外部キー制約は貼れない

PostgreSQL 11の罠 JIT compileは まず導入されただけ 高速化されるユースケースがかなり限定的

PostgreSQL 11の罠 ストアドプロシージャに 過度の期待をしない OracleDBのパッケージのような仕組みは無いので、 クラス継承のようなことは出来ない

PostgreSQL 11の罠 非互換な変更もある

PostgreSQL 11の罠 CREATE Functionで WITH句が禁止に

PostgreSQL 11の罠 RDSなどDBaaSに来るには まだ時間がかかりそう

PostgreSQL 11の罠 とはいえ、大きな罠は少なく 安心して使えるバージョン

あじぇんだ 1 自己紹介 2 目玉の新機能 3 嬉しいユースケース 4 PostgreSQL 11の罠 5 まとめ

まとめ PostgreSQLは魅力的な機能が豊富！

No content

No content

まとめ イチローのバットを使ったからといって 我々がイチローになれるわけでは無い

まとめ この二冊の内容を理解した上で 篠田の虎の巻を読む

まとめ 篠田の虎の巻を読む ↓ 過去のversionもあるので合わせて読む

まとめ 新規案件なら11を選ぶ価値がある

まとめ 既存システムでも新機能が活用できるなら 11に上げる価値がある

まとめ 最新版に追従していきましょう

ご清聴ありがとうございました