PostgreSQL Connection Pooling: The Complete Guide

Why connection pooling matters

PostgreSQL handles concurrency differently from most application servers. Each client connection spawns a dedicated operating system process on the server — not a thread, a full process. This process allocates its own memory (typically 5-10 MB at baseline, more under load), maintains its own query execution state, and persists for the lifetime of the connection.

At small scale, this works fine. At 200+ concurrent connections, the costs compound:

• Memory pressure. 200 connections at 10 MB each consume 2 GB of RAM before any query allocates work_mem. This is memory that cannot be used for shared_buffers or OS page cache.
• Context switching. The OS kernel must schedule 200+ processes. At high connection counts, the scheduler itself becomes a bottleneck, even if most connections are idle.
• Connection establishment overhead. Each new connection requires a TCP handshake (plus TLS negotiation if encrypted), PostgreSQL authentication, and a fork() system call. This adds 50-150ms per connection.

The default max_connections in PostgreSQL is 100. Many teams increase this to 500 or 1,000, assuming more connections means more throughput. The opposite is often true: beyond a certain point, adding connections degrades performance because of memory contention and scheduling overhead. More guests in the dining room does not mean dinner is served faster — it means the kitchen falls behind.

Connection pooling solves this by decoupling application connections from database connections. The pooler maintains a smaller number of persistent backend connections to PostgreSQL and multiplexes many client connections across them. An application with 500 concurrent users might need only 20-30 actual database connections — because most of those 500 users are reading a page, filling out a form, or waiting for a response, not actively executing a query.

For foundational terminology, see the Connection Pooling glossary entry.

Pooling modes explained

Every connection pooler operates in one of three modes, and understanding the distinction is worth your time — the mode you choose determines which PostgreSQL features are available to your application and how efficiently connections are shared.

Session pooling

In session pooling mode, a client receives a dedicated backend connection when it connects and keeps it for the entire session. The pooler's role is limited to reusing backend connections after a client disconnects.

What works: Everything. All PostgreSQL features — prepared statements, LISTEN/NOTIFY, SET commands, temporary tables, advisory locks, cursors.

Efficiency: Modest, at best. If clients hold sessions open for long periods, session pooling is barely better than no pooling at all.

When to use: Legacy applications that depend on session-level state and cannot be refactored.

Transaction pooling

In transaction pooling mode, a client receives a backend connection only when it begins a transaction. The connection is returned to the pool when the transaction commits or rolls back.

What works: Standard SQL queries and transactions. Any operation that completes within a single transaction.

What breaks:

• Prepared statements — created in one transaction, the backend may be different in the next.
• LISTEN/NOTIFY — LISTEN registers on the session, which does not persist across transaction boundaries.
• SET commands — SET search_path, SET work_mem, and other session-level configuration is lost between transactions.
• Temporary tables — created in one transaction, potentially inaccessible in the next.
• Advisory locks — session-level advisory locks are tied to the backend connection.

Efficiency: This is where connection pooling earns its keep. Transaction mode lets dozens or hundreds of clients share a handful of backend connections — because any given client is only actively using the database for a fraction of its session lifetime.

PgBouncer workaround for prepared statements: Set server_reset_query = DEALLOCATE ALL. See asyncpg + PgBouncer Prepared Statement Trap for a detailed walkthrough.

Statement pooling

In statement pooling mode, the backend connection is returned to the pool after every individual statement — not after each transaction.

What breaks: Multi-statement transactions. Since the connection may change between statements, BEGIN ... COMMIT sequences are not guaranteed to execute on the same backend.

When to use: Rarely. Statement pooling is too restrictive for most applications. It is viable only for workloads that consist entirely of single, independent statements.

PgBouncer — The industry standard

PgBouncer is the most widely deployed PostgreSQL connection pooler, and that reputation is well earned. It is lightweight, single-purpose, and battle-tested across millions of production databases.

Architecture and how it works

PgBouncer is a single-threaded event loop that sits between the application and PostgreSQL as a TCP proxy. It accepts client connections on one side, maintains a pool of backend connections on the other, and routes queries between them according to the configured pooling mode.

Because it is single-threaded, PgBouncer has minimal memory and CPU overhead. A single PgBouncer instance can handle thousands of client connections. The trade-off is that extremely high throughput (10,000+ transactions per second) may saturate the single thread.

Setting up PgBouncer

# Debian/Ubuntu
sudo apt install pgbouncer

# RHEL/CentOS
sudo yum install pgbouncer

# Docker
docker run --name pgbouncer -d edoburu/pgbouncer

Minimal configuration (pgbouncer.ini):

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300
server_reset_query = DEALLOCATE ALL

Key settings explained:

Authentication configuration:

The auth_file (userlist.txt) contains username/password pairs. Generate the MD5 hash with:

SELECT 'md5' || md5('password' || 'myuser');

Alternatively, use auth_type = hba with auth_hba_file to mirror PostgreSQL's pg_hba.conf rules, or auth_type = scram-sha-256 for SCRAM authentication (PgBouncer 1.21+).

Monitoring PgBouncer:

-- Pool status: active, waiting, and idle connections per pool
SHOW POOLS;

-- Aggregate statistics: query count, bytes, time
SHOW STATS;

-- Backend server connections: state, database, user
SHOW SERVERS;

-- Client connections: state, database, user
SHOW CLIENTS;

The SHOW POOLS output is the most important for day-to-day monitoring. I would direct your attention to the cl_waiting column — if clients are consistently waiting for a backend connection, either increase default_pool_size or investigate slow queries holding connections longer than they should.

PgBouncer pitfalls

Prepared statement incompatibility in transaction mode. This is the single most common issue. Many database drivers use the extended query protocol, which implicitly creates server-side prepared statements. In transaction mode, the backend connection changes between transactions, and the prepared statement does not exist on the new backend.

Symptoms: ERROR: prepared statement "..." does not exist errors that appear intermittently. Solutions: set server_reset_query = DEALLOCATE ALL, configure your driver to disable implicit prepared statements, or use PgCat instead.

SET statements silently ignored. In transaction mode, SET search_path = ... executes on the current backend, but the next transaction may use a different backend. The SET has no effect for subsequent queries. This fails silently.

Connection queue starvation. When max_client_conn is reached, new connections are rejected. Long-running queries exacerbate this — one query holding a backend for 30 seconds blocks other clients from using that slot.

Single-threaded bottleneck. At very high throughput (10,000+ TPS), PgBouncer's single-threaded event loop can become the bottleneck. Solutions: run multiple PgBouncer processes behind a load balancer, or switch to PgCat or Odyssey.

pgpool-II — The Swiss Army knife

pgpool-II takes a fundamentally different approach from PgBouncer. Where PgBouncer is a TCP proxy that forwards bytes without inspecting them, pgpool-II parses SQL, understands query semantics, and provides a suite of capabilities beyond pooling:

• Connection pooling with session and transaction modes.
• Load balancing — routes read queries to replicas automatically.
• Replication management — can manage streaming and logical replication.
• Watchdog — built-in high availability with automatic failover.
• Query caching — in-memory cache for identical queries.

When pgpool-II is the right choice: you need read replica routing without a separate load balancer, or you want pooling + HA + load balancing in a single component.

When PgBouncer is better: pure connection pooling is the only requirement, you want minimal operational complexity, or performance matters at the margin.

The key architectural difference: pgpool-II understands your SQL. PgBouncer does not. This makes pgpool-II more capable but heavier — it introduces a SQL parser into the connection path. More capable tools require more careful operation.

PgCat and Odyssey — The new generation

PgCat

PgCat is a multi-threaded, Rust-based connection pooler developed by PostgresML. Headline features:

• Multi-threaded architecture. No single-thread bottleneck. Scales across CPU cores.
• Native prepared statement support in transaction mode. PgCat tracks prepared statements and re-prepares them on new backend connections transparently.
• Sharding and multi-tenant routing. Routes queries to different instances based on sharding keys or tenant identifiers.
• Query load balancing. Built-in read replica routing with configurable strategies.

When to consider PgCat: sharded deployments, multi-database routing, workloads needing prepared statements in transaction mode, or environments hitting PgBouncer's single-thread ceiling.

Odyssey

Odyssey is a multi-threaded connection pooler developed by Yandex. Key differentiators:

• Per-route pool configuration. Different pool sizes, modes, and timeouts for different user/database combinations.
• Multi-threaded. Distributes work across threads, avoiding the single-thread bottleneck.
• Lower latency under high concurrency. Benchmarks show Odyssey outperforming PgBouncer under high thread counts.
• Logging and observability. Per-route metrics and query logging built in.

Both PgCat and Odyssey are production-ready, but their communities are smaller than PgBouncer's. PgBouncer's documentation, operational playbooks, and community knowledge run deeper — the kind of depth that matters at 3 AM when something is not behaving.

Application-level connection pooling

Most application frameworks and database drivers include built-in connection pools. Understanding what these do (and do not do) is essential before deciding whether you need an external pooler.

What application-level pooling solves: connection churn within a single process. Instead of opening and closing a connection for every request, the application reuses connections from its internal pool.

What it does not solve: the multi-instance problem. If you run 20 application server instances, each with pool_size=10, you have 200 backend connections to PostgreSQL — regardless of how efficiently each instance manages its own pool.

The standard production pattern combines both: application-level pooling within each process plus an external pooler between all application instances and PostgreSQL. They serve different purposes and complement each other well.

