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 Paxos-based replicati...
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. Horizontal scalability - adding compute capacity (nodes or processing units) increase...
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 atomic clock references distribu...
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 key must start with the parent's primary key columns. CREATE TABLE O...
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, that determines available...
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 100 processing units. Nodes Use case Whole-no...
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 which rows. This makes primary key...
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. PostgreSQL-compatible syntax and types, useful for tea...
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. FLOAT64 / FLOAT32 - floating point numbers. NUMERIC - high-precision decimal, suited to financial data. STRING and BYTES - variable-lengt...
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 is stored separately from the base ...
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. As a table grows or a range of keys gets busy, Spanner automatically divides it into smaller ...
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 Spanner when the transaction commits. transaction.insert(...
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 ); Most DDL statements in Spanner run online : reads and writes ...
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 gcloud config set auth/disable_credential...
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 uncertainty explicitly, as an interva...
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 through three cooperating mechanisms. TrueTime supplies ...
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. Always reads the latest committed data (strong reads). Can read at a specific timestamp, including a slightly stale one. Higher latency due to commit ...
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, and when the child's primary key ca...
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 popular tenant ID, gets far more traffic than others. When ...
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 driver automatically divides an oversized or overloaded spl...
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 becomes one split, and every insert competes for the same split'...
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. Strong external consistency via TrueTime and Paxos. Standard MySQL/PostgreSQL/SQL Server consistency model. Custom dialect (Goo...
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 propagates the pending change to every replica ahead of...
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 is free to route the read to the closest available rep...
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. Add targeted secondary indexes with STORING columns so common queries avoid a base-tab...
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. Competes with production transactional traffic for capacity. Isolated from production traffic, so it doesn't affect transactional laten...
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 ) NOT NULL , Chang...
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 that decision include which indexes exist an...
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. Fully atomic - all statements commit or none do. Not atomic as a whole - executes as independent sub-ranges. Bound by normal transaction size and lock ...
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 CHANGE STREAM OrderChanges FOR Or...
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 TABLE Orders TO ROL...
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. DDL uses Spanner-specific clauses (e.g. INTERLEAVE IN PARENT). DDL closely mirrors standard Postgres DDL syntax. Works with Spanner client libr...
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. Run EXPLAIN / query stat...
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 can serve reads but don't originate writes for that split...
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 writes] C --> D{Spans multiple spl...
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 too large or hot?} C -- Yes --> D[Placement driver divides into sma...
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 talking to each other for...
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 P...
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 the split it touches. Use di...
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. Higher write latency due to cross-region quorum. 99.99% availability SLA. 99.999% avai...
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 reads let the client specify a preferred replica type (r...
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 an interval [earlie...
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, broken down by table, ...
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] --> B[Cost-based optimizer picks plan] B --> C[Qu...
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 an outage, the affected Pa...
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 (version_r...
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] --> B[Spanner compute...
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 of the keyspace. Bit-reverse sequential values...
