GCP: Cloud Spanner

# 概要
https://cloud.google.com/spanner#all-features

- relational
- scalable
- acid
- 99.999% availability
- multi-regional
- synchronous replication
- automatic sharding
- managed
- strong consistency
- PostgreSQL interface
- federated queries with BigQuery
- CMEK
- Spanner change streams
- Key Visualizer

# インスタンス
https://cloud.google.com/spanner/docs/instances

- instance is an allocation of resources
- the instance configuration and the compute capacity
    - the location and amount of the instance's serving and storage resources
- configuration defines the geographic placement and replication of the databases
    - regional or multi-region
- capacity as a number of processing units or as a number of nodes, with 1000 processing units being equal to 1 node
- For instances of 1 node and larger, Spanner allots 4 TB of data for each node.
- regional
  ![regional](https://cloud.google.com/static/spanner/docs/images/regional-instance-configuration.png)

## Regional and multi-region configurations
https://cloud.google.com/spanner/docs/instance-configurations

- If your users and services are located within a single region, choose a regional instance configuration for the lowest-latency reads and writes.
- For any regional configuration, Spanner maintains 3 read-write replicas, each within a different Google Cloud zone
- Multi-region configurations allow you to replicate the database's data in multiple zones across multiple regions
- additional network latency when these replicas communicate with each other to vote on writes
- You can move your instance from any instance configuration to any other instance configuration, including between regional and multi-regional configurations
- You cannot currently change the instance configuration of an instance that has any CMEK-enabled databases

## Replication
https://cloud.google.com/spanner/docs/replication

- Spanner has three types of replicas: read-write replicas, read-only replicas, and witness replicas

# Databases
https://cloud.google.com/spanner/docs/databases

- instance > databases > schema > table, view, indexes
- properties
    - dialect: Google Standard SQL / PostgreSQL
    - IAM
    - encryption key
    - leader region, query optimizer version, etc

# Design and Develop

## schema
https://cloud.google.com/spanner/docs/schema-and-data-model

- a database > some tables > rows / columns
- primary key: one or more columns, uniquely identifiable
- secondary indexes: one or more columns
- strongly typed

### table interleaving

- parent-child relationships
- physically co-locates child rows with parent rows
    - >if you have a Customers table and an Invoices table, and your application frequently fetches all the invoices for a customer, you can define Invoices as an interleaved child table of Customers
- child table composite primary key
- `INTERLEAVE IN PARENT Singers ON DELETE CASCADE;`

### foreign key
https://cloud.google.com/spanner/docs/foreign-keys/overview

![overview](https://cloud.google.com/static/spanner/docs/images/foreign-keys-example.svg)

- not limited to primary key
- multiple foreign key
- do not co-location in storage layer
- `CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)`
- can combine with interleaving
    - ![sample](https://cloud.google.com/static/spanner/docs/images/schema-with-interleaving.svg)
    - **Google recommends that you choose to represent parent-child relationships either as interleaved tables or as foreign keys, but not both**

### primary key

- one or more columns
- do not create hotspots
    - not monotonically increasing integer
    - hash
    - swap the order of primary key
    - uuid
    - bit-reverse values

### splits

- as your database grows, Spanner divides your data into chunks called "splits."

### multi-tenancy

- may want multi-tenancy if you are storing data that belongs to different customers
- to have all customers in a single table in a single database, and to use a different primary key value for each customer
- Adding new tables and non-interleaved indexes can take a long time

## views

- a view is a virtual table defined by a SQL query
- when a query that refers to a view is executed, Spanner creates the virtual table by executing the query defined in the view
- *invoker rights views* means that when a user executes a query against the view, Cloud Spanner checks the user's permissions on the schema objects that the view references
- benefits
    - provide logical data-modeling to applications
    - centralize query definitions and so simplify maintenanc
    - query that defines a view is stored in the database schema instead of in application logic, Spanner can and does ensure that schema changes
- limitation
    - read-only. cannot be used to add, update or delete data
    - cannot use query parameters
    - cannot be indexed
    - not supported by the Read API

### secondary indexes
https://cloud.google.com/spanner/docs/secondary-indexes

- to look up data in that column
- help Spanner execute scans more efficiently, enabling index scans rather than full table scans
- can add a new secondary index to an existing table while the database continues to serve traffic
- `CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName)`
- can receive faster results if an index exists for those columns, and in the order specified by the query

### check constraint

- the values of one or more columns must satisfy a boolean expression
- `CONSTRAINT start_before_end CHECK(StartTime < EndTime)`

# PGAdapter

https://cloud.google.com/spanner/docs/pgadapter

![pgadapter](https://cloud.google.com/static/spanner/docs/images/pgadapter-diagram.png)

- a sidecar proxy that supports the PostgreSQL interface
- translates the PostgreSQL wire protocol into the Cloud Spanner wire protocol, gRPC

# Backup

https://cloud.google.com/spanner/docs/backup

- reside in the same instance as their source database and are replicated in the same geographic locations
- a user-specified expiration date which determines when it will be automatically deleted
- Spanner backups, like databases, are encrypted by either Google-managed or customer-managed (CMEK) encryption
- When you restore a Spanner database, you must specify a source backup and a new target database
    - The new database must be in the same project

# PITR: point-in-time recovery

https://cloud.google.com/spanner/docs/pitr

- protection against accidental deletion or writes
- can recover the data from a point-in-time in the past (up to a maximum of 7 days)
- By default, your database retains all versions of its data and schema for 1 hour. You can increase this time limit to as long as 7 days through the `version_retention_period` option

# External consistency

https://cloud.google.com/spanner/docs/true-time-external-consistency

- the strictest concurrency-control guarantees for transactions
- the system behaves as if all transactions were executed sequentially, even though Spanner actually runs them across multiple servers
- Spanner allows writes to proceed without being blocked by read-only transactions, but without exhibiting the anomalies that snapshot isolation allows

# Observe and tune

https://cloud.google.com/spanner/docs/audit-logging

- Cloud Audit Logs
    - admin activity
    - data access
    - systen event
- Use query parameters to speed up frequently executed queries
    - `SELECT a.SingerId FROM Albums AS a WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'`
- Write efficient queries for range key lookup
    - `SELECT * FROM Table AS t WHERE t.Key IN UNNEST (@KeyList)`
- Use STARTS_WITH instead of LIKE to speed up parameterized SQL queries
- Key Visualizer
    - help you analyze your Spanner usage patterns. It generates visual reports for your databases broken down by ranges of rows over time


# Pricing

https://cloud.google.com/spanner/pricing

- amount of compute capacity in your instance
- number of nodes (1000 processing units) multiplied by the hourly rate
- amount of storage that your databases use (gigabytes (GB), where 1 GB = 2^30 bytes)
- amount of storage that your backups use (gigabytes (GB), where 1 GB = 2^30 bytes)
- amount of network bandwidth used (gigabytes (GB), where 1 GB = 2^30 bytes)
- charged for each instance

# Quotas & limits

https://cloud.google.com/spanner/quotas

- Nodes per instance: Default limits vary by project and configuration
- db per instance: ~100 dbs
- storage size: 4TB per node
- Concurrent reads per session: 100