# Storing Relational Data with Cloud SQL and Spanner

> Chapter 9 of the Courseiva GOOGLE-PDE curriculum — https://courseiva.com/learn/google-pde/storing-relational-data-with-cloud-sql-and-spanner

**Official objective:** 3.1 — Implement relational databases (Cloud SQL, Cloud Spanner) for transactional workloads, including high availability and scaling.

## Introduction

Relational databases: the bedrock of nearly every online transaction you make, from buying a coffee to checking your bank balance. Understanding how to store relational data reliably and at scale is a core skill for a data engineer, because the Google Professional Data Engineer (PDE) exam will test you on making these systems work — not just in theory, but in the real-world cloud of Google Cloud Platform (GCP).

## The Architect and the Department Store Analogy

The architect is the one who designs the building and decides where all the rooms go. Imagine you are the architect hired to design a huge department store. The store sells everything: clothes, electronics, groceries, and furniture. The store owner wants to keep track of every single item in every aisle. 

You design a system where every item has its own unique barcode, a category (like 'electronics'), a price, and a shelf location. This is like designing a relational database. The store's inventory system is your database. It uses a structured layout, like tables, where each table holds a specific type of data (e.g., a 'Products' table, a 'Customers' table, an 'Orders' table). Spanner is like designing the same department store but across multiple cities, all connected by a network of tunnels so that the inventory in New York, London, and Tokyo is always exactly the same, and a customer in Tokyo can buy an item that is physically in London, knowing it's available, without any errors. Cloud SQL is like managing a single, perfect store in one location, where you can easily scale up the size of the stockroom and add more checkout counters as the store gets busier. The architect's job is to ensure the structure is solid (data integrity), the aisles are fast to navigate (query speed), and the checkout lines never have a mistake (transaction consistency).

## Core explanation

Relational databases are systems that store data in a structured way, like a set of organised tables. Imagine a spreadsheet with rows and columns, but much more powerful. Each table represents a specific kind of thing (or 'entity'), such as 'Customers', 'Products', or 'Orders'. Each column represents a property of that thing, like a customer's name or a product's price. Each row is a single record, like one specific customer. 

The 'relational' part comes from the ability to link tables together using keys. For example, an 'Orders' table might have a column called 'Customer ID' which matches the 'Customer ID' column in the 'Customers' table. This link lets you ask complex questions like 'What is the name of the customer who bought the most expensive product last week?' without having to duplicate all the customer information in every order row. This is called a relational database management system (RDBMS). 

Google Cloud offers two main services for this: Cloud SQL and Cloud Spanner. Cloud SQL is a fully managed relational database service. It supports common database engines like MySQL, PostgreSQL, and SQL Server. 'Fully managed' means Google handles all the tedious maintenance tasks for you, such as applying security patches, managing backups, and ensuring the database is running. It is ideal for traditional web applications, content management systems (like WordPress), and smaller to medium-sized workloads. It operates within a single region (a specific geographic location, like London or Tokyo) but can be set up for high availability across multiple zones within that region (zones are distinct data centres within a region, separated by enough distance to survive a power outage or natural disaster in one zone). 

High availability (HA) in Cloud SQL works by creating a 'standby' instance in a different zone. The primary instance handles all the read and write operations. If the primary instance goes down (e.g., due to a hardware failure), Cloud SQL automatically flips traffic to the standby instance, typically within a minute. This is called a failover. You can also use read replicas to offload read-only queries (like generating reports) from the primary instance, which improves performance for the main application. 

Cloud Spanner is a different beast entirely. It is a globally distributed, horizontally scalable relational database service. 'Horizontally scalable' means you can add more machines (nodes) to handle more data and more requests, rather than just making a single machine more powerful (vertical scaling). Spanner combines the benefits of a traditional relational database (like strong consistency and SQL queries) with the scalability of a NoSQL system (like Bigtable). This is incredibly hard to achieve, and it is Spanner's superpower. 

Spanner uses a feature called 'TrueTime' — which is a globally synchronised clock — to provide external consistency (the strongest form of consistency in database systems). This means that if you write a record in Asia, and someone immediately reads that same record in America, they will see the absolutely most recent version of that data, even if the data is physically stored in a location far away. Spanner is designed for mission-critical, global-scale applications, such as large financial systems, inventory management for a global e-commerce platform, or any system where you absolutely cannot lose money because of data being out of sync. 

So, the core difference: Cloud SQL is a great, family-run corner shop that is perfectly reliable for your local needs. Cloud Spanner is a global super-warehouse chain that operates the same across every store in the world, at any second, no matter what. Your choice depends on your application's scale and consistency needs.

## Real-world context

