PostgreSQL interface for Spanner

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

The PostgreSQL interface makes the capabilities of Spanner—fully managed, unlimited scale, strong consistency, high performance, and up to 99.999% global availability—accessible from the open source PostgreSQL ecosystem. It includes a core subset of the PostgreSQL SQL dialect, support for the psql command-line tool, native language clients, and integration into existing Google tools, like Dataflow. Unlike other services that manage actual PostgreSQL database instances, Spanner uses PostgreSQL-compatible syntax to expose its existing scale-out capabilities. This provides familiarity for developers and portability for applications, but not 100% PostgreSQL compatibility. Existing applications that rely on PostgreSQL features such as stored procedures, triggers, extensions, or configurable isolation levels will require rework to run on Spanner. However, the SQL syntax that Spanner supports is semantically equivalent PostgreSQL, meaning schemas and queries written against the PostgreSQL interface can be easily ported to another PostgreSQL environment.

The PostgreSQL interface supports common PostgreSQL SQL syntax, including queries, functions, and operators. Additionally, it supports many data types, DDL syntax, and information schema views. Applications can connect to a PostgreSQL interface-enabled Spanner database using native Spanner clients or PGAdapter, a lightweight proxy that implements the open PostgreSQL wire protocol. Initially wire protocol support is targeted for use with the psql command-line tool.

Administrators provision, manage, and monitor PostgreSQL interface-enabled databases by using Spanner's existing console, APIs, and tools, such as the gcloud CLI. The PostgreSQL interface is configured per database at creation time. A Spanner instance can contain both GoogleSQL and PostgreSQL interface dialect databases. Because they share the same underlying distributed database engine, both database dialects have the same scalability, consistency, performance, and security characteristics.

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 dialect support

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

PostgreSQL client support

Developers can connect their applications to a PostgreSQL interface database using open source Spanner clients for Java, Go, Python, Node.js, Ruby, PHP, C#, and C++ environments. They can also use the open source Spanner JDBC driver and the driver for Go's database/sql package.

The PostgreSQL interface also supports the psql command-line tool. psql is an interactive environment for running queries, exploring metadata, and loading data, common in many PostgreSQL environments. psql support is enabled by PGAdapter, a lightweight proxy that translates the PostgreSQL wire protocol into Spanner's built-in gRPC interface, managing connections and IAM authentication for you. For more information, see PGAdapter overview.

The PostgreSQL JDBC driver and PostgreSQL pgx driver drivers are also supported with PGAdapter. For a full list of supported PostgreSQL drivers, see PostgreSQL drivers and ORMs.

Choose between GoogleSQL and PostgreSQL

When creating a Spanner database, you have a choice between GoogleSQL and PostgreSQL dialects. The dialect determines the syntax and semantics of the queries and data types your applications use, as well as how they connect to the database.

The deciding factor in choosing to use GoogleSQL or PostgreSQL for a given application or project should be business need and goals, not SQL language support for Spanner features. The SQL language dialects are peers that share the same characteristics regarding 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 Spanner features 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 Spanner features like interleaved tables and query hinting.
  • For both, you use the Spanner management interfaces: the Google Cloud console, the gcloud CLI, and the Spanner client libraries for DevOps automation.
  • For both, you use the Spanner application development interfaces: the Spanner client libraries, REST, and RPC APIs.
  • For both, you use the Spanner INFORMATION_SCHEMA and SPANNER_SYS system schemas to query database metadata and statistics.

The current PostgreSQL interface doesn't attain full parity between the GoogleSQL and PostgreSQL dialects. However, longer term, you should determine whether to use PostgreSQL or GoogleSQL 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 GoogleSQL:

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

For more information, see Dialect parity between GoogleSQL and PostgreSQL.

Best practices for using the PostgreSQL interface

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

  • Use the Google Cloud console or the Google Cloud CLI to create your PostgreSQL database in a Spanner instance. (You can create both PostgreSQL and GoogleSQL databases in the same instance.)

  • Use the 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 Google Cloud console Spanner Studio page.

What's next