Advancing Database Management with the Capabilities of PostgreSQL 17

With most PostgreSQL releases, there are around four to eight major features to talk about. The newest PostgreSQL 17 release is different, however, because there aren’t just eight major features, but dozens of supporting features in multiple focus areas.

All of these improvements have been made possible thanks to the growing community of Postgres contributors. As EDB Vice President and Postgres Evangelist Bruce Momjian points out, these new developers bring fresh perspectives and innovative ideas that are clearly reflected in PostgreSQL 17.

For these reasons and many others, EDB VP Chief Engineer Peter Eisentraut notes that PostgreSQL 17 is worth celebrating. We invite you to celebrate with us and explore the latest features with Peter and Bruce in our on-demand webinar.

Here are a few of the PostgreSQL 17 features highlighted in the webinar:

Watch Webinar

Incremental Backups

Among the most notable advancements in PostgreSQL 17 is the new incremental backup feature, which addresses the inefficiency and challenges of traditional backup methods.

Before this release, PostgreSQL had two main backup approaches:

1. Point-in-time backups (file system or pg_dump). Point-in-time backups are fast, but they don't allow for continuous updates, so restoring can require replaying a lot of write-ahead logs (WAL).
2. Continuous archiving/point-in-time. This feature allows restoring to any point in time, but initial full backups can be very slow and resource-intensive, especially for large databases.

The new incremental backup feature bridges this gap. It allows taking a full backup and then incrementally updating it by applying the write-ahead logs. This provides the benefits of point-in-time without the drawbacks of the initial full backup.

The new PostgreSQL 17 incremental backups:

• Can be performed on a separate machine, reducing the impact on the database server and promoting more efficient resource use
• Allow restoring to any point in time by applying the necessary incremental backups
• Provide more flexibility in backup management, as you can keep multiple incremental backups at different intervals
• Make it easy to remove older incremental backups once they’re no longer needed, simplifying backup management
• Are supplemented by the new WAL summarizer, a new internal process that maintains a summary of changes recorded in the WAL files and reduces the need to read through extensive log files

Overall, the new incremental backup feature in PostgreSQL 17 significantly improves backup and restore capabilities, making it ideal for large or busy databases where traditional backup methods can be impractical.

JSON Table Functionality

PostgreSQL 17 also introduces significant enhancements in data manipulation capabilities, particularly with JSON data. PostgreSQL has had JSON support for years, but the new JSON Table feature takes this to the next level. It allows creating virtual tables directly from JSON data, enabling you to query and manipulate JSON as if it were a regular table.

This new JSON Table feature:

• Bridges the gap between JSON and relational data – you can now treat JSON data as tables, allowing joins, grouping, and other relational operations, without cumbersome and error-prone manual parsing
• Provides a declarative syntax to extract data from JSON documents and map it to table columns
• Allows seamlessly integrating JSON data with other relational data sources

The syntax uses the JSON_TABLE function, where you specify the JSON column and provide expressions to map the JSON data into table columns. This allows extracting data from complex, hierarchical JSON documents.

Creating virtual tables from JSON data not only improves query performance but also enhances the overall flexibility and usability of PostgreSQL as a multi-model database.

Robust Copy Error Handling

PostgreSQL has long had a Copy command for bulk loading data from files, but previously it would fail the entire load if any errors were encountered in the input data. The new Copy Error Handling feature addresses this common pain point and makes Postgres’ bulk loading capabilities more practical for real-world scenarios.

• The copy command can now handle errors in the input data, rather than aborting the entire load.
• There are options to control how errors are handled, such as ignoring invalid rows.
• It solves the technical challenge of efficiently catching and handling these errors across Postgres' pluggable data types.

While this initial implementation provides basic error handling, we’ll be adding even more to this feature in the future. This flexible feature is vital for maintaining data integrity without sacrificing efficiency.

Versatile Merge Command

The Merge command, which allows combined insert/update/delete operations, isn’t a feature that everyone necessarily uses, but those who do will appreciate these enhancements:

• Merge can now be used with updatable views, whereas previously it was limited to base tables.
• Merge now supports a "RETURNING" clause, similar to INSERT and UPDATE. This allows retrieving the result of the Merge operation in a single statement.
• As part of the RETURNING clause, a new "MERGE_ACTION" function was added. This function indicates whether each row was inserted, updated, or deleted as part of the Merge.

The RETURNING clause and MERGE_ACTION function in particular address common user requests by streamlining the workflow around Merge operations.

Optimizer Efficiency Improvements

We’re excited about the improvements to Postgres' optimizer, many of which have been driven by newly active contributors. The improvements include:

Propagation of Statistics From Common Table Expressions (CTEs)

Previously, statistics and sort order information from the first CTE were not passed down to later parts of the query. PostgreSQL 17 addresses this by allowing the optimizer to generate better plans for queries involving CTEs.

Optimizations for NOT NULL Columns

Queries asking for NULL values in a NOT NULL column can be immediately determined to return no rows. Queries checking for "IS NOT NULL" on a column known to be NOT NULL can skip the unnecessary check. These optimizations remove unnecessary work for common middleware-generated queries.

Improved Handling of Correlated Subqueries

Correlated subqueries, where the subquery references columns from the outer query, are challenging to optimize. An example query that previously used a subplan in PostgreSQL 16 is now optimized to use a hash join in PostgreSQL 17.

Other Optimizer Enhancements

PostgreSQL 17 also offers improvements to the handling of boolean partitions, range values, LIMIT on partitions, GROUP BY, and parallelism.

If you’re interested in the optimizer, you can run an explanation on a couple of your queries to see the new improvements.

Enhanced Logical Replication

Logical replication, available since version 10, has often been viewed as a specialized tool with limited general usability. PostgreSQL 17 changes this by introducing key enhancements that make logical replication more user-friendly and practical for a wider range of applications. The enhancements include:

            New pg_createsubscriber tool

The new pg_create_subscriber tool simplifies the creation of logical replicas by automating many of the previously manual steps. This tool enables faster and more efficient replication, making it easier to maintain up-to-date copies of databases for analytics or other purposes.

Improved Handling of Failovers for Logical Replicas

Prior to PostgreSQL 17, logical replicas would break if the primary database failed over to a physical standby, necessitating a complete rebuild. But now, with new built-in failover support, logical replicas can continue to function seamlessly, even when the primary database fails over to a standby. This improvement is crucial for maintaining high availability and minimizing downtime in critical systems.

These logical replication enhancements address longstanding pain points around configuration complexity and resiliency. They also lay the groundwork for further improvements and wider adoption of logical replication capabilities.

A Significant Step Forward in Database Technology

With incremental backups, data warehousing optimizations, advanced logical replication and more, PostgreSQL 17 improves database management performance, scalability and resilience. We encourage you to explore these new features and consider upgrading to see the results for yourself.