On September 28, 2024, the PostgreSQL Global Development Group released PostgreSQL 17, the latest version. Here are the key features that come with PostgreSQL 17.
📢 You can check the PostgreSQL release status in PostgreSQL Setup - Version & Utility.
PostgreSQL New Feature is a series.
- PostgreSQL 17 key features, parameters, and system catalog changes
- PostgreSQL 17 new and improved features
- PostgreSQL 17 vacuum performance improvements
- PostgreSQL 17 incremental backup
In this article, we walk through what was added and improved in PostgreSQL 17 with tests. The tests run on PostgreSQL 17.2 and compare against PostgreSQL 16.4. The features covered here are as follows.
- Shared Buffer Flush
- NOT NULL and NULL check optimization
- Execution plan optimization for multiple conditions on the same table
- New --filter option for pg_dump
- New pg_maintain role and MAINTAIN privilege
- EXPLAIN extensions
Evicting the Shared Buffer
PostgreSQL has never had a command to clear the buffer cache like Oracle's
ALTER SYSTEM FLUSH BUFFER CACHE, and up to PostgreSQL 16 there is no way to clear shared buffers other than restarting the instance.In PostgreSQL 17, you can clear shared buffers with the pg_buffercache_evict function. To use pg_buffercache_evict, you need to install the pg_buffercache extension. We also install the pg_prewarm extension to preload data into shared buffers during the test.
Installing and Checking Extensions
Create Test Data
The last SQL using pg_buffercache shows that a total of 58 blocks of table01 are in shared buffers. Loading table01 with pg_prewarm loads 54 blocks, but the fsm (free space map) and vm (visibility map) associated with table01 are loaded as well.
Evicting table01 from Shared Buffers
Use the
pg_buffercache_evict() function to completely remove the table's buffers from shared buffers.Checking That table01 Blocks Were Removed from Shared Buffers
IS NOT NULL / IS NULL Check Optimization
PostgreSQL 17 includes an optimization for IS NULL and IS NOT NULL handling when a table column has NOT NULL set. An IS NOT NULL condition on a NOT NULL column can be removed, and an IS NULL condition on a NOT NULL column can be skipped. Let's check this with the tests below.
Create Test Data
IS NOT NULL Comparison
In PostgreSQL 16.4, even though column c1 is set to NOT NULL, IS NOT NULL is still evaluated, producing a Filter: (c1 IS NOT NULL) execution plan.
PostgreSQL 17 knows that column c1 is set to NOT NULL, so it skips IS NOT NULL and builds an execution plan with the condition removed.
IS NULL Comparison
Just as with IS NOT NULL, PostgreSQL 16.4 evaluates IS NULL, producing a Filter: (c1 IS NULL) execution plan.
PostgreSQL 17 knows that column c1 is set to NOT NULL, so it skips IS NULL and builds an execution plan with the condition removed.
Multiple Conditions on the Same Table
When querying a table with multiple conditions, PostgreSQL analyzes the SQL first, before running it, to build an efficient execution plan.
Create Test Data
The example above contains a contradiction: it looks for rows where c1=3 and c1=5 at the same time. The execution plan in PostgreSQL 16 shows that it accesses the table first (c1=5) before analyzing the SQL, and then does not access the table for the next condition. In the end, resources were wasted building the execution plan.
EXPLAIN Comparison
PostgreSQL 17 analyzes the SQL before running it and builds an execution plan that does not need to access the table at all.
New pg_dump Option
New --filter Option
Option | Description |
extension | Works like the -e/--extension or --exclude-extension option |
foreign_data | Works like the --include-foreign-data option. Only include can be used |
table | Works like the -t/--table or -T/--exclude-table option |
table_and_children | Works like the --table-and-children or --exclude-table-and-children option |
table_data | Works like the --exclude-table-data option. Only exclude can be used |
table_data_and_children | Works like the --exclude-table-data-and-children option. Only exclude can be used |
schema | Works like the -n/--schema or -N/--exclude-schema option |
📢 The same option was also added to the pg_dumpall and pg_restore utilities.
Usage
New pg_maintain Role and MAINTAIN Privilege
PostgreSQL 17 adds the pg_maintain role and the MAINTAIN privilege for database maintenance work.
pg_maintain Role
The pg_maintain role is a predefined role that grants privileges to perform database maintenance tasks. It includes maintenance privileges on the entire database, so you can easily delegate maintenance work without superuser privileges. The pg_maintain role is specialized for database maintenance; it cannot be used for data manipulation, schema changes, or other work outside maintenance.
Tasks provided by the pg_maintain role
Role | Description |
VACUUM | Cleans up unneeded data in tables |
ANALYZE | Collects table statistics |
REINDEX | Rebuilds indexes |
CLUSTER | Reorders table data in index order |
REFRESH MATERIALIZED VIEW | Brings materialized views up to date |
CHECKPOINT | Writes data changes to disk |
Granting and revoking the pg_maintain role
MAINTAIN Privilege
The MAINTAIN privilege lets a user perform maintenance tasks on specific database objects. It can be restricted to maintenance on particular objects only, and like the pg_maintain role, it makes it easy to delegate database object maintenance. The MAINTAIN privilege is specialized for maintaining specific database objects; it cannot be used for data manipulation, schema changes, or other work outside maintenance.
Tasks provided by the MAINTAIN privilege
Role | Description |
VACUUM | Cleans up unneeded data in tables |
ANALYZE | Collects table statistics |
REINDEX | Rebuilds indexes |
CLUSTER | Reorders table data in index order |
REFRESH MATERIALIZED VIEW | Brings materialized views up to date |
Granting and revoking MAINTAIN
Differences Between the pg_maintain Role and the MAINTAIN Privilege
Feature | pg_maintain Role | MAINTAIN privilege |
Applies to | Entire database | Specific objects (tables, schemas, views, etc.) |
Grant scope | Provides maintenance privileges for the entire database | Allows fine-grained privileges on specific objects |
Task scope | All maintenance tasks in the database | Only maintenance tasks on that object |
Testing the pg_maintain Role and MAINTAIN Privilege
We create users and test the pg_maintain role and the MAINTAIN privilege.
We created the test users user01 and user02, and created tables table01 and table02 with 10 rows each in the public schema. Next, we connect as each user, query the tables, and check whether VACUUM can be run.
Since no privileges have been granted to the users yet, an error message says they do not have permission to query the table or run VACUUM. In the example above we only queried and vacuumed table01, but the users have no privileges on table02 either, so querying and VACUUM fail there too. Next, we grant the pg_maintain role to user01 and the MAINTAIN privilege on table01 to user02, then query table01 and run VACUUM.
Even though user01 and user02 have been granted the pg_maintain role and the MAINTAIN privilege on table01, they still cannot manipulate table01 (SELECT, INSERT). Since VACUUM is allowed under both the pg_maintain role and the MAINTAIN privilege, VACUUM runs for each user. Next, let's check VACUUM on table02. (We skip querying the table, since the test above already showed that table manipulation is not allowed.)
user01 has the pg_maintain role, so it can perform maintenance on the entire database. It can therefore run VACUUM on table02. user02, on the other hand, only has the MAINTAIN privilege on table01, so it cannot run VACUUM on table02. In other words, the MAINTAIN privilege allows maintenance only on the specific objects it was granted for.
EXPLAIN Extensions
PostgreSQL 17 adds new options to the EXPLAIN command for more detailed query performance analysis. The
SERIALIZE and MEMORY options were added, and the BUFFERS option now shows I/O time.Category | Description |
New SERIALIZE option | Shows the time spent converting data for network transmission. It must be used with the ANALYZE option, and TEXT or BINARY can be specified as a SERIALIZE sub-option. |
New MEMORY option | Shows the amount of memory allocated for building the execution plan and the amount actually used. Note that this is not the memory used when the query actually runs. |
Improved BUFFERS option | Now shows Local I/O Read Time and Local I/O Write Time. Up to PostgreSQL 16, I/O Read Time and I/O Write Time showed only shared information; they are now Shared I/O Read Time and Shared I/O Write Time, and shared and local information are shown together. |
Create Test Data
SERIALIZE Option
The SERIALIZE option must be used with the ANALYZE option and shows the time spent converting data for network transmission. TEXT or BINARY can be specified as a SERIALIZE sub-option; if no sub-option is given, TEXT is the default.
MEMORY Option
Shows the amount of memory allocated for building the execution plan (allocated) and the amount actually used (used). Note that this is not the memory used when the query actually runs.
BUFFERS Option
PostgreSQL 17 now shows Local I/O Read Time and Local I/O Write Time, and the existing I/O Read Time and I/O Write Time were renamed Shared I/O Read Time and Shared I/O Write Time. To show these, set the parameter
track_io_timing=on.The default EXPLAIN output format is TEXT, which omits items whose value is 0. If you change the output format to JSON and run it again, you can see the Shared I/O Read Time, Shared I/O Write Time, Local I/O Read Time, and Local I/O Write Time items. The full BUFFERS results for PostgreSQL 16 and PostgreSQL 17 are in the toggles below.
EXPLAIN BUFFERS result on PostgreSQL 16.4
EXPLAIN BUFFERS result on PostgreSQL 17.2
Related Articles
