Cloud Spanner: Global SQL, Interleaved Tables, and Schema Design

You've built a global SaaS platform. Users in Tokyo, London, and São Paulo all need to see the same inventory count in real time. Traditional sharded databases give you eventual consistency and weekend-long debugging sessions. NoSQL scales writes but kills joins. You need a database that doesn't make you choose between consistency and scale. That's Cloud Spanner. It's Google's answer to the hardest problem in distributed systems: global ACID transactions at internet scale. But Spanner isn't magic—it's a carefully designed trade-off. Schema design matters more than any other database. Get it wrong, and you'll burn money on inter-node latency. Get it right, and you'll scale reads and writes across continents with sub-second latency. This article covers the schema patterns that make Spanner sing, starting with interleaved tables—the most powerful and misunderstood feature in the platform.

Why Spanner Breaks the CAP Triangle

Spanner achieves CP (Consistency and Partition tolerance) by using TrueTime, a globally synchronized clock service. TrueTime exposes a time interval with bounded uncertainty, allowing Spanner to assign commit timestamps that respect causal ordering across regions. This means you get linearizable consistency without sacrificing availability during network partitions. The trade-off? Write latency is bounded by the round-trip time to the nearest TrueTime master, and you pay for that precision in CPU and network overhead. For production systems, this translates to a hard rule: keep your transactions small and your schema flat where possible. A single distributed transaction touching 100 rows across three continents will take 50-100ms. Design for that reality.

-- Create a simple interleaved table hierarchy
CREATE TABLE Users (
  UserId INT64 NOT NULL,
  Name STRING(100),
  Email STRING(100)
) PRIMARY KEY (UserId);

CREATE TABLE Orders (
  UserId INT64 NOT NULL,
  OrderId INT64 NOT NULL,
  OrderDate TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  Total NUMERIC
) PRIMARY KEY (UserId, OrderId),
  INTERLEAVE IN PARENT Users ON DELETE CASCADE;

Interleaved Tables: The Secret to Fast Joins