Let us say you are a data engineer for a start-up called 'BookWorm', a global online bookstore. Initially, the app has a few thousand users. You choose Cloud SQL (using PostgreSQL) because it is simple, cheap, and meets your needs. You create tables for 'users', 'books', and 'orders'. A customer buys a book. The application sends an SQL command (a 'transaction') to Cloud SQL that inserts a new row in the 'orders' table (linking the user ID and book ID) and decrements the stock count in the 'books' table. 

As BookWorm grows, you notice the database is slow during peak hours. You enable a 'read replica' in the same region. You configure your reporting tool to query the read replica (which has a copy of the data that is usually only a few milliseconds behind) instead of the main database. This solves the slowdown. You also enable 'high availability' by creating a failover instance in a different zone. The cost goes up, but you can now survive a zone-level outage. 

Now, imagine BookWorm explodes in popularity. You are now a global company with millions of users in 20 countries. Customers in Australia are complaining that checkout is slow because the database is in us-central1 (Iowa). You could add read replicas in other regions, but writes still have to go back to the primary. This is slow and risky. 

You migrate to Cloud Spanner. The migration process involves: 

- Converting your PostgreSQL schema to Spanner's schema (using a tool like 'Harbourbridge' or manual scripting). 
- Creating a Spanner instance with nodes distributed across multiple regions (e.g., us-central1, europe-west1, and australia-southeast1). 
- Loading a snapshot of your existing data into Spanner. 
- Pointing your application's code to the new Spanner endpoint. 

Now, a customer in Sydney places an order. The application routes the request to the Australian region. Spanner's TrueTime technology ensures that the stock check and the order insertion are consistent. A few seconds later, a customer in London views their order history. Spanner gives them the correct, up-to-date information. The database is now globally scalable and globally consistent, without any complicated 'sharding' (splitting data across multiple databases) code for your developers to write. 

As an IT professional, your daily tasks shift from 'managing MySQL configurations' to 'monitoring Spanner latency and CPU utilisation via Cloud Monitoring', 'tweaking index designs for performance', and 'planning node capacity for future growth'. You also write 'Data Definition Language (DDL)' statements to add new tables or columns as the product evolves, and you use 'Cloud IAM' roles to finely control who can read or write to specific tables.

## Exam focus

The PDE exam focuses on your ability to choose the right tool and understand its operational characteristics. They will not ask you to write complex SQL. They will test your understanding of trade-offs. 

Here are the exact concepts that appear frequently: 

- **High Availability vs. Disaster Recovery:** Cloud SQL has HA (zone-level failover). Cloud Spanner has built-in HA across zones and regions. Know the difference. The exam will ask: 'You need a database that can survive the failure of an entire region. Which do you pick?' Answer: Cloud Spanner (multi-region configuration). 

- **Scaling directions:** Know that Cloud SQL scales up (vertically, by increasing machine size) and out via read replicas (for reads only). Cloud Spanner scales out (horizontally, by adding nodes) for both reads and writes. A trap question will describe a workload with high write throughput and ask you to choose Cloud SQL with more read replicas — that is wrong because read replicas do not help with write capacity. 

- **Consistency models:** Cloud SQL provides 'strong consistency' within a single database. Cloud Spanner provides 'external consistency' (the strongest) even across continents. A trick question might say 'Cloud Spanner provides eventual consistency across regions' — that is false. Spanner is always strongly consistent. 

- **Schema changes:** Cloud SQL lets you do schema changes easily. Cloud Spanner requires you to use the 'DDL' batch job workflow, which is more restrictive (e.g., you cannot drop a column that has secondary indexes). The exam will test that you know Spanner schema updates are not instantaneous and require a 'CREATE TABLE' or 'ALTER TABLE' statement that applies asynchronously. 

- **Pricing model:** Cloud SQL charges by instance type (vCPU, memory) and storage. Cloud Spanner charges by node-hour and storage, with a minimum of 1 node per instance (which is expensive, around $900/month). The exam scenario might give you a low-budget requirement, leading you to Cloud SQL. 

- **Read activereplicas in Cloud SQL:** They are for read-only workloads. They are not for writes. They are not kept in sync synchronously (they are asynchronous, meaning there is a small delay, usually less than a second). You must know this for questions about data freshness and reporting. 

- **The 'TrueTime' concept:** You need to know it exists and what it does (global clock sync for external consistency). They might ask: 'Which technology allows Spanner to provide global strong consistency?' The answer is 'Google's TrueTime API'. 

- **Sharding vs. Spanner:** The exam will contrast manual sharding (splitting a database yourself, which is hard and error-prone) with using Spanner (which does automatic sharding for you). They love this comparison. 

