Postgres Connection Pooling Explained (Session vs Transaction)

Postgres connection pooling puts a pooler between your app and the database so that many client connections share a small number of real database connections. You need it because each Postgres connection is a separate server process with its own memory, so a database that handles 50 busy connections well can struggle with 2,000 idle ones. Poolers usually offer two modes: session mode, where a client keeps one database connection for as long as it is connected, and transaction mode, where a client gets a connection only for the duration of each transaction.

The right choice depends on what your client does with the connection. This article explains both modes, what breaks in transaction mode, and how to size pools.

#Why Postgres connection pooling exists: connections are expensive

Postgres uses a process-per-connection model. Every new connection forks a backend process that holds memory for its catalog caches, work memory and session state. Opening a connection also costs a TLS handshake and authentication.

That leads to two practical problems:

• Too many connections. max_connections defaults to 100 in stock Postgres, and raising it a lot is not free: every connection, even idle, has a cost, and lock and snapshot management get slower with more backends.
• Connection churn. Serverless functions often open a connection per invocation. Hundreds of short-lived connections per second waste most of their time on setup.

A pooler fixes both. It keeps a fixed set of server connections open and lends them to clients.

#Session mode

In session mode, the pooler assigns a database connection when a client connects and keeps it assigned until the client disconnects. To the client it behaves exactly like a direct connection.

Everything works:

• SET search_path and other session settings
• LISTEN / NOTIFY
• Temporary tables
• Session-level advisory locks (pg_advisory_lock)
• SQL-level PREPARE
• Long migrations and psql sessions

The downside: the pooler can serve only as many clients at once as it has server connections. Session mode protects the database from connection storms (excess clients wait instead of crashing the server) but does not let 1,000 clients share 20 connections.

#Transaction mode

In transaction mode, the pooler assigns a database connection at begin (or for a single statement) and takes it back at commit or rollback. The next transaction from the same client may run on a different server connection.

This is what lets thousands of clients share a small pool. Most web requests run a few short transactions and spend the rest of their time waiting on the network or the CPU, so the server connection is free most of the time.

What stops working is anything that stores state on the connection between transactions:

The last row deserves a note. Older PgBouncer versions broke protocol-level prepared statements in transaction mode, which is why many guides tell you to disable them in Prisma or other drivers. Newer poolers can track prepared statements across connections. On StitchDB, the docs state that prepared statements sent by drivers are supported in transaction mode, while SQL-level PREPARE is not.

#SET LOCAL in practice

If you set per-request values (a tenant id, a statement timeout), do it inside the transaction:

begin;
set local statement_timeout = '5s';
set local app.tenant_id = '5b1e...';
select * from public.invoices where tenant_id = current_setting('app.tenant_id')::uuid;
commit;

set local disappears at commit, so the next client on that connection does not inherit it.

#Which mode for which client

On StitchDB, the two modes are two ports on the same host: 5432 for session mode and 6543 for transaction mode. Postgres itself is not exposed to the internet; every connection goes through the pooler, which checks TLS and the password first. See Connection pooling and Connect to Postgres.

# Session mode
postgresql://p_<ref>_owner:<password>@<db-host>:5432/p_<ref>?sslmode=require
# Transaction mode
postgresql://p_<ref>_owner:<password>@<db-host>:6543/p_<ref>?sslmode=require

#Two pools, not one

Most production apps end up with two layers of pooling:

1. An application-side pool (in your ORM or driver) that keeps a few connections open per process.
2. A server-side pooler in front of Postgres that multiplexes all app processes.

These interact. If you run 20 app instances, each with a pool of 10, you have up to 200 client connections to the pooler. In transaction mode that is fine as long as the pooler accepts that many clients. In session mode, each client holds a server connection, so you would need 200 database connections.

Rules of thumb:

• Keep app-side pools small (5 to 10 per process) when you sit behind a transaction-mode pooler.
• In serverless functions, use a pool of size 1 per function instance, and connect to transaction mode.
• Close idle connections. idle_timeout settings in drivers help.

#Sizing the server pool

A common starting point for the number of busy database connections is a small multiple of the CPU cores on the database server. Beyond that, extra connections mostly wait on each other. Throughput often goes up when you reduce the pool, because fewer queries fight for the same CPU and locks.

Managed platforms set these limits for you. On StitchDB, three numbers are plan limits:

Pooled clients are connections the pooler accepts. Pooler database connections are what it opens to Postgres for transaction mode. Direct database connections limit your project role's connections overall. A dedicated server raises the connection limits with its size. Check the current values in Database limits.

#Symptoms of a pooling problem

• "Too many connections" errors: clients connect directly or in session mode and exceed the limit. Move short-lived clients to transaction mode.
• Requests hang under load, then time out: clients are waiting for a free pooled connection. Usually caused by long transactions holding connections. Look for slow queries and idle in transaction sessions.
• Random "prepared statement does not exist" errors: a driver using named prepared statements through a pooler that does not track them. Upgrade the pooler or disable named statements.
• Settings "randomly" not applied: plain SET in transaction mode. Use SET LOCAL.

On StitchDB, Database › Connections in Studio lists every session with its state, so you can spot Idle in transaction sessions (stuck for 30 seconds or more), blocked sessions and long-running queries, and cancel or close your own. The Health tab of Advisors warns when pooled clients reach 80% of the plan limit or when clients wait for a pooled connection.

-- Find long transactions that hold connections
select pid, now() - xact_start as open_for, state, left(query, 80) as query
from pg_stat_activity
where xact_start is not null
order by xact_start
limit 10;

#Serverless without a pooler: the REST API

For many serverless and edge workloads you can skip SQL connections entirely. Backends like StitchDB generate a REST API from your schema; each request runs as a short transaction on the server side, already pooled. From a browser, mobile app or edge function, that is often simpler than managing a driver:

import { createClient } from 'stitchdb-js'

const stitchdb = createClient('https://<project-ref>.p.stitchdb.com', anonKey)
const { data } = await stitchdb.from('orders').select('id, total').eq('status', 'paid')

For server code in runtimes without TCP, StitchDB's serverless driver sends SQL over HTTPS and runs it through a pool on the server.

When reads dominate, the next step after pooling is often read replicas.

#FAQ

#Is transaction mode always faster?

No. It lets more clients share fewer connections. A single long-running server with a modest pool may see no difference in session mode.

#Can I run migrations through transaction mode?

Avoid it. Migrations use session state and long transactions, and create index concurrently must run outside a transaction block. Use session mode (port 5432 on StitchDB).

#Does PgBouncer support LISTEN/NOTIFY?

Only in session mode. In transaction mode there is no stable connection to receive notifications.

#What pool size should my ORM use?

Behind a transaction-mode pooler, 5 to 10 per process for servers and 1 for serverless functions is a reasonable start. Measure and adjust.

See how pooling and limits work per plan on the pricing page, or create a free project and connect on both ports.