PostgreSQL 15
最新情報解説2022年 10月 29日 11:00-11:45
SRA OSS LLC
陳 寧イ (mail: chen@sraoss.co.jp)

本講演について• 講演内容• PostgreSQL 15 について新機能や性能向上を解説• 動作検証結果を報告• 非互換の点についても解説PostgreSQL１５の概要

PostgreSQLとは• 多機能リレーショナルデータベースソフトウェア• 長い歴史• 1970年代 Ingres → 1980年代 POSTGRES → 1996年 PostgreSQL 6.0• BSDタイプの PostgreSQLライセンス• １社主導ではないオープンソースソフトウェア• 主要開発者や開発運営者が、各PostgreSQLビジネス企業に所属している形態

PostgreSQLのリリース履歴

リリース年月‘11.09‘10.09‘09.07‘08.02‘06.12

コード行数‘20.09 ‘21.09‘19.10‘18.10‘17.10‘16.09‘14.12 ’16.01‘13.09‘12.09JITコン
パイル‘05.11‘05.01

ウィンドウ関数

Windows版
PITR対応

バージョン

パラレル
クエリ

CPUスケール

物理レプリ
ケーション

JSONB

論理レプリ
ケーション

Btree
改良

PostgreSQL 15 の主なエンハンス• SQL機能追加• 性能改善• 外部ソートアルゴリズム変更• MERGE文サポート• ウィンドウ関数• 正規表現関数の追加• ¥copy FROM
• Zstandard圧縮サポート• 論理レプリケーション機能追加• 呼び出し元権限で実行するビュー• 運用管理• JSON形式ログ• モニタリングビュー・関数• 実行時統計情報の共有メモリ化• モジュールによるWALアーカイブとベースバックアップ

SQL機能追加

MERGE文• テーブルに対して、行または表データを照会して、値に応じた様々な更新を行うことができる SQL文• SQL標準構文

UPSERT方式

動作

WITH ..
まず UPDATEして、UPDATE ..
結果が 0行なら、RETURNING.. INSERT する。
INSERT• Oracle Database や SQL Server にも• いわゆる UPSERT機能を実現• 同キー行があれば UPDATE、同キー行が無ければ INSERT• 従来の UPSERT 方法• WITH .. UPDATE .. RETURNING ..
INSERT• INSERT .. ON CONFLICTINSERT ..
ON
CONFLICT

まず INSERTして、主キー衝突したなら、UPDATEする。

MERGE

まず 外部結合して、値を比較したうえで、INSERTしたり、UPDATEしたりする。

MERGE文

論理レプリケーション機能追加
様々な機能追加：• 行フィルタ• PUBLICATION に WHERE句を指定• 条件を満たす行だけレプリケーション• UPDATE して条件を満たす→ サブスクリプション側テーブルに行追加• UPDATE して条件から外れる → サブスクリプション側テーブルで行削除• 列フィルタ• PUBLICATION で列リストを指定• 列挙した列だけレプリケーション

論理レプリケーション機能追加• スキーマ単位で対象テーブルを一括指定• 従来はテーブル単位、データベース単位のみ• エラーで自動無効化• 制約違反などでサブスクリプション側で行変更の適用がエラーになる• するとエラーが繰り返され、後続の変更も適用されない• 自動的に SUBSCRIPTION を無効化する設定• レプリケーションは止まるが、エラー繰り返し発生は回避

論理レプリケーション機能追加• エラーを起こすトランザクションをスキップ

問題の変更データを含むトランザクションをスキップ。

SUBSCRIPTION を無効化していた場合は、有効化して、レプリケーションを再開。

エラーメッセージのLSN番号を指定すれば良い。

正規表現関数の拡充• POSIX正規表現のマッチや置換をする関数群• 似た機能の関数や演算子は従来からある• 商用DB製品と同じ書き方で提供

呼び出し元権限で実行するビュー• ビューの機能（１） 問い合わせに別名を付与して、リレーションとして利用可能に（２） 定義された問い合わせをビュー所有者の権限で実行• （１）だけ欲しい、（２）は不要、ということがある• テーブルに各ロール毎に細かな権限設定をしても、ビューを経由されると単一ロールからのアクセスになってしまう• ビューに新たな属性 security_invoker

性能改善外部ソートアルゴリズム変更• PostgreSQLのソート機能• ORDER BY や GROUP BY、DISTINCT、ウィンドウ関数、マージ結合で• 二つのソート方式
ソート方式

いつ使われるか？

アルゴリズム

インメモリソート

work_mem サイズ内のメモリで処理できる場合

クイックソート

外部ソート

より大規模なソートの場合、一時ファイルを使って実行

多層マージソート↓
平衡K-wayマージソート• 多層マージソート:
ソート用の領域を使いまわす技法（現代的でない）• 均衡K-wayマージソート： 小メモリで大データをソートする際に有利とされる

外部ソートアルゴリズム変更（ランダムデータ）

(ms)

(ms)

•work_mem が少ない時に V15 が優越の傾向•

ランダムデータ（左）よりも、整列済みデータ（右）の方が V15優越が明解

