PostgreSQL 12の話

Copyright©2019 NTT Corp. All Rights Reserved.
PostgreSQL 12の話
NTT OSSセンタ
澤田雅彦
Middlewares Deep Talks @AWS Loft Tokyo

2Copyright©2019 NTT Corp. All Rights Reserved.
約170個の新機能（リリースノートより）
正式版は今年秋頃にリリース予定
最近の新バージョンと目玉機能
9.6 : パラレルクエリ、Vacuum改善
10 : 宣言的テーブルパーティショニング、ロジカルレプリケーション
11 : テーブルパーティショニングの強化、パラレルクエリの強化、SQLスト
アド・プロシージャ
PostgreSQL 12 betaが5/23にリリース

• テーブル・パーティショニングの改善
• CTE Inlining
• Pluggable Storage Engine
• Chained Transaction
• JSON/Path
• Generated Columns (STOREDのみ)
• 拡張統計情報の強化（MCVリスト）
• REINDEX CONCURRENTLY
• CREATE INDEX、REINDEX、VACUUM FULL、CLUSTERのモ
ニタリング
新機能いろいろ(今日は下線の機能を紹介)

PostgreSQL 12でもテーブル・パーティショニングの改善は多い（全
体の約15%、PG11もほぼ同じだった）
• パーティション・プルーニングの性能改善
• パーティション・テーブルへの外部キー制約
• パーティション・テーブルへのCOPYの性能改善
• ATTACH PARTITIONのロックが弱くなった
• AccessExclusiveLock -> ShareUpdateExclusiveLock
• pg_partition_root(), pg_partition_ancestors(), pg_partition_root()
• パーティション境界に式を指定（テーブル作成時に評価）
テーブル・パーティショニングの改善

パーティション・プルーニングの問題
PostgreSQL11の新機能紹介より
PostgreSQL10に
比べて早くなっている。
けど、まだプラン生成
時間が子テーブル数に
比例して伸びている

• これまで、プランナは内部で使用するテーブルの管理情報（
RangeTblEntry, RelOptInfo)を全ての子テーブルについて作成してた。
• PG12では、プルーニング情報を求めた後に興味のある子テーブルだけの
管理情報を作成するようにした。
• 数千規模の子テーブルがある場合に有用
子テーブルが多い時のプラン生成時間が大幅に改善
0
20
40
60
80
100
120
50 100 500 1000 2000 5000 10000
実行計画作成時間(MS)
Range Partitioning
PG12 PG11 PG10

• WITH句で指定したクエリ結果を一時テーブルみたいな形で扱うこと
ができる
• 複雑なSQLをより単純な部品に分解することで、SQLを読みやすくできる
• 再帰SQLのときにも使う
• PostgreSQLの実装では常にCTEの結果を一時テーブルとして持つ
• work_memを使用
• クエリの意味は同じでもsubselectを使った時とは実行計画を異なる
• MySQLも8.0からサポート
Common Table Expression (CTE)

-- CTEを利用(PG11まで)
=# EXPLAIN
WITH test AS (SELECT * FROM a WHERE c = 1)
SELECT * FROM test WHERE d = 1;
QUERY PLAN
------------------------------------------------------------------------
CTE Scan on test (cost=8.17..8.20 rows=1 width=8)
Filter: (d = 1)
CTE test
-> Index Scan using a_pkey on a (cost=0.15..8.17 rows=1 width=8)
Index Cond: (c = 1)
(5 rows)
CTEとSubSelect - PostgreSQL 11まで -
-- SubSelectを利用
=# EXPLAIN
SELECT * FROM (SELECT * FROM a WHERE c = 1 and d = 1) as test;
QUERY PLAN
----------------------------------------------------------------
Index Scan using a_pkey on a (cost=0.15..8.17 rows=1 width=8)
Index Cond: (c = 1)
Filter: (d = 1)
(3 rows)

-- CTEを利用(PG12)
=# EXPLAIN
WITH test AS (SELECT * FROM a WHERE c = 1)
QUERY PLAN
----------------------------------------------------------------------------------
Index Cond: (c = 1)
Filter: (d = 1)
(3 rows)
CTEとSubSelect - PostgreSQL 12から -
-- SubSelectを利用
=# EXPLAIN
SELECT * FROM (SELECT * FROM a WHERE c = 1 and d = 1) as test;
QUERY PLAN
----------------------------------------------------------------
Index Cond: (c = 1)
Filter: (d = 1)
(3 rows)

