Study guide
5.2 Relational Databases: Cloud SQL, AlloyDB & Cloud Spanner
Google Professional Cloud Architect Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview, Strategy & Case Study Methodology
1.1 Exam Overview, Structure & Scoring Logistics 1.2 The Professional Cloud Architect Role & Exam Mindset 1.3 Deconstructing GCP Case Studies & Scenario Questions
2 Chapter 2: Designing for Business Requirements
2.1 Mapping Business Objectives, ROI & Total Cost of Ownership 2.2 Cost Optimization Strategies, Pricing Models & Trade-Offs 2.3 Business Continuity, Governance & Compliance Planning
3 Chapter 3: Designing for Technical Requirements & Availability
3.1 Scalability, Elasticity & Capacity Planning 3.2 High Availability, Disaster Recovery Patterns & RTO/RPO 3.3 Performance Optimization & Global Latency Reduction
4 Chapter 4: Compute Selection & Workload Architecture
4.1 Compute Engine Architecture, Sizing & Managed Instance Groups 4.2 Google Kubernetes Engine (GKE) Standard vs Autopilot & Microservices 4.3 Serverless Architectures: Cloud Run, Cloud Functions & Eventarc
5 Chapter 5: Storage, Database & Data Warehouse Architecture
5.1 Cloud Storage Architecture, Storage Classes & Lifecycle Management 5.2 Relational Databases: Cloud SQL, AlloyDB & Cloud Spanner 5.3 NoSQL & Big Data Storage: Firestore, Bigtable & BigQuery
6 Chapter 6: Networking, Hybrid Connectivity & Traffic Routing
6.1 VPC Design, Subnets, Shared VPC & VPC Peering 6.2 Hybrid & Multicloud Connectivity: Cloud VPN & Cloud Interconnect 6.3 Cloud Load Balancing, Cloud CDN & Cloud DNS
7 Chapter 7: Identity, Access & Resource Governance
7.1 Cloud IAM Hierarchy, Custom Roles & Service Accounts 7.2 Workload Identity Federation, Service Account Impersonation & IAP 7.3 Organization Policies, Tags & Resource Governance
8 Chapter 8: Security Architecture, Data Protection & Compliance
8.1 Data Encryption at Rest/Transit, Cloud KMS, CMEK & Cloud HSM 8.2 Network Security: Cloud Armor, VPC Service Controls & Firewalls 8.3 Compliance Frameworks, Sensitive Data Protection & Security Command Center
9 Chapter 9: DevOps, Automation & Infrastructure as Code
9.1 Infrastructure as Code (IaC) with Terraform on Google Cloud 9.2 CI/CD Pipelines: Cloud Build, Artifact Registry & Cloud Deploy 9.3 Deployment Strategies (Blue/Green, Canary) & GitOps with Config Sync 9.4 Interacting with Google Cloud Programmatically: Cloud SDK, Cloud Shell & Emulators
10 Chapter 10: Site Reliability Engineering & Operations Excellence
10.1 SRE Fundamentals: SLIs, SLOs, SLAs & Error Budgets 10.2 Observability: Cloud Monitoring, Logging & Alert Policies 10.3 Diagnosing Performance: Cloud Trace, Profiler & Error Reporting
11 Chapter 11: Data Pipelines, Integration & Modernization
11.1 Event-Driven Architectures with Cloud Pub/Sub & Eventarc 11.2 Stream and Batch Processing: Dataflow & Dataproc 11.3 API Management & Service Integration: Apigee & Cloud API Gateway 11.4 Migration Planning & Execution: Migration Center, DMS, Datastream & Transfer Options
12 Chapter 12: Well-Architected Framework & Official Case Study Solutions
12.1 Google Cloud Well-Architected Framework & FinOps Governance 12.2 Comprehensive Case Studies: EHR Healthcare & Cymbal Retail 12.3 Comprehensive Case Studies: Altostrat Media & KnightMotives Automotive
13 Chapter 13: AI, Machine Learning & Generative AI Architecture
13.1 AI/ML Platform Architecture: Vertex AI, Gemini Enterprise Agent Platform & AI Hypercomputer 13.2 Prebuilt AI APIs, Model Garden, Gemini Cloud Assist & Securing AI
5.2 Relational Databases: Cloud SQL, AlloyDB & Cloud Spanner
Key Takeaways Explain This Topic AI
Cloud SQL provides managed MySQL, PostgreSQL, and SQL Server with zero-data-loss High Availability (HA) across zones via synchronous Regional Persistent Disk replication.
Cloud SQL read scalability and disaster recovery are achieved through cross-zone and cross-region Read Replicas, Point-In-Time Recovery (PITR) via write-ahead log (WAL) archiving, and customizable maintenance windows.
AlloyDB for PostgreSQL delivers an enterprise-grade PostgreSQL-compatible database featuring decoupled compute and storage, an automated in-memory columnar engine, and up to 4x OLTP / 100x analytical throughput over standard PostgreSQL.
Cloud Spanner provides a globally distributed, horizontally scalable relational database offering external consistency (true ACID) powered by Google's TrueTime API (atomic clocks and GPS receivers).
Cloud Spanner schema design strictly prohibits monotonically increasing primary keys (such as sequential IDs or raw timestamps) to prevent write hotspotting on splits, requiring UUIDv4, hash sharding, or bit-reversal.
Last updated: August 2026
Relational Databases: Cloud SQL, AlloyDB & Cloud Spanner
Architectural Selection Principle: Selecting a relational database on Google Cloud requires balancing scalability, SQL dialect compatibility, transaction throughput, and operational availability. Google Cloud provides three primary tiers: Cloud SQL for standard regional single-node databases, AlloyDB for high-performance enterprise PostgreSQL workloads, and Cloud Spanner for unlimited horizontal scale with global ACID consistency.
Cloud SQL: Architecture, High Availability & Disaster Recovery
Cloud SQL is a fully managed relational database service supporting MySQL , PostgreSQL , and Microsoft SQL Server . It eliminates administrative overhead for provisioning, patching, backup management, and storage capacity scaling. +-----------------------------------------------------------------------------------+
- | CLOUD SQL HIGH AVAILABILITY (HA) |
- +-----------------------------------------------------------------------------------+
- | ZONE A (Primary) ZONE B (Standby) |
- | +-----------------------+ +-----------------------+ |
- | | Primary DB Instance | | Standby DB Instance | |
- | +-----------+-----------+ +-----------+-----------+ |
- | | | |
- | v v |
- | +-----------------------------------------------------------------+ |
- | | Synchronous Regional Persistent Disk (Block-Level) | |
- | +-----------------------------------------------------------------+ |
- | Automatic Regional Failover: ~60-120 seconds | Zero Data Loss (RPO = 0) |
- +-----------------------------------------------------------------------------------+
- High Availability (HA) Mechanics
When HA is enabled, Cloud SQL provisions a primary instance in a primary zone (e.g., us-central1-a ) and a standby instance in a secondary zone (e.g., us-central1-b ):
Regional Persistent Disk: Data is synchronously replicated at the block storage layer across both zones before write transactions are committed. This guarantees an RPO of 0 (zero data loss).
Automatic Failover: If the primary instance fails its internal health checks (due to hardware failure, kernel crash, or zonal outage), Cloud SQL automatically redirects the static IP endpoint to the standby instance within 60 to 120 seconds .
Application Resilience: Client applications experience a momentary drop in database connections during failover and must implement standard exponential backoff retry logic.
Read Replicas & Scalability Topologies
Cross-Zone Read Replicas: Scale out read-intensive queries within the same region. Replication from the primary instance is asynchronous .
Cross-Region Read Replicas: Provide low-latency local reads for globally distributed users and serve as a cross-region Disaster Recovery (DR) target. If a primary region suffers a total outage, an administrator can promote a cross-region read replica to an independent primary read-write database.
Backups, Point-In-Time Recovery (PITR) & Maintenance
Automated Backups & Transaction Logs: Cloud SQL performs daily full snapshots combined with continuous write-ahead log (WAL) archiving or binary log retention.
Point-In-Time Recovery (PITR): Enables recovery of data to any precise microsecond within the configured log retention window (up to 7 days for Cloud SQL).
Maintenance Windows & Deny Periods: Administrators specify weekly 1-hour maintenance windows and maintenance notification channels. For mission-critical retail events (e.g., Black Friday), architects can configure a maintenance deny period of up to 90 days to block all non-emergency updates.
AlloyDB for PostgreSQL: Modern Decoupled Architecture
AlloyDB for PostgreSQL is an enterprise-grade, fully managed PostgreSQL-compatible database designed for demanding transactional (OLTP) and analytical (HTAP/OLAP) enterprise workloads. +-----------------------------------------------------------------------------------+
- | ALLOYDB DISAGGREGATED ARCHITECTURE |
- +-----------------------------------------------------------------------------------+
- | COMPUTE LAYER | [ Primary Read/Write Instance ] [ Read Pool (Autoscaling)]|
- | | - Standard PostgreSQL Engine - Up to 20 Read Nodes |
- | | - In-Memory Columnar Engine - Analytic Query Routing |
- +--------------------+---------------------------------------------------------------+
- | INTERCONNECT | Ultra-fast Andromeda SDN + Remote Direct Memory Access (RDMA)|
- +--------------------+---------------------------------------------------------------+
- | LOG-ACCELERATED | Distributed Storage Engine (Colossus + Intelligent Log Store)|
- | STORAGE LAYER | - Decoupled compute and storage; dynamic sharded storage |
- | | - Storage processes WAL directly; offloads compute nodes |
- +-----------------------------------------------------------------------------------+
- Architectural Innovations
Decoupled Compute and Storage: AlloyDB separates the database processing tier from the underlying persistence layer. Compute instances can be resized, added, or removed in seconds without migrating underlying storage disks.
Log-Processing Storage Engine: Instead of the database engine writing dirty pages to disk, AlloyDB offloads Write-Ahead Log (WAL) processing directly to the intelligent storage nodes. This removes I/O write bottlenecks, yielding up to 4x transactional throughput compared to standard vanilla PostgreSQL.
Automated In-Memory Columnar Engine: AlloyDB dynamically identifies analytical query patterns and populates an in-memory columnar store alongside the traditional row-oriented buffer pool. This accelerates vector and analytical SQL operations by up to 100x without modifying application SQL queries.
Stateless Autoscaling Read Pools: Read pools contain multiple stateless read instances that scale horizontally based on CPU utilization, providing elastic read scalability.
Cloud Spanner: Globally Distributed Relational Architecture
Cloud Spanner is Google's flagship globally distributed, horizontally scalable relational database service. It breaks the traditional CAP theorem trade-off by delivering strict external consistency (true ACID) alongside horizontal scaling and 99.999% availability . +-----------------------------------------------------------------------------------+
- | CLOUD SPANNER TRUETIME & RAFT STACK |
- +-----------------------------------------------------------------------------------+
- | GLOBAL ACID SQL | ANSI 2011 SQL with transactions across tables/regions |
- +--------------------+---------------------------------------------------------------+
- | CONCURRENCY (ACID)| TrueTime API (Atomic Clocks + GPS) -> External Consistency |
- | | Commit-Wait protocol resolves order of global transactions |
- +--------------------+---------------------------------------------------------------+
- | REPLICATION (RAFT)| Synchronous Paxos/Raft consensus groups per split |
- +--------------------+---------------------------------------------------------------+
- | STORAGE (SPLITS) | Colossus Distributed File System |
- | | - Data partitioned into horizontal ~4GB Splits |
- +-----------------------------------------------------------------------------------+
- TrueTime API & External Consistency
Traditional distributed databases rely on NTP (Network Time Protocol), which has an unpredictable clock skew of hundreds of milliseconds, forcing systems to choose between eventual consistency or slow distributed locking. Cloud Spanner solves this via the TrueTime API :
Hardware Synchronization: TrueTime utilizes a synchronized network of atomic clocks and GPS receivers deployed across Google's global data centers.
Bounded Uncertainty (epsilon): TrueTime exposes time not as a static value, but as an interval [t.earliest, t.latest] with bounded uncertainty (epsilon approximately 1-7ms).
Commit-Wait Protocol: When committing a read-write transaction, Spanner deliberately waits out the uncertainty interval (2 * epsilon) before releasing locks. This guarantees that transaction commit timestamps strictly reflect real-world causal ordering across the globe, enabling lock-free, strongly consistent snapshot reads.
Regional vs. Multi-Region Topologies
Configuration Type Availability SLA Replication Topology Quorum Mechanics
Regional Instance 99.99% (4 nines) 3 Read-Write replicas across 3 zones in a single region. Paxos consensus across local zonal replicas.
Multi-Region Instance 99.999% (5 nines) Geo-distributed: 1 Read-Write Leader region, Witness nodes in a 3rd region, Read-Only replicas globally. Cross-region Paxos quorum; survives total loss of an entire region with zero downtime and RPO = 0 .
Compute Capacity: Processing Units vs. Nodes
Spanner scales compute linearly by adding capacity units:
Processing Units (PUs): For smaller production or development environments, Spanner scales in granular increments of 100 Processing Units (1 Node = 1,000 PUs). A 100-PU instance shares node infrastructure while capping storage at 410 GB.
Autoscaling Spanner: Automatically scales PUs or Nodes up and down based on high/low CPU utilization thresholds (recommended: <= 65% for regional, <= 45% for multi-region to maintain headroom for failover quorums).
Database Splits & Primary Key Anti-Patterns
Spanner partitions table data horizontally into ordered ranges called splits (typically ~4 GB each). Each split is managed by an independent Paxos group distributed across the cluster nodes. ANTI-PATTERN: Monotonically Increasing Key (Hotspotting)
- [ Primary Key: Timestamp / Auto-Increment ID ]
- Write 1 (10:00:01) ---> [ Split 1 (Node A) ] <--- 100% Write Load (HOTSPOT!)
- Write 2 (10:00:02) ---> [ Split 1 (Node A) ]
- Write 3 (10:00:03) ---> [ Split 1 (Node A) ] [ Split 2 (Node B) ] (IDLE: 0% Load)
- BEST PRACTICE: Hash-Distributed / UUIDv4 Primary Keys
- [ Primary Key: UUIDv4 / Hashed Shard Prefix ]
- Write 1 (a7f9...) ---> [ Split 1 (Node A) ] (33% Load)
- Write 2 (3c1d...) ---> [ Split 2 (Node B) ] (33% Load)
- Write 3 (e4b2...) ---> [ Split 3 (Node C) ] (33% Load)
Caution
The Monotonically Increasing Key Trap: In Cloud Spanner, using sequential integer IDs ( AUTO_INCREMENT ), sequential order numbers, or raw timestamps ( timestamp_now() ) as the first column of a primary key causes write hotspotting . Because keys are stored in lexicographical order, all sequential writes route to the exact same split on a single node, overwhelming that server while the rest of the multi-million-dollar cluster sits idle. Always use UUIDv4 , hash prefixes , or bit-reversed sequential IDs .
Table Interleaving
For parent-child relational hierarchies (e.g., Customers and Orders ), Spanner allows Table Interleaving . Child rows are physically co-located on the same storage splits as the parent row. This transforms cross-table joins into localized, single-split scans, drastically reducing distributed network roundtrips.
Relational Database Decision Framework
Evaluation Criteria Cloud SQL AlloyDB for PostgreSQL Cloud Spanner Bare Metal Solution (BMS)
Target Dialects MySQL, PostgreSQL, SQL Server PostgreSQL (100% compatible) ANSI 2011 SQL, PostgreSQL dialect Oracle, SAP HANA, IBM Db2
Max Storage Capacity 64 TB 128 TB+ Virtually unlimited (Petabytes) SAN storage limits
Scaling Model Vertical compute scaling; read replicas for reads. Vertical compute; autoscaling read pools. True horizontal read/write scale across nodes globally. Manual physical hardware additions.
High Availability SLA 99.95% (Regional HA) 99.99% (Regional HA) 99.99% (Regional), 99.999% (Multi-Region) Depends on clustering software (e.g., Oracle RAC).
ACID Scope Single-node instance Single-node primary Globally distributed external consistency Single-server / Shared-disk cluster
Ideal Architectural Fit Standard ERP/CRM, departmental apps, WordPress, legacy migration. High-throughput enterprise PostgreSQL, HTAP analytics, read-heavy OLTP. Global financial ledgers, international inventory, hyperscale gaming. Specialized legacy enterprise apps with certified hardware licenses (Oracle RAC).
- Concrete Architectural Scenario: Global FinTech Payment Processing
- Scenario Profile
Client: Global FinTech processing credit card authorizations across North America, Europe, and Asia.
Requirements: Strict ACID compliance; global strong consistency for account balances; multi-region active-active read/write with 99.999% availability; zero data loss during regional outages (RPO = 0, RTO < 5s); handling 150,000 write transactions per second. [ Global Users: NA / EU / Asia ] ---> [ Global Anycast External HTTPS Load Balancer ]
- |
- [ GKE Services in 3 Continents ]
- |
- +------------------+------------------+
- | |
- v v
- [ Cloud Spanner Multi-Region Instance (nam-eur-asia1) ]
- Primary Key: UUIDv4 (Uniform Shard Distribution)
- Table Schema: INTERLEAVE IN PARENT Accounts (Locality)
- 99.999% Availability SLA | TrueTime External Consistency
- Architecture Blueprint
Database Selection: Cloud Spanner Multi-Region Instance to satisfy the global 99.999% availability SLA and provide cross-continental ACID transaction consistency.
Schema Optimization: Primary keys utilize UUIDv4 strings to guarantee uniform hash distribution across database splits. The Transactions table is configured with INTERLEAVE IN PARENT Accounts to co-locate customer account records with their transaction histories.
Capacity Management: Sized with autoscaling enabled between 15 and 45 nodes, maintaining average CPU utilization below 45% to guarantee headroom for instantaneous regional Paxos leader failover.
Important
Exam Watch: If an exam question describes a relational database requirement demanding horizontal write scalability , global multi-region active-active writes , or a 99.999% availability SLA with strict ACID consistency , the answer is Cloud Spanner . If the question involves existing PostgreSQL applications suffering from heavy analytics contention or requiring up to 4x transactional speed without code refactoring, the answer is AlloyDB .