Scaling Read-Heavy Workloads with Cloud SQL Read Replicas

The problem: a primary instance overwhelmed by read traffic

When a Cloud SQL instance serves both write‑heavy transactional workloads and analytics or reporting queries, the primary can become a bottleneck. CPU utilization spikes, query latency grows, and scaling the instance vertically quickly becomes expensive.

Thesis: offload SELECT traffic to a read replica to keep the primary focused on writes while improving read latency and cost‑efficiency

Cloud SQL supports read replicas for MySQL, PostgreSQL, and SQL Server. A replica continuously streams binary logs from the primary, allowing applications to direct read‑only queries to it. This approach reduces load on the primary, provides geographic flexibility, and can be promoted to a standalone instance for failover or migration.

Section 1: Creating a read replica

You can create a replica via the Cloud Console or the gcloud CLI. The CLI method is repeatable in automation pipelines.

# Replace placeholders with your values
PROJECT_ID="my-project"
PRIMARY_INSTANCE="primary-db"
REPLICA_NAME="primary-db-replica"
REGION="us-central1"

# Ensure you have the Cloud SQL Admin API enabled and the required IAM role
# roles/cloudsql.admin (or a custom role with cloudsql.instances.create, etc.)
gcloud sql instances create $REPLICA_NAME \
    --project=$PROJECT_ID \
    --master-instance-name=$PRIMARY_INSTANCE \
    --region=$REGION \
    --tier=db-f1-small  # choose a machine type that matches your read workload

Run the command in Cloud Shell or any workstation with the gcloud SDK installed and authenticated (gcloud auth login). The command returns immediately; the replica creation proceeds asynchronously.

Verifying the replica is ready

After a few minutes, check the replication state:

gcloud sql instances describe $REPLICA_NAME \
    --project=$PROJECT_ID \
    --format="value(replicationState, masterInstanceName)"

You should see SYNCHRONOUS (or PENDING while the initial sync completes) and the master instance name matching your primary. If the state shows FAILED, review the operation logs in the Cloud Console for details.

Section 2: Directing read traffic to the replica

Applications only need to use the replica’s connection string (host, port, user, password). The credentials are identical to those of the primary because the replica inherits the same database users.

Example connection string for PostgreSQL:

POSTGRESQL_HOST="${REPLICA_NAME}.${REGION}.sql.googlecloud.com"
POSTGRESQL_PORT=5432
POSTGRESQL_USER="my_user"
POSTGRESQL_PASSWORD="${SQL_PASSWORD}"
# Use these values in your DSN or connection pool configuration

Configure your application’s read‑only data source (or a separate connection pool) to point at POSTGRESQL_HOST. Write operations continue to target the primary instance.

Checking that reads are served by the replica

Run a simple write on the primary, then a timed read on the replica to observe eventual consistency:

# 1. Insert a row on the primary (run against primary connection)
psql "host=$PRIMARY_INSTANCE.${REGION}.sql.googlecloud.com port=5432 dbname=mydb user=$POSTGRESQL_USER password=$POSTGRESQL_PASSWORD" -c "INSERT INTO test_table (val) VALUES (now());"

# 2. Immediately query the replica and note the timestamp
psql "host=$REPLICA_NAME.${REGION}.sql.googlecloud.com port=5432 dbname=mydb user=$POSTGRESQL_USER password=$POSTGRESQL_PASSWORD" -c "SELECT val FROM test_table ORDER BY id DESC LIMIT 1;"

If the replica is lagging, the returned timestamp may be a few seconds behind. You can quantify lag with Cloud Monitoring:

# Query the replication_lag metric (requires monitoring.viewer role)
gcloud monitoring read "\
    metric.type="cloudsql.googleapis.com/database/replication_lag" \
    AND resource.type="cloudsql_database" \
    AND resource.label.database_id=$(gcloud sql instances describe $REPLICA_NAME --format="value(connectionName)")" \
    --project=$PROJECT_ID --interval=5m

The metric returns lag in seconds; a value under 1‑2 seconds is typical for modest write throughput.

Section 3: Trade‑offs and limitations

• Replication lag: The replica is eventually consistent. Applications that require read‑after‑write consistency must either read from the primary or implement application‑level retry logic.
• Manual scaling: Read replicas do not auto‑scale compute. If the replica’s workload exceeds its provisioned machine type, you must manually upgrade the tier (gcloud sql instances patch $REPLICA_NAME --tier=db-custom-4-7680).
• Administrative operations: Changing certain database flags on the primary requires a restart of the replica, causing a brief read interruption. Plan such changes during maintenance windows.
• Cost: You pay for the replica’s compute and storage as if it were a standalone instance. Network egress within the same region is free, but cross‑region egress incurs standard charges.

Actionable closing

If your workload exhibits a high ratio of reads to writes, start by provisioning a read replica in the same region as your primary. Verify replication state and lag, shift read‑only traffic, and monitor both CPU utilization on the primary and the replica’s lag metric. Adjust the replica’s machine type as needed, and consider promoting it to a primary for regional failover or migration when the time comes. This pattern gives you a clear, observable way to scale read performance without over‑provisioning your primary instance.