Scaling Cloud SQL: From Prototype to Production

Scaling Cloud SQL: From Prototype to Production

Google Cloud SQL is a fully-managed relational database service that supports MySQL, PostgreSQL, and SQL Server. While getting started with Cloud SQL is straightforward—often just a few clicks in the console or a single Terraform module—moving from a prototype database to a production-ready, scalable deployment requires careful planning around instance sizing, high availability, connection management, read replicas, and observability. This tutorial walks through the full journey, giving you practical configurations and code samples you can apply directly.

Why Scaling Cloud SQL Matters

In the prototype phase, a single small Cloud SQL instance is usually sufficient. You might have a handful of users, simple queries, and tolerant latency requirements. But as your application grows, several pressure points emerge:

• Connection exhaustion: Each database connection consumes memory on the instance. Without pooling, a spike in application traffic can exhaust the max_connections limit.
• CPU and memory saturation: Complex queries or high read volume can peg CPU, causing query latency to spike.
• Storage growth: Datasets grow over time, and IOPS requirements increase with them.
• Availability requirements: A single-zone instance is a single point of failure. Production workloads typically demand 99.95%+ uptime.
• Read scalability: When read-heavy workloads dominate, a single primary instance becomes a bottleneck.

Addressing these concerns proactively—rather than reactively during an incident—is the difference between a smooth production launch and a painful one.

Understanding Cloud SQL Architecture

Before diving into configuration, it helps to understand the building blocks Cloud SQL provides for scaling:

• Primary instance: The read-write instance that accepts all writes and serves as the source of truth.
• Read replicas: Asynchronously replicated copies of the primary that serve read traffic. They can be in the same region or cross-region.
• High Availability (HA) configuration: A standby instance in a different zone within the same region, with synchronous replication. If the primary fails, Cloud SQL automatically fails over.
• Cloud SQL Auth Proxy / Connector: A secure, identity-aware way to connect to Cloud SQL without managing SSL certificates or exposing public IPs.
• Connection pooling via PgBouncer / ProxySQL / Cloud SQL connectors: Reduces the number of direct database connections.

Step 1: Provisioning a Production-Grade Instance

Let's start by provisioning a Cloud SQL PostgreSQL instance configured for production using Terraform. This configuration includes HA, automated backups, private IP, and appropriate machine sizing.

# main.tf
resource "google_sql_database_instance" "primary" {
  name             = "prod-db-primary"
  database_version = "POSTGRES_15"
  region           = "us-central1"

  settings {
    tier              = "db-custom-8-30720"  # 8 vCPUs, 30GB RAM
    availability_type = "REGIONAL"            # Enables HA with standby in another zone
    disk_size         = 500
    disk_type         = "PD_SSD"
    disk_autoresize   = true
    disk_autoresize_limit = 1000

    backup_configuration {
      enabled                        = true
      start_time                     = "03:00"
      point_in_time_recovery_enabled = true
      transaction_log_retention_days = 7
    }

    ip_configuration {
      ipv4_enabled    = false
      private_network = "projects/my-project/global/networks/my-vpc"
      require_ssl     = true
    }

    maintenance_window {
      day          = 7  # Sunday
      hour         = 4
      update_track = "stable"
    }

    database_flags {
      name  = "max_connections"
      value = "500"
    }

    database_flags {
      name  = "log_min_duration_statement"
      value = "1000"  # Log queries slower than 1 second
    }

    database_flags {
      name  = "shared_buffers"
      value = "7864320"  # ~7.5GB (25% of RAM in 8KB pages)
    }
  }

  lifecycle {
    prevent_destroy = true
  }
}

Key decisions in this configuration:

• availability_type = "REGIONAL" enables HA by maintaining a standby instance in a second zone with synchronous replication.
• ipv4_enabled = false combined with private_network ensures the database is only reachable from within your VPC, not the public internet.
• point_in_time_recovery_enabled = true enables PITR, allowing you to restore to any point within the retention window.
• prevent_destroy prevents accidental deletion of the production database.
• The shared_buffers flag is set to roughly 25% of total RAM, following PostgreSQL best practices.

Step 2: Adding Read Replicas for Horizontal Scaling

Once your primary instance is provisioned, the next step is to offload read traffic to replicas. Most web applications are read-heavy (often 80-90% reads), so read replicas provide significant headroom.

