Cloud SQL and Managed Databases

Managed databases remove installation and patching, and leave you every decision that determines whether the database survives a bad afternoon. Three of those decisions get confused with each other constantly.

Topic 1: HA, Replicas and Backups Are Three Different Things

High availability — a synchronous standby in another zone of the same region. It serves nothing. On failure, Cloud SQL fails over in roughly 60 seconds and the connection name does not change. It doubles the instance cost and adds no read capacity.

Read replicas — asynchronous copies that serve reads. Up to ten per primary, optionally cross-region. Promotion is manual and permanently breaks replication.

Backups and PITR — automated daily backups plus write-ahead logs, giving recovery to any second in the retention window. This is the only one of the three that protects you from DELETE FROM orders with a bad WHERE.

gcloud sql instances create checkout-db \
  --database-version=POSTGRES_16 --tier=db-custom-4-16384 \
  --region=europe-west1 --availability-type=REGIONAL \
  --no-assign-ip --network=projects/acme/global/networks/prod-vpc \
  --enable-point-in-time-recovery --retained-backups-count=14 \
  --backup-start-time=02:00 --maintenance-window-day=SUN --maintenance-window-hour=3

--availability-type=REGIONAL is HA; ZONAL is a single instance and a zone failure is an outage. The flag is easy to miss and expensive to change later under load.

Topic 2: Connecting Without a Password on the Internet

The default that causes the most damage is a public IP with an authorised network of 0.0.0.0/0. Such an instance is scanned within minutes of creation.

Two correct patterns:

Private IP — the instance has no public address and is reachable only from your VPC (or via PSC, from other VPCs):

--no-assign-ip --network=projects/acme/global/networks/prod-vpc

Cloud SQL Auth Proxy / Connector — a local process or in-process library that establishes an IAM-authenticated, encrypted connection. No authorised-network list, no certificate management, and the application connects to 127.0.0.1:

./cloud-sql-proxy --private-ip --auto-iam-authn acme:europe-west1:checkout-db

IAM database authentication removes the password entirely — the database user is a service account, and access is granted and revoked in IAM:

gcloud sql users create checkout@acme.iam --instance=checkout-db --type=cloud_iam_service_account

That combination — private IP, connector, IAM auth — means there is no database password anywhere to leak, rotate or find in a config map.

Topic 3: What a Failover Actually Looks Like

gcloud sql instances failover checkout-db

From the application’s point of view: existing connections are dropped, the connection name resolves to the standby, and new connections succeed after roughly 60 seconds. Two consequences worth designing for:

• Your application must reconnect. A pool that treats a dropped connection as fatal turns a 60-second failover into an outage that lasts until someone restarts the service. Test this rather than assuming the driver handles it.
• In-flight transactions are lost. The standby is synchronous for committed writes, so committed data survives; uncommitted work does not.

Maintenance is the failover you did not initiate. Cloud SQL patches during your maintenance window using the same mechanism, so a window set to your busiest hour is a self-inflicted incident:

gcloud sql instances patch checkout-db \
  --maintenance-window-day=SUN --maintenance-window-hour=3 \
  --maintenance-release-channel=production

--maintenance-release-channel=preview gets updates earlier — appropriate for a staging instance, so you find breakage before production does.

Topic 4: Replica Lag, and the Bug It Creates

A read replica is asynchronous, so it is always some amount behind. That produces a specific class of application bug: a user writes a record, is redirected to a page that reads it from a replica, and the record is not there yet.

Alarm on:  database/replication/replica_lag

The rules that keep replicas useful:

• Route reads that must be consistent to the primary. “Read your own writes” cannot come from an async replica.
• A lagging replica serves wrong answers, not slow ones. Ten minutes behind is worse than down, because nothing looks broken.
• A cross-region replica is a DR component, not a DR plan. Promotion is manual, takes minutes, breaks replication permanently, and the application still needs repointing. The plan is the written sequence; the replica is one step in it.

Topic 5: Choosing the Right Managed Database

Two decisions that are frequently wrong:

• Spanner chosen for scale it does not need. It is the right answer for genuinely global, strongly consistent writes, and an expensive answer to “our Postgres is at 60% CPU”. Right-size Cloud SQL or move to AlloyDB first.
• BigQuery used as an application backend. It is a warehouse: no primary keys, no low-latency point updates, and per-query billing that punishes an application’s access pattern.

Topic 6: The Operational Baseline

□ REGIONAL availability for anything production
□ private IP only; no authorised networks
□ Auth Proxy / Connector with IAM authentication
□ PITR on, retention chosen against a written RPO
□ deletion protection enabled
□ maintenance window in your genuine trough, staging on preview channel
□ Query Insights on before you need it
□ alarms on CPU, memory, disk utilisation, connections and replica lag
□ storage auto-increase on — a full disk stops the database

The two metrics that predict most incidents: database/disk/utilization (a full disk is unrecoverable quickly) and database/postgresql/num_backends against max_connections. Connection exhaustion is the most common Cloud SQL outage, and the fix is usually a connection pooler — PgBouncer, or the built-in pooling in AlloyDB — rather than a bigger instance.

And run a restore drill. A backup nobody has restored is a hypothesis about a file format. Restore into a scratch instance quarterly, time it end to end, and write the number down — that number is your real RTO, and it is always longer than the estimate.

Try it yourself: force a failover while a query loop runs and record the gap, then repeat with your application’s real connection pool in the path. The two numbers are usually different, and the second one is the one your users experience.

Common mistake: enabling HA and treating it as a backup strategy. HA replicates every write synchronously — including the UPDATE that set every row’s price to zero. The standby has the same corrupted data, instantly, and the only thing that helps is point-in-time recovery. Availability and recoverability are separate features and you need both.