Bigtable vs Spanner vs BigQuery, in depth: access patterns, storage engines, one workload modelled three ways and how to choose

Three questions that decide the choice

Before comparing features, write down three facts about the workload. First, the read shape: do you fetch a row or a short range by a known key, or do you aggregate across most of a table? Second, the write contract: does a business operation have to change several rows, possibly in different tables, all or nothing? Third, the latency budget: does a user wait on the query, or does a dashboard or batch job?

Those three answers map almost directly onto the products. Known-key reads and very high write throughput with single-row atomicity point to Bigtable. Multi-row transactions with SQL, secondary indexes and strong consistency point to Spanner. Large scans, joins and aggregations where seconds are acceptable point to BigQuery. Many systems use two of the three with a pipeline between them.

How each engine stores and serves data

Bigtable is a sorted map from row key to a set of column families, columns and timestamped cells. A table is split into tablets, each a contiguous range of row keys. Nodes do not own data; they serve tablets whose files live in Colossus, Google's distributed file system, so adding nodes or moving a tablet is a metadata operation rather than a bulk copy. Writes go to a log and an in-memory table, then are flushed to sorted files and compacted. The only index is the row key. Atomicity is per row: a mutation that touches many columns in one row is all or nothing, but there are no multi-row transactions. Bigtable now also accepts GoogleSQL queries, including a _key column for the row key, but those queries still run against the same row-key layout, so a filter that does not constrain the key is still a scan.

Spanner is a relational database whose tables are sorted by primary key and divided into splits. Each split is replicated with Paxos; one replica is the leader that coordinates writes. Read-write transactions take locks, and transactions that touch several splits commit with two-phase commit across the Paxos groups. Commit timestamps come from TrueTime, a clock API with bounded uncertainty, and Spanner waits out that uncertainty so that timestamp order matches real-time order. That is what gives external consistency: if transaction B starts after A commits, B sees A, everywhere. The mechanism is covered in Spanner TrueTime.

BigQuery separates storage from compute. Tables are stored in a columnar format on Colossus, and queries are executed by a pool of workers measured in slots, with an in-memory shuffle between stages. A query reads only the columns it names and, with partitioning and clustering, only blocks that can match. It supports DML and transactions, but is built for throughput, not millisecond row updates.

One workload, three models

Take one workload: a fleet of 200,000 delivery vans, each reporting position, speed and battery every 10 seconds. That is 20,000 writes per second, about 1.7 billion rows per day. Three teams want the data. Operations needs the last hour of readings for one van in under 20 ms. Billing needs to charge a customer and decrement a prepaid balance in one atomic step whenever a delivery completes. Analysts want weekly energy use per depot across a year of history.

Bigtable model for operations. The row key decides everything. A key that starts with a timestamp sends every write to the last tablet and creates a hotspot. Starting with the van id spreads writes across the key space, and appending a reversed timestamp makes the newest readings sort first, so the last hour is a short prefix scan.

from google.cloud import bigtable
from google.cloud.bigtable.row_set import RowSet

MAX_TS = 10**13   # milliseconds; reversed so newest sorts first

def row_key(van_id: str, ts_ms: int) -> bytes:
    return f"{van_id}#{MAX_TS - ts_ms:013d}".encode()

client = bigtable.Client(project="fleet-prod")
table = client.instance("telemetry").table("readings")

def write(van_id, ts_ms, lat, lon, speed, battery):
    row = table.direct_row(row_key(van_id, ts_ms))
    for col, val in (("lat", lat), ("lon", lon), ("speed", speed), ("battery", battery)):
        row.set_cell("m", col, str(val).encode())
    row.commit()                       # atomic for this one row only

def last_hour(van_id, now_ms):
    rows = RowSet()
    rows.add_row_range_from_keys(row_key(van_id, now_ms), row_key(van_id, now_ms - 3_600_000))
    return list(table.read_rows(row_set=rows))

In production you would batch mutations with table.mutate_rows rather than committing one row at a time, and set a garbage-collection policy on the column family so old cells expire.

