Google Cloud Spanner Interview Preparation Guide

Google Cloud Spanner is a globally distributed, horizontally scalable relational database that supports full ACID transactions and SQL queries. Unlike traditional relational databases such as PostgreSQL, which are typically confined to a single server or vertical scaling limits, Spanner automatically shards data across thousands of machines using Paxos consensus groups and Colossus storage. This allows Spanner to scale out compute and storage horizontally while maintaining strict serializability and external consistency across multiple global regions, a feat traditionally thought impossible for relational systems.

Spanner achieves global external consistency through the TrueTime API, which utilizes GPS receivers and atomic clocks installed in Google datacenters. Instead of providing an absolute point in time, TrueTime exposes time as a range [earliest, latest] with a bounded uncertainty error epsilon. When a transaction commits, Spanner waits out a duration equal to twice epsilon before releasing locks and returning success. This mandatory commit wait guarantees that any subsequent transaction initiated anywhere in the world will receive a higher timestamp, establishing linearizability without requiring centralized locks.

Interleaved tables allow developers to define a parent-child relationship in the database schema (e.g., Customers and Orders) where child rows are stored physically adjacent to their parent row on the same storage split based on a shared primary key prefix. This is crucial for performance because it turns expensive cross-node distributed joins into local disk lookups within the same tablet server. When properly designed, interleaved tables drastically reduce network latency and eliminate cross-shard coordination overhead for hierarchical entity retrieval.

Spanner automatically shards data into splits based on primary key ranges managed by tablet servers. If an application uses monotonically increasing integers or timestamps as the first column of a primary key, every new write operation will target the exact same key range at the end of the table. Consequently, all write traffic hits a single tablet leader, creating an acute write hot spot that neutralizes Spanner's horizontal scalability. Developers must use hash-prefixed keys or random UUIDs to distribute incoming writes across all available splits.

Read-write transactions acquire pessimistic locks on data rows to prevent concurrent modification conflicts, incurring coordination overhead and potential contention. In contrast, read-only transactions execute without acquiring any locks by reading from a consistent snapshot at a specific timestamp or bounded staleness. This lock-free execution allows analytical queries and heavy reporting traffic to run concurrently with active writes without blocking them or impacting primary Paxos group leaders.

When a transaction modifies data residing in multiple Paxos groups, Spanner coordinates the commit using Two-Phase Commit. One participating Paxos group leader acts as the coordinator. In the prepare phase, the coordinator sends prepare votes to all participant groups, which lock their data and log their votes. Once all participants vote affirmatively and the commit timestamp is assigned via TrueTime, the coordinator initiates the commit phase, instructing all participants to apply the mutation and release their locks.

Spanner continuously monitors the storage size and query load of every data split managed by tablet servers. When a split exceeds storage capacity thresholds or experiences heavy CPU utilization, Spanner automatically divides the split into two smaller ranges. These new splits can then be migrated independently across different tablet servers in the cluster or even across failure domains to balance resource utilization without requiring manual database administrator intervention.

A single-region Spanner instance deploys Paxos groups across multiple zones within one geographic region, providing high availability and ultra-low latency suitable for localized applications. A multi-region instance distributes Paxos replicas across multiple continents or distant regions (e.g., North America and Europe), enabling active-active global deployments that survive entire regional or continental outages. However, multi-region writes incur propagation delays bounded by the physical speed of light between datacenters.

Developers can optimize analytical queries by utilizing stale read-only transactions with bounded staleness (e.g., exact_staleness=15s). This directs queries to read from local read replicas or older snapshot versions without hitting the primary Paxos group leaders. Additionally, ensuring that tables are properly interleaved and using parameterized queries prevents unnecessary query re-compilation and reduces resource consumption on frontend nodes.

Common failure modes include transaction aborts due to lock contention, timeout delays during TwoPhase Commit over wide-area networks, and coordinator failures. These are mitigated by avoiding large bulk mutations in single transactions, implementing exponential backoff with jitter in client-side retry loops, structuring schemas with interleaved tables to minimize cross-shard coordination, and leveraging read-only snapshot transactions whenever strict write locks are unnecessary.

Query parameterization is critical because Spanner caches compiled SQL execution plans. When queries are parameterized, Spanner recognizes recurring query structures and reuses the optimized execution plan. If developers construct dynamic SQL strings by concatenating values directly into the query text, Spanner's query planner is forced to re-parse and re-compile the plan for every unique query string, consuming excessive CPU cycles on frontend nodes and increasing query latency.