Mindful Containers is a fictitious company that is creating containerized microservice applications. They…
What’s change with PostgreSQL 13 on Google Cloud SQL
Today, we are announcing that Google Cloud SQL, our fully managed database service for PostgreSQL, MySQL, and SQL Server, now supports PostgreSQL 13. With PostgreSQL 13 available shortly after its community GA, you get access to the latest features of PostgreSQL while letting Cloud SQL handle the heavy operational lifting, so your team can focus on accelerating application delivery.
PostgreSQL 13 introduces performance improvements across the board, including enhanced partitioning capabilities, increased index and vacuum efficiency, and better extended monitoring. Here are some highlights of what’s new:
- Additional partitioning and pruning cases support: As part of the continuous improvements of partitioned tables in the last two PostgreSQL versions, new cases of partition pruning and direct joins have been introduced, including joins between partitioned tables when their partition bounds do not match exactly. In addition, BEFORE triggers on partitioned tables are now supported.
- Incremental sorting: Sorting is a performance-intensive task, so every improvement in this area can make a difference. Now PostgreSQL 13 introduces incremental sorting, which leverages early-stage sorts of a query and sorts only the incremental unsorted fields, increasing the chances the sorted block will fit in memory and by that, improving performance.
- Efficient hash aggregation: In previous versions, it was decided in the planning stage whether hash aggregation functionality could be used, based on whether the hash table fits in memory. With the new version, hash aggregation can be determined based on cost analysis, regardless of space in memory.
- B-tree index now works more efficiently, thanks to storage space reduction enabled by removing duplicate values.
- Vacuuming: Vacuuming is an essential operation for database health and performance, especially for demanding and critical workloads. It reclaims storage occupied by dead tuples and catalogues it in the visibility map for future use. In PostgreSQL 13, performance improvements and enhanced automations are being introduced:
- Faster vacuum: Parallel vacuuming of multiple indexes reduces vacuuming execution time.
- Autovacuum: Autovacuum can now be triggered by inserts (in addition to the existing update and delete commands), ensuring the visibility map is updating in time. This allows better tuning of freezing tuples while they are still in buffer cache.
- Monitoring capabilities: WAL usage visibility in EXPLAIN, enhanced logging options, new system views for monitoring shared memory and LRU buffer usage, and more.
- WITH TIES addition to FETCH FIRST: To ease paging, simplify processing and reduce number of statements, FETCH FIRST WITH TIES returns any additional rows that tie for the last place in the result set according to the ORDER BY clause.
Cloud SQL helps ensure you can benefit from what PostgreSQL 13 has to offer quickly and safely. With automatic patches and updates, as well as maintenance controls, you can reduce the risk associated with upgrades and stay current on the latest minor version.
To support enterprise workloads, this version is also fully integrated with Cloud SQL’s newest capabilities, including IAM database authentication for enhanced security, audit logging to meet compliance needs, and point-in-time recovery for better data protection.
IAM database authentication
PostgreSQL integration with Cloud Identity and Access Management (Cloud IAM) simplifies user management and authentication processes by using the same Cloud IAM credentials instead of traditional database passwords.
Cloud SQL IAM database authentication consolidates the authentication workflow, allowing administrators to monitor and manage users’ access in an easy and simple way. This approach brings added consistency when integrating with other Google Cloud database services especially for demanding and scaled environments.
Audit logging is enabled now in Cloud SQL for companies required to comply with government, financial, or ISO certifications. The pgaudit extension enables you to produce audit logs at the level of granularity needed for future investigation or auditing purposes. It provides you the flexibility to control the logged statements by setting configuration to specify which classes of statements will be logged.
Point-in-time recovery (PITR) helps administrators restore and recover an instance to a specific point in time using backups and WAL files when human error or a destructive event occurs. PITR provides an additional method of data protection and allows you to restore your instance to a new instance at any point in time in the past seven days. Point-in-time recovery is enabled by default when you create a new PostgreSQL 13 instance on Cloud SQL.
Getting started with PostgreSQL 13 on Google Cloud SQL
To deploy a new PostgreSQL 13 instance using Cloud SQL, you simply need to select PostgreSQL 13 from the database version drop-down menu:
To learn more about Google Cloud SQL for PostgreSQL 13, check out our documentation. Cloud SQL will continue to ensure that you get access to the latest versions and capabilities, while continuing to provide best-in-class availability, security, and integrations to meet your needs. Stay tuned for more updates across all of Google Cloud’s database engines.