# What's New in PostgreSQL 17
> Highlights of PostgreSQL 17, including SQL/JSON, replication, vacuum, incremental backups, query planning, and monitoring.
---
LLMS index: [llms.txt](/en/llms.txt)
---
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 {#intro-section-2-1}
**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 {#intro-section-2-2}
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 {#intro-section-2-3}
**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 {#intro-section-2-4}
**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 {#intro-section-2-5}
**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 {#intro-section-2-6}
**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 {#intro-section-2-7}
**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 {#intro-section-2-8}
**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 `memory` displays **how much memory** was used to design the plan.
- The parameter `serialize` shows 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 IDENTITY` **columns**.
## Monitoring {#intro-section-2-9}
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_since` and `minmax_stats_since`).
- **Normalization of commands** `CALL`, `PREPARE TRANSACTION`, `COMMIT/ROLLBACK PREPARED` and `DEALLOCATE`: 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 {#intro-section-2-10}
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 {#intro-section-2-11}
**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_buffers` and `multixact_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 {#intro-section-2-12}
New **stock provider** with support for `C` and `C.UTF8` locales.
## Miscellaneous {#intro-section-2-13}
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.