Changes coming in PostgreSQL 17

The PostgreSQL project has released beta versions of PostgreSQL 17 containing several interesting security and usability improvements, alongside the usual performance improvements and bug fixes. If the release proceeds according to the usual timeline, the full release of version 17 is expected in September or October. The most important changes are in what PostgreSQL does when a database supervisor has their credentials revoked, and added support for incremental database backups.

Security

PostgreSQL 17 includes a few changes that could affect compatibility, in the name of security. Most noticeable is a change by Jeff Davis to how functions invoked during maintenance operations locate objects in the database. Normally, when an SQL statement refers to a table, data type, function, or other object by a name, PostgreSQL determines which object is being referred to using a search path that defines the order in which schemas are searched. This presents a possible security risk: if someone could influence the contents of the search path, they could make a privileged database operation call a function of their design with the same name as some built-in function. So, in PostgreSQL 17, privileged maintenance operations will default to using a safe search path — either the default, which should be fine for most users, or a search path declared during the creation of a function, so that the function author can be sure that each name will always refer to the same object.

That isn't the only change that makes running maintenance operations safer, however. Nathan Bossart added a new permission, MAINTAIN, that can be granted per-table or per-role. This means that now maintenance operations don't need general administration privileges, and the permissions can be more narrowly scoped. Unlike the search-path change, this change is backward compatible — although it may prompt some administrators to restructure their permission systems.

Another security-related change is a tweak to how PostgreSQL handles the SET SESSION AUTHORIZATION command that allows superusers to temporarily act as other users — the database equivalent of su. In previous versions, anyone who was a superuser at the time their database connection opened could issue the command. This led to the theoretical problem of someone opening a privileged connection, losing their superuser status, and then using their open connection to get it back. Of course, the far more common case is that of a new administrator having to close and reopen their connection to be able to use it. In PostgreSQL 17, Joseph Koshakow fixed this behavior and the command only cares about a user's superuser status at the time it is issued.

A similar issue affecting logical replicas has also been fixed by Vignesh C. If the user who owns a logical replication subscription has their superuser access revoked, the process that applies the updates for logical replication, the apply worker, will now be automatically restarted, instead of continuing to run with superuser-level access.

Administration

There is also one feature that "must not be regarded as a security feature" — a configuration option to disable ALTER SYSTEM that LWN covered in April, by Jelte Fennema-Nio and Gabriele Bartolini. The feature is a boon for system administrators, however, because it allows them to ensure that PostgreSQL is only configured via files, not via ad-hoc invocations of ALTER SYSTEM.

Administrators may also enjoy the addition of incremental backups to the pg_basebackup tool by Robert Haas, Jakub Wartak, and Tomas Vondra. Incremental backups are likely to be faster than full backups for most databases, since they only need to record pages of the database that have changed. Which pages have changed is determined using the database's write-ahead log (WAL) — the log of database operations that is used to ensure that writes to the database are durable. A new background process reads the WAL files and produces a summary of which pages have changed. The administrator can configure its behavior using some new settings. Incremental backups may not be possible if something is slowing that process down too much (such as the database dealing with too many writes).

Other complications include the fact that PostgreSQL does not track the relationship between incremental backups — that responsibility is left to the server administrator. They must retain the last full backup, and all of the incremental backups made after it, and the WAL segment and history files that must be retained for a normal backup as well. This complexity is somewhat nerve-racking to see in a backup system, but the project does provide pg_combinebackup, a utility that can turn a full backup and a series of incremental backups into a full backup. So one possible workflow would be to take incremental backups, and use them to update an existing full backup offline, without impacting the operation of the database.

Finally, PostgreSQL 17 adds some additional options to the COPY FROM command and EXPLAIN queries. The COPY FROM command allows users to import data from a file into a table. It now has the option to discard rows that would cause an error (such as failing to parse a given type, or causing a constraint violation), as well as options to increase the verbosity of what is logged about the copying process. These changes required more work than one might assume; the final version included in the release ended up including changes from Damir Belyalov, Atsushi Torikoshi, Alex Shulgin, Jian He, Yugo Nagata, and Bharath Rupireddy.

EXPLAIN can now be asked to report the memory used by the SQL-query optimizer during execution of a query, and to separately report the cost of converting data for transmission over the network. Its output has also been cleaned up slightly, which should hopefully make EXPLAIN results easier to read. This work also involved contributions from many people: Ashutosh Bapat, Stepan Rutz, Matthias van de Meent, Nazir Bilal Yavuz, Tom Lane, and Dean Rasheed.

Optimizations

Databases are usually made more performant in one of two ways: optimizing the basic operations of the database, or making the SQL-query planner smarter, so that the database can do fewer basic operations in the first place. PostgreSQL 17 includes both kinds of optimization. Vondra and Van de Meent made it so that block-range indexes can be built in parallel; Peter Geoghegan and Van de Meent made using B-Tree indexes more efficient when looking up groups of values.

Vacuuming the database — removing unused rows and pages from the middle of a table — has also become much more efficient. Melanie Plageman, Heikki Linnakangas, Masahiko Sawada, John Naylor, and Thomas Munro all contributed various fixes that should speed up vacuuming in one way or another. The amount of memory used during vacuuming may go up slightly, because the default buffer size has been increased.

Sometimes developers will use object-relational mappers, or other abstractions that sit on top of the database. Those abstractions can introduce redundancies in their SQL queries that a knowledgeable human is unlikely to write by hand. Some of the improvements to the optimizer in PostgreSQL 17 are aimed at removing those redundancies. For example, David Rowley, Richard Guo, and Andy Fan made it so that IS NOT NULL and IS NULL checks can be automatically removed from queries when a column is known to be NOT NULL. Rowley also added partition pruning for IS UNKNOWN and IS NOT UNKNOWN conditions on boolean columns.

The other improvements are more generally applicable, and include things such as allowing for greater parallelism, allowing some kinds of sub-queries to be converted to joins, and allowing the optimizer to consider the statistics of columns referenced in previous clauses. Overall, users of PostgreSQL should expect to see some modest speedups on queries where the new optimizations apply. The project's recommendations for getting good performance from a database remain the same — primarily, that one should start by running their queries through EXPLAIN to see what steps the database will actually have to go through to answer the query.

Overall, PostgreSQL 17 is more of the same from the PostgreSQL project — a large release with improvements to performance, maintenance, and security, that nonetheless preserves backward compatibility in most cases. Information on where to download the beta release can be found on the PostgreSQL snapshots page. The project does not recommend upgrading production servers yet, but does recommend installing the beta release in non-production environments to test it and ensure there are no lurking regressions.