Connection pooling Postgres apps need: what it is, how to size the pool, and how to test it under load

The first line of any connection pooling Postgres checklist is a number: how many connections the database will accept. PostgreSQL’s default is typically 100, and every serverless instance opens connections of its own. The rest of the list is a pooler in front of the database, a small app-side pool sized under that ceiling, and a load test that proves it.

Connection pooling Postgres: what a pooler is and the three places it can live

Connection pooling in Postgres keeps a set of open database connections and lends them to requests, because PostgreSQL starts a backend process for every connection and caps them, typically at 100 by default. A pool can live in the app’s driver, in an external pooler such as PgBouncer, or behind the host’s data API.

PostgreSQL implements a “process per user” model: a supervisor process “spawns a new backend process every time a connection is requested”. The ceiling is max_connections, which, per PostgreSQL’s connection settings, “is typically 100 connections, but might be less if your kernel settings will not support it”, and it can only be set at server start. The client pays too. node-postgres’s docs say connecting a new client “requires a handshake which can take 20-30 milliseconds”, and that PostgreSQL “can only process one query at a time on a single connected client”.

A pool fixes both costs at once: a few connections, opened once and lent out request after request. It is one line of the data consistency checklist for SaaS, which holds the database’s other controls.

Connection pooling in Postgres does its work in one of three places, and the table sets them side by side. The last column is my reading of what each place can and cannot protect.

The pooler’s mode decides what a client keeps between transactions. PgBouncer’s pooling modes and feature map describe the three modes in its own words, and session is the default pool_mode.

The feature map marks these “Never” in transaction pooling, among others: SET/RESET, LISTEN, WITH HOLD cursors, SQL-level PREPARE/DEALLOCATE, and session-level advisory locks. Protocol-level prepared statements are the exception. They work in transaction mode when max_prepared_statements is non-zero, and since PgBouncer 1.24.0 its default has been 200, so a current self-hosted PgBouncer left at its defaults supports them.

What goes wrong without it

Each row below is a situation, what the owner notices, and where the fix is taught. The per-instance multiplication, the error strings and the outage steps already live in every connection pool exhausted error and its fix, so the table points there instead of repeating them.

If the symptom is slowness and not errors, check the queries first: is Supabase slow, or is it your queries walks through them. The last row also has a design side, which is how to wrap multiple writes in a transaction without holding a slot longer than the writes need.

One app I audited, a medical app, opened a brand-new set of database connections on every request instead of reusing a pool. The full finding sits with how to set a timeout on fetch and on the database driver. My reading: a pool is built once per process and reused, so a connection opened per request pays the setup cost every time.

java.sql.SQLRecoverableException: No more data to read from socket

The message No more data to read from socket is Oracle’s ORA-17410. In a pooled Java app, my reading is that the pool lent a connection the database, a restart or a firewall had already closed. The pool-side fixes are to retire connections before the network’s limit, keep idle ones alive, and validate on borrow where the overhead is acceptable.

Oracle’s error page prints the code and “No more data to read from socket.” with no cause or action, so the fix has to come from the pool’s side. HikariCP’s README says maxLifetime “should be several seconds shorter than any database or infrastructure imposed connection time limit”, and its default is 30 minutes. Its keepaliveTime keeps an idle connection from “being timed out by the database or network infrastructure”; it defaults to 2 minutes, accepts nothing under 30 seconds, and must be less than maxLifetime. Oracle’s UCP guide offers setValidateConnectionOnBorrow(true), which is false by default and, in Oracle’s words, “may incur significant overhead in applications that checkout database connections frequently”.

The Postgres form of the same failure is the stale pooled socket in the table above, which is why the verify steps restart the pooler mid-run. A crash on the database server behind the same message is a question for whoever administers that server.

How to do it on the common stacks

Three parts, in this order: the connection budget, the pooler, and AWS.

Configure connection pool size for serverless

Connection pool size for serverless comes from a budget: the database’s connection ceiling minus reserved and background slots. With a pooler in front, each function instance’s app pool starts at 1, and the pooler’s own pool holds the real connections. Without one, divide what is left by the number of app processes.

The budget is my method; every default in it comes from the tool’s own docs.

1. 01 Read the real ceiling: run SHOW max_connections; and check the host's published limit for your compute size (or plan, where the host ties it to the plan)
2. 02 Subtract the reserved slots, superuser_reserved_connections (default 3) and, on PostgreSQL 16 and later, reserved_connections (default 0), then what the host's own services, the dashboard, migrations and scheduled jobs need
3. 03 With a pooler in front, size the pooler's server pool, since that is what reaches Postgres: start from (core_count * 2) + effective_spindle_count and set it as PgBouncer's default_pool_size
4. 04 Set each serverless instance's app pool to 1, and set the pooler's client limit above that pool times the most instances you expect
5. 05 Without a pooler, on a long-running server, divide what is left by the number of app processes; a node-postgres Pool holds up to 10 clients by default
6. 06 Set a connection timeout: node-postgres's connectionTimeoutMillis is 0 by default, which means no timeout

