10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning

10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning

Exam Focus: The Professional Data Engineer exam rigorously tests your ability to design performant, horizontally scalable relational schemas in Cloud Spanner. You will be presented with failure scenarios where Spanner exhibits high write latency, CPU spikes on a single node, or slow join queries. To pass, you must know how to eliminate write hotspots using high-entropy primary key strategies (hash prefixing, UUIDv4, bit-reversal), co-locate relational hierarchies using interleaved tables, eliminate base table back-joins using the STORING clause on secondary indexes, and analyze query execution plans for distributed cross-applies.

In a single-instance relational database like Cloud SQL or Oracle, schema design focuses primarily on normal forms (3NF), B-tree index coverage, and foreign key integrity. In Cloud Spanner, schema design directly governs data placement across a distributed cluster. Because Spanner automatically partitions tables into lexicographically sorted key ranges called splits, an improperly designed primary key can funnel millions of concurrent writes into a single server, neutralizing Spanner's horizontal scaling capabilities.

---

1. Split Boundaries and the Monotonic Primary Key Hotspot Anti-Pattern

To understand why primary key selection is critical in Spanner, consider how data is physically partitioned:

+───────────────────────────────────────────────────────────────────────────────────+
|                     LEXICOGRAPHICALLY SORTED SPLIT PARTITIONS                     |
+───────────────────────────────────────────────────────────────────────────────────+
| Split 1: Keys [0000 - 3333]  │ Split 2: Keys [3334 - 6666]  │ Split 3: Keys [6667 - 9999]  |
| Managed by Node A (Leader)   │ Managed by Node B (Leader)   │ Managed by Node C (Leader)   |
+──────────────────────────────┴──────────────────────────────┴─────────────────────+

Spanner maintains all rows in sorted order by their primary key. Rows residing within a given key range are grouped into a split, and each split is assigned to a specific compute node's Paxos group.

The Anti-Pattern: Monotonically Increasing Keys

In traditional relational systems, primary keys often use auto-incrementing integers (SERIAL, AUTO_INCREMENT, or database sequences) or sequential timestamps (e.g., event_timestamp).

When a table in Spanner uses a monotonically increasing key:

1. Every newly inserted row has a primary key strictly greater than the previous row.
2. Because keys are ordered lexicographically, 100% of all incoming insert operations target the exact end of the key space.
3. The end of the key space resides entirely within a single split managed by a single Paxos Leader compute node.
4. Regardless of whether the Spanner cluster is provisioned with 3 nodes, 30 nodes, or 300 nodes, only one single node processes all incoming writes!
5. The single node saturates its CPU and disk I/O, hitting a hard throughput ceiling of approximately 1,000 to 2,000 writes per second. All other provisioned compute nodes sit completely idle.

[Client Inserts] ───► Key: 2026-09-15 10:00:01 ───┐
[Client Inserts] ───► Key: 2026-09-15 10:00:02 ───┼──► [Split 3 / Node C] (100% CPU HOTSPOT)
[Client Inserts] ───► Key: 2026-09-15 10:00:03 ───┘
                                                       [Split 1 / Node A] (0% CPU - Idle)
                                                       [Split 2 / Node B] (0% CPU - Idle)

---

2. High-Entropy Primary Key Strategies

To achieve linear write scalability where throughput scales proportionally with the number of provisioned nodes, primary keys must distribute inserts uniformly across the entire key space and across all available splits.

Strategy A: Universally Unique Identifiers (UUIDv4)

• Mechanics: Generate randomly distributed 128-bit UUID Version 4 values (e.g., a5c89e21-4f12-4e89-918d-6c1e92d8f012). In Google Standard SQL for Spanner, use the GENERATE_UUID() function.
• Advantages: Completely random distribution across the entire hexadecimal space; zero coordination required between client application threads.
• Trade-off: 16 bytes (or 36-character string representation) consumes slightly more storage than a 64-bit integer, and random inserts cause index fragmentation in cache buffers.

Strategy B: Hash Prefixing (Sharding Buckets)

