Jump to Content
Developers & Practitioners

New PostgreSQL Interface makes Cloud Spanner’s scalability and availability more open and accessible

October 12, 2021
Justin Makeig

Senior Product Manager

Try Google Cloud

Start building on Google Cloud with $300 in free credits and 20+ always free products.

Free trial

Customers in financial services, gaming, retail, and many other industries rely on Cloud Spanner today to power their most demanding relational database workloads that need to scale without downtime. At Google Cloud Next ’21 we announced a preview of the PostgreSQL interface for Cloud Spanner that further democratizes access to Spanner for millions of developers. This new way of working with Spanner combines the scalability and reliability of Spanner that enterprises trust with the familiarity and portability of PostgreSQL that development teams love. The PostgreSQL interface simplifies building transformational applications by allowing teams to take advantage of Spanner’s global scale, strong consistency, and industry-leading 99.999% availability using tools and skills they already have. Sign up for the preview today.

PostgreSQL has emerged as the “API” for operational databases

Moving to managed services in the cloud provides a once-in-a-generation opportunity for organizations to rethink their database usage to address scaling limits or the complexity of architectures designed in a different era. As they modernize, organizations are also looking to avoid the restrictive and expensive licensing that characterized last generation’s databases. 

Enterprises and digital natives alike are standardizing on PostgreSQL as the common “API” for their operational databases. PostgreSQL is a mature, open-source database with a large and growing ecosystem supported by multiple vendors. PostgreSQL’s rapid growth and solid technical foundation have made it a safe choice for forward-looking organizations that value flexibility. 

Spanner’s PostgreSQL interface leverages the familiarity and portability of PostgreSQL to meet development teams where they are. Developers can access the scalability and operational benefits of Spanner using industry-standard tools and skills. Teams can be assured that the schemas and queries they build against the Spanner PostgreSQL interface can be easily ported to another PostgreSQL environment, giving them flexibility and peace of mind. This is especially important for organizations in regulated industries, like financial services, that are required to demonstrate application portability to ensure business continuity.

Bringing new familiarity and portability to Spanner

At its core, Spanner’s PostgreSQL interface compiles PostgreSQL queries to Spanner’s existing distributed query processing and storage primitives. It also supports the PostgreSQL wire protocol, the communication channel used to connect clients. To a developer building an application, this is virtually transparent. Provisioning and monitoring databases use Spanner and Google Cloud’s existing tools, while interacting with data feels like PostgreSQL. The implementation touches every layer of the database to ensure a unified experience.

https://storage.googleapis.com/gweb-cloudblog-publish/images/Screen_Shot_2021-10-12_at_12.16.08_PM.max-600x600.png

Schemas, data types, and metadata

At the lowest level, the PostgreSQL interface implements a core subset of PostgreSQL data types and DDL syntax. This allows users to define their schemas using standard PostgreSQL SQL. As such, any schema you write for Spanner’s PostgreSQL interface will work in any other PostgreSQL-compatible database with full data fidelity. This gives teams the flexibility to deploy applications to multiple environments and allows them to reuse common tools and processes.

For Spanner capabilities that don’t have a direct PostgreSQL analog, like table interleaving, we‘ve extended the PostgreSQL syntax. We’ve tried to minimize these differences, deferring to existing PostgreSQL syntax where possible. Users and tools can also query the standard information schema to access metadata about a database and its schema.

SQL queries

Above the schema layer, the PostgreSQL interface implements key parts of PostgreSQL’s SQL dialect. This includes the most common DQL (SELECT) and DML (INSERT, UPDATE, DELETE) syntax along with many PostgreSQL functions and operators. Today this subset mirrors the rich existing feature set of Spanner.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image4_xrK46jI.max-1800x1800.png

 A PostgreSQL query against a Spanner database from the Cloud Console

Clients and connectivity

