PostgreSQL interface

This page describes the PostgreSQL interface for Cloud Spanner and its components. It 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 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 Google Standard SQL 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 functionality like interleaved tables and query hinting. For detailed information about 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 Spanner clients for Java, Go, Python, Node.js, Ruby, PHP, C#, and C++ environments. Non-Spanner PostgreSQL drivers and ORMs are not yet supported.

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 native gRPC interface, managing connections and IAM authentication for you. For more information, see About PGAdapter.

Choose between Google Standard SQL and PostgreSQL

When creating a Spanner database, you have a choice between Google Standard SQL 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 Google Standard SQL 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 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 Spanner functionality like interleaved tables and query hinting.
  • For both, you use the Spanner management interfaces: the 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 release does not yet attain full parity between the Google Standard SQL and PostgreSQL dialects. 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 Spanner experience or by working with other Google Cloud databases like BigQuery that also support Google Standard SQL.
    • If your organization is standardizing on the use of Google Standard SQL or its underlying ANSI 2011 standard.

For more information, see Dialect parity between Google Standard SQL and PostgreSQL.

Best practices for using the PostgreSQL interface

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

  • Use the console or the Google Cloud CLI to create your PostgreSQL database in a Spanner instance. (You can create both PostgreSQL and Google Standard SQL 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 console Write DDL and Query pages.

What's next