PostgreSQL 9.5 新機能紹介

Copyright © 2015 NTT DATA Corporation
2015年9月12日
株式会社 NTTデータ
PostgreSQL 9.5 新機能紹介
Java Küeche 勉強会「RDB最前線」 in 沖縄

2Copyright © 2015 NTT DATA Corporation
本日は
PostgreSQL9.5の新機能を紹介します
8/6にAlpha 2 リリース！

PostgreSQL 開発者@ NTT データ
社内 PostgreSQL 営業・技術支援
GresCube 開発・サポート
PostgreSQL のコア機能を開発
レプリケーション運用性向上
REINDEX SCHEMA / VERBOSE
pgbench（ベンチマークツール）の改善
pg_bigm(全文検索モジュール)
コア機能へのパッチレビューア
澤田雅彦 @sawada_masahiko

Thanks to
• Postgres 9.5 feature highlight (Michael Paquier)
• http://michael.otacoo.com/
• Waiting for 9.5 (Hubert Lubaczewski)
• http://www.depesz.com/
• 9.5 COMING TO YOU LIVE (Keith Fiske)
• http://slides.keithf4.com/pg95live/#/
• PostgreSQL Deep Dive (Satoshi Nagayasu)
• http://pgsqldeepdive.blogspot.jp/
• 日々の記録別館(nuko_yokohama)
• http://d.hatena.ne.jp/nuko_yokohama/

INDEX
PostgreSQLの進化の歴史
PostgreSQL 9.5 新機能紹介
性能向上系機能
開発に役に立つ機能
運用に役に立つ機能
9.6ではこんな機能が入るかも？

Copyright © 2015 NTT DATA Corporation 6
PostgreSQLの進化の歴史

進化の歴史
カリフォルニア大学のPostgresプロジェクト（1985～1993）が母体。
近年はエンタープライズ向けの機能・性能改善を重視している。
最新版は、9.4.4（2015年6月リリース）
2003
2004
2005
2006
2007
2008
2009
2000
2011
2010
7.3
8.0
• Windows対応
• セーブポイント
• メディア故障対応(PITR)
• テーブルスペース
2012
2013
2014
7.4
8.2
• CPUスケール
• オンライン索引作成
• GIN: 汎用転置索引
8.4
• Window関数・再帰クエリ
• VACUUM用メモリ自動管理
• 他DBMS互換性向上
9.0
• レプリケーション
• 列 / 条件付きトリガ
• Windows 64bit対応
9.1
• 同期レプリケーション
• パーティショニング強化
• 一時テーブル強化
9.2
• カスケードレプリケーション
• スケーラビリティ向上
• Index検索の強化
9.3
• 切替え時間短縮
• Viewの改良
• DB間連携強化、等
8.1
• パーティショニング
• 2相コミット
• バッファ管理改良
9.4
• レプリケーションの
運用性向上
• NoSQL対応強化
• 大容量メモリ対応
8.3
• HOT: 更新性能向上
• VACUUM自動化
• 全文テキスト検索

PostgreSQL 9.5 とは？

Q1. 特徴は？
A1. 大規模、特にDWH的な用途に強くなった！
Q2. いつリリース？
A2. 年内にはリリース予定 (8月にAlpha2がリリース)

本日紹介する PostgreSQL 9.5 新機能
性能改善系
• BRIN INDEX
• WAL圧縮
運用に役立つ系
• pg_rewind
• Row Level Security(RLS)
開発に役立つ系
• UPSERT (INSERT ON CONFLICT)
• 外部テーブルの継承
• IMPORT FOREIGN SCHEMA
• GROUPING SETS, CUBE, ROLLUP
• JSONB型の演算子、関数の追加
• TABLESAMPLE句
3カテゴリ、10機能を紹介

性能改善系新機能

BRIN INDEX (Block Range INdex)
Block Range(min/max)
1 - 128 1 ~ 1000
129 - 256 1001 ~ 2000
: :
インデックス作成
CREATE INDEX hoge_brin ON hoge USING brin(col);
近接している
ブロックの束に対して
列の最小値/最大値
をインデックスに記録
：
テーブル
BRINインデックス
128
ブロック
128
ブロック
128
ブロック
128
ブロック
ブロック
凡例

BRIN INDEX (Block Range INdex)
Block Range(min/max)
1 - 128 1 ~ 1000
129 - 256 1001 ~ 2000
: :
検索する範囲を
絞り高速に検索
：
テーブル
BRINインデックス
検索
SELECT * FROM hoge
WHERE col BETWEEN 1500 AND 1700;
ブロック
凡例

