PostgreSQL 11 新機能解説オープンソースカンファレンス 2018 Tokyo/Fall SRA OSS, Inc. 日本支社近藤雄太 Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 1

Size: px

Start display at page:

こうごにばし
5 years ago
Views:

PostgreSQL 11 新機能解説 2018-10-27 オープンソースカンファレンス 2018 Tokyo/Fall SRA OSS,

5 PostgreSQL とは多機能高性能かつオープンソースのリレーショナルデータベース管理システム INGRES('70),POSTGRES('80) 由来の歴史 BSD スタイルのライセンス特定オーナー企業が無い PostgreSQL の開発体制企業ある種の OSS 開発体制企業企業製品開発者企業 PostgreSQL Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 5

これまでのリリース Bitmap Scan HOT update Replication manycpu スケール JSONB, スロットパラレル論理 Replication Windows, PITR 12CPU スケール分析関数再帰 SQL 同期 Repli, FDW マテビュー更新ビュー BRIN 行 security

6 これまでのリリース Bitmap Scan HOT update Replication manycpu スケール JSONB, スロットパラレル論理 Replication Windows, PITR 12CPU スケール分析関数再帰 SQL 同期 Repli, FDW マテビュー更新ビュー BRIN 行 security UPSERT Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 6

7 現在の PostgreSQL(+ 周辺ツール / 拡張 ) の実力 SQL 機能的には : ANSI SQL:2011コア概ね準拠各種の組み込み言語地理情報システム JSON 型他 DB 連携 (FDW) クラスタ構成 : インスタンス単位レプリケーションテーブル単位論理レプリケーション HAクラスタ BDRクラスタ MPPクラスタ (shared nothing) RAC 型 (shared disk) は不可性能的には : OLTPの多 CPUスケール : 参照 96コアでもスケール更新 96コアでもスケール OLAPには : パラレルクエリ対応拡大運用支援など : ログ / 状態の集積分析ツール pg_statsinfo / pg_badger クライアントツール PgAdmin4 / SI Object Browser AWS / Azure / Google Cloud で対応 AWS Aurora で対応 Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 7

9 JIT コンパイル (1) SQL 実行にあたって Just In Time コンパイルを行いネイティブ実行を実現 LLVM コンパイラ基盤を使用繰り返し処理回数が多い場合にプランナコストで JIT 適用を判断行データ取り出し SELECT リストの式 WHERE 句の式に適用従来の実行 SQL 文字列 Parseツリー Executorツリー実行 JITによる実行 SQL 文字列 Parseツリー Executorツリー bitcode 実行 SELECT id, c1, c2, c3, (c4 * random() * 10)::int FROM t1 WHERE typ = 101; Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 9

10 JIT コンパイル (2) 簡易な性能テスト例 : 11 の関数演算子と 3 箇所のキャスト 1 億件のループこのくらいから効果がでる CREATE FUNCTION f () RETURNS SETOF bigint ROWS LANGUAGE sql AS $$ SELECT g FROM generate_series(1::bigint, ::bigint) AS g; $$; SELECT g, 'X is "' random() * pi() * substr((g * ln(g::float8 + g / 2))::text, 1, 5)::float8 '"' FROM f () AS g; Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 10

11 ストアドプロシージャ (1) 途中で COMMIT/ROLLBACK できるストアドプロシージャ Oracle Database の PL/SQL から移植で役立つ : FOR i IN LOOP FOR j IN LOOP INSERT INTO t_sample SELECT * FROM f1(i * j); END LOOP; COMMIT; END LOOP; : 100 件ごとに COMMIT しつつデータ生成する PL/pgSQL のユーザ定義関数ではそのままには実現できない新たなデータベースオブジェクト PROCEDURE CALL で呼び出す中で COMMIT と ROLLBACK が可能暗黙に次トランザクションが開始される PL/SQL と似た振る舞い Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 11

12 ストアドプロシージャ (2) プロシージャで COMMIT / ROLLBACK が利用可能いろいろ制限がある入れ子で CALL してもサブトランザクションにはならない明示的トランザクション内では実行できない関数内からの CALL ではトランザクション制御不可他の PL 言語でも対応 db1=# CREATE PROCEDURE p_tx1() LANGUAGE plpgsql AS $$ BEGIN FOR i IN 0..9 LOOP INSERT INTO test1 (a) VALUES (i); IF i % 2 = 0 THEN COMMIT; ELSE ROLLBACK; END IF; END LOOP; END; $$; db1=# CALL p_tx1(); db1=# SELECT * FROM test1; a (5 rows) さしあたりはシンプルな用途に使いたい Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 12

