The PostgreSQL Global Community team released the PostgreSQL 17 RC1. We’re excited to share a glimpse of some of the key features coming in PostgreSQL 17. For a deeper dive and thorough testing guidance, stay tuned as we’ll be releasing a series of blogs covering the top features in PostgreSQL 17 in more detail. Keep an eye out for those updates

Top features in Postgres 17

• MERGE / SPLIT partitions
• Invent SERIALIZE option for EXPLAIN
• Added new COPY option LOG_VERBOSITY
• Added support for incremental backup
• Added support event triggers on authenticated login
• Enhanced JSON functions
• Logical Replication Enhancement

MERGE / SPLIT partitions

The new ALTER TABLE … MERGE PARTITIONS command introduces a powerful DDL feature that allows you to combine multiple partitions into a single partition within the target table. The target partition is created using the createPartitionTable() function, which leverages the parent partition as a template for the new structure.

Similarly, the ALTER TABLE … SPLIT PARTITION command enables you to split a single partition into multiple partitions. Like the merge command, the new partitions are also created using the createPartitionTable() function, following the parent partition as a template.

Both commands provide a more flexible and efficient way to manage partitioning in your PostgreSQL tables, improving the handling of large datasets AND simplifying the management of large, partitioned tables. These features offer greater flexibility and efficiency, making routine database tasks smoother and more effective.

Invent SERIALIZE option for EXPLAIN

The SERIALIZE option for EXPLAIN (ANALYZE, SERIALIZE) allows you to measure the data volume and time taken to convert query results into a network-ready format, without sending data to the client. This lets you analyze the cost of data decompression or retrieving external data during formatting, without the added network overhead.

Add a new COPY option LOG_VERBOSITY

This new COPY option is named LOG_VERBOSITY, which controls the level of detail in the messages produced during processing. The available settings are ‘default’ and ‘verbose’. When using COPY FROM with the ON_ERROR option set to ignore, selecting ‘verbose’ will generate a NOTICE message for each row that is discarded. These messages provide detailed information, including the line number, column name, and the problematic value, helping users identify and address issues with rows that failed to load.

Add support for incremental backup

The new incremental backup feature in PostgreSQL 17 is a game-changer for database administrators. By allowing you to back up only the data that has changed since the last backup, this feature saves both time and storage space. Instead of creating full backups every time,. To create an incremental backup, first use the UPLOAD_MANIFEST command to upload the previous backup’s manifest, which can be a full or incremental backup. Then, run BASE_BACKUP with the INCREMENTAL option, using the –incremental=PATH_TO_MANIFEST flag in pg_basebackup.

Incremental backups work like full backups but replace some files with INCREMENTAL.${ORIGINAL_NAME} versions and include extra lines in the backup_label file. You can use the new pg_combinebackup tool to combine a full backup with a series of incremental backups to reconstruct the data directory.

Add support event triggers on authenticated login

Support has been added for event triggers that activate upon connection, which can be useful for tasks such as logging, connection checks, or customizing the environment. To use this feature, you create a function that returns an event_trigger and then define an event trigger for the login event, following the same process as with other triggers.

To minimize connection overhead when no login triggers are active, a new pg_database.dathasloginevt flag has been introduced. This flag indicates whether a database has active login triggers. It is set by the CREATE/ALTER EVENT TRIGGER command and cleared at connection time if no active triggers are found.

Enhanced JSON functions

PostgreSQL 17 enhances JSON data handling by introducing new JSON path functions, including JSON_TABLE. This function enables you to convert JSON data directly into a relational table format, simplifying the process of querying JSON data with SQL, without the need for manual unpacking or transformation.

Logical Replication Enhancement in PG-17

PostgreSQL 17 introduces several notable improvements in logical replication. A new utility, pg_createsubscriber, allows you to create a logical replica directly from a physical standby server.

New features include the ability to failover logical slots with an optional argument in pg_create_logical_replication_slot(), and a new server variable, sync_replication_slots, which supports synchronization of failover logical slots.

Logical replication now also supports hash indexes on the subscriber side, improves performance in scenarios with many subtransactions, and includes additional controls for logical replication failover in CREATE/ALTER SUBSCRIPTION.

Additionally, pg_logical_emit_message() gains a flush option for durability, and you can specify physical standbys to be synchronized before they become visible to subscribers, managed by the synchronized_standby_slots variable.

Other enhancements include a worker type column in pg_stat_subscription and automatic re authentication for apply workers if the subscription owner’s superuser privileges are revoked.