Source URL: https://cloud.google.com/blog/products/databases/postgresql-17-now-available-on-cloud-sql/
Source: Cloud Blog
Title: What’s new in PostgreSQL 17, now available in Cloud SQL
Feedly Summary: 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:
code_block
For example, a postgres user can grant the pg_maintain role to a user with this command:
code_block
<ListValue: [StructValue([(‘code’, ‘grant pg_maintain to <user_name>;’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb62835ed00>)])]>
aside_block
<ListValue: [StructValue([(‘title’, ‘$300 in free credit to try Google Cloud databases’), (‘body’, <wagtail.rich_text.RichText object at 0x3eb635aac550>), (‘btn_text’, ‘Start building for free’), (‘href’, ‘http://console.cloud.google.com/freetrial?redirectPath=/products?#databases’), (‘image’, None)])]>
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.
code_block
<ListValue: [StructValue([(‘code’, ‘SELECT *\r\nFROM json_table(\r\n \'[\r\n {“name": "Alice", "salary": 50000},\r\n {"name": "Bob", "salary": 60000}\r\n ]\’,\r\n \’$[*]\’\r\n COLUMNS (\r\n name TEXT PATH \’$.name\’,\r\n salary INT PATH \’$.salary\’\r\n )\r\n) AS employee;’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb62835ef40>)])]>
The output for the above query should look like this:
code_block
<ListValue: [StructValue([(‘code’, ‘name | salary \r\n——-+——–\r\n Alice | 50000\r\n Bob | 60000’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb62835ecd0>)])]>
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:
code_block
<ListValue: [StructValue([(‘code’, ‘postgres=> explain select * from null_handling where id is null;\r\n QUERY PLAN \r\n——————————————\r\n Result (cost=0.00..0.00 rows=0 width=0)\r\n One-Time Filter: false’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb62835efd0>)])]>
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.
code_block
<ListValue: [StructValue([(‘code’, ‘cat sample_data.csv\r\n1,John,30\r\n2,Mary,abc \r\n3,Sam,25\r\n4,Amy,35’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb62835e100>)])]>
Here, the third column is age(integer), hence “abc” is an invalid data for data type integers.
Import the data with below command
code_block
<ListValue: [StructValue([(‘code’, ‘postgres=> \\COPY test_copy from sample_data.csv (ON_ERROR ignore, LOG_VERBOSITY verbose , format csv);\r\nNOTICE: skipping row due to data type incompatibility at line 2 for column age: "abc "\r\nNOTICE: 1 row was skipped due to data type incompatibility\r\nCOPY 3’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb62835e3d0>)])]>
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:
code_block
<ListValue: [StructValue([(‘code’, ‘[postgres@hostname ~]$ cat filter.file\r\ninclude table pgbench*\r\n\r\npg_dump -d postgres –filter=filter.file -fp -f postgres.text -h <ip_address>’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb6329abc10>)])]>
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:
code_block
<ListValue: [StructValue([(‘code’, ‘postgres=> select \r\n psa.datname, \r\n psa.usename, \r\n psa.state, \r\n psa.wait_event_type, \r\n psa.wait_event, \r\n psa.query, \r\n we.description \r\nfrom \r\n pg_stat_activity psa \r\n join pg_wait_events we on psa.wait_event_type = we.type \r\n and psa.wait_event = we.name;’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb6329abf40>)])]>
Here is sample output for the above command (the output includes projected columns and truncated rows for relevance):
code_block
<ListValue: [StructValue([(‘code’, ‘|wait_event_type | query | description \r\n+—————-+————————————-+—————————-\r\n | Client | update pgbench_accounts set abalance=300; | Waiting to read data from the client\r\n | IPC | checkpoint; | Waiting for a checkpoint to start\r\n . . .’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb6329aba00>)])]>
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:
code_block
<ListValue: [StructValue([(‘code’, ‘SELECT * FROM pg_stat_checkpointer;’), (‘language’, ”), (‘caption’, <wagtail.rich_text.RichText object at 0x3eb6329ab100>)])]>
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.
AI Summary and Description: Yes
Summary: The text details the introduction of PostgreSQL 17 in Cloud SQL, highlighting significant improvements in security, developer experience, performance, tooling, and observability, which are highly relevant for database management professionals, cloud security, and compliance experts. The enhancements underscore a significant focus on operational efficiency, control, and security in cloud database services.
Detailed Description:
The announcement of PostgreSQL 17 in Cloud SQL brings several important advancements that enhance security, performance, tooling, and observability. These improvements are critical for professionals working in cloud computing, database management, or any related field, providing them with tools to optimize and secure their database operations effectively. Here are the major points covered in the announcement:
– **Security Enhancements**:
– Introduction of the **MAINTAIN privilege**, which allows users to perform maintenance tasks even without being the object owner, enhancing flexibility in database management.
– A new predefined role, **pg_maintain**, is introduced to streamline maintenance operations across the board.
– **Developer Experience Enhancements**:
– The new **MERGE … RETURNING command** allows for efficient data modifications in one statement, reducing the number of queries and improving performance.
– Introduction of **JSON_TABLE** function allows for easier conversion of JSON data into PostgreSQL tables, facilitating data handling.
– **Performance Improvements**:
– Implementing an improved **TidStore** significantly reduces memory consumption during VACUUM operations, mitigating the earlier memory usage limits.
– Enhancements in I/O performance through a revamped ReadBuffer API reduce overhead associated with read operations, which is particularly beneficial for batch or complex queries involving NULL values.
– **Tooling Improvements**:
– The COPY command now features enhanced **error handling and verbosity options** to allow better management and tracing of data import operations.
– **New filtering options** in pg_dump and pg_restore grant more control over backup and restore processes, allowing users to manage large datasets more efficiently.
– **Observability Enhancements**:
– The addition of the **pg_wait_events system view** helps identify processes that are causing delays, providing insights into performance bottlenecks.
– The **pg_stat_checkpointer system view** provides detailed metrics on checkpoint processes, facilitating the analysis of database performance.
Overall, the enhancements in PostgreSQL 17 support a more secure and efficient cloud infrastructure, which is paramount for organizations focusing on compliance and operational integrity within their database environments. The improved features collectively advocate for better security practices, enhancing the operational capabilities of developers and database administrators alike in cloud environments. Cloud SQL PostgreSQL 17 promises a comprehensive upgrade that aligns with modern database needs, emphasizing structured database management and streamlined operational workflow.
– **Key Insights and Practical Implications**:
– Security teams can leverage the new privileges and roles to enhance operational control while maintaining rigorous security standards.
– Developers benefit significantly from the added functionality that simplifies data manipulation and enhances overall performance.
– Database administrators gain new tools and insights to monitor and optimize performance, ensuring they can manage larger, more complex systems effectively.
These enhancements are crucial for organizations aiming to optimize their database solutions while ensuring compliance and robust security practices in their cloud environments.