Using the Cloud SQL Python Connector with SQLAlchemy

This guide is part of GCP Cloud SQL Connection Pooling. The Cloud SQL Python Connector replaces the Auth Proxy sidecar with a library: it fetches the instance’s connection details and an ephemeral client certificate from the Cloud SQL Admin API, opens a TLS connection straight to the instance, and hands SQLAlchemy a ready DB-API connection. It does not pool. SQLAlchemy does, through a creator function that calls the connector each time the pool needs a new physical connection. Mixing up those two roles is the source of most problems — a Connector created per request exhausts Admin API quota, a pool with no recycle limit keeps connections through instance maintenance, and a Cloud Run service that scales to many instances multiplies every pool:

google.api_core.exceptions.TooManyRequests: 429 Quota exceeded for quota metric 'Connect Queries'
and limit 'Connect Queries per minute per user' of service 'sqladmin.googleapis.com'
sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 2 reached, connection timed out, timeout 30.00

The objective is one long-lived Connector per process, a SQLAlchemy pool that owns connection reuse, and a pool size that keeps the whole service inside the instance’s max_connections.

Rapid incident diagnosis

Find where Connector is constructed. It should appear once, at module or application startup:

grep -rn "Connector(" --include=*.py . | grep -v "import"
grep -rn "create_async_connector\|connector.connect" --include=*.py .

A Connector() inside a request handler, a get_db() dependency or the creator function itself means a new connector — and new Admin API calls — for every connection or request.

Check Admin API usage. The API’s metrics show request rates by method. A rate that scales with traffic rather than staying flat is the per-request connector pattern:

gcloud monitoring time-series list \
  --filter='metric.type="serviceruntime.googleapis.com/api/request_count" AND resource.labels.service="sqladmin.googleapis.com"' \
  --interval-start-time="$(date -u -d '-1 hour' +%FT%TZ)" --format="value(metric.labels.method, points[0].value.int64Value)"

Check the pool. QueuePool limit ... reached is SQLAlchemy’s own acquisition timeout. Log the pool state periodically to see whether it is saturated or leaking:

import logging
logging.getLogger("sqlalchemy.pool").setLevel(logging.INFO)
print(engine.pool.status())
# Pool size: 5  Connections in pool: 0 Current Overflow: 2 Current Checked out connections: 7

Check the instance. Cloud SQL for PostgreSQL exposes the backend count as cloudsql.googleapis.com/database/postgresql/num_backends; compare it with the instance’s max_connections flag and with max_instances × (pool_size + max_overflow) for every service that connects.

• Connections checked out at the limit, backends mostly active: pool too small or queries slow.
• Checked out at the limit, backends idle in transaction: sessions not closed — a missing session.close() or a long transaction.
• Backends near max_connections while each pool is below its limit: too many instances or services for the instance’s ceiling.

Mathematical sizing or parameter formula

Per instance of the service. SQLAlchemy’s QueuePool holds up to pool_size connections and opens up to max_overflow more under burst, closing overflow connections when they are returned:

max_connections_per_process = pool_size + max_overflow

Size pool_size from concurrent database work in one process. For a Cloud Run service with concurrency = 40 requests per container and 20 % of request time spent in the database, about 8 connections are busy at full concurrency; pool_size = 6 with max_overflow = 4 covers bursts without holding 10 connections idle.

Across the service. Cloud Run and GKE autoscaling multiply the per-process maximum:

service_connections = max_instances × workers_per_instance × (pool_size + max_overflow)

With max_instances = 30, two Uvicorn worker processes per container, and 10 connections per process: 30 × 2 × 10 = 600. A Cloud SQL for PostgreSQL instance with 7.5 GB of memory has a default max_connections in the hundreds — too low. Either lower max_instances, lower the per-process maximum, or raise max_connections on a larger tier.

Admin API calls. A shared Connector refreshes each instance’s certificate roughly once an hour in the background, so API usage is independent of traffic:

api_calls_per_hour ≈ processes × instances_connected × refreshes_per_hour × calls_per_refresh

A connector per request instead makes calls proportional to request rate: at 50 requests per second, several Admin API calls per request quickly exceed a per-minute quota measured in the hundreds or low thousands.

Exact remediation & configuration

One connector, one engine, created at startup. For a synchronous application with the pg8000 driver and IAM database authentication:

# db.py
import os
import sqlalchemy
from google.cloud.sql.connector import Connector, IPTypes

INSTANCE = os.environ["INSTANCE_CONNECTION_NAME"]          # project:region:instance

connector = Connector(refresh_strategy="lazy")             # lazy suits CPU-throttled Cloud Run

def getconn():
    return connector.connect(
        INSTANCE,
        "pg8000",
        user=os.environ["DB_IAM_USER"],                    # service account name without .gserviceaccount.com
        db="orders",
        enable_iam_auth=True,
        ip_type=IPTypes.PRIVATE,
    )

