The PostgreSQL interface for Spanner lets you take advantage ofSpanner's fully managed, scalable, and highly availableinfrastructure using familiar PostgreSQL tools and syntax. This pagehelps you understand the capabilities and limitations of the PostgreSQLinterface.

Benefits of the PostgreSQL interface

• Portability: the PostgreSQL interface provides access tothe breadth of Spanner features, using schemas, queries, andclients that are compatible with open source PostgreSQL. Thissimplifies moving an application built on Spanner to anotherPostgreSQL environment. This portability provides deploymentflexibility and supports disaster recovery scenarios, such as a stressedexit.
• Familiarity: if you already use PostgreSQL, you can quickly getstarted with Spanner using many of the same PostgreSQLstatements and tools. Using PostgreSQL across your databaseportfolio means fewer variations between specific products and a common setof best practices.
• Uncompromisingly Spanner: because it's built onSpanner's existing foundation, thePostgreSQL interface provides all ofSpanner's existing availability, consistency, and price-performancebenefits without having to compromise on any of the capabilities availablein the complementary GoogleSQL ecosystem.

Unsupported PostgreSQL features on Spanner

It's important to understand that the PostgreSQL interfaceprovides the capabilities of Spanner through schemas, types,queries, and clients that are compatible with PostgreSQL. It doesn'tsupport all of the features of PostgreSQL. Migrating an existingPostgreSQL application to Spanner, even usingthe PostgreSQL interface for Spanner, likely requires some rework to accommodateunsupported PostgreSQL capabilities or differences in behavior, likequery optimization or primary key design. However, once it's migrated, yourworkloads can take advantage of Spanner's reliability and uniquemulti-model capabilities.

The following list provides more information on supported and unsupportedPostgreSQL features:

• Supported PostgreSQL functionality: thePostgreSQL interface supports many of the most commonly usedfeatures of PostgreSQL. This includes core parts of the schema andtype system, many common query shapes, a variety of functions and operators,and the key aspects of PostgreSQL's system catalog. Applicationscan use many PostgreSQL clients by connecting overSpanner's implementation of the PostgreSQL wire protocol.
• Some PostgreSQL language features aren't supported: extensions,user-defined data types, user-defined stored procedures, and other featuresaren't supported. For a complete list, seeThe PostgreSQL language in Spanner.There are also some features in PostgreSQL that behave differentlyfrom open source PostgreSQL. For more information, seeKnown issues in the PostgreSQL interface for Spanner.
• Spanner and Spanner control plane: databases withPostgreSQL interfaces use Spanner and Google Cloudtools to provision, secure, monitor, and optimize instances.Spanner doesn't support tools, such as pgAdmin for administrativeactivities.
• Client and wire protocol support: Spanner supports the corequery capabilities of the PostgreSQL wire protocol usingPGAdapter, a lightweight proxy that runs alongside yourapplication. This lets many Spanner clients work as-is with aSpanner PostgreSQL interface database, whileleveraging Spanner's global endpoint and connection management andIAM authentication. Google's internal benchmarking shows thatPGAdapter doesn't add any noticeable additional latency compared todirect connection to Spanner's built-in endpoints.

Administration and Management

The PostgreSQL interface supports the administration andmanagement of your Spanner databases with the following features:

• Unified experience: provision, manage, and monitor PostgreSQLinterface-enabled databases using Spanner's existing console,APIs, and tools like Google Cloud CLI.
• Flexible configuration: configure thePostgreSQL interface per database at creation time. A singleSpanner instance can accommodate bothGoogleSQL and PostgreSQL interface databases.
• Shared benefits: both database dialects share the same underlyingdistributed database engine, ensuring consistent scalability, consistency,performance, and security.

Features

Spanner's PostgreSQL interface offers two primaryfeatures that enable integration with the PostgreSQL ecosystem:

• PostgreSQL dialect support

  Spanner provides a subset of the PostgreSQL SQLdialect, including Data Query Language (DQL), Data Manipulation Language(DML), and Data Definition Language (DDL). Additionally, it includesextensions to support Spanner-specific features likeinterleaved tables,time to live (TTL), andquery hints.

  For detailed information on the supported PostgreSQL languageelements, seeThe PostgreSQL language inSpanner. Tounderstand how to use Spanner features with thePostgreSQL dialect, consult the documentation for the specificfeature.

• PostgreSQL client support

  Spanner lets you connect to databases from a variety ofclients:

  • PostgreSQL ecosystem tools: you can use familiartools like thePostgreSQL JDBC driverandPostgreSQL pgx driver to connectyour applications to a PostgreSQL interface database. Fora list of supported drivers, ORMs, and tools seePostgreSQL drivers and ORMs.

  • psql command-line tool: the popularpsql interactive environmentis supported, letting you run queries, explore metadata, and load datadirectly from your terminal.

  • PGAdapter: this lightweight proxy simplifies connectionmanagement and authentication. For more details, refer to thePGAdapter overview.

  • Spanner clients: Spanner providesopen source Spanner clients for various programminglanguages (Java, Go, Python, Node.js, Ruby, PHP, C#, C++), along with aSpanner JDBC driverand adriver for Go's SQL package.Spanner clients connect directly toSpanner's global endpoint without a proxy. However,Spanner clients don't offer compatibility with existingPostgreSQL clients, ORMs, or tools.

Best practices for using the PostgreSQL interface

Use the following best practices when using the PostgreSQL interface:

1. Connect your applications: use theset of supported PostgreSQLtools for efficient connectivity.

2. Interact with your database: for interactive work, choosebetween the following:

   • The familiarpsql command-line tool(using thePGAdapter proxy)
   • The intuitiveSpanner Studiopage within the Google Cloud console
   • IDEs, suchDBeaverandVisual Studio Code,JetBrains,andIntelliJ
   • The Spanner emulator which lets you emulateSpanner on your local machine. This is useful during thedevelopment and test process.

What's next

• Learn how tochoose between PostgreSQL andGoogleSQL.
• Follow thequickstart tocreate and interact with a PostgreSQL database.
• Learn more aboutSpanner's PostgreSQL languagesupport.
• Learn aboutPGAdapter.
• Learn about thePGAdapter GitHubrepository.
• Reviewknown issues inthe PostgreSQL interface.