Use Spanner over Cloud SQL for the following reasons:

• Data sizes exceeding 10 TB
• Heavy usage, with QPS (queries per second) exceeding 5K
• Users in multiple regions (spanner has replication across regions, Cloud SQL is regional)

Data within each Cloud Spanner replica is organized into two levels of physical hierarchy: database splits, then blocks. Splits hold contiguous ranges of rows, and are the unit by which Cloud Spanner distributes your database across compute resources. Over time, splits may be broken into smaller parts, merged, or moved to other nodes in your instance to increase parallelism and allow your application to scale. Operations that span splits are more expensive than equivalent operations that do not, due to increased communication. This is true even if those splits happen to be served by the same node. https://cloud.google.com/spanner/docs/whitepapers/optimizing-schema-design

Rows with the same primary key (which are the most related ones in most cases of transactional applications) are brought together and converted into a new entity called a split. Each split is replicated multiple times over failure independent zones, which mostly removes the probability of data loss and also achieves geo-redundancy by default. https://learning.oreilly.com/library/view/google-cloud-platform/9781788834308/430ab19b-5ed3-4fb7-bb97-217938d480e8.xhtml

GCloud Commands

Instance management

Create a new instance

gcloud spanner instances create {instance_name} --config regional-asia-east2 --description new_spanner_instance --nodes 3

Describe instance

gcloud spanner instances describe {instance_name}

Database management

Create database

gcloud spanner databases create {database_name} --instance {instance_name}

DML

Create table

gcloud spanner databases ddl update {database_name} --instance {instance_name} --ddl 'create table student(name string(100)) PRIMARY KEY(name);'

gcloud spanner databases ddl update mydb --instance myspanner --ddl 'create table class(subject string(100)) PRIMARY KEY(subject);'

# TODO: Doesn't support multiple interleaves ...
gcloud spanner databases ddl update mydb --instance myspanner --ddl 'create table lessons(subject string(100), name string(100)) primary key(subject, name), interleave in parent student, interleave in parent class;'

Table Types

Two types of tables in Cloud Spanner:

• root tables
• interleaved tables

Interleaving tables

Interleaving means creating a parent child relationship between two tables. Identical to referencial integrity (PK, FK).