Open8

PostgreSQL新機能調査

こばこば

PostgreSQL v15の新機能

（リリースノート）PostgreSQL: Documentation: 15: E.5. Release 15
（虎の巻）PostgreSQL dockument by Noriyoshi Shinoda, Pointnext, HPE Japan
（SRA OSS）PostgreSQL 15検証報告 (sraoss.co.jp)
PostgreSQL15の最新機能を解説してみる (zenn.dev)

（非互換の注意すべき変更）

  • PUBLIC スキーマに対するアクセス権
  • (SRA)新しいデータベースが作成された時、データベース所有者とスーパーユーザ以外は、 デフォルトの public スキーマでオブジェクトを作成することができなくなりました。旧バージョンからデー タベースをリストアした場合も、この新しい所有者権限が適用されます。
  • Stats collectorを廃止、共有メモリを使う方式に変更

（新しいSQL）

  • Mergeコマンド、insert on conflictや WITH 句と RETURNING 句との比較
    • 結合条件からテーブルに対する INSERT / DELETE / UPDATE 文を同時に実行する MERGE 文がサポート
MERGE INTO target_table [AS alias]
 USING source_table [AS alias]
 ON join_clause
 WHEN MATCHED [AND condition] THEN
          UPDATE SET … | DELETE | DO NOTHING
 WHEN NOT MATCHED
          INSERT VALUES … | DO NOTHING
  • SRA資料に実例あり
    • PostgreSQL の INSERT ON CONFLICT と MERGE の簡易性能比較 - Qiita
    • 呼び出し元権限で実行されるビュー、security_invoker 属性
    • リテラルの扱い変更、数字から始まるリテラル文字列は従来数字部分と文字列部分に分割されていましたが、 PostgreSQL 15 ではエラーに

（Logical Replication）

  • Logical Replicationの拡張
    • PostgreSQL 15での論理レプリケーションの改善 - PostgreSQL 15でコミットされた機能の紹介：技術者Blog｜PostgreSQLインサイド : 富士通 (fujitsu.com)
    • Logical_Replication (edbjapan.com)
    • streamingオプションをonにすると
    • 行フィルター指定レプリケーション（条件合致フィルター）
    • 列指定レプリケーション（列フィルター）
    • スキーマ内の全テーブル指定
    • FOR TABLES IN SCHEMA 句とスキーマ名を指定
    • 従来から利用できた FOR ALL TABLE 句の場合は全スキーマのテーブルが登録
    • LSN のスキップ、SKIP (LSN='lsn_value)
    • エラー発生時の SUBSCRIPTION 無効化
    • TWO_PHASE オプションの追加
    • WITH (disable_on_error=true, two_phase=true)

（性能改善、パラレル化など）

  • パラレルクエリー対応、SELECT DISTINCT
  • (SRA)NOT IN 句の改善
  • (SRA)ソート性能改善、外部ソートのアルゴリズムが変更
  • (SRA)ウィンドウ関数の性能改善 row_number()、rank()、count()
  • \copyの性能改善。特に列が少なく行が多いテーブル の読み込み（\copy ... FROM ...）で性能が向上します。これまで 1 行ごとに PostgreSQL のワイヤプロトコル における CopyData メッセージを出していたものを、8KB 単位にまとめるように変更されました。

（WALやバックアップ関連）

  • Base backupでtargetが指定できるようになった？

  • Base backupで圧縮が可能に（クライアント/サーバ）

  • 排他的バックアップ・モードの削除？
    オンライン物理バックアップの排他モードと非排他モードについて ～PostgreSQLバージョン15対応版～（第34回PostgreSQLアンカンファレンス@オンライン 発表資料）

  • wal_compressionパラメータでlz4とZstandardの指定が可能に、Full Page Writesが対象？

    • PostgreSQL ドキュメンテーション: wal_compression パラメータ (postgresqlco.nf)
    • このパラメータがonなら、full_page_writesがonあるいはベースバックアップの際、PostgreSQLサーバはWALに書き出すフルページイメージを圧縮します。
    • 15でもコンパイル時に指定が必要？
    • lz4 (if PostgreSQL was compiled with --with-lz4) and zstd (if PostgreSQL was compiled with --with-zstd).
    • パラメータ自体は前からあった
    • 14ではon/offのみ、設定値 on は設定値 pglz のエイリアス
    • 15だと圧縮形式を指定可能、デフォルトではpglz？
postgres=# alter system set wal_compression = 'zstd';（再起動後）postgres=# show wal_compression ;

 wal_compression
-----------------
 zstd

(1 row)

postgres=# select pg_switch_wal();

 pg_switch_wal 
---------------
 0/2000160

(1 row)

postgres=# select pg_ls_waldir();
                         pg_ls_waldir
--------------------------------------------------------------
 (000000010000000000000002,16777216,"2023-09-22 07:28:36+00")
 (000000010000000000000003,16777216,"2023-09-22 07:28:41+00")

(2 rows)※圧縮されてない？
  • pg_receivewalの圧縮コマンドも追加された
  • （SRA)先読みによるリカバリ性能改善、recovery_prefetch、wal_decode_buffer_size
  • archive_libraryパラメータ、アーカイブログでloadable moduleが利用可能に
    • アーカイブログの取得を共有ライブラリで実施できるようになりました。リファレンス 実装として Contrib モジュール basic_archive が追加された。
    • 15時点では、archive_library と archive_command を両方指定した場合には archive_library が使用される。

