PostgreSQL 16
最新情報セミナー
2023-08-25
SRA OSS合同会社 高塚 遥

Copyright 2023 © SRA OSS LLC All rights reserved

講演内容 / 講演者• 講演内容• PostgreSQL概要• PostgreSQL 16 の新機能、非互換変更• 講演者• SRA OSS合同会社 高塚 遥

• PostgreSQL のヘルプデスク、コンサルティング、設計構築、トレーナー等を１５年以上にわたり担当• 特定非営利活動法人 日本PostgreSQLユーザ会 理事

PostgreSQL とは• 多機能、高性能、かつオープンソースの RDBMS• 伝統的な RDBMS設計• INGRES(‘70)、POSTGRES(‘80) から続く長い歴史• BSDタイプのライセンス• 特定オーナー企業が無い
ある種のOSS開発体制

企業

企業

企業
PostgreSQL
製品

企業
開発者
PostgreSQL開発体制

PostgreSQLのリリース履歴
コード行数

4,000,000
3,500,000
3,000,000‘11.092,500,000‘08.022,000,000 ‘05.01‘05.11

bitmapscan

500,000
0

PITR,MSVC‘10.09‘09.07hot
Update

4core
scale

Replication

OLAP,CTE‘22.09MERGE‘06.121,500,000
1,000,000‘12.09‘21.09‘20.09‘19.10‘18.10‘17.10‘16.09‘16.01‘14.12‘13.09

many
core
scale

SSI,
FDW

JSONB

Parallel
query

RLS,MView, UPSERT
UView

JIT
compilelogicalRepli.

deduplicate
Btree

Table
AM I/F

SEARCH/CYCLE

8.0 8.1 8.2 8.3 8.4 9.0 9.1 9.2 9.3 9.4 9.5 9.6 10 11 12 13 14 15

現代における PostgreSQLの実力
強み• 各種用途での圧倒的な実績• 各種ソフトウェアからの対応• 安定した開発体制

弱み• 伝統的な設計による性能限界• ストレージ主体• 少CPU、小メモリ想定• SQL標準準拠の方針• 保守的な開発方針• 様々な互換製品、拡張製品• 小さい本体と外部機能依存• 豊富なプラットフォーム選択肢• 高可用性• スケールアウト• 監査ログ、プランナヒント、監視、など

PostgreSQL 16 新機能• 性能向上• プランナ／エグゼキュータ改善• 同時データ投入の改善• SIMD CPU機能対応• SQL機能• SQL/JSON• 数値リテラル表現• ICU照合順序ルールに対応• クライアント• libpqロードバランス• psqlで拡張問い合わせ• ロジカルレプリケーション• スタンバイからパブリケーション• 並列適用• 双方向レプリケーション• 運用管理• 新たなモニタリング項目• 新たな定義済ロール• VACUUM／凍結処理の改良• Mesonビルド

プランナ／エグゼキュータ(1) パラレル対応の拡大• Hash Full Join／Hash Right Join がパラレル対応• これまで外部結合での Hash結合は Hash Left Join のみがパラレル対応• string_agg 、 array_agg でパラレル対応

プランナ／エグゼキュータ(2) インクリメンタルソート対応• SELECT DISTINCT でもインクリメンタルソートに対応

プランナ／エグゼキュータ(3) ウィンドウ関数の最適化• フレーム化オプションの最適化• Run Condition 適用拡大

フレーム化
オプション
最適化でプ
ラン要素を集約

性能改善(1)

同時データ投入の改善• テーブルのページ拡張を複数ページまとめて行う• 並列でデータ投入する場合に効果的
ファイルシステムによる性能差あり

pgbench による COPY FROMの並列同時実行

性能改善(2)SIMD CPUアクセラレータ対応• SIMD (Single Instruction Multiple Data)• x86_64 アーキテクチャCPU では SSE2 命令に対応• 内部のプロセス制御処理、JSON文字列入力、ASCII文字列入力

SQL機能(1)SQL/JSON• 述語とJSONデータ構築関数が追加

PostgreSQL 15で１回取り下げとなったもの。
若干変更あり。

SQL機能(2)

数値リテラル表現• 16進数、8進数、2進数の整数値
マイナスはN進数指定の手前に書く。

