PostgreSQL 10 技術解説SRA OSS, Inc. 日本支社
マーケティング部 PostgreSQL 技術グループ
高塚 遙
2017-09-14
PostgreSQL 最新動向紹介セミナー

自己紹介
高塚 遙 （たかつか はるか）
●
所属●

–

●

SRA OSS, Inc. 日本支社
マーケティング部 PostgreSQL 技術グループ

業務–PostgreSQL の技術サポート、 技術支援コンサルタント、クラスタ構築、 セミナー講師、etc.

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

2

PostgreSQL●

代表的なオープンソースのRDBMSの１つ–●

オーナー企業を持たず、コミュニティによる開発が続けられている–●

カリフォルニア大学で開発された研究用RDBMSのIngres(1970)を先祖に持つ

年１回のメジャーバージョンアップ

PostgreSQLライセンスで配布–

BSDタイプの緩いライセンス

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

3

PostgreSQL 10
2017/8/31 に beta4 リリース
正式リリースは未だ

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

4

バージョン番号の仕様変更●

今までは x.y.z 形式– 9.6.3●

9.6 の部分がメジャーバージョン– 機能追加や仕様変更など●.3 の部分がマイナーバージョン– 不具合修正など●

今後は x.y 形式– 10.0●

10 の部分がメジャーバージョン番号●.0 の部分がマイナ―バージョン番号

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

5

PostgreSQL 10 の新機能
ロジカルレプリケーション●
宣言的パーティショニング●
パラレルクエリ改善
●

●

その他

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

6ロジカルレプリケーション

参考資料：
PGCon2017
Logical Replication in PostgreSQL 10Peter Eisentraut
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

7

PostgreSQL のレプリケーション●

ストリーミングレプリケーション（PostgreSQL 9.0 ～）–マスタからスレーブにトランザクションログ（WAL）を転送することによりデータの複製を実現–

対象はデータベース全体–

非同期

スレーブは参照クエリを受け付ける
ことができる （Hot Standby）

マスタへは更新・参照の両方とも可能

リカバリし続ける

WAL転送
プライマリ

スタンバイ
WAL 書き込み

WAL 書き込み

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

8ロジカル（論理） vs 物理？
●

従来のストリーミングレプリケーション–

トランザクションログ（WAL）をスタンバイに転送＆適用
ログの内容は「ファイルの変更内容」●
スタンバイサーバはマスタサーバのビット単位の複製となる●＝ 「物理レプリケーション」
完全な複製●
スタンバイには一切の物理的変更ができない●

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

9論理レプリケーション●

トランザクションログを「論理的な変更内容」として転送する–●例）「テーブル T に、行 (1, ‘abc’) が挿入されました」

この形式のログだと–必要なものだけを転送できたりテーブル単位のレプリケーション●
挿入だけをレプリケーション●–

複製先データベースに変更を加えたり●

一時テーブルの作成や、インデックスの追加→ データの処理や解析に使える–

複数のサーバのデータを１箇所に集めてみたり– 異なるバージョン間での複製をしてみたり
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

10ロジカルレプリケーション概要●

publish/subscribe （出版/購読型）モデル–

出版側は特定の購読者を想定せずにメッセージを送信
複製先（subscriber）

複製元（publisher）

変更

テーブル

テーブル

publication

subscription
論理ログ転送–Publication は転送する変更を選択可能（INSERT, UPDATE, DELETE）–

UPDATE, DELETE のためにはテーブルの行を特定する情報が必要●

デフォルトではプライマリキーが使用される
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

11

publication 作成（複製元）●

指定のテーブルに publication を作成–

テーブルのオーナである必要がある
CREATE PUBLICATION mypublication
FOR TABLE users, departments;●「全てのテーブル」を指定することもできる–

スーパユーザ権限が必要
CREATE PUBLICATION alltables FOR ALL TABLES;●

転送される変更内容の指定
CREATE PUBLICATION insert_only FOR TABLE mydata
WITH (publish = 'insert');
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

12

subscription 作成（複製先）●

Publication 名のリストと接続文字列を指定して、subscription を作成–

スーパユーザ権限が必要–

接続ユーザは、接続先に replication 権限が必要

CREATE SUBSCRIPTION mysub
CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
PUBLICATION mypublication, insert_only;●●

CREATE SUBSCRIPTION 文がコミットされると、テーブルの同期とレプリケーションが開始される
デフォルトでは、自動的に publisher 側にレプリケーションスロットが作成される–