BRIN INDEX (Block Range INdex)のFAQ
Q. BRIN INDEXの特徴は？
A. DWH用途向けのインデックス。
他のインデックスに比べ、インデックスサイズが小さい。(ブロック束の粒度は調整可能)
Q. 使いどころは？
A. 巨大なテーブルにて、ある特定の範囲を検索するときに有用。(例えば、8月分の売り上
げを検索するなど)
さらに、カラムのデータとテーブル内の物理的な順序が相関しているとより効果的。
Q. 使用上の注意点は？
A. Btreeの代替となるインデックスではないことに注意。
静的で大規模なデータ(まさにDWH系)の検索に向いています。

WAL圧縮
Full Page Writes(FPW)を圧縮することでWALサイズを縮小
性能改善、ディスク領域削減、レプリケーションに有用
wal_compression = off(デフォルト) / on
FPW FPW FPW FPW
Compressed
FPW
OFF
ON
WALサイズを
縮小
Compressed
FPW
Compressed
FPW
Compressed
FPW

WAL圧縮(2)
pgbenchを使って圧縮効果を測定
※pgbench -c 5 -t 10000で測定
※②ではfiller列にランダム文字列を設定
約70%
約30%
1,200 MB
1,000 MB
800 MB
600 MB
400 MB
200 MB
0
100MB
70MB
60MB
50MB
40MB
30MB
20MB
10MB
0MB

性能向上系まとめ
・ BRIN INDEX
・ WAL圧縮

開発に役立つ系新機能

UPSERT
INSERT INTO emp VALUES (3, '鈴木', '開発')
ON CONFLICT (id)
DO UPDATE
SET name = EXCLUDED.name,
role = EXCLUDED.role;
行がなければINSERT、あればUPDATEを実行
id name role
1 斉藤開発
2 田中営業
3 鈴木開発
4 山下経理
5 佐藤営業
id=3の行がなければ
INSERT INTO emp VALUES (3, '鈴木', '開発');
id=3の行があれば
UPDATE emp SET name = '鈴木', role = '開発'
WHERE id = 3;

UPSERT(2)
• ON CONFLICT句では列名、制約名が指定可能
• ON CONFLICT (列名)
• ON CONFLICT 制約名
• 衝突時(CONFLICT)の動作はDO UPDATE/NOTHINGが指定可能
• DO UPDATE SET col1 = EXCLUDED.col1 WHERE col1 = ‘hoge’
• DO NOTHING
• EXCLUDED句を使うことで、VALUES句内のデータを使うことが可能
INSERT INTO emp VALUES (3, '鈴木', '開発')
ON CONFLICT (id)
DO UPDATE
SET name = EXCLUDED.name, role = EXCLUDED.role;

FDW強化
PostgreSQL独自機能である
Foreign Data Wrapper (FDW)が
大幅に強化されました！
FDWの主な9.5新機能
• Foreign Table Inheritance
• IMPORT FOREIGN SCHEMA

FDW(Foreign Data Wrapper)とは？
ID NAME
1 沖縄
2 新潟
…
ID NAME
1 沖縄
2 新潟
…
外部テーブル
テーブル
外部のデータをPostgreSQLのテーブルとして扱う機能
1,沖縄
2,新潟
…
CSVファイル
PostgreSQL
Oracle
read()
SELECT
SELECT
oracle_fdw
file_fdw

FDW強化 - IMPORT FOREIGN SCHEMA -
bar スキーマ
リモート先リモート元
CREATE FOREIGN TABLE …
これまではリモート元、リモート先それぞれでテーブル定義を実行する必要があった
fdw スキーマ
CREATE TABLE …
CREATE TABLE …
CREATE TABLE …
凡例
実テーブル
外部テーブル

FDW強化 - IMPORT FOREIGN SCHEMA -
bar スキーマ
リモート先からテーブル、ビュー、マテビューの定義をインポートできる
fdw スキーマ
CREATE TABLE …
CREATE TABLE …
CREATE TABLE …
スキーマ内の
テーブル、ビュー、マテビュー
定義をインポート
IMPORT FOREIGN SCHEMA
bar INTO fdw;
凡例
実テーブル
外部テーブル
リモート先リモート元

FDW強化 - Foreign Table Inheritance -
外部テーブルで継承(INHERITS)が使用可能
=# CREATE FOREIGN TABLE hoge_2015_10 (date timestamp,
value text)
INHERITS (hoge_parent)
SERVER pg_server
OPTIONS(table_name ‘hoge_2015_10’);

FDW強化 - Foreign Table Inheritance -
外部テーブルも含んだパーティショニングが可能に
Partitioning
+
FDW
SQL
子子子
子
子
凡例
実テーブル
外部テーブル
親
外部サーバとの通信など
子 CSV
postgres_fdw file_fdwpostgres_fdw