• 桁区切り付きの数値「_」の使い方は自由。
4桁区切りでも良い。
何であれ無視される。

SQL機能(3)ICU照合順序• ICUライブラリはビルド時に含めるのがデフォルトになった• 定義済み照合順序• 各言語地域のICU照合順序、 ucs_basic（UNICODEコード順）、unicode（言語地域未定時の妥当そうなソート順）• ルール機能「高」と「髙」を同じとみなす
ように指定

ソート順序に英米式トランプの並び順を指定

ロジカルレプリケーション（１） スタンバイでパブリケーション• ストリーミングレプリケーションのスタンバイから、ロジカルレプリケーションが可能になった• CREATE PUBLICATION は プライマリで行う• CREATE SUBSCRIPTION で接続先にスタンバイサーバを指定する• 補助関数 pg_log_standby_snapshot()ロジカルレプリケーション（2） 並列適用

• サブスクリプションの新オプション streaming = parallel• コミットを待たずに送信し、さらに並列に適用する• max_parallel_apply_workers_per_subscription で最大並列度を指定
並列適用なので行の物理順序は一致しなくなるロジカルレプリケーション（3） 双方向レプリケーション• サブスクリプション origin = none 指定• オリジンがある更新データは取り込まない - 更新のピンポンを防止⇒ マルチマスタ構成が可能• デフォルトは any : なんでも取り込む
pub2• 更新の衝突に関する機能は無し• 利用者側で対策が必要
DBサーバ1

DBサーバ2sub2-1

sub2-3
pub1
sub1-2

pub3

sub1-3sub3-1
sub3-2

DBサーバ3

ロジカルレプリケーション（4） 様々な高速化• 主キー以外のインデックス使用• 主キーが無く、REPLICA IDENTITY FULL が指定されているテーブルのレプリケーションで、UPDATE / DELETE で行を特定するときに、主キー以外のインデックスがあれば活用する• 初期コピーでバイナリ転送• これまで binary=on でも、初期コピーには適用されなかった

bytea型とtimestamp型の列を持つテーブルで検証クライアント機能(1) libpqロードバランス• 接続オプション load_balance_hosts = random• デフォルトは disable• 環境変数PGLOADBALANCEHOSTS でも指定可

クライアント(libpq)• 以前から複数接続先ホスト指定は可能（必ず先頭から試行する）• 以前からある target_session_attrs オプションで、接続時プライマリ・スタンバイ指定も可

DBサーバ1

DBサーバ2

ランダムに試行して失敗したら別の宛先を試行

DBサーバ3

クライアント機能(2) psql / pg_dump• psql で拡張プロトコル• parse → bind → execute で実行させる• pg_dump 圧縮オプションに lz4、zstd 追加• 従来は gzip のみ• lz4 : （圧縮時）速い、圧縮率はやや劣る• zstd: （圧縮時）遅い、高い圧縮率

いずれも圧縮レベルのオプションがデフォルトの場合

運用管理(1) 新たなモニタリング項目
関数／ビュー説明ビュー pg_stat_io

ストレージ I/Oに関する統計情報を出力。
→ ストレージ使用量把握に

ビュー pg_stat_*_tables/indexes の
列 last_seq_scan、last_idx_scan

最後にシーケンシャルスキャン、インデックススキャンが実行された時刻を出力。
→ 使用状況把握に

ビューpg_stat_*_tables の列 n_tup_newpage_upd

新しいページへの行バージョン作成を伴って更新された行数を出力。
→ FILLFACTOR調整の目安に関数
pg_stat_get_backend_subxact(backendid)

バックエンドのサブトランザクションキャッシュ内のサブトランザクション数、そのキャッシュがオーバーフローしているかを出力。
→ サブトランザクション多用による性能劣化把握

運用管理(2) 新たな定義済ロール• pg_maintain• 全てのテーブルにメンテナンス操作ができるロール• VACUUM、 ANALYZE、 CLUSTER、 REFRESH MATERIALIZED VIEW、REINDEX、 LOCK TABLE• pg_create_subscription• サブスクリプションを作成できるロール• データベースに対する CREATE権限も必要

いずれも以前はスーパーユーザ限定であった• pg_use_reserved_connections• 新パラメータ reserved_connectionsによる予約枠を使えるロール• superuser_reserved_connections とは別に予約枠を設定

