Connection pooling when every service is tiny
Twelve small Cloud Run services, each with a sensible pool, can still exhaust one Postgres. How to do the connection math and pick a pooler.
Take twelve small services on Cloud Run, all talking to one Cloud SQL for PostgreSQL instance. Each has a connection pool, each pool is a modest size, and each service passes its own load test. Nobody has done anything wrong, and the system can still fail with sorry, too many clients already, because the limit that matters belongs to the database and the multiplier belongs to the autoscaler. Neither lives in any one repository.
This article designs the connection budget for that system on paper: what the worst case is, how small the pools can be, how to write the budget down so it stays true, and when a pooler stops being optional. The numbers are a hypothetical instance, but the arithmetic and the failure modes apply to any single Postgres behind an autoscaling platform. My own services, for a large-scale social platform I'm building, talk to a graph database and a document store rather than Postgres, and I have met the same shape of problem there: connection limits that ignore how tidy the architecture diagram is.
Twelve services, one ceiling, and a worst case of 2,400#
Postgres gives each client connection its own backend process. The ceiling is max_connections, which is 100 by default in the PostgreSQL configuration and needs a restart to change. superuser_reserved_connections (default 3) is held back from that number, so ordinary roles see a little less than the headline figure. On Cloud SQL the default depends on the machine type and the quotas and limits page is the reference, so ask the instance rather than remembering a value:
SHOW max_connections;
SHOW superuser_reserved_connections;Demand is a product of three numbers per service.
- Maximum instances. On Cloud Run this is
--max-instances, or the platform default if you never set one. It is the multiplier you control least during a spike. - Pool size per instance. Client library defaults are often generous. The
pgpackage for Node uses a poolmaxof 10 unless you change it. - Overlap. During a deploy, old and new revisions run side by side. Migrations, jobs, a console session and a BI tool also hold connections.
Suppose each of the twelve services may scale to 20 instances and keeps the default pool of 10. The worst case is 12 x 20 x 10 = 2,400 connections, against a database that allows perhaps a few hundred. The platform will let that happen, because it knows nothing about your database. A monolith has one pool and one number to tune. A fleet has twelve pools and twelve multipliers, and the sum is nobody's job until somebody writes it down.
Why a pool of two or three is enough#
Cloud Run's request concurrency defaults to 80 per instance and can go up to 1,000 (see the concurrency documentation). That does not mean an instance needs 80 connections. A connection is held only while a query or transaction runs, not for the whole request. In my own services an instance capped at a few dozen concurrent requests gets a pool in single digits, not a hundred.
Take a request that lasts 200 ms and spends 8 ms inside database transactions. At 40 requests per second, an instance has on average 40 x 0.008 = 0.32 connections busy at any moment. Required connections are roughly the arrival rate of database work multiplied by how long each unit of work holds a connection. For most small services that is well under one.
So a pool of 2 or 3 per instance usually suffices, and a pool of 10 mostly buys headroom against a database you do not have. When the pool is smaller than the number of concurrent requests, the extra requests wait in the client for a free connection. That wait is far cheaper than a new backend process on the server, and it is where a slow query first shows up as latency rather than as an outage.
The wait needs a bound. In pg the default connectionTimeoutMillis is 0, which means no timeout: a request can queue behind a stuck pool forever. Set it below the request deadline, so an exhausted pool returns an error quickly and the caller can shed load.
The budget, written as a table#
The budget lives next to the infrastructure code. It has a fixed allowance for everything that is not a service, and one row per service with its instance cap and pool size. For the hypothetical instance with max_connections of 100:
max_connections 100 (read from the instance)
reserved for superusers 3
admin, migrations, BI, console 10
rollout overlap (20% of services) 10
-----------------------------------------
available to services 77
service max-instances pool worst case
orders 5 3 15
search 10 2 20
notifications 4 3 12
nine small services 3 1 9 x 3 = 27
-----------------------------------------
total 74Raising --max-instances for search now means editing one line and seeing the total move from 74 toward 77. The sum is visible, which is the entire point.
Making each service obey its row#
Each service builds one pool at startup from environment values, so the pool size and the instance cap in the budget change together. Two lifetimes are easy to confuse here. With the graph database I use, the driver object owns the connection pool and is expensive to build, so each instance creates it once, lazily on first use, and each request borrows a lightweight session from it and returns it straight away. Building a driver per request is the classic mistake, and it hides in local testing, where per request and per process feel identical, and then shows up in production as connection exhaustion. The opposite lesson applies to the document store: its client library already multiplexes requests over a few long-lived channels, so a hand-rolled pool of clients added file descriptors and confusion rather than throughput. Before I write pooling code I now read what the SDK already does. The Cloud SQL Node.js connector opens IAM-aware, encrypted connections without a proxy process and hands its options to pg.
import pg from "pg";
import { Connector, IpAddressTypes } from "@google-cloud/cloud-sql-connector";
import { env } from "../config/env";
import { logger } from "../logger";
const { Pool } = pg;
const connector = new Connector();
export async function createPool() {
const clientOpts = await connector.getOptions({
instanceConnectionName: env.DB_INSTANCE, // project:region:instance
ipType: IpAddressTypes.PRIVATE,
});
const pool = new Pool({
...clientOpts,
user: env.DB_USER,
password: env.DB_PASSWORD,
database: env.DB_NAME,
application_name: env.SERVICE_NAME, // visible in pg_stat_activity
max: env.DB_POOL_MAX, // 1 to 3, taken from the budget
idleTimeoutMillis: 10_000,
connectionTimeoutMillis: 3_000, // fail fast instead of queueing forever
maxLifetimeSeconds: 1_800, // recycle, so no connection lives for days
});
// An idle client can be killed by the server or the network. Without this
// handler, the error is unhandled and takes the process down.
pool.on("error", (err) => logger.warn({ err }, "idle pg client error"));
const close = async () => {
await pool.end();
connector.close();
};
process.once("SIGTERM", () => void close());
return pool;
}max is the budgeted pool size. connectionTimeoutMillis bounds the queue. maxLifetimeSeconds and idleTimeoutMillis are options of the pg-pool package: the idle timeout defaults to 10 seconds and the lifetime to unlimited. application_name is cheap and invaluable, because it shows which service holds which connections.
Closing the pool on SIGTERM matters on Cloud Run, which signals an instance before stopping it. Without it, the database keeps abandoned sessions until its own timeouts notice. Where the credentials come from is covered in configuration and secrets in multi-service Node apps.
The Cloud Run side of the multiplier goes in the same deploy script as the pool size:
gcloud run deploy orders \
--image="$IMAGE" \
--region=europe-west1 \
--max-instances=5 \
--concurrency=40 \
--set-env-vars=DB_POOL_MAX=3,SERVICE_NAME=ordersWith the pool capped independently, concurrency no longer decides how many connections you open, only how many requests share them. How instance counts interact with startup latency is covered in Cloud Run cold starts.
Checking the budget against the database#
A budget is a prediction. This query shows what is happening, grouped by the application_name set above:
SELECT application_name, state, count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, state
ORDER BY connections DESC;A service with many connections in idle has a pool larger than its work needs. A service with many in idle in transaction has a bug, usually a code path that opens a transaction and then awaits something slow. The server can end such sessions with idle_in_transaction_session_timeout, which defaults to 0 (disabled) and is worth setting to a few tens of seconds for application roles.
When the budget does not fit: choosing a pooler#
Sometimes the arithmetic fails: too many services, or instance caps that cannot be lowered. The options stack, so the table is about where each one applies.
| Approach | What it limits | Cost and moving parts | Main caveat |
|---|---|---|---|
Small pools plus --max-instances | Worst case per service, by arithmetic | None | Needs a shared budget; a large fleet outgrows it |
Raise max_connections and the instance size | The ceiling itself | Bigger tier, more idle memory | Pays for headroom, delays the problem |
| Cloud SQL managed connection pooling | Server connections, via a pool Cloud SQL runs | Enterprise Plus edition, no extra process | Transaction mode limits session features; edition and maintenance-version prerequisites |
| PgBouncer on a VM or GKE | Server connections, via a pool you run | A small always-on machine and its config | Another component to patch, monitor and make highly available |
| PgBouncer sidecar in each instance | Concurrency inside one instance | One extra container per instance | Does not reduce the total across instances |
The order is operational. The budget is a table and a few flags. A pooler is a new failure domain: if it is down, every service is down. So give every service a small pool and a bounded instance cap first, add a pooler in transaction mode only when the sum cannot be made to fit, and raise max_connections last, to what the machine can hold rather than to the autoscaler's theoretical peak.
Why the sidecar does not help#
A pooler cannot be an ordinary Cloud Run service: Cloud Run services accept HTTP and gRPC, and PgBouncer speaks the Postgres wire protocol. A self-run pooler lives on a VM, in GKE, or as a sidecar. The sidecar is the trap. It looks like pooling, but each instance gets its own PgBouncer, so a hundred instances still produce a hundred pools. It solves a per-instance problem you probably did not have, and the total reaching Postgres is unchanged.
Managed pooling on Cloud SQL#
Cloud SQL for PostgreSQL offers a built-in pooler. The managed connection pooling overview lists the prerequisites: the Enterprise Plus edition, the newer Cloud SQL network architecture, a minimum maintenance version, and access either directly (port 6432) or through the Auth Proxy (version 2.15.2 or later). The default pool mode is transaction. Enabling it is an instance patch, and the configuration page documents the flags:
gcloud sql instances patch my-instance --enable-connection-pooling
# Tune the pooler, one setting per flag value
gcloud sql instances patch my-instance \
--connection-pool-flags=max_pool_size=40The tunable settings include max_pool_size, max_client_connections, query_wait_timeout and max_prepared_statements. The documented default for max_prepared_statements is 0, so a driver such as asyncpg, which relies on prepared statements, needs it set above 0 to work through the pooler. Enabling pooling on an existing instance restarts the database, so schedule it.
The Enterprise Plus requirement means the premium applies to the whole instance, not only to the pooler. At the time of writing, the Cloud SQL pricing page lists the editions side by side, and it is worth comparing that difference against the monthly cost of one small VM running PgBouncer plus the time to patch and monitor it. For a handful of services, the budget table costs nothing.
Running PgBouncer yourself#
On a small VM in the same region and VPC as the database, a minimal configuration looks like this. Each setting is in the PgBouncer configuration reference; the comments mark where it departs from the default.
[databases]
app = host=10.20.0.5 port=5432 dbname=app
[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 = 2000 ; default 100, far too low for a fleet
default_pool_size = 20 ; server connections per user/database pair (default 20)
reserve_pool_size = 5 ; default 0 (disabled)
reserve_pool_timeout = 3 ; default 5 seconds
max_db_connections = 40 ; hard cap on server connections to this database
query_wait_timeout = 15 ; default 120 seconds; fail fast instead of queueing for two minutes
server_idle_timeout = 600 ; default
max_prepared_statements = 200 ; default since PgBouncer 1.24.0 (introduced in 1.21.0 with default 0)max_client_conn is how many clients PgBouncer accepts, while default_pool_size and max_db_connections decide how many server connections reach Postgres. That gap is the whole value: thousands of cheap client connections multiplexed onto a few dozen expensive server ones. The 120-second default for query_wait_timeout is a trap, because a client waiting two minutes for a connection is a client whose request was abandoned long ago. Shorten it to something below the request deadline.
In transaction mode, consecutive transactions can run on different server connections, so session state does not survive. That rules out relying on SET outside a transaction, session-level advisory locks, LISTEN and NOTIFY, and SQL-level PREPARE. Use SET LOCAL inside a transaction, transaction-level advisory locks, and a direct connection for anything that listens. Protocol-level prepared statements work through max_prepared_statements (on PgBouncer 1.21.0 or later, with a non-zero value), which is why most modern drivers need no changes. Migrations and schema tools often need session features, so point them at the database directly and keep them in the admin allowance of the budget.
What breaks during a scale-out#
Connection storms. A pool opens connections lazily, as work arrives. When a spike brings 30 new instances up at once, each starts its pool and runs its first queries in the same few seconds, so the database sees a burst of new backends, each paying for TLS, authentication and process start. If the total crosses max_connections, new attempts fail with SQLSTATE 53300, and callers retrying those failures add load. The instance cap bounds the burst, a pooler absorbs it, and a retry with jittered exponential backoff stops a failed batch from becoming a second burst. On my platform the same thing happens when traffic lands on a service that has scaled to zero: the platform starts several instances at once and each opens its database connections together, a thundering herd of my own making. What helped was unglamorous. I capped the maximum instances per service at a number the database's connection budget could absorb in the worst case, kept pools modest, added jitter to retries, and wrote the arithmetic down service by service.
import type { Pool } from "pg";
const TOO_MANY_CONNECTIONS = "53300";
export async function queryWithRetry<T>(pool: Pool, text: string, values: unknown[] = []) {
for (let attempt = 0; ; attempt++) {
try {
return await pool.query(text, values);
} catch (err) {
const retryable = (err as { code?: string }).code === TOO_MANY_CONNECTIONS;
if (!retryable || attempt >= 3) throw err;
const cap = 200 * 2 ** attempt;
await new Promise((r) => setTimeout(r, cap / 2 + Math.random() * (cap / 2)));
}
}
}Retry only this one error class, and only for idempotent work or writes carrying a key, as described in idempotent Pub/Sub consumers. Retrying a non-idempotent write after a timeout is how duplicates are made.
Exhaustion that looks like an application bug. When the database is full, the symptoms appear in the services: timeouts, slow endpoints, health checks that fail because they run a query. Look at pg_stat_activity before looking at code.
Idle connections on throttled instances. With request-based billing, Cloud Run limits CPU between requests, so a pool's idle timers may not fire promptly and a connection can stay open on the server until the instance wakes or stops. Keep pools small, set idle_session_timeout on the server for the application role, and keep the client-side error handler shown earlier so a killed idle connection is dropped from the pool instead of being handed to the next request.
The pooler as a single point of failure. One PgBouncer on one VM is an outage waiting for a reason. Run two behind an internal load balancer if the services cannot tolerate it, and size max_db_connections on each so the sum stays inside the budget. Managed pooling removes this burden, which is a real part of its price.
Transaction mode changing what code may do. The symptom is intermittent: a SET applies on one request and not the next. Search the codebase for SET , pg_advisory_lock, LISTEN and PREPARE before adopting transaction pooling, and run integration tests against the pooler, not only against the database.
The budget rots. Someone raises an instance cap during an incident and nobody updates the table. Make the sum a CI check that reads the caps from your deployment config and fails when the total passes the allowance.
The same reasoning also says when to stop: with one or two services, skip the table and the pooler, and a sensible pool and --max-instances are enough. A document store such as Firestore has no connections to budget at all, which is one of the factors compared in documents and graphs. And if the application depends on session state, LISTEN and NOTIFY or long-lived cursors, fix that first or give those components a direct connection, rather than forcing them through transaction pooling.
Related articles
All articles →Which Postgres schema changes lock a table, which are safe, and the three-release pattern I use to change a live column without an outage or a long window.
12 min
A jobs table, one claiming query and a lease column give a small team a reliable work queue on the Postgres it already runs. What to build, and where it stops.
11 min
Auto-pause saves money until the first request after a quiet weekend. What a paused database looks like to your app, and a design that survives it.
12 min