PostgreSQL 9.4
評価検証報告SRA OSS, Inc. 日本支社 高塚 遙
2014-09-11 15:55 ～ 16:30
PostgreSQL 9.4 最新情報セミナー

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

1

はじめに
本講演の構成Part 1 性能アップって、どのくらいですかPart 2

この新機能は何ですか

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

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

2

PostgreSQL 9.4 検証報告 Part1

性能アップって、どのくらいですか？

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

3

PG 9.4 検証報告 Part1 : GIN 性能改善 (1) 基礎的な性能改善度合いを確認 50万件データを使って同マシンで PostgreSQL 9.3 と 9.4 を比較

 ４つの局面で優劣を比較 インデックスの物理サイズ
 既にデータがある状態からのインデックス作成性能
 インデックスが作成されている状態からのデータ挿入性能
 インデックスを使った検索性能

配列カラムに対して、指定した配列を「含む」 行の件数を調べる
Copyright © 2014 SRA OSS, Inc. Japan All rights reserved.

4

PG 9.4 検証報告 Part1 : GIN 性能改善 (2)

1/5 に縮小
20％ 改善

30％ 改善10% 改善Copyright © 2014 SRA OSS, Inc. Japan All rights reserved.

5

PG 9.4 検証報告 Part1 : WALの性能改善（１） データ圧縮により WAL出力量が低減
 更新トランザクションを 5万回実行 カスタムシナリオも実施 ― UPDATEだけの単純な更新内容に変更

6 ％低減

15 ％低減Copyright © 2014 SRA OSS, Inc. Japan All rights reserved.

6

PG 9.4 検証報告 Part1 : WALの性能改善（２） ＷＡＬ 書き込みの同時実行性が向上
 4core × 2cpu マシンにて RAMディスク上でベンチマーク・ ５％程度の向上
・ 僅かだが有意

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

7

PG 9.4 検証報告 Part1 : JSONB の性能 (1) 旧来の JSON 型 と JSONB型の比較 ― データ投入
 １０万件のログ情報を投入

JSON への INSERT 時間
3530
所要時間 (sec)2520154 ～ 5％だけ遅い

105

1 つの GIN
インデックス

4 つのBtree
インデックス

0JSONB 型
JSON 型

JSON+ 関数 Btree x 4
JSONB 型＋ GIN

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

8PG 9.4 検証報告 Part1 : JSONB の性能 (2) 旧来の JSON 型 と JSONB型の比較 ― 検索
テーブル定義SELECT id, j-> 'ts', j -> 'mes'
FROM t_logb
WHERE (j -> 'lev')::text = '"fatal"'
ORDER BY id DESC LIMIT 100;

フィールド検索 ( インデックス利用なし )idserialj

json or
jsonb

データ例
8

関数インデックスで性能差異なし。
ＧＩＮの「～を含む」
インデックス検索はjsonb のみ可能。

7所要時間 (sec)

6
54
3
2
1

半分の所要時間0
JSON 型JSONB 型

1400003 |{ "ts":"2014-08-23 21:26:21","pid":14212,"lev":"error", "tag":"ddd","mes":"ae01d175 .............."}
1400004 |{ "ts":"2014-08-23 21:27:41","pid":14858,"lev":"warning", "tag":"aaa""mes":"9ff0d058fd ............"}

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

9

PG 9.4 検証報告 Part1 : JSONB の性能 (3) 他のドキュメント型データベースとの比較
 Couchbase Server 速さを訴求する製品（右は製品Webサイトより）

 前項と同様「整数主キー + ＪＳＯＮカラム + GINインデックス」のテーブルで比較
10 万件データ投入
60

PG、couchbaseとも、大量データ投入専用コマンドの利用なし

50

sec

40
30
20
100

PG9.4

PG9.4(unlogged)

couchbase

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

10

PG 9.4 検証報告 Part1 : JSONB の性能 (4) 並列ランダム更新
 ４並列で２件を主キーでランダム読み出し、１件を書き換え

 条件読み出し ＪＳＯＮ内の属性で条件検索／10万件から 600件程度を取り出す
 GINインデックスが使えるので PostgreSQL が高速
並列ランダム更新

条件読み出し200

0.4

1800.35

160

0.3

120

0.25

1000.2

sec

sec

140

80
60
40

およそ２倍

0.15
0.1

20

0.05

00

PG9.4(unlogged)

couchbase

およそ1.5倍
PG9.4

couchbase

あくまで単体比較であって、NoSQL製品の売りは「スケールアウトのしやすさ」である点にも注意
Copyright © 2014 SRA OSS, Inc. Japan All rights reserved.

11

PostgreSQL 9.4 検証報告 Part 2

この新機能は何ですか？

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

12

PG 9.4 検証報告 Part 2 : レプリケーションスロット (1)レプリケーションスロット＝ プライマリとスタンバイをつなぐ絆

プライマリ
スロット

スタンバイスロット

データ消費者

 レプリケーション状態や付帯データを保持管理する
 これまで特定スタンバイに対応して状態を保持する場所が無かった
pg_create_physical_replication_slot('slot_name') physical レプリケーションスロット
 スタンバイが必要としている WALファイルを削除してしまうのを防ぐ
 スタンバイが参照しているデータの物理削除（VACUUM）を防ぐ
 従来の対策よりも優れた点がある

 logicalレプリケーションスロット
 WALによる変更差分の論理表現を記録、蓄積してくれる
Copyright © 2014 SRA OSS, Inc. Japan All rights reserved.

13

