Choosing Storage and Databases: Cloud SQL, BigQuery, Spanner, and More

Ask three questions in order and the answer usually falls out. First, what shape is the data — relational rows with joins and transactions, JSON-like documents, wide-column key-value at huge scale, or analytical tables you scan in aggregate? Second, what scale and where — one region or global, gigabytes or petabytes? Third, what consistency and latency — strict transactions, or eventual freshness with sub-millisecond reads? Relational plus regional scale means Cloud SQL (managed MySQL, PostgreSQL, and SQL Server) or, for demanding PostgreSQL performance, AlloyDB. Relational plus horizontal, global scale with strong consistency means Spanner — it is the only option that combines all three. Flexible documents with mobile and web sync means Firestore. Massive-throughput, low-latency wide-column data — time series, IoT, personalization — means Bigtable. Scanning terabytes for analytics with SQL means BigQuery, which is a serverless data warehouse, not an operational database.

Scenario: a global ticketing platform needs strongly consistent seat inventory across three continents — Spanner, because Cloud SQL cannot scale writes globally and Firestore's model does not fit relational inventory. Its analytics team then loads sales history for dashboarding — BigQuery. Its mobile app stores user preferences with offline sync — Firestore. The exam nearly always includes one requirement (global strong consistency, document sync, petabyte scans) that eliminates all but one service.

Cloud SQL is the default relational answer, and you deploy it in one command: gcloud sql instances create app-db --database-version=POSTGRES_16 --region=us-central1 --tier=db-custom-2-8192. For availability, enable regional HA at creation with --availability-type=REGIONAL, which maintains a synchronous standby in a second zone and fails over automatically — note this is zonal fault tolerance within one region, not multi-region. Read scale comes from read replicas, which can live in the same region or a different one; cross-region replicas are also Cloud SQL's disaster-recovery story, because you can promote one to a standalone primary.

AlloyDB is the step up for PostgreSQL: a Google-built, PostgreSQL-compatible engine that separates compute from storage and adds a columnar engine for fast analytical queries over operational data. Choose it over Cloud SQL when the question stresses demanding PostgreSQL performance, hybrid transactional-and-analytical queries, or migrating a heavyweight PostgreSQL estate that has outgrown Cloud SQL — while staying wire-compatible with PostgreSQL tools.

Spanner is deployed as an instance with a configuration that fixes its geography: gcloud spanner instances create orders --config=regional-us-central1 --processing-units=100 --description=Orders. The config is the redundancy decision — regional-* configs replicate across zones in one region, while multi-region configs replicate across regions for global strong consistency and higher availability. Capacity is sized in processing units or nodes and scales horizontally without downtime.

Pick multi-region only when the requirement is surviving a regional outage with no data loss or serving strongly consistent reads close to users in several regions; a single-region workload that just needs zonal resilience is served by a regional config. The trade-off to remember: Spanner costs more at small scale, so if a scenario fits comfortably in Cloud SQL, Spanner is the over-engineered wrong answer.

Analytics on live PostgreSQL data is where the three options separate. A Cloud SQL read replica takes reporting load off the primary, but it runs the same row-oriented engine, so a heavy scan or aggregate is no faster on it. A BigQuery federated query (EXTERNAL_QUERY) against Cloud SQL copies nothing — but it executes inside the Cloud SQL instance, so it adds load there and speeds nothing up. When the requirement is fast scans, joins and aggregates over current transactional data, the AlloyDB columnar engine is the feature built for it; when the analytics can tolerate a copy, replicate into BigQuery instead.

One gotcha across all three: these are regional-or-wider managed services — you never SSH into them, and machine-level tuning questions about them are distractors.

Firestore is created once per database as a project-level resource with a location that cannot be changed later: gcloud firestore databases create --location=nam5 creates a multi-region database. Firestore location IDs come in two kinds: multi-region IDs such as nam5 (United States) and eur3 (Europe), which replicate across regions for the highest availability, and regional IDs such as us-central1 or europe-west2, which cost less and keep data in one region. The location is permanent — moving a database means creating a new one and exporting and importing the data. Firestore gives you strongly consistent document reads and queries, real-time listeners, offline sync for mobile and web clients, and automatic scaling with no capacity planning — which is why mobile app backend is its exam cue. Choose Native mode for new applications; Datastore mode exists for server-side workloads built on the older Datastore API.

