Alright, let’s talk about the mode you’ll almost certainly use: Transaction Mode. This is PgBouncer’s default for a reason, and it’s the workhorse that makes the whole endeavor worthwhile. The concept is beautifully simple: we hand you a dedicated PostgreSQL connection for the entire life of a single transaction. As soon as you issue a COMMIT or ROLLBACK, that connection is yanked right back into the pool, ready for the next poor soul’s transaction. It’s a brutally efficient rental system.

Think of it like this: in Session Mode, you’re leasing an apartment. You get the keys and you can leave your weird collectibles on the shelf for weeks. In Transaction Mode, you’re renting a meeting room. You get it for exactly the length of your meeting; when you’re done, the whiteboard gets wiped clean and the next group files in. No mess, no fuss, maximum turnover.

Why Your Connection Can’t Remember Your Temp Tables

This is the single biggest “gotcha” and the core reason Transaction Mode exists. Because the connection is reset between transactions, anything that is tied to the session is annihilated. This includes:

  • TEMPORARY TABLEs (poof, gone after commit)
  • PREPAREd statements (you’ll get a “prepared statement already exists” error if you try to reuse the name in a new transaction on a different connection)
  • Session-level variables set with SET (e.g., SET timezone TO 'UTC';)
  • The contents of the LISTEN/NOTIFY channel

This isn’t a bug; it’s the fundamental trade-off. We sacrifice session state for massive connection scalability. If your application litters the place with temp tables and prepared statements, it’s going to have a very bad time. You need to architect around this constraint.

The Nasty Little Secret of DEFAULT_TRANSACTION_ISOLATION

Here’s a fun one the PostgreSQL docs might not shout about. Let’s say you have this in your postgresql.conf: default_transaction_isolation = 'repeatable read'

You connect to PgBouncer, which happily gives you a connection from the pool that was used for a READ COMMITTED transaction moments ago. You then issue:

BEGIN;
SHOW transaction_isolation;

What do you get? You’ll get repeatable read. Wait, what? The connection’s actual isolation level is still READ COMMITTED! PostgreSQL is lying to you.

Why? Because SHOW doesn’t report the live connection state; it reports the defaults from when the session began. But the session began when the connection was first established by PgBouncer, long before your transaction got it. The connection’s true runtime isolation level is whatever was left by the previous user. This is a fantastic way to introduce subtle, maddening concurrency bugs.

The fix is non-negotiable: You must explicitly set your transaction state at the start of every transaction. Never rely on defaults. Your code should be doing this:

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Be EXPLICIT
-- ... your queries ...
COMMIT;

Or, use the SET command outside the transaction if it’s for the entire session (but remember, in Transaction Mode, “session” is just your one transaction!). This practice makes your intent clear and ensures correctness regardless of the pool’s state.

The Prepared Statement Pitfall (And How to Avoid It)

I mentioned this above, but it deserves its own horror story. You can’t use PREPARE in the classic sense. If you prepare a statement named get_user in one transaction, and the next transaction gets a different physical connection, that new connection has no idea what get_user is. You’ll get an error.

The solution is to use a [pool_mode](https://www.pgbouncer.org/config.html#pool_mode) that doesn’t use named prepared statements. Fortunately, the PostgreSQL protocol has an option for this: unnamed prepared statements. Most clever drivers (like libpq in Python’s psycopg2) will automatically use unnamed prepared statements if you use parameterized queries correctly. For example, in Python:

# This is GOOD. Psycopg2 will use an unnamed prepared statement.
cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# This is BAD. You're trying to use a named prepared statement across transactions.
cur.execute("PREPARE get_user AS SELECT * FROM users WHERE id = $1")
# ... after commit ...
cur.execute("EXECUTE get_user(%s)", (user_id,)) # This will likely fail!

Check your driver’s documentation. If it’s a good citizen, it will handle this for you seamlessly. If not, you’ll need to stick to simple, unprepared queries.

Best Practices for a Smooth Life

  1. Be Explicit: Always set your ISOLATION LEVEL, READ WRITE/READ ONLY mode, and other critical settings at the start of your transaction. It’s a few extra milliseconds of overhead for a world of reliability.
  2. Test Like You Mean It: Before you deploy PgBouncer to production, hammer your application with load in a staging environment. The bugs you’ll find will almost always be related to sneaky session state assumptions.
  3. Check Your Driver: Make absolutely certain your database client library supports and uses unnamed prepared statements. This is the linchpin of making ORMs and other complex tools work with Transaction Mode.
  4. Embrace the Reset: server_reset_query in your pgbouncer.ini is your final line of defense. It’s a command run every time a connection is returned to the pool. The default (DISCARD ALL) is nuclear and can be expensive. For Transaction Mode, you can often get away with a lighter RESET ROLE; RESET transaction_isolation; RESET search_path; or similar to clear the most common gotchas without the full DISCARD ALL overhead.