• デフォルトでCTEのクエリを展開するようになった(CTE inlining)
• 他のRDBMSもこっちの動作がデフォルトになっていると思う
• ただし再帰SQL、SELECT以外、volatile関数が入っている場合は強制的
にMATERIALIZE（一時テーブル的な扱い）される
• 明示的にMATERIALIZED句をつけると、PostgeSQL10までと同じ動
作になる
• WITH句を使って結合順序を変えたり、SQLチューニングする時は
MATERIALIZED句を使っても良さそう
PostgreSQL 12以降のCTE
=# EXPLAIN
WITH test AS MATERIALIZED (SELECT * FROM a WHERE c = 1)
QUERY PLAN
------------------------------------------------------------------------
CTE Scan on test (cost=8.17..8.20 rows=1 width=8)
Filter: (d = 1)
CTE test
-> Index Scan using a_pkey on a (cost=0.15..8.17 rows=1 width=8)
Index Cond: (c = 1)
(5 rows)

• 独自のテーブル（Access Method）を定義、使用できる
ようになった
• これまではインデックスでは可能だった
• 夢が広がる機能
• 例えば、
• 追記型以外を採用したテーブル
• 列指向テーブル
• インメモリテーブル
• 他のDBとの連携（RocksDB + PostgreSQL = PgRocks?）
Pluggable Storage Engine(Table AM)

Table AMの概要
https://anarazel.de/talks/2018-10-25-pgconfeu-pluggable-storage/pluggable.pdf

• Heapのみ
• 従来のテーブル
• PostgreSQL 12では、インタフェースが切られただけで
、新しい種類のテーブルは提供されていない
• 新しい種類のテーブルはPostgreSQL 13以降
サポート中のAccess Method

• ZHeap (EnterpriseDB社)
• 行指向
• UNDOログ
• タプルヘッダが小さい(Heapに比べて)
• ZedStore (Pivotal社)
• 列指向
• UNDOログ
• 圧縮
• 分析用途等に適しているが、最大限効果を出すためには
Optimizer、Executorの改良も必要
• In-memory storage
どんなAccess Methodが今後入るか？

• UNDOログを利用
• 古いタプルはすべてUNDO領域に退避
• Commitなら退避した古いタプルを消す、Rollbackならもとに戻す
• In-place update
• VACUUMがいらない
• タプルヘッダが従来のテーブルよりも小さい
• 25 bytes -> 5 bytes
• MVCCに必要なデータはUNDOログにある
• ソースは公開されているので試してみるのもあり
• https://github.com/EnterpriseDB/zheap
ZHeap

目的は異なるが機能性は一部重複している
• 目的の違い
• FDWは外部データとの連携が目的
• SQL標準にあるSQL/MEDの実装系
• Table AMはPostgreSQLのストレージ層の切り出し
• 機能の違い
• FDWでは、実行計画の作成（外部サーバが担当する部分の切り
出し等）、外部サーバからのデータ取得を担当
• GetForeignPaths、GetForeignJoinPath, ExecForeignScanなど
• Table AMは、Executor⇔ストレージのI/Fのみ（今後変わる可能
性あり）
• scan_getnextslot, index_fetch_begin, scan_bitmap_next_block,
tuple_insert, tuple_delete, scan_bitmapなど
• FDWではDDLに対応していない
Foreign Data Wrapperとの違い

COMMIT [ AND [NO] CHAIN ]
ROLLBACK [ AND [NO] CHAIN ]
• 「AND CHAIN」を指定すると、トランザクション分離レベル、
READ_WRITE/READ_ONLY、DEFERRABLEが引き継がれて、新しいト
ランザクションが開始される
• SQL標準の機能(“Chained Transaction”)
• Stored Procedure内でトランザクションの設定を変えて頻繁にコミ
ットをする時に便利
• MySQLもサポートしてる
Chained Transaction

=# SHOW transaction_isolation ; --デフォルトはread committed
transaction_isolation
-----------------------
read committed
(1 row)
=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ; -- serializableでトランザクションを開始
BEGIN
=# SHOW transaction_isolation ; -- serializableになっていることを確認
-----------------------
serializable
(1 row)
=# COMMIT AND CHAIN; -- AND CHAINを指定してCOMMIT
COMMIT
=# SHOW transaction_isolation ; -- トランザクション分離レベルが引き継がれてserializableのまま
-----------------------
serializable
(1 row)
=# COMMIT; -- 普通のCOMMIT
COMMIT
=# SHOW transaction_isolation ; -- read committedに戻っている
-----------------------
read committed
(1 row)
実行例

