Google Spanner is a remarkable piece of technology. It’s a globally distributed, strongly consistent, relational database that scales horizontally with a 99.999% SLA. With the recent addition of granular instance sizing, it's now possible to start with spanner at $40/month andnder the hood it even uses atomic clocks! How cool is that?

If you decide to try Spanner, right off the bat you've got to choose between two dialects, and it's not clear which one is the best choice.

Spanner comes in two flavors, GoogleSQL(an ANSI-compliant SQL) and PostgreSQL. Unfortunately it's a permanent choice on a per-database basis and it will have significant impact on the code you write, so you need to choose wisely!

Google provides a guide that can help you make an informed decision regarding the choice between PostgreSQL and GoogleSQL, along with a description of the features and limitations of Postgres that are and are not supported. These documents are a good starting point for your decision-making process.

As a long-time fan of Postgres, I was initially happy that we had chosen the PostgreSQL dialect. However, after becoming more familiar with its limitations compared to GoogleSQL, the PostgresSQL dialect feels like a second-class citizen in Google Spanner. Therefore, we have recently started creating new tables in a GoogleSQL dialect database instead. Here are the details of our experience.

Poor Selection of Functions

Spanner implements many Postgres functions, but when I started to do some in-database rollups of data within time windows and so quickly reached for good old date_trunc and found it unimplemented along with date_bin. The list of supported postgres datetime methods is short and undistinguished. GoogleSQL has much better date time functions

If you think you might query by date or timestamp in your database, you should probably use GoogleSQL.

Missing PostgreSQL Emulator

Google has a spanner emulator runnable from a docker image for the GoogleSQL dialect which will enable cheap, conflict free testing in local and CI/CD environments. PostgresSQL dialect users must pay to run tests against real Spanner database instances with all the headaches that come with that approach. There’s an issue but its been almost a year since the last update.

Named Placeholder Support

The PostgreSQL spanner driver makes you write ugly code like this to build statements

Statement.Builder builder = Statement
      .newBuilder("SELECT * FROM cool_table WHERE project_id = $1 AND id >= $2")
      .bind("p1")
      .to(projectId)
      .bind("p2")
      .to(startAt);

GoogleSQL spanner driver lets you use this much more pleasant named placeholder approach

Statement.Builder builder = Statement
      .newBuilder("SELECT * FROM cool_table WHERE project_id = @projectId AND id >= @id")
      .bind("projectId")
      .to(projectId)
      .bind("id")
      .to(startAt);

This distinction is documented here

Your Mileage May Vary

The PostgresSQL dialect might be a good fit for an application already using postgres that also happens not to use any of the missing postgres features. For us, the compatibility isn’t quite there so we’ll be steadily moving towards GoogleSQL