For framework-specific pool tuning guides:

• FastAPI + PostgreSQL Connection Pool
• HikariCP Pool Sizing for PostgreSQL
• Rails Connection Pool Sizing with Solid Queue
• pgxpool Tuning for High Concurrency
• Laravel Horizon PostgreSQL Connections
• PHP-FPM PostgreSQL Connection Exhaustion
• Django Channels PostgreSQL Connection Exhaustion

Serverless and managed pooling

Serverless and edge compute environments present a particular challenge for PostgreSQL connections. Each function invocation may create a new connection, and the platform may scale to thousands of concurrent invocations. Without pooling, this can exhaust max_connections in seconds.

Neon

Neon's architecture includes an HTTP-based connection proxy that handles pooling transparently. The HTTP interface is inherently pooled — each request is a self-contained transaction. No separate pooler needed.

Supabase

Supabase includes Supavisor, an Elixir-based multi-tenant connection pooler. Session and transaction mode available via different ports: port 5432 for direct connections and port 6543 for pooled connections.

AWS RDS Proxy

Amazon's managed pooling solution for RDS and Aurora. Managed service with automatic failover detection and IAM authentication support. The cost is a per-vCPU/hour charge on top of the RDS instance cost — worth evaluating whether the operational simplicity justifies the price compared to self-managed PgBouncer.

For serverless-specific connection challenges, see Vercel PostgreSQL Connection Pool Exhaustion and Cloudflare Hyperdrive with Drizzle and Workers.

Tuning max_connections

PostgreSQL's max_connections parameter controls the maximum number of concurrent backend connections. Getting this number right requires understanding how it interacts with your pooling layer and available memory.

The common mistakes

Leaving it at 100. With no pooler, 100 connections is a hard ceiling. If your application has more than 100 concurrent database operations, connections are refused.

Setting it to 1,000. Each connection allocates memory for query execution. With work_mem = 256MB and max_connections = 1000, the theoretical maximum memory allocation is 256 GB per sort node. You will encounter OOM conditions long before reaching theoretical maximums.

The right approach

-- With PgBouncer (default_pool_size=20, 1 database, reserve=5):
-- max_connections = (20 * 1) + 5 + 3 + 5 = 33
-- Setting max_connections = 40 gives comfortable headroom.

-- Without a pooler (10 app servers, pool_size=10):
-- max_connections = (10 * 10) + 10 = 110

The work_mem relationship

The work_mem setting deserves particular attention. Each query operation (sort, hash join, hash aggregate) can allocate up to work_mem bytes. A single complex query might have 5-10 such operations. The worst-case memory usage:

max_memory = max_connections * work_mem * operations_per_query

This is why the combination of high max_connections and high work_mem is dangerous. Either keep max_connections low (using a pooler) or keep work_mem conservative. Never set both high.

A practical guideline: allocate no more than 25% of available RAM to the product of max_connections * work_mem. The remaining 75% is needed for shared_buffers, OS page cache, and application processes.

Monitoring connection usage

-- Current connection count
SELECT count(*) FROM pg_stat_activity;

-- Connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- Connections by application
SELECT application_name, count(*)
FROM pg_stat_activity
GROUP BY application_name
ORDER BY count DESC;

-- How close to the limit
SELECT
    max_conn,
    used,
    max_conn - used AS available,
    round(100.0 * used / max_conn, 1) AS pct_used
FROM
    (SELECT count(*) AS used FROM pg_stat_activity) q,
    (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') s;

If pct_used is consistently above 70%, investigate whether connections are being held unnecessarily or whether the pool size needs adjustment. If it is consistently below 20% with an external pooler, max_connections can be safely lowered to free memory.

Choosing the right pooling strategy

For a comparison of how Gold Lapel approaches connection pooling, see Gold Lapel vs PgBouncer.

When pooling adds complexity without payoff

I should be honest about this: connection pooling is not universally necessary.

Single-instance applications with modest traffic. If you run one application server with a connection pool of 10-20 and your PostgreSQL instance is not under memory pressure, an external pooler provides almost no value.

The pooler as a band-aid. If your queries hold connections for 2 seconds each because of slow query execution, a connection pooler does not fix the underlying problem — it queues the pain. Fix the slow queries first. A pooler placed in front of a slow database is a waiting room in front of a slow clinic. Consider materialized views for expensive read queries.

Transaction mode restrictions causing subtle bugs. Prepared statement errors, lost session variables, and advisory lock failures can appear intermittently — exactly the failure mode that is hardest to diagnose.

Added failure point. The pooler is a network hop in the critical path. If PgBouncer goes down, all database access stops.

My honest recommendation: start without an external pooler, use your framework's built-in connection pool, and add an external pooler when monitoring shows you need one — not before. The best infrastructure is the infrastructure you actually need.