（ロール関連）

  • ロール　pg_checkpointが追加、checkpointを実行できる

（ログやモニタリング）

  • pg_stat_subscription_statsが追加、pg_stat_statements モジュールでは一時ファイルの I/O 時間や JIT に関する情報がモニタリングできる
  • log_checkpoints パラメーターのデフォルト値が on に変更
  • ログ出力形式がJSONに
こばこば

PostgreSQL v16の新機能

（リリースノート）PostgreSQL: Documentation: 16: E.1. Release 16
（虎の巻）PostgreSQL16 Beta 1 tech documents by Noriyoshi Shinoda, Pointnext, Japan (hpe.com)
（SRAOSS）PostgreSQL 16検証報告 (sraoss.co.jp)

（非互換の注意すべき変更）

（新しいSQL）

  • JSON 構文をチェックできる IS JSON句の追加。
  • FROM 句にサブクエリーが記述されている場合、エイリアス名はオプションになった
  • 数値リテラルに 2 /8/16 進数も記述可に。数値文字列内 のアンダースコアがサポート
  • ICU ロケールの拡張
  • PostgreSQL 16 (Beta 1) で実装された SQL:2023 標準の関数 ANY_VALUE() って何者？ - Speaker Deck
  • libpq接続ロード・バランシング

（Logical Replication）

  • ロジカル・レプリケーションの拡張

  • 長時間トランザクションの適用パフォーマンスの改善

  • streamingオプションにparallelの追加

  • streaming = parallel でロジカルレプリケーションを並列適用した場合の注意点としては、パブリッシャと サブスクライバでデータ更新順序が一致しないことがあり得ることと、並列適用にあたってロック競合があるということです

  • 限定的な双方向レプリケーション
    Do PostgreSQL 16 Dream of Multi-Master Replication? - Speaker Deck
    origin オプションが追加

  • SUBSCRIPTION 作成を許可するロールを追加

  • (SRA)初期データ転送性能の改善、初期コピーでバイナリ形式が利用可
    サブスクリプションの binary オプションで指定し、デフォルトは off でテキスト形式

  • スタンバイ・ インスタンスにおけるロジカル・デコーディング
    スタンバイをパブリケーションとする場合、スタンバイは参照しか受け付けず、直接パブリケーションを 作成できないため、プライマリでパブリケーションを作成し、ストリーミングレプリケーションでスタンバイ に複製する必要がある

（性能改善、パラレル化など）

  • パラレルクエリーの拡張
  • FULL OUTER JOIN 構文や RIGHT OUTER JOIN 構文
  • Window関数の最適化、string_agg 関数、array_agg 関数に対応
  • (SRA)SELECT DISTINCTでインクリメンタルソート
  • (SRA)COPY句性能改善、PostgreSQL 16 では行データ追加にともなうページ追加について改善
  • postgres_fdw を使った外部テーブルに対する COPY 文で複数レコードを一括挿入

