Cloud SQL vs AlloyDB vs Spanner: A PostgreSQL Decision Framework on Google Cloud
Database selection fails when a benchmark number substitutes for a workload model. There is no universal TPS boundary where Cloud SQL becomes AlloyDB or Spanner; transaction shape, key distribution, latency, consistency, and operational ownership determine the break point.
Situation
Google Cloud offers three materially different relational services with PostgreSQL-adjacent interfaces: Cloud SQL for PostgreSQL, AlloyDB for PostgreSQL, and Spanner’s PostgreSQL interface. A fourth option—sharding PostgreSQL across Cloud SQL instances—preserves more PostgreSQL behavior but transfers distributed-systems work to the application and platform teams.
A useful decision must account for current requirements and credible growth: extensions, write locality, analytical contention, geographic placement, transaction boundaries, recovery objectives, and team capability.
The Problem
“PostgreSQL compatible” hides different meanings. Cloud SQL runs PostgreSQL with managed-service restrictions. AlloyDB provides high PostgreSQL compatibility over Google’s disaggregated architecture and adds service-specific capabilities. Spanner implements a PostgreSQL dialect and supported client compatibility over a different distributed database engine.
The original decision tree used 50,000 TPS as a rule of thumb. That threshold was unsupported and misleading. Ten thousand large, contended, multi-row transactions can be harder than a much larger number of independent point writes. The core question is: which hard requirement eliminates an option, and what evidence demonstrates the remaining choice?
Architecture Problem
The four options disagree at the point where a write becomes durable.
In Cloud SQL, an application sends a transaction to one PostgreSQL primary. In a regional HA configuration, Google runs a standby in another zone and synchronously replicates writes to regional persistent disk. The standby is not a read endpoint. A failover retains the instance connection name and IP address, but existing sessions close and applications must reconnect. Read replicas are separate, asynchronous resources; they can lag and are not substitutes for the HA standby. Cross-region promotion changes the serving instance and therefore belongs in a disaster-recovery runbook, not an automatic reconnect assumption.
In AlloyDB, writes still enter one writable primary instance, but compute and storage are disaggregated. Google documents that the active node writes WAL to a regional log persistor; log processing servers then materialize data blocks into regional storage. A highly available primary has an active and standby node in different zones. Read pool instances attach to the cluster storage and expose read-only endpoints. This reduces coupling between read compute and storage, but it does not distribute the writable SQL execution path across multiple primaries.
In Spanner, the primary key determines a key range, or split. Splits are replicated and have leaders. A read-write transaction that touches multiple splits coordinates across their participants; Spanner uses locking concurrency control, wound-wait deadlock prevention, distributed commit, and TrueTime in commit ordering. There is no PostgreSQL primary to resize or promote. The corresponding risk moves into schema distribution, transaction scope, contention, and client retries.
In sharded Cloud SQL, each shard has the Cloud SQL write path, but the routing layer becomes a new control plane. A tenant transfer, shard split, or cross-shard workflow now has two failure domains: PostgreSQL and the application-owned coordinator. The database service cannot make those independent primaries atomic.
flowchart TD
Write[Business transaction] --> CSQL[Cloud SQL — one PostgreSQL primary]
Write --> Alloy[AlloyDB — one writable primary]
Write --> Span[Spanner — key-range leaders]
Write --> Router[Shard router]
Router --> ShardA[Cloud SQL shard A]
Router --> ShardB[Cloud SQL shard B]
CSQL --> RegionalDisk[Regional persistent disk]
Alloy --> Log[Regional log persistor]
Log --> Blocks[Regional data blocks]
Span --> Consensus[Replicated split groups]
The failure boundary follows that write path. Cloud SQL and AlloyDB can lose sessions when compute fails even if durable data survives. Spanner can preserve service through replica failures while an application still suffers aborts or latency from a hot key range. Sharding can keep one tenant’s failure isolated while a routing error sends writes to the wrong owner. “Highly available” therefore needs an object: data, connection endpoint, transaction coordinator, region, or application workflow.
The Requirements-First Decision
flowchart TD
Start[Workload requirements] --> Compat{Needs PostgreSQL behavior unsupported by AlloyDB or Spanner}
Compat -->|Yes| Scale{One writer satisfies measured headroom}
Scale -->|Yes| CloudSQL[Cloud SQL]
Scale -->|No| Shard[Shard PostgreSQL deliberately]
Compat -->|No| Global{Needs atomic transactions across distributed key ranges}
Global -->|Yes| Spanner[Spanner]
Global -->|No| HTAP{Scan-heavy analytics harms OLTP}
HTAP -->|Yes| AlloyDB[Evaluate AlloyDB]
HTAP -->|No| Default[Start with Cloud SQL]
This is an elimination tree, not a product ranking. If hard requirements point to conflicting branches—for example, an unsupported PostgreSQL extension plus native distributed transactions—none of the services qualifies without application or requirement redesign.
Design Options
Start by stating the invariants that cannot be negotiated. Examples include atomic balance transfers across arbitrary accounts, PostGIS behavior required by an existing application, a read-after-write contract for a user session, or a residency rule that prevents data from leaving one geography. Then eliminate any option that cannot meet an invariant using a documented GA capability. Preview features can be evaluated, but they should not silently carry a production availability or support requirement.
The most important option may be no database change. If profiling shows that a few queries, unbounded pools, lock contention, or missing indexes consume the primary, moving the same workload preserves the defect and adds migration risk. Establish a tuned Cloud SQL baseline before attributing the limit to architecture.
The second no-fit case is separating analytics instead of choosing an HTAP database. If reports tolerate pipeline delay and require large historical scans, exporting changes to BigQuery can provide a clearer resource and cost boundary than keeping analytics beside transactions. AlloyDB’s columnar engine is a fit when freshness and operational simplicity justify co-location and representative queries show the benefit; it is not an obligation to keep every analytical workload in the transactional system.
The third no-fit case is choosing a non-PostgreSQL interface intentionally. Spanner’s PostgreSQL interface helps supported clients and SQL, but teams that need Spanner-specific capabilities may prefer native client libraries rather than forcing an ORM through PGAdapter. Conversely, an application dependent on unsupported extensions, user-defined stored procedures, or PostgreSQL administrative semantics is not a Spanner candidate merely because simple queries compile.
Use explicit admission criteria:
| Option | Evidence that admits it | Evidence that eliminates it |
|---|---|---|
| Cloud SQL | Tuned single primary meets forecast with failure headroom; required PostgreSQL surface is supported | Measured write path exceeds the largest acceptable configuration or distribution is a hard requirement |
| Sharded Cloud SQL | Stable shard key; bounded cross-shard operations; team can operate routing and rebalancing | Arbitrary cross-shard atomic transactions or globally ordered queries are business invariants |
| AlloyDB | PostgreSQL compatibility audit passes; read pools or columnar engine improve the measured workload | Required extension is unsupported, or the primary write path remains the limiting resource |
| Spanner | Distributed transaction boundary and key model are demonstrated; retry semantics are built into the application | PostgreSQL engine behavior is required, or distribution adds no material correctness or scale value |
Every admission decision needs a falsifiable statement. “Choose AlloyDB because analytics are slow” becomes “the identified scan-heavy queries reduce checkout p99 through cache eviction on Cloud SQL, and the same production trace meets both checkout and reporting objectives on the tested AlloyDB configuration.” “Choose Spanner for scale” becomes “the transaction must atomically update accounts whose owners cannot share one shard, and the proposed keys distribute the measured write workload without a hot range.”
Cloud SQL: managed PostgreSQL as the default
Choose Cloud SQL when one writable PostgreSQL instance meets measured peak demand with failure headroom, and PostgreSQL extensions, tooling, or behavior are important. Regional HA, read replicas, backups, and PITR are separate features that must be configured and tested. Do not call compatibility “full”: Cloud SQL restricts host access, supported extensions, flags, and superuser capabilities.
AlloyDB: PostgreSQL-compatible disaggregation and HTAP tools
Evaluate AlloyDB when a PostgreSQL-compatible application is constrained by a workload its architecture addresses: scan-heavy analytics competing with transactions, read scaling through read pools, or a need to test its columnar engine. AlloyDB still has one writable primary endpoint. Its storage and reads can scale independently, but that is not horizontal write compute.
Migration is not automatically lift-and-shift. Audit extensions, flags, logical replication, authentication, and performance-sensitive SQL. Benchmark the columnar engine with representative plans instead of applying Google’s “up to” result as an estimate.
Spanner: distributed transactions and horizontal scale
Choose Spanner when the workload needs atomic transactions and strong consistency across key ranges or locations that cannot be kept on one PostgreSQL writer, and the team accepts a Spanner redesign. The PostgreSQL interface supports a subset of PostgreSQL. PGAdapter translates the wire protocol; it does not provide PostgreSQL storage internals, extensions, triggers, or administration.
Spanner offers configurations with strong availability and consistency properties, but “zero RPO” must be attached to a documented instance configuration and failure model—not asserted for every disaster scenario. Key design, transaction contention, retry behavior, and data placement remain application architecture.
Manual sharding: preserve PostgreSQL, own distribution
Shard Cloud SQL when data has a durable partition key—often tenant or geography—cross-shard transactions are rare or explicitly handled, and PostgreSQL behavior is non-negotiable. The cost is a routing layer, shard catalog, rebalancing, per-shard schema rollout, fleet-wide backup and recovery, hotspot management, and a correctness design for cross-shard workflows.
Manual sharding is not “Spanner for free.” It is a decision to own the coordination Spanner provides.
In Practice
Build a one-page workload contract before selecting a product:
| Requirement | Evidence required |
|---|---|
| Write demand | Transaction mix, row and byte size, key distribution, peak duration, contention, and two-year forecast |
| Consistency | Exact read-after-write and cross-entity atomicity requirements |
| PostgreSQL surface | Extensions, types, triggers, stored procedures, drivers, ORMs, and admin tools |
| Analytics | Query plans, scan volume, concurrency, freshness target, and acceptable OLTP impact |
| Resilience | Failure model, RPO, RTO, endpoint behavior, and restore-drill results |
| Geography | User-to-region latency, write placement, residency, and failover locations |
| Operations | On-call skill, migration effort, feature release stage, and exit path |
| Cost | Regional prices, normal load, burst, replicas, backups, network, licenses, and staff time |
Then run the smallest discriminating experiments. For Cloud SQL, saturate the likely primary and test failover. For AlloyDB, test the same SQL with read pools and columnar execution. For Spanner, test the proposed primary keys and contended transactions through the intended driver. For sharding, implement one cross-shard workflow and one shard split before declaring the model simple.
The CARL evidence in this article is the documented behavior of the named systems rather than a fabricated company result:
- Context: The workload requires a relational API but its compatibility, distribution, and recovery requirements are not yet reconciled.
- Action: Eliminate candidates with hard requirements, then run identical workload and failure tests against the remaining designs.
- Result: The architecture decision is supported by observed limits, recovery behavior, and documented service boundaries rather than a universal TPS claim.
- Learning: Database selection is the placement of coordination and operational ownership. Product features matter only after those boundaries are explicit.
Migration and rollback are part of selection
For Cloud SQL or AlloyDB, inventory extensions, flags, roles, collations, logical replication requirements, large objects, and SQL whose plan matters. Database Migration Service or logical replication can reduce cutover time, but the design still needs a write-fencing point, replication-lag gate, validation queries, endpoint change, and rollback authority. A rollback after target writes begin is not simply “point DNS back”; it requires a reverse data path or an explicit acceptance that writes during the target window will be reconciled.
For Spanner, separate syntax conversion from semantic conversion. Map every table and access pattern, then test unsupported types and functions, sequence behavior, transaction isolation assumptions, ORM-generated SQL, and operational tooling. Run backfill and change capture through an immutable mutation identity and monotonic domain version so replay cannot overwrite newer state. Cut over by bounded cohorts and retain the source until rollback has been rehearsed with target-originated writes.
For manual sharding, migration is a repartitioning project. The migration record needs the shard-key function, ownership metadata, dual-routing behavior, resharding state machine, and handling of requests that arrive while a tenant moves. A shard catalog must be strongly controlled and auditable; stale routing is a correctness defect, not merely a cache miss.
Benchmark protocol
Use the same production-shaped trace and encoded data for every candidate. Record engine version, region, instance or processing capacity, storage configuration, HA topology, reader count, client version, pool size, schema, indexes, key distribution, transaction size, consistency settings, test duration, and warm-up state.
Run at least five phases:
- steady load with normal read and write mix;
- the expected peak for its full duration, not a short throughput burst;
- a skewed-key and contended-transaction phase;
- one documented failure-domain event with client reconnect or retry behavior;
- backlog replay while current traffic continues.
Report accepted business operations separately from attempts and retries. Capture p50, p95, and p99 latency; error and abort classes; CPU and memory; storage latency; lock or transaction contention; replica or change-stream lag; queue age; and time to recover steady state. For AlloyDB, record row versus columnar plans and reader routing. For Spanner, record split distribution, transaction aborts, and latency by transaction shape. For sharding, record router errors and per-shard imbalance.
Cost model inputs
Do not publish a product ordering without a dated, regional model. Include compute or processing capacity, storage, backup and retained logs, replicas, cross-region resources, network transfer, observability, support, migration overlap, failure headroom, and the durable ingest or change-capture system. For sharding, include the routing control plane, fleet-wide schema operations, rebalancing, and on-call work. For Spanner, model the selected instance configuration rather than treating “global” as one price or topology.
Use sensitivity ranges for growth, utilization, data retention, and regional traffic. Vendor calculators are inputs, not evidence that the architecture meets its SLO. Price the configuration that passed the failure test, including spare capacity, rather than a smaller steady-state diagram.
Security, tenancy, and disaster recovery
The products also create different isolation boundaries. One database per tenant can offer a strong operational boundary but increases fleet work. Shared tables reduce fleet count but require row-level authorization, tenant-aware observability, noisy-neighbor controls, and deletion verification. Sharding by tenant can improve blast-radius isolation while making tenant moves and fleet-wide queries explicit workflows. Spanner distribution does not automatically create tenant isolation; keys, IAM, database boundaries, and application authorization still do that work.
Validate identity and network paths with the real application. Cloud SQL connectors, AlloyDB connectors, and PGAdapter have different deployment and authentication roles. Audit database roles, IAM bindings, secret rotation, encryption-key ownership, private connectivity, and break-glass access. Confirm that observability and backups do not cross residency boundaries or expose tenant identifiers.
DR must name the failure. Cloud SQL regional HA covers zonal failure; cross-region replicas or restored instances cover different regional scenarios and can require promotion and endpoint changes. AlloyDB HA and cross-region secondary clusters have their own switchover, failover, and data-loss semantics. Spanner’s regional, dual-region, and multi-region configurations place replicas differently and expose different availability tradeoffs. Select the configuration from the RPO, RTO, write-latency, and residency requirements, then run the supported failure and restore procedures. A product family name is not a DR plan.
Tradeoff Matrix
| Dimension | Cloud SQL | Sharded Cloud SQL | AlloyDB | Spanner PostgreSQL interface |
|---|---|---|---|---|
| Writable topology | One primary | One primary per shard | One primary per cluster | Distributed by key range |
| PostgreSQL compatibility | High, service-restricted | High per shard | High, service-specific | PostgreSQL dialect subset |
| Native cross-partition transaction | Single database only | No | Single cluster only | Yes |
| Analytical acceleration | PostgreSQL plus edition features | Per-shard PostgreSQL | Optional columnar engine | Distributed query engine, different SQL surface |
| Distribution ownership | Google manages instance HA | Team owns routing and rebalancing | Google manages storage and HA | Google manages distributed storage and consensus |
| Migration shape | Managed-service migration | Application repartitioning | Compatibility and performance validation | Schema and transaction redesign |
Failure Modes
The architecture review should pre-register the failure that would falsify each choice.
Cloud SQL falsification: after query and pool tuning, the primary has insufficient CPU, memory, storage, lock, or connection headroom during the documented peak or failover recovery. Adding asynchronous replicas does not fix a write constraint, and regional HA does not create another readable writer.
AlloyDB falsification: the tested analytical queries are ineligible for columnar execution, the working set cannot remain resident, read traffic cannot be separated safely, or the single writable primary remains saturated. Storage disaggregation cannot repair a hot logical row or serialize less application contention.
Spanner falsification: the proposed leading keys concentrate traffic, distributed transactions touch more splits than expected, abort retries amplify external side effects, or unsupported PostgreSQL behavior forces an unbounded rewrite. More processing capacity cannot fix a schema that sends new writes to one key range.
Sharding falsification: a supposedly rare cross-shard invariant becomes common, shard ownership cannot be changed without an outage, or operational automation cannot maintain schema, backup, and recovery consistency across the fleet. The first cross-shard financial correction implemented as two independent writes is evidence that the boundary is wrong.
| Failure boundary | Detection evidence | Reversible response |
|---|---|---|
| Cloud SQL connection loss | Pool errors, reconnect rate, transaction outcomes | Bound retries, recycle connections, and test manual failover before resizing |
| AlloyDB primary pressure | Primary CPU, query plans, reader utilization | Route eligible reads, remove expensive SQL, or reject the choice if writes dominate |
| Spanner hot range or contention | Key Visualizer, transaction and lock statistics, abort rate | Redesign keys or transaction scope and replay an identical workload |
| Shard routing divergence | Catalog version mismatch, wrong-owner responses, parity checks | Freeze moves, restore one authoritative catalog version, and reconcile mutations |
| Regional recovery misses objective | Promotion or restore timeline and last durable transaction | Revisit topology, traffic fencing, and the stated RPO or RTO |
Where It Breaks
| Wrong choice | Production symptom | Corrective path |
|---|---|---|
| Cloud SQL past one-writer headroom | CPU, locks, or write latency saturate | Tune first; then shard or evaluate Spanner from transaction requirements. |
| AlloyDB selected from benchmark marketing | Cost rises while queries remain primary-bound or row-based | Verify plans, read routing, and column-store eligibility. |
| Spanner selected for ordinary CRUD | Migration complexity without distributed benefit | Return to managed PostgreSQL unless distributed transactions are real. |
| Sharding selected before transaction analysis | Cross-shard correctness moves into application code | Redesign boundaries or use a database with native distributed transactions. |
| Any service selected from list price | Network, HA, backup, and engineering costs dominate | Model a tested architecture in the target region and update prices at decision time. |
What to Do Next
- Problem: Product selection is based on compatibility labels and invented throughput thresholds.
- Solution: Eliminate options using hard requirements, then benchmark the remaining architectures with the same workload contract.
- Proof: Official documentation describes different PostgreSQL surfaces, writable topologies, replication models, and transaction systems.
- Action: Require an architecture decision record containing the workload contract, eliminated options, benchmark artifacts, recovery tests, feature stages, and exit plan.