Database / Google Spanner Database Interview questions
1. What is Google Cloud Spanner?
Google Cloud Spanner is a fully managed, horizontally scalable relational database that combines the strong consistency and SQL support of traditional databases with the scale of distributed NoSQL systems. It spreads data across multiple zones or regions and uses synchronous...
2. What are the main features of Google Spanner?
Spanner's feature set centers on giving developers relational guarantees at global scale. External consistency - every committed transaction gets a timestamp that reflects real commit order, even across regions.
3. What is the purpose of TrueTime in Spanner?
TrueTime is a globally synchronized clock API that Spanner uses to assign commit timestamps to transactions. Instead of returning a single instant, TrueTime returns an interval [earliest, latest] that is guaranteed to contain the actual current time, using GPS and...
4. Define interleaved tables in Google Spanner?
An interleaved table is a child table whose rows are physically stored next to the parent row they belong to, based on a shared primary key prefix. You declare the relationship with INTERLEAVE IN PARENT , and the child's primary...
5. What is a Spanner instance?
A Spanner instance is the top-level allocation of compute and storage resources within a Google Cloud project. It defines an instance configuration (regional or multi-region, which fixes where replicas live) and a compute capacity, expressed in nodes or processing units,...
6. What are processing units in Google Spanner?
Processing units are Spanner's fine-grained measure of compute capacity, introduced so customers don't have to provision a full node just to get started. 1,000 processing units equal one node , and capacity can be provisioned in increments as small as...
7. Describe primary keys in Google Spanner schema design?
Every Spanner table requires a primary key, and unlike most relational databases, that key also decides how rows are physically distributed across storage splits. Rows are stored in primary-key order, so the key you choose directly controls which machine handles...
8. What are the supported database dialects in Spanner?
Spanner databases are created with one of two dialects, chosen at creation time and fixed for the life of the database. GoogleSQL dialect PostgreSQL dialect Google's native SQL syntax, closest to standard SQL with Spanner-specific extensions....
9. List the data types supported by Google Spanner?
Spanner's GoogleSQL dialect supports a standard set of scalar and structured types for schema design: BOOL - true/false values. INT64 - 64-bit signed integers....
10. How do you create a database in Google Spanner?
A database is created inside an existing instance, either through the Cloud Console, the gcloud CLI, or a client library, and it requires choosing the dialect up front. gcloud spanner databases create orders-db \ --instance=prod-instance \ --database-dialect=GOOGLE_STANDARD_SQL You can optionally
11. What is a secondary index in Spanner?
A secondary index is an additional sorted structure on one or more non-primary-key columns, created so queries filtering or sorting on those columns don't have to scan the base table. CREATE INDEX OrdersByDate ON Orders(OrderDate); By default, a secondary index...
12. Explain the purpose of splits in Spanner?
A split is a contiguous range of rows, ordered by primary key, that Spanner manages as a single unit of data movement and load balancing. Splits are the mechanism behind Spanner's horizontal scalability....
13. What are mutations in Google Spanner?
A mutation is a buffered write operation, either an insert, update, delete, or replace, applied to a Spanner table outside of SQL DML syntax, typically through a client library. Mutations are queued on a transaction object and only sent to...
14. How do you apply schema changes in Spanner?
Schema changes are applied as DDL statements, such as ALTER TABLE , CREATE INDEX , or ADD COLUMN , submitted through the console, gcloud , or a client library's updateDatabaseDdl call. ALTER TABLE Orders ADD COLUMN Status STRING( 20 );...
15. What is the Spanner emulator?
The Cloud Spanner emulator is a local, in-memory version of Spanner that implements the same gRPC API as the production service, letting developers write and test code without connecting to a real instance or incurring cost. gcloud emulators spanner start...
16. Why does Spanner use TrueTime for consistency?
Spanner needs to order transactions correctly across machines that may be thousands of miles apart, and clocks on separate servers naturally drift, so a plain local timestamp can't be trusted to reflect real-world order. TrueTime solves this by exposing clock...
17. How does Spanner achieve external consistency?
External consistency means that if transaction A commits before transaction B starts (in real time), then A's timestamp is guaranteed to be less than B's, and any client observing both will see them in that same order. Spanner delivers this...
18. What is the difference between read-write and read-only transactions in Spanner?
Read-write transaction Read-only transaction Uses locking and two-phase commit to safely mix reads and writes. Never takes locks; only reads data....
19. When should you use interleaved tables versus foreign keys?
Both express a parent-child relationship, but they optimize for different things. Interleaved tables physically co-locate child rows with their parent, so use them when the child is almost always accessed together with its parent, such as an order's line items,...
20. What happens when a hotspot occurs in Spanner?
A hotspot happens when a disproportionate share of reads or writes lands on a small number of splits, usually because the primary key is monotonically increasing (like a timestamp or auto-increment ID) or because one key value, such as a...
21. How is data partitioned across nodes in Spanner?
Spanner partitions data by primary key range into splits, and each split is assigned to a Paxos group of replicas spread across the zones or regions in the instance configuration. As data grows or a range becomes hot, Spanner's placement...
22. Why should you avoid monotonically increasing primary keys?
Because Spanner stores rows in primary-key order, a key that always increases, such as an auto-incrementing integer, a sequential ID, or a plain TIMESTAMP , means every new row is appended at the same end of the keyspace. That range...
23. What is the difference between Spanner and Cloud SQL?
Cloud Spanner Cloud SQL Horizontally scalable, distributed across zones/regions automatically. Runs on a single managed instance (with read replicas), vertically scaled.
24. How does Spanner handle schema changes without downtime?
Spanner treats DDL as a distributed operation coordinated across all replicas rather than a lock that halts the database. When a DDL statement is submitted, Spanner assigns it a future timestamp at which the new schema becomes active, and it...
25. When would you choose bounded staleness over strong reads?
Bounded staleness tells Spanner "give me data that is at most N seconds old, but pick whatever timestamp lets you answer fastest," instead of forcing a strong read that must confirm it has the absolute latest commit before responding. Spanner...
26. How can you optimize query performance in Spanner?
Most Spanner performance problems trace back to either schema design or query shape, so optimization usually targets both. Design keys to avoid hotspots - a hot split caps throughput regardless of query tuning....
27. What is the difference between Data Boost and standard reads?
Standard reads Data Boost Executed using the instance's provisioned nodes/processing units. Executed on separate, serverless compute Google provisions on demand.
28. Why do we use commit timestamps in Spanner tables?
A commit timestamp column, declared with OPTIONS (allow_commit_timestamp=true) , lets Spanner fill in the exact TrueTime-derived commit time of the transaction automatically, rather than the application computing its own "updated at" value. CREATE TABLE AuditLog ( Id STRING( 36 )...
29. How does Spanner's query optimizer choose an execution plan?
Spanner's optimizer is cost-based: it evaluates candidate plans using table and index statistics, such as row counts and cardinality estimates, and picks the plan with the lowest estimated cost in terms of data scanned and rows processed. Key inputs to...
30. What is the difference between batch DML and partitioned DML?
Batch DML Partitioned DML Groups several DML statements into one transaction. Splits one large DML statement across many transactions internally....
31. When should you use change streams in Spanner?
Change streams capture row-level insert, update, and delete activity on watched tables (or the whole database) in near real time, ordered and partitioned by commit timestamp, without the application having to write custom trigger logic or poll for changes. CREATE...
32. How is fine-grained access control implemented in Spanner?
Beyond project-level IAM roles, which grant or deny access to an entire instance or database, Spanner supports database roles that restrict access down to individual tables, columns, or views inside a single database. CREATE ROLE analyst; GRANT SELECT(CustomerId, OrderDate) ON...
33. Why doesn't Spanner support auto-incrementing primary keys?
Spanner deliberately omits a native auto-increment or identity-column feature because it conflicts with how the database achieves horizontal scale. An auto-incrementing sequence, by definition, produces monotonically increasing values, and since rows are stored in primary-key order, every new row
34. What is the difference between GoogleSQL and PostgreSQL dialects in Spanner?
GoogleSQL dialect PostgreSQL dialect Native Spanner types like STRUCT and ARRAY are first-class. Uses Postgres types: bigint, numeric, text, timestamptz.
35. How do you troubleshoot high latency in Spanner queries?
Latency issues in Spanner usually fall into a few diagnosable buckets, so troubleshooting starts with narrowing down which one applies. Check Cloud Monitoring's split-level metrics for CPU and lock-wait concentration, which points to a hotspot rather than a query problem....
36. Explain the internal working of Paxos in Spanner replication?
Every split in Spanner is replicated across a set of replicas (typically 3, 5, or more depending on the instance configuration) that form an independent Paxos group. One replica is elected leader for that group; the rest are followers that...
37. Explain the execution flow of a read-write transaction in Spanner?
A read-write transaction in Spanner moves through acquisition of locks, buffered writes, and a two-phase commit if the transaction spans more than one split. flowchart TD A[Begin transaction] --> B[Perform reads, acquire row locks] B --> C[Buffer mutations / DML...
38. Explain the lifecycle of a split in Spanner?
A split begins as part of a larger key range, typically the entire table when it's small, and evolves as data volume and traffic change over time. flowchart LR A[Table created as one split] --> B[Data/traffic grows] B --> C{Split...
39. How does Spanner guarantee external consistency across regions?
Cross-region external consistency relies on the fact that TrueTime's uncertainty bound is a physical property of the clock infrastructure, not something tied to any one data center, so every region can independently agree on "has this timestamp definitely passed" without...
40. What happens internally when Spanner commits a distributed transaction?
For a transaction touching multiple splits, Spanner's client picks one participant Paxos group as the transaction coordinator and runs a two-phase commit protocol across all groups involved. sequenceDiagram participant Coord as Coordinator Group participant P1 as Participant Group 1 participant...
41. How can you optimize a multi-region Spanner configuration for latency?
Multi-region latency optimization mostly comes down to controlling where reads and writes physically go relative to the leader region. Place the app close to the leader region for write-heavy services, since every write ultimately routes through the leader replicas of...
42. Which is better and why: multi-region or regional Spanner configuration for a global app?
Neither is universally better; the right choice depends on which failure mode and latency profile the application can least tolerate. Regional Multi-region Lower write latency - all replicas are close together....
43. How does directed reads improve read latency in multi-region Spanner?
By default, some read paths in a multi-region instance can end up routed toward the leader region even when a closer replica could have served them, because the client isn't explicitly telling Spanner where it prefers to read from. Directed...
44. Why is clock skew uncertainty critical to Spanner's TrueTime API?
TrueTime's core insight is refusing to pretend clocks are perfectly synchronized. Every physical clock, even GPS and atomic-clock-disciplined ones, drifts slightly, so instead of returning a single "current time" value that might be wrong by an unknown margin, TrueTime returns...
45. How do you troubleshoot transaction aborts in Spanner?
Aborts in Spanner almost always trace back to lock contention between concurrent read-write transactions, so troubleshooting focuses on finding and reducing that contention rather than treating each abort as a bug. Check abort rate and lock-wait metrics in Cloud Monitoring,...
46. Explain the internal working of the Spanner query execution engine?
Once the optimizer picks a plan, Spanner executes it as a tree of operators, distributed across whichever splits the query touches, and streams partial results back rather than materializing the whole result set at once. flowchart TD A[Parsed SQL] -->...
47. What happens when a leader region becomes unavailable in Spanner?
In a multi-region configuration, each split's Paxos group has replicas spread across the configured regions, with the leader typically hosted in the designated "leader region" for low write latency. If that region becomes unavailable, whether from a network partition or...
48. How does Spanner implement point-in-time recovery internally?
Point-in-time recovery (PITR) relies on Spanner's version_retention_period , a per-database setting (up to 7 days) that controls how long old row versions are kept before garbage collection instead of being purged immediately after being superseded. ALTER DATABASE orders_db SET OPTIONS...
49. Explain the execution flow of a partitioned DML statement in Spanner?
Partitioned DML is designed for bulk updates or deletes that would be impractical, or impossible, inside a single transaction, so instead of one atomic operation it decomposes the statement into many independent pieces. flowchart TD A[Client issues partitioned DML statement]...
50. How can you optimize schema design to avoid hotspotting at scale?
Hotspot prevention is primarily a key-design problem, so the fix has to happen before the table fills up rather than after traffic concentrates. Avoid monotonic keys - sequential IDs, auto-increment, or plain timestamps all funnel new rows to one end...