（WALやバックアップ関連）

  • pg_dump コマンドの出力に LZ4 圧縮、Zstandard 圧縮が利用に
    -Z / --compress の圧縮方式で、従来利用可能であった gzip 以外に新しい圧縮方式として lz4 と zstd が追加

  • パラメーターarchive_command と archive_library は同時に値を設定することが不可に
    PostgreSQL 15 では archive_library が優先
    16では同時に設定するとログにエラーが出力され、WAL アーカイブは出力されない。

（ロール関連）

  • pg_maintainロールの追加

（ログやモニタリング）

  • pg_stat_io ビューが追加。pg_stat_all_tables ビュー、pg_stat_all_indexes ビューにはオブジェクトに対する最終アクセス時刻がわかる列を追加
    PostgreSQL 16 Add pg_stat_io view, providing more detailed IO statistics - Speaker Deck

その他

  • pg_hba.conf ファイル、pg_ident.conf ファイルの一部設定項目に正規表現が使えるように
  • GSSAPI/Kerberos 資格情報の委任をサポート
  • 複数の接続先を指定したデータベースサーバへの接続を確率的に均等に分散させることが可 能になり、システム全体のパフォーマンスと耐久性の向上
  • load_balance_hosts には、 disable と random のいずれかの値を設定
  • (SRA)ページ単位でのタプル凍結
  • (SRA)REINDEX DATABASE 仕様変更
  • (SRA)postmaster コマンド廃止
  • (SRA)SIMD CPUアクセラレータ対応
こばこば

Error: connect EACCES 172.21.116.234:6443

2023-09-20T01:16:04.422Z: Registered distributions: 
2023-09-20T01:16:18.100Z: Registered distributions: rancher-desktop
2023-09-20T01:16:18.953Z: Registered distributions: rancher-desktop
2023-09-20T01:16:18.954Z: Creating initial data distribution...
2023-09-20T01:16:54.871Z: Did not find a valid mount, mounting /mnt/wsl/rancher-desktop/run/data
2023-09-20T01:17:01.160Z: Installing C:\Program Files\Rancher Desktop\resources\resources\linux\internal\trivy as /mnt/c/Program Files/Rancher Desktop/resources/resources/linux/internal/trivy into /usr/local/bin/trivy ...
2023-09-20T01:17:02.781Z: Installing C:\Program Files\Rancher Desktop\resources\resources\linux\internal\rancher-desktop-guestagent as /mnt/c/Program Files/Rancher Desktop/resources/resources/linux/internal/rancher-desktop-guestagent into /usr/local/bin//rancher-desktop-guestagent ...
2023-09-20T01:17:23.844Z: WSL: executing: cat /root/.docker/config.json: Error: wsl.exe exited with code 1
こばこば

docker run --name postgres-v14 -e POSTGRES_PASSWORD=password -p 54324:5432 -d postgres:14.9

こばこば
version: '3.9'
services:
  postgres-v14:
    image: postgres:14.9
    container_name: postgres-v14
    environment:
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=testdb
#    volumes:
#      - postgres:/var/lib/postgresql/data
    ports:
      - 54324:5432
  postgres-v15:
    image: postgres:15.4
    container_name: postgres-v15
    environment:
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=testdb
#    volumes:
#      - postgres:/var/lib/postgresql/data
    ports:
      - 54325:5432
  postgres-v16:
    image: postgres:16.0
    container_name: postgres-v16
    environment:
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=testdb
#    volumes:
#      - postgres:/var/lib/postgresql/data
    ports:
      - 54326:5432

接続

docker-compose exec -it postgres-v16 bash
docker-compose exec -it postgres-v14 psql -U postgres

こばこば

PostgreSQL v14の新機能

（虎の巻）

https://edbjapan.com/pgvt2021/sawada_pgvt_2021.pdf

PostgreSQL 14でのBtreeインデックスのVacuum関連の改善についての解説 (masahikosawada.github.io)

（非互換の注意すべき変更）

  • (SRA OSS) クライアント証明書認証の変更
  • pg_standby コマンドの廃止

