The PostgreSQL Global Development Group recently announced the general availability of PostgreSQL 17, the latest version of the popular open-source database. This release focuses on performance improvements, including a new memory management implementation for vacuum, storage access optimizations, and enhancements for high-concurrency workloads.
While the latest GA release includes general improvements to query performance and adds more flexibility to partition management, many database administrators have highlighted the updates to vacuuming, which reduce memory usage, improve vacuuming time, and display the progress of vacuuming indexes. Vacuuming is an operation aimed at reclaiming storage space occupied by data that is no longer needed. The more efficient VACUUM operations in PostgreSQL 17 have been made possible by the new data structure, TidStore, which stores tuple IDs during VACUUM operations. The team explains:
The PostgreSQL vacuum process is critical for healthy operations, requiring server instance resources to operate. PostgreSQL 17 introduces a new internal memory structure for vacuum that consumes up to 20x less memory. This improves vacuum speed and also reduces the use of shared resources, making more available for your workload.
PostgreSQL 17 introduces enhancements to logical replication, simplifying the management of high-availability workloads and major engine version upgrades by eliminating the need to drop logical replication slots. Other recent improvements include enhanced I/O performance for workloads that read multiple consecutive blocks, improved EXPLAIN support, and better handling of IS [NOT] NULL conditions.
While the list of improvements is substantial, the release may lack a standout new feature. Laurenz Albe, senior consultant and support engineer at CYBERTEC, writes:
That's not because PostgreSQL has lost its momentum: in fact, there are more contributors today than ever before (...) Many smart people have contributed many great things over the years. Most of the easy, obvious improvements (and some difficult ones!) have already been made. The remaining missing features are the really hard ones.
The new version supports the JSON_TABLE option, which enables handling JSON data alongside regular SQL data. Similar to MySQL, JSON_TABLE() is an SQL/JSON function that queries JSON data and presents the results as a relational view.
SELECT *
FROM json_table(
'[
{"name": "Alice", "salary": 50000},
{"name": "Bob", "salary": 60000}
]',
'$[*]'
COLUMNS (
name TEXT PATH '$.name',
salary INT PATH '$.salary'
)
) AS employee;
Source: Google blog
Dave Stokes, technology evangelist at Percona and author of MySQL & JSON, writes:
JSON_TABLE() is a great addition to PostgreSQL 17. Those of us who deal with lots of JSON-formatted data will make heavy use of it.
Mehdi Ouazza, data engineer and developer advocate at MotherDuck, notes:
The last release of PostgreSQL 17 silently killed NoSQL, aka document store databases. Document store DBs were popular a couple of years ago with the explosion of web applications and APIs (thanks to REST) and the JSON format usage.
The MERGE command is another addition, enabling developers to perform conditional updates, inserts, or deletes in a single SQL statement. This simplifies data manipulation and improves performance by reducing the number of queries. On a popular Reddit thread, user Goodie__ comments:
Postgres manages to straddle the line of doing a little bit of everything, and somehow always falls on the side of doing it awesomely, which is exceedingly rare.
Cloud providers have already begun supporting the latest version of the popular open-source relational database. Amazon RDS has had it available in the preview environment since last May, and Cloud SQL, the managed service on Google Cloud, recently announced full support for all PostgreSQL 17 features.
All bug fixes and improvements in PostgreSQL 17 are detailed in the release notes.
