～徹底検証報告～
次期メジャーバージョン
PostgreSQL 9.5 の実力

SRA OSS, Inc. 日本支社 高塚 遙
2015-10-06 14:10 ～ 14:50【PostgreSQL 最新動向セミナー】
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

1

TOC
本講演の内容 PostgreSQL開発とリリースの概要

 PostgreSQL 9.5 新機能の検証報告

講演者について高塚 遙
SRA OSS, Inc. 日本支社にてPostgreSQLサポート、 各種クラスタ構築技術支援、マイグレーション技術支援、 などを担当

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

2

PostgreSQL開発とリリース （１） 開発体制 - 特定のオーナー企業を持たない方式
支援企業
Contributors

Major Contributors

Core TeamFujii Masao
Harada Hitoshi
Ishii Tatsuo
Kaigai KouheiSuzuki Koichi： 30～40名

Josh Berkus
Tom LanePeter Eisentraut
Magnus Hagander
Bruce Momjian
Dave Pageetc...

開発コミュニティ※www.postgresql.org 記載より
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

3

PostgreSQL開発とリリース （2） リリースの歴史
PostgreSQL ソースコード行数?!

4,500,000
4,000,000
3,500,000
3,000,000

ビットマップスキャン

2,500,000

更新の高速化

レプリケーション
Windows 64bit

多CPU
スケール

マテビュー
更新ビュー
更新FDW
データチェックサム

行数2,000,000
1,500,000
1,000,000

PITR、
Windows

4 CPU
スケール

ウィンドウ関数
再帰SQL

同期レプリケーション
外部テーブル

500,000

高速JSON
レプリケーションスロット

0

2005-11-08
2008-02-04
2010-09-20
2012-09-10
2014-12-188.1
8.3
9.0
9.2
9.4
2005-01-19
2006-12-05
2009-07-01
2011-09-12
2013-09-09
2015-12-20 ?
9.5
8.0
8.2
8.4
9.1
9.3
9.5
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

4

PostgreSQL開発とリリース （３） いつリリースされる？
 9.5.x は、仕様確定済み、 9.5 alpha2 がリリース済み
 9.5.0 リリース予定は？

2014/6/11

2015/7/1

5/15

12/18

9.4beta1
リリース

9.4.0
リリース

master
9.4.x
8/6

9.5.x

9.5alpha2
リリース

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

5

PostgreSQL 9.5 の新機能
主要な新機能 行単位セキュリティ
 BRINインデックス
 トランザクションの巻き戻し
pg_rewind コマンド 「挿入または更新」に対応
INSERT ... ON CONFLICT ...

 外部テーブルのインポート／継承 集計構文 に対応
GROUPING SETS、CUBE、ROLLUP

 JSONB型 むけの関数・演算子の拡充

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

6行単位セキュリティ（１） Row Level Security (RLS) と呼ばれる
 ポリシー定義に基づきテーブルの行アクセスを制御
ROW LEVEL
SECURITY 属性:
enable / disable

BYPASSRLS属性:
有/無DBロール（ユーザ）
ポリシー定義に基づきアクセス制限
テーブル
force で所有者、スーパーユーザに強制アクセス制御を実現

設定パラメータ(postgresql.conf)
row_security = on / off/ force

ポリシー定義Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

7行単位セキュリティ（2）テーブル：t1
GRANT ALL on t1 to user1;
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;→ 従来のアクセス権限は全て「可」に設定し、テーブルに対する RLS を有効に設定。
CREATE POLICY policy1 ON t1
FOR SELECT TO user1
USING (lv <= 2);

USING 指定条件が暗黙に適用
db1=> SELECT * FROM t1
WHERE id <= 103;
id | dat| lv-----+-------------+------102 | データ2
| 1103 | データ3 (公開) | 0(2 rows)

id101
102
103
104105

dat
データ１ (機密)
データ2
データ3 (公開)データ4
データ5 (機密)

lv5
1015

CREATE POLICY policy2 ON t1
FOR INSERT TO user1
WITH CHECK (lv = 1);

CHECK 指定条件を満たさないのでエラーになる
db1=> INSERT INTO t1 VALUES(106, 'データ6', 0);
ERROR: new row violates
row level security policy
for "t1"

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

8

BRIN インデックス（１） 新しいインデックス方式「brin」が追加された
 いくつかのブロック（＝ページ）の塊ごとに最大値・最小値を保持する

最大： '2013-12-05'
最小： '2013-01-23'

SELECT * FROM t2
WHERE dt >= '2015-10-01';

本SELECTでは、ブロック128・・・255 は読み飛ばし可能！

インデックスからスキャンするとき、読み飛ばせるブロックが分かる
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

9

BRINインデックス(2) Btreeインデックスとの比較
 作成時間とインデックスサイズを 10万行程度のデータで実測
300

インデックス作成時オプション
pages_per_range にて、何ブロックをインデックス上の１項目とするかを指定。
128 はデフォルト。

200

所要時間
6分の 1

150

単調増加データ
ランダム順データ

100

10万件データの整数カラムにて
インデックスをサイズを比較

50

2500

0B-Tree

2000

BRIN

インデックス作成時間を比較

データ量 [kB]

作成時間 [ms]

250

1500

サイズ
40分の 11000
500
0B-TreeCopyright © 2015 SRA OSS, Inc. Japan All rights reserved.

BRIN(pages_per_range=128)
10

BRINインデックス(2) Btreeインデックスとの比較 - 検索所要時間
 1 から 100万の昇順整数データを持つテーブルで条件検索
 WHERE c = 500000
 WHERE c BETWEEN 100000 AND 200000
 WHERE c BETWEEN 100000 AND 600000
Btree、BRIN とも「ビットマップインデックススキャン」の実行プランになる。
BRINが若干速い。

250

検索時間[m s ]

200
150
10050

インデックスなし
B-TreeBRIN

１件抽出はBtree が最も速い。

0
between 100000 and 200000= 500000
between 100000 and 600000
検索条件
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

ただし、行の物理配置がランダムなカラムだと、BRIN は効果的に働かない。
11トランザクション巻き戻し - pg_rewind (1) 昇格時点より先に進んだデータベースクラスタの問題
サーバ１
プライマリ
0/11000030

サーバ２

サーバ３
スタンバイ

スタンバイ
0/11000010

LSN(xlog location)

0/11000020

非同期ストリーミングレプリケーション
0/11000010 時点でタイムライン切り替え
サーバ１
障害停止
プライマリ
0/11000030

サーバ２昇格新プライマリ
0/11000025

サーバ３
スタンバイ

昇格時点よりも先に進んだデータは新たなプライマリのスタンバイとして使用することとができない。

0/11000020

再度ベースバックアップ取得をするしかなかった
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

12トランザクション巻き戻し - pg_rewind (2) pg_rewind コマンド
 プライマリとしたいサーバのタイムライン切り替え時点まで、ローカル側のデータベースクラスタのトランザクションを巻き戻す
クラッシュ終了後なら、[server1]$ pg_ctl start
起動・停止をしておく[server1]$ pg_ctl stop
[server1]$ pg_rewind -D $PGDATA \--source-server='host=server2 port=5432 user=postgres'[server1]$ vi $PGDATA/recovery.conf[server1]$ pg_ctl start[server3]$ pg_ctl stop
[server3]$ pg_rewind -D $PGDATA \--source-server='host=server2 port=5432 user=postgres'[server3]$ vi $PGDATA/recovery.conf[server3]$ pg_ctl start
必要な設定パラメータ(postgresql.conf)

full_page_writes = on （デフォルト）
wal_log_hints = on （デフォルトは off）
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

13

挿入または更新 - INSERT ... ON CONFLICT (1) よくある処理パターン
 新データ［id=101 hn='TARO' ml='taro@example.org'］につき
id = 101 のレコードが無いなら INSERT
id = 101 のレコードが在るなら UPDATE 以下のように記述できる
INSERT INTO t3 VALUES (101, 'TARO', 'taro@example.org')
ON CONFLICT (id)
DO UPDATE SET hn = EXCLUDED.hn, ml = EXCLUDED.ml; 従来はアプリ側で場合分けを実装
 あるいは以下のような WITH構文による技巧により実現
WITH val AS (SELECT ((101, 'TARO', 'taro@example.org')::t3).*),
upd AS (UPDATE t3 SET hn = val.hn, ml='taro@example.org'
FROM val RETURNING t3.id)
INSERT INTO t3
SELECT * FROM val WHERE id NOT IN (SELECT id FROM upd);
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

14

挿入または更新 - INSERT ... ON CONFLICT (2) ３つの方式を pgbench で比較 INSERT...ON CONFLICT WITH...INSERT PL/pgSQL プロシージャ

同時 500接続で「挿入または更新」を実行
pgbench -f test1.sql -c 500 -t 100 -n db1

まず、UPDATEして、0件なら INSERT、ユニーク制約違反なら、ROLLBACK してリトライする方式。

