Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PostgreSQL11解体新書 / PostgreSQL11-Release

soudai sone
November 17, 2018

PostgreSQL11解体新書 / PostgreSQL11-Release

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

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

soudai sone

November 17, 2018
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. 自己紹介 名前 : 曽根 壮大(そね たけとも) 年齢 : 33歳(3人の子供がいます) 職業

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

    : 副社長/CTO 所属 : 株式会社 オミカレ 日本PostgreSQLユーザ会(JPUG) 勉強会担当 技術的にはLL系言語やRDBが好きです
  3. 子テーブル レコード4 レコード1 子テーブル レコード2 子テーブル レコード3 親テーブル レコード1 レコード2

    レコード3 レコード4 … レコード5 レコード6 レコード7 レコード8 レコード9 … … … パーティーションとは? 親テーブルにレコード の実体は無い 指定したルールに基づいて 子テーブルに分散される
  4. パーティーションとは? 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月の集計が他のテーブルを参照 しないので小さなデータを直接 集計できる
  5. 3種類のパーティーション 親テーブル 子テーブル 親テーブル 子テーブル 親テーブル 子テーブル レンジ パーティーション リスト

    パーティーション ハッシュ パーティーション 1月,2月,3月 ~ など の範囲で分ける 東京,大阪,広島 ~ など の 項目で分ける Keyのハッシュ値に基づ いて分ける 新機能
  6. リストパーティーション -- 親テーブル作成 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');
  7. リストパーティーション -- データ投入 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)
  8. レンジパーティーション 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 ');
  9. レンジパーティーション 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)
  10. 子テーブル 子テーブル 子テーブル 親テーブル 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が分散する
  11. 巨大なテーブル レコード レコード レコード レコード … レコード レコード レコード レコード

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

    レコード レコード レコード レコード 部分集計 パラレル ワーカ スキャン SELECT count(*) FROM tb_name; 実行 パラレルクエリの強化 部分集計 パラレル ワーカ 部分集計 パラレル ワーカ 結果 スキャン 結果 スキャン 結果
  13. • Parallel Index Scan(b-treeのみ) • Parallel Index Only Scan(b-treeのみ) •

    サブクエリ • Merge Join • Parallel bitmap heap scan 10で追加された対象 パラレルクエリの強化
  14. • Parallel Hash Join(9.6からより強化) • CREATE TABLE AS SELECT •

    CREATE MATERIALIZED VIEW • UNION ALLによるAPPEND • SELECT INTO • CREATE INDEX 11で追加された対象 パラレルクエリの強化
  15. SQLの拡張 INCLUDE句の追加 ↓ INDEXに参照用の列を指定 SELECT indexed_col FROM table_name WHERE id

    in (1, 2, 3); このような時にINCLUDE句でindexed_colを INDEXに含めることが出来る
  16. SQLの拡張 • EXCLUDE CURRENT ROW 自分の行を除外する • EXCLUDE GROUP 自分と同じ値の行を除外する

    • EXCLUDE TIES 重複した値の行を除外する • EXCLUDE NO OTHERS ウインドウフレーム以外の値を省略する EXCLUDE句
  17. 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)
  18. 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)
  19. パーテーション同士の結合 パーティション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