# replicas.tf
resource "google_sql_database_instance" "read_replica_1" {
  name             = "prod-db-replica-1"
  database_version = "POSTGRES_15"
  region           = "us-central1"
  master_instance_name = google_sql_database_instance.primary.name

  settings {
    tier              = "db-custom-8-30720"
    availability_type = "ZONAL"
    disk_size         = 500
    disk_type         = "PD_SSD"
    disk_autoresize   = true

    ip_configuration {
      ipv4_enabled    = false
      private_network = "projects/my-project/global/networks/my-vpc"
      require_ssl     = true
    }

    database_flags {
      name  = "hot_standby"
      value = "on"
    }

    database_flags {
      name  = "max_connections"
      value = "500"
    }
  }
}

resource "google_sql_database_instance" "cross_region_replica" {
  name             = "prod-db-replica-eu"
  database_version = "POSTGRES_15"
  region           = "europe-west1"
  master_instance_name = google_sql_database_instance.primary.name

  settings {
    tier              = "db-custom-8-30720"
    availability_type = "ZONAL"
    disk_size         = 500
    disk_type         = "PD_SSD"

    ip_configuration {
      ipv4_enabled    = false
      private_network = "projects/my-project/global/networks/my-vpc"
    }
  }
}

The cross-region replica serves two purposes: it provides a read endpoint closer to European users (reducing latency), and it can be promoted to a standalone primary in a disaster recovery scenario.

Step 3: Connection Management with Cloud SQL Connector

One of the most common production issues is connection exhaustion. Each connection to PostgreSQL consumes memory, and opening a new connection for every request is expensive. The Cloud SQL Connector for Python (or its equivalents for other languages) handles authentication, encryption, and connection lifecycle automatically.

# app/database.py
from google.cloud.sql.connector import Connector, IPTypes
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

# Initialize Connector
connector = Connector()

def getconn():
    conn = connector.connect(
        "my-project:us-central1:prod-db-primary",
        "pg8000",
        user="app_user",
        password="secure_password_from_secret_manager",
        db="appdb",
        ip_type=IPTypes.PRIVATE,
    )
    return conn

# Create engine with connection pooling
engine = create_engine(
    "postgresql+pg8000://",
    creator=getconn,
    poolclass=QueuePool,
    pool_size=20,
    max_overflow=10,
    pool_pre_ping=True,
    pool_recycle=1800,  # Recycle connections every 30 minutes
)

# Usage in application code
from sqlalchemy import text

def get_user(user_id: int):
    with engine.connect() as conn:
        result = conn.execute(
            text("SELECT id, email, name FROM users WHERE id = :uid"),
            {"uid": user_id}
        )
        return result.fetchone()

For even more aggressive connection pooling—especially with serverless platforms like Cloud Run or Cloud Functions where many short-lived instances may each open connections—you should add PgBouncer as a connection pooler in front of Cloud SQL. PgBouncer runs as a sidecar or a separate Compute Engine instance and multiplexes many application connections onto a smaller number of database connections.

# pgbouncer.ini
[databases]
prod-db = host=10.0.0.3 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 100
server_idle_timeout = 240
query_wait_timeout = 120

The pool_mode = transaction setting is critical: it means connections are returned to the pool after each transaction rather than each session, dramatically improving connection reuse.

Step 4: Routing Reads to Replicas

With read replicas in place, your application needs logic to route read queries to replicas and write queries to the primary. Here's a pattern using SQLAlchemy with multiple engines:

# app/multi_db.py
from google.cloud.sql.connector import Connector, IPTypes
from sqlalchemy import create_engine, text
from sqlalchemy.pool import QueuePool
from contextlib import contextmanager
import random

connector = Connector()

def make_creator(instance_name, db="appdb"):
    def getconn():
        return connector.connect(
            instance_name,
            "pg8000",
            user="app_user",
            password="secure_password",
            db=db,
            ip_type=IPTypes.PRIVATE,
        )
    return getconn

# Primary engine for writes
primary_engine = create_engine(
    "postgresql+pg8000://",
    creator=make_creator("my-project:us-central1:prod-db-primary"),
    poolclass=QueuePool,
    pool_size=10,
    max_overflow=5,
    pool_pre_ping=True,
)

# Replica engines for reads
replica_engines = [
    create_engine(
        "postgresql+pg8000://",
        creator=make_creator("my-project:us-central1:prod-db-replica-1"),
        poolclass=QueuePool,
        pool_size=15,
        max_overflow=10,
        pool_pre_ping=True,
    ),
    create_engine(
        "postgresql+pg8000://",
        creator=make_creator("my-project:us-central1:prod-db-replica-2"),
        poolclass=QueuePool,
        pool_size=15,
        max_overflow=10,
        pool_pre_ping=True,
    ),
]