Subscription を削除すると、レプリケーションスロットも削除される–

これらを回避するオプションもある。その場合は手動で作成/削除が必要。
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

13

レプリケーションの衝突●

ロジカルレプリケーションでは、複製先でもテーブルの更新が可能–

制約の衝突が起こる可能性がある●●

テーブルにプライマリキーか、UNIQUEキーが存在する場合(UPDATE, DELETE の複製時)衝突が発生するとレプリケーションが停止してしまう–

手動で解消が必要
複製先の問題の行を削除する●
または、複製元のトランザクションをスキップする●– subscriber 側で pg_replication_origin_advance() という関数を使う

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

14

今のロジカルレプリケーションではできないこと●●

CREATE TABLE などの DDL のレプリケーション–

対応しているのは INSERT, DELETE, UPDATE–TRUNCATE は非対応

テーブル以外のレプリケーション–●

シーケンスは非対応

相互レプリケーション–

テーブル毎に publisher / subscriber が異なるなら可能–

同じテーブルをマルチマスターにはできない
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

15

宣言的パーティショニング

参考資料：PGCon2017
Partition and conquer large data with PostgreSQL 10
Declarative partitioning comes to PostgreSQL
Amit Langote Ashutosh Bapata
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

16

パーティショニング●

巨大な親テーブルを複数の子テーブルに分割–

親テーブルへの検索時、必要な子テーブルのみを検索–

挿入時には適切な子テーブルに振り分け

親テーブル
（売上）

子テーブル
（１月の売上）子テーブル（２月の売上）・・・

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

子テーブル（１２月の売上）17

パーティショニング：従来の方法●

PostgreSQL のテーブル継承と制約を使って実現–

子テーブルは親テーブルを継承して作成–

検索時は「制約による除外」により、必要な子テーブルのみが検索される–

挿入時はトリガー関数でレコードを振り分ける
INSERT トリガー
親テーブル
（売上）

継承

子テーブル
（１月の売上）CHECK制約

子テーブル（２月の売上）・・・

CHECK制約
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

子テーブル（１２月の売上）CHECK制約18

パーティショニング：従来の方法
親テーブルを作成して●親テーブルを継承する子テーブルを作成して●●子テーブル全部に CHECK 制約を作成して●親テーブルに INSERT トリガーを作成して⇒手順が煩雑
トリガーが遅い

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

19

宣言的パーティショニング●

CREATE TABLE 文でパーティショニングが構築可能に–

振り分けルールは２種類
リスト： 子テーブルにパーティションキーのリストを指定●
レンジ： 子テーブルにパーティションキーの範囲を指定●●

ALTER TABLE 文で、子テーブルの追加／除去が可能●

階層的なパーティショニングも可能●

子テーブルに外部サーバのテーブル（FDW)を使用可能●

INSERT の高速化●

テーブル継承の機能を使用して実装されている
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

20

宣言的パーティショニング
挿入速度の違いパーティションテーブルに 20 万行を挿入
2000
1800
1600
1400ms

1200
1000
800
6004002000
継承とトリガを利用

PARTITION 構文を利用

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

21

パーティショニング構築（レンジパーティショニング）
●

●

親テーブル
–

パーティションキーには複数のカラムも指定可能–

もし NOT NULL 制約がなかったら、自動付与される

子テーブル
–

CREATE TABLE measurement (logdate
date not null,peaktempint,unitsalesint) PARTITION BY RANGE (logdate);

範囲が重なるパーティションが存在していてはいけない

CREATE TABLE measurement_y2016m07
PARTITION OF measurement (unitsales DEFAULT 0) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

22

パーティショニング構築（リストパーティショニング）●親テーブル–●

パーティションキーは１つのカラムまたは式

子テーブル

CREATE TABLE cities (city_idbigserial not null,
nametext not null,
population bigint) PARTITION BY LIST (left(lower(name), 1));–

格納するキー値のリストを指定する–

キーが NULL のデータを格納できる子テーブルは１つのみ

CREATE TABLE cities_ab
PARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id != 0)) FOR VALUES IN ('a', 'b');
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

23

パーティションの追加／削除●

パーティションの追加 (ATTACH)–

あらかじめ子テーブルは作成しておく–

キー値の条件に合わないデータが入っているとエラー

ALTER TABLE cities
ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b')●

パーティションの削除 (DETACH)

