Sizing Cloud SQL max_connections by Instance Tier

This guide is part of GCP Cloud SQL Connection Pooling. Cloud SQL derives a default max_connections from the instance’s memory, and the number is both smaller than most teams expect and easy to change in a way that makes things worse. Raising it removes the immediate error and moves the failure into memory pressure, where it is far harder to diagnose:

FATAL:  remaining connection slots are reserved for non-replication superuser connections
# after raising max_connections:
server closed the connection unexpectedly
FATAL:  the database system is in recovery mode

The second pair is what an out-of-memory event looks like from the client. The objective is a max_connections chosen from the instance’s memory and workload, divided deliberately across the services that connect, and changed with an understanding of what it costs.

Rapid incident diagnosis

Read the effective limit and the current usage. The flag and the live count answer different questions:

SHOW max_connections;
SELECT count(*) AS total,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity WHERE backend_type = 'client backend';

From Cloud Monitoring, the same numbers over time:

gcloud monitoring time-series list \
  --filter='metric.type="cloudsql.googleapis.com/database/postgresql/num_backends" AND resource.labels.database_id="my-project:orders-pg"' \
  --interval-start-time="$(date -u -d '-1 hour' +%FT%TZ)" --format="value(points[0].value.int64Value)"

Identify who holds the connections. With application_name set by each service, the split is immediate:

SELECT application_name, count(*), count(*) FILTER (WHERE state = 'idle') AS idle
FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;

A large idle share means pool minimums, not workload, are consuming the limit.

Check memory headroom before considering a raise. Each backend has a private memory cost, and Cloud SQL’s memory metric shows whether there is room:

gcloud monitoring time-series list \
  --filter='metric.type="cloudsql.googleapis.com/database/memory/components" AND resource.labels.database_id="my-project:orders-pg"' \
  --interval-start-time="$(date -u -d '-30 min' +%FT%TZ)" --format="value(metric.labels.component, points[0].value.doubleValue)"

Confirm the reserved slots. PostgreSQL holds back superuser_reserved_connections, and Cloud SQL itself keeps connections for management and replication, so the number available to applications is lower than the flag suggests.

Mathematical sizing or parameter formula

Memory is the real ceiling. Each PostgreSQL backend uses a baseline of private memory plus whatever its queries allocate:

per_backend_memory ≈ baseline (a few MB) + work_mem × sorts_per_query + temp_buffers
usable_for_backends = instance_memory − shared_buffers − os_and_platform_overhead
max_connections_memory ≈ usable_for_backends ÷ per_backend_worst_case

On a 16 GB instance with shared buffers around 25 % and 2 GB of overhead, roughly 10 GB remains. At a conservative 10 MB per backend under load, that is about 1,000 — far above the default. But work_mem is per sort, not per connection: a query with three sorts at 16 MB uses 48 MB, and 200 such queries at once would exhaust the instance. The practical ceiling is therefore set by concurrent memory-hungry queries, not by idle connections.

Concurrency is the other ceiling. Beyond a few times the vCPU count of actively running queries, added concurrency reduces throughput:

useful_active_backends ≈ vcpus × 2 to 4

A 4-vCPU instance cannot usefully run more than about 16 queries at once, whatever the connection count.

Deriving the number. A limit should cover idle pool connections plus a modest multiple of the useful active count:

max_connections ≈ Σ pool minimums across the fleet + peak_active × 1.5 + reserved + operator headroom

With 12 services holding 4 idle connections each (48), a peak of 16 active queries (24 with margin), 3 reserved and 10 for operators and tools: about 85. Rounding to 100 leaves room to grow, and is far below what the memory would allow — which is the point: the flag should express the workload, not the memory.

When the fleet outgrows it, the answer is a pooler rather than a larger flag. Cloud SQL’s managed pooling, or PgBouncer on a small instance, decouples the client count from the backend count entirely.

Exact remediation & configuration

Change the flag deliberately, and expect a restart:

gcloud sql instances patch orders-pg --database-flags=max_connections=100
# note: patching database flags replaces the whole set — include every flag you already use
gcloud sql instances describe orders-pg --format="value(settings.databaseFlags)"

Two operational cautions: gcloud sql instances patch --database-flags replaces all flags rather than merging, so the command must list every flag the instance already has; and changing max_connections restarts the instance, which is a brief outage for every connected service.

Divide the result explicitly. A short table per instance, kept with the infrastructure code, is what prevents the next service from taking someone else’s share:

Keep pool minimums small. Idle connections consume the same slot as busy ones; across a fleet they are usually the largest single consumer:

engine = create_engine(DATABASE_URL, pool_size=6, max_overflow=2, pool_timeout=5, pool_recycle=1800)

Set an alert well before the limit. At 80 % there is still time to act:

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

Reserve capacity for operators. A database at its limit cannot be investigated. PostgreSQL 16 and later support reserved_connections with a grantable role, which keeps slots for monitoring and humans:

ALTER SYSTEM SET reserved_connections = 5;     -- where the flag is available in Cloud SQL
GRANT pg_use_reserved_connections TO ops_role;

Where the flag cannot be set, the same effect comes from budgeting the 10 operator connections above and never allocating them to a service.

Prefer a pooler to a bigger number. If the division cannot be made to fit, the fleet is too large for direct connections, and the fix is architectural — Cloud SQL’s managed connection pooling on supported editions, or PgBouncer between the services and the instance, as compared in PgBouncer vs RDS Proxy vs pgpool-II.

Reviewing the limit as the fleet changes

Re-derive after every scaling change. Adding replicas to a service, raising an autoscaler’s maximum, or adding a worker type all change the claim table. The review is arithmetic, and it belongs in the same pull request as the scaling change.

Watch the idle share. A rising idle share means pool minimums are growing faster than the workload:

SELECT round(100.0 * count(*) FILTER (WHERE state = 'idle') / count(*), 1) AS idle_pct
FROM pg_stat_activity WHERE backend_type = 'client backend';

Above 60 % at peak, the fleet is reserving more than it uses, and lowering minimums returns slots without any capacity loss.

Watch memory alongside connections. A limit raised without a memory check shows up as growing memory usage and, eventually, an out-of-memory restart. The two metrics belong on the same dashboard:

num_backends          — how many connections exist
memory/components     — how much the instance is using
cpu/utilization       — whether more concurrency would help at all

Re-check after an instance resize. Changing the machine type changes both the memory ceiling and the useful concurrency, so the derived limit changes with it — upward for a larger instance, and critically downward for a smaller one.

Distinguish read replicas. Each replica has its own max_connections and its own claim table; a read-heavy service may hold more connections on a replica than on the primary.

Validation & verification

The claim table matches reality. At peak, the per-service session counts should match the table within a few connections:

SELECT application_name, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;

Headroom survives a deploy. Roll every service in sequence while sampling num_backends; the peak must stay below the limit with the operator reserve intact.

An operator can always connect. During the deploy test, open a psql session from an operator account; it must succeed.

Memory does not track connections. After any increase, watch memory for a full traffic cycle. A rise that follows connection count rather than query volume means the per-backend cost is larger than assumed — usually a generous work_mem.

Alerts fire before the limit. Force the count above 80 in staging and confirm the alert fires and resolves.

Frequently Asked Questions

Related

• GCP Cloud SQL Connection Pooling — the parent guide to Cloud SQL connection paths.
• Configuring Cloud SQL Auth Proxy Connection Limits — bounding what the proxy layer can open.
• Using the Cloud SQL Python Connector with SQLAlchemy — sizing one service’s pool against this limit.
• Tuning PostgreSQL max_connections and superuser_reserved_connections — the same decision on self-managed PostgreSQL.