PostgreSQL 11 技術解説

2018-08-24
PostgreSQL 11 技術解説セミナー
SRA OSS, Inc. 日本支社 高塚 遥
Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

1

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

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

企業

企業

企業

PostgreSQL
製品

開発者

企業

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

2

これまでのリリースのPostgreSQL source code lines
3,000,000

manyCPUスのケール

BitmapScan

2,000,000

パラレル

Replication

HOTupdate

2,500,000JSONB,
スのロット

論理システムReplication

1,500,000
1,000,000

Windows,PITR

12CPU
スのケール

分析関数
再帰SQL

同期Repli,
FDW

マテビュー
更新ビュービュー

500,000

BRIN
行security
UPSERT0
8.0

8.1

2005-01

8.2

8.3

2006-12

2005-11

8.4

9.0

2009-072008-02

9.1

9.2

2011-092010-09

9.3

9.4

2013-09

2012-099.5

2016-01

2014-12

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

9.6

10
2017-10

2016-093

現在の の PostgreSQL（+周辺ツールツール/拡張）の実力の実力
クラスタ構成：構成：

SQL機能的には：には：
ANSI SQL:2011コア 概ね準拠概ね準拠ね準拠準拠● 各種の組み込み言語の組み込み言語組み込み言語み込み言語込み言語み込み言語言語●

地理情報システムシステム● JSON型●●●

他DB連携（FDW）

性能的には：には：●●

HAクラスタ構成：、BDRクラスタ構成：、MPPクラスタ構成：(shared nothing) shared nothing) )RAC型(shared nothing) shared disk))は不可

運用支援など：など：

OLTPの組み込み言語多CPUスケール単位論理レプリケーション：
参照→96コアでもスケールスケール単位論理レプリケーション
更新→96コアでもスケールスケール単位論理レプリケーション●

インスタ構成：ンス単位レプリケーションレプリケーション● テーブル単位論理レプリケーション単位レプリケーション論理レプリケーション●

OLAPには：パラレル単位論理レプリケーションクエリ対応拡大

ログ/状態の集積分析ツール の組み込み言語集積分析ツール ツール単位論理レプリケーション 概ね準拠pg) _statsinfo / pg) _badg) er● クライアントツール単位論理レプリケーション
Pg) Admin4 / SI Object Browser● AWS / Azure / Goog) le Cloudで対応
AWS Aurora で対応●Copyright © 2018 SRA OSS, Inc. Japan All rights reserved.

4

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

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

5

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.

6

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.

7

スのトアドプロシージャ（１）の実力 途中でで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.

8

スのトアドプロシージャ（２）の実力 プロシージャで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.

9パーティショニング機能の拡充機能の拡充（１）の実力 パーティションテーブ実行を実現ル全体にインデックスを作成できるにインデックスのを行い、ネイ作成する。できるストアドプロシージャ
 パーティションテーブ実行を実現ル全体にインデックスを作成できるに外部キーを設定できるキーを行い、ネイ設定オーナー企業が無いできるストアドプロシージャ
 パーティション間の行更新によるデータ移動の行更新ビューによるストアドプロシージャデータ移動
 ハッシュパーティショニング機能の拡充
 問い合わせでのパーティション除外処理の改善い合にわせでのパーティション除外処理システムの改善
 問い合わせでのパーティション除外処理の改善い合にわせでのパーティション指向の結合／集約の結合に／集約パーティションテーブ実行を実現ル
パーティション1

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

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

10

パーティショニング機能の拡充機能の拡充（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.

これはＯＫ
これはＮＧ11

パーティショニング機能の拡充機能の拡充（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.

12

パーティショニング機能の拡充機能の拡充（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 loLEFT 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.

13

パラレル処理システムの拡充（１）の実力 並列ハッシュ結合に新ビューし処理回数が多い場合にいプランナ要がある。素の追加の追加
ほとんどの主キー制約が作れる要がある。プランナ要がある。素の追加が無い並列化されたされた 並列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.

14

パラレル処理システムの拡充（２）の実力 並列ハッシュ結合に
 並列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.

15

SCRAMチャンネルバインド
 SCRAM認証がチャンネルバインドに対応が無いチャンネルバインドに対応
 SSL接続の場合に意味があるの場合にに意味があるが無いあるストアドプロシージャ
 セッション固有情報を認証に使うを行い、ネイ認証がチャンネルバインドに対応に使う↓
やり返し処理回数が多い場合にとり返し処理回数が多い場合に内容を別のセッションに使いまわすことができないを行い、ネイ別できないケースでも実行時に除外するのセッションに使いまわすことが無いできな性能テスト例：い↓Man-in-the-middle攻撃を防ぐを行い、ネイ防ぐぐ
 SSL接続の場合に意味がある + scram-sha-256認証がチャンネルバインドに対応 であればデフォルトで使われるデフォルトで使われるストアドプロシージャ
方式は接続の場合に意味がある文字列で指定オーナー企業が無い（他には tls-server-end-point か 空欄）の実力：
psql "host=test1.example.com dbname=db1 user=test
sslmode=require scram_channel_binding=tls-unique"（実際は一行）は一に配付される行）の実力

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

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

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

欠けていたけていた機能の補完された

例えばデフォルトで使われる、一に配付される定オーナー企業が無いでな性能テスト例：い間の行更新によるデータ移動隔で採取されたで採取り出し、された8月24日のあるストアドプロシージャ所のキャストの気温データがあるとして・・・データが無いあるストアドプロシージャとし処理回数が多い場合にて・・・

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

17その他（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.

18

その他（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.

19その他（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.

20その他（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.

21

まとめ
 強力な性能テスト例：開発体にインデックスを作成できる制を行い、ネイ持つ前後１件をフレームに含めている。つオープンソースの PostgreSQL の適用範囲を決めている。は十分に広いい PostgreSQL 11 は未実現機能を行い、ネイ埋めていくバージョンアップめていくバージョンアップ
 累積データに対する分析問い合わせ むけの拡充データに対するストアドプロシージャ分析問い合わせでのパーティション除外処理の改善い合にわせ む必要がある。けの拡充
 ＪＩＴによる実行 ／ パラレル問い合わせでのパーティション除外処理の改善い合にわせ ／ パーティショニング機能の拡充 ／ ウィンドウ関数

 Oracle からの移植で役立つでのニーズ設定によるストアドプロシージャ拡充 プロシージャでの COMMIT / ROLLBACK クラウド利用を行い、ネイにらんだ SSL接続の場合に意味があるの拡充
 SCRAMチャンネルバインド

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

22

オープンソースのとともに

23