PG 9.4 検証報告 Part 2 : レプリケーションスロット (2) physicalレプリケーションスロットのアドバンテージ
 コンフリクト対策、WAL削除対策が従来手段よりも優れている 「どこまで必要」という情報がスロットに保持される
 スタンバイがダウン中でも機能する

 レプリケーションコンフリクトとは？
 主としてスタンバイで参照しているものを削除して発生する
 VACUUM による行物理削除、テーブル、データベース削除

プライマリフィード
バック

スタンバイ

スロットテーブルテーブル

 WAL削除の問題とは？
 スタンバイが未だ参照したい WAL
ファイルが、プライマリサーバ上で削除されてしまうこと

DELETE、VACUUM

repeatable read
SELECT 従来は？
 wal_keep_segment、 vacuum_defer_cleanup_age で固定値指定
 restore_command で ファイル単位レプリケーションも併用
Copyright © 2014 SRA OSS, Inc. Japan All rights reserved.

14

PG 9.4 検証報告 Part 2 : レプリケーションスロット (3) logical レプリケーションスロットの可能性=# SELECT pg_create_logical_replication_slot('logi_slot', 'test_decoding');=# CREATE TABLE t_test (id int primary key, v text);=# INSERT INTO t_test
SELECT g, left(md5(g::text),5) FROM generate_series(1,5) as g;=# UPDATE t_test SET v = 'XXX' WHERE id = 3;=# SELECT * FROM pg_logical_slot_get_changes('logi_slot', NULL, NULL);
location | xid | data----------+------+-----------------------------------------------------------0/D907F60 | 2015 | BEGIN 2015
0/D91CEA8 | 2015 | COMMIT 2015
0/D91CEE0 | 2016 | BEGIN 2016
0/D91CEE0 | 2016 | table public.t_test: INSERT: id[integer]:1 v[text]:'c4ca4'
0/D91CFA8 | 2016 | table public.t_test: INSERT: id[integer]:2 v[text]:'c81e7'
0/D91D038 | 2016 | table public.t_test: INSERT: id[integer]:3 v[text]:'eccbc'
0/D91D0C8 | 2016 | table public.t_test: INSERT: id[integer]:4 v[text]:'a87ff'
0/D91D158 | 2016 | table public.t_test: INSERT: id[integer]:5 v[text]:'e4da3'
0/D91D268 | 2016 | COMMIT 2016
0/D91D490 | 2017 | BEGIN 2017
0/D91D490 | 2017 | table public.t_test: UPDATE: id[integer]:3 v[text]:'XXX'
0/D91D658 | 2017 | COMMIT 2017
Copyright © 2014 SRA OSS, Inc. Japan All rights reserved.

15

PG 9.4 検証報告 Part 2 : SQL新機能 (1) 更新ビューのCHECKオプション=> CREATE TABLE t_num (num int);=> CREATE VIEW v_natural AS SELECT num FROM t_num WHERE num > 0
WITH CASCADED CHECK OPTION;=> INSERT INTO v_natural VALUES (10);← 正の数は格納できる=> INSERT INTO v_natural VALUES (-100);← 負の数は定義外

ERROR: new row violates WITH CHECK OPTION for view "v_natural"
DETAIL: Failing row contains (-100).

 集約クエリの FILTERオプション
SELECT to_char(date_trunc('month', ts), 'YYYY-MM') as month,coalesce(sum(amount) FILTER(WHERE iid = 1), 0) as sales_i1,coalesce(sum(amount) FILTER(WHERE cid = 1), 0) as sales_c1,coalesce(sum(amount), 0) as sales_all
FROM t_sale GROUP BY date_trunc('month', ts);

⇒ 実行プランにおいて集約とスキャンが一括して行われる
Copyright © 2014 SRA OSS, Inc. Japan All rights reserved.

16

PG 9.4 検証報告 Part 2 : SQL新機能 (2) ALTER SYSTEM で設定変更=# ALTER SYSTEM
SET work_mem TO '10MB';=# ALTER SYSTEM
SET work_mem TO DEFAULT;

postgresql.conf

自動書換え
優先参照

postgresql.auto.conf※ pg_ctl reload や pg_ctl restart に相当する処理は実行してくれない ALTER TABLESPACE MOVE で一括移転=# ALTER TABLESPACE pg_default MOVE TABLES TO tblspc1;=# ALTER TABLESPACE pg_default MOVE INDEXES TO tblspc2;
従来は テーブル、インデックスごとに ALTER コマンド実行が必要であった

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

17

PG 9.4 検証報告 Part 2 : SQL新機能 (3) REFRESH MATERIALIZED VIEW のCONCURRENTLYオプション
 リフレッシュが、マテリアライズドビューへの SELECT と競合しない
 マテリアライズドビュー上の ユニークインデックスが必要

 ALTER TABLE のロック競合が一部軽減
 ... ALTER COLUMN ... SET STATISTICS ... など明らかにテーブルのデータ読み書きと関係ない⇒ こういったオプションではテーブル読み書きと競合しない、 SHARE UPDATEEXCLUSIVE モードに変更 （従来は ACCESS EXCLUSIVE モードで SELECTもブロックされてしまう）
Copyright © 2014 SRA OSS, Inc. Japan All rights reserved.

18

レポート形式の資料
 「PostgreSQL 9.4 に関する検証報告」
 本講演の内容を概ね含みます 実行して試すことのできる検証報告文書となっています

SRA OSS, Inc. 日本支社
（http://www.sraoss.co.jp）⇒ ［技術情報］⇒ ［PostgreSQL］

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

19

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

20