Interleaved tables physically colocate child rows with their parent row on the same tablet (Spanner's storage unit). This means a join between parent and child rows becomes a local operation—no network round trips. The performance difference is staggering: a join across 1,000 orders for a single user takes <5ms with interleaving versus 50-200ms without. The catch: you must declare the parent-child relationship at schema creation time, and the parent's primary key must be a prefix of the child's primary key. This forces you to think about access patterns upfront. In production, we use interleaving for all 1:N relationships where the parent is the primary access path: users and orders, customers and invoices, devices and telemetry.

-- Query that benefits from interleaving
SELECT o.OrderId, o.Total
FROM Users u
JOIN Orders o ON u.UserId = o.UserId
WHERE u.UserId = 12345
  AND o.OrderDate > '2026-01-01';

Primary Key Design: Hotspot Prevention

Spanner distributes rows across tablets based on the primary key hash. If you use a monotonically increasing key (like auto-increment integers or timestamps), all new writes hit a single tablet, creating a hotspot. This throttles throughput and causes latency spikes. The fix: use a monotonically increasing key as a secondary index, and use a UUID or a composite key with a shard prefix as the primary key. For example, prefix the key with a hash of the user ID modulo 1000. This spreads writes evenly across tablets. In production, we use a 4-byte shard ID computed from a hash of the natural key, followed by a timestamp. This gives us even distribution and efficient range scans for time-series data.

-- Bad: monotonically increasing primary key
CREATE TABLE Events_Bad (
  EventId INT64 NOT NULL,  -- auto-increment -> hotspot
  UserId INT64,
  EventTime TIMESTAMP,
  Payload STRING(MAX)
) PRIMARY KEY (EventId);

-- Good: sharded primary key
CREATE TABLE Events_Good (
  ShardId INT64 NOT NULL,  -- hash(UserId) % 1000
  EventTime TIMESTAMP NOT NULL,
  UserId INT64,
  Payload STRING(MAX)
) PRIMARY KEY (ShardId, EventTime DESC);

Secondary Indexes: Global vs. Local

Spanner supports two types of secondary indexes: global and local (interleaved). Global indexes are stored in their own tablet set and are consistent across all parent rows. They're ideal for queries that filter on non-primary key columns without a parent key constraint. However, global indexes add write amplification—every index write is a distributed transaction. Local indexes are interleaved under the parent table, meaning they're colocated with the parent rows. They're faster for reads that include the parent key, but they can't enforce uniqueness across parents. In production, we use global indexes for unique constraints (e.g., email) and local indexes for filtering within a parent (e.g., order status per user). Always benchmark both; the difference can be 10x on read-heavy workloads.

-- Global unique index
CREATE UNIQUE INDEX UsersByEmail ON Users(Email);

-- Local index interleaved in Users
CREATE INDEX OrdersByStatus ON Orders(Status)
  INTERLEAVE IN Users;

Schema Design for Time-Series Data

Time-series workloads (IoT, logs, metrics) are common on Spanner. The key challenge is balancing write distribution with efficient range scans. A common pattern is to use a shard key (e.g., device_id hash) as the primary key prefix, followed by a timestamp in descending order. This ensures writes are distributed across tablets, and the most recent data is at the top of the tablet for fast scans. However, descending primary keys can cause hotspotting on the first tablet if all new data has the same shard. To mitigate, use a shard key with high cardinality (e.g., device_id itself, not a modulo). For retention, use TTL policies or manual deletion in batches. Spanner doesn't support automatic TTL, so you must implement a cleanup job.

-- Time-series schema with sharding
CREATE TABLE SensorReadings (
  DeviceId STRING(36) NOT NULL,
  RecordedAt TIMESTAMP NOT NULL,
  Temperature FLOAT64,
  Humidity FLOAT64
) PRIMARY KEY (DeviceId, RecordedAt DESC);

-- Query latest reading per device
SELECT DeviceId, ARRAY_AGG(
  STRUCT(Temperature, Humidity)
  ORDER BY RecordedAt DESC LIMIT 1
)[OFFSET(0)] AS latest
FROM SensorReadings
WHERE DeviceId = 'sensor-001'
GROUP BY DeviceId;

Transaction Design: Minimizing Lock Contention

Spanner uses pessimistic locking for read-write transactions. If two transactions touch the same row, one will be aborted and retried. This is fine for low-contention workloads, but high-contention patterns (e.g., incrementing a counter) can cause cascading aborts. The solution: use atomic mutations (INSERT/UPDATE/DELETE with conditions) or partition the hot row. For counters, use a sharded counter pattern: split the counter into N sub-counters and sum them on read. For hot rows (e.g., a 'latest version' row), consider using a separate table with a timestamp-based key to avoid contention. In production, we always use read-only transactions for reads and keep read-write transactions under 100 rows.

-- Sharded counter table
CREATE TABLE PageViewCounters (
  PageId INT64 NOT NULL,
  ShardId INT64 NOT NULL,
  Count INT64 NOT NULL
) PRIMARY KEY (PageId, ShardId);

-- Increment a random shard
UPDATE PageViewCounters
SET Count = Count + 1
WHERE PageId = 42 AND ShardId = FLOOR(RAND() * 100);

-- Read total count
SELECT SUM(Count) AS TotalViews
FROM PageViewCounters
WHERE PageId = 42;

Data Modeling for Strong Consistency

Spanner's strong consistency means you can model relationships without denormalization. However, cross-table transactions are expensive. The rule of thumb: if two entities are always accessed together, interleave them. If they're sometimes accessed together, use a foreign key and accept the distributed transaction cost. For example, an order and its line items should be interleaved. An order and a user profile should not—they're accessed in different contexts. Use Google's standard SQL for joins; they're optimized for Spanner's distributed execution. Avoid stored procedures; they're not supported. Instead, use client-side logic with transactions.

-- Interleaved: Order + LineItems
CREATE TABLE Orders (
  OrderId INT64 NOT NULL,
  UserId INT64 NOT NULL,
  OrderDate TIMESTAMP NOT NULL,
  Status STRING(20)
) PRIMARY KEY (OrderId);

CREATE TABLE LineItems (
  OrderId INT64 NOT NULL,
  LineItemId INT64 NOT NULL,
  ProductId INT64,
  Quantity INT64,
  Price NUMERIC
) PRIMARY KEY (OrderId, LineItemId),
  INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

-- Non-interleaved: UserProfile (separate access pattern)
CREATE TABLE UserProfiles (
  UserId INT64 NOT NULL,
  Name STRING(100),
  Email STRING(100)
) PRIMARY KEY (UserId);

Schema Migration Strategies

Spanner supports online schema changes with no downtime. However, some operations (like adding a NOT NULL column to an existing table) require a multi-step process. Use the spanner-cli or Google Cloud Console to apply changes. For large tables, avoid adding indexes or changing primary keys online—they can take hours and impact performance. Instead, create a new table with the desired schema, backfill data using batch jobs, and then switch traffic. Use change data capture (Spanner's built-in change streams) to keep the new table in sync during migration. In production, we always test schema changes on a clone first.

# Add a column (online, no downtime)
gcloud spanner databases ddl update my-db --instance=my-instance --ddl='ALTER TABLE Users ADD COLUMN Phone STRING(20);'

# Create a new table for migration
CREATE TABLE UsersV2 (
  UserId INT64 NOT NULL,
  Name STRING(100),
  Email STRING(100),
  Phone STRING(20) NOT NULL
) PRIMARY KEY (UserId);

# Backfill (use Dataflow or batch client)
bq query --nouse_legacy_sql 'SELECT UserId, Name, Email, "" AS Phone FROM Users' | \
  gcloud spanner rows insert --instance=my-instance --database=my-db --table=UsersV2

Cost Optimization Through Schema Design

Spanner pricing is based on node count (compute) and storage. Schema design directly impacts both. Interleaved tables reduce storage by colocating data, but they increase write amplification on the parent. Secondary indexes add storage and write cost. The biggest cost driver is the number of nodes, which is determined by your peak throughput. To reduce node count, optimize your schema to minimize distributed transactions and hotspotting. Use commit timestamps sparingly—they add overhead. For read-heavy workloads, use read replicas (available in Spanner) to offload reads. In production, we monitor the 'CPU utilization' and 'Storage utilization' metrics to right-size nodes.

-- Avoid commit timestamps if not needed
CREATE TABLE AuditLog (
  LogId INT64 NOT NULL,
  EventTime TIMESTAMP NOT NULL,  -- use application timestamp
  Action STRING(100)
) PRIMARY KEY (LogId);

-- Use interleaving to reduce storage
CREATE TABLE UserSessions (
  UserId INT64 NOT NULL,
  SessionId INT64 NOT NULL,
  StartTime TIMESTAMP NOT NULL
) PRIMARY KEY (UserId, SessionId),
  INTERLEAVE IN PARENT Users ON DELETE CASCADE;

Monitoring and Debugging Performance

Spanner provides a rich set of metrics via Cloud Monitoring. Key metrics: 'High priority CPU utilization' (should be <65%), 'Average read latency', 'Average write latency', and 'Transaction abort rate'. Use the 'Spanner Query Statistics' page to find slow queries. The 'Key Visualizer' tool shows row access patterns and helps identify hotspots. For debugging, use EXPLAIN ANALYZE to see query execution plans. Look for 'Distributed cross-node' operations—they indicate joins that could benefit from interleaving. In production, we set up alerts on abort rate >1% and CPU >70%.

EXPLAIN ANALYZE
SELECT o.OrderId, o.Total
FROM Users u
JOIN Orders o ON u.UserId = o.UserId
WHERE u.UserId = 12345;

Disaster Recovery and Backup Strategies

Spanner offers automatic backups with point-in-time recovery (PITR) for up to 7 days. For longer retention, export to Cloud Storage using Dataflow. For multi-region instances, Spanner automatically replicates data across regions, providing disaster recovery. However, if you need to recover from a logical error (e.g., accidental table drop), you must use PITR or a backup. Test your restore process regularly. In production, we schedule weekly exports to Cloud Storage and retain them for 30 days. We also use change streams to replicate data to BigQuery for analytics, which doubles as a backup.

# Create a backup
gcloud spanner backups create my-backup --instance=my-instance --database=my-db --expiration-date=2026-08-11T00:00:00Z

# Restore from backup
gcloud spanner databases restore --source-backup=my-backup --destination-database=my-db-restored --instance=my-instance

# Export to Cloud Storage
gcloud spanner databases export my-db --instance=my-instance --destination-uri=gs://my-bucket/spanner-export/