Migrations belong to the background share in step 2. Supabase’s docs route “Migrations, pg_dump, backup and restore, or replication” to the direct connection, which is on IPv6, or on IPv4 only if the project has the IPv4 add-on; on an IPv4-only network without it, the docs say to use session mode instead. Scheduled jobs need slots too, such as the runs in the database cleanup checklist. A soft-delete design adds another: once you know what soft delete is, its purge job is one more scheduled writer holding a slot.

For step 3, PgBouncer’s default_pool_size is “The maximum number of server connections to allow per user/database pair”, so two apps under two database users are two pools, and the budget has to hold both. HikariCP’s page on pool sizing gives the starting formula, which it says “is provided by the PostgreSQL project as a starting point”, and tells you to simulate expected load and try settings around it. That gives a PostgreSQL connection pool size to begin with; the load test in the verify section moves it. On a managed pooler these numbers are the host’s to publish and yours to read: Neon sets default_pool_size to 0.9 × max_connections and max_client_conn to 10,000, and says “These settings are not user-configurable.”

For step 4, Supabase’s docs set the pool to 1 connection because “The client is shared by every invocation on that warm instance, so this caps the instance, not the request”, and say to raise it “only when you have evidence that concurrent invocations on one instance are queuing for the connection”. In my reading, that evidence is likeliest on a platform that runs invocations side by side, and Vercel’s Fluid compute docs say Fluid compute “allows multiple invocations to share a single function instance”. The instance count itself is an estimate: Supabase’s docs say the number of warm instances “isn’t something you control”. Prisma’s own pool settings, by version, are in the pool exhaustion guide from the section above.

For steps 5 and 6, node-postgres’s docs say an app usually wants just one pool. When every client is checked out, requests “wait in a FIFO queue until a client becomes available”, and the docs warn that once idle clients run out, further pool.connect calls “will timeout with an error or hang indefinitely if you have connectionTimeoutMillis configured to 0”, so set it, and watch pool.waitingCount as well. To optimize connection pooling from there, change one number at a time and rerun the same load.

Here is the budget worked through as an illustration, with my assumptions: a self-hosted Postgres left at its defaults, a 4-core database server whose active data fits in cache, and serverless functions behind PgBouncer.

The 8 sits far below the 87 on purpose: the ceiling is a maximum to stay under, and HikariCP’s sizing advice is a small pool that the load test then adjusts. If you expect more than 100 instances, PgBouncer’s default max_client_conn is already too low.

How to set up PgBouncer, or turn on the pooler your host already ships

PgBouncer is set up by adding the database entry, setting pool_mode = transaction, sizing default_pool_size from the connection budget, configuring auth and the listen address, and pointing the app at port 6432. Supabase, Neon, DigitalOcean and Azure already ship a pooler that needs only switching on or a pooled string, plus the driver setting where that pooler lacks prepared-statement support.

Whether you create and manage a connection pool in PostgreSQL yourself or let the host run it, check this table first: on these hosts the pooler already exists.

The host docs behind the table: Supabase’s guide to connecting to Postgres, Neon’s connection pooling docs, Cloud SQL Managed Connection Pooling, DigitalOcean’s connection pools and Azure’s built-in PgBouncer.

On your own server, the steps come from PgBouncer’s configuration reference:

1. 01 Install PgBouncer on a server the app can reach
2. 02 Add a [databases] entry that maps a database name to your Postgres host and database
3. 03 Set pool_mode = transaction; the default is session, so serverless apps need this line
4. 04 Set default_pool_size from the budget above; the default is 20 per user/database pair
5. 05 Set max_client_conn above the app side's total; the default is 100
6. 06 Set auth_type (the options include scram-sha-256, md5, cert, hba, ldap and pam) and the auth_file of users
7. 07 Set listen_addr, point the app at listen_port (default 6432), and confirm with SHOW POOLS;

Step 7 has two traps. When listen_addr is not set, “only Unix socket connections are accepted”, so an app connecting over TCP is refused. And the admin console, the pgbouncer database on the same port, lets in only users listed in admin_users or stats_users, both empty by default, so add the reading user to stats_users. In SHOW POOLS, cl_waiting counts “Client connections that have sent queries but have not yet got a server connection”. A minimal config, with placeholders in capitals and the pool size from the illustration above:

[databases]
appdb = host=DB_HOST port=5432 dbname=APP_DB
[pgbouncer]
listen_addr = localhost
listen_port = 6432
pool_mode = transaction
default_pool_size = 8
max_client_conn = MAX_CLIENTS
auth_type = scram-sha-256
auth_file = users.txt
stats_users = STATS_USER

Use localhost only when the app runs on the same machine; otherwise list the address the app reaches PgBouncer on.

The driver change depends on whether the pooler supports prepared statements. Supabase’s docs say “Transaction mode does not support prepared statements. To avoid errors, turn them off in your connection library”, and give a setting per driver: prepare: false for Postgres.js and Drizzle, pgbouncer=true on the connection string for Prisma, statement_cache_size=0 for asyncpg, prepareThreshold=0 for JDBC. Azure’s built-in PgBouncer ships max_prepared_statements at 0, and DigitalOcean’s docs call session mode useful when an app uses prepared statements, so the same care applies on both. Neon runs PgBouncer with max_prepared_statements=1000, and a self-hosted PgBouncer from 1.24.0 on defaults to 200, so protocol-level prepared statements work on both; SQL-level PREPARE still does not.

Take a founder who moves a Supabase app’s serverless functions to the shared pooler’s transaction-mode string to stay under the database’s connection ceiling. The driver still prepares statements, and the app starts returning the errors Supabase’s docs warn about when prepared statements stay on in transaction mode. My reading: moving to a transaction-mode pooler changes what a connection remembers, so the pooled string and the driver setting are one change, shipped together.

SET needs the same care: PgBouncer marks SET/RESET “Never” in transaction pooling, as the modes table shows. When writes start failing as read-only after a pooler change, the error to look up is cannot execute INSERT in a read-only transaction. Pgpool-II is a different tool from these and stays outside this page.

Pooling on AWS: RDS Proxy and the managed poolers

RDS Proxy is AWS’s database proxy for Amazon RDS and Aurora. It pools and shares database connections, queues or throttles clients it cannot serve at once, and connects to a standby during a failure while preserving application connections. Its weak point is pinning: session state such as a SET ties a client to one connection.

AWS’s page on Amazon RDS Proxy says it can let applications “pool and share database connections to improve their ability to scale” and makes them “more resilient to database failures by automatically connecting to a standby DB instance while preserving application connections”. It can enforce IAM authentication for clients, and it connects to the database with IAM database authentication or credentials stored in AWS Secrets Manager. The purpose of RDS Proxy, on the same page, is to handle “unpredictable surges in database traffic” without the overhead of opening a new database connection each time; it “queues or throttles application connections that can’t be served immediately from the connection pool”, and rejects them when requests exceed the limits you set.

One condition decides whether it fits a serverless stack at all. The proxy “must be in the same virtual private cloud (VPC) as the database” and is not reachable from the public internet, so functions hosted on another platform need their own network path into that VPC first.

AWS’s page on avoiding pinning defines it: “When a connection is pinned, each later transaction uses the same underlying database connection until the session ends.” For PostgreSQL the conditions include SET commands, PREPARE, DISCARD, DEALLOCATE or EXECUTE, temporary tables, cursors, listening on a notification channel, and session-level advisory locks such as pg_advisory_lock, among others. Transaction-level advisory locks such as pg_advisory_xact_lock do not pin.

The key difference between RDS Proxy and PgBouncer, in my reading of the two sources, is what happens to session state: the same SET that PgBouncer’s transaction mode marks “Never” makes RDS Proxy pin the session and keep working, with less sharing.

How to verify it

Connection pooling is verified under load: record the settings and the deployed connection string, then run concurrent requests at about twice the expected peak on staging (my working rule). It passes when connections come through the pooler and stay under the budget, and errors appear only during a planned pooler restart, lasting seconds, not minutes (also my working rule).

This is also how deliverable 4.5 of the Production Hardening Sprint is verified: “Run concurrent requests and record connection usage, errors, and pool configuration.”

1. 01 Record the configuration: max_connections, the reserved slots, the pooler's mode, default_pool_size, max_client_conn, each app pool's size and timeouts, the instance cap or your estimate, and the host, port and mode of the connection string the deployed app actually uses, read from the deployed environment and not a local file
2. 02 Take a baseline: count client backend rows in pg_stat_activity against the budget, grouped by client_addr, and run SHOW POOLS; where a PgBouncer admin console is reachable
3. 03 On staging, at the same compute size and pooler settings as production, run concurrent requests at about twice the expected peak for about ten minutes
4. 04 Read connections about every ten seconds; pass when client backends stay under the budget and come from the pooler's address, the app logs no connection errors, and PgBouncer's maxwait does not keep climbing
5. 05 Restart the pooler, or fail the database over, once mid-run where the host gives you a way to; pass when errors last seconds, not minutes, and no stale-connection errors follow
6. 06 Repeat after any change of host, plan, ORM version or instance count, and keep the configuration, the readings, the load tool's report with its error rate, and the date

