On September 28, 2024, the PostgreSQL Global Development Group released PostgreSQL 17, the latest version. This post introduces the key features of the PostgreSQL 17 release.
📢 You can check the PostgreSQL release history in PostgreSQL Setup - Version & Utility.
PostgreSQL New Feature is a series.
- Key features of PostgreSQL 17 and changes to parameters and system catalogs
- New and improved features in PostgreSQL 17
- Vacuum performance improvements in PostgreSQL 17
- Incremental backup in PostgreSQL 17
In this post, we list the key features of PostgreSQL 17 and then describe the changes in parameters, default extensions, and system catalogs in PostgreSQL 17.
Key features of PostgreSQL 17
PostgreSQL 17 brings many performance improvements and new features. The key features of PostgreSQL 17 are listed below.
Key feature | Description |
Vacuum performance improvements | ● The dead tuple storage structure changed to a radix tree, reducing memory usage by up to 20 times.
● Faster vacuum processing and less WAL generated by vacuum |
Incremental backup (Incremental Backup support) | ● Incremental backup support through the pg_basebackup utility
● New pg_combinebackup utility for reconstructing a full backup from incremental backups |
Checkpoint statistics | ● New pg_stat_checkpointer view that stores statistics for the checkpointer process
● Some columns of the pg_stat_bgwriter view moved to the pg_stat_checkpointer view |
Logical replication improvements | ● New pg_createsubscriber utility converts physical replication into logical replication
● Can go straight to the synchronization phase without copying initial data |
New wait event view | ● The new pg_wait_events view can be matched with pg_stat_activity to see a session's wait state and wait reason
● PostgreSQL 17 only provides descriptions of wait events; future releases are expected to provide wait event statistics. |
EXPLAIN extensions | ● New EXPLAIN options (SERIALIZE, MEMORY)
● With track_io_timing=on, the BUFFER option shows I/O time |
New pg_maintain role and MAINTAIN privilege | ● New pg_maintain role with privileges to perform maintenance on the entire database
● New MAINTAIN privilege to perform maintenance on specific database objects |
Parameter changes in PostgreSQL 17
We compare the pg_settings system catalog against PostgreSQL 16 to identify the parameter changes in PostgreSQL 17.
Removed parameters
Parameter name | Description |
trace_recovery_messages | Logged recovery-related debugging information; removed because it is no longer needed (logs that were output when set to on are still output at DEBUG1~DEBUG4 levels) |
db_user_namespace | Simulated per-database user namespaces; removed because it was rarely used (no replacement) |
old_snapshot_threshold | Was used to prevent snapshot too old errors; removed because the feature is no longer needed |
Added parameters
Parameter name | Category | Default | Description |
allow_alter_system | Version and Platform Compatibility> Other Platforms and Clients | on | Whether the ALTER SYSTEM command is allowed (ON/OFF) |
commit_timestamp_buffers | Resource Usage > Memory | 32 | Size of the dedicated buffer pool for the commit timestamp cache (Unit:8kB) |
enable_group_by_reordering | Query Tuning> Planner Method Configuration | on | Whether reordering of GROUP BY keys is enabled (ON/OFF) |
event_triggers | Client Connection Defaults> Statement Behavior | on | Whether event triggers are enabled (ON/OFF) |
io_combine_limit | Resource Usage> Asynchronous Behavior | 16 | Limit on data read and write sizes (Unit:8kB) |
max_notify_queue_pages | Resource Usage > Disk | 1048576 | Maximum number of pages that can be allocated to the NOTIFY/LISTEN queue |
multixact_member_buffers | Resource Usage > Memory | 32 | Size of the dedicated buffer pool for the MultiXact member cache (Unit:8kB) |
multixact_offset_buffers | Resource Usage > Memory | 16 | Size of the dedicated buffer pool for the MultiXact offset cache (Unit:8kB) |
notify_buffers | Resource Usage > Memory | 16 | Size of the dedicated buffer pool for the LISTEN/NOTIFY message cache (Unit:8kB) |
restrict_nonsystem_relation_kind | Client Connection Defaults> Statement Behavior | Prohibits access to certain kinds of non-system relations | |
serializable_buffers | Resource Usage > Memory | 32 | Size of the dedicated buffer pool for the serializable transaction cache (Unit:8kB) |
subtransaction_buffers | Resource Usage > Memory | 32 | Size of the dedicated buffer pool for the subtransaction cache (Unit:8kB) |
summarize_wal | Write-Ahead Log > Summarization | off | Whether to start the walsummarizer process and enable incremental backup (ON/OFF)● Must be set to ON to use incremental backup. When enabled, the walsummarizer process is started and WAL summaries are stored in the pg_wal/summaries directory |
synchronized_standby_slots | Replication > Primary Server | Names of the replication slots of streaming replication standby servers that logical WAL sender processes wait for | |
sync_replication_slots | Replication > Standby Servers | off | Whether the standby server synchronizes the logical failover replication slots set up on the primary server, so that replication and data transfer can continue smoothly after failover (ON/OFF) |
trace_connection_negotiation | Developer Options | off | Whether to log details of the connection handshake before authentication (ON/OFF) |
transaction_buffers | Resource Usage > Memory | 32 | Size of the dedicated buffer pool for the cache that manages transaction status (Unit:8kB) |
transaction_timeout | Client Connection Defaults> Statement Behavior | 0 | Maximum time a transaction can run within a session (ms) |
wal_summary_keep_time | Write-Ahead Log > Summarization | 14400 | How long to keep WAL summary files (min) |
System catalog changes in PostgreSQL 17
We compare the system catalogs against PostgreSQL 16 to identify the changes in PostgreSQL 17.
pg_attribute
Change | Column name | Description |
Column attribute changed | attstattarget | The column's NOT NULL constraint was removed. The default statistics target was shown as a negative number (-1) up to version 16, but is shown as NULL starting with version 17 |
pg_collation
Change | Column name | Description |
Column renamed | colliculocale → colllocale |
pg_database
Change | Column name | Description |
Column added | dathasloginevt | Shows whether a login event trigger is defined (F/T); used to avoid unnecessary lookups of the pg_event_trigger table |
Column renamed | daticulocale → datlocale |
pg_replication_slots
Change | Column name | Description |
Column added | dathasloginevt | For a logical replication slot, whether it is enabled so that logical replication can resume on the new primary server after failover (F/T). Always F for physical slots |
ㅤ | inactive_since | Time at which the replication slot became inactive. NULL if the slot is currently in use |
ㅤ | invlidation_reason | If the conflicting column is True, shows why the replication slot was invalidated● NULL: the slot is not invalidated● wal_removed: required WAL was removed● rows_removed: required rows were removed● wal_level_insufficient: the primary server cannot perform logical decoding because of an incorrect wal_level parameter setting |
ㅤ | synced | Logical slot synchronized from the primary server (Boolean) |
pg_stat_bgwriter
Change | Column name | Description |
Column removed | buffers_backend | Duplicates a pg_stat_io column |
ㅤ | buffers_backend_fsync | Duplicates a pg_stat_io column |
ㅤ | buffers_checkpoint | Moved to the pg_stat_checkpointer catalog (buffers_written) |
ㅤ | checkpoints_timed | Moved to the pg_stat_checkpointer catalog (num_timed) |
ㅤ | checkpoints_req | Moved to the pg_stat_checkpointer catalog (num_requested) |
ㅤ | checkpoint_write_time | Moved to the pg_stat_checkpointer catalog (write_time) |
ㅤ | checkpoint_sync_time | Moved to the pg_stat_checkpointer catalog (sync_time) |
pg_stat_checkpointer
A new system catalog in PostgreSQL 17 that shows statistics for the checkpointer. Some of the information previously in pg_stat_bgwriter has been consolidated here.
Change | Column name | Description |
Catalog added | num_timed | Number of checkpoints run because of a timeout (includes both completed and skipped checkpoints) |
ㅤ | num_requested | Number of requested checkpoints performed (number of checkpoints run because of WAL volume) |
ㅤ | restartpoints_timed | Number of scheduled restartpoints due to timeout or after a failed attempt |
ㅤ | restartpoints_req | Number of requested restartpoints (number of restartpoints due to WAL volume) |
ㅤ | restartpoints_done | Number of restartpoints performed |
ㅤ | write_time | Total time spent in the phase of checkpoint processing where files are written to disk (ms) (file write time) |
ㅤ | sync_time | Total time spent in the phase of checkpoint processing where files are synchronized to disk (ms) |
ㅤ | buffers_written | Number of buffers written to disk during checkpoints |
ㅤ | stats_reset | Time the statistics were last reset |
pg_stat_progress_copy
Change | Column name | Description |
Column added | tuples_skipped | Number of tuples skipped because they contained invalid data. Incremented only when the ON_ERROR option of COPY is set to a value other than stop |
pg_stat_progress_vacuum
Change | Column name | Description |
Column added | dead_tuple_bytes | Amount of dead tuples that can be stored before index vacuuming, based on the maintenance_work_mem setting |
ㅤ | indexes_total | Total number of indexes to be vacuumed |
ㅤ | indexes_processed | Number of indexes processed. Incremented during index vacuuming or index cleanup |
Column renamed | max_dead_tuples → max_dead_tuple_bytes | |
ㅤ | num_dead_tuples → num_dead_item_ids |
pg_stat_slru
Change | Before | After | Description |
name column values changed | CommitTs | commit_timestamp | Stores transaction commit timestamps |
ㅤ | MultiXactmember | multixact_member | Stores the members (individual transaction IDs) of multi-transactions (MultiXact) |
ㅤ | MultiXactOffset | multixact_offset | Stores offset information for the members (transaction IDs) of multi-transactions |
ㅤ | Notify | notify | Stores notification information related to the LISTEN / NOTIFY feature |
ㅤ | Serial | serializable | Stores information about serializable transactions |
ㅤ | Subtrans | subtransaction | Manages the relationship between subtransactions and their parent transactions |
ㅤ | Xact | transaction | Stores information related to transaction status |
ㅤ | ㅤ | ㅤ | ㅤ |
pg_stat_subscription
Change | Column name | Description |
Column added | worker_type | Type of subscription worker process (apply, parallel apply, table synchronization) |
pg_stats
Change | Column name | Description |
Column added | range_length_histogram | Histogram of the lengths of non-empty, non-NULL range type values (using the subtype_diff range function) |
ㅤ | range_empty_frac | Fraction of column entries whose value is an empty range (NULL if not a range type) |
ㅤ | range_bounds_histogram | Histogram of the lower and upper bounds of non-empty, non-NULL range values (NULL if not a range type) |
pg_subscription
Change | Column name | Description |
Column added | subfailover | Whether the associated replication slot is enabled for synchronization to the standby server (Boolean) |
pg_wait_events
A new system catalog in PostgreSQL 17 that provides descriptions of wait events. It can be joined with
pg_stat_activity.wait_event_type and pg_stat_activity.wait_event.Change | Column name | Description |
Catalog added | type | Wait event type |
ㅤ | name | Wait event name |
ㅤ | description | Wait event description |
pg_stat_statements
Change | Column name | Description |
Column added | local_blk_read_time | Total time the query spent reading local blocks (ms). Requires track_io_timing to be enabled; 0 if disabled |
ㅤ | local_blk_write_time | Total time the query spent writing local blocks (ms). Requires track_io_timing to be enabled; 0 if disabled |
ㅤ | jit_deform_count | Total number of tuple deforming functions JIT-compiled by the query |
ㅤ | jit_deform_time | Total time spent JIT-compiling for the query (ms) |
ㅤ | stats_since | Time at which statistics collection for the query started |
ㅤ | minmax_stats_since | Time at which min/max statistics collection for query planning and execution time started (min_plan_time, max_plan_time, min_exec_time, max_exec_time columns) |
Column renamed | blk_read_time → shared_blk_read_time | |
ㅤ | blk_write_time→ shared_blk_write_time |
pg_stat_activity
Wait events new in PostgreSQL 17 were added to, or removed from, the
wait_event_type and wait_event columns.Change | wait_event_type | wait_event | Description |
Column value removed | LWLock | OldSnapshotTimeMap | |
Column value added | Activity | ReplicationSlotsyncMain | The slot sync worker is waiting for work in its main loop |
ㅤ | ㅤ | ReplicationSlotsyncShutdown | Waiting for the slot sync worker to shut down |
ㅤ | ㅤ | WalSummarizerWal | The WAL summarizer is waiting for more WAL to be generated |
ㅤ | Client | WaitForStandbyConfirmation | Waiting for WAL to be received and flushed to disk on the standby server |
ㅤ | IO | WalSummaryRead | Waiting to read the required data from a WAL summary file |
ㅤ | ㅤ | WalSummaryWrite | Waiting for a write to a WAL summary file to complete |
ㅤ | IPC | CheckpointDelayComplete | Waiting for backends that block checkpoint completion to finish |
ㅤ | ㅤ | CheckpointDelayStart | Waiting for backends that block a checkpoint from starting to finish |
ㅤ | ㅤ | MultixactCreation | Waiting for a MultiXact creation to complete |
ㅤ | ㅤ | WalSummaryReady | Waiting for new WAL summary information to be generated |
ㅤ | LWLock | DSMRegistry | Waiting to read or update the dynamic shared memory registry |
ㅤ | ㅤ | DSMRegistryDSA | Waiting to access the dynamic shared memory allocator from the dynamic shared memory registry |
ㅤ | ㅤ | DSMRegistryHash | Waiting to access a shared hash table in the dynamic shared memory registry |
ㅤ | ㅤ | InjectionPoint | Waiting to read or update information related to injection points |
ㅤ | ㅤ | ParallelVacuumDSA | Waiting to allocate the dynamic shared memory needed for parallel vacuum |
ㅤ | ㅤ | SerialControl | Waiting to read or update the shared pg_serial state |
ㅤ | ㅤ | WaitEventCustom | Waiting to read or update information related to custom wait events |
ㅤ | ㅤ | WALSummarizer | Waiting to read or update the WAL summarization state |
ㅤ | Timeout | WalSummarizerError | After an error in the WAL summarizer, waiting for the system to handle the error |
Changes to default extensions in PostgreSQL 17
We compare the default extensions against PostgreSQL 16 to identify the changes in PostgreSQL 17.
Removed extensions
Extension name | Description |
adminpack | Removed because pgAdmin III reached end-of-life |
old_snapshot | Removed because the old_snapshot_threshold parameter was removed |
Changes to pgrowlocks extension output
The output of the modes column has changed when running the pgrowlocks function.
PostgreSQL 16 and earlier | PostgreSQL 17 |
Key Share | For Key Share |
Share | For Share |
For No Key Update | For No Key Update |
No Key Update | No Key Update |
For Update | For Update |
Update | Update |
Related articles
