Choose a Google Cloud Database by Data and Query Shape

Database Decision

Google Cloud offers managed relational, document, wide-column, distributed relational, cache, and warehouse services. Product selection should begin with transactions, query patterns, consistency, scale, latency, recovery, and operational ownership.

A familiar API is useful but not decisive. The wrong data model creates application complexity that no amount of instance sizing can repair.

Database Selection

Service	Strong fit	Question to resolve
Cloud SQL	Managed MySQL, PostgreSQL, or SQL Server applications	Can one regional relational primary and its replica model meet scale and recovery needs?
Firestore	Document-oriented mobile and web data with indexed queries	Do document boundaries and query limits fit the domain?
Spanner	Relational transactions requiring horizontal or multi-region scale	Does the workload justify its schema discipline and cost model?
Bigtable	Low-latency, high-throughput key or time-series access	Can every critical query begin from a well-designed row key?
Memorystore	Managed Redis or Memcached caching	What happens when cached data is evicted or unavailable?

Connections and Identity

Cloud SQL applications need bounded connection pools because serverless scaling can create more client processes than the database accepts. Private IP, connectors, TLS, IAM database authentication, and Secret Manager solve different parts of the connection path.

Firestore and Bigtable avoid traditional connection pools but require deliberate keys, indexes, hotspot prevention, and retry behavior. Spanner transaction modes and key distribution deserve load tests with production-like access patterns.

Write a Firestore document with a server timestamp

import { Firestore, FieldValue } from "@google-cloud/firestore";

const db = new Firestore();
const order = db.collection("orders").doc("order-1001");

await order.create({
  customerId: "customer-42",
  totalMinor: 149900,
  currency: "INR",
  status: "paid",
  createdAt: FieldValue.serverTimestamp()
});

create fails if the document already exists, which can protect an idempotency boundary.
Store money as integer minor units with an explicit currency.

Restore Procedure

Automated backups are only inputs to recovery. Restore into an isolated target, validate schema and data, rotate any copied secrets, run application checks, and document the point at which traffic can move.

Read replicas improve read capacity and can support availability patterns, but replication is not protection from bad writes. Define recovery point and recovery time targets before choosing backup frequency and topology.

Workload and Data Contract

Choose a database from the invariants it must protect, the queries it must answer, and the failures it must survive. Record entities, keys, relationships, transaction boundaries, read and write rates, value sizes, retention, consistency needs, latency objectives, residency, recovery point objective, and recovery time objective. A product comparison without this workload contract turns familiar terminology into architecture by guesswork.

Cloud SQL provides managed MySQL, PostgreSQL, or SQL Server for conventional relational workloads. AlloyDB for PostgreSQL targets demanding PostgreSQL-compatible transactional and analytical workloads. Spanner provides relational semantics with horizontal scale and strong consistency across distributed deployments. Firestore is a document database for application data and real-time clients; Bigtable is a wide-column system for very large low-latency key-based workloads; BigQuery is an analytical warehouse rather than an online transaction database.

Memorystore can serve low-latency cache and ephemeral coordination patterns, but a cache does not become the durable source of truth merely because it is fast. Search, graph, vector, time-series, or streaming needs may require a specialized service or a deliberate model in a general system. Validate product limits, regional availability, client support, and pricing against a representative dataset before committing.

Name the authoritative store for every business fact.
Write dominant queries before designing keys and indexes.
Separate transactional, analytical, cache, and search responsibilities.
Prototype the hardest query and failure path with real data shape.

Transactions and Consistency

A transaction defines which changes succeed or fail together. Keep it aligned with a business invariant and short enough to avoid unnecessary contention. Retrying a transaction can rerun application code, so external side effects such as email or event publication need an outbox, idempotency record, or post-commit workflow. A database commit that times out at the client is ambiguous until reconciled by a stable operation identifier.

Consistency is observable behavior, not a marketing label. Ask whether a read must include the caller's prior write, whether two users may briefly see different versions, whether globally ordered updates are required, and what happens during partition or failover. Spanner, Firestore, Bigtable, Cloud SQL, and analytical systems expose different transaction, snapshot, and replication models; teach the application to use the selected model instead of assuming every read is current.

Indexes accelerate selected access paths but add storage and write work. Composite, secondary, covering, and search indexes have product-specific rules. Use query plans, scanned rows or bytes, lock and wait evidence, and latency distributions to tune. Avoid adding indexes from a slow-query symptom before confirming that network time, connection waits, hot keys, or downstream work is not the actual bottleneck.

