PostgreSQL 12
新機能解説2019-08-02
OSC 2019 Kyoto
SRA OSS, Inc. 日本支社
近藤 雄太
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

1講演者 と 本講演のテーマ
【講演者】
名前
近藤 雄太
所属
SRA OSS, Inc. 日本支社

略歴
PostgreSQLのセミナ講師、サポート、コンサルティング等に従事。
最近はPostgreSQLをベースにしたデータベース製品PowerGresの開発リーダーを務める【本日の講演内容】

 PostgreSQL の概況
 バージョン１２ の新機能解説
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

2

PostgreSQLの概況 (1) 多機能、高性能、かつオープンソースのリレーショナルデータベース管理システム（RDBMS）

 INGRES('70)、POSTGRES('80) 由来の歴史
 利用、複製、修正、再配布可能なライセンス
 特定オーナー企業が無い
 比較的組織だった開発体制

企業

企業

ある種のOSS開発体制
企業

PostgreSQL
製品

企業
開発者
PostgreSQL開発体制
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

3

PostgreSQLの概況 （2） 安定的で管理されたリリース
 機能面で着実に商用製品にキャッチアップ

そして12へ…

PostgreSQL各バージョンの行数とリリース日、主な追加機能
2018.102016.12017.102014.122011.92016.92013.92010.92012.92006.122009.7
JIT
compile2008.2
2005.11many
Parallel
JSONB
core
2005.1
Repliscale
hot (fast)cationBitmapUPDATE
Scan
logical
RLS,
Repli.
Mview,UPSERT
SSI,
UView
OLAP,
FDW
4core
CTE
scale
PITR

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

4

PostgreSQLの概況（３）
 機能面

 性能面
 多数のCPUコアによる
OLTP読み書き性能スケール（単純ベンチマーク条件）
 パラレル問い合わせ／JITに対応 （OLAPむけに）

 SQL SQL:2011 機能のほとんどをサポート
 JSON対応での優越
 PostGIS による GIS対応
 マテビュー差分更新など

 更新でWALボトルネック有 運用機能 PITRバックアップ
 監査ログ クラウドによるマネージドDB フラッシュバック／時系列

 クラスタ構成

論理・物理のレプリケーション
アクティブ＝スタンバイ構成
参照負荷分散／シャーディング
両方向論理レプリケーション

 共有ストレージを用いたアクティブ＝アクティブ構成（いわゆるRAC型）

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

5

PostgreSQL１２の新機能
 JSON path
 生成列（式を使って計算される列）
 大文字小文字を区別しない比較

アプリケーションのためのSQL機能追加。

 インデックスの機能追加・性能改善プラン作成や実行処理の改善を通した、実行時の性能アップ。

 パーティショニングの改善
 プランナのWITH句の最適化
 MCV拡張統計情報運用時の利便性向上。
構成設計上の選択肢が広がる。

 ページチェックサム改善 認証処理の拡充
 テーブルAMインタフェース追加
 いくつかの仕様変更

ー

将来のPostgreSQL拡張の布石

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

6

JSON Path (1) JSON Path … JSON から要素を抽出する記述方式の一つ
 SQL:2016 に含まれる ／ PostgreSQL実装は少し異なる
 jsonb型のみ対応 （json型は非対応）
db1=# SELECT family FROM t_family WHERE id = 1;
family-----------------------------------------------[{"age": 40, "att": "head","name": "Tarou"},{"age": 35, "att": "spouse", "name": "Hanako"},{"age": 10, "att": "child", "name": "Shou"}](1 row)
db1=# SELECT family_name,jsonb_path_query(family,'$[*]?(@.att == "head").name')
FROM t_family WHERE id = 1;
family_name | jsonb_path_query-------------+-----------------Satou| "Tarou"
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.
(1 row)

属性（att）が筆頭者（head）である人の名前（name）
7

JSON Path (2) JSON Path式は jsonpathデータ型の値
 JSON Path 検索で GINインデックスが利用可 より性能に優れた専用演算子クラス jsonb_path_ops
 従来からある jsonb のデフォルト演算子クラス jsonb_ops も動作