engine = sqlalchemy.create_engine(
    "postgresql+pg8000://",
    creator=getconn,
    pool_size=6,
    max_overflow=4,
    pool_timeout=10,          # seconds to wait for a connection; below the request deadline
    pool_recycle=1800,        # replace connections every 30 minutes
    pool_pre_ping=True,       # detect connections broken by maintenance or failover
)

refresh_strategy="lazy" fetches certificates when a connection is needed rather than on a background timer, which matters on Cloud Run when CPU is only allocated during requests; the default background refresh suits always-on CPU.

The async variant uses asyncpg and must create the connector inside the running event loop:

from contextlib import asynccontextmanager
from fastapi import FastAPI
from google.cloud.sql.connector import create_async_connector
from sqlalchemy.ext.asyncio import create_async_engine

@asynccontextmanager
async def lifespan(app: FastAPI):
    connector = await create_async_connector()

    async def getconn():
        return await connector.connect_async(
            INSTANCE, "asyncpg", user=IAM_USER, db="orders", enable_iam_auth=True)

    app.state.engine = create_async_engine(
        "postgresql+asyncpg://", async_creator=getconn,
        pool_size=6, max_overflow=4, pool_timeout=10, pool_recycle=1800, pool_pre_ping=True)
    yield
    await app.state.engine.dispose()     # close pooled connections first
    await connector.close_async()        # then stop certificate refresh

app = FastAPI(lifespan=lifespan)

Close in the right order on shutdown. Dispose of the engine before closing the connector, so pooled connections close cleanly while the connector still exists. For the synchronous version, register both with atexit or the framework’s shutdown hook.

Bound the service.

gcloud run services update orders-api --max-instances=18 --concurrency=40
gcloud sql instances patch orders-pg --database-flags=max_connections=400

Changing max_connections restarts the instance; schedule it. Session scoping in FastAPI — making sure every request’s session is closed so connections return to the pool — follows Scoping Async SQLAlchemy Sessions in FastAPI.

Validation & verification

Connector count. In a running container, confirm one connector per process by logging at construction, or by counting background refresh tasks. Admin API request counts should stay flat while request traffic varies.

Pool bounds under load. Run a load test at the service’s maximum instance count and sample backends on the instance:

SELECT usename, application_name, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, state
ORDER BY count(*) DESC;

The service’s total must stay at or below max_instances × workers × (pool_size + max_overflow), and the instance must keep free slots.

Maintenance and failover. Trigger a failover on a staging high-availability instance and confirm requests recover without restarts:

gcloud sql instances failover orders-pg-staging

With pool_pre_ping=True, broken connections are detected at checkout and replaced through creator; expect a short burst of retried requests, not a sustained error rate.

Certificate refresh. Leave a staging instance idle for more than an hour, then send a request. It must succeed without authentication errors, showing that refresh — background or lazy — produced a valid certificate for the new connection.

Managed Connection Pooling and connection limits on Cloud SQL

Cloud SQL for PostgreSQL offers a built-in managed connection pooler on supported editions, which multiplexes client connections onto fewer server connections in the same way as PgBouncer. When it is enabled, the arithmetic changes: the application’s pools become clients of the pooler, and the instance’s max_connections bounds only the pooler’s server-side connections.

That makes the sizing question two separate questions.

client_side:  max_instances × workers × (pool_size + max_overflow)  ≤  pooler client limit
server_side:  pooler server pool size                              ≤  max_connections − reserved

Transaction pooling brings the usual caveats. Session state set on a connection — SET statements, advisory locks, temporary tables — does not persist across transactions. With SQLAlchemy and the Python connector, keep sessions stateless: set per-transaction options with SET LOCAL inside the transaction, and avoid prepared statement caching in drivers that use server-side prepares. With pg8000, which uses unnamed prepared statements by default, this is usually not an issue; with asyncpg, disable its statement cache when connecting through a transaction-mode pooler:

async def getconn():
    return await connector.connect_async(
        INSTANCE, "asyncpg", user=IAM_USER, db="orders", enable_iam_auth=True,
        statement_cache_size=0,          # required behind transaction pooling
    )

Whether the pooler is used or not, check three numbers before each scaling change:

Cloud Monitoring’s cloudsql.googleapis.com/database/postgresql/num_backends gives the third number directly. An alert at 80 % of max_connections gives time to act before new connections — from any service, including the connector’s own new connections after a failover — are refused:

gcloud alpha monitoring policies create --notification-channels="$CHANNEL" \
  --display-name="orders-pg backends above 80%" \
  --condition-display-name="num_backends high" \
  --condition-filter='metric.type="cloudsql.googleapis.com/database/postgresql/num_backends" AND resource.labels.database_id="my-project:orders-pg"' \
  --condition-threshold-value=320 --condition-threshold-duration=300s

Frequently Asked Questions

Related

• GCP Cloud SQL Connection Pooling — the parent guide to Cloud SQL connection ceilings and connection paths.
• Using the Cloud SQL Go Connector with database/sql — the same pattern in Go.
• Tuning SQLAlchemy pool_size and max_overflow — the pool parameters in depth.
• Managing Connection Limits in Cloud Run and Cloud Functions — instance scaling limits that bound total connections.