Post on 16-Apr-2017
中国地方中国地方 DBDB 勉強会勉強会 20162016(2017-04-08)(2017-04-08)
PostgreSQL 10PostgreSQL 10 ががやってくる!やってくる!
ぬこ@横浜ぬこ@横浜 (@nuko_yokohama)(@nuko_yokohama)
2
自己紹介「 PostgreSQL ラーメン」
でググってください。
ねぎ中華@山富士ねぎ中華@山富士
3
日本の PostgreSQL コミュニティ内で自称ゆる枠担当やってる
PostgreSQL 好きのフレンズです。
4
今日は JPUG 枠として中国ちほー DB 勉強会へ
参加することとなりました。
PostgreSQL のくだらない使い方を日々考えています
https://github.com/nuko-yokohama/pg_reversi
5
今日は JPUG 枠として中国ちほー DB 勉強会へ
参加しました。
6
目次PostgreSQL の概要と歴史PostgreSQL 10 の新機能
Logical ReplicationDelective Partition
Others
7
PostgreSQL の概要と歴史
8
MySQL と並ぶ OSS DBMSライセンスは BSD ライクなもの高度なクエリにも対応性能面でも商用 DBMS とも遜色なし9.0 以降はレプリケーションも対応非常に高い拡張性活発な開発コミュニティ
9
9.0レプリケーション
ストアド改良列トリガ排他制約
64bit Windows 対応
9.1同期レプリケーション
外部データラッパ (FDW)WITH 句+更新文
UNLOGGED TABLE
9.2パフォーマンス向上
カスケードレプリケーションIndex Only Scan範囲型サポート
JSON 型サポート
9.3マテリアライズドビュー
更新可能ビュー更新 FDW
JSON 型の機能拡張
エンタープライズ向け用途エンタープライズ向け用途実験的な高度な機能実験的な高度な機能
2010年
2011年
2012年
2013年
PostgreSQL の歴史( 9.0 ~)
レプリケーションが追加されてからもう 5 年以上になったのだなあ
End of Life ←
10
9.4JSONB
ALTER SYSTEMマテビュー改善
ロジカル・デコーディングWAL バッファ並列挿入
9.5BRIN
UPSERTRow Level Security
Import foreign schemapg_rewind
9.6
パラレルクエリ複数同期スタンバイサーバ
フレーズ検索postgres_fdw 改善もちろん性能改善も
9.4, 9.5, 9.69.4, 9.5, 9.6 と順調に成長と順調に成長そして・・・そして・・・
2014年
2015年
2016年
PostgreSQL の歴史( 9.4 ~)
9.6 で待望のパラレルクエリが!
11
そしてそしてPostgreSQL 10!PostgreSQL 10!
12
すごくどうでもいいけど、PostgreSQL 10 って
「ぽすぐれてん」なのか「ぽすぐれじゅう」なのか。
Oracleの場合、“ ”おらくるテン なのか、“ ”おらくるじゅう なのか
13
PostgreSQL 10( 開発中 )
開発自体は昨年から開始数回の commitfest で取り込む機能を議論
2016/09, 11 commit fest2017/01, 03 commit fest2017/05 beta release?
・・・2017/09 PostgreSQL 10 リリース予定?
14
PostgreSQL 10 のリリース情報https://twitter.com/sawada_masahiko/status/844933525807640576
なので、6月くらいに呼んでもらえると
スケジュール的に一番良かったりします。
15
PostgreSQL 10 の新機能 (予定 )
まだ beta版も出ていない状況なので細かい仕様等は今後、変更される
可能性はあります。
16
Logical ReplicationDeclarative PartitioningOthers...
PostgreSQL 10 主な新機能
17
Logical Replication
18
Physical と Logical複製(物理)と複製(論理)物理削除と論理削除物理休暇と論理休暇マジカル (物理 ) とロジカル ( 論理 )
Logical Replication
19
Physical ReplicationLog ShippingStreaming Replication(9.0-)WAL の物理コピーを転送転送された WAL を適用データベースクラスタ全体が対象
Logical Replication
20
Logical Replication外部レプリケーション製品pgpool-II, Slony-I
Logical Decoding(9.4-)Bi-Direction Replication
Logical Replication(10-)
Logical Replication
21
Logical Decoding のイメージ
Logical Replication
Replication Slot
WAL
テーブル
変換プラグインユーザ定義
論理ログ
論理ログの適用プログラム
PostgreSQL/その他 DBMS
更新
プラグイン開発 /インストール
22
Logical Replication のイメージ
Logical Replication
Replication Slot
WAL
テーブル
レプリケーション用標準プラグイン
(pgoutput)
論理ログ
サブスクライバ
PostgreSQL
更新
PUBLICATION定義
SUBSCRIPTION定義
Logical Decoding の基盤を使って、PostgreSQL 間のレプリケーション専用に使いやすくした、というイメージ
23
Logical Replication の基本パターン 1あるテーブルのみを複製する
Logical Replication
PostgreSQL server (srv2) (port=5433)
tokyo DBkanagawa DB
machida table machida table
SUBSCRIPTIONsub_kanagawa_machida
PUBLICATIONpub_kanagawa_machida
client
PostgreSQL server (srv1) (port=5432)
ReplicationProtocol
yokohama table
接続
24
大まかな手順postgresql.conf の設定pg_hba.conf の設定データベース / テーブルの作成Logical Replication の設定
Logical Replication
25
postgresql.conf の設定wal_level = logicalmax_wal_senders = 3 # 0 より大きい数max_replication_slots = 3 # 0より大きい数
PUBLICATION を設定する上流側サーバのみ設定しておけば OK.
Logical Replication
26
pg_hba.conf の設定通常の Streaming Replication 用設定と同じで OK.レプリケーション専用ユーザを使う場合には、別途レプリケーション権限をもつユーザを作成しておく必要がある。
Logical Replication
# Allow replication connections from localhost, by a user with the# replication privilege.
local replication postgres trust
この例では postgresユーザで接続してるけど、本当は
レプリケーション専用ユーザを作る方がいいです。
27
データベース / テーブルの作成Logical Replication はデータベース単位に設定する(重要!)複製先に Logical Replication 対象のテーブルを持つデータベースがなければ作成する。
Logical Replication
28
Logical Replication の設定複製元サーバ
複製したい対象のテーブルを選んで、 CREATE PUBLICATION を実行する。デフォルトでは、挿入 / 更新 / 削除の全更新種別の情報が複製される。明示的に複製させない更新種別を指定可能。
複製先サーバPUBLICATION が存在するデータベースへ接続し、更新情報を受信する CREATE SUBSCRIPTION を実行する。
Logical Replication
29
Logical Replication の SQL コマンドCREATE/ALTER/DROP PUBLICATIONCREATE/ALTER/DROP SUBSCRIPTION
Logical Replication
30
CREATE PUBLICATION syntaxhttps://www.postgresql.org/docs/devel/static/sql-createpublication.html
Logical Replication
CREATE PUBLICATION name [ FOR TABLE table_name [, ...] | FOR ALL TABLES ] [ WITH ( option [, ... ] ) ]
where option can be:
PUBLISH INSERT | NOPUBLISH INSERT | PUBLISH UPDATE | NOPUBLISH UPDATE | PUBLISH DELETE | NOPUBLISH DELETE
31
CREATE SUBSCRIPTION syntaxhttps://www.postgresql.org/docs/devel/static/sql-createsubscription.html
Logical Replication
CREATE SUBSCRIPTION subscription_name CONNECTION 'conninfo' PUBLICATION { publication_name [, ...] } [ WITH ( option [, ... ] ) ]
where option can be:
| ENABLED | DISABLED | CREATE SLOT | NOCREATE SLOT | SLOT NAME = slot_name | COPY DATA | NOCOPY DATA | NOCONNECT
32
Logical Replication の設定例テーブルの作成
Logical Replication
psql -p 5432 -U postgres kanagawa -c "CREATE TABLE machida(id int primary key, pref text, data text)"psql -p 5432 -U postgres kanagawa -c "CREATE TABLE yokohama(id int primary key, pref text, data text)"psql -p 5433 -U postgres tokyo -c "CREATE TABLE machida(id int primary key, pref text, data text)"
PUBLICATION を srv1 の kanagawa DB に作成対象は machida テーブルのみ
psql -p 5432 -U postgres kanagawa -c "CREATE PUBLICATION pub_kanagawa_machida FOR TABLE machida"
SUBSCRIPTION を srv2 の tokyo DB に作成psql -p 5432 -U postgres tokyo -c "CREATE SUBSCRIPTION sub_kanagawa_machida CONNECTION 'dbname=kanagawa port=5432 user=postgres' PUBLICATION pub_kanagawa_machida"
33
Logical Replication の基本パターン 1(再掲)
Logical Replication
PostgreSQL server (srv2) (port=5433)
tokyo DBkanagawa DB
machida table machida table
SUBSCRIPTIONsub_kanagawa_machida
PUBLICATIONpub_kanagawa_machida
client
PostgreSQL server (srv1) (port=5432)
ReplicationProtocol
yokohama table
“ ”パブリッシャー と呼ぶ “ ”サブスクライバ と呼ぶ
接続
34
設定の確認方法srv1 の kanagawa DB に接続。psql メタコマンド \dRp で PUBLICATION を確認
Logical Replication
psql -p 5432 -U postgres kanagawa -c "\dRp" List of publications Name | Owner | Inserts | Updates | Deletes----------------------+----------+---------+---------+--------- pub_kanagawa_machida | postgres | t | t | t
srv2 の tokyo DB に接続psql メタコマンド \dRs で SUBSCRIPTION を確認
psql -p 5433 -U postgres tokyo -c "\dRs" List of subscriptions Name | Owner | Enabled | Publication----------------------+----------+---------+------------------------ sub_kanagawa_machida | postgres | t | {pub_kanagawa_machida}
35
レプリケーションの例 ( 挿入 )パブリッシャー (srv1) の kanagawa DB にログインmachida テーブルに挿入し、直後に検索
Logical Replication
psql -p 5432 -U postgres kanagawa -c "INSERT INTO machida VALUES (1, 'kanagawa', 'Naruse'),(2, 'kanagawa', 'Machida')"INSERT 0 2psql -p 5433 -U postgres tokyo -c "TABLE machida" id | pref | data----+----------+--------- 1 | kanagawa | Naruse 2 | kanagawa | Machida
サブスクライバ (srv2) の tokyo DB に接続machida テーブルに挿入内容が伝播している。
psql -p 5433 -U postgres tokyo -c "TABLE machida" id | pref | data----+----------+--------- 1 | kanagawa | Naruse 2 | kanagawa | Machida
36
レプリケーションの例 ( 更新と削除 )パブリッシャー (srv1) の kanagawa DB にログインmachida テーブルへ更新 / 削除して、直後に検索
Logical Replication
psql -p 5432 -U postgres kanagawa -c "UPDATE machida SET data = 'Nyaruse' WHERE id = 1"UPDATE 1psql -p 5432 -U postgres kanagawa -c "DELETE FROM machida WHERE id = 2"DELETE 1psql -p 5432 -U postgres kanagawa -c "TABLE machida" id | pref | data----+----------+--------- 1 | kanagawa | Nyaruse
サブスクライバ (srv2) 側にも更新 / 削除内容が伝播している。
psql -p 5433 -U postgres tokyo -c "TABLE machida" id | pref | data----+----------+--------- 1 | kanagawa | Nyaruse
37
レプリケーションされない例パブリッシャー (srv1) の kanagawa DB にログインmachida テーブルを TRUNCATE し、直後に検索
Logical Replication
psql -p 5432 -U postgres kanagawa -c "TRUNCATE machida"TRUNCATE TABLEpsql -p 5432 -U postgres kanagawa -c "TABLE machida" id | pref | data----+------+------(0 rows)
サブスクライバ (srv2) 側は・・・TRUNCATE されていない!
psql -p 5433 -U postgres tokyo -c "TABLE machida" id | pref | data----+----------+--------- 1 | kanagawa | Nyaruse(1 row)
現状、 Logical Replication では TRUNCATE は未対応!
38
レプリケーションされないものTRUNCATE 文DDL(CREATE/ALTER/DROP 等 )VACUUM, ANALYZE などの運用系コマンド
Logical Replication は DML しか対応していないので要注意。
なお、 Streaming Replication は TRUNCATE も DDLも対応している。
Logical Replication
39
ロジカルレプリケーションのメタデータ
Logical Replication
40
パブリッシャー定義pg_publicationpg_publication_relpg_publication_tables (view)
サブスクライバ定義pg_subscriptionpg_subscription_rel
オリジン定義pg_replication_origin
Logical Replication
41
レプリケーション状態の情報pg_stat_replication (SUBSCRIPTION の状態表示)pg_stat_subscriptionpg_replication_origin_status
Logical Replication
42
Logical Replication における Conflict
Logical Replication
43
Logical Replication の Conflict
Logical Replication
PostgreSQL server (srv2) (port=5433)
tokyo DBkanagawa DB
machida table machida table
SUBSCRIPTIONsub_kanagawa_machida
PUBLICATIONpub_kanagawa_machida
client
PostgreSQL server (srv1) (port=5432)
ReplicationProtocol
yokohama table
“ ”パブリッシャー と呼ぶ “ ”サブスクライバ と呼ぶ
key=xのレコードを先に挿入
key=xのレコードを後で挿入
接続
44
INSERT の Conflict 例srv1 と srv2 の machida テーブルが同期している状態
Logical Replication
psql -p 5432 -U postgres kanagawa -c "TABLE machida" id | pref | data ----+----------+--------- 1 | kanagawa | Naruse 2 | kanagawa | Machida
この状態で srv2 の machida テーブルに id=3 のレコードを先に挿入しておく
psql -p 5433 -U postgres tokyo -c "TABLE machida" id | pref | data----+----------+--------- 1 | kanagawa | Naruse 2 | kanagawa | Machida
psql -p 5433 -U postgres tokyo -c "INSERT INTO machida VALUES (3, 'tokyo', 'Kobuchi')"INSERT 0 1
To be continued
45
INSERT の Conflict 例srv1 に id=3 のレコードを挿入すると・・・
Logical Replication
psql -p 5432 -U postgres kanagawa -c "TABLE machida" id | pref | data ----+----------+--------- 1 | kanagawa | Naruse 2 | kanagawa | Machida 3 | kanagawa | Kobuchi
この状態で srv1 と srv2 の machida テーブルを参照するとこうなる。
psql -p 5433 -U postgres tokyo -c "TABLE machida" id | pref | data ----+----------+--------- 1 | kanagawa | Naruse 2 | kanagawa | Machida 3 | tokyo | Kobuchi
psql -p 5432 -U postgres kanagawa -c "INSERT INTO machida VALUES (3, 'kanagawa', 'Kobuchi')"INSERT 0 1
psql -p 5432 -U postgres kanagawa -c "INSERT INTO machida VALUES (3, 'kanagawa', 'Kobuchi')"INSERT 0 1
ConfilictConfilict発生!発生!
46
INSERT の Conflict 発生時のログパブリッシャー側のサーバログ
Logical Replication
2017-04-01 15:05:25.470 JST [63830] LOG: logical decoding found consistent point at 0/172D2682017-04-01 15:05:25.470 JST [63830] DETAIL: There are no running transactions.2017-04-01 15:05:25.476 JST [63830] LOG: unexpected EOF on standby connection2017-04-01 15:05:30.490 JST [63833] LOG: starting logical decoding for slot "sub_kanagawa_machida"2017-04-01 15:05:30.490 JST [63833] DETAIL: streaming transactions committing after 0/172D2A0, reading WAL from 0/172D268
2017-04-01 15:05:25.475 JST [63829] ERROR: duplicate key value violates unique constraint "machida_pkey"2017-04-01 15:05:25.475 JST [63829] DETAIL: Key (id)=(3) already exists.2017-04-01 15:05:25.476 JST [1994] LOG: worker process: logical replication worker for subscription 16425 (PID 63829) exited with exit code 1
サブスクライバ側のサーバログ
47
Conflict が起きたときに、どうやって解決するのか。
Logical Replication
https://www.postgresql.org/docs/devel/static/logical-replication-conflicts.html
競合によってエラーが発生し、レプリケーションが停止します。 ユーザーが手動で解決する必要があります。競合の詳細は、サブスクライバのサーバログにあります。
マジすか・・・
ユーザーがユーザーが手動で解決する手動で解決する必要があります必要があります
!!!!!!
48
解決方法は 2 つある。
Logical Replication
https://www.postgresql.org/docs/devel/static/logical-replication-conflicts.html
この解決は、サブスクライバのデータを到着する変更と競合しないように修正するか、または既存のデータと競合するトランザクションをスキップすることによって行うことができます。
49
サブスクライバ側で解決する例srv2 側の id=3 のレコードを削除する。
Logical Replication
psql -p 5433 -U postgres tokyo -c "DELETE FROM machida WHERE id=3"DELETE 1
しばらくすると、 srv1側に挿入された id=3 レコードが反映される。
psql -p 5433 -U postgres tokyo -c "TABLE machida" id | pref | data----+----------+--------- 1 | kanagawa | Naruse 2 | kanagawa | Machida
psql -p 5433 -U postgres tokyo -c "TABLE machida" id | pref | data ----+----------+--------- 1 | kanagawa | Naruse 2 | kanagawa | Machida 3 | kanagawa | Kobuchi
削除直後に参照すると id=3 のレコードは消えているが
50
サブスクライバの更新を優先したいときは、どうすればいいの?
Logical Replication
パブリッシャー側は、パブリッシャーの更新が優先されるべきと主張している。しかしちょっと待って欲しい。パブリッシャーの更新が優先されるべきと主張するには早計に過ぎないか。パブリッシャーの真摯な姿勢が、今ひとつ伝わってこない。
例えばサブスクライバからはサブスクライバの更新が優先される場合もあるのではないかと主張するような声もある。このような声にパブリッシャーは謙虚に耳を傾けるべきではないか。
(天声人語風に)
51
PostgreSQL 文書にはこう書いてあるが・・・
Logical Replicationhttps://www.postgresql.org/docs/devel/static/logical-replication-conflicts.html
サブスクリプション名に対応する node_name と位置を指定してpg_replication_origin_advance() 関数を呼び出すと、トランザクションをスキップできます。 オリジンの現在の位置は、 pg_replication_origin_status システムビューに表示されます。
いきなり node_name とかオリジンとか言われても
わかんねーよ・・・弁当かよっ!?
と Slackに愚痴っていたら救いの神、降臨!
52
Logical Replication
ということでアドバイスに従って試してみよう。
53
サブスクライバの pg_replication_origin_status を確認
Logical Replicationtokyo=# TABLE pg_replication_origin_status; local_id | external_id | remote_lsn | local_lsn ----------+-------------+------------+----------- 1 | pg_16425 | 0/172CCE8 | 0/17532D8
パブリッシャーの pg_current_wal_location() を確認kanagawa=# SELECT pg_current_wal_location(); pg_current_wal_location ------------------------- 0/172CF28
サブスクライバ上で pg_replication_origin_advance() を実行第 1引数には external_id を指定第 2引数には pg_current_wal_location() の値を指定
tokyo=# SELECT pg_replication_origin_advance('pg_16425', '0/172CF28'); pg_replication_origin_advance -------------------------------
To be continued
54
Confilict 状態からは抜け出せるが、パブリッシャー側の更新もサブスクライブ側の更新もされたままである。
Logical Replication
kanagawa=# TABLE machida; id | pref | data ----+----------+--------- 1 | kanagawa | Naruse 2 | kanagawa | Machida 3 | kanagawa | Kobuchi
tokyo=# TABLE machida; id | pref | data ----+----------+--------- 1 | kanagawa | Naruse 3 | tokyo | Kobuchi 2 | kanagawa | Machida
あくまでも Conflict 状態でなくなった ( 以降のレプリケーションを受け付けるようになった)だけ!
パブリッシャー側で id=3 のレコードに UPDATE かければデータ上も同期はされる。
55
そもそも Conflict しているかってどう判断するのか?
Logical Replication
以下の SQL をパブリッシャーで発行する(たぶん)。
サブスクライバ側のサーバログに Conflict 原因のログが出力されている。
ERROR: duplicate key value violates unique constraint "machida_pkey"DETAIL: Key (id)=(3) already exists.
SELECT (SELECT confirmed_flush_lsn FROM pg_replication_slots) = pg_current_wal_location();
true なら Conflict していない。false なら Conflict している。
このやり方が正しいのか正直、まだわからぬ。
56
ここまでの検証からの所感現時点での Confilct 解消方式はイケてない。パブリッシャーとサブスクライバを監視する別サーバがないと自動 Conflict 解消って難しいかも?
Logical Replication
監視サーバ
サブスクライバパブリッシャー
レプリケーション
Confilict 検知WAL位置取得 Confilict解消
57
異なる構造間の Logical ReplicationLogical Replication は以下のようなテーブルの差異があっても複製は可能。列定義の順番が異なっていても OK。複製先テーブルには、複製元にない列があっても OK( null 可列の場合)ただし、複製テーブルには、複製元の列が全てなくてはいけない。ややこしい。
Logical Replication
58
異なる構造間の Logical Replication
Logical Replication
列 A 列 B 列 C
AAA BBB CCC
複製元
列 C 列 B 列 A
CCC BBB AAA
複製先
列 A 列 B 列 C
AAA BBB CCC
複製元
列 A 列 B 列 C 列 X
AAA BBB CCC (null)
複製先
列 A 列 B 列 C
AAA BBB CCC
複製元
列 A 列 B
AAA BBB
複製先
59
異文字コード間の Logical Replication昨日 (4/7?) に Fix されたみたい。バグレポした篠田さん@ HPE 、そして修正した堀口さん@NTT 、 GJ !この手の問題は英語圏の人は積極的に対応しない・・・。https://postgresql-jp.slack.com/archives/C0AM4GZC0/p1491547292443787
Logical Replication
Logical Replication の文字コード変換問題、修正モジュールがコミットされました。UTF-8から EUC_JP への変換も確認できました。堀口さん、ありがとうございました。
https://www.postgresql.org/message-id/20170407.100555.17227106.horiguchi.kyotaro@lab.ntt.co.jp
60
応用編マルチマスター構成異バージョン間複製異アーキテクチャ間複製
Logical Replication
61
マルチマスタ構成こういう構成なら conflict は発生しない。同一テーブル間のマルチマスタは難しそう・・・
Logical Replication
PostgreSQL server (srv2)
AAA DBAAA DB
XXX table XXX table
PostgreSQL server (srv1)
YYY table YYY table更新削除
更新削除
挿入
挿入
LogicalReplication
LogicalReplication
62
異バージョン間複製PostgreSQL 10 と PostgreSQL 11 間での複製
Logical Replication
PostgreSQL 11 server (srv2)
PG11 DB クラスタ
PG10 DB クラスタ
全 DB 全 DB
PostgreSQL 10 server (srv1)
更新操作
LogicalReplication
PG10 DB クラスタ
全 DB
PostgreSQL 10 server (srv2)
PhysicalReplication
pg_upgrade
63
異アーキテクチャ間複製Linux 版と Windows 版との間の複製
Logical Replication
PostgreSQL 10 serve
PG10 DB クラスタ
PG10 DB クラスタ
全 DB 全 DB
PostgreSQL 10 server
更新操作
LogicalReplication
64
まとめLogical Replication によって、一部のデータの複製が可能になる。DML による更新のみ対応。現状は Conflict 解消方法が面倒。マルチマスタ / 異バージョン間 / 異アーキテクチャ間の複製も可能になる( はず? ) 。
Logical Replication
65
Physical vs LogicalLogical Replication
観点 Physical Replication Logical Replication
複製範囲 データベースクラスタ全体 特定の DB/表も選択可能
複製対象の操作 DML(TRUNCATE 含む ),DDL
DML(TRUNCATE 除く )
同期方式 同期または非同期 非同期
用途 高可用化 部分的な複製異バージョン /異アーキテクチャ間複製
用途に合わせて選定しよう。(私見) Logical Replication は実装されたばかり。品質面でのリスクは覚悟しておくべきか。併存も可能だと思う(未検証)
66
Declarative Partitioning
67
従来の PostgreSQL パーティション構築親テーブル定義親テーブルを継承した子テーブル(+ CHECK 制約)を定義トリガ関数作成 (C 言語 or PL/pgSQL)トリガ定義職人芸
Declarative Partitioning
68
Oracle, DB2 などの商用 DBMS ではDDL 構文として、パーティションがサポートされていた。PostgreSQL の従来のパーティションはいわば「なんちゃってパーティション」
Declarative Partitioning
69
PostgreSQL 10 新機能CREATE TABLE 文の拡張によりパーティションテーブルの定義が可能に。INSERT トリガ定義不要に!しかも INSERT が高速化!制約矛盾もある程度チェック可能!
Declarative Partitioning
70
PostgreSQL 10 で対応するパーティションの種類レンジパーティションリストパーティション
対応しないパーティション種別ハッシュパーティション (議論中 )
Declarative Partitioning
71
CREATE TABLE 構文親テーブル側
Declarative Partitioning
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | U\NLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE source_table [ like_option ... ] } [, ... ]] )[ INHERITS ( parent_table [, ... ] ) ][ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ][ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ TABLESPACE tablespace_name ]
72
CREATE TABLE 構文子テーブル側
Declarative Partitioning
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name PARTITION OF parent_table [ ( { column_name [ column_constraint [ ... ] ] | table_constraint } [, ... ]) ] FOR VALUES partition_bound_spec[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ][ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ TABLESPACE tablespace_name ]
子テーブル側にも PARTITION BY が!多段パーティション定義が可能!
73
シンプルなリストパーティションの例Declarative Partitioning
japan
kanagawatokyo shizuoka
親テーブル japan と同じ構造のテーブル 3つ (tokyo, kanagawa, shizuoka) をパーテイションテーブルとする。
74
従来の定義方式親テーブルの定義。
Declarative Partitioning
CREATE TABLE japan ( pref text, city text, data text);
CREATE TABLE tokyo ( CHECK (pref IN ('東京 '))) INHERITS (japan);
CREATE TABLE kanagawa ( CHECK (pref IN ('神奈川 '))) INHERITS (japan);
CREATE TABLE shizuoka ( CHECK (pref IN ('静岡 '))) INHERITS (japan);
子テーブルの定義
To be continued
75
従来の定義方式(つづき)トリガ関数の定義
Declarative Partitioning
CREATE OR REPLACE FUNCTION pref_insert_trigger_func()RETURNS TRIGGER AS $$BEGIN IF ( NEW.pref = '東京 ') THEN INSERT INTO tokyo VALUES (NEW.*); ELSIF ( NEW.pref = '神奈川 ') THEN INSERT INTO kanagawa VALUES (NEW.*); ELSIF ( NEW.pref = '静岡 ') THEN INSERT INTO shizuoka VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the pref_insert_trigger() function!'; END IF; RETURN NULL;END;$$LANGUAGE plpgsql;
To be continued
76
従来の定義方式(つづき)トリガの定義
Declarative Partitioning
REATE TRIGGER pref_insert_trigger BEFORE INSERT ON japan FOR EACH ROW EXECUTE PROCEDURE pref_insert_trigger_func();
まとめテーブル定義だけでなく、トリガ関数定義・トリガ定義が必要になる。パーティション追加によりトリガ関数の変更が入りやすい。
77
面倒ですね
78
PostgreSQL 10 の定義方式親テーブルの定義。
Declarative Partitioning
CREATE TABLE japan ( pref text, city text, data text)PARTITION BY LIST (pref);
CREATE TABLE tokyo PARTITION OF japanFOR VALUES IN ('東京 ');
CREATE TABLE kanagawa PARTITION OF japanFOR VALUES IN ('神奈川 ');
CREATE TABLE shizuoka PARTITION OF japanFOR VALUES IN ('静岡 ');
子テーブルの定義
これだけで完了! ( レンジパーティションもだいたい同じ)
79
多段パーティションの例Declarative Partitioning
japan
kanagawatokyo shizuoka
kanagawa.machidakanagawa.yokohama kanagawa.kawasaki
「町田は神奈川。いいね?」「アッハイ」
80
多段パーティションの定義例親テーブルの定義。
Declarative Partitioning
CREATE TABLE japan ( pref text, city text, data text)PARTITION BY LIST (pref);
CREATE TABLE tokyo PARTITION OF japanFOR VALUES IN ('東京 ');
CREATE TABLE kanagawa PARTITION OF japanFOR VALUES IN ('神奈川 ')PARTITION BY LIST (city);
CREATE TABLE shizuoka PARTITION OF japanFOR VALUES IN ('静岡 ');
子テーブルの定義
これだけで完了!
CREATE TABLE "kanagawa.yokohama" PARTITION OF kanagawaFOR VALUES IN ('横浜 ');
CREATE TABLE "kanagawa.kawasaki" PARTITION OF kanagawaFOR VALUES IN ('川崎 ');
CREATE TABLE "kanagawa.machida" PARTITION OF kanagawaFOR VALUES IN ('町田 ');
81
パーティション状態の確認親テーブルの確認
Declarative Partitioning
CREATE TABLE japan ( pref tpartition=# \d+ japan Table "public.japan" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------+-----------+----------+---------+----------+--------------+------------- pref | text | | | | extended | | city | text | | | | extended | | data | text | | | | extended | | Partition key: LIST (pref)Partitions: kanagawa FOR VALUES IN ('神奈川 '), shizuoka FOR VALUES IN ('静岡 '), tokyo FOR VALUES IN ('東京 ')
パーティション種別とキーを表示してくれる。子テーブルとパーティションキー値を表示してくれる。
82
パーティション状態の確認子テーブルの確認
Declarative Partitioning
partition=# \d+ kanagawa Table "public.kanagawa" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------+-----------+----------+---------+----------+--------------+------------- pref | text | | | | extended | | city | text | | | | extended | | data | text | | | | extended | | Partition of: japan FOR VALUES IN ('神奈川 ')
所属する親テーブルと、パーティションキー値を表示してくれる。
83
パーティションキー重複のチェックDeclarative Partitioning
partition=# \d+ japan Table "public.japan" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------+-----------+----------+---------+----------+--------------+------------- pref | text | | | | extended | | city | text | | | | extended | | data | text | | | | extended | | Partition key: LIST (pref)Partitions: kanagawa FOR VALUES IN ('神奈川 '), shizuoka FOR VALUES IN ('静岡 '), tokyo FOR VALUES IN ('東京 ')
partition=# CREATE TABLE kanagawa2 PARTITION OF japan FOR VALUES IN (' 神奈川 ');ERROR: partition "kanagawa2" would overlap partition "kanagawa"
パーティションキーの重複を検知してエラーにする。
84
INSERT の高速化トリガ方式 /PostgreSQL 10 方式で構築したパーティション親テーブルに 100万件データを COPY
Declarative Partitioning
10倍以上の高速化! すごーい!
85
プルーニングパーティションキーを条件に使った場合に発動。条件に一致する一部のパーティションのみにアクセスすることで検索を効率化する。従来のトリガベース方式でも対応している。
Declarative Partitioning
86
多段パーティションの例Declarative Partitioning
japan
kanagawatokyo shizuoka
kanagawa.machidakanagawa.yokohama kanagawa.kawasaki
「町田は神奈川。いいね?」「アッハイ」
パーティションキーは都道府県 (pref)
パーティションキーは市 (city)
87
パーティションキーがない場合
Declarative Partitioning
partition=# EXPLAIN SELECT * FROM japan ; QUERY PLAN----------------------------------------------------------------------------- Append (cost=0.00..82.50 rows=3250 width=96) -> Seq Scan on tokyo (cost=0.00..16.50 rows=650 width=96) -> Seq Scan on shizuoka (cost=0.00..16.50 rows=650 width=96) -> Seq Scan on "kanagawa.yokohama" (cost=0.00..16.50 rows=650 width=96) -> Seq Scan on "kanagawa.kawasaki" (cost=0.00..16.50 rows=650 width=96) -> Seq Scan on "kanagawa.machida" (cost=0.00..16.50 rows=650 width=96)(6 rows)
パーティションキーがないので、全てのパーティションを検索する実行計画になる。Kanagawa テーブルに対する検索は、その子テーブルを合せた結果になる。
88
都道府県を条件に指定した場合
Declarative Partitioning
partition=# EXPLAIN SELECT * FROM japan WHERE pref = '神奈川 '; QUERY PLAN--------------------------------------------------------------------------- Append (cost=0.00..54.38 rows=9 width=96) -> Seq Scan on "kanagawa.yokohama" (cost=0.00..18.12 rows=3 width=96) Filter: (pref = '神奈川 '::text) -> Seq Scan on "kanagawa.kawasaki" (cost=0.00..18.12 rows=3 width=96) Filter: (pref = '神奈川 '::text) -> Seq Scan on "kanagawa.machida" (cost=0.00..18.12 rows=3 width=96) Filter: (pref = '神奈川 '::text)(7 rows)
pref が神奈川のデータが格納されている子テーブル(の配下の子テーブル)のみ検索する実行計画になる。
89
市を条件に指定した場合
Declarative Partitioning
partition=# EXPLAIN SELECT * FROM japan WHERE city = '町田 '; QUERY PLAN-------------------------------------------------------------------------- Append (cost=0.00..54.38 rows=9 width=96) -> Seq Scan on tokyo (cost=0.00..18.12 rows=3 width=96) Filter: (city = '町田 '::text) -> Seq Scan on shizuoka (cost=0.00..18.12 rows=3 width=96) Filter: (city = '町田 '::text) -> Seq Scan on "kanagawa.machida" (cost=0.00..18.12 rows=3 width=96) Filter: (city = '町田 '::text)(7 rows)
この場合は pref への条件がないので、全ての都道府県の子テーブルへアクセスする実行計画になる。kanagawa テーブルに対する検索は、市が町田に対する子テーブル“ kanagawa.machida” のみに限定される。
90
都道府県と市を条件に指定した場合
Declarative Partitioning
partition=# EXPLAIN SELECT * FROM japan WHERE pref = '神奈川 ' AND city = '町田 '; QUERY PLAN-------------------------------------------------------------------------- Append (cost=0.00..19.75 rows=1 width=96) -> Seq Scan on "kanagawa.machida" (cost=0.00..19.75 rows=1 width=96) Filter: ((pref = '神奈川 '::text) AND (city = '町田 '::text))(3 rows)
この場合は pref が神奈川、 city が町田の子テーブル(kanagawa.machida) のみアクセスする実行計画になる。
91
現状の制約パーティション間更新の制約インデックス関連の制約
Declarative Partitioning
92
パーティション間更新の制約パーティションキーを変更するような UPDATE はエラーになる。変更後の値のパーティションテーブルに挿入するわけではなく、元のパーティションテーブルに挿入しようとして、制約違反になる。
Declarative Partitioning
To be continued
partition=# SELECT * FROM japan WHERE id = 6; id | pref | city | data----+--------+------+-------- 6 | 神奈川 | 町田 | 竹の助(1 row)
partition=# UPDATE japan SET pref = '東京 ' WHERE id = 6;ERROR: new row for relation "kanagawa.machida" violates partition constraintDETAIL: Failing row contains (6, 東京 , 町田 , 竹の助 ).
93
パーティション間更新の制約代替手段として、 DELETE→INSERT する必要がある。
Declarative Partitioning
To be continued
partition=# SELECT * FROM japan WHERE id = 6; id | pref | city | data----+--------+------+-------- 6 | 神奈川 | 町田 | 竹の助(1 row)
partition=# BEGIN;BEGINpartition=# DELETE FROM japan WHERE id = 6;DELETE 1partition=# INSERT INTO japan VALUES (6, '東京 ', '町田 ', '竹の助 ');INSERT 0 1partition=# COMMIT;COMMITpartition=# SELECT * FROM japan WHERE id = 6; id | pref | city | data----+------+------+-------- 6 | 東京 | 町田 | 竹の助(1 row)
94
パーティション間更新の制約RETURNING 句を使って 1つのクエリにすることも可能
Declarative Partitioning
partition=# SELECT * FROM japan WHERE id = 6; id | pref | city | data ----+--------+------+-------- 6 | 神奈川 | 町田 | 竹の助(1 row)
partition=# WITH tmp AS (DELETE FROM japan WHERE id = 6 RETURNING id, '東京 ', city, data) INSERT INTO japan SELECT * FROM tmp;INSERT 0 1partition=# SELECT * FROM japan WHERE id = 6; id | pref | city | data ----+------+------+-------- 6 | 東京 | 町田 | 竹の助(1 row)
ぐぬぬ・・・町田は東京に奪われました
95
インデックス関連の制約パーティションの親テーブルには Primary key が設定できない!
Declarative Partitioning
partition=# CREATE TABLE japan ( id integer primary key, pref text, city text, data text)PARTITION BY LIST (pref);ERROR: primary key constraints are not supported on partitioned tablesLINE 2: id integer primary key, ^
To be continued
96
インデックス関連の制約子テーブルを持つテーブルにはインデックスが作成できない。
Declarative Partitioning
partition=# \d japan Table "public.japan" Column | Type | Collation | Nullable | Default--------+---------+-----------+----------+--------- id | integer | | | pref | text | | | city | text | | | data | text | | |Partition key: LIST (pref)Number of partitions: 3 (Use \d+ to list them.)
partition=# CREATE INDEX japan_id ON japan USING btree (id);ERROR: cannot create index on partitioned table "japan"
親テーブルにインデックス設定したら、子テーブルに自動的に伝播してくれればいいのになあ、とも思う・・・。
97
今後期待している機能パーティション機能+並列 FDW アクセス?
Declarative Partitioning
親テーブル
子テーブル子テーブル
子テーブル子テーブル
子テーブル子テーブル
子テーブル( FDW )
子テーブル( FDW )
子テーブル( FDW )
ここが並列動作すれば・・・
98
まとめCREATE TABLE 定義だけで簡単にパーティション化!INSERT も高速化された!PK, インデックスの扱いには注意
Declarative Partitioning
99
Others
100
Parallel Query の改善バージョン番号体系データベースクラスタ内の名称変更Othres...
Others
101
パラレルクエリ周りの改善項目MergeJoinIndexScanBitmap HeapScanサブクエリを含むクエリの対応pl/pgsql 関数を含むクエリの対応
パラレルクエリの改善
これらの改善により、どんなクエリでどのくらい性能改善効果が出てくるのかは、
こうした団体さんや企業さんが今後、試すんじゃないかと。
102
これまで (~ 9.6 )バージョン番号体系
X. Y. Z メジャーバージョン番号X が上がるときには、大きな機能追加があった場合( PITR, レプリケーションなど)
マイナーバージョン番号バグ Fix, セキュリティ Fix など
X. Zメジャーバージョン番号機能追加
マイナーバージョン番号バグ Fix, セキュリティ Fix など
これから ( 10 ~)
103
より実態にあった名前に変更pg_clog → pg_xactpg_xlog が pg_wal
この影響でユーティリティも名称変更があったりする。pg_resetxlog→pg_resetwalpg_xlogdump→pg_waldump旧名のバイナリも残ってはいる
サーバログ出力先のデフォルトが変更pg_log → log
DB クラスタ内の名称変更
pg_xlog と pg_log の名称が紛らわしいという
議論からこうなったみたい。
104
今のところは、 commitfest のページを見て commited の項目をリストアップするしかない・・・。5 月に PostgreSQL 10-beta がリリースされたら、リリースノートで確認できるはず。
Others...
105
commitfest のページをチラ見して、気になったものを挙げてみた。
Quorum commit for multiple synchronous replication 複数同期レプリケーションでの投票による昇格
pg_hba_rules viewpg_hba.conf が SQL で参照可能に!\if, \elseif, \else, \endif
また、 psql にいらん楽しそうな機能が!象神様が、俺にこの機能で遊べと言っている気がする。
もちろん、この他に色々な機能改善が含まれています!
Others...
106
おわりに
107
PostgreSQL 10 は新世代の PostgreSQL !
様々な可能性を秘めた Logical Replication使いやすい Delective Partition更に進化した Parallel Queryバージョン番号体系の変更
108
まだ正式リリース前なのでいろいろ動かして
バグレポートするとコミュニティの中の人も喜ぶと思います。
109
あなたと PostgreSQL 10,今すぐダウンロード
110
ご清聴ご清聴ありがとうありがとう
ございましたございました
111
PostgreSQL 10devel Documentationhttps://www.postgresql.org/docs/devel/static/index.htmlCommitfesthttps://commitfest.postgresql.org/Michael Paquier - Open source developer based in Japanhttp://michael.otacoo.com/@sawada_masahiko さんの Twitterhttps://twitter.com/sawada_masahikoPostgreSQL Update 2017 ( 高塚さん )https://www.slideshare.net/HarukaTakatsuka/postgre-sql-update20170310postgresql-jp Slackhttps://postgresql-hackers-jp.herokuapp.com/篠田さん、永安さん、大山さん、澤田さんの書き込みなどなど。ていうか、 Slack マジ有能。
参考にしたもの
112
私も微力ながらPostgreSQL 10 新機能に関する調査・検証結果を
Qiita で公開してます(最近、怠け気味ですが・・・ )
http://qiita.com/nuko_yokohama
113
Question?