Use pgBouncer for CockroachDB Connection Pooling (2026)

CockroachDB isn’t a traditional PostgreSQL database, so using a standard PostgreSQL connection pooler like pgBouncer requires a few twists to work effectively.

Here’s how you can set up pgBouncer with CockroachDB, focusing on what makes it different and how to get it running.

CockroachDB and pgBouncer: The Core Challenge

The fundamental issue is that CockroachDB’s wire protocol is mostly PostgreSQL compatible, but not entirely. pgBouncer expects a certain behavior from PostgreSQL servers that CockroachDB doesn’t always provide, especially around how it handles client-side cursors and transaction management. The most common failure mode you’ll see is pgBouncer: ERROR: unexpected EOF from client or pgBouncer: ERROR: client closed connection unexpectedly. This happens because pgBouncer sometimes misinterprets CockroachDB’s responses, leading it to believe the client has disconnected when it hasn’t, or vice-versa.

Setting Up pgBouncer for CockroachDB

You’ll need to install pgBouncer (e.g., sudo apt-get install pgbouncer on Debian/Ubuntu) and then configure its files: pgbouncer.ini and userlist.txt.

pgbouncer.ini Configuration

This is where the magic happens. We need to tweak several parameters to make pgBouncer play nice with CockroachDB.

[databases]
crdb = host=your_crdb_host port=26257 user=your_user password=your_password dbname=your_db

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
server_reset_query = DISCARD ALL

Let’s break down the key settings:

• crdb = host=your_crdb_host port=26257 user=your_user password=your_password dbname=your_db: This defines your connection string to CockroachDB. Replace your_crdb_host, your_user, your_password, and your_db with your actual CockroachDB connection details. CockroachDB typically runs on port 26257.
• pool_mode = transaction: This is critical. transaction mode is generally the most compatible with CockroachDB. In this mode, a connection is returned to the pool only when the client issues a COMMIT or ROLLBACK. Other modes like session (where a connection is reused for the entire client session) or statement (where a connection is returned after each statement) can lead to subtle issues with CockroachDB’s distributed nature and transaction semantics.
• server_reset_query = DISCARD ALL: This is another crucial setting. When pgBouncer returns a connection from the pool to a new client, it needs to clean up any state from the previous client. DISCARD ALL is a PostgreSQL command that resets the connection to a clean state, discarding any prepared statements, temporary tables, or session settings that might have been left behind. This is essential because CockroachDB might not handle these lingering states gracefully when reused by a different client.
• max_client_conn = 1000: Sets the maximum number of clients that can connect to pgBouncer. Adjust this based on your application’s needs.
• default_pool_size = 20: The number of connections pgBouncer will keep open to the database (CockroachDB in this case) for each database defined in pgbouncer.ini. This is a good starting point; you’ll likely need to tune this based on your workload and the number of concurrent clients.
• log_connections = 1, log_disconnections = 1, log_pooler_errors = 1: Enable verbose logging. This is invaluable for debugging. You can check these logs using sudo tail -f /var/log/postgresql/pgbouncer/pgbouncer.log (path may vary).

userlist.txt Configuration

This file stores the usernames and passwords that pgBouncer will use to authenticate clients and connect to CockroachDB.

"your_user" "your_password"

Replace "your_user" and "your_password" with the credentials your application will use to connect to pgBouncer. These credentials do not have to be the same as your CockroachDB credentials, but they do need to exist in userlist.txt. pgBouncer will then use the credentials specified in pgbouncer.ini (user=your_user password=your_password) to connect to the actual CockroachDB instance.

Starting and Testing pgBouncer

1. Reload pgBouncer configuration:

   sudo psql -h 127.0.0.1 -p 6432 -U your_user -d pgbouncer
   # Inside psql:
   SHOW CONFIG;
   RELOAD;
   EXIT;
   

   Or, if you’re managing the service:

   sudo systemctl reload pgbouncer
   

2. Connect your application: Configure your application’s database connection string to point to your pgBouncer instance: host=your_pgbouncer_host port=6432 user=your_user password=your_password dbname=crdb.

3. Monitor Logs: Keep an eye on the pgBouncer logs (/var/log/postgresql/pgbouncer/pgbouncer.log or similar) for any ERROR messages.

Why These Settings Matter

• pool_mode = transaction: Ensures that a connection is only considered "idle" and available for reuse after a transaction has been fully committed or rolled back. This aligns better with CockroachDB’s transaction handling, preventing issues where a connection might retain state from a previous transaction that could interfere with a new one.
• server_reset_query = DISCARD ALL: This is a crucial cleanup step. When a connection is pulled from the pool, DISCARD ALL resets its state. This prevents problems like lingering temporary table definitions or prepared statements from one client session affecting the next client session using the same underlying connection. CockroachDB’s distributed nature can make it particularly sensitive to such lingering session state.

The Next Hurdle

After successfully configuring pgBouncer, you’ll likely encounter issues related to CockroachDB’s specific handling of prepared statements and transaction retries, often manifesting as pq: unexpected state: transaction already committed or similar errors originating from your application’s driver when it tries to re-execute a transaction that CockroachDB has already successfully committed or aborted.