5.2 Relational Databases: Cloud SQL, AlloyDB & Cloud Spanner

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):

1. 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).
2. 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.
3. 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

1. 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.
2. 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.
3. 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.
4. 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

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

---

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

1. Database Selection: Cloud Spanner Multi-Region Instance to satisfy the global 99.999% availability SLA and provide cross-continental ACID transaction consistency.
2. 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.
3. 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.