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.
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.A PostgreSQL query against a Spanner database from the Cloud Console
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.
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.
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.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.
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.
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.
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.