GCP
Intermediate 15 min read GCP

GCP Databases and BigQuery

From transactional SQL to serverless analytics

Cloud SQL, Spanner, Firestore, Bigtable and BigQuery are GCP's data tier. Relational OLTP to global SQL to serverless warehouse — each answers a different data question.

Cloud SQLSpannerFirestoreBigtableBigQuerydata warehouse
Prerequisites: GCP Service Catalog Managed Databases
Before we start

What you will learn

  • Choose Cloud SQL vs Spanner vs Firestore vs Bigtable
  • Explain BigQuery's serverless architecture
  • Design global consistency with Spanner where needed
In 60 seconds

Cloud SQL is managed Postgres/MySQL; Spanner is globally distributed SQL with strong consistency; Firestore is a document NoSQL with real-time; Bigtable is wide-column for large-scale ops data; BigQuery is a serverless SQL warehouse.

Introduction

What is this?

Google's data platforms: Cloud SQL (Postgres/MySQL/SQL Server managed, with read replicas and HA) for classic relational needs. Spanner: horizontally scaling, globally consistent SQL — one system that behaves like a database and scales like a NoSQL store. Firestore: serverless document DB with realtime listeners and atomic transactions. Bigtable: wide-column store for high-throughput time-series/ops data. BigQuery: serverless columnar data warehouse with SQL, storing data in columnar format with standard pay-per-query pricing.

Motivation

Why do you need it?

  • Cloud SQL de-risks the most common DB ops: backups, patches, HA.
  • Spanner solves the 'I need global reads/writes with ACID' problem you can't build by hand.
  • BigQuery gives warehouse capability with no servers and instant elasticity.
  • Choosing the right one is a top-3 database interview question.
Think about it

Mental model

Imagine

A workshop with a bench, a global highway and a library

Think of

Cloud SQL, Spanner, BigQuery

The bench (Cloud SQL) is where one table's careful work happens. Spanner is the highway — many lanes (nodes) but one set of traffic laws (across regions). The library (BigQuery) is where every past record is indexed and queryable on demand.

Mechanics

How it works

  • Cloud SQL runs managed Postgres/MySQL in your VPC with automated backups, replicas and HA (primary + standby).
  • Spanner splits rows across nodes/paxos groups and uses TrueTime to give strong consistency globally.
  • Firestore: document collections with indexes; scales automatically; billing per read/write/storage.
  • Bigtable: rows keyed by a row key, sorted within tablets partitioning; good for very high write throughput.
  • BigQuery: serverless multi-tenant columnar storage + Dremel engine; queries bill by data scanned.

Choosing a GCP data service.

Relational OLTP
Global strong consistency
Serverless document
Wide-column ops data
Analytics / warehouse
Real world

In production

  • A SaaS keeps user data in Cloud SQL with a read replica for reporting.
  • A global booking app uses Spanner for inventory consistency across regions.
  • A mobile gam3 stores player state in Firestore; a billing pipeline reads into BigQuery nightly for dashboards.
Guidelines

Best practices

  • Start relational with Cloud SQL; reach for Spanner only when single-region SQL can't serve scale or global consistency.
  • Give Firestore bounded queries and pay attention to index requirements.
  • Bigtable: design the row key first (entity+time ordering), it determines read patterns.
  • BigQuery: partition + cluster, use materialized views for hot metrics, and prefer public datasets to avoid cost surprises.
Watch out

Common mistakes

  • Choosing Spanner 'because it's cool' when Cloud SQL suffices; Spanner costs and complexity aren't free.
  • BigQuery full-table scans on every dashboard — no partition/cluster filtering.
  • Firestore reads without indexes → exceptions in prod.
  • Running operational queries against the primary Cloud SQL instance instead of a replica.
Root cause analysis

Common issues

Error
Cloud SQL refuses connections
Root cause

Private IP + authorization rule missing, or instance down for maintenance

Fix

Authorize the connecting IP/subnets or use the private IP with VPC peering configured.

Error
BigQuery query is slow/costs a lot
Root cause

No partition filter, scanning the whole table

Fix

Add partition/column filtering and favor clustered/partitioned tables.

Error
Firestore writes throttled
Root cause

Hotspotting on a monotonically increasing field

Fix

Distribute keys; use a hash/sharding prefix to spread writes.

When things break

Debugging tips

  • Cloud SQL slow: slow-query log + EXPLAIN before scaling the machine.
  • BigQuery cost: look at bytesProcessed per query in the query history UI.
  • Firestore errors: read the index errors; add the suggested composite indexes.
  • Spanner latency: examine key distribution (hotspotting) and transaction isolation.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/1
1

Which GCP service gives globally consistent SQL at scale?

Go deeper

Official documentation

GCP — Cloud SQL overview
docs
GCP — Spanner overview
docs
GCP — BigQuery overview
docs