PostgreSQL 17 and its key improvements now available for pgEdge Distributed PostgreSQL

The PostgreSQL community released PostgreSQL 17 to GA on September 26,  2024. With PostgreSQL 17, community focus continues to be on making PostgreSQL more performant, scalable, secure, and enterprise ready. Postgres 17 also improves the developer experience by adding new features for compatibility, and making existing features more powerful and robust.  

pgEdge, which provides a PostgreSQL based distributed database platform for low latency, ultra-high availability, and data residency this week made PostgreSQL 17 available as a supported Postgres version in pgEdge Platform, alongside PostgreSQL versions 15 and 16. Support for PostgreSQL 17 in pgEdge Cloud will come later in Q4.pgEdge support for PostgreSQL 17 makes it available as part of a responsive multi-master cluster that offers enhanced replication capabilities like DDL replication, conflict management, conflict avoidance, and more.  pgEdge supports clusters running on a mix of different PostgreSQL versions, permitting zero downtime major version upgrades.

Recently, I blogged about some of the key logical replication features that you'll see in PostgreSQL 17 https://www.pgedge.com/blog/logical-replication-features-in-Postgres 17.

In this blog, we'll pick up where I left off.  The following sections detail the major improvements in Postgres 17 that enhance database behavior in a multi-master distributed cluster.

Logical Replication

The most notable improvements in PostgreSQL 17 are improvements to logical replication features:

• Failover slots: This is essentially a high availability feature that allows logical replication to continue working in the event of a primary failover. The feature keeps the replication slot on the primary node synchronized with the designated slots in the standby server.

• pg_createsubscriber: pg_createsubcriber is an executable included in PostgreSQL 17 that converts a physical standby server into a logical replica. This utility creates a replication setup for each of the databases that are specified in the pg_createsubscriber command. If you specify multiple databases, the utility will create a publisher node and subscriber node for each database, and all the tables within the specified database(s).

• Retention of replication slots during major upgrade: The retention of logical replication slots and migration of subscription dependencies during the major upgrade process is another logical replication feature added to PostgreSQL 17. Please note that this feature is only useful when upgrading from PostgreSQL 17 to later versions, and is not intended to support upgrades from versions prior to Postgres 17. The replication slots and replication origins are generated when building a logical replication environment. However this information is specific to the node in order to record replication status, application status and WAL transmission status so they aren’t upgraded as part of the upgrade process. Once the published node is upgraded, you'll need to manually re-construct these objects.

Storage with Incremental Backup

Block level incremental backup is a major feature added to pg_basebackup in PostgreSQL 17. The incremental backup feature allows you to only backup the changes since the last full backup. This feature will greatly improve the efficiency of backups and reduce the storage you need to use for storing backups. Instead of performing a full backup every time you can instruct the server to backup changes since the last full backup, significantly reducing the size of the backup and decreasing the time it takes to perform the backup.

Performance 

Several enhancements have been made to Postgres 17 to improve performance:

• Major improvements to common table expression (CTE) queries: By propagating information like pathkeys and column statistics to the upper level plan, PostgreSQL significantly improves query planning and populates CTE queries faster.

• Better memory management of VACUUM: The vacuum process is optimized to reduce memory usage by up to 20 times by introducing a more efficient internal memory structure for use during vacuum operations. This leads to faster execution, especially on large tables, and frees up more shared memory resources for other operations.

• Improved WAL throughput: Write ahead log handling is significantly improved in Postgres 17, allowing twice the WAL throughput in certain high concurrency workloads.

• Faster indexing: Significant improvements have been made to B-tree index scans in Postgres 17 that optimize performance, particularly for queries with large IN lists or ANY conditions. These enhancements reduce the number of index scans performed by the system, thereby decreasing CPU and buffer page contention, resulting in faster query execution.

Compatibility 

Key compatibility improvements were introduced, including MERGE command updates and better JSON support:

• MERGE command: The MERGE command is a compatibility enhancement in PostgreSQL that enables synchronization of multiple tables by combining multiple statements (i.e. INSERT, UPDATE, and DELETE) into a single statement. The MERGE statement is particularly useful for UPSERT operations (where you need to UPDATE rows if they exist and INSERT rows that don’t exist). 

The MERGE command benefits from the following improvements in Postgres 17:

• Allow the MERGE command to modify updateable views.

• WHEN NOT MATCHED on target table was previously supported; Postgres 17 adds WHEN NOT MATCHED by source to MERGE command

• The use of the RETURNING clause is now supported in the MERGE command; the new function merge_action() reports on the DML that generated the row. 

• JSON support: PostgreSQL 17 improves the search, extract, and manipulation capabilities of JSON data. It also adds support for JSON_TABLE, which allows the user to convert JSON data directly into relational table format.