（新しいSQL）

  • postgres_fdwの拡張
    • 外部テーブルのTRUNCATE
    • 外部テーブルの非同期スキャン
      • ForeignScanの非同期処理
      • postgres_fdw モジュールでは async_capableオプションを on に設定（デフォルト値はoff）。
    • 外部テーブルの一括コピー
      • batch_size、デフォルトは1で行わない
    • 外部サーバとの接続処理が改善されており、外部サーバとの接続が切れていたら自動的に再接続を行うようになりました
    • keep_connections （デフォルト on）
  • pg_amcheck
    • テーブルやインデックスの構造の論理的な一貫性を検査します。pg_amcheck の機能は contrib として提供される amcheck 拡張の関数によって実現されています。したがって、pg_amcheck を使うには amcheck の導入が必要で、pg_amcheck はamcheck 拡張を使いやすくして利便性を高めたものと言えます。
  • 長いパスワード対応
  • パーティションテーブルへのREINDEX対応
  • トースト列のlz4圧縮
    • CREATE TABLE compress1(c1 INT, c2 TEXT COMPRESSION lz4) ;
    • これまではpglz圧縮
    • configureで--with-lz4が必要
  • パーティションデタッチのロック軽減
    • ALTER TABLE DETACH CONCURRENTLY
  • SEARCH句とCYCLE句
    • SQL標準らしい
  • GROUP BY 句の DISTINCT

（Logical Replication）

  • ストリーム送信
    • WITH (streaming = on)
    • 13以前はコミットを契機に変更を送信しており、変更量の大きなトランで、パブリッシャ側でディスクへのあふれが発生する
    • サブスクライバ側で変更内容をファイルに保持するように
  • (SRA OSS) 初期同期時の更新を複数トランザクション化
    • 初期コピーとコピー中の変更のトランザクションを分離
    • サブスクライバ側のデータの参照可能タイミングも変わっている
  • バイナリ転送モード
    • WITH (binary = on)
    • timestamp型やbytea型で効果が大きいと考えられる

（性能改善、パラレル化など）

  • B-Treeインデックスの肥大化防止
    • Bottom-up index deletion
    • HOTでないupdateで有効、Vaccum以外のタイミングで削除
  • Index VaccumのSkip
    • デフォルト？INDEX_CLEANUP = ONで削除も出来る
    • ゴミがテーブル全体の2%以内の集中している場合、インデックスVacuumをスキップ
      する
  • Memoize
  • VaccumのFailsafeモードの導⼊
    • XID周回の危機が迫っている場合、XIDの凍結処理（FREEZE）以外の処理をスキップ
    • vacuum_failsafe_age、vacuum_multixact_failsafe_ageで制御可能
  • パラレルクエリの拡張
    • パラレルシーケンシャルスキャン
    • REFRESH MATERIALIZED VIEWでパラレル対応
  • BRINインデックスの拡張
    • Bloom フィルターを使用する演算子クラス「{データ型}_bloom_ops」が追加
    • Min/Max-Multi Index
  • (SRA OSS)多セッションにおけるオーバーヘッドの軽減
    • CPU 数が多くセッション数が多いシステムにおいて MVCC 可視化スナップショットの計算速度が改善
    • 本改良の開発者からもベンチマーク結果が報告されていて、1000 以上のセッションが存在する時に
      オーバーヘッド軽減による性能が向上

（WALやバックアップ関連）

  • pg_stat_wal

（ロール関連）

  • pg_database_owner
  • pg_read_all_data
  • pg_write_all_data

（ログやモニタリング）

  • COPYコマンドのモニタリングなど
    • pg_stat_progress_copy
  • pg_stat_wal
  • pg_stats_ext_exprs
    • 式に関する拡張統計
    • (SRA OSS)拡張統計情報がない場合、予測行数が実際の件数とかけ離れています。WHERE や GROUP BY の条件に該当する式を拡張統計情報に使うと、より正確な行数見積りが行われます。
  • pg_backend_memory_contexts
  • トランザクション周回の警告閾値が変更
  • log_autovacuum_min_durationにインデックス情報の詳細が