What's new in Postgres 15?

The PostgreSQL community released version 15 (stable) in October 2022. Let's review some of the new features.

CREATE permission is revoked from all users except the database owner. It makes permission assigning more tunable (details). And for the migrated database don't forget to revoke CREATE permission on the public schema manually to fit the new policy.

There is a useful option CREATE VIEW .. WITH (security_invoker=on) to create a view that uses permissions of a view caller rather than a view creator to access underlying tables. With this, you should not worry that a user that doesn't have access to a table could see its data through a view.

Sorting rows of data is a frequent operation inside the PostgreSQL code. It is used not only when you use the ORDER BY clause, but also in indices creation, table partitioning, etc. Furthermore, sorting is one of the most algorithmically expensive operations. So increasing speed up to 4 times of both in-memory and on-disk sorts in version 15 is one of the main reasons to upgrade.

Window functions performance is also improved.

A new MERGE operation is to modify target table data according to the provided source with many available conditional processing options. This allows data processing without writing procedural language functions or complex CTE queries. Essentially merge can work as conditional delete/insert/update and also this makes PostgreSQL syntax implementation closer to SQL:2008 standard. For details on MERGE uses see: https://www.postgresql.org/docs/15/sql-merge.html

Logical replication is one of the fast-developing parts of PostgreSQL. Since version 15 two-phase commits are supported in logical replication. Also now you can choose parts of table data to logically replicate using i.e. some sets of rows and columns. In the case of replication conflict i.e. replicated data violate some constraint or there is a permission violation on a subscriber server user can choose to skip the conflicting transaction or disable the subscription until the user can decide how to handle the conflict.

Parallel execution of queries using several worker processes is another part of PostgreSQL that develops fast. In version 15 SELECT DISTINCT queries that drop duplicate rows from output can improve performance by using parallel workers.

Output log data as JSON structure helps logs processing and structured output on the client's side.

In version 15 Zstandard and ZL4 can be chosen instead of pglz as a WAL compression method. WAL compression is useful to decrease disk space usage while safeguarding data integrity.

When you make a database backup with the provided pgbasebackup extension you can also choose _Zstandard or ZL4 to compress database backups on the server side, to transfer less data over the network.

There are several new regular expressions functions that you may find useful for strings processing i.e. regexp_substr(), regexp_count(), etc. Details

You can find a full changelog and full list of new features in the official changelog.

The main reason to switch to a new PostgreSQL version is the large number of bug fixes that each new version delivers. Bug fixes aren't as prominent as new features or performance improvements, but they often have a greater impact. While some bug fixes are applied to previous versions, these changes require modifications to the ABI, interface functions, and system catalog structures, often not possible in an already-released major version.

So, the TL;DR: yes, you should update to improve stability.

Upgrading is safe thanks to the half-year stabilization period between April's code freeze of every new feature and October's stable release. Generally, you'd rather find a bug in an old version than in a new one.

Before choosing to upgrade, don't forget to check the list of rare cases that need manual actions before or after the upgrade.

If you're self-hosting, upgrading between major PostgreSQL is easily done with pg_upgrade utility or a set of recommended practices in the official docs.
If you're starting a new project on Supabase, you'll already be on PG15.
If you want to upgrade an existing project, check out our Upgrading documentation.

推荐订阅源

Supabase Blog