PostgreSQL 17 accelerates open source database with replication and  JSON tables

The PostgreSQL 17 database is generally available today, marking a new milestone in the decades-long history of the widely deployed open-source technology.

The PostgreSQL database (sometimes also referred to simply as Postgres) is one of the most established database technologies in existence. Developers have been working on the database for more than 35 years, tracing its roots back to the University of California at Berkeley and evolving it as an open-source technology.

Technically the database development is run under the governance of The PostgreSQL Global Development Group, an association of volunteers and contributing companies that support the project with code contributions. For the PostgreSQL 17 release, there were over 450 individual contributors across many companies. Every major cloud provider including AWS, Microsoft Azure and Google Cloud has managed PostgreSQL offerings and numerous vendors including EDB, Percona, Instaclustr and Aiven also provide commercial support and technologies.

The new update marks the first major milestone since developers released PostgreSQL 16 in 2023. As with all PostgreSQL releases there is a focus on improved performance. For enterprise users, a key focus of the update is a series of innovations that will make the database easier to use and manage. PostgreSQL has always been a relational database, but with the new release, it now integrates more JSON document database capabilities, that many enterprises commonly associate with the MongoDB Atlas database.

Jonathan Katz, a core team member and contributor to the PostgreSQL Global Development Group, told VentureBeat that he sees the improvements for database administrators as being about performance, scale and ease of development. 

“There’s a heavy focus on improving foundational operations and user-facing operations,” Katz explained. “These build on scale, and particularly, if you note the logical replication features, which are used to distribute data, PostgreSQL 17 makes logical replication more reliable for production use.”

Improved memory management and query execution in PostgreSQL 17

A key challenge for all database users has long been memory management, as database administrators always seem to want more. In many databases, including PostgreSQL, there is a “vacuuming” operation that will suck up or “vacuum” up space after a row is deleted in the database. Katz explained that PostgreSQL 17 has a new memory management system for vacuum.

“Under many situations, this will significantly reduce the amount of memory it takes to complete a vacuum and can improve overall vacuuming performance,” Katz said. “In other words, PostgreSQL 17 makes vacuum more efficient and performant.”

A core operation of any database is to execute queries. Database administrators will notice potentially significant query execution gains with PostgreSQL 17. The gains will be noticeable for queries that use the ‘IN’ clause with a B-tree index which is the standard index type in PostgreSQL. An ‘IN” clause is used to check if a value matches any value in a list. There are also improvements for query planning that will further optimize performance.

Sometimes to get the benefit of SQL query optimization, a database administrator might have to rewrite a query, which can be a cumbersome process. But that’s not the case here.

“The only thing the database administrator has to do is upgrade to PostgreSQL 17, and run an ANALYZE, which they need to do after upgrading,” Katz explained. “PostgreSQL 17 will automatically optimize the execution queries with ‘IN’ clauses that use a B-tree index.”

How PostgreSQL 17 will help enterprises and their database operations

For enterprise database administrators in particular there are a series of features in PostgreSQL 17 that will make life easier. Among the key features highlighted by PostgreSQL contributor EDB are incremental backups, logical replication and SQL: JSON functionality.

Many third-party developers have long created tools for use with PostgreSQL, including tools for incremental backup. What PostgreSQL 17 does is standardize the approach and directly integrate that capability into the core open-source database. Jozef de Vries, chief product engineering officer at EDB told VentureBeat that while having lots of third-party tools is useful, it also complicates operations for organizations.

“The kind of thesis here with this incremental backup work is bringing more of that database lifecycle management functionality into the core server itself, to make the adoption, deployment, and day two activities, easier on our customers and more consistent,” de Vries said.

There are also a series of logical replication enhancements in PostgreSQL 17. Logical replication is a feature in PostgreSQL that allows for replicating data between different PostgreSQL databases. 

“One big problem PostgreSQL has had for years with logical replication is if you failover to a standby, you have to resync the replication,” Tom Kincaid, senior VP of database server and tools at EDB told VentureBeat.

Kincaid explained that if the master table in a logical replication scenario failed and a standby node got promoted to be the new master, database tables would need to be resynchronized. That process, especially with large tables, could take a long time. PostgreSQL 17 eliminates the need for the resync process.

“The logical replica will failover, which I think will save the DBA (database administrator) a lot of pain,” Kincaid said.

There is also an update that enables DBAs to more easily take a physical database replica and turn it into a logical replica.  Kincaid expects that the capability called PG create subscriber will enable faster database replication overall.

Why JSON in PostgreSQL simplifies enterprise database deployment

Over the last several years, PostgreSQL has been incrementally adding support for the SQL: JSON standard. JSON (JavaScript Object Notation) is a format that is very popular with developers and is the basis for document databases like MongoDB.

Kincaid said that PostgreSQL 17 now supports JSON tables, which is a major step forward. What that means is a database administrator can take a JSON document and turn it into a table that runs in PostgreSQL.

“JSON is still very popular for developers, and PostgreSQL has won StackOverflow’s most loved database by developers,” Kincaid said. “So we think this will make them love it even more.”