PostgreSQL 17 is released with improved vacuum processing and performance improvements

The PostgreSQL global development team recently announced general availability PostgreSQL 17the 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 improvements for highly parallel workloads.

Until the last GA release contains general query performance improvements and adds more flexibility to partition management, many DBAs have highlighted updates to vacuuming that reduce memory usage, improve vacuuming time, and display index vacuuming progress. Vacuuming is an operation to reclaim storage space occupied by data that is no longer needed. More efficient VACUUM operations in PostgreSQL 17 are made possible by the new TidStore data structure, which stores tuple IDs during VACUUM operations. The team explains:

PostgreSQL’s vacuum process is critical to healthy operation, as it requires server instance resources to run. PostgreSQL 17 introduces a new internal memory structure for vacuum that consumes up to 20 times less memory. This improves vacuuming speed and also reduces the use of shared resources, making more available for your workload.

PostgreSQL 17 improves logical replication by simplifying the management of high-availability workloads and major engine version updates, eliminating the need to delete logical replication slots. Other recent improvements include improved 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 extensive, the release may be missing a standout new feature. Lorenz Albe, Senior Consultant and Support Engineer at CYBERTEC, writes:

This is not because PostgreSQL has lost its momentum: in fact, there are more contributors today than ever before (…) Lots of smart people have contributed lots of great things over the years. Most of the simple, obvious improvements (and some complex ones!) have already been made. The rest of the missing features are really complex.

The new version supports the JSON_TABLE option, which allows JSON data to be processed alongside regular SQL data. Similar to MySQL, JSON_TABLE() is a SQL/JSON function that queries JSON data and presents the results as a relational representation.

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 Stokestechnology evangelist at Percona and author of MySQL and JSON, writes:

JSON_TABLE() is a great addition to PostgreSQL 17. Those of us who deal with a lot of data in JSON format will use it heavily.

Mehdi Ouazza, Data Engineer and Developer Advocate at MotherDuck, notes:

The latest release of PostgreSQL 17 quietly eliminated NoSQL, or document store databases. Databases for document stores were popular a few years ago with the explosion of web applications and APIs (thanks to REST) ​​and the use of the JSON format.

The MERGE command is another addition that allows 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 popular Reddit threaduser Goodie__ comments:

Postgres manages to push the limit of doing a little bit of everything, and somehow always manages to do it beautifully, which is extremely rare.

Cloud providers have already started supporting the latest version a popular open source relational database. Amazon RDS made it available in the preview environment since last Mayand Cloud SQL, a managed service on Google Cloud, recently announced full support for all PostgreSQL 17 features.

All of the bug fixes and improvements in PostgreSQL 17 are detailed in the release notes.