GROUPING SETS, CUBE, ROLLUP
SQLの新しい構文が追加
• １回のクエリで複数の異なる条件のGROUP処理が簡単・速く
brand | size | sum
-------+------+-----
Foo | | 30
Bar | | 20
| L | 15
| M | 35
| | 50
(5 rows)
 SELECT brand, size, sum(sales) FROM items_sold GROUP BY
GROUPING SETS ((brand), (size), ());
ブランドごとの集計
サイズごとの集計
全ての集計
<基本となるGRUPPING SETS>

GROUPING SETS, CUBE, ROLLUP
GRUPPING SET
(),
(d1),
(d2),
(d3),
(d1,d2),
(d1 ,d3),
( d2,d3),
(d1,d2,d3)
GROUP BY CUBE (d1, d2, d3);
列挙された要素について、
全ての組み合わせごとに集計
いずれもGROUPING SETをさらに便利にする構文
GRUPPING SET
(),
(d1),
(d1,d2),
(d1,d2,d3)
列挙された要素について、
左側から絞り込んだ組み合わせごとに集計
GROUP BY CUBE (d1, d2, d3); GROUP BY ROLLUP (d1, d2, d3);

PostgreSQLにおけるJSONの歴史
v9.2 (2012年) v9.3 (2013年) v9.4 (2014年) v9.5
TEXT型 + 構文チェッカ
利用可能な関数と演算子
はわずか
演算子4個追加
要素抽出の演算子(->)
関数10個追加
JSONから行への変換
バイナリ形式 + 構文チェッカ
GINインデックス対応
JSON型
サポート
JSONB型
サポート !?
関数と演算子の
充実化

JSONB型に追加された新しい関数・演算子
関数
演算子
-、-# 削除
|| 追加
jsonb_concat() 追加
jsonb_delete() 削除
jsonb_set() 更新
jsonb_pretty() JSONB型を見やすく整形
NEW!
NEW!
NEW!
NEW!
NEW!
NEW!
※JSONB型でのみ使用できる関数・演算子です

JSONB型に追加された新しい関数・演算子 - 追加 -
※同様のことがjsonb_concat関数で可能です
jsonb=# SELECT '{ "key1" : 1, "key2" : 2 }'::jsonb || '{"key3" : 3}'::jsonb ;
?column?
-----------------------------------
{"key1": 1, "key2": 2, "key3": 3}
jsonb=# SELECT '{ "key1" : 1, "key2" : 2 }'::jsonb || '{"key1" : 999}'::jsonb ;
?column?
--------------------------
{"key1": 999, "key2": 2}

JSONB型に追加された新しい関数・演算子 - 削除 -
※同様のことがjsonb_delete関数で可能です
jsonb=# SELECT '{ "key1" : 1, "key2" : 2 }'::jsonb - 'key1';
?column?
-------------
{"key2": 2}
jsonb=# SELECT '{ "key1" : { "key11" : 11 }, "key2" : 2 }'::jsonb #- '{key1, key11}';
?column?
-------------------------
{"key1": {}, "key2": 2}
jsonb=# SELECT '[ "hoge", "bar", "foo" ]'::jsonb - 1;
?column?
-----------------
["hoge", "foo"]

JSONB型に追加された新しい関数・演算子 - 追加・置換 -
jsonb=# SELECT jsonb_set('{ "key1" : 1, "key2" : 2 }'::jsonb,
'{"key1"}',
'999');
jsonb_set
--------------------------
{"key1": 999, "key2": 2}
jsonb=# SELECT jsonb_set('{ "key1" : 1, "key2" : 2 }'::jsonb,
'{"key999"}',
'999');
jsonb_set
---------------------------------------
{"key1": 1, "key2": 2, "key999": 999}

JSONB型に追加された新しい関数・演算子 - 追加・置換 -
jsonb=# SELECT jsonb_set('{ "key1" : { "key:array" : [1,2,3] } , "key2" : 2 }'::jsonb,
'{"key1", "key:array"}',
'999');
jsonb_set
-----------------------------------------
{"key1": {"key:array": 999}, "key2": 2}
jsonb=# SELECT jsonb_set('{ "key1" : { "key:array" : [1,2,3] } , "key2" : 2 }'::jsonb,
'{"key1", "key:array", 1}',
'999');
jsonb_set
-------------------------------------------------
{"key1": {"key:array": [1, 999, 3]}, "key2": 2}