13 パーティショニング機能の拡充 (1) パーティションテーブル全体にインデックスを作成できるパーティションテーブル全体に外部キーを設定できるパーティション間の行更新によるデータ移動ハッシュパーティショニング問い合わせでのパーティション除外処理の改善問い合わせでのパーティション指向の結合 / 集約パーティションテーブルパーティション 1 バージョン 10 までで欠けていた機能が補完されたパーティション 2 パーティション 3 Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 13

14 パーティショニング機能の拡充 (2) パーティションテーブル全体にインデックスを作成できるグローバルインデックスが作られるわけではない各パーティションに各々インデックスが作られるだけパーティション分割条件の列を含む必要があるパーティションテーブル全体に主キー制約が作れるパーティションテーブル全体に外部キーを設定できるパーティションテーブルを外部キーの被参照テーブルにするのは不可 t_log_parted log_id, ts, message, severity_level t_severity_level_master severity_level, description これは OK t_log_detail_parted log_id, detail_message これは NG Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 14

15 パーティショニング機能の拡充 (3) パーティション間の行更新によるデータ移動パーティションテーブル t_log パーティション 1 (2016 年データ ) パーティション 2 (2017 年データ ) パーティション 3 (2018 年データ ) UPDATE t_log SET ts = ' :00' WHERE log_id = 12345; ハッシュパーティショニングキー列のハッシュ値 ( 正整数値 ) の剰余でパーティション分けを行う各パーティションに行が均一に配付されるパラレル全件スキャンに向いているハッシュ対象の列 (col1, col2) ハッシュ値 WITH (MODULUS 5, REMAINDER 2) のパーティションに格納 Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 15

16 パーティショニング機能の拡充 (4) 問い合わせでのパーティション除外処理の改善従来の constraint_exclusion では除外できなかったケースに対応 SELECT count(*) FROM t_log WHERE ts < ' ' などプラン作成時点では判別できないケースでも実行時に除外する enable_partition_pruning = on で有効になる ( デフォルト ) 問い合わせでのパーティション指向の結合 / 集約結合の例 : SELECT lo.mes, ld.detail FROM t_log_parted lo LEFT JOIN t_log_detail_parted ld ON (lo.id = ld.id); パーティションテーブル t_log_parted パーティション 1 (id: ~ ) パーティション 2 (id: ~ ) パーティション 3 (id: ~ ) 結合結合結合パーティションテーブル t_log_detail_parted パーティション 1 (id: ~ ) パーティション 2 (id: ~ ) パーティション 3 (id: ~ ) Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 16

17 パラレル処理の拡充 (1) 並列ハッシュ結合並列 Append 新しいプランナ要素の追加ほとんどの主要プランナ要素が並列化された並列 CREATE TABLE.. AS 並列 SELECT.. INTO.. 並列 CREATE MATERIALIZED VIEW.. これまでこれら構文では並列処理が行われなかった並列インデックス作成 db1=# CREATE INDEX ON t_log (ts, id); DEBUG: building index "t_log_0_ts_id_idx" on table "t_log_0" with request for 1 parallel worker DEBUG: building index "t_log_1_ts_id_idx" on table "t_log_1" with request for 1 parallel worker DEBUG: building index "t_log_2_ts_id_idx" on table "t_log_2" serially CREATE INDEX 他にも多数の細かなパラレル対応並列数は max_parallel_maintenance_workers 設定の影響を受ける /Btree のみ Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 17

18 パラレル処理の拡充 (2) 並列ハッシュ結合並列 Append 例 b1=# explain SELECT max(length(l.mes d.detail)) FROM t_log l LEFT JOIN t_log_detail d ON (l.id = d.id); QUERY PLAN Finalize Aggregate (cost= rows=1 width=4) -> Gather (cost= rows=2 width=4) Workers Planned: 2 -> Partial Aggregate (cost= rows=1 width=4) -> Parallel Hash Left Join (cost= rows= width=66) Hash Cond: (lo.id = ld.id) -> Parallel Append (cost= rows= width=37) -> Parallel Seq Scan on t_log_0 lo (cost= rows=58824 width=37) -> Parallel Seq Scan on t_log_1 lo_1 (cost= rows=58824 width=37) : Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 18

19 SCRAM チャンネルバインド SCRAM 認証がチャンネルバインドに対応 SSL 接続で利用可能セッション固有情報を認証に使うやりとり内容を別のセッションに使いまわすことができない Man-in-the-middle 攻撃を防ぐ SSL 接続 + scram-sha-256 認証であればデフォルトで使われる Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 20

20 その他 (1) ウィンドウ関数の対応構文追加 GROUPS ウィンドウフレーム対応ウィンドウフレーム RANGE モード対応ウィンドウフレームの EXCLUDE オプション欠けていた機能の補完 db1=# SELECT * FROM t_temperature2 ; id dt t :00: :11: :23: :35: :38: : : : : :40: :55: :14: :27: :47: (146 rows) 例えば一定でない間隔で採取された 8 月 24 日のある所の気温データがあるとして Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 21