Make command identifiers unique when writes may be retried.
Reconcile ambiguous outcomes before replaying a payment or mutation.
Test concurrent writers against each important invariant.
Measure index benefit and write cost after realistic growth.

Topology and Connection Control

High availability, read replicas, cross-region replication, and backups solve different problems. A regional Cloud SQL high-availability setup can fail over within a region, while a read replica may serve reads or assist disaster recovery according to service behavior. Spanner instance configuration determines placement and quorum behavior. Firestore location and Bigtable cluster design affect latency and resilience. Diagram every writer, reader, replica, and failover dependency.

Applications must bound connection pools across every replica, process, function host, and autoscaled instance. A serverless platform can create clients faster than a relational database accepts sessions. Use language-appropriate pooling or connectors, limit concurrency, close leaked resources, and reserve administrative capacity. Backoff and jitter help transient failures only when the operation is safe to retry and the total deadline remains useful.

Hotspots appear when a key design sends disproportionate traffic to one range, partition, document, or row. Sequential identifiers, monotonically increasing timestamps, a popular tenant, or a single counter can defeat horizontal capacity. Use documented key-distribution patterns, sharded counters where appropriate, workload partitioning, and load tests that preserve real skew rather than uniformly random synthetic keys.

Budget total connections from maximum application scale.
Classify driver errors as retryable, terminal, or ambiguous.
Test failover with existing sessions and in-flight transactions.
Detect hot keys through per-partition and per-query evidence.

Backup, Migration, and Verification

Backups, point-in-time recovery, exports, replicas, and change streams have different guarantees. Define which protects deletion, corruption, region loss, schema error, and compromised credentials. Place recovery artifacts in a protected administrative boundary where supported, retain them for the business requirement, and monitor failed backup jobs. Replication can faithfully copy a bad update and therefore cannot replace history.

A migration plan covers schema conversion, initial copy, ongoing change capture, application compatibility, cutover, verification, rollback, and decommissioning. Measure replication lag and identify writes that cannot be represented at the destination. Dual writes are difficult to make correct; prefer a controlled source of truth plus change propagation unless the consistency design has been proven.

Run restore drills into an isolated environment, validate row counts and business invariants, replay dependent events safely, rotate credentials, and measure elapsed recovery time. After a failover or cutover, verify read and write paths, indexes, jobs, replicas, monitoring, backups, and cost. Do not delete the previous system until the rollback window and evidence requirements have been satisfied.

Map each recovery mechanism to a named failure scenario.
Validate data semantics, not only successful import status.
Keep rollback possible until post-cutover checks pass.
Record measured RPO and RTO after every exercise.

Deletion and Corruption Drill

Practice two different incidents: an operator deletes authoritative rows, and a valid application release writes logically corrupt values. For deletion, identify the clean recovery point and restore into isolation. For corruption, determine how to locate affected records, preserve later valid writes, and repair only the damaged business state.

Point-in-time recovery can recreate an earlier database state but cannot decide which post-incident transactions the business still needs. Use audit records, change streams, outbox events, or application history to build a reconciliation plan. Suspend destructive writers before recovery and preserve evidence for root-cause analysis.

Verify constraints, indexes, permissions, encryption, backups, replicas, jobs, and downstream consumers after repair. Measure detection time as well as restore time; a one-hour restore does not meet a one-hour RTO if corruption remains unnoticed for a day.

Distinguish physical restore from business reconciliation.
Preserve valid writes that occurred after the bad change.
Verify backup resumes after recovery.

Database Decision Examples

Select a Database for an Inventory Workflow

Inventory reservations need transactions while product search needs flexible read models.

Select a Database for an Inventory Workflow

Constraints: Reservation writes cannot oversell; search may be eventually consistent; recovery must be tested.
Decision: Keep reservation authority in a transactional database and publish committed changes to a search-oriented read model.
Verification: Concurrent reservation tests preserve stock, lag is measured, and a restore drill rebuilds the read model.
Failure test: Pause event delivery and confirm stale search never becomes the authority for a reservation.

Output

Expected evidence: Concurrent reservation tests preserve stock, lag is measured, and a restore drill rebuilds the read model.

This is a worked engineering decision, so the result is operational evidence rather than terminal output.

Before you move on