2 What's New in PostgreSQL 17
Web edition note. This chapter contains several source errors and version qualifications. Read the corrections for PostgreSQL 17 alongside the original text below.
If you are familiar with the previous versions of PostgreSQL, this chapter can give you a sense of what has changed over the past year. It mentions only some of the updates; for the full list of changes, see the Release Notes: postgrespro.com/docs/postgresql/17/release-17. You can also follow our blog to keep up with the news: https://habr.com/en/companies/postgrespro/articles/.
SQL Commands
Improved MERGE command. The new WHEN NOT MATCHED BY SOURCE clause now lets you work with rows from the target relation which are not in the source, the RETURNING clause is now supported, and you can use views as targets same way you did with tables.
The COPY FROM command can now be forced to ignore errors caused by invalid value formats in some columns, courtesy of the new parameter on_error. And the parameter log_verbosity will display the skipped values as NOTICE-level messages. A future enhancement may allow pushing invalid values into a separate table.
You can now alter generated table expressions using the command ALTER COLUMN with the SET EXPRESSION clause. Previously, the only option was to remove it.
The abbreviation AT LOCAL for the current time zone can now be used instead of AT TIME ZONE with a time zone value, as defined in the SQL standard.
Functions and types
The overloaded function random now accepts two parameters to define the minimum and maximum values for the random number. Supports int, bigint and numeric values.
The type interval now supports infinite values.
In addition to to_hex for hexadecimal, similar functions are added for binary (to_bin) and octal (to_oct) systems.
New format masks for the to_timestamp function: TZ (for Time Zone) and OF (Offset from UTC). Previously, you had to use to_char.
The function xmltext, as defined in the SQL Standard, converts the input string to XML, properly escaping special characters.
New Unicode functions: unicode_assigned checks if every character in a string has a valid Unicode value, unicode_version returns the Unicode version for PostgreSQL, and icu_unicode_version returns the ICU version.
Parameter names have been assigned to some aggregate function definitions with more than one parameter.
SQL/JSON
The glorious epic that is implementation of the 2016 SQL/JSON Standard has now concluded. The missing functions JSON_EXISTS, JSON_QUERY, JSON_VALUE and JSON_TABLE are finally in, and the jsonpath language gets new methods for data conversion into bigint, boolean, date, decimal, integer, number, string, time_tz, time, timestamp_tz and timestamp.
Logical replication
Upgrading is much easier now. The pg_upgrade tool migrates replication slots to the publisher, so subscribers just have to update the connection string. And when upgrading the subscriber, all subscriptions stay intact and replication continues without the need to resynchronize.
A logical replica can use a physical one as a base: it already has all the tables synchronized, so setting up the replication will not take long. The new tool pg_createsubscriber switches the physical replica into read-write mode, creates the publisher and subscriber pairs in one or several databases on both servers, and specifies the position to continue replicating from in the subscriptions’ properties.
Logical slots are migrated to replicas and kept up to date. After switchover, all you have to do is update the connection string to point to the new server, and the replication resumes.
You can check what replication workers are doing in the pg_stat_subscription view’s new column worker_type.
Possible values are: apply, parallel apply, and table synchronization.
The columns conflicting and invalidation_reason in the pg_stat_replication view show if and why the logical replication slot became invalid.
In addition to that, logical decoding and, consequently, logical replication have been significantly optimized for cases with large numbers of subtransactions.
Vacuum
Vacuum is now much less memory-intensive thanks to the new dead tuples storage utilizing radix trees. Additionally, tuple ID lookup is faster, memory is allocated dynamically (used to be all maintenance_work_mem at once), and the 1 GB hard cap is no more. Now, most tables will be vacuumed in one pass, avoiding repeat index scans.
Vacuum monitoring has improved. There are two new columns in pg_stat_progress_vacuum: total number of indexes to be vacuumed (indexes_total) and the number of indexes already vacuumed (indexes_processed).
WAL size is now smaller in cases where rows are frozen during vacuuming. Both events are treated as one record in WAL.
Backup and upgrade
Incremental backup on the page level has been added to PostgreSQL. The process walsummarizer (with the parameter summarize_wal) reads WAL and records which pages are modified. The pg_basebackup tool’s new parameter --incremental enables incremental backup and utilizes the new replication protocol features. pg_combinebackup is the recovery tool that assembles the full backup from the incremental backup and all the backups it depends on.
You can specify the database to connect to as the parameter dbname in pg_basebackup and pg_receivewal. The former (with the -R key) will also add the database name to primary_conninfo when writing to postgresql.auto.conf. Neither tool needs the database name as such, but it is required when connecting over a proxy and when setting up replication slot synchronization on a replica.
New keys for pg_dump: --filter specifies a file with a list of objects to be included into or excluded from the dump. --exclude-extension excludes a list of extensions.
Quicker upgrades with pg_upgrade for databases with multiple large objects and tables. pg_dump now groups the large objects in the backup index, and pg_restore commits changes in transactions of the size specified in --transaction-size. Additionally, for parallel recovery, the leader process logic is improved.
Access control
New privilege MAINTAIN for ANALYZE, VACUUM (including VACUUM FULL), CLUSTER, REINDEX, REFRESH MATERIALIZED VIEW and LOCK TABLE. Membership in pg_maintain grants this privilege for every relation in the database.
Direct connection over TLS is now available for client applications using libpq. Enabled by setting the parameter sslnegotiation to requiredirect.
New default privilege display: psql returns (none) if there are no privileges, and also respects \pset null.
Query planning and execution
Materialized CTE statistics are now available to the planner. This includes CTE column statistics and row sorting order.
The planner can swap tables in GROUP BY, for example, to utilize index or incremental sorting.
Functions with subtransactions are now PARALLEL SAFE. This includes, in particular, PL/pgSQL functions with EXCEPTION blocks. Additionally, the parallel section of the plan can also include InitPlan nodes.
Improvements for the EXPLAIN command:
The parameter
memorydisplays how much memory was used to design the plan.The parameter
serializeshows the cost of conversion of the query result into a text or binary format to be sent to the client (particularly for TOAST assembly).Improved readability of SubPlan and InitPlan nodes.
Many indexing features have always been available for B-trees only. Now, other index types can take advantage of them too:
- Hash indexes allows for identifying altered rows on the logical replication subscriber.
Incremental sorting works with GiST and SP-GiST indexes.
BRIN indexes can be created in parallel.
Some partitioning improvements:
You can now set exclusion constraints.
Full support for
GENERATED AS IDENTITYcolumns.
Monitoring
The new view pg_stat_checkpointer displays checkpointer process statistics, previously available in pg_stat_bgwriter. It also shows replica restart points statistics. Columns buffers_backend and buffers_backend_fsync are gone from pg_stat_bgwriter, since pg_stat_io now shows more accurate data about the processes.
pg_stat_statements view:
New columns tracking full statistics reset times and minimum and maximum time statistics (
stats_sinceandminmax_stats_since).Normalization of commands
CALL,PREPARE TRANSACTION,COMMIT/ROLLBACK PREPAREDandDEALLOCATE: their parameters are replaced with constant values, so the commands will be recorded as a single event.
Range type column statistics are now available in pg_stats.
Extension developers can now define their own wait events. The pioneers are the people behind the dblink and postgres_fdw extensions.
Wait event types and descriptions are available in the new view pg_wait_events as well as in the documentation.
Event triggers
You can finally create an ON LOGIN trigger for database connection events.
Event triggers work for the REINDEX command.
The parameter event_triggers enables or disables event triggers, primarily for debugging purposes.
Configuration parameters
The parameter old_snapshot_threshold, first introduced in PostgreSQL 9.6, is gone, after issues with implementation and efficiency came up.
The new parameter transaction_timeout limits total transaction time. Old statement_timeout and idle_in_transaction_session_timeout parameters did not guarantee that a transaction will complete or terminate within a specific timeframe.
The parameter huge_pages_status shows if any huge pages were allocated if huge_pages = try.
Some new configuration parameters define SLRU cache sizes, allowing for more fine-tuning:
commit_timestamp_buffers— time of commit,multixact_member_buffersandmultixact_offset_buffers— multitransactions,subtransaction_buffers— nested transactions,notify_buffers— asynchronous messages,
transaction_buffers— transaction statuses,serializable_buffers— serializable transaction conflicts.
The ALTER SYSTEM command now can record custom parameters to postgresql.auto.conf. The parameter allow_alter_system can block ALTER SYSTEM from accidentally altering the configuration.
Localization
New stock provider with support for C and C.UTF8 locales.
Miscellaneous
You can use the key --synch-method to set the disc synchronization method for some utilities: initdb, pg_basebackup, pg_checksums, pg_rewind, pg_upgrade and pg_dump. The default method is fsync.
The walsender process reads WAL records from buffers when possible, bypassing the file system.
Previously, PostgreSQL sent a separate system call for each page and hoped that the OS would combine these operations on its end. Now, the multiblock read infrastructure exists: a sequence of pages (up to io_combine_limit bytes at a time) can be retrieved in one pass. Asynchronous reading is to be added in the future, enabling page processing as they come in, without waiting for the operation to complete.