Alright, let’s pull back the curtain on PgBouncer. This isn’t some magical black box; it’s a remarkably clever, single-threaded proxy written in C. Its entire reason for being is to be the gatekeeper between your legion of application threads and your finite pool of precious PostgreSQL connections. Think of it as a bouncer at an exclusive club—it doesn’t do the dancing (that’s the database’s job), but it makes sure the right number of people are on the dance floor at once so nobody gets trampled.

Its architecture is elegantly simple. You, the application, connect to PgBouncer’s virtual port. PgBouncer then takes your connection and maps it to one of its actual, persistent connections to the real PostgreSQL backend. The magic is in how it manages this mapping, which brings us to the three pooling modes. You absolutely must understand these; getting them wrong is the fastest way to create a new, more confusing class of problems.

The Three Pooling Modes: Session, Transaction, and Statement

This is the heart of it. The mode you choose dictates how long PgBouncer holds onto a backend connection for you.

  • Session pooling: The most straightforward. When your application connects, PgBouncer grabs a backend connection and gives it to you. It’s yours until you disconnect. This is the least efficient mode because connections are held for a long time, but it’s 100% compatible with everything. Use this if you have prepared statements that live outside transactions or if you’re terrified of changing anything.

  • Transaction pooling: This is where the big wins are, and also where the dragons live. PgBouncer lends you a backend connection for the duration of a single transaction. As soon as you issue COMMIT or ROLLBACK, the connection is yanked back into the pool and given to the next client. This is wildly more efficient. The huge “but” is that anything that has state across transactions will break horribly. This includes:

    • Prepared statements (they are destroyed when the connection is recycled).
    • Temporary tables (they are, by definition, dropped at the end of a session).
    • Using SET to change connection-level parameters (they’ll be reset).
    • Listen/Notify (the LISTEN connection is lost after the first transaction).

  • Statement pooling: An even more aggressive mode where the connection is recycled after every single statement, with autocommit forced on. This is for the brave, the foolish, or those with extremely simple workloads. I’ve almost never seen a valid use case for it that isn’t better handled by transaction pooling. Tread carefully.

For probably 95% of you, transaction mode is the goal. Your job is to ensure your application and frameworks don’t rely on the aforementioned footguns.

Dissecting the Configuration File: The Important Bits

The config file (pgbouncer.ini) is refreshingly simple. Let’s skip the boilerplate and hit the critical parts. First, you define how to talk to the real database.

[databases]
# Format is: mydbname = host=127.0.0.1 port=5432 dbname=actualdbname
mydb = host=/var/run/postgresql dbname=myproductiondb
mylogdb = host=logdb.example.com port=5432 dbname=logdb

[pgbouncer]
; Where does PgBouncer itself listen for connections?
listen_addr = 127.0.0.1
listen_port = 6432

; The core settings - size your pool correctly!
; This is the max number of connections PgBouncer will allow.
max_client_conn = 1000
; This is the max number of connections it will open to PostgreSQL itself.
; This is your lever to prevent overwhelming the database.
default_pool_size = 100

; How PgBouncer behaves when all connections are busy.
; 'wait' means the client will wait for a connection to free up.
; 'immediate' means it'll get an error instantly. I prefer 'wait' to avoid
; throwing errors back to the app, but it can lead to timeouts there.
pool_mode = transaction
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

The server_reset_query and ignore_startup_parameters Lifesavers

These two settings are your best friends for maintaining stability in transaction mode.

server_reset_query is what PgBouncer runs on a backend connection before handing it to a new client. DISCARD ALL is the nuclear option—it resets everything (prepared statements, temporary objects, session state). It’s perfect for ensuring no client inherits the previous client’s garbage. If you find the overhead of DISCARD ALL is too high, you can try RESET ALL followed by SET SESSION AUTHORIZATION DEFAULT, but DISCARD ALL is the safer bet.

ignore_startup_parameters is brilliantly pragmatic. When a client connects, it often sends a bunch of optional parameters (like extra_float_digits). In transaction mode, PgBouncer can’t guarantee the same backend connection for the whole session, so these parameters can’t be set reliably. This setting tells PgBouncer to just strip them out, preventing a whole class of pointless errors. You almost always want extra_float_digits in there.

The Userlist File: A Necessary Evil

PgBouncer needs to authenticate users itself before it can proxy them. This means you need to give it a list of usernames and password hashes. Yes, it’s a pain. You can generate this file by querying PostgreSQL itself and then keeping it in sync (a Puppet/Ansible/Chef template is your friend here).

# On your PostgreSQL primary, run:
echo "SELECT usename, passwd FROM pg_shadow" | psql -qAt > /etc/pgbouncer/userlist.txt

Just remember that if you change a password in PostgreSQL, you now have to change it in two places. Automation isn’t optional here; it’s mandatory unless you enjoy 3 AM pages.

The final piece of advice? Test your pooling mode choice thoroughly under load. A misconfigured PgBouncer can introduce bizarre, intermittent bugs that are a nightmare to debug. But when tuned correctly, it’s the piece of infrastructure that quietly makes your entire application faster and more resilient. And that’s no joke.