JSONB型に追加された新しい関数・演算子 - 整形 -
postgres=# SELECT jsonb_pretty(
'{ "key1" : { "nest-key1" : [1, 2], "nest-key2" : { "nest-nest-key3" : "value" }}}‘
);
jsonb_pretty
---------------------------------------
{ +
"key1": { +
"nest-key1": [ +
1, +
2 +
], +
"nest-key2": { +
"nest-nest-key3": "value“ +
} +
} +
}
(1 row)

TABLESAMPLE句
• サンプリングメソッドをBERNOULLI、SYSTEMの2つから選択
• 取得する割合をパーセント(%)で指定
• REPEATABLEオプションを使うことでサンプルを選択することが可能
• TABLESAMPLE SYSTEM(10) REPEATABLE(4);
hoge テーブル
結果セット
SELECT * FROM hoge TABLESAMPLE SYSTEM(10);
Sample Scan
テーブル内の行を
サンプリング
して結果を取得

TABLESAMPLE句(2)
タプル単位でランダムに選択
• シーケンシャルI/O
• SYSTEMより遅い
タプル1
タプル2
タプル7
タプル8
タプル4
タプル5
タプル13
タプル9
タプル10
タプル12
タプル3
タプル11
BERNOULLIを指定
タプル1
タプル2
タプル7
タプル8
タプル4
タプル5
タプル13
タプル9
タプル10
タプル12
タプル3
タプル11
SYSTEMを指定
テーブルテーブル
ブロック
選択されたタプル
選択されないタプル
サンプリング方法は、BERNOULLI(ベルヌーイ)、SYSTEMから選択可能
ブロック単位でランダムに選択
• ランダムI/O
• BERNOULLIより早い

TABLESAMPLE句(3)
• BERNOULLI、SYSTEM共にページまたはタプル毎に指定された確率で選択される
• 1000行のテーブルで10%を指定してもちょうど100行とは限らない
• 選択されるタプルはデッドタプルも含む
• 確実に見える行のみをサンプリング対象としたい場合は、事前にVACUUMまたはVACUUM
FULLが必要
• WHERE句と併用した場合は、「サンプリング」→「WHERE句の評価」の順
• サンプリングをしてテーブルを結合する場合は注意が必要
• サンプリングの影響でテーブルの一貫性がないように見えてしまうことも
• 結合したテーブルに対してサンプリングしたい場合はマテビューを使う

TABLESAMPLE句についてのFAQ
Q. SYSTEM、BERNOULLIの使い分けは？
A. 遅くてもより正確な行数を取得したい→BERNOULI
多少取得行数はずれるが、速くサンプリングしたい→SYSTEM
Q. TABLESAMPLEの使いどころは？
A. 例えば、
• テーブルの中身全部をちゃんと見なくてもいい検索の時（傾向分析等）
• 総量計算の見積もり
• 「10%サンプリングして結果を10倍」＞「全件カウント」
• SELECT item, count(*) * 10 FROM item_table TABLESAMPLE BERNOULLI(10)
WHERE item = ‘xxx’ GROUP BY item;
など

開発に役立つ系新機能まとめ
・ UPSERT (INSERT ON CONFLICT)
・ FDW強化(Foreign table inheritance, IMPORT FOREIGN SCHEMA)
・ GROUPING SETS, CUBE, ROLLUP
・ JSONB型の演算子、関数の追加
・ TABLESAMPLE句

運用に役立つ系新機能

バージョンアップ時の非互換に注意
ちょっとその前に。

演算子の優先度が変わりました
9.4以前の優先度
.
::
[ ]
+ -
^
* / %
+ -
IS
ISNULL
NOTNULL
(any other operator)
IN
BETWEEN
OVERLAPS
LIKE ILIKE SIMILAR
< >
=
NOT
AND
OR
9.5の優先度
.
::
[ ]
+ -
^
* / %
+ -
(any other operator)
OVERLAPS
BETWEEN IN LIKE ILIKE
SIMILAR
< > = <= >= <>
IS ISNULL NOTNULL
NOT
AND
OR
例えば、
 IS, IS NULL,IS NOT NULLが同じ優先度に
 「<,>,=…」と「IS,IS NULL…」の優先度が逆転
よりSQL標準に合った、演算子の優先度になりました

※ 優先度は「IS」 > 「>」なので、「3 IS true」が先に評価される
※優先度は「IS」 < 「>」
SELECT 4 > 3 IS true;
(9.4以前)
ERROR: argument of IS TRUE must be type boolean,
not type integer
(9.5)
?column?
----------
t

