Q: How does Google Cloud Spanner achieve external consistency (serializable ACID transactions) across continents without locking the entire database? What primary key anti-pattern causes severe write throttling, and how do you fix it?

How Spanner coordinates global transactions without central clock drift:

• TrueTime API: Google uses synchronized GPS atomic clocks in datacenters to expose time as an interval [earliest, latest] with bounded uncertainty ε < 7ms.
• Commit Wait: When writing a transaction, Spanner assigns a commit timestamp and waits out the uncertainty window (commit wait) before releasing locks.
• Paxos Replication: Data is split into 'splits' (table ranges), each managed by a Paxos group of replicas across regions.
• SLA: Multi-region configurations achieve five-nines (99.999%) availability with zero scheduled maintenance downtime.

Why traditional auto-incrementing IDs destroy Spanner write throughput:

• The Anti-Pattern: Using monotonically increasing keys like AUTO_INCREMENT, sequential integers, or TIMESTAMP as the primary key root.
• The Consequence: All new writes land on the exact same Paxos split and server node, overloading one CPU while hundreds of other nodes sit idle.
• Symptoms: Extreme write latency spikes (10ms -> 2,000ms), DEADLINE_EXCEEDED errors, and CPU starvation.

Distribute writes uniformly across the entire cluster:

• Bit-Reversed Sequences: Used GET_BIT_REVERSED_SEQUENCE() in Cloud Spanner to convert sequential numbers into randomly distributed integer values.
• UUIDv4: Switched primary keys to randomly generated UUIDv4 strings.
• Hash Sharding: For timestamp queries, prepended a synthetic shard ID prefix: FARM_FINGERPRINT(user_id) % 10.

Leverage stale reads to avoid taking read locks:

• Stale Reads: Configured non-critical read queries to use max_staleness=15s or exact_staleness=10s.
• Local Replica Execution: Stale reads execute on nearest local read-only replicas without coordinating with the Paxos leader in the witness region.
• Outcome: Read latency dropped from 45ms to 2ms for global user queries.