Connection pooling when every service is tiny

Every service in the system was configured correctly. Each one used a connection pool, each pool had a modest size, and each service had been load tested on its own. Then a marketing email went out, five of the services scaled out at the same moment, and the database started answering new connections with sorry, too many clients already. Nothing was wrong in any single repository. The failure lived in the sum, and no one owned the sum.

This is the connection problem of small services. A monolith has one pool and one number to tune. A fleet of tiny services on Cloud Run, Cloud Functions or any other autoscaling platform has dozens of pools, each multiplied by an instance count that the platform changes without asking you. They all point at one Postgres, which has a hard connection ceiling. This article is for engineers running several small services against one Cloud SQL for PostgreSQL instance (or any single Postgres) who want to size pools deliberately, know when a pooler is worth running, and understand what actually happens during a scale-out. I'll cover the budget arithmetic, the options for pooling, the code and commands I use, and the failure modes that remain afterwards.

The constraint: one ceiling, many multipliers#

Postgres gives each client connection a dedicated backend process. The limit is the max_connections setting, which defaults to 100 in the PostgreSQL source and needs a server restart to change. A few of those slots are held back: superuser_reserved_connections defaults to 3, so ordinary roles see a little less than the headline number. On Cloud SQL the default depends on the machine type, and the quotas and limits page is where Google documents it. I don't memorise those values. I ask the instance:

SHOW max_connections;
SHOW superuser_reserved_connections;

Against that ceiling, the demand is a product of three numbers per service:

1. Maximum instances. On Cloud Run this is the --max-instances setting, or the platform default if you never set it. It is the multiplier you least control during a spike.
2. Pool size per instance. The client library default is often generous. The pg package for Node, for example, uses a pool max of 10 unless you change it.
3. Overlap. During a deploy, old and new revisions run side by side. Cloud Run jobs, migrations, a console session and a BI tool also hold connections.

Here is an illustrative case: 12 services, each allowed 20 instances, each instance with the default pool of 10. The worst case is 12 x 20 x 10 = 2,400 connections, against a database that may allow a few hundred. The platform will happily let that happen, because it knows nothing about your database.

There is also a Cloud Run specific limit. Per Google's documentation, an instance connected through the built-in Cloud SQL connection (the one you enable with --add-cloudsql-instances) is limited to 100 connections to a Cloud SQL database. That limit does not apply to the Cloud SQL Auth Proxy run as a sidecar, to the language connectors, or to connections made straight to the instance IP.

Pool size, concurrency and what a pool actually does#

Cloud Run's request concurrency defaults to 80 per instance and can be raised to 1,000 (see the concurrency documentation). It does not mean an instance needs 80 connections. A connection is only held while a query or transaction is running, not for the whole request. If a request spends 200 ms in total and 8 ms of that inside database transactions, then at 40 requests per second an instance needs, on average, 40 x 0.008 = 0.32 connections busy at any moment. These numbers are illustrative, but the method is real: required connections are roughly the arrival rate of database work multiplied by how long each unit holds a connection.

That is why a pool of 2 or 3 per instance is usually enough, and why a pool of 10 per instance mostly buys you headroom against a database you don't 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 cheap compared with a new backend process on the server, and it is the first place a bad query shows up as latency instead of as an outage.

The wait needs a bound, though. In pg the default connectionTimeoutMillis is 0, meaning no timeout. A request can queue behind a stuck pool forever. I always set it, to a value shorter than the request deadline, so that an exhausted pool returns an error quickly and the caller can shed load.

The options#

There are five approaches I've used or evaluated. They stack, so the table is about where each one applies.

Two details about that table matter in practice.

First, a pooler cannot be a normal Cloud Run service. Cloud Run services accept HTTP and gRPC requests, not raw TCP, and PgBouncer speaks the Postgres wire protocol. If you run your own, it 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.

Second, managed pooling on Cloud SQL has prerequisites. At the time of writing, Google's documentation says the instance must be on the Enterprise Plus edition and a recent enough maintenance version, and that the pooler is reached through the Auth Proxy (version 2.15.2 or later) or directly on port 6432. It defaults to transaction pooling. Check the managed connection pooling overview for the current requirements before designing around it, since this is a feature that is still evolving.

The decision#

I use a layered rule, and I apply the layers in order.

1. Always give every service a small pool, a bounded --max-instances, and an entry in a shared connection budget. This costs nothing and removes most incidents.
2. When the budget cannot be made to fit, because there are too many services or too many instances, add a pooler in transaction mode. If the instance is already on, or justifies, Enterprise Plus, I use managed pooling because it removes a component I would otherwise have to run. If not, I run PgBouncer on a small VM close to the database.
3. Raise max_connections last, and only to match what the machine can hold, not to match the autoscaler's worst case.

The reason 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.

Implementation#

Write the budget down#

The budget is a small table, kept next to the infrastructure code. Mine has one row per service with its instance cap and pool size, and a fixed allowance for everything that isn't a service.

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 more rows)                            ...
-----------------------------------------
total must stay at or below 77

The numbers are illustrative. The habit is the point: raising --max-instances becomes a one-line change to this table, and the sum stays visible.

Pool configuration in the service#

Each service builds one pool at startup, from environment values, so the pool size and the instance cap in the budget can be changed together. I use the Cloud SQL Node.js connector, which opens connections with IAM-aware authorisation and encryption 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, // 2 or 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;
}

