国际新闻 · 中文译文与原文对照

星期日 最新 1,000 篇 · 滚动浏览

科技

PostgreSQL 19发布,具备在线表重打包及查询计划控制功能

来源:InfoQ 技术发布 上传时间 (多伦多时间)1 家来源 · 1 篇报道

中文译文

PostgreSQL全球开发团队发布了PostgreSQL 19版本,对数据库维护、查询规划、复制以及应用程序开发方面进行了改进。此次更新旨在降低管理成本并提升性能,提供了无需停机即可重新组织表结构以及控制查询规划的新工具。

其中一项重要新增功能是REPACK,它能够回收未使用的表空间,并重新排列数据以使其与索引相匹配。其CONCURRENTLY选项可使该操作在不会阻塞读写操作的情况下执行,因此有望替代VACUUM FULL和CLUSTER这些常用命令。PostgreSQL 19还引入了pg_plan_advice和pg_stash_advice这两个扩展,它们允许管理员影响查询计划的生成,并通过标识符将存储的建议应用到查询中,而无需修改应用程序代码。其他维护方面的改进包括并行索引清理、自动清理任务的优先级调整以及额外的监控视图。EXPLAIN ANALYZE及相关日志现在能够报告异步I/O操作以及为全页数据生成的WAL信息。PostgreSQL还指出,由于对外键检查的改进,批量插入操作在测试中的速度几乎提高了一倍。查询规划器的改进包括针对符合条件的NOT IN子句采用更高效的反连接方式,以及在连接之前进行提前聚合处理。

此次版本还为复制和应用程序开发添加了多项功能。逻辑复制现在支持序列值,当创建第一个逻辑复制槽时,PostgreSQL会自动将有效的WAL级别提升为逻辑级别。新的WAIT FOR LSN命令允许应用程序在查询数据之前等待备用节点重放指定的WAL位置,从而实现读写一致性。开发者还可以使用INSERT ...语句。在发生冲突时,对于获取或创建操作,将使用SELECT语句;COPY操作会输出JSON格式的数据;还新增了多种SQL/JSON字符串处理方法,同时窗口函数也支持忽略NULL值。

在安全性和兼容性方面,此次版本引入了服务器端TLS服务器名称指示(SNI)功能,使得服务器能够根据客户端使用的主机名来呈现不同的证书。PostgreSQL 19在成功完成MD5密码认证后还会发出警告。MD5在PostgreSQL 18中已被弃用,未来版本还将将其移除,建议改用SCRAM加密方式。由于JIT编译的成本估算结果不可靠,该功能现在默认处于禁用状态;而当构建版本支持时,LZ4将成为默认的TOAST压缩方法。

此次版本也在Hacker News上引发了讨论,人们在探讨生产环境用户是否应该等待PostgreSQL 19.1的发布。

另有用户建议,在关键的生产系统中,应等待首个次要版本的发布。就我个人而言,对于关键的生产系统,还是建议等待0.1版本的发布。

PostgreSQL 19现已可用。完整的变更列表可在官方发布说明中查看。

原文

The PostgreSQL Global Development Group released PostgreSQL 19, introducing changes to database maintenance, query planning, replication, and application development. The release focuses on reducing administrative overhead and improving performance, with new tools for reorganizing tables without downtime and controlling query plans.

One of the main additions is REPACK, which reclaims unused table space and can reorder data to match an index. Its CONCURRENTLY option allows the operation to run without blocking reads or writes, making it a potential replacement for common uses of VACUUM FULL and CLUSTER. PostgreSQL 19 also introduces pg_plan_advice and pg_stash_advice, extensions that let administrators influence query plan selection and apply stored advice to queries by identifier, without changing application code.

Other maintenance improvements include parallel index vacuuming, changes to autovacuum prioritization, and additional monitoring views. EXPLAIN ANALYZE and related logging now report asynchronous I/O activity and WAL generated for full-page images. PostgreSQL also reports that changes to foreign key checks made bulk inserts nearly twice as fast in testing. Query planner improvements include more efficient anti-joins for eligible NOT IN clauses and eager aggregation before joins.

The release adds several features for replication and application development. Logical replication now supports sequence values, and PostgreSQL automatically raises the effective WAL level to logical when the first logical replication slot is created. The new WAIT FOR LSN command lets applications wait until a standby has replayed a specified WAL position before querying data, providing read-your-writes consistency. Developers also get INSERT ... ON CONFLICT DO SELECT for get-or-create operations, JSON output from COPY, additional SQL/JSON string methods, and IGNORE NULLS support for window functions.

Security and compatibility changes include server-side TLS Server Name Indication (SNI), which allows a server to present different certificates based on the hostname used by the client. PostgreSQL 19 also warns after successful MD5 password authentication. MD5 was deprecated in PostgreSQL 18 and is planned for removal in a future release, with SCRAM recommended instead. JIT compilation is now disabled by default because its cost estimates proved unreliable, while LZ4 becomes the default TOAST compression method when supported by the build.

The release has also prompted discussion on Hacker News about whether production users should wait for PostgreSQL 19.1.

Another user recommended waiting for the first minor release on critical production systems:

Personally, I would say, for critical production systems, it's still good to wait for the .1 release.

PostgreSQL 19 is available now. The complete list of changes is available in the official release notes.