GCP: Cloud Spanner

Posted at 2022-10-12

概要

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

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

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

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

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;

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
- Google recommends that you choose to represent parent-child relationships either as interleaved tables or as foreign keys, but not both

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

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

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

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

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

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

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

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

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