ただ、優先度が変わるケースがわかりづらい。。
9.5にバージョンアップ時に優先度の変更に気づくためには？
operator_precedence_warning = on
(9.5)
=# SET operator_precedence_warning to on;
SET
=# SELECT 4 > 3 IS true;
WARNING: operator precedence change: IS is now lower
precedence than >
LINE 1: SELECT 4 > 3 IS true;
^
?column?
----------
t
(1 row)
バージョンアップ時は、
このパラメータをON
にして、WARNING
が出たSQLを直せば
OK！

運用に役立つ系新機能

ROW LEVEL SECURITY (RLS)
id name age role
1 斉藤 27 開発
2 田中 53 営業
3 鈴木 45 開発
4 山下 39 経理
5 佐藤 31 営業
role = ‘開発’
が見れるユーザ
ユーザが閲覧・操作できる行を指定できる機能
GRANTによるアクセス制御
role = ‘営業’
が見れるユーザ
テーブルへの
アクセス権が
ないユーザ
スーパーユーザー
または
テーブルの所有者
RSLによるアクセス制御

ROW LEVEL SECURITY (RLS)
id name age role
1 斉藤 27 開発
2 田中 53 営業
3 鈴木 45 開発
4 山下 39 経理
5 佐藤 31 営業
ユーザfoo
empテーブル
例) 開発者の情報のみをユーザfooに閲覧許可したい
① CREATE POLICY emp_foo_policy ON emp
FOR SELECT
TO foo
USING (role = '開発');
② ALTER TABLE emp ENABLE ROW LEVEL SECURITY;
SELECT *
FROM tmp;

pg_rewind
これまではフェイルバック時にはフルバックアップの転送が必須
レプリケー
ション
マスタスタン
バイ
停止マスタ
マスタスタン
バイ
レプリケー
ション
停止マスタ
両系稼働
マスタ単独稼働
両系稼働
フルバックアップ転送
マスタ故障により
フェイルオーバ
旧マスタの再組込み
(フェイルバック)
面倒！大変！時間かかりすぎ！
9.4
フル
バック
アップ

pg_rewind
フェイルバック時にDBデータを差分バックアップ転送できる機能
レプリケー
ション
マスタスタン
バイ
停止マスタ
マスタスタン
バイ
レプリケー
ション
停止マスタ
差分
バック
アップ
両系稼働
マスタ単独稼働
両系稼働
差分バックアップ転送
マスタ故障により
フェイルオーバ
旧マスタの再組込み
(フェイルバック)
pg_rewind
フェイルバック時間を大幅短縮！
9.5

運用に役立つ機能まとめ
• Row Level Security
• pg_rewind

9.6には、こんな機能が入るかも？

9.6提案中の機能
PG PG
PG
backend
凡例
実テーブル
外部テーブル
Parallel
Query
worker worker worker
Join
Pushdown
SQL
VACUUM
強化

マルチ同期レプリケーション(quorum commit)
マスタ
スタンバイ
例えば、
スタンバイのうち2台から応答があったら
同期レプリケーションは完了と判断したい！
同期レプリケーションのより柔軟な構成を可能にする機能

さいごに
PostgreSQL 9.5 使ってみませんか？
ソースコードはこちら
http://www.postgresql.org/ftp/source/v9.5alpha2/
バグ報告はこちら
http://www.postgresql.org/support/submitbug/

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

参考資料

参照スケーラビリティ向上
9.4に比べ、参照スケーラビリティが大きく向上
Scalability and Performance Improvements in PostgreSQL by Amit Kapila
https://www.pgcon.org/2015/schedule/attachments/378_postgresql-95-scalability-perf-improvements.pdf
DBデータが全てShared Bufferに収まるケース DBデータが全てメモリに収まるケース
LWLock
を改善
バッファ置換、
バッファ管理方法
を改善

id …
10
20 …
30 …
40 …
50 …
SKIP LOCKED句
SELECT * FROM hoge
WHERE id > 20
FOR UPDATE
NOWAIT;
SELECT * FROM hoge
WHERE id > 20
FOR UPDATE
SKIP LOCKED;
行ロックが取れる行だけロックして結果を取得
30 …
50 …
②
行ロックが取れる行のみ
(id = 30と50)
結果を返す
②
行ロックが一つでも
取れなかったら
ERROR終了
①
他処理によって
既に行ロックされてる

スタンバイWALアーカイブ
スタンバイサーバでのWALアーカイブが可能に！
archive_modeにalwaysが追加
レプリケー
ション
マスタ
スタン
バイ
スタン
バイ
スタン
バイ
WAL
アーカイブ
archive_mode = off
WAL
アーカイブ
archive_mode = always
archive_mode = always

PostgreSQL 9.5 新機能紹介

More Related Content

PostgreSQL 9.5 新機能紹介