展望 Postgres 19

展望 Postgres 19
Looking Ahead to Postgres 19

原始链接: https://www.snowflake.com/en/blog/engineering/postgresql-19-features-beta/

PostgreSQL 19 延续了其稳健且全面发展的传统，重点关注性能、开发者体验和运维效率。主要更新包括通过 `UPDATE` 和 `DELETE FOR PORTION OF` 增强了对临时表（Temporal SQL）的支持，以及对查询规划器的重大优化。许多性能提升（例如改进的反向连接处理、增量排序和 SIMD 加速的 `COPY` 操作）都是“无感”的改进，这意味着现有应用程序在升级后将自动受益。此次版本发布的广度引人注目，在多个领域都提供了实质性的升级： * **开发者：** 改进了 SQL 语法和图查询能力。 * **运维人员：** 增强了监控、自动清理（autovacuum）和在线维护工具。 * **性能：** 更快的外键检查、精细化的排序以及更高的执行器效率。 * **生态系统：** 新的挂钩（hooks）和扩展支持，巩固了 Postgres 作为多功能平台的地位。随着 Postgres 19 即将全面上市，社区鼓励用户立即测试其工作负载。通过在测试阶段验证迁移、维护工作流和查询计划，用户有助于确保这些改进能有效地转化为现实中的稳定性与高性能。

近期 Hacker News 上关于一篇“PostgreSQL 19”文章的讨论，迅速从技术反馈演变成了一场关于 AI 生成内容的元辩论。技术贡献者们指出了对未来 Postgres 版本的功能改进建议，例如轻量级连接和同步更新的物化视图。其他人则指出了文中缺失的功能，如原生的应用级时间数据支持和查询提示。然而，讨论串中有相当一部分内容集中在对该文章完全由 AI 生成的怀疑上。批评者认为其文风缺乏真实感，并特别指出大型语言模型（LLM）不应声称自己拥有并未经历过的个人经验。这在社区内引发了一场关于“AI 垃圾内容”泛滥的激烈辩论，即文章的创作媒介是否与其实质内容同样重要。尽管一些用户认为无论来源如何，只要技术内容有实用价值即可；但另一些人则坚持认为，缺乏人类作者身份本身就是对产品本身的一种有效批评。

原文

Postgres also adds UPDATE and DELETE FOR PORTION OF, continuing work around temporal use cases. Time is one of the most common dimensions in real applications, and better temporal syntax in core SQL is a welcome direction.

Expanded RANDOM() temporal functions. (Thanks to our own Paul Ramsey and Greg Sabino Mullane for work on this one.)

Performance improvements across the board

The planner and executor continue to get steady work in Postgres 19. Special thanks to Snowflake's Tom Lane for his efforts in Postgres 19 in performance and many other areas.

There are improvements around anti-joins, semi-joins, constant folding, incremental sort with append paths, aggregate processing before joins, join selectivity computation, function statistics and more.

The easy thing to do here is list planner changes and call it done. But the more useful takeaway is this: Postgres keeps getting better at recognizing the shape of common queries and doing less unnecessary work.

Some aggregate processing can now happen before joins, which can reduce the number of rows processed. More NOT IN and LEFT JOIN cases can become efficient anti-joins. Memoize has more visibility in EXPLAIN. Sort performance improves with radix sort. Foreign key constraint checks get faster. COPY FROM text and CSV input can use SIMD instructions.

These are not always features you change application code to use. In many cases, you upgrade and Postgres simply has a better chance of doing the right thing.

That is one of the best kinds of database improvements.

Why Postgres 19 feels important

The thing that stands out to me about Postgres 19 is not one single feature. It is the breadth.

There is something for application developers: graph queries, better SQL syntax, window function improvements, better upsert behavior.

There is something for operators: REPACK CONCURRENTLY, better autovacuum, better monitoring, online checksum changes, more replication visibility.

There is something for performance-minded folks: planner improvements, SIMD improvements, asynchronous I/O visibility, faster foreign key checks, better sorts.

There is something for people building on top of Postgres: new hooks, planner advice modules, extension improvements, FDW stats retrieval and continued investment in the extension ecosystem.

That breadth is one of the reasons Postgres keeps winning. It is not just getting better for one workload or one persona. It is getting better as an application database, an operational database, an analytical database, an extensible database and a platform.

Postgres 19 is not GA yet, so now is the time to test. Try your application. Run your migration tests. Check your extensions. Look at plans for your most important queries. Exercise logical replication if you depend on it. Run your maintenance workflows. See what breaks while there is still time to fix it.

Postgres releases get better because people test them against real workloads.

And based on what is already in Postgres 19 beta, there is a lot here worth testing.