
PostgreSQL's process-per-connection model, while robust, introduces significant overhead at scale. Each client connection spawns a dedicated backend process, consuming memory (typically 10MB+ per backend, varying with workload and configuration) and CPU cycles. For applications with many short-lived connections or high concurrency, this overhead quickly becomes a bottleneck, leading to increased latency, resource exhaustion, and ultimately, service degradation. PgBouncer addresses this by acting as a lightweight proxy, multiplexing client connections onto a smaller, fixed pool of server connections.
Model connection scaling, transaction pooling & RAM limits
Model PostgreSQL connection scaling across microservices. Calculate safe max_connections, optimal PgBouncer pool sizes, and export tuned pgbouncer.ini, postgresql.conf, and Kubernetes manifests.
PgBouncer offers three primary pooling modes, each with distinct implications for application behavior and resource utilization. Understanding these modes is critical for correct deployment.
pool_mode = session)In session pooling, a server connection is assigned to a client for the entire duration of its connection to PgBouncer. When the client disconnects, the server connection is returned to the pool. This mode is the most transparent to the application, as it behaves almost identically to a direct PostgreSQL connection.
Pros:
Cons:
pool_mode = transaction)Transaction pooling is the most commonly recommended mode for web applications and microservices. A server connection is assigned to a client only for the duration of a transaction. Once the transaction commits or rolls back, the server connection is immediately returned to the pool, even if the client remains connected to PgBouncer.
Pros:
Cons:
pool_mode = statement)In statement pooling, a server connection is assigned to a client for the duration of a single statement. After the statement executes, the server connection is immediately returned to the pool. This is the most aggressive pooling mode.
Pros:
Cons:
The primary challenge with transaction pooling is its incompatibility with server-side prepared statements. When a client executes PREPARE or uses a client library that implicitly prepares statements (e.g., Npgsql's NpgsqlCommand.Prepare(), psycopg2's cursor.execute() with prepare=True), the prepared statement is associated with the specific server connection. In transaction pooling, this server connection is returned to the pool after the transaction, and a subsequent transaction from the same client might receive a different server connection, which does not have the prepared statement. This results in errors like ERROR: prepared statement "..." does not exist.
DISCARD ALLPgBouncer can be configured to automatically execute DISCARD ALL at the end of each transaction when a server connection is returned to the pool. DISCARD ALL cleans up all session-local state, including prepared statements, advisory locks, and temporary tables. This ensures a clean slate for the next client using that server connection.
Configuration (pgbouncer.ini):
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb pool_mode=transaction
[pgbouncer]
server_reset_query = DISCARD ALL
Impact:
DISCARD ALL will negate that benefit, as statements will be re-prepared for each transaction. For most web applications, the overhead of re-preparing simple statements is negligible compared to the benefits of transaction pooling.Many modern database drivers implement client-side statement caching and preparation. The driver parses the query, substitutes parameters, and sends the complete query string to the server. This avoids server-side state issues with PgBouncer.
Example (Node.js pg library):
import { Pool } from 'pg';
const pool = new Pool({
user: 'app_user',
host: 'pgbouncer_host',
database: 'mydb',
password: 'password',
port: 6432, // PgBouncer default port
});
async function getUser(userId: number) {
const client = await pool.connect();
try {
// This is a client-side prepared statement (parameterized query)
// The driver sends 'SELECT * FROM users WHERE id = $1' and [userId]
// PgBouncer handles this fine in transaction mode.
const res = await client.query('SELECT * FROM users WHERE id = $1', [userId]);
return res.rows[0];
} finally {
client.release();
}
}
// Example of a server-side prepared statement that would break in transaction mode
// unless DISCARD ALL is used or the application explicitly manages it.
async function createAndExecuteServerSidePreparedStatement() {
const client = await pool.connect();
try {
// This explicitly uses a server-side prepared statement named 'my_stmt'
// This will break in transaction pooling without DISCARD ALL or similar cleanup.
await client.query('PREPARE my_stmt (int) AS SELECT * FROM users WHERE id = $1');
const res = await client.query('EXECUTE my_stmt(1)');
console.log(res.rows);
// Must explicitly deallocate or rely on DISCARD ALL
await client.query('DEALLOCATE my_stmt');
} finally {
client.release();
}
}
Impact:
PREPARE statements will still break.DEALLOCATEIf server-side prepared statements are absolutely necessary for performance and DISCARD ALL is not desired (e.g., due to the overhead of re-preparation for very complex queries), the application must explicitly manage the lifecycle of named prepared statements. This means issuing DEALLOCATE <statement_name> before the transaction commits or the connection is returned to the pool. This is complex and error-prone.
// This is a conceptual example, actual implementation depends heavily on the ORM/driver.
async function executeComplexQueryWithManagedPreparedStatement(client: any, param: any) {
const statementName = `my_complex_query_${Date.now()}`; // Unique name per execution
try {
await client.query(`PREPARE ${statementName} (int) AS SELECT complex_func($1) FROM large_table WHERE condition = $1`);
const res = await client.query(`EXECUTE ${statementName}(${param})`);
return res.rows;
} finally {
// Crucial: Deallocate the prepared statement before the transaction ends
// or the connection is returned to the pool.
await client.query(`DEALLOCATE ${statementName}`);
}
}
Impact:
pgbouncer.ini Essentials[databases]
# Define your databases. 'mydb' is the alias clients connect to.
# 'host', 'port', 'dbname' are for the actual PostgreSQL server.
# 'pool_mode' is critical. 'transaction' is generally recommended.
# 'pool_size' is the number of server connections PgBouncer will maintain for this database.
mydb = host=127.0.0.1 port=5432 dbname=mydb pool_mode=transaction pool_size=20
[pgbouncer]
listen_addr = 0.0.0.0 ; Address PgBouncer listens on
listen_port = 6432 ; Port PgBouncer listens on
auth_type = md5 ; Authentication method (md5, plain, trust, hba, cert)
auth_file = /etc/pgbouncer/userlist.txt ; Userlist for authentication
; Max client connections PgBouncer will accept. This is the total across all databases.
max_client_conn = 1000
; Default pool size for databases not explicitly configured.
; This is the number of server connections per database.
default_pool_size = 10
; Max server connections PgBouncer will open to a single PostgreSQL server.
; This limits the total load on the backend.
max_db_connections = 50
; Max server connections PgBouncer will open to a single database.
; This is per database, overriding default_pool_size if specified.
max_user_connections = 50
; How long a server connection can be idle before being closed.
server_idle_timeout = 600
; How long a client connection can be idle before being closed.
client_idle_timeout = 300
; Query to run when a server connection is returned to the pool.
; Essential for transaction pooling to clean up state.
server_reset_query = DISCARD ALL
; Log level (DEBUG, INFO, WARNING, ERROR)
logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
pool_mode: As discussed, transaction is the default recommendation.pool_size: This is the number of connections PgBouncer maintains to the PostgreSQL server for a given database. This should be tuned based on your PostgreSQL server's max_connections and workload. A common starting point is (CPU_cores * 2) + 1 for the PostgreSQL server, then distribute this across your PgBouncer pools. For example, if your PostgreSQL has 16 cores and max_connections = 200, you might set pool_size = 32 for your primary application database.max_client_conn: The maximum number of client connections PgBouncer will accept. This should be significantly higher than pool_size to absorb connection spikes. Set this to a value that your application expects to reach during peak load, plus a buffer.default_pool_size: Applies to databases not explicitly listed in the [databases] section.server_reset_query = DISCARD ALL: Crucial for transaction pooling to prevent prepared statement errors and other session state leakage.server_idle_timeout: Prevents PgBouncer from holding idle connections to PostgreSQL indefinitely.client_idle_timeout: Prevents idle client connections from consuming PgBouncer resources.PgBouncer itself can be a single point of failure. For high availability, multiple PgBouncer instances are typically deployed, often alongside a PostgreSQL high-availability solution (e.g., Patroni, repmgr).
The most common approach is to configure the application's database connection string with multiple PgBouncer endpoints. The client driver (e.g., libpq based drivers) will attempt to connect to the first host, and if it fails, try the next.
Example (Node.js pg connection string):
const pool = new Pool({
connectionString: 'postgresql://app_user:password@pgbouncer_host1:6432,pgbouncer_host2:6432/mydb',
});
Here, pgbouncer_host1 and pgbouncer_host2 would be separate PgBouncer instances, each configured to connect to the same PostgreSQL primary. If the primary fails and a replica is promoted, the PgBouncer instances must be reconfigured or restarted to point to the new primary.
Using a DNS entry that resolves to multiple PgBouncer IPs (round-robin DNS) or a load balancer (e.g., HAProxy, AWS NLB) in front of PgBouncer instances provides another layer of abstraction. The DNS entry or load balancer can be updated to remove unhealthy PgBouncer instances or direct traffic to a new set of instances.
HAProxy can provide health checks and load balancing for multiple PgBouncer instances.
# HAProxy configuration snippet
listen pgbouncer_cluster
bind *:6432
mode tcp
balance roundrobin
option tcp-check
server pgbouncer1 192.168.1.10:6432 check port 6432
server pgbouncer2 192.168.1.11:6432 check port 6432
This setup allows clients to connect to a single HAProxy endpoint, which then distributes connections to healthy PgBouncer instances.
"Prepared statement '...' does not exist":
pool_mode=transaction. An application is using server-side prepared statements, but the server connection is returned to the pool and reused by another client (or the same client gets a different connection) before the prepared statement is deallocated.server_reset_query = DISCARD ALL is set in pgbouncer.ini. This is the simplest and most common fix.DISCARD ALL is not an option (e.g., performance-critical prepared statements), refactor application to use client-side prepared statements or explicitly DEALLOCATE server-side statements."Too many connections" (from PgBouncer):
max_client_conn limit reached. Too many application instances or clients are trying to connect to PgBouncer simultaneously.max_client_conn in pgbouncer.ini. Ensure your PgBouncer host has sufficient resources (memory, file descriptors) to handle the increased client connections."Too many connections" (from PostgreSQL):
pool_size (or default_pool_size) is too high, or max_db_connections is too high, causing PgBouncer to open too many connections to the backend PostgreSQL server, exceeding its max_connections limit.pool_size and max_db_connections in pgbouncer.ini. Tune these values based on your PostgreSQL server's capacity and workload. Monitor PostgreSQL's active connections.Application hangs or slow connections:
pool_size is too low for the application's concurrency. Clients are waiting for a server connection to become available in the PgBouncer pool.pool_size for the affected database in pgbouncer.ini. Monitor PgBouncer's SHOW STATS output for total_wait_time and avg_wait_time. High values indicate connection starvation.Authentication failures:
auth_type mismatch, incorrect auth_file path, or incorrect credentials in userlist.txt.auth_type in pgbouncer.ini matches your setup (e.g., md5 for password-based auth). Ensure auth_file points to the correct userlist.txt and that user entries are correctly formatted: "username" "password_hash". For md5, the password hash is md5(password + username).PgBouncer not starting:
pgbouncer.log for startup errors. Ensure listen_port is not in use by another process. Validate pgbouncer.ini syntax.A high-traffic e-commerce application uses PostgreSQL and experiences frequent connection bottlenecks during peak sales events. The application heavily relies on server-side prepared statements for performance and uses advisory locks for managing inventory updates. Which PgBouncer pooling mode would be the most appropriate choice to alleviate connection overhead while minimizing application code changes and preserving existing PostgreSQL features?
Use PgBouncer when:
Application-level pools are still useful for managing connections from a single application instance to PgBouncer. PgBouncer then pools these connections to the PostgreSQL server.
Connect to PgBouncer's administrative console (usually port 6432, with a special pgbouncer database) and use SHOW commands:
SHOW STATS: Provides connection, transaction, and byte statistics.SHOW POOLS: Shows current pool status, active/waiting clients, and server connections.SHOW CLIENTS: Lists all connected clients.SHOW SERVERS: Lists all connections to backend PostgreSQL servers.These metrics should be integrated into your monitoring system (e.g., Prometheus, Datadog).
Yes. PgBouncer supports SSL/TLS for both client-to-PgBouncer and PgBouncer-to-server connections. You configure this in pgbouncer.ini using parameters like client_tls_mode, server_tls_mode, client_tls_key_file, client_tls_cert_file, etc. Ensure your certificates and keys are correctly configured and accessible.
PgBouncer is designed to be very lightweight. Its memory consumption is primarily driven by the number of active client and server connections it manages. Each connection consumes a small amount of memory for buffers and state. For thousands of connections, PgBouncer typically consumes tens to hundreds of MBs, significantly less than the equivalent number of PostgreSQL backend processes.
SET commands?In session pooling, SET commands behave as expected, as the server connection is dedicated to the client.
In transaction pooling, SET commands (e.g., SET search_path, SET timezone) are typically reset by server_reset_query = DISCARD ALL at the end of the transaction. If you need session-specific SET commands to persist across transactions, transaction pooling is not suitable, or you must re-issue the SET command at the beginning of each transaction. For most applications, DISCARD ALL is the desired behavior to prevent state leakage.
| Feature/Mode | Session Pooling | Transaction Pooling | Statement Pooling |
|---|---|---|---|
| Server Conn. Reuse | On client disconnect | On transaction commit/rollback | On statement completion |
| Prepared Statements | Fully compatible | Breaks (needs DISCARD ALL or client-side prep) | Breaks (needs DISCARD ALL or client-side prep) |
| Advisory Locks | Fully compatible | Breaks | Breaks |
| Temp Tables | Fully compatible | Breaks | Breaks |
SET Commands | Persist for client session | Reset by DISCARD ALL (default) | Reset by DISCARD ALL (default) |
| Connection Util. | Low (idle connections hold server resources) | High (server connections quickly returned to pool) | Very High (server connections returned after each stmt) |
| Application Impact | Minimal | Requires careful handling of stateful features | Requires significant application refactoring |
| Typical Use Case | Legacy apps, long-lived interactive sessions | Web apps, microservices, short-lived transactions | Niche, highly specialized workloads |
| Overhead | Low (PgBouncer itself) | Low (PgBouncer itself) | Low (PgBouncer itself) |
Clean AI CLI logs, build cron expressions, decode JWTs, and calculate chmod permissions offline.

Stress-testing PgCat, PgBouncer, and Supavisor at 10,000+ connections: transaction mode overhead, client pooling latency, and zero-downtime failover.
Read more
Analyzing PostgreSQL 17 EXPLAIN plans: optimizing memory sort buffers, HashJoin spills, bitmap index scans, and eliminating seq scans.
Read more
Diagnose and eliminate PostgreSQL table and index bloat. Master autovacuum tuning formulas, pg_repack zero-downtime compaction, and MVCC visibility maps.
Read more