The load level, the duration and the reading interval in steps 3 and 4, and the seconds-not-minutes bar in step 5, are my working rule. The reading for steps 2 and 4:

SHOW max_connections;
SELECT client_addr, count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY client_addr;

The client_addr grouping is what proves the pooler is in the path. PostgreSQL records the “IP address of the client connected to this backend”, and a null means a Unix socket or an internal process. Through a pooler, in my reading, the rows carry the pooler’s address and stay at or below its server pool while the load tool runs far more concurrent requests; rows from the app instances’ own addresses mean the deployed app is still on the direct string. Where the pooler runs on the database’s own machine, as Azure’s built-in PgBouncer does (“on the same virtual machine (VM) as the database server”), its rows show a local address or, over a Unix socket, a null one (my reading again), so the pass condition becomes no row from an app instance’s address.

The PgBouncer console is reachable on a self-hosted pooler once the reading user is in stats_users, and on Azure’s built-in PgBouncer once pgbouncer.stats_users names an existing user, connecting to the pgbouncer database on port 6432. If maxwait rises, PgBouncer’s docs say “the current pool of servers does not handle requests quickly enough”. For step 5, a self-hosted PgBouncer can always be restarted. On Azure, a server restart restarts PgBouncer and the VM too, and “You then need to re-establish the existing connections”. Where a host’s docs name no restart or failover control, record step 5 as not run.

The method, the tooling and the workload design are in load testing a web application; this page adds only the connection readings to take during the run. If the run reproduces an exhaustion error, the repair loop is the pool exhaustion guide’s, and so is the query that groups connections by application, user and state. The measured connection count belongs in the capacity statement, the document that answers what is a capacity plan. The database’s other checks sit in the database review checklist.

Where the sprint does this

Connection pooling is deliverable 4.5 of the Production Hardening Sprint: “Configure connection pooling or the platform’s equivalent connection management and test it under load.” The load test is deliverable 9.2, verified this way: “Report the workload, duration, environment, concurrency, latency, and error rate before and after changes.” The written capacity statement is 9.6, verified this way: “Link capacity claims to load-test evidence and identify untested projections as projections.” The production readiness report, 13.1, accounts for all 123 IDs, keeps failures visible until resolved and explains genuine non-applicable items. Your app’s current framework and hosting setup are our starting point, and we refactor or replace components where the production work requires it. Hosting, paid tools and API usage remain in your accounts, and we explain any required third-party costs before enabling them. Every item is listed on the published scope.

Common questions about connection pools

How much does Amazon RDS proxy cost?

Amazon RDS Proxy is priced per vCPU per hour of the underlying provisioned instance, with a minimum charge of 2 vCPUs, or per ACU-hour on Aurora Serverless v2, with a minimum of 8 ACUs. Partial hours are billed in 1-second increments, with a 10-minute minimum charge after a billable status change such as creating, starting or modifying. The rate is set per region; RDS Proxy pricing shows the figure for yours.

Is an RDS proxy worth it?

Yes, in my reading, when many clients open connections at a fast rate or traffic surges unpredictably, and when a failover should keep application connections open: those are the cases AWS describes it for. It gives less when most sessions pin, when your functions run outside the database’s VPC and would need a network path built first, or when one long-running server with its own driver pool already stays under the ceiling. Its cost also grows with the database’s vCPUs.

What is the difference between a client and a pool in PostgreSQL?

A client is one connection to the server, and a pool is a set of clients the app reuses. In node-postgres, a pool is “a reusable pool of clients you can check out, use, and return”, and an app usually wants just one. The rest is in node-postgres’s pooling guide.

How many SQL connections are too many?

More than the server’s ceiling is too many: PostgreSQL accepts at most max_connections at once, typically 100 by default, and keeps reserved slots back from ordinary users. For connections doing work at the same moment, HikariCP’s advice is “a small pool of a few dozen connections at most”, with its core-count formula as the starting point. No single number fits every app; the load test decides.

What is the purpose of connection pooling in SQL Server?

The purpose is the same as in Postgres: reuse open connections instead of opening one per request. Microsoft’s docs say “By default, connection pooling is enabled in ADO.NET”, so a .NET app pools unless it explicitly turns pooling off, and Microsoft’s page on SQL Server connection pooling lists the connection string settings that control it.