@contextmanager
def read_db():
    """Yields a connection from a randomly selected replica."""
    engine = random.choice(replica_engines)
    with engine.connect() as conn:
        yield conn

@contextmanager
def write_db():
    """Yields a connection from the primary for read-write operations."""
    with primary_engine.begin() as conn:
        yield conn

# Example usage
def get_product_catalog(category: str):
    with read_db() as conn:
        return conn.execute(
            text("SELECT * FROM products WHERE category = :cat"),
            {"cat": category}
        ).fetchall()

def create_order(user_id: int, items: list):
    with write_db() as conn:
        result = conn.execute(
            text("INSERT INTO orders (user_id, status) VALUES (:uid, 'pending') RETURNING id"),
            {"uid": user_id}
        )
        order_id = result.fetchone()[0]
        for item in items:
            conn.execute(
                text("INSERT INTO order_items (order_id, product_id, qty) VALUES (:oid, :pid, :qty)"),
                {"oid": order_id, "pid": item["product_id"], "qty": item["qty"]}
            )
        return order_id

Be aware that read replicas use asynchronous replication, so there is replication lag. If your application reads immediately after writing and requires read-your-writes consistency, route that specific read to the primary. A common pattern is to use the primary for reads within a short window (e.g., 2-5 seconds) after a write by the same user.

Step 5: Monitoring and Observability

Production databases require robust monitoring. Cloud SQL integrates with Cloud Monitoring, and you should set up alerts for critical metrics. Here's how to configure alerting policies using Terraform:

# monitoring.tf

# Alert: High CPU utilization
resource "google_monitoring_alert_policy" "sql_cpu_high" {
  display_name = "Cloud SQL - High CPU"
  combiner     = "OR"

  conditions {
    display_name = "CPU > 80% for 5 minutes"
    condition_threshold {
      filter = <<-EOT
        resource.type="cloudsql_database" AND
        resource.label.database_id="my-project:prod-db-primary" AND
        metric.type="cloudsql.googleapis.com/database/cpu/utilization"
      EOT
      duration        = "300s"
      comparison      = "COMPARISON_GT"
      threshold_value = 0.8
      aggregations {
        alignment_period   = "60s"
        per_series_aligner = "ALIGN_MEAN"
      }
    }
  }

  notification_channels = [google_monitoring_notification_channel.email.id]
  alert_strategy {
    auto_close = "1800s"
  }
}

# Alert: Replication lag on replicas
resource "google_monitoring_alert_policy" "sql_replication_lag" {
  display_name = "Cloud SQL - High Replication Lag"
  combiner     = "OR"

  conditions {
    display_name = "Replication lag > 30 seconds"
    condition_threshold {
      filter = <<-EOT
        resource.type="cloudsql_database" AND
        metric.type="cloudsql.googleapis.com/database/replication/replica_lag"
      EOT
      duration        = "120s"
      comparison      = "COMPARISON_GT"
      threshold_value = 30
      aggregations {
        alignment_period   = "60s"
        per_series_aligner = "ALIGN_MAX"
      }
    }
  }

  notification_channels = [google_monitoring_notification_channel.email.id]
}

# Alert: Connection count approaching limit
resource "google_monitoring_alert_policy" "sql_connections_high" {
  display_name = "Cloud SQL - Connection Count High"
  combiner     = "OR"

  conditions {
    display_name = "Active connections > 80% of max"
    condition_threshold {
      filter = <<-EOT
        resource.type="cloudsql_database" AND
        resource.label.database_id="my-project:prod-db-primary" AND
        metric.type="cloudsql.googleapis.com/database/postgresql/num_backends"
      EOT
      duration        = "180s"
      comparison      = "COMPARISON_GT"
      threshold_value = 400  # 80% of max_connections=500
      aggregations {
        alignment_period   = "60s"
        per_series_aligner = "ALIGN_MEAN"
      }
    }
  }

  notification_channels = [google_monitoring_notification_channel.email.id]
}

resource "google_monitoring_notification_channel" "email" {
  display_name = "DBA Team Email"
  type         = "email"
  labels = {
    email_address = "dba-team@my-company.com"
  }
}

Beyond Cloud Monitoring, you should also enable query-level observability. The log_min_duration_statement flag we set earlier logs slow queries to Cloud Logging. You can query these logs to identify performance bottlenecks:

# Query slow logs from Cloud Logging using gcloud
gcloud logging read '
  resource.type="cloudsql_database" AND
  resource.label.database_id="my-project:prod-db-primary" AND
  jsonPayload.message=~"duration:.*ms.*statement:"
