7.1 Spanner Keys, Indexes, and Transactions

Spanner Schema Design: Hotspotting & Table Interleaving

In Cloud Spanner, data is automatically split horizontally into chunks called splits based on primary key ranges. Each split is managed by an independent Spanner node. Achieving high throughput requires distributing reads and writes evenly across all splits.

ANTI-PATTERN (Monotonically Increasing Key):
Split 1: [1000 - 1999]  (IDLE)
Split 2: [2000 - 2999]  (IDLE)
Split 3: [3000 - 3999]  <=== ALL WRITES HIT HERE (100% CPU HOTSPOT!)

OPTIMIZED PATTERN (Bit-Reversed / UUIDv4 Distributed Keys):
Split 1: [0000 - 3FFF]  <=== 25% Writes
Split 2: [4000 - 7FFF]  <=== 25% Writes
Split 3: [8000 - BFFF]  <=== 25% Writes
Split 4: [C000 - FFFF]  <=== 25% Writes

The Hotspotting Anti-Pattern

If a table uses a monotonically increasing primary key (such as an auto-increment integer, a sequential sequence, or a current timestamp CURRENT_TIMESTAMP()), all new inserts will have keys greater than all existing keys. Every insert targets the exact same split at the end of the key space, overwhelming a single node while all other nodes sit idle.

Anti-Hotspotting Solutions

1. UUIDv4 (Universally Unique Identifiers): Generate random 128-bit UUIDv4 values as primary keys. Random bits ensure uniform distribution across all splits.
2. Bit-Reversed Sequential Keys: Reverse the binary bits of an auto-incrementing integer sequence using Spanner's built-in BIT_REVERSE() function. Sequential numbers (1, 2, 3...) produce radically dispersed binary values, distributing writes across all splits while preserving numeric compactness.
3. Hash Prefixing: Prepend a hash of a column attribute or shard ID (an application-computed stable hash bucket) to the primary key.

Table Interleaving

In traditional relational databases, joining a parent table (Customers) with a child table (Orders) requires cross-server network hops if tables reside on different disks or nodes. Spanner resolves this using Table Interleaving:

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

-- Child Table Interleaved in Parent
CREATE TABLE Orders (
  CustomerId STRING(36) NOT NULL,
  OrderId STRING(36) NOT NULL,
  OrderDate DATE,
  TotalAmount NUMERIC,
  OrderStatus STRING(30)
) PRIMARY KEY (CustomerId, OrderId),
INTERLEAVE IN PARENT Customers ON DELETE CASCADE;

• Physical Co-location: Interleaving physically co-locates the child Orders rows directly alongside the parent Customers row in related key ranges to improve locality; large interleaved groups can still be split.
• Performance Impact: Joins between Customers and Orders on CustomerId can benefit from locality, but are not guaranteed to have zero network communication, dramatically accelerating query execution and parent-child transactional commits.

---

Spanner Secondary Indexes, STORING, and Transactions

Secondary Indexes and the STORING Clause

A secondary index in Cloud Spanner is implemented as an independent distributed table sorted by the index key. If a query filters on the indexed column but selects columns not present in the index, Spanner must perform an expensive back-join across the network to the base table split.

To eliminate back-joins, use the STORING clause:

-- Index without STORING: Requires expensive back-join to fetch TotalAmount
CREATE INDEX OrdersByDate ON Orders(OrderDate);

-- Covering Index with STORING: Eliminates back-joins
CREATE INDEX OrdersByDateCovering ON Orders(OrderDate)
STORING (TotalAmount, OrderStatus);

The STORING clause duplicates the specified non-key columns (TotalAmount, OrderStatus) into the index split. The query engine satisfies the query entirely from the index split, reducing latency and compute consumption.

Mutation Limits

Cloud Spanner enforces strict limits on transaction size to maintain predictable latency:

• Maximum Mutations per Commit: A Mutation API commit permits up to 80,000 mutations, including index effects. Current DML limits apply per statement rather than cumulatively across separate DML statements (where a mutation is an insert, update, or delete of a cell—calculated as rows * columns + index updates).
• Maximum Transaction Size: A commit payload cannot exceed 100 MiB.
• Developer Mitigation for Bulk Loads: When ingesting millions of rows, developers must partition data into smaller batches (e.g., 2,000 to 5,000 mutations per batch) and commit them independently, or use the Spanner Dataflow/Bulk Import connectors.

Read-Write vs. Read-Only Transactions

• Read-Write Transactions: Provide atomic, externally consistent updates; concurrency-control/isolation options determine locking and conflict behavior. Contending operations can abort and require the entire transaction callback to be retried. Commit wait ensures TrueTime external consistency.
• Read-Only Transactions: Never acquire locks and never block incoming writes. They execute using a TrueTime timestamp (strong read or bounded staleness). For analytics, dashboards, and batch exports, developers can choose read-only transactions with a freshness bound suitable for the task (e.g., exact_staleness = 15s) to read from local replicas without cross-region consensus latency.

---

Architectural Decision Framework: Cloud SQL vs. Cloud Spanner

Official references checked October 18, 2026: Google Cloud 1, Google Cloud 2.