- **Migration scenarios:** You might be asked to migrate from Cloud SQL to Spanner. The correct approach is to use a 'Dataflow' pipeline or a 'Harbourbridge' tool, not a simple export/import, because you need to handle ongoing changes. 

- **The 'Interleave' feature in Spanner:** This is a way to physically store related rows together (like an order and its line items) so reads are faster. The exam might test this as an optimisation technique specific to Spanner. 

Traps to avoid: 
- Assuming 'Cloud SQL' is always the cheapest — for very small workloads, yes. But for any serious global scale, Spanner might be more cost-effective because you avoid expensive application-level workarounds. 
- Recommending Cloud SQL when the question mentions 'global users and strong consistency' — that is a Spanner scenario, always. 
- Forgetting that Cloud SQL's HA failover is not instantaneous (takes 1-5 minutes), and it may briefly lose connections (connections are dropped during failover). The application must be designed to retry. 

Key definitions to memorise: 
- **Transaction:** A logical unit of work that must succeed or fail as a whole (ACID). 
- **ACID:** Atomicity, Consistency, Isolation, Durability. 
- **Vertical scaling:** Adding more power (CPU/RAM) to a single machine. 
- **Horizontal scaling:** Adding more machines. 
- **Strong consistency:** Every read sees the most recent write. 
- **Eventual consistency:** Reads may see stale data for a short time. 
- **Primary key:** A column (or set of columns) that uniquely identifies each row. 
- **Foreign key:** A column that links to a primary key in another table. 
- **Index:** A data structure that speeds up searches on a table.

## Step by step

1. **1. Assess your application's requirements** — Determine if your workload needs to be global, how much write throughput you expect, and your consistency tolerance. This decision points you to either Cloud SQL (regional) or Cloud Spanner (global).
2. **2. Create a Cloud SQL instance (or a Spanner instance)** — In the GCP Console, navigate to Cloud SQL (or Spanner) and click 'Create Instance'. For Cloud SQL, choose your database engine (e.g., PostgreSQL) and select a machine type and region. For Spanner, choose a configuration (regional or multi-region) and set the number of nodes.
3. **3. Design your database schema** — Define your tables, columns, primary keys, and indexes. In Spanner, you can use Interleaved tables to store parent-child rows together for faster reads. In Cloud SQL, foreign keys are enforced by the database engine.
4. **4. Set up High Availability (for Cloud SQL) or Global Distribution (for Spanner)** — For Cloud SQL, enable the 'High Availability' option during instance creation or later, which provisions a standby in a different zone. For Spanner, choose a multi-region configuration (e.g., 'nam3' for the US) to have nodes spread across multiple geographic areas.
5. **5. Connect your application and monitor performance** — Use Cloud SQL Auth Proxy or a JDBC/ODBC driver to connect your app. For Spanner, use the Google Cloud client library. Monitor latency and throughput via Cloud Monitoring. Scale Cloud SQL by changing the machine type; scale Spanner by adding more nodes.

## Comparisons

### Cloud SQL vs Cloud Spanner

**Cloud SQL:**
- Single-region by default, HA within the region
- Vertically scalable (add more CPU/RAM)
- Medium write throughput limit

**Cloud Spanner:**
- Multi-region globally distributed by design
- Horizontally scalable (add more nodes)
- High write throughput, scales out

### Cloud SQL (with Read Replicas) vs Cloud Spanner (with Nodes)

**Cloud SQL (with Read Replicas):**
- Replicas only for reads
- Replicas are asynchronous
- Manual management of replica count

**Cloud Spanner (with Nodes):**
- All nodes handle reads and writes
- All nodes are synchronously replicated
- Automatic management of data distribution

### Cloud SQL (HA Failover) vs Cloud Spanner (Built-in Resilience)

**Cloud SQL (HA Failover):**
- Failover takes minutes
- Affects a single zone
- Application must handle connection drops

**Cloud Spanner (Built-in Resilience):**
- Failover is sub-second and transparent
- Survives a full region outage
- No application code needed for failover

### Manual Sharding vs Cloud Spanner

**Manual Sharding:**
- Developers write complex sharding logic
- Rebalancing data is error-prone
- No strong consistency across shards

**Cloud Spanner:**
- Automatic sharding at storage layer
- Seamless rebalancing
- Strong consistency across all shards

## Diagram

_High-level decision flow: choose Cloud SQL for regional needs with HA and read replicas, or Cloud Spanner for global scale with strong consistency and automatic distribution._

