PostgreSQL 17 Release: Boosting Performance and Simplifying Data Operations

PostgreSQL 17 accelerates open source database with replication and JSON tables

Join our daily and weekly newsletters for the latest updates and exclusive content on industry-leading AI coverage. Learn More 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."

PostgreSQL 17 packs big performance and feature enhancements

In a significant milestone for the 2024 Stack Overflow developer's favorite relational database management system (DBMS), the PostgreSQL Global Development Group has released PostgreSQL 17. This latest version of the open-source DBMS introduces a host of new features and performance improvements that further solidify PostgreSQL's position as a leading database solution for enterprises and developers alike. Also: 20 years later, real-time Linux makes it to the kernel - really One of the most anticipated new features in PostgreSQL 17 is native support for incremental backups. Previously, you had to use third-party programs for this; now, it's baked into the server. This should reduce storage requirements and recovery times, making it particularly valuable for enterprise database environments where full backups are time-consuming and resource-intensive. PostgreSQL 17 significantly expands its SQL/JSON capabilities, introducing new functions like JSON_TABLE(), JSON_EXISTS(), JSON_QUERY(), and JSON_VALUE(). These additions bring PostgreSQL more in line with the SQL:2023 standard, offering developers powerful tools for interacting with JSON documents in a SQL-friendly manner. The new PostgreSQL also introduces the ability to configure the Simple Least Recently Used (SLRU) cache, which is crucial for handling subtransactions. This feature allows applications with high transaction volumes to run more efficiently without requiring significant rework of their transaction handling. PostgreSQL also -- finally -- improved logical replication, which enables you to replicate data between PostgreSQL databases. Previously, if you used logical replication to start running a standby database in case of a failure, you had to resync the replication. This ate up a lot of time. Database administrators won't have to deal with this delay from this release on. "Our work on PostgreSQL 17," noted Jozef de Vries, chief product engineering officer of EnterpriseDB, a leading PostgresSQL company, "underscores a belief that Postgres isn't just a database -- it's a data platform supporting your most critical business systems. By introducing features like incremental backups and advancing features like JSON functionality and performance, EDB is doubling down on our commitment to the open source project while enabling enterprises to 'just use Postgres' to solve their most pressing data challenges." Also: Linux was never made for the cloud, but DBOS is - and you can try it for free In addition to new features, PostgreSQL 17 comes with several performance enhancements. The most significant of these are: With its focus on advanced features and better performance, PostgreSQL 17 represents a substantial step forward for the database platform. As organizations rely increasingly on data-driven decision-making and AI applications, the enhancements in this release are likely to cement PostgreSQL's position as a go-to DBMS for modern database needs.