Discover on Google Cloud Spanner

Google announces Google Spanner in Google Cloud Next 2017, which is the first No-Compromise Relational Database Service:

Cloud Spanner offers:

• Strong consistency, including strongly consistent secondary indexes.
• SQL support, with ALTER statements for schema changes.
• Managed instances with high availability through transparent, synchronous, built-in data replication.

Here is the comparison among Google’s database services:

How does it works?

To scale the database horizontally, Spanner divide a table into many splits by key range:

Spanner replicates each splits in multiple zones:

• API Layer: key look-up, request dispatching.
• Split Leader: transaction synchronization, deadlock prevention.
• Split Replicas: data storage and replication.
• True Time: it is an API that allows any machine in Google datacenters to know the exact global time with a high degree of accuracy. This allows different Spanner machines to reason about the ordering of transactional operations often without any communication at all.

An example of single node write:

Let’s say client wants to insert a row with ID=7

1. API layer looks-up what the split contains ID=7, it is in split 1.
2. API layer sends the request to the leader of split 1.
3. Leader of split 1 tries to acquire the write lock on the row ID=7.
4. If the row ID=7 is locked by a previous transaction, request is blocked until the previous one is done.
5. Apply wound-wait algorithm for deadlock prevention.
6. If lock is acquired, leader of split tags a timestamp to the transaction based on True Time.
7. Leader tells the Split 1 replicas about the transaction and its timestamp. Once a majority of those replicas have stored the transaction mutation in stable storage, the transaction commits.
8. Leader replies the client and release the transaction lock.

Read-Only Transaction V.S. Read-Write Transaction

An example of read-write transaction locks.

1. Read-Only Transaction: no locking, only read operations.
2. Read-Write Transaction: need locking, has write operations.

Data Schema Design

Create a table Singers in traditional way:

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

Its physical layout in Spanner is like:

Another table Albums which is always joined with Singers:

CREATE TABLE Albums (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId);

The physical layout in Spanner is like:

Now we can find the highly-related data may be assigned into different splits and hurt the performance of database.

To solve this problem, Spanner provider interleaved table schemas to group rows in different tables to the same split in such way:

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

The physical layout now looks like:

In the same way, we can extend a deeper hierarchy of interleaved table as:

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  TrackId      INT64 NOT NULL,
  SongName     STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

Best Practices:

Choose primary key by a monotonically increases/decreases keys may incur hot-spots.

-- ANTI-PATTERN: USING A COLUMN WHOSE VALUE MONOTONICALLY INCREASES OR
-- DECREASES AS THE FIRST KEY PART

CREATE TABLE Users (
  LastAccessTimestamp INT64 NOT NULL,
  UserId              INT64 NOT NULL,
  ...
) PRIMARY KEY (LastAccessTimestamp, UserId);

Possible fix #1: Swap the order of keys

CREATE TABLE Users (
  UserId              INT64 NOT NULL,
  LastAccessTimestamp INT64 NOT NULL,
  ...
) PRIMARY KEY (UserId, LastAccessTimestamp);

Possible fix #2: Hash the key and spread the writes among N shards

CREATE TABLE Users (
  ShardId             INT64 NOT NULL, // hash(key parts) % N
  LastAccessTimestamp INT64 NOT NULL,
  UserId              INT64 NOT NULL,
  ...
) PRIMARY KEY (ShardId, LastAccessTimestamp, UserId);

Loading Data Practice

1. Avoid writing rows in primary key order.
2. Avoid creating indexes in increasingly/decreasingly order.
3. Commit between 1 MiB to 5 MiB mutations at a time.
4. Upload data before creating secondary indexes.
5. Don’t write each row one a time.
6. Don’t package N random rows into a commit with N mutations.

SQL Practice

Use query parameters to speed up frequently executed queries

Assume we have two queries as below:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'
SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

We should parameterize the query like:

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

We have three benefits by parameterized query:

• Pre-optimized plans: Queries that use parameters can be executed faster on each invocation because the parametrization makes it easier for Cloud Spanner to cache the execution plan.
• Simplified query composition: you do not need to escape string values when providing them in query parameters. This also reduces the risk of syntax errors.
• Security: query parameters make your queries more secure by protecting you from various SQL injection attacks. This is especially important for queries that are constructed from user input.

Write efficient queries for range key lookup

If the list of keys is sparse and not adjacent, use query parameters and UNNEST to construct your query.

SELECT *
FROM Table AS t
WHERE t.Key IN UNNEST (@KeyList)

If the list of keys is adjacent and within a range, specify the lower bound and higher bound of the key range in the WHERE clause.

SELECT *
FROM Table AS t
WHERE t.Key BETWEEN @min AND @max

Avoid large reads inside read-write transactions

Use ORDER BY to ensure the ordering of your SQL results

Example with Go