Cloud SQL vs Spanner vs Bigtable: When to Use Each

A data warehouse answers questions about the business. An operational database runs the business: it holds the order as it is placed, the account as it is debited, the reading as the sensor sends it. Google Cloud has several, and three cover most cases: Cloud SQL, Spanner and Bigtable. Data engineers meet them as the sources that pipelines read from and the stores that applications read results from. They are not three sizes of the same thing. Each is a different kind of database, and choosing the wrong kind is expensive to undo.

At a glance

Cloud SQL: the database you already know

Cloud SQL is a managed MySQL, PostgreSQL or SQL Server. You get the real engine, so existing applications, drivers and skills work unchanged. Google runs the machine: backups, patching, monitoring, and a standby in another zone if you ask for high availability (Google Cloud: Cloud SQL overview).

Underneath, an instance is one virtual machine with a disk. That is its strength, because it behaves exactly like the database you know, and its limit, because it grows by becoming a larger machine.

Use it by default. Most applications never outgrow a well-run relational database, and the instinct to start with something more exotic is usually wrong.

Spanner: relational, without the single machine

Spanner keeps what makes relational databases useful, tables, SQL and transactions, and removes the single machine. Capacity is added in units, and an instance can be regional, dual-region or multi-region (Google Cloud: Spanner instances).

Its distinguishing property is consistency at that scale. Spanner provides what Google calls external consistency: the system behaves as if every transaction ran one after another, even though they run on many servers and possibly in several data centres (Google Cloud: TrueTime and external consistency). An application does not have to reason about replicas disagreeing.

It asks something in return. Because data is spread by primary key, the key matters. A key that only ever increases, such as a sequence number or a timestamp, sends every insert to the same server and creates a hotspot (Google Cloud: Spanner schemas).

Use it when a relational system has outgrown one machine, or must stay consistent and available across regions: payment ledgers, inventory, global user accounts.

Bigtable: enormous, and read by key

Bigtable is not relational. It is one vast sorted table: billions of rows, each found by a single row key, with columns that can differ from row to row (Google Cloud: Bigtable overview). It is built for very high read and write rates at low latency.

What it leaves out is as important (Google Cloud: Bigtable schema design):

• No joins.
• Transactions within a single row only.
• One index, the row key. Rows are stored in key order, and the fast way to read is a range of neighbouring keys.

So you do not design a Bigtable schema from the data and then write queries. You decide the queries first and build the row key to serve them. Google's documentation states it as the key point of schema design.

The row key is the design

The script models that on real data: 77,033 New York taxi trips from one day (NYC TLC: trip record data). It builds the row key two ways, sorts the rows as Bigtable would, splits them into eight tablets, and asks two questions of each design. It is a model that sorts and counts, not Bigtable.

# A model of row key design in a sorted key-value store such as Bigtable, on one day of real trip
# data. Rows are kept in key order and split into eight equal tablets. It is not Bigtable: it only
# sorts keys and counts, to show which rows a scan must read and where new writes land.
from bisect import bisect_left, bisect_right
from collections import Counter

import pandas as pd

trips = pd.read_parquet("../../dedata/yellow_tripdata_2024-01.parquet", columns=["tpep_pickup_datetime", "PULocationID"])
day = trips[trips.tpep_pickup_datetime.dt.strftime("%Y-%m-%d") == "2024-01-15"]
events = [(row.tpep_pickup_datetime.strftime("%H:%M:%S"), f"{row.PULocationID:03d}", index)
          for index, row in enumerate(day.itertuples())]

DESIGNS = {
    "time#zone": lambda time, zone, n: f"{time}#{zone}#{n}",       # timestamp first
    "zone#time": lambda time, zone, n: f"{zone}#{time}#{n}",       # high-cardinality value first
}
TABLETS = 8

print(f"{len(events):,} rows; question 1: all trips from zone 132; question 2: all trips from 08:00 to 08:59\n")
print(f"{'row key':10} {'rows read, q1':>14} {'rows read, q2':>14} {'last hour of writes: share in busiest tablet':>46}")
for name, make_key in DESIGNS.items():
    keys = sorted(make_key(*event) for event in events)
    if name == "zone#time":
        q1 = bisect_left(keys, "132#~") - bisect_left(keys, "132#")       # one contiguous prefix scan
        q2 = len(keys)                                                    # the hour is scattered: full scan
    else:
        q1 = len(keys)                                                    # the zone is scattered: full scan
        q2 = bisect_left(keys, "09:") - bisect_left(keys, "08:")          # one contiguous range scan
    boundaries = [keys[len(keys) * i // TABLETS] for i in range(1, TABLETS)]
    late = [make_key(*event) for event in events if event[0] >= "23:00:00"]
    tablet_of = Counter(bisect_right(boundaries, key) for key in late)
    print(f"{name:10} {q1:14,} {q2:14,} {max(tablet_of.values()) / len(late):45.0%}")

77,033 rows; question 1: all trips from zone 132; question 2: all trips from 08:00 to 08:59

row key     rows read, q1  rows read, q2   last hour of writes: share in busiest tablet
time#zone          77,033          2,440                                          100%
zone#time           5,651         77,033                                           35%

• A key that starts with the zone answers "all trips from zone 132" by reading 5,651 neighbouring rows. The same question against the time-first key must read all 77,033, because that zone's rows are scattered through the day.
• The time-first key answers "all trips in one hour" with 2,440 rows. The zone-first key must read everything.
• No key serves both. This is the trade a relational database hides with secondary indexes, and Bigtable makes you face it.
• The time-first key has a second problem. Every write in the last hour lands in one tablet: 100%. That is the hotspot Google warns about for row keys that start with a timestamp. With the zone first, the busiest tablet takes 35%.

The standard advice follows directly: lead the key with a high-cardinality value such as a device, user or zone, and put the timestamp after it.

Where analytics fits

None of the three is for analysis. They are built to answer small, precise requests quickly, thousands of times a second. Scanning a year of history to compute a trend is a different workload, and running it on an operational database slows the application it serves.

Analysis belongs in BigQuery. The data gets there by loading, by change data capture, or, for occasional questions on live data, by querying across. See BigQuery federated queries and loading data into BigQuery.

Choosing

1. Is it a normal application with relational data that fits on one machine? Cloud SQL. Start here.
2. Does it need relational transactions at a size or geographic spread one machine cannot give? Spanner.
3. Is it a very large volume of data, written fast and read by a known key, such as time series, events or per-user feature values? Bigtable.
4. Is the purpose to analyse data, not to serve an application? None of these; use BigQuery.

Two warnings. Do not choose Bigtable because the data is large; choose it because the access pattern is by key. And do not choose Spanner for prestige; a single well-sized relational instance is simpler and is enough far more often than people expect.

For how these compare with the other clouds' databases, see GCP vs AWS vs Azure. For the modelling ideas behind relational design, see data modelling: star schema, snowflake and data vault.

Databases and data modelling are covered in the Data Engineering course.

Sources

• Cloud SQL overview (Google Cloud Documentation) — Google Cloud
• Instances overview (Google Cloud Documentation) — Google Cloud
• Spanner: TrueTime and external consistency (Google Cloud Documentation) — Google Cloud
• Schemas overview (Google Cloud Documentation) — Google Cloud
• Bigtable overview (Google Cloud Documentation) — Google Cloud
• Schema design best practices (Google Cloud Documentation) — Google Cloud
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission