GCP – What’s new in PostgreSQL 17, now available in Cloud SQL

We’re excited to announce support for PostgreSQL 17 in Cloud SQL, complete with many new features and valuable enhancements across five key areas:

Security
Developer experience
Performance
Tooling
Observability

In this blog post, we explore these areas in depth, providing valuable insights and examples to guide you in leveraging PostgreSQL 17 on Cloud SQL.

Enhanced security 

MAINTAIN privilege and pg_maintain role

PostgreSQL 17 introduces the MAINTAIN privilege, which lets you perform maintenance operations like VACUUM, ANALYZE, REINDEX, and CLUSTER on database objects, even if you are not the owner of those objects. This provides a more granular level of control over database maintenance tasks.

PostgreSQL 17 also introduces a predefined role called pg_maintain that allows executing maintenance operations on all relations, as if you had MAINTAIN rights on those objects, even without having it explicitly. 

For example, here is how a table owner can grant the MAINTAIN privilege on a table to a user:

For example, a postgres user can grant the pg_maintain role to a user with this command:

Developer experience enhancements

MERGE … RETURNING

The MERGE command is a powerful addition to PostgreSQL 17 that allows developers to perform conditional updates, inserts or deletes in a single SQL statement. This command not only simplifies data manipulation but also improves performance by reducing the number of separate queries.

Convert JSON data into a standard PostgreSQL table

The JSON_TABLE function in PostgreSQL 17 introduces a more intuitive way to convert JSON data into a standard table format, making it easier to work with it. Unlike earlier methods such as json_to_recordset(), which can be complex to work with, JSON_TABLE offers a cleaner and more standardized approach for transforming JSON documents into tabular form.

The output for the above query should look like this:

Performance improvements

Improved memory structure for vacuum 

PostgreSQL 17 introduces a new, more efficient data structure called TidStore to store tuple IDs during VACUUM operations. This replaces the previous array-based approach, resulting in significantly reduced memory consumption. This approach also eliminates the 1GB of memory usage limit while vacuuming the table. 

In the pg_stat_progress_vacuum system view, a couple of  new columns have been introduced to provide additional details about the vacuum process, and a couple of existing column names have been changed 

Improved I/O performance 

In PostgreSQL 17, an enhancement to the ReadBuffer API introduces reading multiple consecutive blocks from disk into shared buffers with one system call. 

This improvement is particularly beneficial for workloads that involve reading multiple consecutive blocks, as it reduces the overhead associated with multiple individual read operations. It also helps the ANALYZE operation to quickly update planner statistics.

PostgreSQL 17 also introduces io_combine_limit to control your largest I/O size for operations that combine I/O. The default is 128kB.

Improved IS [NOT] NULL handling

PostgreSQL 17 introduces optimizations to reduce the unnecessary evaluation of IS NULL and IS NOT NULL clauses. This change enhances the efficiency of queries by avoiding redundant checks and makes these operations faster, especially in complex queries or when multiple conditions involve NULL values.

For example, assuming the id column in the null_handling table is defined as NOT NULL, executing the explain plan would results to:

Given this, PostgreSQL can immediately determine that the condition “id IS NULL” will never be true, so it doesn’t need to actually access the table data. The One-Time Filter: false indicates that the condition “id IS NULL”  is not satisfied for any rows, as all values in the id column are guaranteed to be non-null.

Tooling improvements

Improved COPY Error Handling and Verbosity Control

PostgreSQL 17 introduces improvements to the COPY command with options like ON_ERROR and LOG_VERBOSITY. These options let you handle errors more gracefully and obtain better insights into skipped rows during data import operations.

Here, the third column is age(integer),  hence “abc” is an invalid data for data type integers.

Import the data with below command

PostgreSQL 17 also introduces a new column, tuples_skipped, to the pg_stat_progress_copy view, which shows the number of tuples skipped because they contain malformed data. 

pg_dump, pg_dumpall, pg_restore with –filter option

PostgreSQL 17 introduces a –filter option, which provides more fine-grained control over what objects are included or excluded in a dump or restore operation. 

For example, you can create a filter file and use it in the pg_dump:

pg_restore with –transaction-size option

The pg_restore command now includes a –transaction-size option, allowing you to commit after processing a specified number of objects.  Using the –transaction-size option lets you break down the restore process into smaller sets of objects, for more manageable transactions.

Enhanced observability

pg_wait_events system view

The new pg_wait_events system view provides information about events that are causing processes to wait. This can be helpful for identifying performance bottlenecks and troubleshooting database issues.

A simple query should look like:

SELECT * FROM pg_wait_events LIMIT 5;

By effectively using pg_wait_events with pg_stat_activity, you can gain valuable insights into the performance of your PostgreSQL database and identify areas for improvement.

For example:

Here is sample output for the above command (the output includes projected columns and truncated rows for relevance):

pg_stat_checkpointer system view

The pg_stat_checkpointer system view provides information about the performance and activity of the checkpoint process, offering valuable insights into the frequency of checkpoints, the amount of data written during checkpoints, and the time taken to complete checkpoints.

Run the following query to get insights on checkpointer activity:

This query returns a record containing various metrics related to the checkpoint process, allowing you to monitor and analyze the performance of checkpoints in the PostgreSQL instance.

Summary

In summary, Cloud SQL for PostgreSQL 17 introduces significant advancements in security, developer experience, performance, tooling, and observability. These enhancements are designed to streamline your database operations and improve database management capabilities. Refer to the official release notes for a complete list of new features and detailed information. 

We encourage you to try Cloud SQL PostgreSQL 17 today and experience the benefits of these powerful updates. For more information about Cloud SQL, explore the Cloud SQL for PostgreSQL Managed Database section. To create your Cloud SQL PostgreSQL 17 instance, click here.