• Mechanics: When the application domain requires a natural sequential identifier or timestamp, prepend a deterministic hash prefix to the key. In Spanner SQL, compute a hash bucket using FARM_FINGERPRINT:

CREATE TABLE SensorReadings (
  ShardId INT64,
  DeviceId STRING(64),
  ReadingTimestamp TIMESTAMP,
  Temperature FLOAT64
) PRIMARY KEY (ShardId, DeviceId, ReadingTimestamp);

• The client or a generated column computes ShardId = MOD(ABS(FARM_FINGERPRINT(DeviceId)), 10). This spreads incoming writes evenly across 10 discrete key ranges (shards 0 through 9), distributing writes across 10 different splits and compute nodes.

Strategy C: Bit-Reversed Sequences

• Mechanics: If the application requires sequential numeric IDs, reverse the binary bits of the sequence number before storing it as the primary key. Sequential numbers like 1, 2, 3 have consecutive binary representations, but their bit-reversed counterparts alternate between opposite ends of the 64-bit integer spectrum:
  • Integer 1 (000...0001) -> Bit-reversed: 100...0000 (large negative/high number)
  • Integer 2 (000...0010) -> Bit-reversed: 010...0000 (mid-range number)
  • Integer 3 (000...0011) -> Bit-reversed: 110...0000
• Advantages: Eliminates hotspotting while preserving sequential uniqueness without relying on random UUID generation. In Google Standard SQL, Spanner provides the BIT_REVERSE() function and auto-generating bit-reversed sequences (CREATE SEQUENCE ... OPTIONS (sequence_kind = 'bit_reversed_positive')).

Strategy D: Swapping Key Column Ordering

• When designing composite primary keys, never place a sequential timestamp as the leading column. Instead, place a high-cardinality, uniformly distributed identifier first:
  • Hotspot Anti-Pattern: PRIMARY KEY (EventTimestamp, CustomerId)
  • Scalable Pattern: PRIMARY KEY (CustomerId, EventTimestamp)

---

3. Interleaved Tables: Co-Locating Relational Hierarchies

In distributed databases, joining two large tables that reside on different physical servers requires a distributed cross-join or distributed cross-apply, transferring millions of rows across the datacenter network and severely degrading query performance. Cloud Spanner solves this with Interleaved Tables.

The Interleaving Concept

Interleaving creates a strict parent-child relationship at the physical storage level. Spanner physically co-locates child rows directly alongside their corresponding parent row within the exact same Colossus split!

+───────────────────────────────────────────────────────────────────────────────────+
|                     PHYSICAL INTERLEAVED STORAGE ON COLOSSUS                      |
+───────────────────────────────────────────────────────────────────────────────────+
| Split 1 (Node A):                                                                 |
|   CustomerId: 100 (Parent Row)                                                    |
|     ├── OrderId: 100-1 (Child Row)                                                |
|     │     ├── LineItemId: 100-1-A (Grandchild Row)                                |
|     │     └── LineItemId: 100-1-B (Grandchild Row)                                |
|     └── OrderId: 100-2 (Child Row)                                                |
|   CustomerId: 101 (Parent Row)                                                    |
|     └── OrderId: 101-1 (Child Row)                                                |
+───────────────────────────────────────────────────────────────────────────────────+

DDL Implementation

To interleave a child table into a parent table, the child table's primary key must start with the parent table's complete primary key, followed by the INTERLEAVE IN PARENT clause:

-- Parent Table
CREATE TABLE Customers (
  CustomerId STRING(36),
  CustomerName STRING(100),
  Email STRING(100)
) PRIMARY KEY (CustomerId);

-- Interleaved Child Table
CREATE TABLE Orders (
  CustomerId STRING(36),
  OrderId STRING(36),
  OrderDate TIMESTAMP,
  TotalAmount NUMERIC
) PRIMARY KEY (CustomerId, OrderId),
  INTERLEAVE IN PARENT Customers ON DELETE CASCADE;

