在2024年8月成功完成第三个beta版本后，PostgreSQL开发组于9月26日发布了GA版本。最近，我在博客中讨论了PostgreSQL 17的一些关键逻辑复制功能。在这篇博客中，我将描述Postgres 17中的一些新性能特性，以及我之前博客系列中没有涵盖的另一个重要逻辑复制功能。
PostgreSQL这些年来发展迅速，随着每个主要版本的发布，它变得更加健壮、可靠和响应迅速，适用于各种企业应用，无论是关键任务还是非关键任务。全球活跃的PostgreSQL社区为PostgreSQL的成功做出了重要贡献，认真审查和review所有更改 trước将其添加到项目源代码中。看到像Microsoft、Google、Apple等大公司通过开发内部专长并回馈开源社区，也非常鼓舞人心。
逻辑复制的改进为将分布式PostgreSQL支持添加到核心功能铺平了道路。分布式PostgreSQL指的是在分布式架构中实现PostgreSQL，允许跨多个节点提高可扩展性、容错性和性能。
现在，让我们讨论一些PostgreSQL 17的性能特性。
改进的查询性能与物化CTE
### 改进的查询性能
#### 物化CTE（Common Table Expressions）
PostgreSQL 17 significatively cải thiện了CTE查询的性能。通过将路径键和列统计信息传播到上层计划，PostgreSQL大大加快了CTE查询的计划和填充速度.
#### Scalar Array Operation Expressions（SAOP）
PostgreSQL 17引入了对SAOP的处理优化，特别是对于多维查询。例如，当你有多个索引列（每个都有自己的`IN`列表）时，PostgreSQL 17可以在单个索引扫描中更高效地处理这些操作，而不是像以前版本那样进行多个扫描。这可以在CPU绑定工作负载中实现20-30%的性能提升.
#### 内部锁管理
PostgreSQL 17还改进了内部锁的管理，进一步提高了高并发工作负载的性能，特别是在B树索引中扫描多个维度时.
#### B树索引扫描
PostgreSQL 17对B树索引扫描进行了显著优化，特别是对于包含大`IN`列表或`ANY`条件的查询。这些增强减少了系统执行的索引扫描次数，从而减少了CPU和缓冲页面争用，导致查询执行速度更快.
#### JSON支持
PostgreSQL 17进一步增强了对JSON数据的支持，包括`JSON_TABLE`命令，允许将JSON数据转换为标准PostgreSQL表。同时添加了更多的JSON构造函数和查询函数，例如`JSON_EXISTS`、`JSON_QUERY`和`JSON_VALUE`.
#### MERGE命令
PostgreSQL 17对`MERGE`命令进行了更新，允许修改可更新的视图，并添加了`RETURNING`子句以及报告生成行的DML操作的功能.
### 存储和备份
#### 增量备份
PostgreSQL 17中的`pg_basebackup`添加了块级增量备份功能，允许只备份自上一次完整备份后的更改。这种功能大大提高了备份效率并减少了存储需求.
### 内存管理和WAL处理
#### 内存管理优化
PostgreSQL 17引入了一种新的内部内存结构用于VACUUM操作，减少了VACUUM过程中的内存使用量，最高可达20倍。这提高了VACUUM速度，并释放了更多的共享资源用于其他操作.
#### WAL处理优化
PostgreSQL 17显著改进了写前日志（WAL）处理，在某些高并发工作负载中实现了两倍的WAL吞吐量.
### 其他改进
#### 流I/O接口
PostgreSQL 17引入了新的流I/O接口，加速了顺序扫描（读取整个表）和更新计划统计的速度.
#### 监控和分析
`EXPLAIN`命令现在显示了本地I/O块读写所花费的时间，并添加了`SERIALIZE`和`MEMORY`选项，用于查看数据转换为网络传输所花费的时间以及使用的内存量。PostgreSQL 17还报告了索引清理的进度，并添加了`pg_wait_events`系统视图，结合`pg_stat_activity`，提供了更多关于为什么会话正在等待的见解.
这些改进使得PostgreSQL 17更加适合企业应用，提供了更好的性能、可扩展性和兼容性。
共同表表达式（CTEs）是PostgreSQL中的临时结果集，可以在SELECT、INSERT、UPDATE或DELETE语句中引用。它们增强了复杂查询的可读性和组织性，并且可以递归，这使得它们特别适用于分层数据。CTE查询的基本语法如下：
WITH cte_names AS
(– QUERY here )
Select * from cte_names;在查询中包含WITH关键字来创建CTE；父查询（定义结果集）在CTE名称之后的AS子句中。定义CTE后，您可以通过名称引用CTE的结果集，并在同一查询中对结果集执行进一步操作。
PostgreSQL 17继续增强CTE的性能和功能，包括查询规划和执行方面的改进。早期版本的Postgres将CTE视为优化围栏，意味着规划器不能将谓词推送到它们中。然而，从PostgreSQL 12开始，您可以定义更高效的执行计划。在性能关键时，您始终应分析查询并考虑执行计划。
性能提示：如果你将多次引用相同的结果集，请使用MATERIALIZED关键字创建CTE。当你创建一个材料化CTE时，Postgres会计算并存储父查询的结果。然后，如果引用CTE多次，后续查询就不需要多次执行复杂的计算。
从CTE引用中提取列统计信息，Postgres 17改进了材料化CTE
材料化CTE基本上作为一种优化围栏，这意味着一旦选择了子查询的计划，外层查询不会影响子查询的计划。外层查询可以看到CTE结果集的估计宽度和行数，因此很有意义地将子查询的列统计信息传递给外层查询的规划器。外层查询可以利用可用的任何信息，允许列统计信息传递到外层查询计划，但不会传递到CTE计划。
这个错误报告给社区包含一个简单的测试用例，可以演示这一改进对查询规划器的影响。
示例：比较Postgres 16行为与Postgres 17
首先，我们在Postgres 16中创建我们的工作区并对其运行ANALYZE；两个表和索引：
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到外部查询传递路径键
在PostgreSQL 17中，CTE功能的另一个有趣改进是从子查询传递路径键到外部查询。在PostgreSQL中，路径键是查询执行规划过程的一部分，主要用于对需要有序结果的查询（如带有ORDER BY子句的查询）进行排序和排序行，或者在需要排序的其他操作（如合并连接）中进行排序。
在Postgres 17之前，即使保证了通过索引扫描节点或排序节点来确保排序顺序，物化CTE子查询的排序顺序也不会与外部查询共享。没有保证的排序顺序允许PostgreSQL规划器选择不那么优化的计划，而具有保证的排序顺序则更有可能选择优化的计划。
在PostgreSQL 17中，如果一个公用表表达式（CTE）被物化并且具有特定的排序顺序，规划器可以在外部查询中重用这个信息，从而通过避免重复排序或启用更有效的连接方法来提高性能。正如Tom Lane在提交注释中所提到的：
“将路径键提升到外部查询的代码对于普通的
RTE_SUBQUERY子查询已经存在，但它并没有用于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
------------------------------------------------------------------------------------------------------------------------------- 聚合 (成本=987.55..987.56行=1宽度=8) (实际时间=8.777..8.778行=1循环=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的增强而有显著的不同。这是一个小例子；你可以在更大的查询中看到性能提升会非常显著。请注意，这个改进只有当CTE子查询中有 ORDER BY 子句时才有效。
针对标量数组的快速B树索引扫描
在 PostgreSQL 中，ScalarArrayOpExpr 是执行计划中的一个节点类型，用于处理涉及数组或值列表的查询操作，如 IN 或 ANY。它特别适用于比较列与一组值的情况，例如：SELECT * FROM table WHERE column = ANY(ARRAY[1, 2, 3]);。
ScalarArrayOpExpr 允许 PostgreSQL 优化涉及多次比较且使用 IN 或 ANY 的查询。PostgreSQL 17 引入了新的性能提升，使得这些操作变得更快。
在 PostgreSQL 17 中，对B树索引扫描进行了重大改进，特别是对于具有大的 IN 列表或 ANY 条件的查询，这些改进减少了系统执行的索引扫描次数，从而降低了CPU和缓冲区页面的争用，导致查询执行更快。
一个关键的改进是在处理标量数组操作表达式（SAOP）方面，这使得对B树索引的遍历更加高效，特别是对于多维查询。例如，当您有多个索引列（每个都有自己的IN列表）时，PostgreSQL 17现在可以更高效地在单个索引扫描中处理这些操作，而不是像早期版本那样进行多次扫描。这可以在页面访问之前是瓶颈的CPU绑定工作负载中带来20-30%的性能提升。
此外，PostgreSQL 17引入了对内部锁的更好管理，进一步提高了高并发工作负载的性能，尤其是在B树索引内扫描多个维度时。
我们可以用一个简单的例子来说明这一点。我们将使用与Postgres回归套件中上一个示例相同的tenk1表和数据。
我们的示例，首先在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，更重要的是，它只进行了一次索引扫描（与 Postgres 16 中的 3 次扫描相比）。随着 Postgres 17 的这一改进，标量数组操作的性能得到了大幅提高，Postgres 可以从中选择更好的优化查询计划。
在升级过程中保留逻辑复制槽和订阅
在 PostgreSQL 17 的主要升级过程中，保留逻辑复制槽和迁移订阅依赖项是添加到 PostgreSQL 的另一个逻辑复制功能。请注意，此功能只有在从 PostgreSQL 17 升级到后续版本时才有效，不支持在 Postgres 17 之前的升级。在构建逻辑复制环境时生成复制槽和复制源。然而，这些信息是特定于节点的，以记录复制状态、应用状态和 WAL 传输状态，因此它们不会作为升级过程的一部分升级。一旦发布节点升级，用户需要手动构建这些对象。
在 PostgreSQL 17 中，pg_upgrade 过程得到了改进，以引用并重新构建这些内部对象；这一功能使得在升级具有逻辑复制的节点时，复制可以自动恢复。以前，在执行主要版本升级时，用户必须删除逻辑复制槽，升级后需要与订阅者重新同步数据。这增加了升级过程中的复杂性并延长了停机时间。
在升级发布者集群时，请按照以下步骤操作：
- 确保通过执行
ALTER SUBSCRIPTION….DISABLE临时禁用对发布者的任何订阅。升级过程完成后，再启用这些订阅。 - 将新集群的
wal_level设置为logical。 - 新集群上的
max_replication_slots必须设置为大于或等于旧集群上的复制槽值。 - 插槽使用的输出插件必须安装在新集群中。
- 在升级之前，旧集群的所有更改都已复制到目标集群。
- 旧集群上的所有插槽都必须可用；您可以通过检查pg_replication_slots视图中冲突的列来确保这一点。对于旧集群上的所有插槽，冲突应为false。
- 新集群中不应有任何
pg_replication_slots视图中Temporary列值为false的插槽。新集群中不应有任何永久性逻辑复制插槽。
如果上述先决条件未满足，升级复制插槽的pg_upgrade过程将导致错误。
结论
在 PostgreSQL 17 中，社区继续致力于提高 PostgreSQL 的性能、可扩展性、安全性和企业级特性。Postgres 17 还通过添加新功能提高兼容性以及使现有功能更加强大和健壮来改善开发者体验。
在版本 17 之后，PostgreSQL 将继续发展、改进，并变得更加高性能，以满足需要更可扩展数据库的企业级应用。多年来，可扩展性（包括水平可扩展性和垂直可扩展性）已经得到提高，但通过向 PostgreSQL 添加分片能力来提高水平可扩展性确实还有改进空间。我们将看到更多逻辑复制的改进，并且 DDL 复制或复制缺失对象（如序列）以及更好的节点管理方面的更多改进。社区也认识到需要使 PostgreSQL 更加兼容，因此 Postgres 17 中的 MERGE 命令改进，以及计划在 Postgres 17 之后的更多兼容性功能。
Source:
https://dzone.com/articles/postgresql-17-a-major-step-forward-in-performance