Cloud Spanner

Cloud Spanner is a fully managed, globally distributed, horizontally scalable relational database service from Google Cloud. It is the first and only database that offers both strong consistency across regions and horizontal scaling, which traditionally were mutually exclusive. Spanner is designed for mission-critical applications that require ACID transactions, SQL queries, and high availability across multiple regions, such as financial trading platforms, global inventory systems, and gaming leaderboards.

Spanner is not a traditional relational database like MySQL or PostgreSQL; it is a NewSQL database that internally uses a distributed architecture with atomic clocks and Paxos consensus to achieve global strong consistency. The ACE exam expects you to know when to use Spanner versus Cloud SQL (for single-region, smaller workloads) or Bigtable (for NoSQL, high-throughput, low-latency analytical workloads).

Spanner's architecture consists of several layers: - Zones: A Spanner instance spans multiple zones (which are roughly equivalent to Google Cloud zones). Each zone contains one or more servers that store data replicas. - Tablets: Data is horizontally partitioned into tablets, each of which is a contiguous range of rows. Tablets are replicated across zones using Paxos for consensus. Each tablet has a Paxos leader that handles writes. - Directory: A directory is a set of contiguous rows that form the unit of replication and load balancing. Directories are stored in tablets and can be split or merged automatically. - TrueTime: Spanner uses Google's TrueTime API, which exposes a globally synchronized clock with bounded uncertainty. TrueTime provides a monotonically increasing timestamp for each transaction, enabling global consistency without locking.

When a write transaction occurs, the client sends the write to the Paxos leader for the affected tablets. The leader assigns a TrueTime timestamp to the write and replicates it to a majority of replicas (Paxos quorum). Once the write is committed, it is visible to all subsequent reads that use timestamps greater than the commit timestamp. Reads can be served by any replica that is sufficiently up-to-date, but for strong reads, Spanner waits until the replica's timestamp is at least as recent as the read timestamp (using TrueTime).

To create a Spanner instance, you can use the gcloud command-line tool:

gcloud spanner instances create my-instance \
    --config=regional-us-central1 \
    --description="My Regional Instance" \
    --nodes=1

To create a database:

gcloud spanner databases create my-database \
    --instance=my-instance

To create a table:

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

To query the database using gcloud:

gcloud spanner databases execute-sql my-database \
    --instance=my-instance \
    --sql="SELECT * FROM Singers"

To monitor instance metrics, use Cloud Monitoring:

gcloud monitoring metrics list \
    --filter="metric.type = 'spanner.googleapis.com/instance/query_count'"

Spanner integrates with: - Cloud IAM: For access control, you grant roles like spanner.databaseAdmin or spanner.databaseUser. - Cloud Key Management Service (KMS): For customer-managed encryption keys (CMEK). - Cloud Audit Logs: For auditing operations. - Cloud Monitoring and Logging: For performance metrics and error logs. - Dataflow and Dataproc: For bulk data import/export. - Cloud Functions and Cloud Run: For serverless applications that need a globally consistent database.

Spanner does not support stored procedures, triggers, or foreign key constraints (though it does support referential integrity via interleaved tables). It also does not support auto-increment; you must use a sequence or generate unique IDs manually (e.g., using UUID).

A large investment bank needs a database to store real-time trade orders and account balances across multiple continents. The bank requires strong consistency to ensure that a trade executed in London is immediately visible to the balance check in New York. They also need high availability with automatic failover. The bank chooses Spanner with a multi-region configuration (e.g., nam3 covering US, Europe, and Asia). They use interleaved tables for accounts and trades (each account has many trades). The primary key for trades includes the account ID and a timestamp to distribute writes. They set up 10 nodes initially to handle peak load. During a regional outage, Spanner automatically fails over to another region without data loss. Common mistakes include not distributing primary keys properly, causing hotspots on the leader node. The bank uses Cloud Monitoring to track CPU and storage and scales nodes during high-volume trading days.

A mobile gaming company with millions of daily active users needs a real-time leaderboard that is globally consistent. Players from around the world submit scores, and the leaderboard must update instantly without conflicts. The company uses Spanner with a regional configuration (e.g., regional-us-central1) because most players are in North America. They use a single table with a composite primary key (game_id, player_id, score) and a secondary index on score for fast leaderboard queries. They use read-write transactions to update scores atomically. At peak, they handle 100,000 writes per second with 20 nodes. They use Cloud Spanner's built-in client-side buffering to batch writes. A common error is using auto-increment IDs, which cause hotspots; instead, they use random UUIDs. They also use Cloud CDN to cache leaderboard data for read-heavy workloads.

Misconfiguring Spanner often involves choosing the wrong instance configuration (regional vs. multi-region) or insufficient node count. For example, a single node can handle only about 1,000 writes/second; if the application exceeds that, writes will be throttled. Another common mistake is using a monotonically increasing primary key, which causes all writes to go to a single tablet (hotspot). Spanner automatically splits and rebalances tablets, but a hotspot can still cause performance degradation. Also, forgetting to create secondary indexes for common query patterns leads to full table scans. In production, always test with realistic traffic patterns and use the Spanner emulator for local development.