Seat Inventory With a Global Consistency SLA and a Sub-150 ms Write Target
TicketNova sells concert seats in 14 countries. Seat inventory lives in Cloud SQL for PostgreSQL Enterprise Plus: primary in us-central1, cross-region read replicas in europe-west1 and asia-east1. All checkout writes go to the primary. During a Friday sale, Berlin buyers saw 380 ms p95 write latency and Singapore buyers 610 ms; 2.1% of checkouts failed with serialization errors after the team introduced optimistic locking. Two oversell incidents occurred when a regional service read a replica and confirmed a seat before the write replicated. Daily volume is 22 million seat-lock transactions against a 40-million-row inventory table; 87% of operations are reads of seat maps, and 0.4% of reads must be strictly current (the final seat confirmation). Leadership now requires 99.999% availability, p95 write latency under 150 ms on all three continents, zero double-sold seats, and reuse of the team's existing PostgreSQL drivers and SQL. Budget is approved for a substantial re-platform.
1. Recommend a target database and topology. Your answer must resolve the apparent conflict between 99.999% availability, sub-150 ms writes on three continents, and zero oversell.
Read the worked answer
The constraint set is not simultaneously satisfiable as stated, so the first job is to expose the physics. A strongly consistent write must reach a single leader and commit after clock-uncertainty wait. Round-trip time from Singapore to us-central1 is roughly 180-220 ms before any database work; no product choice fixes that. So either latency, or consistency scope, has to change.
Recommendation: shard inventory by venue region and make consistency local. Each event's seat inventory is owned by exactly one region (the venue's region) and stored in a Spanner instance whose leader sits in that region. A Berlin buyer purchasing a Frankfurt concert writes to europe-west1; a Berlin buyer purchasing a Los Angeles concert writes to us-central1 and legitimately sees higher latency. This satisfies the 150 ms target for the dominant case (buyers mostly buy local events) and keeps zero oversell because there is exactly one leader per seat row.
Why Spanner rather than Cloud SQL: a multi-region Spanner configuration is the only option that reaches a 99.999% SLA, and its external consistency removes the replica-read oversell class entirely. A 99.99% regional configuration can be used for lower-value shards, but then the 99.999% claim applies only to the shards that need it - state that explicitly in the design doc rather than implying a global five-nines figure.
Why not the alternatives: Cloud SQL cross-region replicas are asynchronous, so a promotion loses transactions (violates zero oversell after failover) and replica reads caused the two incidents already observed. Multi-region Spanner with a single leader preserves consistency but reintroduces cross-continent write latency for every buyer.
Driver constraint: Spanner's PostgreSQL interface accepts PostgreSQL DDL and many functions but is not wire-compatible with standard PostgreSQL drivers; the data-access layer must move to Spanner client libraries. Budget that rewrite explicitly - it is the largest hidden cost of this decision.
2. Design the read path for seat maps (87% of operations) so that sharding and strong consistency do not degrade the browsing experience or reintroduce oversell.
Read the worked answer
Split reads into two classes with different guarantees. A seat-map render is a hint; the seat confirmation is a fact. Only the fact needs a strongly consistent read.
Browsing reads: serve seat maps from bounded-staleness reads (10-15 seconds) against the nearest read-only replica of the owning shard, or from Memorystore with a short TTL. Seat maps change slowly relative to render rate, so 10-second staleness is acceptable to users and removes most cross-region read traffic. Instrument 'map shows free, confirmation fails' as a product metric - expect it to be non-zero and acceptable.
Confirmation reads: the final seat claim must be a read-write transaction on the leader that re-checks the seat state and writes the reservation in the same transaction. Never trust a cached or replica-read value here; this is precisely the bug that produced the two oversell incidents.
Cache invalidation: drive Memorystore invalidation from Spanner change streams rather than TTL alone, so a sold seat disappears from maps within seconds without polling the database.
Serialization retries: the 2.1% error rate came from optimistic locking plus long transactions. Replace it with short single-shard read-write transactions and an exponential-backoff retry loop with an idempotency key on the reservation, so a retried commit cannot duplicate a hold. Keep transactions to a few milliseconds of work - no application callouts inside the transaction.
Guardrail: because inventory is one row per seat rather than one row per event, hotspot risk inside a shard is real for a stadium on-sale. Verify with key-visualizer-style analysis that no single split serves the whole hot event, and pre-split if needed. If a single event exceeds a split's write throughput, introduce a small per-seat counter shard rather than a global lock.
3. Describe how you would migrate from the current Cloud SQL primary to the sharded Spanner design with no oversell and a rollback path.
Read the worked answer
Phase 1 - dual-write shadow, read-only validation. Create the Spanner shards and a migration service that reads the Cloud SQL change stream (logical replication slot) and applies mutations to Spanner. Keep Cloud SQL authoritative. Run a continuous validator that compares, per event, the set of held seats in both stores and reports drift; do not proceed until drift is zero for 72 hours across peak sales.
Phase 2 - cut over one region at a time, not one table. Pick a low-traffic region, flip that region's write path to Spanner behind a feature flag keyed by venue region, keep the reverse replication Cloud SQL←Spanner running for 48 hours, and keep the flag reversible in seconds. This is the judgment call: a big-bang cutover gives you one window in which every bug is fatal; regional cutover gives you fourteen smaller windows.
Phase 3 - verify invariants, not row counts. Correctness here is 'no seat is held twice and no paid seat is lost'. Assert that at the transaction level by reconciling a per-event seat-state hash, plus a financial reconciliation against the payment ledger. Row-count comparison will not catch the failure mode that matters.
Phase 4 - decommission. Stop reverse replication, snapshot Cloud SQL to GCS for the retention period, drop the primary, and keep a documented restore path that includes the Spanner export (Avro to GCS) alongside the legacy dump.
Rollback trigger criteria must be written before cutover: >0.01% failed writes for 10 minutes, or any reconciliation drift, triggers flag rollback. Because Phase 1-2 keeps both stores current, rollback is a flag flip plus a bounded reconcile, not a restore.