经典案例

首页
经典案例
PostgreSQL 15 的重要功能数据库博客

PostgreSQL 15 的重要功能数据库博客

2026-01-27 12:09:07

PostgreSQL 15 的重要特性

关键要点

在 PostgreSQL 15 中，增加了多项显著特性，提升了性能、安全性和易用性，包括：

新增的 MERGE 命令，简化数据的插入、更新和删除操作。改进的 WAL 压缩，采用 zstd 和 lz4 算法，提升性能和减少磁盘空间使用。逻辑复制的新特性，允许以更细粒度的方式复制特定列数据。改善的大型数据集上的 SELECT DISTINCT 查询性能。PUBLIC schema 权限的更改，提升了数据库的安全性。

PostgreSQL 是最受欢迎的开源关系数据库系统之一，经过超过 30 年的发展，已证明其在处理复杂数据工作负载时的可靠性和稳健性。AWS 提供的服务使得 PostgreSQL 数据库的部署易于设置、管理和扩展，且具有成本效益，包括 Amazon Relational Database Service (Amazon RDS) for PostgreSQL 和 Amazon Aurora PostgreSQL 兼容版本。

大约一年前，PostgreSQL 开发者社区发布了 PostgreSQL 15，该版本带来了多项影响深远的特性，使得开发者在本地和分布式环境中管理工作负载时，可以获得显著的性能提升，尤其是在排序操作方面。新增加的 [MERGE](https//wwwpostgresqlorg/docs/current/sqlmergehtml) 命令优化了开发者体验，而追加的能力让数据库状态的观察提供了更多的洞见。

在本文中，我们将详细探讨 PostgreSQL 15 的一些重要特性，包括 MERGE 语句、新的 WAL 压缩算法、逻辑复制和 PUBLIC schema 权限的增强、分区表的改进以及一般性能优化。

前提条件

如想根据本文内容进行操作，需要完成以下前提条件：

如果您尚未创建 Aurora PostgreSQL 集群或 RDS for PostgreSQL 实例，请按照创建 Amazon Aurora PostgreSQL 兼容数据库集群或创建 PostgreSQL 数据库实例的说明进行操作。创建一个 Amazon Elastic Compute Cloud (Amazon EC2) 实例以安装 PostgreSQL 客户端，连接到 Aurora PostgreSQL 或 RDS for PostgreSQL 实例。有关说明，请参阅创建您的 EC2 资源并启动 EC2 实例。或者，您也可以通过一键设置 RDS 数据库与 EC2 计算实例的连接。安装 PostgreSQL 客户端。在 Amazon Linux 2023 中，您可以使用以下命令下载 psql 命令行工具：

bash sudo dnf install postgresql15client

MERGE 命令

MERGE 是 PostgreSQL 15 中最受期待的特性之一，因为它提供了一个单一命令，允许您执行条件插入、更新和删除。在 PostgreSQL 15 之前，您只能使用 INSERT ON CONFLICT 语句来运行上插风格的查询，但目标行需要具有唯一或排斥约束。使用 MERGE，您现在可以通过一个 SQL 语句修改行，包括插入、更新和删除，简化了数据操作并提升了开发者的整体体验，减少了复杂性并优化了数据库交互。

MERGE 命令的用法

MERGE 命令的基本语法如下：

sqlMERGE INTO targettable USING sourcetable ON joinconditionWHEN MATCHED THEN UPDATE SET column1=value1 column2=value2 WHEN NOT MATCHED THEN INSERT (column1 column2 ) VALUES (value1 value2 )

以下是语法说明：

MERGE INTO targettable 指定要合并数据的目标表。USING sourcetable 指定要合并数据的源表。ON joincondition 指定连接条件，以确定源表中哪些行与目标表中的哪些行匹配。WHEN MATCHED THEN UPDATE 指定找到匹配时的操作，此时目标表中的指定列将在源表中更新为对应的值。WHEN NOT MATCHED THEN INSERT 指定未找到匹配时的操作，此时将在目标表中插入新行，包含指定值。

示例用法

假设一个源表名为 employeeupdates 和一个目标表名为 employees，您可以连接到您的 EC2 实例并连接到运行在实例上的 PostgreSQL 数据库引擎。

创建 employees 表：

sql CREATE TABLE employees ( id SERIAL PRIMARY KEY firstname TEXT NOT NULL lastname TEXT NOT NULL salary NUMERIC NOT NULL )

向 employees 表中插入一些初始数据：

sql INSERT INTO employees (firstname lastname salary) VALUES (John Doe 50000) (Jane Doe 60000) (Bob Smith 70000)

创建 employeeupdates 表：

sql CREATE TABLE employeeupdates ( id INTEGER PRIMARY KEY firstname TEXT NOT NULL lastname TEXT NOT NULL salary NUMERIC NOT NULL )

向 employeeupdates 表中插入一些更新数据：

sql INSERT INTO employeeupdates (id firstname lastname salary) VALUES (1 Johnny Doe 55000) (2 Janie Doe 65000) (4 Alice Jones 80000)

使用 MERGE 命令更新目标表中的数据：

sql MERGE INTO employees USING employeeupdates ON employeesid = employeeupdatesid WHEN MATCHED THEN UPDATE SET firstname = employeeupdatesfirstname lastname = employeeupdateslastname salary = employeeupdatessalary WHEN NOT MATCHED THEN INSERT (id firstname lastname salary) VALUES (employeeupdatesid employeeupdatesfirstname employeeupdateslastname employeeupdatessalary)

使用 MERGE 命令将更新和插入 employeeupdates 表中的数据到 employees 表中。最终，通过对 employees 表运行 SELECT 语句，验证数据已经正确更新和插入：

sqlSELECT FROM employees

显示结果应如下：

id firstname lastname salary 3 Bob Smith 70000 1 Johnny Doe 55000 2 Janie Doe 65000 4 Alice Jones 80000 (4 rows)

通过 workmem 提高 MERGE 性能

PostgreSQL 在内存中使用临时工作区通过 workmem 参数设定来进行各种操作，例如在索引创建、排序以及为查询分组记录时存储中间结果。因此，通过增加 workmem 可以提高 MERGE 的性能。以下是一个简单示例，以 dbr5large 实例为例：

创建两个示例表并插入一些虚拟数据：

sql CREATE TABLE mergetarget (a int b int) CREATE TABLE mergesource (a int b int) INSERT INTO mergetarget SELECT i i10 FROM generateseries(1100000002) i INSERT INTO mergesource SELECT i i10 FROM generateseries(1100000001) i ANALYZE mergesource ANALYZE mergetarget

获取连接的后端进程 ID 并运行 MERGE 命令：

sql SELECT pgbackendpid()

使用 EXPLAIN ANALYZE 查看 MERGE 命令的执行计划和性能：

sql EXPLAIN ANALYZE MERGE INTO mergetarget t USING mergesource s ON ta = sa WHEN MATCHED THEN UPDATE SET b = tb 1

您将看到性能统计信息并发现执行时间。

随着后续的参数调整，如增加 workmem，您将能明显看到性能的改善。而且没有临时文件被创建，所有操作都在内存中完成。

WAL 压缩zstd 和 lz4

PostgreSQL 15 的 WAL 压缩有了显著改善，引入了 zstd 和 lz4 压缩算法。这使得事务日志记录更加高效，从而提升性能、减少磁盘空间使用并加快恢复速度。

lz4 和 zstd WAL 压缩方法在 Amazon RDS for PostgreSQL 15 中得到支持，默认的 WAL 压缩为 zstd。由于 Aurora 独特的分布存储系统，Amazon Aurora PostgreSQL 版本 15 并不支持使用 pgbasebackup、基于 pgbackupstart() 和 pgbackupstop() 的在线备份、以及 WAL 恢复过程中的预取。

zstd 算法是一种高性能的数据压缩算法，类似于 gzip 或 bzip2，但通常具有更佳的压缩比和更快的压缩速度，适用于需要高写入吞吐量的应用。lz4 算法是一个高速数据压缩算法，设计用于提供比 gzip 或 bzip2 更快的速度，同时仍然保持良好的压缩比。它通常用于需要低读取延迟的应用。

要在 PostgreSQL 15 中使用 WAL 压缩，可以设置 walcompression 参数。支持的方法包括 pglz、lz4如果 PostgreSQL 是用 withlz4 编译的和 zstd如果 PostgreSQL 是用 withzstd 编译的。以下代码展示了如何设置：

sqlSHOW walcompression SET walcompression TO onSHOW walcompression

在 Amazon RDS 中，默认启用 zstd 选项。您可以通过基准测试查看每种可用 WAL 压缩方法之间的性能差异。

以下图表展示了每种 WAL 压缩方法在不同连接数量下的事务每秒 (TPS)：

另一个图表则显示了按连接数量分别对应的 WAL 文件大小：

结果显示在 TPS 方面显著提升最高可达到 41 的提升并减少生成的 WAL 大小最高可达 79 的改善。

逻辑复制的增强

逻辑复制是一种在逻辑层级上复制数据对象及相关变更或事务的技术基于复制标识。与物理复制不同，后者复制整个数据库集群，逻辑复制允许选择性地复制数据。

在逻辑复制中，对源表所做的更改被捕获为一系列逻辑更改，然后在目标表上重放。这允许实时复制变更，同时也能够对复制的数据进行更细致的控制。

下列是逻辑复制的一些使用场景：

在地理分散的数据库之间实时复制数据在不同 PostgreSQL 版本之间复制数据复制表中的特定列或行同步多个数据库以维护数据一致性

在之前版本的 PostgreSQL 中，逻辑复制仅限于复制整个表。然而，在 PostgreSQL 15 中，逻辑复制经过增强，现在允许更精细化的复制，包括能够复制表中的特定列。

以下是在设置源 Aurora PostgreSQL 实例和目标 Aurora PostgreSQL 实例中的逻辑复制所需的步骤：

连接到源 Aurora PostgreSQL 实例并创建源数据库、表和出版物：

sql CREATE DATABASE sourcedb c sourcedb CREATE TABLE mytable ( id SERIAL PRIMARY KEY col1 TEXT NOT NULL col2 TEXT NOT NULL col3 TEXT NOT NULL) CREATE PUBLICATION testpub FOR TABLE mytable (col1 col2)

在此示例中，我们为 mytable 表创建了一个出版物，但仅复制 col1 和 col2 列。然后在目标数据库上创建一个连接到出版物的订阅。

PostgreSQL 15 的重要功能数据库博客连接到目标 Aurora PostgreSQL 实例并创建目标数据库、包含选定列的表和订阅：

sql CREATE DATABASE targetdb c targetdb CREATE TABLE mytable ( id SERIAL PRIMARY KEY col1 TEXT NOT NULL col2 TEXT NOT NULL) CREATE SUBSCRIPTION testsub CONNECTION host=ltsourcehostgt port=5432 dbname=sourcedb user=postgres password=ltpasswordgt PUBLICATION testpub

向源表插入数据并验证目标数据库：

sql INSERT INTO mytable (col1 col2 col3) VALUES (foo bar baz)

通过在目标数据库执行 SELECT 语句，验证数据是否已按预期复制。

ALTER SUBSCRIPTION SKIP

如果传入的数据违反任何约束，逻辑复制将停止，直到解决问题。使用 ALTER SUBSCRIPTION SKIP 命令，逻辑复制工作者可以跳过一个事务中的所有数据修改更改。可以通过指定日志序列号 (LSN) 来执行此操作，注意需要超级用户权限。

假设某个订阅中发生重复事务。在目标数据库日志中可见以下条目：

ERROR duplicate key value violates unique constraint

在 PostgreSQL 15 中，通过 ALTER SUBSCRIPTION SKIP 功能，您可以使用对应日志中的 LSN 跳过特定事务。

sqlALTER SUBSCRIPTION mysub SKIP (lsn = 7/185A66A8)

日志表明该事务已被跳过。

自动禁用逻辑复制错误时

默认情况下，当逻辑复制过程中出现冲突时，工作者会进入错误循环，反复尝试在后台应用同一更改。新选项disableonerror允许在出现错误时自动禁用订阅，从而打破循环。这使用户能够做出如何继续的明智决定。此外，在初始表同步过程中任何失败也将禁用订阅。此选项的默认值为 false，表示在冲突的情况下相同的错误会不断发生。

sqlALTER SUBSCRIPTION mysub SET disableonerror = true

当发布者准备好时，您可以重新启用复制：

sqlALTER SUBSCRIPTION mysub ENABLE

在模式中发布所有表

以前您只能在数据库中发布所有表，但 PostgreSQL 15 允许在特定模式中发布所有表：

sqlCREATE PUBLICATION mypub FOR ALL TABLES IN SCHEMA ltschemanamegt

SELECT DISTINCT

在 PostgreSQL 15 中，SELECT DISTINCT 查询可以使用两阶段的独特处理过程进行并行化。在第一阶段，多个并行工作者通过哈希或排序使行唯一。然后，来自并行工作者的结果合并，最后顺序执行去重，去除由于合并而出现的重复行。这使得 PostgreSQL 在处理大型数据集上的 SELECT DISTINCT 查询性能得到了改善。

以下是 PostgreSQL 14 和 PostgreSQL 15 的示例。

在 PostgreSQL 14 中：

sqlCREATE TABLE selectdistinct (id INT)INSERT INTO selectdistinct VALUES (generateseries(13000000))EXPLAIN ANALYZE SELECT DISTINCT id FROM selectdistinct

在 PostgreSQL 15 中，执行同样的操作：

sqlCREATE TABLE selectdistinct (id INT)INSERT INTO selectdistinct VALUES (generateseries(13000000))EXPLAIN ANALYZE SELECT DISTINCT id FROM selectdistinct

魔戒机场