The highlighted settings do the work. max is the budgeted pool size. connectionTimeoutMillis bounds the queue. maxLifetimeSeconds and idleTimeoutMillis are options of the pg-pool package, and I checked them in its source: the idle timeout defaults to 10 seconds, the lifetime to unlimited. The application_name is cheap and invaluable, because it lets you see which service holds which connections.

Closing the pool on SIGTERM matters on Cloud Run, which signals an instance before it stops it. Without it, the database sees abandoned sessions and keeps their backend processes until its own timeouts notice. Where the credentials come from is covered in configuration and secrets in multi-service Node apps.

Set the Cloud Run side of the multiplier#

The instance cap and concurrency are deployment settings, so they belong in the same place 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=orders

With the pool capped independently, concurrency no longer decides how many connections you open, only how many requests share them. The effect of instance count on idle cost and cold starts is covered in scale-to-zero as a budget strategy and Cloud Run cold starts.

See the real numbers#

A budget is a prediction. This query shows what is actually 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 the idle state 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. You can also have the server end abandoned sessions with idle_in_transaction_session_timeout, which defaults to 0 (disabled) and is worth setting to a few tens of seconds.

Adding managed pooling on Cloud SQL#

If you are on an eligible edition, enabling the pooler is an instance setting. Per the Cloud SQL documentation, the flag is --enable-connection-pooling:

gcloud sql instances patch my-instance --enable-connection-pooling

Pool behaviour is then tuned through Cloud SQL's pooling configuration, which I leave to the configuration guide because the parameter names are specific to that feature and have been changing. Two points from the overview are worth planning for. Transaction pooling is the default mode, so the session-state caveats below apply. And if you use the asyncpg Python driver, the documentation says max_prepared_statements must be set above 0 for prepared statements to work through the pooler.

Running PgBouncer yourself#

On a small VM in the same region and VPC as the database, a minimal configuration looks like this. Every setting below is in the PgBouncer configuration reference, and I've commented the ones where I depart 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 is 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, do not queue for two minutes
server_idle_timeout = 600     ; default
max_prepared_statements = 200 ; default, protocol-level prepared statements in transaction mode

max_client_conn is the number of clients PgBouncer accepts, while default_pool_size and max_db_connections decide how many server connections reach Postgres. That gap is the whole point: thousands of cheap client connections multiplexed onto a few dozen expensive server ones. The query_wait_timeout of 120 seconds by default is a trap. A client waiting two minutes for a connection is a client whose request has long since been abandoned, so I shorten it to something below the request deadline.

In transaction mode, PgBouncer's own documentation says clients must not use session-based features, since consecutive transactions can run on different server connections. In practice that rules out relying on SET outside a transaction, session-level advisory locks, LISTEN and NOTIFY, and SQL-level PREPARE statements. Use SET LOCAL inside a transaction, transaction-level advisory locks, and a separate direct connection for anything that listens. Recent PgBouncer versions can also track protocol-level prepared statements in transaction mode through max_prepared_statements (default 200), which is why most modern drivers work without changes. Migrations and schema tools often need session features, so I point those at the database directly and keep them out of the budget's service rows.

Trade-offs and failure modes#

Connection storms on scale-out. A pool opens connections lazily, as work arrives. When a spike brings 30 new instances up at once, each starts its pool at the same moment and each runs its first queries, so the database sees a burst of new backends, each paying TLS, authentication and process start. If the total crosses max_connections, new attempts fail with 53300 (the "too many clients" class), and the failing requests tend to be retried by callers, which adds load. The defences are layered. The instance cap bounds the burst. A pooler absorbs it, because clients connect to PgBouncer, not to Postgres. And a retry with jittered exponential backoff keeps a failed batch from becoming a second burst.

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)));
    }
  }
}

I only retry this one error class, and only for idempotent reads or writes with a key, in line with the approach in idempotent Pub/Sub consumers. Retrying a non-idempotent write after a timeout is how you create duplicates.

Exhaustion that looks like an application bug. When the database is full, the symptoms show up in the services: timeouts, slow endpoints, health checks that fail because they run a query. Look at pg_stat_activity before you look 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. The connection stays open on the server until the instance wakes or is stopped. This is one more reason to keep pools small and to set idle_session_timeout on the server for the application role, with the client-side error handler shown earlier so a killed idle connection is dropped from the pool, not handed to the next request.

The pooler is a single point of failure. One PgBouncer on one VM is a new outage waiting to happen. Run two behind an internal load balancer or a managed instance group if the services cannot tolerate it, and size max_db_connections on each so that the sum stays inside the budget. Managed pooling removes this operational burden, which is a real part of its price.

Transaction mode changes what your code may do. The session features above are the usual breakage, and the symptom is intermittent: a SET applies on one request and not the next. I search the codebase for SET , pg_advisory_lock, LISTEN and PREPARE before adopting transaction pooling, and I run the 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.

Checklist#

When not to do this#

If you run one or two services, skip the table and the pooler. A well-sized pool and a sensible --max-instances are enough, and extra infrastructure is extra risk. If your database already sits behind a platform that pools for you, or your workload is document-shaped and fits a store without connections to manage, such as Firestore, you can avoid the problem altogether. I compare those stores in documents and graphs. Avoid transaction pooling if your application leans on session state, LISTEN and NOTIFY or long-lived cursors, and fix the application first or give that one component a direct connection. And do not raise max_connections to match the autoscaler's theoretical peak. Cap the autoscaler instead.