cert-bank

Guides

이 글을 한국어로 읽기

Cloud SQL vs AlloyDB vs Spanner vs Bigtable vs Firestore vs BigQuery: How to Tell Which One a Question Wants

Google Cloud has six data stores that turn up as options in the same exam questions: Cloud SQL, AlloyDB for PostgreSQL, Spanner, Bigtable, Firestore and BigQuery, often with Memorystore as a seventh. Every one of them is a good product, so the question is never "which is best?" but "which matches the way this data is read and written?". This comparison gives you the three questions that settle almost every case, and the clue words that answer them.

Concept comparison · GCP-ACE, GCP-CDL, GCP-PCA and GCP-PDE

Cloud SQL vs AlloyDB vs Spanner vs Bigtable vs Firestore vs BigQuery: How to Tell Which One a Question Wants

Why this matters in four exams

The choice appears at every level. In the Associate Cloud Engineer, Cloud Digital Leader, Professional Cloud Architect and Professional Data Engineer questions of this question bank, 27 questions name two or more of the operational databases (Cloud SQL, AlloyDB, Spanner, Bigtable, Firestore) in their answer options, and Cloud SQL alone is named in the stem or options of 60 questions across those four exams. The depth changes (the Cloud Digital Leader asks which product fits a business, the Data Engineer asks about row keys and partitions), but the logic is the same.

Question 1: is this an application or an analysis?

Operational databases serve applications: many small reads and writes, each touching a few rows, with answers needed in milliseconds. Cloud SQL, AlloyDB, Spanner, Bigtable and Firestore all belong here. BigQuery is a different kind of service: a serverless data warehouse that scans large amounts of data with SQL for reports, dashboards and analysis, with storage and compute separate and nothing to size or patch.

The clue words for BigQuery are "analysts", "ad hoc SQL", "years of history", "terabytes", "dashboards", "no infrastructure to manage". The classic wrong answer is a bigger Cloud SQL instance for analytics. The opposite mistake also appears: BigQuery as the back end of a shopping cart or a mobile app, where each request reads or writes one record.

Question 2: if it is relational, how far can one primary take it?

Three services speak SQL with transactions, and they differ in how they scale writes.

  • Cloud SQL runs managed MySQL, PostgreSQL or SQL Server. Google handles backups, patching and failover; high availability adds a standby in another zone, and read replicas add read capacity. It fits regional applications of moderate size, and lift-and-shift moves that want the same engine with less administration.
  • AlloyDB for PostgreSQL is PostgreSQL-compatible and built for demanding work. Its columnar engine speeds up analytical queries on live transactional data, and read pool instances move reporting load off the primary. It fits a PostgreSQL system that has outgrown a general-purpose setup, especially one where heavy reports slow down order entry.
  • Spanner shards data automatically and scales both reads and writes horizontally while keeping transactions strongly consistent, in single-region, dual-region or multi-region configurations. It fits writers on several continents who must all see one correct answer: a global inventory, a payments ledger, seat reservations.

The deciding fact: Cloud SQL and AlloyDB send every write to one primary. Replicas and read pools scale reads, not writes. When a stem says writes must grow by adding capacity, or must be strongly consistent across regions, only Spanner fits. When it says neither, Spanner is over-engineering: it can start small (compute comes in steps of 100 processing units), but a 200 GB regional HR application gains nothing from scaling writes out and still pays the cost of moving to a different database.

Beginner tip: before you look at the options of a database question, fill in four blanks on scrap paper: "read by ___ (key, query, scan), written from ___ (one region, many), must be ___ (strongly consistent, fine if slightly stale), size ___". Then match the four words to the table below. It takes twenty seconds and stops the most common mistake, which is choosing the product you know best rather than the one the access pattern describes.