db1=# CREATE INDEX t_family_family_idx ON t_family
USING gin (family jsonb_path_ops);
db1=# explain SELECT id FROM t_family
WHERE family @@ '$[*].age < 15';

15才未満の家族を持つ人

QUERY PLAN----------------------------------------------------------------Bitmap Heap Scan on t_family (cost=56.00..60.01 rows=1 width=4)
Recheck Cond: (family @@ '($[*]."age" < 15)'::jsonpath)-> Bitmap Index Scan on t_family_family_idx (cost=0.00..56.0Index Cond: (family @@ '($[*]."age" < 15)'::jsonpath)(4 rows)
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

8

生成列（１）
 式を使って計算される列
 生成式の列についても実体が格納される=# CREATE TABLE t_people (id int primary key,height_cm float4,height_in float4 GENERATED ALWAYS AS (height_cm / 2.54) STORED);=# INSERT INTO t_people (id, height_cm)
VALUES (1, 156), (2, 168), (3, 172), (4, 185), (5, 191);=# SELECT * FROM t_people ;id | height_cm | height_in----+-----------+----------1 |156 | 61.417324
2 |
168 | 66.14173
3 |
172 | 67.71654
4 |
185 | 72.83465
5 |
191 | 75.19685(5 rows)

height_in には自動計算された値が格納される
直接変更はエラーになる=# UPDATE t_people SET height_in = 61.4 WHERE id = 1;
ERROR: column "height_in" can only be updated to DEFAULT
DETAIL: Column "height_in" is a generated column.
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

9生成列（２）
 従来からあるビューでも似たことができる
CREATE TABLE t_people_base (id int primary key, height_cm float4);
CREATE VIEW v_people AS
SELECT id, height_cm, (height_cm/2.54)::float4 AS height_in
FROM t_people_base;
二つの方法の違い
生成列を持つテーブル

列を計算する時点：

データ挿入・更新時

更新可能ビュー
データ参照時計算する列を直接操作しない INSERT、UPDATE、
データ更新処理：
DELETE が可能

INSERT、UPDATE、DELETE が可能

計算する列の格納データ：

実体を持つ実体を持たない計算する列に対するインデックスの作成：可能

元となるテーブルに式インデックスを作る

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

10大文字小文字やアクセントを区別しない文字列比較
 COLLATE（文字照合）機能の拡張
 ICUライブラリ を使って（--with-icu）ビルドした場合
 ICUライブラリが対応している同一視比較ができる 新しい ICUライブラリバージョンを要する 大文字/小文字、アクセント記号の有無、平仮名/片仮名/半角
db=# CREATE COLLATION ja_case_insensitive (provider = icu,
locale = 'ja-u-ks-level2', deterministic = false);
db=# SELECT * FROM t_case WHERE txt = 'ああ'
COLLATE ja_case_insensitive;id | txt----+----1 | ああ
2 | アア
3 | ｱｱ(3 rows)
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

新たなオプション。
false に指定して、同一視させる。
true だと従来通りの厳格な同一比較。

11インデックスの機能追加・性能改善（１）
改善箇所は多岐にわたる： Btreeインデックス性能改善
 キー重複が多くある場合に高速化／サイズを小さく 複合インデックスのサイズを小さく 内部ロック軽減で多コア同時処理を効率化

 REINDEX が CONCURRENTLY に対応
 GiST、SP-GiST、GIN の WAL出力量を削減
 GiST が INCLUDE に対応
 SP-GiST で kNN近傍探索に対応
 GiST に対する VACUUM 改善 他にもいくつか
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

12インデックスの機能追加・性能改善（２）
重複値を多く持つ Btree インデックスのサイズ
3,500

約20％縮小

3,000
2,500

kB

2,000
1,500
1,000
5000v11v12

CREATE TABLE t_idxtest1(id int primary key, code bigint);
CREATE INDEX t_idxtest1_code_idx
ON t_idxtest1 (code);
INSERT INTO t_idxtest1 SELECT g,(random() * 100)::bigint
FROM generate_series(1, 100000) as g;
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

13

インデックスの機能追加・性能改善（３）複合 Btree インデックスのサイズ
300,000

約5％縮小

250,000kB