ALTER TABLE measurement
DETACH PARTITION measurement_y2015m12;

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

24今はできないこと●

親テーブルへのインデックス付与 （グローバルインデックス）–●

全子テーブルを対象にした一つのインデックス

デフォルトパーティション–

振り分け条件に合うパーティションが「無かった」場合の振り分け先●

UPDATE でパーティションキーが変更されたときの、パーティション間自動移動●

外部テーブルへの振り分け●

事後のパーティションの分割およびマージ–●

子テーブルを親テーブルから切り離し、新しいパーティションを作って、データの再投入、が必要

他にも多数
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

25

パラレルクエリ改善

参考資料：
PGCon2017
Parallel Query v2
The herd of elephants we unleashed claims new territory
Rafia Sabih, Robert Haas
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

26

パラレルクエリ(PostgreSQL 9.6〜）●

大きなテーブルに対するクエリを複数のCPUで実行することでパフォーマンス向上
workerworkerテーブル

Gather

結果それぞれの結果を連結
worker

複数のプロセスで並行にテーブルをスキャン
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

27

PostgreSQL 9.6 までのパラレルクエリ●

Scan– Sequential scan●

JOIN– Nested Loop JoinGather
Workers Planned: 4Workers Launched: 4-> Parallel Seq Scan

– Hash Join
●集約Finalize HashAggregate-> Gather
Workers Planned: 4-> Partial HashAggregate-> Parallel Seq Scan

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

28

PostgreSQL 10 のパラレルクエリ●Scan– Sequential scan– btree Index scan
– Index only scanFinalize Aggregate-> Gather
Workers Planned: 4-> Partial Aggregate-> Parallel Index Scan

– Bitmap Heap Scan●

●Bitmap Index Scan はまだJoin– Nested Loop join
– Hash join
– Merge join9.6 では未対応だったクエリもパラレルクエリの恩恵を得られるように
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

29

PostgreSQL 10 のパラレルクエリ●

Gather Merge–

複数のワーカが生成したソート済の行集合を、１つのソート済集合にまとめる
大きなソートを小さいソートに分割し並列実行●
Parallel btree Index Scan, Parallel Merge Join と相性がよい●

Finalize GroupAggregate-> Gather Merge
Workers Planned: 4-> Partial GroupAggregate-> Sort-> Parallel Seq Scan on tenk1●

非相関サブクエリのパラレル実行●

手続き言語内でのパラレルクエリ

9.6 では未対応だったクエリもパラレルクエリの恩恵を得られるように

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

30その他の新機能

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

31その他の PostgreSQL 10 新機能●

SCRAM認証–●

md5 より一層セキュアなパスワード認証方法

Hash index–

WAL ログ出力するようになった●

永続的、レプリケーション可能–

性能改善、サイズ抑制●
FDW で外部サーバに集約を push down–●

可能な限り、外部サーバ側で集約計算させてから行を取得する

Transition Table （遷移表）–

AFTER TRIGGER の中で「クエリに影響を受けた全ての行」にアクセスできるようになった
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

32その他の PostgreSQL 10 新機能●

プランナによる取得行数の推定–

今までは、それぞれのカラムで値の分布が独立であることを想定–

しかし、カラム値に間に相関のある場合には、推定を誤って悪い計画になりがちだった
CREATE TABLE t (a INT, b INT);
INSERT INTO t
SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i);ANALYZE t;

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
QUERY PLAN----------------------------------------------------------------------------Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=100 loops=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 9900

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

33

その他の PostgreSQL 10 新機能●●

ANALYZE で複数カラムに関する統計情報を取得可能になった–カラム間の相関係数（関数従属性）–「カラムの組み合わせ」に現れる個別値の数調べるカラムは「拡張統計（extended statistics)」を作成するときに指定–

以下は、関数従属性（dependencies）の統計情報を取得する例

CREATE STATISTICS stts (dependencies) ON a, b FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
QUERY PLAN------------------------------------------------------------------------------Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 9900

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

34

まとめ
●

PostgreSQL 10– ロジカルレプリケーション

まだまだ登場したばかり●
これからの運用・応用しだいで可能性は広がる●– パーティショニング

構文ができて構築が楽になった・・・だけではなく●巨大データ処理の基盤の１つとして他の機能と連携●– パラレルクエリ改善●

この先も、並列化できる領域がどんどん拡張
Copyright © 2017 SRA OSS, Inc. Japan All rights reserved.

35

Thank you

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

36