Google Cloud SQL for PostgreSQL 13

We explore Cloud SQL PostgreSQL 13 — A fully-managed instance of the world’s most advanced open-source relational database.

New to PostgreSQL?

PostgreSQL is a powerful, open-source object-relational database system. It shares many commonalities with other relational database platforms, including Microsoft SQL Server and MySQL.

New to Google Cloud SQL?

Part of the Google Cloud Platform ecosystem, Cloud SQL is a fully-managed database service designed to make it easy to set up, manage, and administer relational databases on Google Cloud Platform. At the time of writing, Cloud SQL supports MySQL, PostgreSQL, and SQL Server.

Did you know?

PostgreSQL started out in life in 1986 as a military-sponsored project, codenamed POSTGRES. 

Developed at the University of California at Berkeley, the project received significant backing from Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. 
Excerpt from A Brief History of PostgreSQL

PostgreSQL 13

This much-anticipated release appears to be heavily centered around scalability, helping the platform to cope with ever-increasing storage volumes. 

Improved indexing

PostgreSQL 13 boasts significant improvements to its methodology for indexing. Users who have especially large volumes of data should see an improvement in query performance and reduced storage size.

Engineers at PostgreSQL have achieved this in a number of ways. 

Optimisation has been applied to duplicate data in B-Tree indexes (the standard table index used in PostgreSQL). Duplicate data occurs when multiple leaf-nodes in the index (leaf-nodes point to a physical row in a table) contain the same value as another leaf-node and both point to the same physical row.

PostgreSQL 13 now supports an offline process to merge these duplicates indexes, reducing storage and improving query performance.

Deduplication is enabled by default, but can be disabled (although we note the process is managed lazily, and therefore should not impact DML performance).

A more intelligent query engine

PostgreSQL 13 has introduced some enhancements to its query engine. One of these is to apply greater intelligence to processing sorts needed to execute a SQL query. By “looking ahead”, the optimiser can detect multiple sorts ahead of time and structure the query execution accordingly.

A further enhancement is the ability to define extended statistics. These inform the optimiser to gather statistics on particular columns of interest, and can prevent slow running queries. The official release notes state these should “improve plans for queries with OR clauses and IN/ANY lookups over lists.”

More efficient vacuuming

The PostgreSQL team reveal in the release notes for version 13, they were often asked to provide better support for vacuuming. Vacuuming is a PostgreSQL administration task performed by DBAs to enable the database to free up storage space after rows are updated and deleted via DDL activity. Version 13 introduces parallelised vacuuming, to speed up this vacuuming process.

New data types and more flexible partitioning

Partitioning has received a boost, and partitioned tables can now be replicated as well as support for BEFORE row-level triggers (useful for example, to enforce business logic to prevent certain updates from taking place on a table).

PostgreSQL 13 includes a new DateTime() function to convert (valid) strings to a native type. The GEN_RANDOM_UUID() is now callable without the need to install additional extensions.

Security enhancements

Previously, PostgreSQL extensions had to be installed by a database superuser. In 13, this has been relaxed slightly, and superusers can now mark an extension as “trusted”, allowing that extension to be installed by non-superusers.

“PostgreSQL 13 showcases the collaboration and dedication of our global community in furthering the abilities of the world’s most advanced open source relational database. The innovations that each release brings along with its reputation for reliability and stability is the reason why more people choose to use PostgreSQL for their applications.”
Peter Eisentraut, a PostgreSQL Core Team member

Get started with PostgreSQL on Google Cloud SQL

If you are a data scientist, developer, or engineer looking for a fully-managed, Cloud-based instance of PostgreSQL, it’s really easy to get started with Cloud SQL. 

In fact, it can be free too — new customers get $300 in free credits to spend on Google Cloud during the first 90 days. This can be used for lots of other services too, such as Google BigQuery.

You can review the Cloud SQL pricing using this link.

Why Google Cloud SQL?

There are many good reasons to use Cloud SQL to manage your PostgreSQL instance. 

We have drawn out the 3 that we think are most compelling (these are taken from the official Google Cloud SQL documentation).

Secure and compliant. For peace of mind with sensitive data, all Cloud SQL data is encrypted at rest and in transit. Private connectivity with Virtual Private Cloud and user-controlled network access with firewall protection. Compliant with SSAE 16, ISO 27001, PCI DSS, and HIPAA.

Scale as you go. Scale your instances effortlessly with a single API call, whether you start with simple testing or you need a highly available database in production.

Set up in minutes. Standard connection drivers and built-in migrational tools allow you to create and connect to your first database in just a few minutes.

How to set up PostgreSQL in Cloud SQL

We finish with a short showcase, so you can see for yourself, just how simple creating a new instance is. These steps are taken from the Google Get Started documentation.

Before you start

1. You will first need a Google Cloud project. A project can contain any of the services you wish to take advantage of and not just Cloud SQL. 

To create a project, in a browser launch the Google Cloud console and select Create Project

2. Make sure that billing is enabled for your Google Cloud project. Learn how to confirm billing is enabled for your project

3. Make sure you have the Cloud SQL Admin and Compute Viewer roles on your user account. Go to the IAM page

Learn more about PostgreSQL roles and permissions in Google Cloud.

Create a PostgreSQL instance in Cloud SQL

1. Go to the Cloud SQL Instances page in the Google Cloud Console

2. Click Create instance, shown below in blue

3. Select PostgreSQL and then click Next 

4. Enter a name for your new instance

5. Enter a password for the postgres user

6. Under Location, chose a zone and region. We always advise choosing a region local to you.

7. Under Configuration options, update settings for your instance. Learn more about instance settings

8. Click Create

And you are done!

Next steps

1. Read the Google Cloud SQL PostgreSQL release note

2. Read the Google Cloud SQL documentation

3. Learn more about Ancoris Data, Analytics & AI