2024年8月に3回目のベータ版の開発に成功した後、PostgreSQL開発グループは9月26日にGAバージョンをリリースしました。最近、私はPostgreSQL 17で見られるいくつかの重要な論理レプリケーション機能についてブログを書いた。
このブログでは、Postgres 17に含まれるいくつかの新しい性能機能を説明し、このシリーズの前のブログで触れなかった別の重要な論理レプリケーション機能も紹介します。
PostgreSQLは年々显著に成長し、各メジャーリリースにとっては、クリティカルな enterprise アプリケーションと非クリティカルな enterprise アプリケーションのためにより健全で信頼性に富んだデータベースとして機能するようになっています。世界中の活発なPostgreSQLコミュニティは、PostgreSQLの成功に貢献しており、すべての変更がプロジェクトのソースコードに追加される前に慎重に調査を行いレビューすることを Ensure しています。MicrosoftやGoogle、Appleなどの大きな技術企業の名前がPostgresに投資をし、内部の専門知識を開発し、オープンソースコミュニティに貢献することを非常に励みます。
論理レプリケーションの改善は、分散型PostgreSQLのサポートを本機能に追加する道を開くことになります。分散型PostgreSQLは、PostgreSQLのディストリビューションアーキテクチャーの実装を指し、マルチノードでの拡張性、耐久性、パフォーマンスの改善を可能にします。
次に、PostgreSQL 17の性能機能について話しましょう。 Materialized CTEsを使用したクエリパフォーマンスの改善。
共通テーブル表現（CTE）は、SELECT、INSERT、UPDATE、またはDELETEステートメントの内部で参照可能な一時的な結果セットです。これらは複雑なクエリの可读性と構造を向上させることができ、再帰的にもなることができるため、阶层的なデータに特に有用です。CTEクエリの基本スyntaxは以下の通りです：
WITH cte_names AS
(– QUERY here )
Select * from cte_names;クエリにWITHキーワードを含めてCTEを作成します。CTE名の後にAS節により結果セットを定義した親クエリを続けます。CTEを定義した後、CTEの名前を参照して、CTEの結果セットを参照し、同じクエリ内で結果セット上でさらなる操作を行います。
PostgreSQL 17は、CTEに関する性能と機能をさらに向上させました。これまでのPostgresの较旧バージョンはCTEを最適化フェンスとして扱い、プランナーがそれらに基づいて predicatesを下げることはできませんでした。しかし、PostgreSQL 12から、より効率的な実行計画を定義することができました。パフォーマンスが重要である場合は、いつもクエリを分析し、実行計画を考慮するべきです。
性能のヒント: 同じ結果セットを複数回参照する場合は、MATERIALIZED キーワードを使用して CTEを作成します。マテリアルイズied CTEを作成すると、Postgresは親クエリの結果を計算して保存します。その後、CTEを複数回参照する場合、後続のクエリは複雑な計算を何度も行う必要はなくなります。
CTEから列統計を抽出する方法、Postgres 17はマテリアルイズied CTEを改善しました
マテリアルイズied CTEは基本的に最適化フェンスとして機能します。これは、一旦计划が選ばれた後、外側クエリがサブクエリの計画を影響しないことを意味します。外側クエリはCTE結果セットの推定幅や行数を見つけることができますので、サブクエリから列統計情報をプランナーに伝達することは合理です。外側クエリは利用可能な情報を使い、列統計情報を外側クエリ計画に伝達することができますが、CTE計画には下流に伝達しません。
このバグはコミュニティに報告されました。これには、この改善の影響を示す簡単なテストケースが含まれています。
例: Postgres 16の動作とPostgres 17の比較
まず、Postgres 16でワークスペースを作成し、それにANALYZEを実行します; 2つのテーブルとインデックス:
postgres=# create table t1(a int);
CREATE TABLE
postgres=# create table t2(b int);
CREATE TABLE
postgres=# create index my_index on t1 using btree (a);
CREATE INDEX
postgres=# insert into t1 select generate_series(1, 100000) from generate_series(1, 3);
INSERT 0 300000
postgres=# insert into t2 select generate_series(1, 100) from generate_series(1, 10);
INSERT 0 1000
postgres=# analyze t1;
ANALYZE
postgres=# analyze t2;
ANALYZE
Then, we create our materialized CTE:
postgres=# explain analyze with my_cte as materialized (select b from t2) select *
from t1 where t1.a in (select b from my_cte);
The query plan from our Postgres 16 code sample contains:
QUERY PLAN
----------------------------------------------------------------------
Nested Loop (cost=37.92..856.50 rows=2966 width=4) (actual time=0.574..0.722 rows=300 loops=1)
CTE my_cte
-> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.038..0.161 rows=1000 loops=1)
-> HashAggregate (cost=22.50..24.50 rows=200 width=4) (actual time=0.449..0.461 rows=100 loops=1)
Group Key: my_cte.b
Batches: 1 Memory Usage: 40kB
-> CTE Scan on my_cte (cost=0.00..20.00 rows=1000 width=4) (actual time=0.046..0.322 rows=1000 loops=1)
-> Index Only Scan using my_index on t1 (cost=0.42..4.06 rows=3 width=4) (actual time=0.002..0.002 rows=3 loops=1
00)
Index Cond: (a = my_cte.b)
Heap Fetches: 0
Planning Time: 1.242 ms
Execution Time: 1.051 ms
(12 rows)
查询计划中、子查询的200行列统计信息不正确，这对整体计划产生了影响。
-> HashAggregate (cost=22.50..24.50 rows=200 width=4) (actual time=0.449..0.461 rows=100 loops=1)
Group Key: my_cte.b
然后，我们将相同的设置和查询针对PostgreSQL 17进行测试：
postgres=# explain analyze with my_cte as materialized (select b from t2) select *
from t1 where t1.a in (select b from my_cte);
QUERY PLAN
-------------------------------------------------------------------------------------------------
---------------------------------
Merge Join (cost=42.25..54.29 rows=302 width=4) (actual time=0.627..0.712 rows=300 loops=1)
Merge Cond: (t1.a = my_cte.b)
CTE my_cte
-> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.031..0.134 rows=1000
loops=1)
-> Index Only Scan using my_index on t1 (cost=0.42..7800.42 rows=300000 width=4) (actual tim
e=0.027..0.049 rows=301 loops=1)
Heap Fetches: 0
-> Sort (cost=26.82..27.07 rows=100 width=4) (actual time=0.598..0.604 rows=100 loops=1)
Sort Key: my_cte.b
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=22.50..23.50 rows=100 width=4) (actual time=0.484..0.494 rows=1
00 loops=1)
Group Key: my_cte.b
Batches: 1 Memory Usage: 24kB
-> CTE Scan on my_cte (cost=0.00..20.00 rows=1000 width=4) (actual time=0.033..0
.324 rows=1000 loops=1)
Planning Time: 1.066 ms
Execution Time: 0.946 ms
(15 rows)正如您在PostgreSQL 17的查询计划中所见，子查询的列统计信息正确地传播到了外部查询的上层规划器。这有助于PostgreSQL选择更好的计划，从而改善查询的执行时间。
这是一个简单的查询，但是对于更大和更复杂的查询，这种变化可能会导致重大的性能差异。
从CTE到外部查询传播Pathkeys
在PostgreSQL 17中，CTE功能的另一个有趣改进是从子查询传播路径键到外部查询。在PostgreSQL中，路径键是查询执行计划过程的一部分，主要用于对需要有序结果的查询（如带有ORDER BY子句的查询）进行排序和排序行，或者在需要进行其他操作（如合并连接）时进行排序。
在Postgres 17之前，即使保证了通过索引扫描节点或排序节点来确保物化CTE子查询的排序顺序，物化CTE子查询的排序顺序也不会共享给外部查询。没有保证的排序顺序允许PostgreSQL规划器选择不那么优化的计划，而拥有保证的排序顺序则更有可能选择优化的计划。
PostgreSQL 17では、CTEが具象化され、特定のソート順序を持つ場合、プランナーは外部クエリでその情報を再利用でき、冗長なソートを避けたり、より効率的な結合方法を有効にすることでパフォーマンスを向上させます。トム・レーン氏のコミットコメントに書かれているように：
「通常の
RTE_SUBQUERYサブクエリのpathkeysを外部クエリに持ち上げるコードはすでに存在していますが、CTEでは使用されていませんでした。CTEと外部クエリの間に最適化フェンスを維持する懸念からかもしれません。」
Postgresソースコードのこの単純な修正により、ソートやマージ結合がCTEの結果の固有の順序に基づいて最適化できる、特に複雑なCTEを含むクエリのパフォーマンスが向上するはずです。
以下は、PostgreSQLの回帰データを使用した例です：
postgres=# CREATE TABLE tenk1 (
postgres(# unique1 int4,
postgres(# unique2 int4,
postgres(# two int4,
postgres(# four int4,
postgres(# ten int4,
postgres(# twenty int4,
postgres(# hundred int4,
postgres(# thousand int4,
postgres(# twothousand int4,
postgres(# fivethous int4,
postgres(# tenthous int4,
postgres(# odd int4,
postgres(# even int4,
postgres(# stringu1 name,
postgres(# stringu2 name,
postgres(# string4 name
postgres(# );
CREATE TABLE
postgres=# CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
CREATE INDEX
postgres=# \copy tenk1 FROM '~/projects/postgres/src/test/regress/data/tenk.data';
COPY 10000
postgres=# VACUUM ANALYZE tenk1;
VACUUM
Postgres 16のコードサンプルのクエリプランには以下が含まれています：
postgres=# explain analyze with x as materialized (select unique1 from tenk1 b order by unique1)
select count(*) from tenk1 a
where unique1 in (select * from x);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
---
Aggregate (cost=764.29..764.30 rows=1 width=8) (actual time=21.592..21.593 rows=1 loops=1)
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b (cost=0.29..306.29 rows=10000 width=4) (actual time=0.046..1.415 rows=10000 loops=
1)
Heap Fetches: 0
-> Nested Loop (cost=225.28..445.50 rows=5000 width=0) (actual time=7.545..20.911 rows=10000 loops=1)
-> HashAggregate (cost=225.00..227.00 rows=200 width=4) (actual time=7.535..9.051 rows=10000 loops=1)
Group Key: x.unique1
Batches: 1 Memory Usage: 929kB
-> CTE Scan on x (cost=0.00..200.00 rows=10000 width=4) (actual time=0.070..3.933 rows=10000 loops=1)
-> Index Only Scan using tenk1_unique1 on tenk1 a (cost=0.29..1.08 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=10000)
Index Cond: (unique1 = x.unique1)
Heap Fetches: 0
Planning Time: 0.806 ms
Execution Time: 21.890 ms
(14 rows)
Postgres 17のコードサンプルのクエリプランには以下が含まれています：
postgres=# explain analyze with x as materialized (select unique1 from tenk1 b order by unique1)
select count(*) from tenk1 a
where unique1 in (select * from x);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=987.55..987.56 rows=1 width=8) (actual time=8.777..8.778 rows=1 loops=1)
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b (cost=0.29..306.29 rows=10000 width=4) (actual time=0.010..1.095 rows=100
00 loops=1)
Heap Fetches: 0
-> Merge Semi Join (cost=0.31..656.26 rows=10000 width=0) (actual time=0.037..8.024 rows=10000 loops=1)
Merge Cond: (a.unique1 = x.unique1)
-> Index Only Scan using tenk1_unique1 on tenk1 a (cost=0.29..306.29 rows=10000 width=4) (actual time=0.013..1.262 rows
=10000 loops=1)
Heap Fetches: 0
-> CTE Scan on x (cost=0.00..200.00 rows=10000 width=4) (actual time=0.016..3.678 rows=10000 loops=1)
Planning Time: 0.800 ms
Execution Time: 8.899 ms
(11 rows)PostgreSQL 16とPostgreSQL 17の查询計画は、このバージョン17の拡張によって大幅に異なります。これは小さな例ですが、より大きなクエリで性能向上が显著になることがわかります。ORDER BY節がCTEのサブクエリに存在する場合にのみ、この改善が有効であることに注意してください。
Scalar Arrayのための高速B-Treeインデックススキャン
PostgreSQLでは、ScalarArrayOpExprは実行計画のノードタイプで、INやANYとして配列やリストの値に関連するクエリ操作を処理します。これは、列を値の集合と比較するクエリに特に有用です。例えば、SELECT * FROM table WHERE column = ANY(ARRAY[1, 2, 3]);のようなクエリ。
ScalarArrayOpExprは、INやANYを使用した複数の比較によるクエリをPostgreSQLにより効果的に最適化することができます。PostgreSQL 17では、これらの操作をさらに速くする新しい性能改善が導入されました。
PostgreSQL 17では、B-treeインデックススキャンに対する重要な改善が行われました。これにより、特に大きなINリストやANY条件を持つクエリの性能を最適化します。これらの改善により、システムが行うインデックススキャンの数が減少し、CPUとバッファページの競合が减轻され、クエリの実行速度が速まれます。
键として改善されたのは、スカラー配列操作式（SAOP）の取り扱いで、B-树のインデックスをより効率的に巡回することができるようになり、特に multidimensional queries において。たとえば、複数のインデックス列（それぞれ独自のINリストを持っている）がある場合、PostgreSQL 17は今やこれらの操作を1つのインデックススキャンでより効率的に処理することができ、前のバージョンでは複数のスキャンを行わなくてもです。これは、以前にページアクセスが瓶颈であったCPU依存型のワークロードにおいて、性能向上が20-30%になる可能性があります。
加えて、PostgreSQL 17は内部ロックの管理をより良くし、高コンカーレンシーのワークロードにおいて、特にB-树インデックス内で複数の次元をスキャンする際の性能をさらに向上させます。
これを示すために、簡単な例を用います。同じtenk1テーブルとデータを使用して、Postgresレジストリサイトでの前の例と同じように行います。
私の例で、最初にPostgres 16で実行します。
CREATE TABLE tenk1 (
postgres(# unique1 int4,
postgres(# unique2 int4,
postgres(# two int4,
postgres(# four int4,
postgres(# ten int4,
postgres(# twenty int4,
postgres(# hundred int4,
postgres(# thousand int4,
postgres(# twothousand int4,
postgres(# fivethous int4,
postgres(# tenthous int4,
postgres(# odd int4,
postgres(# even int4,
postgres(# stringu1 name,
postgres(# stringu2 name,
postgres(# string4 name
postgres(# );
CREATE TABLE
postgres=# CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
CREATE INDEX
postgres=# \copy tenk1 FROM '~/projects/postgres/src/test/regress/data/tenk.data';
COPY 10000
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 IN (1, 2, 3);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=14.20..330.12 rows=176 width=244) (actual time=0.138..0.153 rows=3 loops=1)
Recheck Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Heap Blocks: exact=3
Buffers: shared hit=9
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..14.16 rows=176 width=0) (actual time=0.102..0.102 rows=3 loops=1)
Index Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Buffers: shared hit=6
Planning:
Buffers: shared hit=2
Planning Time: 0.900 ms
Execution Time: 0.242 ms
(11 rows)
postgres=# SELECT idx_scan, idx_tup_fetch FROM pg_stat_user_tables WHERE relname = 'tenk1';
idx_scan | idx_tup_fetch
----------+---------------
3 | 3
(1 row)
前のクエリで、INクエリの共有バッファヒットが9であり、インデックススキャンを3回行って結果を得たことがわかります。PostgreSQLでは、共有ヒットはバッファ管理に関連付けられた特定の種類のキャッシュヒットであり、共有ヒットはPostgreSQLがディスクからではなく共有バッファプールからデータブロックまたはページをアクセスする際に発生し、クエリパフォーマンスを改善することができます。
この同一の例を、今度はPostgres 17で実行します。
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 IN (1, 2, 3);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=12.88..24.08 rows=3 width=244) (actual time=0.043..0.054 rows=3 loops=1)
Recheck Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Heap Blocks: exact=3
Buffers: shared hit=5
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..12.88 rows=3 width=0) (actual time=0.026..0.026 rows=3 loops=1)
Index Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Buffers: shared hit=2
Planning:
Buffers: shared hit=59
Planning Time: 0.479 ms
Execution Time: 0.116 ms
(11 rows)
postgres=# SELECT idx_scan, idx_tup_fetch FROM pg_stat_user_tables WHERE relname = 'tenk1';
idx_scan | idx_tup_fetch
----------+---------------
1 | 3
(1 row)
Postgres 17では、共有バッファヒットが5に減少し、最も重要な点はインデックススキャンが1回のみ行われる点です（Postgres 16の場合は3回スキャンされます）。この改善により、Postgres 17ではスカラー配列操作のパフォーマンスが大幅に向上し、Postgresはより最適化されたクエリプランを選択することができます。
メジャーアップグレード中のロジカルレプリケーションスロットおよびサブスクリプションの保持
ロジカルレプリケーションスロットの保持とサブスクリプション依存関係の移行は、PostgreSQL 17に追加された別のロジカルレプリケーション機能です。この機能は、PostgreSQL 17から後のバージョンへのアップグレードにのみ役立ちます。Postgres 17以前のアップグレードはサポートされていません。レプリケーションスロットとレプリケーションオリジンは、ロジカルレプリケーション環境を構築する際に生成されます。ただし、この情報はノードに固有であり、レプリケーションのステータス、アプリケーションのステータス、およびWAL送信のステータスを記録するために使用されるため、アップグレードプロセスの一部としてアップグレードされません。パブリッシュノードがアップグレードされた後、ユーザーはこれらのオブジェクトを手動で構築する必要があります。
pg_upgradeプロセスは、PostgreSQL 17で改善されており、これらの内部オブジェクトを参照して再構築する機能があります。この機能により、ロジカルレプリケーションを持つノードをアップグレードする際に、レプリケーションが自動的に再開されます。以前は、メジャーバージョンのアップグレードを実行する際に、ユーザーはロジカルレプリケーションスロットを削除し、アップグレード後にサブスクライバーとデータを再同期する必要がありました。これにより、アップグレードの複雑さが増し、ダウンタイムが長くなりました。
アップグレードプリンタークラスター時に行う手順は以下の通りです。
- パブリッシャーに登録されているすべての購読を一時的に無効にしてください。これは、アップグレードプロセスが完了する後、再び有効にします。
- 新しいクラスターの
wal_levelをlogicalに設定します。 - 新しいクラスター上の
max_replication_slotsは、古いクラスター上のレプリケーションスロットと同じまたは大きい値に設定します。 - スロットで使用されている出力プラグインは、新しいクラスターにインストールされています。
- アップグレード前に、古いクラスターのすべての変更が目标クラスターにレプリケートされています。
- 古いクラスター上のすべてのスロットは使用可能であることを確認してください。これはpg_replication_slotsビューの衝突する列をチェックすることで行います。古いクラスター上のすべてのスロットで、衝突はfalseに設定されているはずです。
- 新しいクラスターの
pg_replication_slotsビューのTemporary列でfalseとなっているスロットは存在してはならません。新しいクラスターには永続的な論理的なレプリケーションスロットが存在してはならません。
上記の前提条件が満たされていない場合、レプリケーションスロットのアップグレードプロセスpg_upgradeはエラーを返します。
結論
PostgreSQL 17では、コミュニティの注目はPostgreSQLをより性能が高く、スケーラブルで、安全性が高く、 enterprise-readyにすることをionaleに持続する。Postgres 17は、互換性に関する新機能の追加と既存の機能をよりパワフルで健全なものに改善することで開発者の経験を改善します。
バージョン17を超えて、PostgreSQLはさらに成長し、改善され、より高性能になり、よりスケーラブルなデータベースが必要な enterprise applicationに適していくでしょう。過去数年間ではスケールアウト（水平和）の性能が改善されましたが、PostgreSQLにシャーディング機能を追加することで水平方向の機能をより改善する余地はまだあります。MERGEコマンドの改善によりPostgres 17の互換性が高まり、Postgres 17以降により多くの互換性機能の計画があります。より多くの論理レプリケーションの改善がもたらされることになり、DDLレプリケーションや欠けるオブジェクト（如seq）のレプリケーション、より良いノード管理についての進展が予想されます。
Source:
https://dzone.com/articles/postgresql-17-a-major-step-forward-in-performance