「挿入または更新」各方式の性能比較
4500
4000
3500

最も好成績

3000
2500

tps

2000

デッドロックが多発するため
TPS=1.0 程度

1500
1000
5000
INSERT...ON CONFLICTWITH...INSERT

PL/pgSQL プロシージャ

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

15

外部テーブルのインポート
 IMPORT FOREIGN SCHEMA 命令が追加
 外部テーブルをスキーマ単位で一括登録できる
db1=> CREATE SCHEMA remote;db1=> IMPORT FOREIGN SCHEMA apscm
FROM SERVER foreign_server INTO remote;db1=> \dE remote.*
List of relations
Schema |Name|
Type| Owner--------+------------+----------------+------remote | customers | foreign table | apuser
remote | products| foreign table | apuser...

 従来はテーブル毎に CREATE FOREIGN TABLE が必要であった
 各カラムの対応するデータ型をデフォルト通りでなく個別に設定するなら、引き続き、CREATE FOREIGN TABLE を使うことになる

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

16

外部テーブルの継承
 外部テーブルとローカルテーブルで継承が可能に パーティショニングと分散格納の組み合わせが可能になる
 従来から、テーブル継承はテーブルパーティショニング実現に利用されていた
サーバ１

並列問い合わせ機能は未だ実装されていないことに注意。

親テーブル
pt1

サーバ２

サーバ３

サーバ４

子テーブル
ct2

子テーブル
ct3

子テーブル
ct4

コマンド例： (s1db1 と s2db1 は各々リモートテーブル用のスキーマ)

s2db1=> CREATE TABLE ct2 () INHERITS (s1db1.pt1);
s1db1=> ALTER TABLE s2db1.ct2 INHERIT pt1;
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

17

集計構文
 以下の集計構文に対応
GROUP BY GROUPING SETS ( ... )GROUP BY ROLLUP ( ... )GROUP BY CUBE ( ... ) 各カテゴリ組み合わせ毎の小計を一度に出力させることができる SQL99 に規定されており、他ＤＢＭＳ の実装と大きな違いはない
テーブル：t_sales
customerproductnumTomTomegg
apple

1020JhonJhonegg
egg

155CUBE (c1, c2, ..) :指定カラムの全組み合わせ
ROLLUP (c1, c2, ...) :指定カラム順に包含する組み合わせ

db1=> SELECT customer,product,sum(num)FROM t_sales
GROUP BY GROUPING SETS((customer), (customer, product), ());
customer | product | sum----------+---------+----John
| egg
| 20
John|
| 20
Tom| apple| 20
Tom| egg| 10Tom|| 30|| 50(6 rows)

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

18

JSONB型むけ関数・演算子の拡充(1) JSONB データの部分更新が容易になる
 項目が無ければ追加、あれば右辺値で上書きする。
db1=> SELECT '{"name": "John", "age": 25}'::jsonb|| '{"age": 26}'::jsonb;?column?
----------------------------{"age": 26, "name": "John"}

 JSONB のオブジェクト要素、配列要素を削除
db1=> SELECT '{"name": "John", "age": 25}'::jsonb - 'age';?column?
-----------------{"name": "John"}
db1=> SELECT '["apple", "orange", "melon"]'::jsonb - 1;?column?
-------------------["apple", "melon"]
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

19

JSONB型むけ関数・演算子の拡充(2) 深い位置にある要素の削除
db1=> SELECT '{"name": "John", "contact": {"phone": "0123","email":"John@foohoge.com"}}'::jsonb#- '{contact, email}'::text[];?column?
-----------------------------------------------{"name": "John", "contact": {"phone": "0123"}}

 指定パスに値があるかに応じた動作
 第４引数 true なら 「あれば値の更新」 「無ければ挿入」
 第４引数 false なら 「あれば値の更新」 「無ければ何もしない」
db1=> SELECT jsonb_set('{"name": "James", "contact":{"phone": "01234 567890","fax": "01987543210"}}'::jsonb,'{contact,skype}','"myskypeid"'::jsonb,true);

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

20

まとめ
 PostgreSQL開発の概要についてお話いたしました。
 PostgreSQL 9.5 の新機能について紹介いたしました。
 各機能の詳細については、9.5 alpha2版のマニュアル（英語）を参照いただくことができます。
 SRA OSS, Inc. Webサイトの技術情報ページで弊社による検証レポートも今月中には公開予定です。

ご清聴ありがとうございました。
ご質問を承ります。

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

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

22