Cloud Spanner: Globally Distributed Relational Database Design Patterns

Relational databases have historically faced a fundamental trade-off: they give you ACID transactions and SQL but don't scale horizontally. When you need more capacity, you add more powerful hardware (vertical scaling) or partition your data across multiple databases (sharding) — a complex operation that breaks cross-shard transactions.

Cloud Spanner eliminates this trade-off. It's a fully relational SQL database that scales horizontally across nodes and regions while maintaining strong consistency and ACID transactions globally. Google built the original Spanner for Google's advertising systems and processes millions of transactions per second internally.

This makes Spanner genuinely different — but different in ways that require schema design that accounts for its distributed architecture. This guide covers Spanner-specific design patterns, the schema decisions that determine whether your Spanner workload is fast or slow, and how to decide when Spanner is the right database choice.

When Spanner Is the Right Choice

Spanner is expensive (minimum $0.90/node-hour for a regional instance, $3/node-hour for multi-regional) and has a specific learning curve. It's the right choice when:

• You need horizontal scale beyond what a single Postgres instance can handle (>5TB, >50,000 QPS on writes)
• You need global replication with strongly consistent reads from multiple regions
• You need cross-row or cross-table ACID transactions at scale
• You have financial, inventory, or gaming data where consistency is more important than the occasional microsecond of latency
• You need 99.999% availability (Spanner's multi-regional SLA)

It's probably not the right choice when:

• Your database fits on a single large Postgres/Cloud SQL instance
• You have mostly read-heavy workloads where read replicas solve the scale problem
• Your team doesn't have time to learn Spanner's schema design requirements
• Cost is a primary concern (Cloud SQL is 5-10x cheaper)

For databases that fit on a single node, see our Cloud SQL vs AlloyDB vs Spanner comparison.

Spanner Architecture and Why Schema Design Matters

Spanner splits tables into splits — ranges of rows distributed across servers. The split boundary is determined by the primary key ordering. Rows with adjacent primary keys end up on the same split (and therefore the same server) until the split grows too large and Spanner automatically splits it further.

This has critical implications for schema design:

Hot spots: If all writes have keys that are close together in sorted order (sequential integers, timestamps), they all land on the same split. That split becomes a hot spot that limits your write throughput regardless of how many Spanner nodes you add.

Hot spot example (bad):

-- Sequential integer primary key — all inserts go to the same split
CREATE TABLE orders (
  order_id INT64 NOT NULL,
  customer_id STRING(36) NOT NULL,
  order_date TIMESTAMP NOT NULL,
  total_amount FLOAT64,
) PRIMARY KEY (order_id);

Hot spot prevention (good):

-- UUID primary key — writes distribute evenly across splits
-- Or: bit-reverse sequential IDs, or use UUIDs generated by Spanner
CREATE TABLE orders (
  order_id STRING(36) NOT NULL DEFAULT (GENERATE_UUID()),
  customer_id STRING(36) NOT NULL,
  order_date TIMESTAMP NOT NULL,
  total_amount FLOAT64,
) PRIMARY KEY (order_id);

If you need sequential IDs for business reasons (order numbers), use them as a non-primary-key column and use a UUID as the actual primary key:

CREATE TABLE orders (
  order_uuid STRING(36) NOT NULL DEFAULT (GENERATE_UUID()),
  order_number INT64 NOT NULL,  -- Sequential for customers, not the PK
  customer_id STRING(36) NOT NULL,
  order_date TIMESTAMP NOT NULL,
  total_amount FLOAT64,
) PRIMARY KEY (order_uuid);

CREATE UNIQUE INDEX idx_order_number ON orders (order_number);

Interleaved Tables: Co-locating Parent-Child Data

Interleaved tables physically co-locate child rows with their parent rows on the same split. This is Spanner's mechanism for making parent-child reads efficient without cross-node joins.

-- Parent table
CREATE TABLE customers (
  customer_id STRING(36) NOT NULL DEFAULT (GENERATE_UUID()),
  email STRING(256) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP()),
  name STRING(256),
) PRIMARY KEY (customer_id);

-- Child table interleaved under customers
-- customer_id is the first part of the primary key
CREATE TABLE orders (
  customer_id STRING(36) NOT NULL,
  order_id STRING(36) NOT NULL DEFAULT (GENERATE_UUID()),
  order_date TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP()),
  total_amount FLOAT64 NOT NULL,
  status STRING(50) DEFAULT "pending",
) PRIMARY KEY (customer_id, order_id),
  INTERLEAVE IN PARENT customers ON DELETE CASCADE;

-- Grandchild table: order line items
CREATE TABLE order_items (
  customer_id STRING(36) NOT NULL,
  order_id STRING(36) NOT NULL,
  item_id STRING(36) NOT NULL DEFAULT (GENERATE_UUID()),
  product_id STRING(36) NOT NULL,
  quantity INT64 NOT NULL,
  unit_price FLOAT64 NOT NULL,
) PRIMARY KEY (customer_id, order_id, item_id),
  INTERLEAVE IN PARENT orders ON DELETE CASCADE;