運用管理(3) mesonビルド• これまで• UNIX、Linux むけ： autoconf / make• Windows むけ： 独自perlスクリプト / Visual Studio• これらに加えて meson / ninja によるビルドをサポート• いまのところ従来ビルド方式のすぐの廃止が宣言されたわけではない

VACUUM処理(1) ページ凍結の改善• ページ単位でのタプル凍結• 「積極的」でないVACUUM処理であっても、WALにフルページ書き込みをする
ページについては、凍結処理も行う

凍結処理を行う弊害は WAL が多く出ること↓
元々 WALが多く出るタイミングで一緒に凍結処理もしてしまおう

タプル凍結とは？
• タプル（≒行） には作成/削除時の XID（トランザクションID）が記録される• XID は今のところ 32bit整数なので、値を周回して使うために VACUUM時にタプルの XID値を 「最も古い値」を意味する予約された値に書き換える⇒ これを「凍結する」 と呼ぶ

VACUUM処理(2)

ページ凍結のWAL量削減• ページ凍結を行う VACUUM による WAL出力量を抑制• WAL出力の重複をなくす

テーブルをTRUNCATEで空にする↓
CHECKPOINT を実行↓
10万行をINSERT↓無関係なトランザクションでXID進行↓VACUUM FREEZE 実行
VACUUM FREEZEコマンド前後でWAL出力量を計測

VACUUM処理(3) リングバッファ指定パラメータ• VACUUM コマンドに新オプション
BUFFER_USAGE_LIMIT• デフォルト 256kB• この値の範囲内だけ、共有バッファを使用• １回のVACUUM で 共有バッファがすっかり書き換えられるのを防止
50MBほどのテーブルのVACUUM

非互換の変更(1)• PL/pgSQL カーソル仕様変更• これまで: カーソル変数名がそのまま PostgreSQLカーソル名（ポータル名）• → ポータル名はセッション内が一つの名前空間なのでしばしば衝突していた「ERROR: cursor “c1” already in use」

• これから: カーソル変数名とは無関係なポータル名が付与される• 「 <unnamed portal 3>」のような名前になる• カーソル変数に文字列を代入してポータル名を明示的に指定することもできる
DECLARE c1 CURSOR FOR SELECT * FROM pg_users:
BEGIN
c1 := 'c1’;
OPEN c1;・・・非互換の変更(2)• 主キー用のインデックスに NULLS NOT DISTINCT 指定禁止
• これまで指定可能だったがリストア不能のダンプが生成されていた• REINDEX DATABASE コマンドでシステムテーブルが対象外になる• これからは REINDEX SYSTEM を実行する必要がある• 継承テーブル／パーティションテーブルで、親子間で生成列と通常列の混在禁止• 生成列の式が異なることは引き続き許容される• ロールの CREATEROLE属性の制限• CREATEDB、REPLICATION、BYPASSRLS 変更にはその属性が必要に• ロールのメンバ変更には自身の所属と ADMIN OPTION が必要に

非互換の変更(3)• contrib/pg_walinspect で関数廃止• pg_get_wal_records_info_till_end_of_wal()• pg_get_wal_stats_till_end_of_wal()• 「end_lsn」引数を持つ同機能の関数に「FFFFFFFF/FFFFFFFF」を指定すれば代替できる。

• CREATE RULE “_RETURN“ によるビュー作成が廃止• postmasterコマンド廃止• 以前から postgres へのシンボリックリンクにすぎない非互換の変更(4)• 既存設定パラメータの廃止、名称変更• force_parallel_mode → debug_parallel_query に名称変更• vacuum_defer_cleanup_age → 廃止
• レプリケーションスロットや hot_standby_feedback = on で代替• promote_trigger_file → 廃止
• トリガファイルを置くことでスタンバイサーバを昇格させる機能自体が廃止• lc_collate → 廃止
• lc_ctype → 廃止
• 接続先データベースの collate と ctype を返していた。以下問い合わせで代替。

まとめ• PostgreSQL• 長い歴史を持つ、特定オーナーを持たない OSS DB• 安定した開発体制• 引き続き活発な開発活動• PostgreSQL 16• 追加項目の多いリリース： 機能/性能の両面で様々な拡張がされている• 2023年 秋～冬にリリース予定