Question 3: if it is not relational, is it a document or a key?

  • Firestore is a serverless document database. Records are JSON-like documents in collections, fields can differ from one document to the next, and there is nothing to provision. Its mobile and web SDKs offer real-time listeners and offline sync, which is why a collaborative mobile app with offline editing points to it.
  • Bigtable is a wide-column NoSQL database for very large volumes of single-keyed data with low latency and high throughput: IoT readings, time series, financial ticks, marketing events. You read by row key or by a range of keys, throughput grows with nodes, and there are no joins across tables.
  • Memorystore is not a system of record but a managed in-memory cache (Valkey or Redis engines; Memorystore for Memcached is deprecated) for sessions, leaderboards and hot rows. Anything in it should be rebuildable from the database behind it.
Going deeper: why the Bigtable row key decides everything

Bigtable has one index, the row key, and stores rows sorted by it. Contiguous ranges of keys are served by the same node. That has two consequences the exam tests. First, reads are fast only when they follow the key: sensor42#20261030T0815 turns "this sensor's last hour" into one short range scan, while a filter on any other column means scanning far more data. Second, a key that starts with a timestamp sends every new write to the end of the key space, which is one node, so the cluster's other nodes sit idle while that one becomes a hotspot. Starting the key with a device or user identifier spreads writes across nodes and still keeps one entity's rows together. Replication between Bigtable clusters is eventually consistent, so an application that must read its own writes uses single-cluster routing; multi-cluster routing favours availability over freshness. When a question needs relational transactions that stay strongly consistent across regions, that is the line between Bigtable and Spanner.

Which Google Cloud data store does the question want?
Analysts, ad hoc SQL over years of history, dashboards, no servers?
YES →
BigQuery
↓ no
Relational, with writes that must scale out or stay strongly consistent across regions?
YES →
Spanner
↓ no
PostgreSQL, high performance, reports on live transactional data?
YES →
AlloyDB for PostgreSQL
↓ no
Existing MySQL, PostgreSQL or SQL Server app, minimal change, one region?
YES →
Cloud SQL
↓ no
Very large single-keyed data: time series, IoT, lookups by device and time?
YES →
Bigtable
↓ no
Mobile or web app, flexible documents, real-time sync, offline?
YES →
Firestore
↓ no
OTHERWISE →
Sub-millisecond reads of data that can be rebuilt: Memorystore in front of a database
Read the stem from the top: the first question answered yes names the store.

The clue table

The stem saysThe answer is usually
Existing MySQL, PostgreSQL or SQL Server app, minimal change, one regionCloud SQL
PostgreSQL, high performance, reports on live transactional dataAlloyDB
Relational, global writers, strong consistency, horizontal write scaleSpanner
Very high write throughput, time series, lookups by device and timeBigtable
Mobile or web app, flexible documents, real-time sync, offlineFirestore
Analysts, ad hoc SQL over large history, dashboards, no serversBigQuery
Sub-millisecond reads of data that can be rebuiltMemorystore in front of a database

Four traps, with the rule that settles each

  1. "Add read replicas" for a write bottleneck. Replicas and read pools add reads only; every write still goes to one primary. When writes must scale out, more replicas cannot help, and the answer moves to Spanner.
  2. "Use Bigtable replication" for globally consistent transactions. Bigtable replication is eventually consistent and Bigtable has no relational transactions; that requirement is Spanner's.
  3. "A bigger instance" for analytics. Large scans belong in BigQuery, not on the operational database that serves the application.
  4. "The biggest product to be safe". Exams reward the smallest service that meets the stated need. Over-buying adds cost and complexity and is marked wrong just like under-buying.

One more rule applies to every store here: replication and high availability protect against the loss of a zone or region, not against a bad write. A dropped table is dropped on every replica too. Recovering from it needs backups or point-in-time recovery, and each product has its own way of restoring.

Practise

Practise in the question bank: every question explains each option, right and wrong. Start with the domain you just studied, then take a timed mock exam.

Product capabilities as described in Google Cloud's documentation and the official exam guides. Database features change often; check the current product pages before relying on a detail.