-- Interleaved Grandchild Table
CREATE TABLE OrderLineItems (
  CustomerId STRING(36),
  OrderId STRING(36),
  LineItemId INT64,
  Sku STRING(32),
  Quantity INT64,
  UnitPrice NUMERIC
) PRIMARY KEY (CustomerId, OrderId, LineItemId),
  INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

Architectural Benefits of Interleaving

1. Zero-Network Joins: Queries that join parent and child records (SELECT * FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId WHERE c.CustomerId = 'C1') execute as a local single-split scan. The compute node reads the contiguous data from local memory or Colossus without initiating cross-node network RPCs.
2. Atomic Hierarchical ACID Transactions: Mutations affecting a customer, their orders, and their line items all execute against the same Paxos group, avoiding expensive distributed Two-Phase Commit (2PC) coordination.
3. Cascading Deletions: Specifying ON DELETE CASCADE automatically and atomically prunes all associated child and grandchild records when a parent record is deleted.

When NOT to Interleave Tables

• Independent Access Patterns: If the child table is frequently queried across all customers without filtering by CustomerId (e.g., SELECT * FROM Orders WHERE OrderDate > '2026-09-01'), interleaving forces Spanner to perform a full distributed table scan across every customer split.
• Size Constraints: All interleaved data for a single root parent row should not grow boundlessly. While a split can hold up to 4 GB, massive single-parent hierarchies that grow uncontrollably can cause split management inefficiencies.
• Multi-Parent Relationships: A table can only be interleaved in one parent. Many-to-many relationships cannot be modeled with simple interleaving.

---

4. Secondary Indexes: Global Sharding, the STORING Clause, and NULL-Filtered Indexes

Secondary indexes in Spanner are not local B-trees stored inside the base table; every secondary index is an entirely separate distributed table partitioned into its own independent splits.

The Back-Join Overhead

Suppose you create a simple index on Orders.OrderDate:

CREATE INDEX OrdersByOrderDate ON Orders(OrderDate);

If you execute the following query:

SELECT OrderId, OrderDate, TotalAmount, Status 
FROM Orders 
WHERE OrderDate >= '2026-09-01';

1. Spanner scans the OrdersByOrderDate index splits to find matching OrderDate entries.
2. The index contains only OrderDate and the base table's primary keys (CustomerId, OrderId).
3. To retrieve TotalAmount and Status, Spanner must execute a back-join: it performs network RPCs back to the base Orders table splits distributed across other nodes to fetch the non-indexed columns.
4. If the query matches 50,000 orders, Spanner initiates thousands of cross-node network lookups, causing severe query latency.

The Solution: The STORING Clause

The STORING clause copies specified non-key columns directly into the secondary index table:

CREATE INDEX OrdersByOrderDate ON Orders(OrderDate)
STORING (TotalAmount, Status);

• Impact: The index now physically contains OrderDate, CustomerId, OrderId, TotalAmount, and Status. The query is satisfied entirely by scanning the index split (covering index). Zero network back-joins are performed.
• Trade-off: Increases storage consumption and incurs write amplification, as every update to TotalAmount or Status requires writing to both the base table and the index table.

NULL-Filtered Indexes

Many operational workflows process records based on a transient status, such as unprocessed tasks or unfulfilled orders. In such tables, 99% of historical rows have a NULL or completed timestamp.

CREATE INDEX UnprocessedOrdersIndex ON Orders(ScheduledShipDate)
WHERE ScheduledShipDate IS NOT NULL;

• Impact: Rows where ScheduledShipDate IS NULL are completely excluded from the index. This reduces index storage from gigabytes to megabytes and eliminates write overhead for all records where the column is unset.

---

5. Query Execution Plans and Performance Tuning

When optimizing Spanner queries, data engineers inspect query execution plans via the Google Cloud Console, gcloud spanner databases execute-sql --query-mode=PLAN, or the EXPLAIN statement.

