PostgreSQL interface

This page describes the Cloud Spanner PostgreSQL interface and its components. It also provides guidance on choosing between PostgreSQL and Google Standard SQL, and offers best practices for adopting and using the PostgreSQL interface.

The PostgreSQL interface makes the capabilities of Cloud Spanner—fully managed, unlimited scale, strong consistency, high performance, and up to 99.999% global availability—accessible from the open-source PostgreSQL ecosystem. It provides familiarity for developers and portability for applications. For the SQL syntax that it supports, the PostgreSQL interface uses the same semantics as PostgreSQL. This means that applications written against the PostgreSQL interface can be readily ported to another PostgreSQL environment, obviously without the non-functional benefits, like scalability and availability, that are unique to Cloud Spanner. However, 100% compatibility with PostgreSQL is not the goal.

The PostgreSQL interface provides a rich subset of the open-source PostgreSQL SQL dialect, including common query syntax, functions, and operators. Additionally, it supports a core collection of open-source PostgreSQL data types, DDL syntax, and information schema views. Applications can connect to a PostgreSQL interface-enabled Cloud Spanner database using native Cloud Spanner clients or a sidecar proxy that implements the open PostgreSQL wire protocol. Initially wire protocol support is targeted for use with psql, the popular PostgreSQL command-line tool.

Administrators provision, manage, and monitor PostgreSQL interface-enabled databases using Cloud Spanner's existing console, APIs, and tools, such as gcloud. The PostgreSQL interface is configured per database at creation time. A Cloud Spanner instance can contain both Google Standard SQL and Cloud Spanner PostgreSQL interface databases.

Components of the PostgreSQL interface

The PostgreSQL interface consists of two primary capabilities, support for the PostgreSQL SQL dialect and support for clients connecting to PostgreSQL interface-enabled databases.

PostgreSQL SQL language support

The PostgreSQL interface provides a subset of the PostgreSQL SQL dialect, including DQL, DML, and DDL, along with extensions to support Cloud Spanner functionality like interleaved tables and query hinting. For detailed information about Cloud Spanner's PostgreSQL language support, see The PostgreSQL language in Cloud Spanner.

PostgreSQL client support

Developers can connect their applications to a PostgreSQL interface database using native, open-source Cloud Spanner clients. The preview release initially supports Java, JDBC, Go, and Python environments.

The PostgreSQL interface also provides PostgreSQL wire protocol support through PGAdapter, a Google-sponsored, open-source proxy that translates between the wire protocol that open-source PostgreSQL uses and the gRPC wire protocol that Cloud Spanner uses. For more information, see About PGAdapter.

Choosing between PostgreSQL and Google Standard SQL

When creating a Cloud Spanner database, you have a choice between Google Standard SQL and open-source PostgreSQL dialects. The dialect determines the syntax and semantics of the queries and data types your applications will use as well as how they connect to the database.

The deciding factor in choosing to use PostgreSQL or Google Standard SQL for a given application or project should be business need and goals, not SQL language support for Cloud Spanner features. The SQL language dialects are peers that share the same characteristics regarding Cloud Spanner features:

  • Both are implemented on top of the same distributed storage and query processing foundation. As such they share performance, scalability, consistency, and availability characteristics.
  • Both express Cloud Spanner database functionality through a subset of their respective standard language constructs (the ANSI 2001 for one, the open-source PostgreSQL de facto standard for the other), with extensions added to support Cloud Spanner functionality like interleaved tables and query hinting.
  • For both, you use the Cloud Spanner management interfaces: the Cloud Console, the gcloud command-line tool, and the Cloud Spanner client libraries for DevOps automation.
  • For both, you use the Cloud Spanner application development interfaces: the Cloud Spanner client libraries, REST, and RPC APIs.
  • For both, you use the Cloud Spanner INFORMATION_SCHEMA and SPANNER_SYS system schemas to query database metadata and statistics.

The preview release does not yet attain full parity between the Google Standard SQL and PostgreSQL interfaces. However, longer term, you should determine whether to use PostgreSQL or Google Standard SQL for a given application or project as follows:

  • Choose PostgreSQL if the development team is familiar with the open-source PostgreSQL ecosystem, or if your organization has or is standardizing on the use of PostgreSQL.

  • Choose Google Standard SQL if the development team is familiar with Google Standard SQL, either from past Cloud Spanner experience or by working with other Google Cloud databases like BigQuery that also support Google Standard SQL, or if your organization has or is standardizing on the use of Google Standard SQL or its underlying ANSI 2011 standard.

Best practices for using the PostgreSQL interface

To use the PostgreSQL interface preview effectively, Google advises that you:

  • Use the Cloud Console or the gcloud command-line tool to create your PostgreSQL database in a Cloud Spanner instance. (You can create both PostgreSQL and Google Standard SQL databases in the same instance.)

  • Use the Cloud Spanner client libraries to connect applications that access your database.

  • Use the psql command-line tool through the PGAdapter proxy to work interactively with your database. Alternatively, you can use the Cloud Console Write DDL and Query pages.

What's next