3 great new features in Postgres 17

The PostgreSQL Global Development Group has officially released Postgres 17, marking another milestone for the community, developers, and EDB, the leading contributor to PostgreSQL code. As the reigning DB-Engines database of the year and the most desired and admired database according to the 2024 Stack Overflow developer survey, Postgres continues to cement its status as a top choice for both developers and database administrators (DBAs).

Recent Postgres releases, typically arriving in the September-October time frame, have consistently introduced key updates and features that drive its widespread adoption. In this article, I’ll highlight what I believe to be the most impactful features of Postgres 17, those that will continue to boost its adoption and make it an even more compelling option for an even greater variety of applications.

Incremental backup: High-efficiency, in-core support

For many years, Postgres users have relied on external tools and a variety of technologies to perform incremental backups. With Postgres 17, however, an in-core feature now provides a highly-optimized process for incremental backup using a granular block-level approach. Having this built directly into Postgres ensures both the stability and the availability of this feature from day one on all new major and minor versions.

This new feature also introduces a more efficient method of merging incremental backups offline into full backups, reducing the server load typically required for a full backup. Designed and implemented by EDB employee and long-time Postgres hacker and committer Robert Haas, this functionality is expected to be popular among DBAs, especially those managing on-premises Postgres deployments. Additionally, the feature includes a robust set of hooks that enable storage to many different targets, as well as support for features like compression and tarball storage.

New in-core backup functionality means faster recovery times, reducing recovery time objectives (RTO) dramatically—an essential requirement for enterprises running large databases or high-availability systems. This makes Postgres an even more attractive option for organizations where downtime has significant business impacts, such as those handling large-scale analytics and AI workloads.

SQL/JSON and JSON enhancements: Meeting developers’ needs

SQL/JSON is part of the SQL:2023 standard, providing database developers with a powerful set of functions for interacting with JSON documents in a way that feels natural to those familiar with SQL. Postgres has been gradually enhancing its SQL/JSON functionality over the past several releases, but Postgres 17 marks a significant leap forward. It introduces highly requested features like JSON_TABLE(), along with query functions such as JSON_EXISTS(), JSON_QUERY(), and JSON_VALUE(), and a rich new set of constructors for building JSON SQL objects.

In addition to the expanded SQL/JSON support, Postgres 17 adds several developer convenience features to its JSONPath implementation, further improving productivity for developers working with JSON in Postgres. As JSON is a widely used technology among developers, Postgres now offers significant new features that address their needs directly.

As Postgres continues to expand its support for JSON, it becomes an even stronger alternative to document databases for applications needing JSON document storage. With Postgres, users benefit from a unified understanding of transactional behavior, security models, indexing, and other database semantics across all data types—whether traditional relational data, geospatial data, vector data, or document data. In many cases, Postgres, through either core functionality or its extensive collection of extensions, can handle whatever data types an application needs. This also enables hybrid queries that filter across all these different data types seamlessly.

An interesting aspect of these new JSON features in Postgres 17 is the collaborative effort behind them: Nine authors from at least three different companies contributed to these advancements.

Configurable SLRU cache: Tuning for complex transactions

While the previous two features are undoubtedly among the most significant in Postgres 17, I believe this semi-behind-the-scenes feature deserves equal attention.

In Postgres, the SLRU (simple least recently used) cache is critical for handling subtransactions. In previous versions, applications that used a large number of subtransactions within a single top-level transaction often required moderate changes during migration to Postgres. In extreme cases, these changes were so extensive that the migration became impractical.

Postgres 17 introduces the ability to configure the SLRU cache, allowing applications with high transaction volumes to run more efficiently without requiring significant rework of their transaction handling. Instead of modifying the application, developers can fine-tune the internal cache(s), ensuring the business logic continues to function as expected.

Beyond simplifying migrations, this feature also provides DBAs with another set of knobs to tune Postgres, offering greater flexibility and performance for a range of applications.

Additional enhancements and performance improvements

Beyond the headline features, Postgres 17 delivers a number of smaller, yet impactful improvements. These include:

• Logical replication: Advancements that enable more flexible and reliable data replication, particularly useful for distributed databases.
• Partitioning: Significant performance improvements for table partitioning, optimizing query performance on partitioned tables.
• Maintenance tasks: Enhancements in vacuuming processes reduce overhead, improving the speed and efficiency of routine database maintenance.

Each of these features contributes to the overall robustness and scalability of Postgres 17, making it well-suited for organizations that need a high-performance, flexible, and reliable database solution.

For a comprehensive look at all the new features in this release, please see the Postgres 17 release notes.

Tom Kincaid is senior vice president of database servers and tools at EDB.

—

New Tech Forum provides a venue for technology leaders—including vendors and other outside contributors—to explore and discuss emerging enterprise technology in unprecedented depth and breadth. The selection is subjective, based on our pick of the technologies we believe to be important and of greatest interest to InfoWorld readers. InfoWorld does not accept marketing collateral for publication and reserves the right to edit all contributed content. Send all inquiries to doug_dineley@foundryco.com.