Bigtable is provisioned as an instance containing one or more clusters: gcloud bigtable instances create metrics --display-name=Metrics --cluster-config=id=metrics-c1,zone=us-central1-b,nodes=3. It is a wide-column store designed for very high read and write throughput at consistently low latency over huge datasets — time series, IoT telemetry, ad serving, personalization. Scale by adding nodes; add clusters in other zones or regions for replication. Row-key design decides performance: rows are stored sorted by key and split across nodes by key range, so a key that starts with a timestamp sends every new write to the same tablet and hotspots one node. Put a high-cardinality identifier first and the time second — field promotion such as sensor42#20261006T1200 — which spreads writes across nodes while still letting you range-scan one device's history in time order. App profiles control how requests reach a replicated instance: multi-cluster routing sends each request to the nearest available cluster and fails over automatically, while single-cluster routing pins traffic to one cluster (needed for single-row transactions) and requires you to fail over manually. Its limits are the selection signal: no joins (Bigtable now accepts GoogleSQL queries, but not joins across tables), single-row transactions only, and it is not cost-effective for small datasets — if the scenario is small or needs multi-row transactions, Bigtable is wrong.

Memorystore is the managed in-memory layer — Redis-compatible and Valkey offerings plus Memcached — used as a cache or session store in front of a database: gcloud redis instances create session-cache --size=5 --region=us-central1 --tier=standard creates a Redis instance with a replica and automatic failover. The tiers differ in what they survive and how they scale:

So read-heavy load points to Standard tier read replicas, while write throughput beyond one primary points to the sharded cluster offerings. Exam cue: reduce read latency for repeated lookups or store user sessions outside the app means Memorystore, not a bigger database.

BigQuery is the serverless data warehouse: you create datasets and tables, load or stream data in, and query with standard SQL while Google handles all capacity — bq mk --location=US sales_ds then bq load --source_format=CSV sales_ds.orders gs://sales-bucket/orders.csv ./schema.json. There is no instance to size, which is precisely why it is the analytics answer and never the operational-database answer: it is optimized for scanning columns across billions of rows, not for single-row lookups behind an application. When analytics results must be served to an application per key in milliseconds — recommendations or feature lookups — compute them in BigQuery and export them to an operational store such as Bigtable (reverse ETL, for example with EXPORT DATA to Bigtable, which runs on an Enterprise or Enterprise Plus edition reservation rather than on-demand pricing), then serve from there.

The streaming trio divides by job. Pub/Sub is the global messaging backbone — publishers send to topics, subscribers pull or receive pushes, delivery is at-least-once with automatic scaling — the answer for decoupling services and ingesting event streams: gcloud pubsub topics create clickstream and gcloud pubsub subscriptions create clickstream-sub --topic=clickstream. Dataflow is the managed processing engine (Apache Beam) that transforms data in motion or at rest — the answer whenever a question says transform, enrich, deduplicate, or window a stream, and Google provides prebuilt templates for common paths such as Pub/Sub to BigQuery. Managed Service for Apache Kafka runs real Kafka clusters as a managed service — the answer only when the scenario explicitly requires Kafka compatibility, usually migrating an existing Kafka estate without rewriting producers and consumers; for greenfield messaging, Pub/Sub is the simpler, serverless default.

Not every stream needs Dataflow. A Pub/Sub BigQuery subscription writes each message straight into a BigQuery table with no pipeline to run (gcloud pubsub subscriptions create events-bq --topic=clickstream --bigquery-table=PROJECT:ds.events --use-table-schema; --use-topic-schema or --use-table-schema maps message fields to columns, and without either flag each payload lands in a single data column), and a Cloud Storage subscription similarly lands messages as files. Add Dataflow only when the stream must be transformed, enriched, deduplicated or windowed on the way in.