ウィンドウ関数のプラン改善• row_number(）、rank()、count() の性能向上• WindowAgg に「Run Condition」 実行方式が追加• V14 以前は WindowAgg 結果をすべて出したうえで、上位の Subquery Scan にて、これら関数の結果値でフィルタしていたウィンドウ関数のプラン改善

100万件データに対して実行した所要時間を比較（ソートはクイックソート）

psql の ¥copy FROM 性能改善
• 列の少ないテーブルの ¥copy FROM で効率改善• データを１行ずつ送出していた↓• 複数行をまとめて送出

本検証は最も効果が出る条件

Zstandard圧縮サポート• WAL圧縮とベースバックアップに利用できる同条件の pgbench を実行、前後の WAL進行量と、pgbench のＴＰＳ値

WAL圧縮は full page のみ
展開速度のアドバンテージは本検証には含まれない

運用管理

JSON形式ログ• log_destination パラメータに新たな選択肢 「jsonlog」• csvlog の JSON版{"timestamp":"2022-08-30 18:04:51.671 JST","user":"postgres","dbname":"db1","pid":1609,"remote_host":"[local]","session_id":"630dd2af.649","line_num":1,"ps":"SELECT","session_start":"2022-08-30 18:04:47 JST","vxid":"3/2", "txid":0,"error_severity":"ERROR","state_code":"22012","message":"division by zero","statement":"SELECT 1 / 0;","application_name":"psql","backend_type":"client backend","query_id":0}{"timestamp":"2022-06-27 17:28:50.270 JST","pid":1999,"session_id":"62b96916.7cf","line_num":1,"session_start":"2022-06-27 17:23:50 JST","txid":0,"error_severity":"LOG","message":"checkpoint starting: time","backend_type":"checkpointer","query_id":0}

実際の出力には改行は含まれないメッセージによって含まれる属性が異なる

モニタリングビュー・関数の追加• pg_ident.conf ファイル、論理レプリケーション関連のモニタリング関数・ビューが追加

実行時統計情報の共有メモリ化• 実行時統計情報とは:• 各テーブルの各種アクセス回数、推定デッドタプル数、VACUUM記録、チェックポイント実行時の各種カウント、デッドロック発生数、etc ...

• 従来の実行時統計情報• プロセス間通信で stats collector プロセスに連携• 稼働中は ${PGDATA}/pg_stat_tmp/ 下ファイルに書き出し• 停止中は ${PGDATA}/pg_stat/ 下ファイルに保存• pg_stat_tmp を RAM disk に割り当てて高速化• V15 で共有メモリ上で管理するように変更• 停止中はファイルに保存モジュールによるWALアーカイブ• 一般的なこれまでの WALアーカイブ設定例• 外部コマンドを毎回起動しているのは効率的でない• WALアーカイブ処理を行うモジュールを指定可能に• 参考実装 contrib/basic_archive による WALアーカイブ設定例

モジュールによるベースバックアップ• pg_basebackupコマンドの --target オプション• client
従来動作、-Dオプション先に取得• server:/path/of/basebackup サーバ側に取得• 新たな ベースバックアップtarget を追加できる内部API• BaseBackupAddTarget• contrib/basebackup_to_shell• ベースバックアップの本体処理を任意コマンドで実行• pg_basebackup の --target に shell を指定• 指定コマンドに各テーブルスペースの tar データが渡される

使わなくても良い非互換の変更点

排他的ベースバックアップ廃止• 従来: ２種類のベースバックアップ
方式

使用方法

動作の特長

排他的
ベースバックアップ

pg_start_backup()
関数で exclusive =true

pg_stop_backup() 関数を別セッションで実行可。
backup_label 、tablespace_mapファイルを自動生成。
同時に1つのバックアップ取得しかできない。

非排他的ベースバックアップ

pg_start_backup()
関数で exclusive =false

pg_stop_backup() は pg_start_backup() と同セッションで実行。
backup_label や tablespace_map ファイルに書くべき情報が pg_stop_backup() から返される。

pg_basebackup コマンド

• 排他的ベースバックアップ廃止／関数も変更• pg_start_backup()→ 関数名変更 pg_backup_start()• pg_stop_backup()→ 関数名変更 pg_backup_stop()• pg_backup_start_time() , pg_is_in_backup() → 廃止

publicスキーマへのデフォルト権限• 従来: publicスキーマには誰でもテーブル等を作成可能• セキュリティバグを突いた攻撃に利用されがち• 例: オブジェクトを作る→ VACUUM 実行を通してスーパーユーザ権限でコード実行• publicスキーマのデフォルト権限変更• データベース所有者かスーパーユーザ以外はオブジェクト作成権限無し• publicスキーマの所有者が postgres から pg_database_owner に変更• 考えられる影響• バックアップリストア失敗• パッケージソフトがインストールで失敗

推奨はできないが、これで旧デフォルト権限に戻せるその他の非互換• PL/Python で Python 2 サポート終了• array_to_tsvector() 関数で挙動変更• 空文字列の配列要素に対してエラーになる• 関数インデックスのダンプリストアで失敗する可能性• hash_mem_multiplier のデフォルト値 1.0 → 2.0• メモリ使用量が増える• interval型の出力関数が IMMUTABLE → STABLE

非互換修正点は他にも多数

まとめ• バージョン15に見る PostgreSQLの現在• 意欲的な機能追加• MERGE文や論理レプリケーション機能追加• 実用的な利用者メリット提供• 商用DB互換関数など• 拡張性の拡大• 拡張モジュールによる実現余地を拡大• 不断の旧実装の見直し• ソートアルゴリズム差し替え、publicスキーマ権限変更、など