PostgreSQL 11 新機能解説

2018-10-27
オープンソースカンファレンス 2018 Tokyo/FallSRA OSS, Inc. 日本支社 近藤 雄太
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

1

【リリース間近！】 PostgreSQL 11 新機能解説

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

2

【リリース間近！】 PostgreSQL 11 新機能解説↓
10/18 に 11.0 がリリースされました！

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

3

自己紹介
 近藤雄太 所属： SRA OSS, Inc. 日本支社 PostgreSQL技術グループ
 仕事内容： PostgreSQL サポート
 商用 PostgreSQL 開発
 PostgreSQL トレーニング講師

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

4

PostgreSQLとは
 多機能、高性能、かつオープンソースのリレーショナルデータベース管理システム

 INGRES('70),POSTGRES('80)由来の歴史
 BSDスタイルのライセンス
 特定オーナー企業が無い
PostgreSQLの開発体制
ある種のOSS開発体制

企業

企業

企業

PostgreSQL
製品

開発者

企業

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

5これまでのリリース

manyCPU
スケール

Windows,PITR

2005-01

論理
Replication

12CPU
スケール

2006-12

2005-11

パラレル

ReplicationHOT
updateBitmapScan

JSONB,
スロット

分析関数
再帰SQL

2009-072008-02

同期Repli,FDW

2011-092010-09

マテビュー
更新ビュー

2013-09

2012-09

2016-01

2014-12

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

BRIN
行security
UPSERT2017-10

2016-096

現在の 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

PostgreSQL 11 での拡張ポイント
 JITコンパイル
 途中でCOMMIT/ROLLBACKできるストアドプロシージャ
 パーティショニング機能の拡充
 パラレル処理の拡充
 SCRAMチャンネルバインド
 その他

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

8

JITコンパイル（１） SQL実行にあたって Just In
Time コンパイルを行い、ネイティブ実行を実現

従来の実行

ＪＩＴによる実行

SQL文字列SQL文字列Parseツリー

Parseツリー

Executorツリー

Executorツリー

実行bitcode

 LLVMコンパイラ基盤を使用
 繰り返し処理回数が多い場合に
 プランナコストでJIT適用を判断 行データ取り出し、SELECTリス
トの式、WHERE句の式に適用

実行

SELECT id, c1, c2, c3, (c4 * random() * 10)::int
FROM t1 WHERE typ = 101;
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

9

JITコンパイル（２）簡易な性能テスト例：
11の関数・演算子と3箇所のキャスト
×１億件のループ↓
このくらいから効果がでる

CREATE FUNCTION f100000000() RETURNS SETOF bigint
ROWS 100000000 LANGUAGE sql AS $$SELECT g FROM generate_series(1::bigint, 100000000::bigint) AS g;$$;
SELECT g, 'X is "' || random() * pi() *substr((g * ln(g::float8 + g / 2))::text, 1, 5)::float8 || '"'FROM f100000000() AS g;
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

10

