Google Spanner: Globally Distributed SQL at Scale

Google Spanner solves a genuinely hard problem: managing petabytes of data across data centers worldwide while keeping the relational model that SQL developers rely on. It launched around 2007 as an internal project, influenced by what engineers called “Bigtable 2”, and became Cloud Spanner in 2017. The selling point was SQL plus horizontal scalability without throwing away ACID guarantees.

---

Architecture Overview

Spanner arranges data in layers that blend traditional database concepts with distributed systems patterns.

At the center is the directory, a B-tree-like structure that serves as the unit of data placement. Directories can move between servers and zones dynamically, which sounds complicated but happens transparently to running transactions.

The hierarchy: databases contain tables, tables contain directories, directories contain key-value pairs. Each directory falls under a Paxos group controlling replication.

graph TD
    A[Cloud Spanner Instance] --> B[Database 1]
    A --> C[Database 2]
    B --> D[Table: Users]
    B --> E[Table: Orders]
    D --> F[Directory: User Data]
    E --> G[Directory: Order Data]
    F --> H[Paxos Group - 3+ Replicas]
    G --> I[Paxos Group - 3+ Replicas]
    H --> J[Replica 1 - US East]
    H --> K[Replica 2 - US West]
    H --> L[Replica 3 - Europe]

Spanner stores data in tablets (inherited from Bigtable thinking). Each tablet spans a range of primary keys and is managed by a Paxos group. Paxos ensures all replicas agree before writes are acknowledged.

---

TrueTime: Global Consistency Through Clock Uncertainty

TrueTime is Spanner’s most distinctive feature. It uses GPS receivers and atomic clocks in each data center to produce globally consistent timestamps.

TrueTime does not claim clocks are perfectly synchronized. Instead, it makes clock uncertainty explicit as a bounded interval. Call TT.now() and you get back a TTinterval with earliest and latest possible timestamps:

# TrueTime returns a time interval, not a point
tt = TT.now()
# tt.earliest = timestamp that could be in the past
# tt.latest = timestamp that could be in the future
# The actual time is somewhere within this interval

The uncertainty bound sits around 1-7 milliseconds. Spanner leverages these intervals for external consistency: if transaction T1 commits before T2 starts in real-time, then T1’s timestamp is less than T2’s. That is the guarantee that matters in practice.

This means globally consistent reads without a single time source synchronizing everything. Read at a specific timestamp from anywhere on Earth, and Spanner returns results consistent with that moment.

TrueTime needs specialized hardware, which is why direct competitors are rare. But the conceptual model has influenced other systems to adopt “clock uncertainty” thinking.

---

Schema and SQL Support

Spanner gives you a relational schema with SQL. Unlike NoSQL systems that offer query capabilities as an afterthought, Spanner includes:

• Standard SQL with JOINs, aggregations, subqueries
• Inline primary keys and foreign keys
• Interleaved tables for parent-child relationships
• Generated columns and indexes

-- Spanner supports standard SQL with relational features
CREATE TABLE Users (
  UserId STRING(64) NOT NULL,
  Email STRING(256),
  CreatedAt TIMESTAMP,
) PRIMARY KEY (UserId);

CREATE TABLE Orders (
  OrderId STRING(64) NOT NULL,
  UserId STRING(64),
  Total DECIMAL(10,2),
  Status STRING(32),
) PRIMARY KEY (OrderId);

-- Foreign key support
ALTER TABLE Orders ADD CONSTRAINT FK_UserOrders
  FOREIGN KEY (UserId) REFERENCES Users(UserId);

-- Create index for common query pattern
CREATE INDEX idx_orders_user_status ON Orders(UserId, Status);

The INTERLEAVE IN clause co-locates related rows physically, cutting the need for distributed joins. When you interleave child rows with their parent, Spanner puts them in the same tablet. Queries run without network round-trips.

Partition Key Design

Choosing a good primary key matters more in Spanner than in a single-node database. Since data is range-partitioned by key, a poorly chosen key creates hotspots that degrade performance.

Avoid sequential keys for high-write tables. Auto-incrementing integers cause all writes to hit a single tablet. UUIDs or hashed keys spread load across nodes:

-- Bad: sequential key creates hotspots
CREATE TABLE Events (
    event_id INT64 AUTO_INCREMENT,
    payload STRING(MAX),
    PRIMARY KEY (event_id)
);

-- Good: uniformly distributed key
CREATE TABLE Events (
    event_id STRING(36) NOT NULL,
    payload STRING(MAX),
    PRIMARY KEY (event_id)
) PRIMARY KEY (event_id);

For read-heavy tables where you query by user ID or customer ID most often, use that ID as the leading key. For write-heavy tables generating many rows per user, consider a composite key with a suffix to distribute writes.

Interleaved Tables

Interleaved tables model parent-child relationships where child rows are co-located with their parent on the same storage node. This improves read performance when you frequently fetch a parent and its children together.

CREATE TABLE Orders (
    order_id STRING(36),
    customer_id STRING(36),
    total DECIMAL,
    created_at TIMESTAMP,
    PRIMARY KEY (order_id)
) PRIMARY KEY (order_id);

CREATE TABLE OrderItems (
    order_id STRING(36),
    item_id STRING(36),
    product_id STRING(36),
    quantity INT64,
    PRIMARY KEY (order_id, item_id)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

With INTERLEAVE IN PARENT Orders ON DELETE CASCADE, deleting an order automatically deletes its items. The real win is that fetching an order with its items stays in a single tablet read, avoiding a distributed query.

Trade-offs: Interleaving shines when you almost always fetch parent and child together. But if you often query children independently, it backfires: all items for a given parent land in the same tablet, which becomes a hotspot for popular parents. Deleting the parent also gets pricier since Spanner has to delete all the children first.

---

Transaction Handling

Spanner handles two transaction types: read-write and read-only.

Read-write transactions run two-phase commit (2PC) over Paxos. Nodes prepare first, promising to commit if asked. Then the coordinator tells them to commit or abort. This gives atomicity across distributed nodes, but adds latency.

Read-only transactions skip locks. They take a timestamp bound and read from consistent snapshots. No blocking, no contention.

Spanner also supports partitioned transactions for high-contention workloads, splitting large writes across servers for better throughput.

sequenceDiagram
    participant C as Coordinator
    participant P1 as Participant 1
    participant P2 as Participant 2

    Note over C,P2: Two-Phase Commit Protocol

    C->>P1: Prepare
    C->>P2: Prepare
    P1-->>C: Promise to Commit
    P2-->>C: Promise to Commit

    Note over C: Coordinator decides COMMIT

    C->>P1: Commit
    C->>P2: Commit
    P1-->>C: Committed
    P2-->C: Committed

---

Replication and Consistency

Spanner uses Paxos-based replication with configurable replica counts (typically 3-5), replica placement across regions for disaster recovery, and witness replicas that participate in Paxos without storing data.

The Paxos leader handles all reads and writes, but read leases let followers serve reads locally for a period. This cuts latency for read-heavy workloads.

By default, Spanner provides external consistency, stronger than serializability. If T1 commits before T2 starts in real-time, T1 happens before T2. Most applications need exactly this guarantee.

---

Direct Connections and Performance

Google recommends direct connections over the Proxy. Direct connections use gRPC and are faster, especially for apps running in Google Cloud.

Rough numbers:

• Write latency: 10-50ms depending on distance and replica setup
• Read latency: 1-10ms for local replica reads
• Throughput scales horizontally as you add nodes

Each node handles about 10,000 QPS reads or 2,000 QPS writes. Your mileage varies with workload patterns.

---

Use Cases and Trade-offs

Spanner works well for:

• Globally distributed databases needing strong consistency
• Financial systems requiring ACID transactions across regions
• Multi-tenant SaaS needing isolation and scalability
• Inventory or order management with consistent cross-region updates

Shortcomings:

• Cost: Not cheap compared to self-managed databases
• TrueTime dependency: Needs Google’s hardware for full benefit
• Latency floor: Even local reads have 1-2ms minimum
• Query optimization: Less mature than PostgreSQL or MySQL

---

Production Failure Scenarios

Failure	Impact	Mitigation
Zone outage (single AZ failure)	Spanner keeps serving from other zones; TrueTime bounds how stale reads can get	Multi-zone deployment; Spanner replicates synchronously across zones
Leader election during high write load	Short unavailability or latency spike while a new leader takes over	TrueTime uncertainty caps the maximum outage window; consider allowing leaderless reads
TrueTime hardware failure	TrueTime uncertainty grows until you replace the hardware; reads can return slightly stale data	Monitor clock synchronization closely; replace TrueTime hardware quickly when errors surface
Schema change during heavy load	Online schema changes hit limits on very large tables	Plan schema changes for low-traffic periods; be careful with ALTER TABLE on big datasets
Cross-region replication lag	Non-local replicas see higher read latency	Route read/write traffic to regional endpoints; globally consistent reads inherently need cross-region latency
Network partition	Isolated region cannot reach the replica majority	Spanner stays available in the majority partition; isolated region serves cached data until the partition heals
Transaction conflict rate spike	More retries and latency when concurrent transactions touch overlapping key ranges	Prefer read-only transactions where feasible; structure your key schema to reduce write contention

---

Common Pitfalls / Anti-Patterns

Choose Spanner When

The strongest case for Spanner is global distribution with concurrent writes across geographic regions. If your application writes from data centers in London, Tokyo, and Sydney simultaneously, most databases force you to build reconciliation logic to handle conflicting updates. Spanner’s external consistency handles this natively. A transaction in London sees the same state as a concurrent transaction in Tokyo because TrueTime’s commit-wait rule guarantees that if T1 commits before T2 starts in real time, T1’s timestamp is less than T2’s everywhere on Earth. This is stronger than serializability or snapshot isolation—it preserves real-time ordering across continents. Financial systems, inventory management, and collaborative tools are the obvious use cases here.

Horizontal scalability is the second major draw. When your data outgrows a single node, Spanner moves directories between servers automatically without re-sharding your application. You add nodes and Spanner rebalances load. For teams that have hit PostgreSQL limits but cannot rewrite their SQL codebase, this is the practical path forward. Scaling is automatic and transparent to application code.

Managed operations matter when your team lacks database operations expertise. Spanner handles replication, failover, load balancing, and storage rebalancing without user intervention. You do not configure Paxos groups or tune split boundaries. This operational simplicity comes at a premium, but it frees engineers to focus on product instead of database administration.

Compliance-driven data locality is another reason Spanner comes up in these conversations. When regulations like GDPR or government contracts require data to stay within specific geographic boundaries, Spanner’s regional configuration controls where data lives while maintaining cross-region disaster recovery. You get data residency without sacrificing availability.

The workloads that fit well include multi-region e-commerce platforms with inventory updates from warehouses worldwide, financial ledgers processing multi-currency transactions across time zones, SaaS applications serving globally distributed users writing to the same logical database, and gaming backends where players in different regions need consistent game state.

Scenario	Why Spanner Fits
Multi-region OLTP with strong consistency	External consistency across regions without reconciliation code
PostgreSQL scaling ceiling reached	Automatic horizontal scaling without SQL rewrite
Regulatory data residency requirements	Regional configuration with cross-region disaster recovery
Mission-critical workload needing hands-off ops	Managed replication, failover, and rebalancing
Globally distributed collaborative apps	TrueTime ensures all regions see the same state

Avoid Spanner When

Spanner is a poor fit when your workload does not need its defining features. If your application runs in a single region and does not write from multiple geographic locations simultaneously, you are paying for global distribution you will never use. PostgreSQL with read replicas or Aurora handles single-region OLTP workloads at a fraction of the cost, with more mature query optimization and a richer ecosystem of extensions and tooling.

Cost sensitivity is the most common reason to rule out Spanner. Spanner’s pricing is not iterative; it is step-function expensive. Storage at $0.17-0.25/GB/month plus compute at $0.045-0.09/vCPU-hour adds up fast at production scale. A 16-vCPU regional deployment runs roughly $525/month in compute alone before storage and egress. For a startup or a cost-conscious team, that budget buys a lot of RDS or self-managed PostgreSQL. The managed operations convenience is real, but it comes at a premium that only makes sense when the alternative is building and operating your own distributed database.

High-frequency short transactions are where Spanner’s latency floor becomes a liability. Even local reads carry a 1-2ms minimum due to Paxos overhead. For workloads like real-time bidding, session tracking, or rate-limiting where you need sub-millisecond latency, this floor is too high. In-memory systems like Redis or Memcached handle these workloads at orders of magnitude lower latency. Spanner trades raw speed for correctness at global scale.

Advanced query optimization is another area where Spanner lags. Its SQL support is solid for standard OLTP queries, but PostgreSQL and MySQL have had decades of query planner refinement. Complex aggregations, parallel query execution, advanced indexing strategies, and JSON manipulation are better served by mature relational engines. If your workload is query-heavy with complex joins and you do not need global writes, PostgreSQL or its managed variants are the better choice.

Vendor lock-in is a legitimate concern for teams that value flexibility. Spanner is a Google-specific implementation. TrueTime depends on hardware Google deploys in its data centers. You cannot run Spanner on-premises or on another cloud provider. If open-source portability matters for your architecture, CockroachDB offers similar semantics—external consistency via HLC instead of TrueTime—with self-hosted and multi-cloud deployment options.

Scenario	Better Alternative	Why
Single-region OLTP	PostgreSQL, Aurora, MySQL	Lower cost, mature optimizer, richer ecosystem
Sub-millisecond latency needs	Redis, Memcached	In-memory speed, orders of magnitude lower latency
Query-heavy with complex joins	PostgreSQL, analytical databases	Decades of query planner refinement
Cost-sensitive at scale	Self-managed PostgreSQL, CockroachDB	Fraction of Spanner’s cost with similar guarantees
Open-source portability required	CockroachDB, self-managed distributed DB	HLC-based consistency without vendor lock-in

Decision Framework

flowchart TD
    A[Need Horizontal Scale?] --> |Yes| B[Need Global Distribution?]
    A --> |No| E[Consider PostgreSQL or MySQL]
    B --> |Yes| C[Need Strict Consistency?]
    B --> |No| F[Consider DynamoDB or Cassandra]
    C --> |Yes| D[Can Afford Premium Pricing?]
    C --> |No| F
    D --> |Yes| G[Spanner is a Strong Fit]
    D --> |No| H[Consider CockroachDB or Aurora Global]

---

---

Quick Recap Checklist

• Spanner arranges data in directories (B-tree-like structures) grouped into Paxos groups for replication
• TrueTime uses GPS and atomic clocks to produce bounded clock uncertainty intervals, enabling globally consistent reads
• Schema supports standard SQL with foreign keys, interleaved tables, generated columns, and indexes
• Read-write transactions use two-phase commit over Paxos; read-only transactions use timestamp-bound snapshots
• Primary key design matters: avoid sequential keys for write-heavy workloads to prevent hotspots
• Interleaved tables co-locate parent-child rows but can create hotspots for popular parents
• External consistency (stronger than serializable) comes from TrueTime’s commit-wait rule
• Spanner works best for globally distributed OLTP, financial systems, and multi-tenant SaaS
• Cost scales linearly with nodes: storage ($0.17-0.25/GB/mo), compute ($0.045-0.09/vCPU-hour)

---

Interview Questions

---

Further Reading

• TrueTime: Time You Can Trust — How GPS and atomic clocks enable globally consistent timestamps
• CAP Theorem: Beyond the Binary — Why consistency vs availability tradeoffs define distributed systems
• CAP Theorem explains why consistency across partitions is hard
• Consistency Models covers external consistency and snapshot isolation
• Database Replication discusses Spanner’s replication strategies
• Database Replication: From Primary to Multi-Primary — How replication topologies impact consistency and availability
• Distributed Transactions explains two-phase commit
• Distributed Transactions: Two-Phase Commit — The consensus puzzle and how Spanner solves it with Paxos

---

Operational Guidance

Backup and Restore

Spanner handles backups automatically. The service takes continuous backups in the background, covering all data and indexes without you configuring anything. This automatic backup runs on an internal schedule and gives you point-in-time recovery (PITR) coverage for the last 7 days. If you need to restore a database, you pick any timestamp within that window and Spanner reconstructs your data to that point. This covers accidental data deletion, bad schema changes, and most ransomware scenarios without you touching a backup configuration.

The 7-day PITR window is worth understanding clearly. Spanner can restore to any point within the last 7 days, but this is not a rolling window you can stretch longer. If compliance requires longer retention, you need on-demand backups on top of PITR. On-demand backups are available through the Cloud Console or the spanner_backup.sh script, and these can be retained indefinitely. You can also trigger on-demand backups before risky operations like bulk data migrations or major schema changes as an extra safety net.

Cross-region backup setup is manual and takes engineering time. Spanner’s built-in backup infrastructure stores backups within the same region as your instance. If you have strict data residency requirements or want disaster recovery beyond a single region, you need to export data to Avro or Parquet files in Cloud Storage and then configure cross-region copy jobs using Cloud Storage Transfer Service or equivalent tooling. This is not a native Spanner feature—you are building a custom pipeline. Budget a few engineering days to design and implement this if your compliance or DR strategy depends on it.

Schema Changes

Spanner supports online schema changes via ALTER TABLE statements. There is no downtime for schema changes, but they go through an internal schema upgrade process that can take time on very large tables.

# Online schema change with ALTER TABLE
operation = client.update_database_ddl(
    database_id,
    ['ALTER TABLE Users ADD COLUMN last_login TIMESTAMP']
)
operation.result()  # Waits for completion

Schema change best practices:

• Add one column at a time rather than batching multiple changes
• Avoid large DEFAULT values that increase write amplification
• Use NOT NULL only when truly required, since it forces a full table rewrite
• Schedule large schema changes during low-traffic windows when possible

Import and Export

Cloud Dataflow handles bulk import and export. Spanner exports to Avro or Parquet in Cloud Storage. For imports, point Dataflow at your Cloud Storage files and let Spanner do the distributed write.

# Cloud Dataflow export job configuration (pseudocode)
export_job = {
    "target_project": "my-project",
    "target_instance": "my-instance",
    "target_database": "my-db",
    "output_prefix": "gs://my-bucket/spanner-export/",
    "output_format": "AVRO",  # or PARQUET
    "batch_size": 10000
}

For small datasets, the Cloud Console provides a no-code import wizard. For production workloads, always use Dataflow with appropriate batch sizing.

---

Cost Modeling

Spanner pricing has three main components: storage, compute, and networking egress.

Storage

Storage is charged per GB per month. Regional storage runs about $0.17/GB/month, while multi-region configurations (needed for global write distribution) cost roughly $0.25/GB/month. One thing that trips people up: since Spanner keeps multiple replicas synchronously, you effectively pay for 3x the raw data at the regional rate. Storage pricing covers all data, indexes, and the replication overhead.

A few operational notes worth knowing:

• Storage is billed on peak usage, not average. Bulk-load 10TB and delete 8TB the same month, and you get billed for 10TB.
• Indexes count toward storage. Each composite index you add costs proportionally to your table size, and on wide tables that adds up fast.
• Spanner enforces a 1GB minimum storage per database, even if your actual data is under 1GB.
• Backup storage is billed separately at standard Cloud Storage rates. Cross-region backup exports will hit your egress budget.

# Check current storage usage via the Cloud API
def get_spanner_storage_usage(instance_id, database_id):
    instance = client.instance(instance_id)
    database = instance.database(database_id)

    # Get storage stats
    stats = database.get_storage_stats()
    return {
        "storage_bytes": stats.storage_bytes,
        "backup_bytes": stats.backup_storage_bytes,
        "effective_storage_gb": stats.storage_bytes / (1024**3)
    }

Spanner handles storage rebalancing automatically. There is no manual sharding or partitioning to manage.

Compute

Compute is provisioned as nodes with a set vCPU count. Regional configurations cost about $0.045/vCPU-hour, while multi-region setups run roughly $0.09/vCPU-hour, roughly double the regional rate because Spanner has to coordinate writes across more locations.

A few things that differ from traditional databases:

• Node-based provisioning means you pick the number of nodes and vCPUs per node, not individual machine sizes. Each regional node typically has 64 vCPUs.
• A rough throughput baseline is 10,000 QPS reads or 2,000 QPS writes per node, though query complexity and key distribution will shift your actual numbers.
• Spanner bills for provisioned compute at full rate regardless of utilization. Run a 16-vCPU config at 5% load, still pay for all 16 vCPUs.

# Estimate compute cost for your configuration
def estimate_compute_cost(vcpus, hours_per_month=730, region_type="regional"):
    rates = {
        "regional": 0.045,
        "multi-region-us": 0.07,
        "multi-region-global": 0.09
    }
    rate = rates.get(region_type, 0.045)
    return vcpus * rate * hours_per_month

# 16 vCPUs, regional, 730 hours (month)
# 16 * 0.045 * 730 = $525.60/month

One gotcha on sizing: Spanner autoscaling is storage-based, not compute-based. If you need more throughput you add nodes manually, and that takes time. Size for peak write load from the start rather than trying to react mid-incident.

Networking Egress

Networking charges apply when data leaves Google Cloud infrastructure. Cross-region writes and reads within Spanner are generally cheap, but egress to the public internet or to other cloud providers adds up quickly.

The breakdown that matters:

• Intra-Spanner traffic between replicas in the same instance is free. All read/write traffic Spanner handles internally does not count toward egress.
• Egress to the public internet is what costs. Every byte your application serves to end users counts. A 10GB/day workload runs about $36/month in egress at standard Cloud rates.
• Cross-region access is another gotcha. If your app servers are in us-east1 but your Spanner instance is in europe-west1, each query result crossing a region boundary adds to your bill.
• Data exports to Cloud Storage in a different region, or reads from a distant read replica, all add egress costs.

# Estimate monthly egress cost
def estimate_egress_cost(egress_gb_per_month, region_type="regional"):
    # Egress rates vary by region and destination
    # Standard internet egress: ~$0.12/GB
    egress_rate = 0.12  # conservative estimate

    return egress_gb_per_month * egress_rate

# Example: 500GB/month public internet egress
# 500 * 0.12 = $60/month

Keeping egress costs manageable comes down to placement: put your application servers in the same region as your Spanner instance, use regional read replicas for geographically distributed read-heavy workloads, and cache aggressively where slightly stale reads are acceptable.

Cost Comparison Example

Configuration	Storage (1TB)	Compute (16 vCPU)	Estimated Monthly Cost
Single-region	$170/month	$26/month (regional)	~$196/month
Multi-region (US)	$170/month	$52/month (multi-region)	~$222/month
Multi-region (global)	$250/month	$104/month	~$354/month

These numbers assume 24/7 operation at full compute capacity. Actual costs vary with utilization.

Cost Estimation Pseudocode

def estimate_monthly_spanner_cost(
    storage_gb: float,
    vcpus: int,
    region_type: str = "regional",  # "regional" | "multi-region-us" | "multi-region-global"
    egress_gb: float = 0
) -> dict:
    # Storage pricing per GB/month
    storage_prices = {
        "regional": 0.17,
        "multi-region-us": 0.20,
        "multi-region-global": 0.25
    }
    # Compute pricing per vCPU-hour
    compute_prices = {
        "regional": 0.045,
        "multi-region-us": 0.07,
        "multi-region-global": 0.09
    }
    # Egress pricing per GB (approximate)
    egress_rate = 0.12

    hours_per_month = 730  # Average month

    storage_cost = storage_gb * storage_prices[region_type]
    compute_cost = vcpus * compute_prices[region_type] * hours_per_month
    egress_cost = egress_gb * egress_rate

    return {
        "storage": round(storage_cost, 2),
        "compute": round(compute_cost, 2),
        "egress": round(egress_cost, 2),
        "total": round(storage_cost + compute_cost + egress_cost, 2)
    }

# Example: 1TB storage, 16 vCPU, multi-region US, 100GB egress
cost = estimate_monthly_spanner_cost(
    storage_gb=1024,
    vcpus=16,
    region_type="multi-region-us",
    egress_gb=100
)
# cost["total"] ~ $270/month

Plug your numbers into the Google Cloud Pricing Calculator for real estimates. One practical note: Spanner costs scale linearly with nodes, so you can start small for dev/test environments and scale up when you go to production.

---

Conclusion

Spanner combines SQL with horizontal scalability and strong consistency. It shows you do not have to throw away ACID guarantees to get NoSQL-like scaling. TrueTime hardware is the main barrier to competition, but the ideas have shaped how engineers think about distributed consistency.

For globally distributed apps needing strong consistency, Spanner is worth the cost. For others, studying its architecture teaches you the real trade-offs in distributed database design.