Scenario: clickstream events publish to a Pub/Sub topic; a Dataflow streaming job windows and enriches them; results land in BigQuery for dashboards. That three-step pipeline — Pub/Sub ingests, Dataflow processes, BigQuery analyzes — is the canonical pattern when processing is required; drop the middle step when messages already match the table.

Cloud Storage prices the same durable object storage four ways, trading lower at-rest cost for retrieval fees and minimum storage durations. All classes share the same 11-nines durability design, the same APIs, and millisecond first-byte access — colder classes are not slower to read, they just charge for retrieval and early deletion. Choose the class per object, by how often each object is actually read, because the retrieval fee is charged on every read of a Nearline, Coldline or Archive object:

The minimum duration is the classic trap: deleting or rewriting an Archive object after two months still bills the full 365 days. Create a bucket with a default class and location: gcloud storage buckets create gs://acme-backups --location=us --default-storage-class=NEARLINE --uniform-bucket-level-access. Location is chosen at creation and is permanent:

Turbo replication exists only for dual-region buckets. Reads by compute running in a region that is part of the bucket's location — for example us-central1 reading a us multi-region or nam4 dual-region bucket — carry no network charge, while reading a regional bucket from a different region does. Autoclass can manage class transitions automatically, and Object Lifecycle Management rules (a Section 3 topic) downgrade or delete objects on a schedule you define.

Object storage is not a file system, so when a workload needs a shared POSIX file system mounted by many clients at once, you reach for the file family. Filestore is the default: managed NFS file shares that Compute Engine VMs and GKE Pods mount concurrently — the answer for lift-and-shift apps expecting an NFS mount, shared home directories, and content-management workloads. Create one with gcloud filestore instances create shared-fs --zone=us-central1-a --tier=BASIC_SSD --file-share=name=share1,capacity=2560GB --network=name=default, then mount it over NFS from any client in the VPC. Higher tiers add capacity, performance, and availability options.

Google Cloud NetApp Volumes is the enterprise option: fully managed NetApp ONTAP file storage supporting both NFS and SMB, with enterprise data-management features such as snapshots and replication. Its exam cue is explicit — SMB shares for Windows-based workloads or an organization standardized on NetApp tooling migrating to Google Cloud. Managed Lustre serves the opposite extreme: a managed parallel file system for high-performance computing and AI training, where thousands of clients need massive aggregate throughput to shared scratch data. If the scenario says HPC cluster or feeding a large training job from a shared file system, Managed Lustre is the answer; for everything ordinary, it is over-engineered.

Cloud Storage FUSE mounts a bucket as a local directory, which is useful for reading training data or files with ordinary tools, but it is not NFS and not fully POSIX: there is no file locking and concurrent writes to the same file are not coordinated, so it does not replace Filestore for many clients writing shared files.

Selection in one line: general NFS means Filestore, SMB or NetApp enterprise features mean NetApp Volumes, HPC-scale parallel throughput means Managed Lustre, and if no client actually needs a mounted file system, step back to Cloud Storage.

Choose a loading tool by size, source, and cadence. For ad hoc uploads from a workstation or VM, use the gcloud storage commands (successor to the legacy gsutil): gcloud storage cp ./export.csv gs://acme-ingest/ copies a file, and gcloud storage rsync ./data gs://acme-ingest/data --recursive synchronizes a directory, transferring only differences — the right answer for scripted, repeatable pushes of moderate size. Details that change the outcome:

• cp, mv and rsync all need --recursive (-r) to include a directory tree; without it subdirectories are skipped or the command fails.
• mv copies and then deletes the source; cp leaves the source in place.
• In wildcards, * matches within one directory level and ** matches across levels.
• cp --no-clobber skips objects that already exist at the destination — so a changed file is not updated; use rsync when changes must propagate.
• rsync takes SOURCE then DESTINATION and only ever changes the destination; --delete-unmatched-destination-objects also deletes destination objects that are not in the source, making the destination an exact mirror (get the order wrong and you delete the wrong side).

