Question 1

How does Cloud Spanner achieve global consistency and high availability at the same time?

Accepted Answer

Cloud Spanner uses a globally distributed architecture with synchronous replication across multiple regions, relying on Google's implementation of the Paxos consensus protocol to coordinate writes. Every write is replicated to a majority of replicas before being acknowledged, which guarantees strong external consistency . At the same time, because data is spread across multiple zones and regions with automatic leader election , the system remains highly available even during zonal or regional failures. This combination allows Spanner to deliver both a relational, ACID compliant experience and the fault tolerance typically associated with NoSQL systems.

Question 2

Scenario: Your application requires a relational database that can scale horizontally across regions without sacrificing ACID guarantees. How does Cloud Spanner satisfy this requirement?

Accepted Answer

Cloud Spanner is purpose built to combine horizontal scalability with full ACID transaction semantics. Under the hood, data is automatically split into ranges and distributed across nodes, and you scale by simply adding more compute capacity (nodes or processing units). Synchronous replication via Paxos ensures every transaction is strongly consistent across all regions. Key capabilities that make this work: Automatic sharding distributes data across splits without application level partitioning logic TrueTime provides globally synchronized timestamps so transactions are externally consistent Multi region configurations (e.g., nam eur asia1 ) replicate data across continents while preserving ACID guarantees Adding nodes scales both throughput and storage linearly

Question 3

Explain the role of TrueTime in Cloud Spanner and why it is important.

Accepted Answer

TrueTime is a globally synchronized clock system built into Google's infrastructure that provides bounded time uncertainty intervals rather than single point timestamps. Cloud Spanner uses TrueTime to assign globally consistent commit timestamps to every transaction, which enables external consistency the strongest consistency guarantee available. When a transaction commits, Spanner waits out the TrueTime uncertainty interval (typically a few milliseconds) before making the result visible, ensuring that if transaction T1 committed before T2 in real time, T1's timestamp is always lower. Prevents anomalies such as stale reads and write skew across regions Enables snapshot reads at any past timestamp with guaranteed consistency The uncertainty window is kept small (typically under 7ms) via GPS and atomic clocks in every Google data center

Question 4

Scenario: You need to migrate an existing PostgreSQL database to Cloud Spanner. What steps would you follow to ensure a smooth migration?

Accepted Answer

Migrating from PostgreSQL to Cloud Spanner requires careful planning across schema, data, and application layers. Spanner's PostgreSQL interface simplifies this by supporting familiar SQL syntax, but there are key differences to address. Schema Conversion : Use Harbourbridge (or the Spanner migration tool) to auto convert PostgreSQL schemas, adjusting for Spanner requirements like choosing appropriate primary keys (avoid sequential IDs use UUIDs or hashed prefixes) Data Migration : Extract data via pg dump or CDC and load into Spanner using Dataflow or batch mutation APIs Application Updates : Switch to Spanner client libraries or the PostgreSQL compatible interface, and refactor queries that use unsupported features (e.g., stored procedures, triggers) Testing : Validate data integrity, transaction behavior, and latency with realistic workloads before cutover

Question 5

How does Cloud Spanner support schema changes without downtime?

Accepted Answer

Cloud Spanner performs online schema changes that are applied in the background while the database continues to serve reads and writes without interruption. When you issue a DDL statement such as ALTER TABLE or CREATE INDEX , Spanner progressively applies the change across all splits and replicas. Long running changes like backfilling a new index happen asynchronously and are tracked by schema update timestamps. No table locks or maintenance windows required Multiple schema changes can be batched into a single request for atomic application You can monitor progress via the schema update operations in the Cloud Console or gcloud CLI

Question 6

Scenario: Your application experiences increased latency during peak traffic periods. How would you diagnose and resolve this issue in Cloud Spanner?

Accepted Answer

Start by identifying whether the bottleneck is compute, query design, or connection overhead, then address accordingly. Monitor Metrics : Use Cloud Monitoring dashboards to check CPU utilization (should stay below 65% for single region, 45% for multi region), request latency percentiles, and operations per second Analyze Query Plans : Run EXPLAIN on slow queries to detect full table scans or missing indexes, and add secondary indexes where needed Scale Compute : Add processing units or nodes to reduce per node CPU pressure Spanner automatically rebalances splits across new capacity Optimize Transactions : Keep read write transactions short, prefer read only transactions with bounded staleness for analytics, and use batch reads to reduce round trips Check Hotspots : Look for splits receiving disproportionate traffic due to monotonically increasing keys

Question 7

Write a SQL query to retrieve the top five customers with the highest total purchase amounts from a sales table.

Accepted Answer

This query aggregates purchase amounts per customer, orders the results by total spending in descending order, and returns the top five. Spanner's SQL dialect supports standard aggregation, ORDER BY , and LIMIT . GROUP BY collapses rows by customer id and computes the aggregate ORDER BY ... DESC sorts highest spenders first LIMIT 5 restricts the result set Spanner pushes this down to avoid scanning unnecessary data when paired with appropriate indexes

Question 8

Explain the difference between interleaved and non-interleaved tables in Cloud Spanner.

Accepted Answer

Interleaved tables define a parent child relationship where child rows are physically co located with their parent row on the same split, dramatically improving join and lookup performance for related data. Non interleaved tables are stored independently and distributed across splits based solely on their own primary key. Interleaved : Best for data that is almost always accessed together (e.g., a customer and their orders). Avoids cross split joins. Non interleaved : Better for entities accessed independently or with many to many relationships. Allows Spanner to distribute and scale each table separately. A parent row and all its interleaved children must fit within a single split (roughly 8 GB), so avoid interleaving for unbounded child growth.