' --limit=50 --format=json | jq '.[].jsonPayload.message'

Step 6: Vertical Scaling and Instance Upgrades

When vertical scaling is needed—either because CPU is consistently high or memory is constrained—Cloud SQL allows you to change the machine type with minimal downtime. For HA instances, this involves a failover to the standby during the reconfiguration. Here's how to perform an upgrade programmatically:

# scale_up.py
from google.cloud import sql_v1

client = sql_v1.SqlInstancesClient()
project = "my-project"
instance = "prod-db-primary"

# Get current settings
current = client.get(project=project, instance=instance)

# Prepare updated settings with a larger machine type
settings = current.settings
settings.tier = "db-custom-16-61440"  # 16 vCPUs, 60GB RAM

# Apply the update
operation = client.patch(
    project=project,
    instance=instance,
    sql_instances_update_request_body=sql_v1.SqlInstancesUpdateRequest(
        settings=settings
    )
)

# Wait for the operation to complete
operation.result()  # Blocks until done
print(f"Instance {instance} scaled to {settings.tier}")

For zero-downtime vertical scaling, a more advanced approach is to create a new, larger instance as a read replica of the current primary, let it catch up, then promote it and update your application's connection strings. This approach avoids the brief downtime of an in-place upgrade.

Step 7: Index Optimization and Query Performance

Scaling isn't only about adding resources—query optimization often yields the biggest improvements. Use the pg_stat_statements extension to identify your most expensive queries:

-- Enable the extension (run once)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the top 10 queries by total execution time
SELECT
    queryid,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    rows,
    left(query, 120) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Find queries with high I/O (sequential scans on large tables)
SELECT
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND n_live_tup > 10000
ORDER BY seq_tup_read DESC;

-- Reset statistics after making changes
SELECT pg_stat_statements_reset();

Once you identify problematic queries, use EXPLAIN ANALYZE to understand their execution plans and add appropriate indexes:

-- Analyze a slow query
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE user_id = 12345
  AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 50;

-- Add a composite index to support the query pattern
CREATE INDEX CONCURRENCY idx_orders_user_created
ON orders (user_id, created_at DESC);

-- For partial indexes when you only query a subset
CREATE INDEX CONCURRENCY idx_orders_pending
ON orders (user_id)
WHERE status = 'pending';

Always use CREATE INDEX CONCURRENCY in production to avoid locking the table during index creation. Note that this requires more time and temporary disk space.

Best Practices Summary

• Start with HA from day one. Enabling availability_type = "REGIONAL" on your primary is much easier during provisioning than converting later. The cost premium is roughly 2x, but the availability guarantee is worth it for production workloads.
• Use private IP exclusively. Never expose a production database on a public IP. Use Private Service Access and the Cloud SQL Connector for secure connectivity.
• Always use connection pooling. Whether through the Cloud SQL Connector's built-in pooling, PgBouncer, or your ORM's pool, never open a new connection per request. Size your pool based on your max_connections and the number of application instances.
• Monitor replication lag. If lag grows consistently, your replicas can't keep up with write volume. This may indicate you need more replica capacity or that you should reduce write volume through batching.
• Enable PITR and test restores. Point-in-time recovery is only useful if you've tested restoring from it. Run quarterly restore drills to a temporary instance.
• Right-size your instances. Use Cloud Monitoring data to identify over-provisioned instances. A db-custom-16-61440 running at 15% CPU might be better served by a db-custom-8-30720, saving significant cost.
• Use maintenance windows wisely. Schedule maintenance during low-traffic periods and use the "stable" update track to avoid surprise disruptions.
• Tag and label everything. Apply labels like environment=production, team=backend, and cost-center=engineering to track spending and enforce policies.
• Implement graceful degradation. If replicas become unavailable, your application should fall back to the primary for reads rather than returning errors to users.
• Regularly review slow query logs. Set up a weekly review of the slowest queries and proactively optimize them before they become incidents.

Conclusion

Scaling Cloud SQL from a prototype to a production system is a multi-faceted effort that touches infrastructure provisioning, connection management, read scaling, monitoring, and query optimization. By starting with a well-configured HA instance on private IP, adding read replicas to distribute load, implementing proper connection pooling, and establishing comprehensive monitoring and alerting, you create a database layer that can grow with your application. The key insight is that scaling is not a one-time event but an ongoing process: continuously monitor your metrics, review slow queries, right-size your instances, and test your disaster recovery procedures. With the configurations and patterns outlined in this tutorial, you have a solid foundation for running Cloud SQL in production with confidence.