With this schema, customers, orders, and order_items rows for the same customer are physically stored together. Querying a customer's complete order history (including line items) is a local operation — no cross-split reads.

-- This query is efficient: all data is co-located
SELECT
  c.name,
  o.order_date,
  o.total_amount,
  oi.product_id,
  oi.quantity
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi ON o.customer_id = oi.customer_id AND o.order_id = oi.order_id
WHERE c.customer_id = @customer_id
ORDER BY o.order_date DESC;

Secondary Index Design

Secondary indexes in Spanner have important characteristics that differ from PostgreSQL:

1. Indexes are stored independently (not co-located with the base table)
2. Index rows are distributed by the index key, which may be on different nodes than the base table
3. If a query uses a secondary index but also needs columns not in the index, Spanner does a back-join — potentially crossing splits

-- Index on email for customer lookup
CREATE UNIQUE INDEX idx_customers_email ON customers (email);

-- Storing additional columns avoids back-join for common queries
CREATE INDEX idx_orders_by_status ON orders (status, order_date DESC)
  STORING (total_amount, customer_id);  -- Avoids back-join for status dashboard queries

-- Index with null filtering: exclude NULL statuses from index
CREATE INDEX idx_active_orders ON orders (customer_id, order_date DESC)
  WHERE status IS NOT NULL;

Force Index Hints

Spanner's query optimizer usually picks the right index, but you can force it:

-- Force use of a specific index
SELECT *
FROM orders@{FORCE_INDEX=idx_orders_by_status}
WHERE status = 'pending'
  AND order_date >= '2025-01-01'
ORDER BY order_date DESC
LIMIT 100;

Transactions: Read-Write and Read-Only

Spanner supports three transaction types:

Read-write transactions: Full ACID with locking. Use for any operation that writes data.

from google.cloud import spanner

client = spanner.Client(project="my-project")
instance = client.instance("my-spanner-instance")
database = instance.database("my-database")

def transfer_funds(from_account: str, to_account: str, amount: float):
    """Transfer funds atomically between two accounts."""
    def txn_body(transaction):
        # Read current balances (with lock)
        from_row = transaction.execute_sql(
            "SELECT balance FROM accounts WHERE account_id = @id",
            params={"id": from_account},
            param_types={"id": spanner.param_types.STRING},
        ).one()

        to_row = transaction.execute_sql(
            "SELECT balance FROM accounts WHERE account_id = @id",
            params={"id": to_account},
            param_types={"id": spanner.param_types.STRING},
        ).one()

        if from_row[0] < amount:
            raise ValueError("Insufficient funds")

        # Atomic updates
        transaction.execute_update(
            "UPDATE accounts SET balance = balance - @amount WHERE account_id = @id",
            params={"amount": amount, "id": from_account},
            param_types={"amount": spanner.param_types.FLOAT64, "id": spanner.param_types.STRING},
        )
        transaction.execute_update(
            "UPDATE accounts SET balance = balance + @amount WHERE account_id = @id",
            params={"amount": amount, "id": to_account},
            param_types={"amount": spanner.param_types.FLOAT64, "id": spanner.param_types.STRING},
        )

    database.run_in_transaction(txn_body)

Read-only transactions: Consistent snapshots without locks. Use for reporting queries that need consistency but don't write.

with database.snapshot() as snapshot:
    results = snapshot.execute_sql(
        "SELECT * FROM orders WHERE status = 'pending' ORDER BY order_date"
    )

Stale reads: Read at a specific timestamp in the past, slightly faster and cheaper.

import datetime

with database.snapshot(exact_staleness=datetime.timedelta(seconds=15)) as snapshot:
    # Reads data from 15 seconds ago — useful for analytics dashboards
    results = snapshot.execute_sql("SELECT COUNT(*) FROM orders WHERE status = 'pending'")

Spanner Emulator for Local Development

Run the Spanner emulator locally to avoid cloud costs during development:

# Pull and start the Spanner emulator
docker run -p 9010:9010 -p 9020:9020 gcr.io/cloud-spanner-emulator/emulator

# Configure the Cloud SDK to use the emulator
export SPANNER_EMULATOR_HOST="localhost:9010"

# Create instance and database in the emulator
gcloud spanner instances create test-instance   --config=emulator-config   --description="Test"   --nodes=1

gcloud spanner databases create my-database   --instance=test-instance   --ddl-file=schema.sql

The emulator is functionally compatible with the production Spanner API, so your application code works without modification against both the emulator and production.

For choosing between Spanner, Cloud SQL, and AlloyDB, see our GCP database selection guide. For the FinOps approach to managing Spanner costs, see our Google Cloud FinOps guide.