21 その他 (2) ウィンドウ関数の対応構文追加 SELECT dt, t, round(avg(t) OVER (PARTITION BY to_char(dt,'yyyymmdd-hh24')), 2) AS t_avg, round(avg(t) OVER ( ORDER BY to_char(dt,'yyyymmdd-hh24') GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS t_avg3 FROM t_temperature2; dt t t_avg t_avg : : : :30: :39: :56: :15: :32: :40: :45: :00: :12: :19: :39: :52: :12: :13: :20: : : : t_avg は含まれる 1 時間単位の平均年月日時でパーティション区切りこれは以前から使えた機能 t_avg3 は含まれる前後 3 時間の平均年月日時順で並べて同じ値を持つ前後 1 件をフレームに含めている Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 22

22 その他 (3) ウィンドウ関数の対応構文追加 SELECT dt, t, round(avg(t) OVER (ORDER BY dt RANGE BETWEEN '1 hour'::interval PRECEDING AND '1 hour'::interval FOLLOWING),2) AS t_avg_1h, round(avg(t) OVER (ORDER BY dt RANGE BETWEEN '1 hour'::interval PRECEDING AND '1 hour'::interval FOLLOWING EXCLUDE CURRENT ROW ), 2) AS t_avg_1hx FROM t_temperature2; dt t t_avg_1h t_avg_1hx : : : :30: :39: :56: :15: :32: :40: :45: :00: :12: :19: :39: :52: :12: :13: :20: : : : t_avg_1h は前後 1 時間の平均当該行の値との距離でフレーム範囲を決めている t_avg_1hx は前後 1 時間から当該行を除いた平均 EXCLUDE で除外している Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 23

23 その他 (4) ALTER TABLE.. ADD COLUMN 改善デフォルト値を伴った ADD COLUMN が高速化 db1=# CREATE TABLE t_alt (id int primary key, c1 int); db1=# INSERT INTO t_alt SELECT g, g FROM generate_series(1, ) AS g; db1=# \timing Timing is on. db1=# ALTER TABLE t_alt ADD c2 int; ALTER TABLE Time: ms db1=# ALTER TABLE t_alt ADD c3 int DEFAULT 100; ALTER TABLE Time: ms 同マシンPostgreSQL 10 では 600~700ms デフォルト値を行データに格納しない ALTER TYPE は従来と変わらず遅い db1=# ALTER TABLE t_alt ALTER c3 TYPE bigint; ALTER TABLE Time: ms db1=# ALTER TABLE t_alt ALTER c3 TYPE int; ALTER TABLE Time: ms Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 24

24 その他 (5) CREATE INDEX が INCLUDE 句に対応 index-only-scan 用 CREATE INDEX idx1234 ON t1 (c1, c2) INCLUDE (c3, c4); initdb 時に WAL ファイルサイズ設定従来は --with-wal-segsize=nn としてビルド時に指定 blocksize wal-blocksize については変わらずビルド時に指定ロジカルレプリケーションで TRUNCATE 対応ビューに対するテーブルロック各種 psql pgbench の機能追加各種のロック軽減とオプティマイザ改良 Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 25

25 まとめ強力な開発体制を持つ PostgreSQL の適用範囲は十分に広い PostgreSQL 11 は未実現機能を埋めていくバージョンアップ累積データに対する分析問い合わせむけの拡充 JIT / パラレル問い合わせ / パーティショニング / ウィンドウ関数 Oracle からの移植でのニーズによる拡充プロシージャでの COMMIT / ROLLBACK クラウド利用をにらんだ SSL 接続の拡充 SCRAM チャンネルバインド Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 26

PostgreSQL 11 検証レポート

PostgreSQL 11 検証レポート 1.0 版 2018 年 7 月 4 日 SRA OSS, Inc. 日本支社 170-0022 東京都豊島区南池袋 2-32-8 Tel. 03-5979-2701 Fax. 03-5979-2702 http://www.sraoss.co.jp/ 目次 1. はじめに... 2 2. 概要... 2 3. 検証のためのセットのためのセットアップ... 2 3.1. ソフトウェア入手... 2

PostgreSQL 11 新機能解説 オープンソースカンファレンス 2018 Tokyo/Fall SRA OSS, Inc. 日本支社近藤雄太 Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 1

PostgreSQL 11 新機能解説オープンソースカンファレンス 2018 Tokyo/Fall SRA OSS, Inc. 日本支社近藤雄太 Copyright 2018 SRA OSS, Inc. Japan All rights reserved. 1