Finally, the PostgreSQL interface provides last-mile connectivity for applications and ecosystem tools. We’ve updated the Spanner native clients for Java, JDBC, Go, and Python to support the PostgreSQL query dialect, ensuring simple integration with the most popular development environments and up to 99.999% availability using Spanner’s fully managed global endpoint. We plan to expand this to other languages and popular ORMs going forward. 

To enable PostgreSQL tools that don’t know anything about Spanner, the PostgreSQL interface also includes an open-source adapter that runs as a sidecar proxy, close to a client. The adapter enables connectivity to a Spanner instance over the open PostgreSQL wire protocol. Initially this is focused on development scenarios using the psql command line tool to interact with a Spanner database. However, we plan to extend this to other community tools and drivers.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image2_q4VnKjL.max-800x800.png

A psql session exploring tables in a Spanner database

All this builds off of the proven distributed query and storage engine that powers Spanner. As a result, the PostgreSQL interface offers the same availability, consistency, scalability, and security guarantees as Spanner today, with negligible performance differences. This means that applications can scale up and down without expensive re-architecture or downtime, using familiar SQL and tools development teams already know.

Familiarity and portability are the goals, not 100% compatibility

This preview release is the first in a much larger, long-term investment in making Spanner more open and accessible. Initially, the Spanner PostgreSQL interface supports a core subset of the capabilities that PostgreSQL offers. By design, these align with the current features of Spanner that power a wide variety of mission-critical applications in production today. Queries and schemas that use the PostgreSQL interface will have the same semantics as other PostgreSQL environments. 100% PostgreSQL compatibility is not the goal. We’ve focused on familiarity and portability, providing easier access to Spanner’s consistency and availability at scale without reducing deployment flexibility.

Cloud SQL for PostgreSQL will continue to be the best choice in Google Cloud for applications that need the highest level of compatibility with PostgreSQL. Cloud SQL is the most direct route to lift and shift PostgreSQL applications to the cloud to take advantage of the operational and performance benefits of a fully managed service. In addition, heterogenous migrations from proprietary databases will likely be best served by Cloud SQL unless customers require some of the unique capabilities Spanner has to offer. Database Migration Service streamlines migrations with no extra charge.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image1_0FEooxM.max-1600x1600.png

Cloud SQL eases migrations with full PostgreSQL compatibility. Spanner enables transformation with PostgreSQL familiarity and portability.

​​As teams look beyond migrating existing databases as-is, building new, transformative applications, Spanner offers unmatched availability and consistency at scale, without the workarounds of last-generation’s databases, like complex application sharding or brittle replication and multi-master configurations. The PostgreSQL interface helps to future-proof that transformation investment. 

Choosing the right interface

For existing Spanner customers, Google is fully committed to continued support and evolution of Google Standard SQL, which is based on ANSI SQL. Spanner’s existing SQL dialect and ecosystem are the best choice for teams already familiar with Google Cloud. Along with a wide range of functionality, it provides compatibility with BigQuery’s SQL, allowing teams to reuse queries and concepts. Once generally available, teams that have standardized on PostgreSQL elsewhere in their stack should consider Spanner’s PostgreSQL interface first. Administrators configurare the database dialect at creation time. A Spanner instance can host multiple databases of either dialect.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image3_zRwNwQi.max-1200x1200.png

Select a dialect at database creation time using the Cloud Console

This is only the beginning of our PostgreSQL journey. Spanner will expand the PostgreSQL syntax that it supports by adding new capabilities to the database and exposing them through both the existing and new PostgreSQL interfaces. Users can eventually expect parity in functionality between both interfaces. They should choose their interface based on the ecosystem in which they operate. The capabilities and non-functional aspects, such as scalability, availability, and performance, will be the same.

Get started

The preview of Spanner’s PostgreSQL interface is open for new and existing customers at no additional cost. You can sign up today. You can also explore the preview documentation or review the demo and launch announcements from Cloud Next ’21.

Posted in