Spanner model for billing. Balances and charges must change together. Interleaving the Charges table in Accounts stores each customer's charges physically next to the account row, so the common transaction touches one split.

CREATE TABLE Accounts (
  AccountId   STRING(36) NOT NULL,
  BalanceCents INT64 NOT NULL,
) PRIMARY KEY (AccountId);

CREATE TABLE Charges (
  AccountId  STRING(36) NOT NULL,
  ChargeId   STRING(36) NOT NULL,
  AmountCents INT64 NOT NULL,
  CreatedAt  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp = true),
) PRIMARY KEY (AccountId, ChargeId),
  INTERLEAVE IN PARENT Accounts ON DELETE CASCADE;

import uuid
from google.cloud import spanner
from google.cloud.spanner_v1 import param_types

db = spanner.Client().instance("billing").database("ledger")

def charge(txn, account_id, amount):
    rows = list(txn.execute_sql(
        "SELECT BalanceCents FROM Accounts WHERE AccountId = @a",
        params={"a": account_id}, param_types={"a": param_types.STRING}))
    if not rows or rows[0][0] < amount:
        raise ValueError("insufficient balance")
    txn.execute_update(
        "UPDATE Accounts SET BalanceCents = BalanceCents - @amt WHERE AccountId = @a",
        params={"amt": amount, "a": account_id},
        param_types={"amt": param_types.INT64, "a": param_types.STRING})
    txn.insert("Charges", columns=("AccountId", "ChargeId", "AmountCents", "CreatedAt"),
               values=[(account_id, str(uuid.uuid4()), amount, spanner.COMMIT_TIMESTAMP)])

db.run_in_transaction(charge, "acct-42", 1250)   # retried automatically on abort

The function passed to run_in_transaction may run more than once if Spanner aborts the transaction because of lock contention, so it must not have side effects outside the database, such as sending an email.

BigQuery model for analytics. Partition by day so a weekly query reads seven partitions, and cluster by the columns most often filtered. The techniques are explained in BigQuery partitioning and clustering.

CREATE TABLE fleet.readings (
  van_id STRING, depot STRING, ts TIMESTAMP,
  speed FLOAT64, battery_pct FLOAT64, energy_wh FLOAT64
)
PARTITION BY DATE(ts)
CLUSTER BY depot, van_id;

SELECT depot, DATE_TRUNC(DATE(ts), WEEK) AS week, SUM(energy_wh) / 1000 AS kwh
FROM fleet.readings
WHERE ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)
GROUP BY depot, week
ORDER BY depot, week;

What happens when you pick the wrong one

Now swap the roles. Put the operations query on BigQuery and each lookup of one van becomes a job with scheduling overhead of hundreds of milliseconds or more, billed by bytes read on on-demand pricing. Put the analytics query on Bigtable and you scan the whole table through client code, with no columnar pruning or distributed aggregation, competing with the writes for node CPU. Put billing on Bigtable and the balance update and the charge insert are two rows, so a crash between them leaves money unaccounted for; you end up rebuilding transactions in application code. Spanner could store the telemetry, but every write would pay for transaction machinery that independent sensor readings do not need.

Moving data between them

The common architecture keeps each engine on its own job and moves data between them. Writes land where the write contract is enforced, and copies flow towards the analytical side.

• Spanner to BigQuery. Spanner change streams record row changes and can be read by a Dataflow pipeline that writes to BigQuery. For ad hoc reads, BigQuery federated queries can read Spanner directly, and Spanner Data Boost runs such reads on separate compute so they do not compete with transactional traffic.
• Bigtable to BigQuery. BigQuery can define an external table over a Bigtable table for occasional queries, and Bigtable change streams can feed a pipeline for continuous replication. External queries scan Bigtable, so run them against a replica cluster with its own app profile.
• BigQuery to Bigtable. Features and aggregates computed in BigQuery are often served from Bigtable at low latency; BigQuery's EXPORT DATA statement can write query results into Bigtable, which is a common pattern for feature serving.

In the fleet example, readings stream into Bigtable and, through Pub/Sub and Dataflow, into BigQuery; billing replicates from Spanner to BigQuery with a change stream.