200,000
150,000
100,000
50,0000v11v12CREATE TABLE t_idxtest2(k1 text, k2 text, k3 text, k4 text);
CREATE INDEX ON t_idxtest2(k1, k2, k3, k4);
INSERT INTO t_idxtest2 SELECT: {適当なダミーデータ}
FROM generate_series(1, 500000) as g;(× 2回)
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

14

インデックスの機能追加・性能改善（4）各インデックス作成時のWAL出力量
インデックス作成時の W AL 出力量kB (ただし GINについては 10倍値)

16,00014,00012,00010,000
8,000

40％削減

4分の1

3分の1v11v12

6,000
4,000
2,000
0GIN
GIN

GiST
GiST

kB （ GIN については 0.1kB ）SP-GiST
SP-GiST※100万件のダミーデータに各種インデックスを作成して、WAL の LSN進行量を計測
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

15

パーティショニングの改善（１） 性能面
 多パーティションに対する検索の高速化
 COPY TO によるデータ投入の高速化
 SQL実行で無駄なソートを省略する

 機能面




外部キー制約で被参照側でもパーティションテーブル対応
パーティションを調べるビュー、関数、psqlコマンドの拡充
パーティション定義構文で定数でなく式を使用可能に
パーティションテーブルでテーブルスペース指定

 他にもいくつか
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

16

パーティショニングの改善（2）多パーティションに COPY データ投入
4,500
4,000

所要時間 2分の1、バラつきも小さく

3,500
3,000
ms

2,500
2,000
1,500
1,000
500
0v11
●v12

3,000パーティションを持つパーティションテーブルに各10件ずつ 30,000件のデータを投入

CREATE TABLE oya (id int primary key, v text) PARTITION BY RANGE (id);CREATE TABLE ko1 PARTITION OF oya FOR VALUES FROM (0) TO (10);CREATE TABLE ko2 PARTITION OF oya FOR VALUES FROM (10) TO (20);..

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

17

パーティショニングの改善（3）一部パーティションのみ参照する SQL
200.00180.00160.00140.00

所要時間
100分の1ms

120.00100.00
80.00
60.00
40.00
20.00
0.00
●v11

v12

同テーブルで以下ＳＱＬを実行。
3000パーティション中、3パーティションのみ該当。
SELECT * FROM oya
WHERE id IN (100, 1000, 10000);id|v-------+---------------------------------100 | f899139df5e1059396431415e770c6dd
1000 | a9b7ba70783b617e9998dc4dd82eb3c5
10000 | b7a782741f667201b54880c925faec4b(3 rows)
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

18

プランナのWITH句最適化（１） WITH句の節（CTE、共通テーブル式）も最適化される
従来のプラン： WITH句は書いた通りに独立して処理される
EXPLAIN
WITH cte AS (SELECT * FROM t_customer)
SELECT * FROM t_sales JOIN cte USING (customer_id)
WHERE cte.info ~ '^abcde';info列にインデックス
QUERY PLANが在っても、全件取得----------------------------------------------------------------------------してから絞り込む、
Hash Join (cost=42470.00..53695.48 rows=5123 width=73)
賢くないプラン
Hash Cond: (cte.customer_id = t_sales.customer_id)
CTE cte-> Seq Scan on t_customer (cost=0.00..1834.00 rows=100000 width=37)-> CTE Scan on cte (cost=0.00..2250.00 rows=500 width=36)
Filter: (info ~ '^abcde'::text)-> Hash (cost=19346.00..19346.00 rows=1000000 width=41)-> Seq Scan on t_sales (cost=0.00..19346.00 rows=1000000 width=41)(8 rows)

最適化のために以下のようにサブクエリに書き直す必要があった
SELECT * FROM t_sales JOIN
(SELECT * FROM t_customer) c USING (customer_id)
WHERE c.info ~ '^abcde';
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

19

プランナのWITH句最適化（2）PostgreSQL12 のプラン： WITH句も最適化の対象となる

EXPLAIN
WITH cte AS (SELECT * FROM t_customer)
SELECT * FROM t_sales JOIN cte USING (customer_id) WHERE cte.info ~ '^abcde';QUERY PLAN--------------------------------------------------------------------------------

Gather (cost=1008.57..15625.03 rows=100 width=74)
Workers Planned: 2-> Hash Join (cost=8.56..14615.03 rows=42 width=74)
Hash Cond: (t_sales.customer_id = t_customer.customer_id)-> Parallel Seq Scan on t_sales(cost=0.00..13512.67 rows=416667 width=41)-> Hash (cost=8.44..8.44 rows=10 width=37)-> Index Scan using t_customer_info_idx on t_customer(cost=0.42..8.44 rows=10 width=37)
Index Cond: ((info >= 'abcde'::text) AND (info < 'abcdf'::tFilter: (info ~ '^abcde'::text)(9 rows)

WITH cte_name MATERIALIZED AS ( … )従来動作 ／ 必ず独立プラン

WITH cte_name AS ( … )最適化を許すWITH cte_name NOT MATERIALIZED AS ( … )複数回評価する最適化も許す

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

20

MCV拡張統計情報
 拡張統計情報とは:
 CREATE STATISTICS コマンドでプランナ統計情報項目を追加
 作成後は ANALYZE処理で統計情報が収集される
 v11 までのサポート項目
dependencies:ndistinct:

列間の依存関係
複数列の組み合わせに対する値の種類数

 MCV
 複数列の組み合わせに対する Most Common Values （最頻値）
列 location の最頻値:

列 age の最頻値:

最頻値

東京

大阪

京都

…

最頻値

20代

30代

40代

…

出現率

0.10

0.09

0.08

…

出現率

0.20

0.15

0.10

…

列 location、age の組み合わせの最頻値:
最頻値

京都,40代東京,20代東京,30代大阪,20代

大阪,
30代

…

出現率

0.05

0.02

0.01

0.01

0.01

…

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

個別の統計情報
では見えてこない偏りを把握
21

ページチェックサム改善
 ページチェックサム機能とは: initdb 時に (-k) –data-checksums オプションでチェックサム付加
 ページアクセス時に異常を検出できる（チェックサム無しの場合、黙ってデータが変わったり、構造的に不正な場合だけエラーが出たりする）
 コマンドによる明示的な検証も可能

12での拡張： 既存データベースクラスタを後からチェックサム有効化
pg_checksums –-enable {データベースクラスタディレクトリ}
 検証用コマンド pg_verify_checksums が pg_checksums に統合された

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

22

認証処理の拡充

 GSSAPI認証での暗号化に対応
 pg_hba.conf の第一フィールド（host や hostssl 等が書かれる所）にhostgssenc、 hostnogssenc 指定が追加

 LDAP認証で DNS から LDAPサーバを探す動作に対応
 SRVレコードを参照する

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

23

テーブルＡＭインタフェース追加
 プラガブルなテーブル格納インタフェース（＝Ａccess Method） 拡張モジュールでテーブル用のストレージエンジンを追加できる 従来から インデックスAM は任意に追加可能、これに加えて、テーブルAM を追加できるようになった テーブル毎にテーブルAM指定できる

 今のところインタフェースのみ
 開発されている実装
UNDOログを使って VACUUMレスを実現 zheap:
 zedstore: 列指向格納で OLAP最適化を実現

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

24非互換部分
 recovery.conf 廃止 リカバリ設定も postgresql.conf に記述する
 リカバリ起動を指示するには、recovery.signal ファイルを置く スタンバイ起動を指示するには、standby.signal ファイルを置く テーブルの行ごとの OID 廃止
 CREATE TABLE で WITH OIDS 指定のサポートが除去

 システムテーブルの oid 列が隠されなくなった SELECT * FROM pg_class で oid 列も出力される。

 その他、様々な細かな非互換修正


設定パラメータ名変更（trigger_file → promote_trigger_file）
設定値のデフォルト変更（jit = on...）
システムテーブル／ビューの定義変更（pgclass.relhasoids列の削除...）etc.
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

25

12 は今までのメジャーバージョンに比べて？
 既存機能の性能向上が主 12自体で「今すぐ使える」大きな機能追加はない
 9.6: パラレルクエリ
 10: 宣言的パーティショニング 11: JITコンパイル

 しかし、将来の布石として・・・ テーブルアクセスメソッドインターフェースの実装

 PostgreSQL は力をためている・・・

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

26

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

27