Supabase PgBouncer Connection Pooling: Config and Tuning (2026)

Supabase PgBouncer connection pooling is a powerful tool that can dramatically improve your application’s performance and scalability, but its configuration can feel like navigating a minefield.

Here’s a typical Supabase PgBouncer setup in action. Imagine you have a busy application that needs to interact with your Supabase database. Instead of each application request opening a brand new, expensive connection to the PostgreSQL server, PgBouncer sits in front of it. It maintains a pool of ready-to-go PostgreSQL connections. When your application requests a database connection, PgBouncer hands it one from its pool. When the application is done, instead of closing the connection, it returns it to PgBouncer’s pool for the next request. This avoids the overhead of establishing new connections repeatedly.

The core problem PgBouncer solves is the significant cost associated with creating and tearing down PostgreSQL connections. Each new connection requires authentication, handshake, and resource allocation on the PostgreSQL server. For applications with many short-lived database interactions, this overhead can become a major bottleneck, consuming CPU and memory on the database server and slowing down your application. PgBouncer mitigates this by keeping a set of persistent connections open to the database and managing them efficiently.

Let’s dive into the configuration and tuning. The primary configuration file for PgBouncer is pgbouncer.ini. Key parameters you’ll encounter are:

• pool_mode: This dictates how PgBouncer manages connections. The most common and generally recommended modes are session and transaction.

  • session: A client connection is kept open for the entire duration of the client’s session. This is simpler and often works well for applications that maintain long-lived connections.
  • transaction: A client connection is pooled on a per-transaction basis. PgBouncer will acquire a database connection when a transaction begins and release it back to the pool when the transaction commits or rolls back. This is generally more efficient for applications with many short-lived transactions and is often the default for managed services like Supabase.
  • Diagnosis: To check the current mode, you can connect to PgBouncer using psql (or any PostgreSQL client) and run SHOW pool_mode;.
  • Fix: In pgbouncer.ini, set pool_mode = transaction (or session, depending on your application’s needs).
  • Why it works: The transaction mode allows PgBouncer to reuse database connections more aggressively across different client sessions, as long as those sessions are not actively in a transaction.

• max_client_conn: The maximum number of concurrent client connections that PgBouncer will accept.

  • Diagnosis: Monitor your application’s connection count and observe if clients are being denied connections or if PgBouncer is reporting connection limits. You can also check PgBouncer’s logs for "connection limit reached" messages.
  • Fix: Increase max_client_conn in pgbouncer.ini. A common starting point for a moderately busy application might be max_client_conn = 2000.
  • Why it works: This directly increases the capacity of PgBouncer to handle more incoming connections from your application servers.

• default_pool_size: The number of server connections to keep open for each pool. This is the number of actual PostgreSQL connections PgBouncer will maintain for a given database.

  • Diagnosis: Observe your PostgreSQL server’s connection count. If it’s consistently maxed out even with PgBouncer, or if you see query latency increase due to connection contention on the PostgreSQL side, your default_pool_size might be too low. Conversely, if PostgreSQL connections are idle and consuming resources without being used, it might be too high.
  • Fix: Adjust default_pool_size in pgbouncer.ini. For example, default_pool_size = 100 means PgBouncer will try to keep 100 connections open to the target database for each pool.
  • Why it works: This parameter directly controls the number of PostgreSQL connections PgBouncer actively manages, ensuring there are enough ready connections for your application’s peak demand without over-provisioning.

• max_db_connections: The maximum number of server connections for a single database. This is a hard limit on the total number of connections PgBouncer can establish to a specific database.

  • Diagnosis: Similar to default_pool_size, monitor your PostgreSQL connection count. If you’re hitting this limit and can’t scale up further, it’s time to tune.
  • Fix: Increase max_db_connections in pgbouncer.ini. For instance, max_db_connections = 2000 allows up to 2000 connections to the database.
  • Why it works: This acts as a safeguard, preventing PgBouncer from overwhelming a single PostgreSQL instance by establishing an excessive number of connections.

• server_reset_query: A query executed when a connection is returned to the pool. This is crucial for transaction mode to clean up any temporary state left by the previous transaction.

  • Diagnosis: If you observe unexpected data or state leakage between transactions, especially if using features like SET LOCAL or temporary tables, this might be misconfigured or missing.
  • Fix: Set server_reset_query = RESET ALL;. For specific cases, you might need DISCARD TEMP; or DISCARD PLANS; if RESET ALL is too aggressive or not sufficient.
  • Why it works: RESET ALL (or its more granular counterparts) ensures that any session-specific settings or temporary objects created by the previous transaction are cleaned up before the connection is reused, preventing state contamination.

• auth_type: How PgBouncer authenticates clients. Common types include md5, clear, and trust. Supabase typically uses md5.

  • Diagnosis: Connection failures with "authentication failed" messages, especially when using a password.
  • Fix: Ensure auth_type = md5 (or your specific requirement) in pgbouncer.ini and that the auth_file contains valid user credentials.
  • Why it works: This dictates the security mechanism PgBouncer uses to verify the identity of connecting clients, ensuring only authorized applications can access the database pool.

A common pitfall is misinterpreting default_pool_size and max_db_connections. default_pool_size is the target number of connections for each pool (which typically corresponds to a database in a simple setup), while max_db_connections is the absolute maximum allowed for that database. If you have multiple pools for the same database (less common), max_db_connections applies to the sum across all pools.

When tuning, always make incremental changes and monitor your system’s performance and resource utilization. Observe your PostgreSQL server’s CPU, memory, and active connection count, as well as your application’s request latency and error rates.

The next challenge you’ll likely encounter is managing connection timeouts and idle connections, leading you to explore parameters like idle_in_transaction_session_timeout and server_idle_timeout.