ストアドプロシージャ（１） 途中でCOMMIT/ROLLBACKできるストアドプロシージャ Oracle Database の PL/SQL から移植で役立つ:
FOR i IN 1..1000 LOOP
FOR j IN 1..100 LOOP
INSERT INTO t_sample
SELECT * FROM f1(i * 100 + 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

ストアドプロシージャ（２） プロシージャで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--02
さしあたりは4
シンプルな用途に
6
使いたい
8(5 rows)

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

12

パーティショニング機能の拡充（１） パーティションテーブル全体にインデックスを作成できる
 パーティションテーブル全体に外部キーを設定できる
 パーティション間の行更新によるデータ移動
 ハッシュパーティショニング 問い合わせでのパーティション除外処理の改善
 問い合わせでのパーティション指向の結合／集約
パーティションテーブル
パーティション1

バージョン10 までで欠けていた機能が補完されたパーティション2
パーティション3

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

13

パーティショニング機能の拡充（2） パーティションテーブル全体にインデックスを作成できる
 グローバルインデックスが作られるわけではない。
各パーティションに各々インデックスが作られるだけ。
 パーティション分割条件の列を含む必要がある。
 パーティションテーブル全体に主キー制約が作れる

 パーティションテーブル全体に外部キーを設定できる
 パーティションテーブルを外部キーの被参照テーブルにするのは不可
t_log_parted

log_id , ts , message , severity_levelt_severity_level_mastert_log_detail_parted

severity_level , description

log_id , detail_message

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

これはＯＫ
これはＮＧ
14

パーティショニング機能の拡充（3） パーティション間の行更新によるデータ移動
パーティションテーブル
t_log
パーティション1（2016年データ）
パーティション2(2017年データ）

UPDATE t_log
SET ts = '2018-08-03 12:00'
WHERE log_id = 12345;

パーティション3（2018年データ)

ハッシュ対象の列 (col1, col2)

 ハッシュパーティショニング キー列のハッシュ値（正整数値）の剰余でパーティション分けを行う
 各パーティションに行が均一に配付される
 パラレル全件スキャンに向いている

ハッシュ値 1234567

WITH (MODULUS 5, REMAINDER 2)のパーティションに格納

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

15

パーティショニング機能の拡充（4） 問い合わせでのパーティション除外処理の改善
 従来の constraint_exclusion では除外できなかったケースに対応
 「SELECT count(*) FROM t_log WHERE ts < '2017-01-01'」など
 プラン作成時点では判別できないケースでも実行時に除外する
 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

パーティションテーブル
t_log_detail_parted

パーティション1（id: 100000 ～ 199999）

結合

パーティション1（id: 100000 ～ 199999）

パーティション2(id: 200000 ～ 299999）

結合

パーティション2(id: 200000 ～ 299999）

パーティション3(id: 300000 ～ 399999）

結合パーティション3(id: 300000 ～ 399999）

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

16

パラレル処理の拡充（１） 並列ハッシュ結合
新しいプランナ要素の追加
ほとんどの主要プランナ要素が並列化された

 並列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

パラレル処理の拡充（２） 並列ハッシュ結合
 並列Append

例

b1=# explain SELECT max(length(l.mes || d.detail)) FROM t_log lLEFT JOIN t_log_detail d ON (l.id = d.id);
QUERY PLAN----------------------------------------------------------------Finalize Aggregate (cost=15810.11..15810.12 rows=1 width=4)-> Gather (cost=15809.89..15810.10 rows=2 width=4)
Workers Planned: 2-> Partial Aggregate (cost=14809.89..14809.90 rows=1 width=4)-> Parallel Hash Left Join(cost=6192.53..14028.64 rows=104167 width=66)
Hash Cond: (lo.id = ld.id)-> Parallel Append(cost=0.00..4569.43 rows=104168 width=37)-> Parallel Seq Scan on t_log_0 lo(cost=0.00..1619.24 rows=58824 width=37)-> Parallel Seq Scan on t_log_1 lo_1
(cost=0.00..1619.24 rows=58824 width=37):

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

18

SCRAMチャンネルバインド
 SCRAM認証がチャンネルバインドに対応

 SSL接続で利用可能
 セッション固有情報を認証に使う↓やりとり内容を別のセッションに使いまわすことができない↓
Man-in-the-middle攻撃を防ぐ
 SSL接続 + scram-sha-256認証 であればデフォルトで使われる

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

20その他（１） ウィンドウ関数の対応構文追加
 GROUPSウィンドウフレーム対応
 ウィンドウフレームRANGEモード対応
 ウィンドウフレームのEXCLUDEオプション

db1=# SELECT * FROM t_temperature2 ;
id |
dt|
t
----+---------------------+------1 | 2018-08-24 00:00:00 | 18.20
2 | 2018-08-24 00:11:18 | 20.74
3 | 2018-08-24 00:23:49 | 22.65
4 | 2018-08-24 00:35:41 | 21.51
5 | 2018-08-24 00:38:29 | 20.13::::
142 | 2018-08-24 22:40:37 | 27.74
143 | 2018-08-24 22:55:29 | 26.32
144 | 2018-08-24 23:14:59 | 24.40
145 | 2018-08-24 23:27:11 | 23.61
146 | 2018-08-24 23:47:06 | 27.40(146 rows)

欠けていた
機能の補完

例えば、一定でない間隔で採取された8月24日のある所の気温データがあるとして・・・Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

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_avg3---------------------+-------+-------+-------:::
2018-08-24 08:30:18 | 29.28 | 29.06 | 28.48
2018-08-24 08:39:33 | 30.59 | 29.06 | 28.48
2018-08-24 08:56:40 | 29.82 | 29.06 | 28.48
2018-08-24 09:15:05 | 27.54 | 28.14 | 29.00
2018-08-24 09:32:42 | 29.11 | 28.14 | 29.00
2018-08-24 09:40:13 | 27.75 | 28.14 | 29.00
2018-08-24 09:45:58 | 28.14 | 28.14 | 29.00
2018-08-24 10:00:06 | 29.11 | 29.62 | 29.91
2018-08-24 10:12:01 | 29.15 | 29.62 | 29.91
2018-08-24 10:19:12 | 28.72 | 29.62 | 29.91
2018-08-24 10:39:05 | 29.65 | 29.62 | 29.91
2018-08-24 10:52:36 | 31.47 | 29.62 | 29.91
2018-08-24 11:12:09 | 32.01 | 31.12 | 31.47
2018-08-24 11:13:22 | 30.55 | 31.12 | 31.47
2018-08-24 11:20:27 | 31.89 | 31.12 | 31.47:::

t_avg は含まれる１時間単位の平均。
「年月日時」でパーティション区切り。
これは以前から使えた機能。
t_avg3 は含まれる前後3時間の平均。
「年月日時」順で並べて同じ値を持つ前後１件をフレームに含めている。

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

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 FOLLOWINGEXCLUDE CURRENT ROW ), 2) AS t_avg_1hx
FROM t_temperature2;
dt|t| t_avg_1h | t_avg_1hx---------------------+-------+----------+----------:::
2018-08-24 08:30:18 | 29.28 |28.74 |
28.68
2018-08-24 08:39:33 | 30.59 |
28.77 |
28.57
2018-08-24 08:56:40 | 29.82 |28.72 |
28.61
2018-08-24 09:15:05 | 27.54 |28.94 |
29.09
2018-08-24 09:32:42 | 29.11 |28.88 |
28.85
2018-08-24 09:40:13 | 27.75 |28.78 |
28.91
2018-08-24 09:45:58 | 28.14 |28.78 |
28.86
2018-08-24 10:00:06 | 29.11 |28.96 |
28.94
2018-08-24 10:12:01 | 29.15 |28.96 |
28.94
2018-08-24 10:19:12 | 28.72 |29.57 |
29.66
2018-08-24 10:39:05 | 29.65 |30.04 |
30.08
2018-08-24 10:52:36 | 31.47 |30.53 |
30.43
2018-08-24 11:12:09 | 32.01 |31.06 |
30.97
2018-08-24 11:13:22 | 30.55 |31.06 |31.11
2018-08-24 11:20:27 | 31.89 |31.30 |
31.23:::

t_avg_1h は前後１時間の平均。
当該行の値との距離でフレーム範囲を決めている。
t_avg_1hx は前後1時間から当該行を除いた平均。
EXCLUDEで除外している。

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

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, 100000) AS g;
db1=# \timing
Timing is on.
db1=# ALTER TABLE t_alt ADD c2 int;
ALTER TABLE
Time: 16.918 ms
db1=# ALTER TABLE t_alt ADD c3 int DEFAULT 100;
ALTER TABLE
Time: 25.824 ms ← 同マシンPostgreSQL 10では 600～700ms

デフォルト値を行データに格納しない。

 ALTER TYPE は従来と変わらず遅い
db1=# ALTER TABLE t_alt ALTER c3 TYPE bigint;
ALTER TABLE
Time: 640.206 ms
db1=# ALTER TABLE t_alt ALTER c3 TYPE int;
ALTER TABLE
Time: 602.989 ms

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

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

まとめ
 強力な開発体制を持つ PostgreSQL の適用範囲は十分に広い PostgreSQL 11 は未実現機能を埋めていくバージョンアップ
 累積データに対する分析問い合わせ むけの拡充
 ＪＩＴ ／ パラレル問い合わせ ／ パーティショニング ／ ウィンドウ関数

 Oracle からの移植でのニーズによる拡充
 プロシージャでの COMMIT / ROLLBACK クラウド利用をにらんだ SSL接続の拡充
 SCRAMチャンネルバインド

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

26

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

Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

27

オープンソースとともにURL： http://www.sraoss.co.jp/
E-mail： sales@sraoss.co.jp
Tel： 03-5979-2701
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

28