• CHAINする前後のトランザクションに特別な関係性はない
• いくつかの設定が引き継がれるだけ
• Jim Grayのトランザクション本では、Chained Transactionはカーソルを開いた
ままにすると解説している
注意点
Transaction Processing: Concepts and
Techniques, Chained Transactions(P193)

• XPathのJSON版
• JSONデータから特定の部分を指定し、抽出するための
構文（言語）
• jsonb型のデータに対してのみ利用可能
• json型には対応していない
• SELECT jsonb_path_query(event_jb, ‘$.event.speakers[*]’)
SQL/JSON path

{ "event": {
"name": "Middleware Deep Talks",
"speakers": [
{ "name": "yoku0825",
"title": "ぼくらが8.0に至ったみちのり（踏破）"
},
{ "name": "澤田雅彦",
"title": "PostgreSQL 12の話"
},
{ "name": "Jun Ohtani",
"title": "What''s new Elastic Stack 7.0?"
},
{ "name": "星井祥吾",
"title": "Apache Cassandraの特性と自動化運用"
},
{ "name": "Noritaka Sekiyama",
"title": "S3整合性モデルとHadoop/Sparkの話"
}
],
"DDTL": {
"date" : "2019-05-23",
"time" : "18:00",
"place" : "AWS Loft Tokyo"
}
}
}

{ "event": {
"speakers": [
{ "name": "yoku0825",
},
},
},
},
}
],
"DDTL": {
"date" : "2019-05-23",
"time" : "18:00",
}
}
}
Query : $.event.name

{ "event": {
"speakers": [
{ "name": "yoku0825",
},
},
},
},
}
],
"DDTL": {
"date" : "2019-05-23",
"time" : "18:00",
}
}
}
Query : $.event.speakers

{ "event": {
"speakers": [
{ "name": "yoku0825",
},
},
},
},
}
],
"DDTL": {
"date" : "2019-05-23",
"time" : "18:00",
}
}
}
Query : $.event.speakers[1]

{ "event": {
"speakers": [
{ "name": "yoku0825",
},
},
},
},
}
],
"DDTL": {
"date" : "2019-05-23",
"time" : "18:00",
}
}
}
Query : $.event.speakers[1].title

{ "event": {
"speakers": [
{ "name": "yoku0825",
},
},
},
},
}
],
"DDTL": {
"date" : "2019-05-23",
"time" : "18:00",
}
}
}
Query : $.event.DDTL.*

{ "event": {
"speakers": [
{ "name": "yoku0825",
},
},
},
},
}
],
"DDTL": {
"date" : "2019-05-23",
"time" : "18:00",
}
}
}
Query : $.event.speakers[*] ? (@.name = “yoku0825”)

{ "event": {
"speakers": [
{ "name": "yoku0825",
},
},
},
},
}
],
"DDTL": {
"date" : "2019-05-23",
"time" : "18:00",
}
}
}
Query : $.event.speakers[*] ? (@.name start with “澤田”)

• PostgreSQL 12は今秋リリース予定
• 例年5月にBeta、7,8月くらいにRCで9月正式リリース
• リリースノートは公開されているので、追加された機能
を確認できます
• PostgreSQL公式ドキュメント（英語）にあります
• 正式リリースされるバージョンと大きく変わることはないはず
• 来週はPGCon@Ottawaが開催されます
最後に

Vacuum、Replication slotでいくつか機能開発をしました
• インデックスのVacuumをスキップする機能
• Vacuumの高速化（Vacuum処理時間の大半はインデックスの
Vacuumなので、それをスキップできるようにした）
• Replication Slotをコピーする機能
• Logical Replicationやバックアップにも役に立ちます
その他、分散Tx機能(Atomic Commit)、透過的暗号化機能
(TDE)にも取り組んでいるので、興味があれば話しましょ
う！
YOUはなにかコントリビュートしてないの？

• JSONPath - Xpath for JSON
• https://goessner.net/articles/JsonPath/
• zheap: less bloat, fewer writes, and just plain smaller
• https://www.pgcon.org/2018/schedule/attachments/501_zheap-
a-new-storage-format-postgresql-5.pdf
• PostgreSQL 12
• http://www.sai.msu.su/~megera/postgres/talks/pg12-stachka-
2019.pdf
• Pluggable Storage in PostgreSQL
• https://anarazel.de/talks/2018-10-25-pgconfeu-pluggable-
storage/pluggable.pdf
参考資料

Thank you

PostgreSQL 12の話

More Related Content

What's hot

Similar to PostgreSQL 12の話

More from Masahiko Sawada

PostgreSQL 12の話