Consistency in practice

Consistency differs more than most comparisons admit. Spanner offers strong reads by default and externally consistent transactions; stale reads at a chosen staleness can be served by the nearest replica without contacting the leader, which suits read-heavy pages that tolerate slightly old data.

Bigtable is strongly consistent within a single cluster. With replication across clusters it is eventually consistent: a write to one cluster reaches the others after a delay. An app profile with multi-cluster routing sends requests to the nearest available cluster and fails over automatically, but a client can then read from a cluster that has not received its own recent write. If you need read-your-writes, use single-cluster routing for that traffic and accept manual failover.

In BigQuery the practical issue is freshness rather than consistency: if the pipeline into it lags, dashboards show stale numbers with no error.

Failure modes

These are the failures that reach incident reviews, with the guard for each.

• Bigtable hotspots. Keys that begin with a timestamp or a sequential id concentrate writes on one tablet; one node saturates while the others idle. Guard: lead the key with a high-cardinality field and check Key Visualizer before launch.
• Spanner sequential primary keys. The same hotspot appears with auto-increment style keys. Guard: use UUIDv4 keys or a bit-reversed sequence so inserts spread across splits.
• Spanner long transactions. A transaction that reads many rows, calls an external service and then writes holds locks for the whole time and gets aborted under contention. Guard: keep transactions short, do external calls outside them, and make the transaction body idempotent because it may be retried.
• BigQuery runaway scans. A SELECT * over an unpartitioned table reads every column of every row. Guard: require partition filters on large tables and set a maximum bytes billed on interactive queries.
• Using BigQuery as an OLTP store. Per-row DML from an application quickly hits DML concurrency limits and latency. Guard: batch writes, or put the operational path on Spanner or Bigtable.

Capacity and cost models

The capacity models differ in kind, and that shapes cost behaviour. Bigtable charges for provisioned nodes plus storage, with a choice of SSD or HDD storage; nodes can autoscale on CPU and storage targets. You pay for nodes whether or not they are busy, so a steady high-throughput workload is efficient and a spiky small one is not. Spanner is provisioned in processing units, where 1000 processing units equal one node and instances can start at 100 processing units, with editions that add features such as more replication options. Spanner also has a managed autoscaler. BigQuery offers on-demand pricing per byte scanned or capacity pricing in slots through editions and reservations.

Prices vary by region and edition, so price your workload with the official calculator. As a rule, Bigtable and Spanner cost scales with throughput and storage, while BigQuery on-demand cost scales with bytes read, so table layout is a cost control in BigQuery.

Decision table

Read the table as a procedure. If the workload needs multi-row atomic updates, start with Spanner. Otherwise, if reads are by key with tight latency and writes are high volume, choose Bigtable. If the main consumers aggregate large ranges and can wait seconds, choose BigQuery. When the answer is two of these, keep writes on the transactional or serving side and replicate to the analytical side.

Trade-offs

Bigtable gives the lowest cost per write at high volume and predictable millisecond reads, but you pay with schema work: every query pattern must be designed into a row key or an extra table, and changing the key later means rewriting the data. Spanner gives SQL, indexes and real transactions across regions, at the price of higher write latency for multi-region configurations and higher cost per write than Bigtable. BigQuery gives analytical flexibility without capacity planning on on-demand pricing, but no serving-grade latency. Running two engines adds a pipeline and a freshness gap, usually cheaper than forcing one engine to do both jobs.

What to do next

• Write down read shape, write contract and latency budget for each consumer of the data before choosing.
• For a Bigtable design, list every query and show the row-key prefix that serves it; reject keys that start with time.
• For a Spanner design, pick non-sequential primary keys and use interleaving for parent-child access.
• For a BigQuery table, choose a partition column and clustering columns and set a maximum bytes billed on ad hoc queries.
• Prototype the critical path with realistic volume and measure p99 latency, not averages.
• Plan the replication path (change streams, Dataflow, federated queries) and alert on its lag.
• Price each option for your volume in the official pricing calculator and record the assumptions.