+───────────────────────────────────────────────────────────────────────────────────+
|                       DISTRIBUTED QUERY EXECUTION OPERATORS                       |
+───────────────────────────────────────────────────────────────────────────────────+
| Distributed Cross Apply:                                                          |
|   - Outer input evaluated on coordinator node                                     |
|   - Batch RPCs sent to distributed splits for inner evaluation                     |
|   - High latency if inner input requires frequent cross-network back-joins        |
|                                                                                   |
| Distributed Union:                                                                |
|   - Coordinator sends parallel sub-queries to all participating splits            |
|   - Merges sorted streams from remote nodes; highly efficient for scans           |
+───────────────────────────────────────────────────────────────────────────────────+

Key Query Tuning Best Practices

1. Force Index Hints: If Spanner's cost-based query optimizer selects a full table scan over an index due to stale statistics, force the index explicitly:

SELECT OrderId, TotalAmount 
FROM Orders @{FORCE_INDEX=OrdersByOrderDate}
WHERE OrderDate >= '2026-09-01';

1. Parameterize All Queries: Never concatenate string literals into dynamic SQL statements. Parameterized queries (WHERE CustomerId = @cid) enable Spanner to cache execution plans in memory across calls, preventing costly plan compilation overhead.
2. Use Read-Only Transactions for Multi-Statement Reads: Wrap multi-query read workflows in an explicit read-only transaction (readOnly: true). Read-only transactions do not acquire shared locks, never abort due to lock contention, and do not trigger commit-wait delays.

---

6. Primary Key Strategy Trade-Off Matrix

---

7. Real-World Exam Scenarios and Architectural Anti-Patterns

Scenario 1: E-Commerce Order Flash Sale Hotspot

• Requirement: A retail platform launches a flash sale expecting 50,000 order insertions per second. The initial schema uses PRIMARY KEY (CreatedAt, OrderId). During preliminary load testing, write latency spikes to over 5 seconds and write throughput caps at 1,800 QPS with a single Spanner node pinned at 100% CPU.
• The Anti-Pattern: Using CreatedAt as the leading primary key column. All orders for the current second hit the same split and the same Paxos leader node.
• The Certified Architecture: Redesign the primary key to use a randomly generated UUIDv4 OrderId as the leading column (PRIMARY KEY (OrderId)), or prepend a hash shard ID: PRIMARY KEY (ShardId, CreatedAt, OrderId). This distributes write transactions across all provisioned nodes, scaling throughput linearly to 50,000+ QPS.

Scenario 2: Slow Order History Join Queries

• Requirement: An online banking portal displays recent transactions when a user logs in. The query joins Accounts and Transactions on AccountId. Under peak login load, query latency degrades to 800ms due to distributed cross-apply joins between separate splits.
• The Anti-Pattern: Leaving Accounts and Transactions as independent top-level tables with foreign keys.
• The Certified Architecture: Interleave Transactions into Accounts: CREATE TABLE Transactions (...) PRIMARY KEY (AccountId, TransactionId), INTERLEAVE IN PARENT Accounts ON DELETE CASCADE;. This guarantees that an account and all its transactions reside on the exact same physical split, converting distributed cross-network joins into sub-millisecond local storage lookups.

---

8. Common Exam Pitfalls and Gotchas

• Pitfall 1: Using Foreign Keys Instead of Interleaving for Hierarchical 1:N Data: While Spanner supports standard foreign keys, foreign keys only enforce referential integrity; they do not co-locate data physically. Joins across foreign keys still execute as distributed network joins. Interleaving physically co-locates data on the same split.
• Pitfall 2: Neglecting the STORING Clause on High-Frequency Lookups: Creating a secondary index without STORING forces Spanner to execute a base table back-join for every projected column not in the index. On large result sets, this causes severe network RPC latency.
• Pitfall 3: Indexing Columns with High NULL Ratios Without a Filter: Indexing an optional timestamp column across 100 million rows where 98% of values are NULL wastes index storage and creates write overhead. Always use a NULL-filtered index (WHERE Column IS NOT NULL).
• Pitfall 4: Misunderstanding Interleaved Table Deletions: Attempting to delete a parent row that contains interleaved child rows without specifying ON DELETE CASCADE in the DDL will cause the delete statement to fail with an integrity error.