```mermaid
flowchart TD
    A[User Application] --> B{Scale & Consistency Needs?}
    B -->|Regional, Medium Traffic| C[Cloud SQL Instance]
    B -->|Global, High Traffic, Strong Consistency| D[Cloud Spanner Instance]
    C --> E[Primary Zone]
    C --> F[Standby Zone (HA)]
    C --> G[Read Replica Zone]
    D --> H[Multi-Region Nodes]
    H --> I[TrueTime Global Clock]
```

## Common misconceptions

- **Misconception:** Cloud SQL is always the best choice for any relational database workload on GCP. **Reality:** Cloud SQL is excellent for single-region, moderate-scale workloads. For global scale, high write throughput, or extreme consistency requirements, Cloud Spanner is the correct choice. (Beginners often assume 'simpler equals better', not realising that scaling a single-region database globally involves painful manual sharding and application rewrites.)
- **Misconception:** Cloud Spanner is just a bigger version of Cloud SQL. **Reality:** Cloud Spanner is architecturally different: it uses distributed computing and a global clock (TrueTime) to provide strong consistency across regions, which Cloud SQL cannot do. (Both are relational databases, so the surface-level similarity masks the deep technical differences in their internal architecture.)
- **Misconception:** Adding a read replica to Cloud SQL increases write performance. **Reality:** Read replicas only handle read queries. Write queries still go to the primary instance, so write performance is unaffected by the number of replicas. (People hear 'more replicas = more capacity' and assume it applies to all operations, not realising replicas are for read scaling only.)
- **Misconception:** Cloud Spanner provides eventual consistency across regions because it is distributed. **Reality:** Cloud Spanner provides external consistency (the strongest form of strong consistency) globally, using TrueTime. This is its defining feature. (A common mental shortcut is that 'distributed' databases must be eventually consistent, but Spanner deliberately defies this pattern.)

## Key takeaways

- Cloud SQL is a fully managed relational database for single-region, moderate-scale applications, supporting MySQL, PostgreSQL, and SQL Server.
- Cloud Spanner is a globally distributed, horizontally scalable relational database that provides external consistency across regions using TrueTime.
- High Availability in Cloud SQL uses a standby instance in a different zone within the same region, providing automatic failover.
- Cloud Spanner automatically handles sharding at the storage layer, so you do not need to write any application code to distribute data.
- Read replicas in Cloud SQL improve query performance for read-heavy workloads but do not improve write performance.
- The primary difference between Cloud SQL and Spanner for the exam is scale and consistency: use Cloud SQL for regional, low-to-medium traffic, and Spanner for global, high-traffic, strongly-consistent requirements.

## FAQ

**What is the main difference between Cloud SQL and Cloud Spanner?**

Cloud SQL is a single-region, vertically scalable relational database good for moderate workloads. Cloud Spanner is a globally distributed, horizontally scalable database that provides strong consistency across regions.

**Can I use Cloud SQL for a global e-commerce app?**

You can, but you will struggle with latency for distant users and it will require manual read replicas per region and complex application logic for writes. Cloud Spanner is designed for that scenario.

**How do I migrate from Cloud SQL to Spanner?**

Use Google's Harbourbridge tool to convert your schema and migrate data, or use Dataflow for an ongoing live migration. You cannot simply export and import a SQL dump.

**Does Cloud Spanner support foreign keys?**

Yes, Cloud Spanner supports foreign keys, allowing you to enforce referential integrity between tables, similar to Cloud SQL.

**What happens if a Cloud SQL instance with HA fails?**

Cloud SQL performs an automatic failover to a standby instance in a different zone, which takes a few minutes. Your application must reconnect and queries may be briefly rejected.

**How does Spanner achieve global consistency?**

Spanner uses Google's TrueTime API, a globally synchronised clock, to ensure that any read sees the most recent write, regardless of geographic location.

## Check your understanding

1. **Which Google Cloud service would you recommend for a global financial trading platform that requires strong consistency across three continents?**

   Answer: Cloud Spanner. It provides external consistency globally thanks to TrueTime, which is essential for financial applications.

2. **What is the purpose of a read replica in Cloud SQL?**

   Answer: To offload read-only queries from the primary instance, improving performance for read-heavy applications, but it does not increase write capacity.

3. **What does 'horizontal scaling' mean in the context of Cloud Spanner?**

   Answer: It means you can add more nodes to the Spanner instance, which increases both read and write throughput and storage capacity.

4. **True or False: Cloud Spanner provides eventual consistency across regions.**

   Answer: False. Cloud Spanner provides external consistency (the strongest form of strong consistency) globally, not eventual consistency.

5. **What is the minimum number of nodes required for a Cloud Spanner instance?**

   Answer: A Cloud Spanner instance requires a minimum of 1 node.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/google-pde/storing-relational-data-with-cloud-sql-and-spanner