Your application needs to store, retrieve, and remember things. Without a database, your code is just a brain in a jar — it cannot do anything meaningful. The challenge in Google Cloud is that there is not one database service. There are six major ones, and each is designed for a fundamentally different use case.
Picking the wrong database is one of the most expensive architectural mistakes you can make. Let me walk you through all of them so you know exactly when to use what.
Cloud SQL — The Reliable Workhorse
For most applications, the journey starts here. Cloud SQL is a fully managed relational database service supporting MySQL, PostgreSQL, and SQL Server. “Fully managed” means Google handles patching, updates, backups, and replication. You focus on your schemas and queries.
When to use it: Standard web applications, e-commerce sites, WordPress blogs — anything where data has clear relationships (users have orders, orders have products) and fits into rows and columns.
# Create a PostgreSQL instance
gcloud sql instances create my-postgres \
--database-version=POSTGRES_15 \
--region=us-central1 \
--cpu=2 \
--memory=4GB \
--storage-type=SSD
Output:
Creating Cloud SQL instance...done.
Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/my-project/instances/my-postgres].
Cloud SQL — High Availability and Read Replicas
What happens if the single VM running your database fails? Your site goes down. To fix this, you configure High Availability (HA). Google creates a standby instance in a different zone within the same region. Data is replicated synchronously. If the primary fails, automatic failover kicks in — usually within minutes.
For handling more read traffic, use Read Replicas. These are read-only copies using asynchronous replication. You can place them in different regions to serve reads closer to your users. Great for data analytics dashboards that query your production data.
Scaling: Cloud SQL scales vertically — give it a bigger machine (more CPU, more RAM). You can also increase storage on the fly.
Cloud SQL — Connectivity
How does your app talk to Cloud SQL? Three options:
- Public IP — Simple but less secure. You must configure authorized networks. Not recommended for production.
- Private IP — The instance gets an internal IP on your VPC. Your GKE pods and VMs communicate over the internal network. This is the recommended approach.
- Cloud SQL Auth Proxy — The gold standard. A small client that creates a secure, encrypted tunnel using IAM for authentication. No SSL certificates to manage. Your app connects to
localhost, and the proxy handles everything.
# Start the Cloud SQL Auth Proxy
./cloud_sql_proxy -instances=my-project:us-central1:my-postgres=tcp:5432 &
# Connect with psql through the proxy
psql "host=127.0.0.1 port=5432 sslmode=disable dbname=mydb user=myuser"
Cloud SQL — Backups
Automated daily backups run by default. You can also take on-demand backups. The killer feature is Point-in-Time Recovery (PITR) — restore your database to its exact state at any second within your retention window. If someone accidentally runs DROP TABLE users;, PITR saves you.
Cloud Spanner — Planet-Scale SQL
Your application is now global. Users in Tokyo, New York, Mumbai. Your single-region Cloud SQL instance is struggling. Read replicas help with reads, but all writes still go to the primary instance in us-central1. That creates huge latency for users in other regions. And you have already scaled vertically as far as you can.
You need a database that is globally distributed, horizontally scalable, AND strongly consistent. That sounds impossible, but it is exactly what Cloud Spanner does.
Spanner looks and feels like a relational database — you write SQL, define schemas, and get ACID transactions. But it scales horizontally like a NoSQL database. It achieves this through Google’s dedicated fiber network and atomic clocks.
When to use it: Financial trading platforms, global inventory systems, leaderboards — anything that needs massive horizontal scale with strong transactional consistency.
# Create a Spanner instance
gcloud spanner instances create my-spanner \
--config=regional-us-central1 \
--description="My Spanner Instance" \
--nodes=1
For global reach, use a multi-region config like nam-eur-asia1.
Scaling is Spanner’s superpower — just add more nodes. Spanner automatically rebalances data across them. In multi-region configurations, it maintains read-write replicas in some regions and read-only replicas in others, providing low-latency reads globally and surviving entire region outages.
The critical design rule: Your row key design matters enormously. A sequential timestamp as a primary key creates hotspots — all new writes hit the same node. Design your keys to distribute writes evenly.
Firestore — Serverless NoSQL
Now let us say you need something more flexible. User profiles where each user has different attributes. A real-time chat feature. Forcing this kind of schema-less data into rigid SQL tables feels wrong.
Firestore is a fully managed, serverless, NoSQL document database. Instead of rows in tables, you store documents (like JSON objects) organized into collections.
What makes Firestore special:
- Serverless — No instances to manage, no nodes to provision. Scales automatically from zero.
- Real-time Updates — Your app can subscribe to a query, and Firestore pushes changes in real time. Perfect for chat, collaboration, and live dashboards.
- Scales to Zero — You pay only for reads, writes, and storage. No traffic = no cost.
When to use it: Mobile apps, real-time collaboration tools, user profiles, product catalogs — anything with fluid schemas or client-to-server sync requirements.
A note on Datastore: you might see “Datastore mode” when creating a Firestore database. Datastore is the predecessor. For new projects, always choose Firestore in Native Mode.
Security for client-side access uses Firestore Security Rules:
// Allow users to read and write only their own profile
match /users/{userId} {
allow read, write: if request.auth.uid == userId;
}
Cloud Bigtable — The Big Data Beast
Your application now has an IoT component. Sensors are sending location and temperature data every second. Terabytes, even petabytes of time-series data pouring in.
Trying to put this into Firestore or Cloud SQL would be a disaster. Cloud Bigtable is designed for exactly this — massive-scale, low-latency operational and analytical workloads. It is the same database that powers Google Search, Maps, and Gmail.
When to use it: IoT data streams, financial market data, analytics ingestion pipelines, personalization engines. Not for your general-purpose web app backend.
# Create a Bigtable instance with 3 nodes
gcloud bigtable instances create my-bigtable \
--display-name="My Bigtable Instance" \
--cluster-config=id=my-cluster,zone=us-central1-b,nodes=3
The most critical thing: Row key design. A poorly designed row key creates hotspots where all traffic hits a single node. For time-series data, a common pattern is sensor_id#reverse_timestamp to distribute writes evenly.
Scale by adding more nodes. If CPU consistently exceeds 70-80%, add more.
Memorystore — The In-Memory Speed Demon
Your product pages are getting millions of hits. You are querying the same popular product info over and over. Hitting the database every time is inefficient and adds unnecessary load.
Memorystore is a fully managed in-memory data store for Redis and Memcached. All data lives in RAM, so read/write latency is measured in microseconds.
Use cases:
– Caching — Cache database query results, rendered HTML
– Session Management — Store user sessions
– Leaderboards — Redis sorted sets for real-time rankings
– Rate Limiting — Track API call counters
Redis vs. Memcached: Redis is the more powerful and recommended option. It supports rich data structures (strings, lists, sets, hashes), persistence, and replication. Memcached is simpler — pure key-value cache, volatile.
Important: Memorystore is only accessible via Private IP from within the same VPC and region. No public access. If connecting from Cloud Run or other serverless, you need a Serverless VPC Access connector.
Treat it as a transient cache, not permanent storage. Have a strategy to repopulate from your persistent database if the cache gets wiped.
BigQuery — The Analytics Powerhouse
You have operational data in Cloud SQL, user profiles in Firestore, IoT logs in Bigtable. Now the business team asks: “Which products are most viewed by users in Germany who have purchased more than three items in the last month?”
Running this query against your production database would crush it. This is not an operational problem — it is an analytical problem. You need a data warehouse.
BigQuery is a fully managed, serverless, petabyte-scale data warehouse. It runs SQL queries over massive datasets in seconds.
The architectural magic is separation of compute and storage. Data lives in Google’s distributed filesystem (Colossus). When you query, BigQuery spins up thousands of compute resources (Dremel engine) to scan data in parallel, then spins them down. You pay for storage and bytes processed.
# Run a query
bq query --use_legacy_sql=false \
'SELECT word, SUM(word_count) AS count
FROM `bigquery-public-data.samples.shakespeare`
WHERE word LIKE "%love%"
GROUP BY word
ORDER BY count DESC
LIMIT 10'
You can load data from Cloud Storage, stream it directly, or use Federated Queries to read from Cloud SQL, Spanner, or Bigtable without moving data.
Common Pitfalls and Best Practices
Cloud SQL:
– Pitfall: Public IP in production without authorized networks. Security risk.
– Best Practice: Use Private IP and the Cloud SQL Auth Proxy.
– Pitfall: No HA for production databases.
– Best Practice: Always enable HA. The cost is worth the resilience.
Cloud Spanner:
– Pitfall: Sequential primary keys causing hotspots.
– Best Practice: Design keys to distribute writes. Use interleaved tables.
– Pitfall: Overprovisioning nodes.
– Best Practice: Start small with Processing Units, monitor CPU, scale up as needed.
Firestore:
– Pitfall: Insecure or missing security rules.
– Best Practice: Write and test security rules thoroughly. They are part of your business logic.
Bigtable:
– Pitfall: Poor row key design — the #1 performance killer.
– Best Practice: Invest serious time in row key design.
Memorystore:
– Pitfall: Treating it as permanent storage. Data is volatile.
– Best Practice: Always have a repopulation strategy from your persistent database.
BigQuery:
– Pitfall: SELECT * FROM big_table without LIMIT. BigQuery charges by bytes processed.
– Best Practice: Select only the columns you need. Use the cost estimator before running queries.
– Pitfall: Not partitioning large tables.
– Best Practice: Partition by date and cluster by frequently filtered columns.
Quick Reference
# -- Cloud SQL --
gcloud sql instances create [NAME] --database-version=POSTGRES_15 \
--region=[REGION] --cpu=2 --memory=4GB
gcloud sql instances create [REPLICA] --master-instance-name=[MASTER]
gcloud sql connect [INSTANCE] --user=[USER]
# -- Cloud Spanner --
gcloud spanner instances create [NAME] --config=[CONFIG] --nodes=1
gcloud spanner databases create [DB] --instance=[INSTANCE]
gcloud spanner databases execute-sql [DB] --instance=[INSTANCE] \
--sql="SELECT * FROM Users LIMIT 10"
# -- Bigtable --
gcloud bigtable instances create [NAME] --display-name="[DISPLAY]" \
--cluster-config=id=my-cluster,zone=us-central1-b,nodes=3
# -- Memorystore (Redis) --
gcloud redis instances create [NAME] --size=4 \
--region=[REGION] --tier=STANDARD
# -- BigQuery --
bq query --use_legacy_sql=false 'SELECT ...'
bq load --source_format=CSV [DATASET].[TABLE] gs://[BUCKET]/file.csv
bq mk [DATASET]