Large single files are uploaded as parallel composite chunks automatically, but the tool is still bounded by your machine and network, which is the limitation that motivates the next option.

For large-scale or recurring movement, use Storage Transfer Service: a managed service that transfers data into Cloud Storage from other cloud object stores, HTTP endpoints, other buckets, and — via agents — on-premises file systems, with scheduling, filtering, checksumming, and retries handled for you. Exam cues: terabytes from another cloud provider's object storage, nightly sync from a data center, or move objects between buckets on a schedule all mean Storage Transfer Service, not a scripted copy. Transfers from Amazon S3 and other cloud stores run agentless; agents are needed only for on-premises file systems and private sources.

When the network is the bottleneck, Transfer Appliance is the offline option: Google ships a storage device, you copy data onto it locally and ship it back for upload. Decide with arithmetic: a fully used 1 Gbps link moves roughly 10 TB a day, so tens of terabytes over a fast link finish online in days, while hundreds of terabytes to petabytes over a slow or shared link would take weeks or months — that is the appliance case. Two similarly named services do different jobs:

Loading into databases follows a two-step pattern: stage in Cloud Storage, then load. BigQuery loads directly from a bucket — bq load --source_format=NEWLINE_DELIMITED_JSON app_ds.events gs://acme-ingest/events/*.json — with load jobs free of query cost. By default bq load appends to an existing table; add --replace to overwrite it, and for CSV files with a header row add --skip_leading_rows=1 so the header is not loaded as data. Cloud SQL imports SQL dumps or CSV from a bucket with gcloud sql import sql app-db gs://acme-ingest/dump.sql --database=app; the import runs as the instance's service account (the serviceAccountEmailAddress shown by gcloud sql instances describe), not as you, so that account needs read access to the bucket for imports (object get and list — Storage Object Viewer covers it, and the Cloud SQL docs suggest Storage Object Admin) and Storage Object Admin, which can create objects, for exports — the most common cause of a failed import. Firestore and Spanner similarly import from Cloud Storage exports, and Dataflow templates cover streaming and transform-heavy loads.

Multi-region redundancy is configured differently in every service, and the exam tests that you know which knob each one exposes. In Cloud Storage, it is the bucket location: multi-region and dual-region buckets store data redundantly across regions automatically, and dual-region buckets can add turbo replication, which targets 15 minutes for newly written objects instead of the default objective (99.9% within one hour, 100% within 12 hours) — nothing to operate, just choose the location at creation. In Spanner, it is the instance configuration: pick a multi-region config at creation and Spanner synchronously replicates with global strong consistency and automatic failover. In Firestore, it is the database location: multi-region locations such as nam5 or eur3 replicate across regions with strong consistency and the highest availability tier.

Services without a single switch use replicas. Cloud SQL achieves cross-region redundancy with cross-region read replicas that you promote during a regional outage — an explicit, operator-driven failover, distinct from the automatic zonal failover of regional HA; remember that pairing: HA is zonal, replicas are regional DR. Bigtable replicates by adding clusters in other regions to an instance; replication is asynchronous and eventually consistent, and app profiles route traffic between clusters — multi-cluster routing fails over automatically, single-cluster routing needs a manual switch. BigQuery datasets placed in a multi-region location such as US or EU are stored redundantly within that geography, and cross-region dataset replication can copy datasets between regions for disaster recovery. Memorystore offers a cross-zone replica with automatic failover in Standard tier and per-shard replicas in its cluster offerings — but a cache should be rebuildable by design.

Scenario: a health-tech company must survive a full regional outage. It stores documents in a dual-region bucket with turbo replication, runs Spanner in a multi-region config for patient-facing transactions, keeps a cross-region Cloud SQL replica for a legacy reporting app, and places BigQuery datasets in the US multi-region. Four services, four different mechanisms — knowing which is automatic (Storage, Spanner, Firestore, BigQuery within a multi-region) and which is operator-driven (Cloud SQL promotion, Bigtable failover under single-cluster routing) is exactly what the question will probe.