엑셈

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