My previous post, PgBouncer is important, useful, and fraught with peril, was a deep dive into Postgres feature compatibility with different modes of PgBouncer.

I’m happy with how it came out and it was well received. I think it is the most comprehensive guide to Postgres/PgBouncer compatibility that exists. But it might be a daunting read for some (9k words 😅), and the name doesn’t clearly convey what it offers - I want it to be easily found when searching for PgBouncer/Postgres pooling questions, and be quickly digestible on the fly.

Below is a list of topics you may be looking for more information about. While PgBouncer is the most popular option, most of it applies to any of the Postgres pooling options available (Supavisor, PgCat, Odyssey, etc).

SQL feature map for pooling modes

Below is each compatibility table feature linked to the original post. If there isn’t a link, it means the feature works the same way with or without PgBouncer.

Feature Session pooling Transaction pooling
Startup parameters* Yes Yes
SET/RESET statement_timeout Yes Never
SET/RESET lock_timeout Yes Never
LISTEN/NOTIFY Yes Never
WITHOUT HOLD CURSOR Yes Yes
WITH HOLD CURSOR Yes Never
Protocol-level prepared plans Yes No**
PREPARE / DEALLOCATE Yes Never
ON COMMIT DROP temp tables Yes Yes
PRESERVE/DELETE ROWS temp tables Yes Never
Cached plan reset Yes Yes
LOAD statement Yes Never
Session-level advisory locks Yes Never

* Startup parameters are: client_encoding, datestyle, timezone, and standard_conforming_strings. PgBouncer detects their changes and so it can guarantee they remain consistent for the client.

** It is possible to add support for that into PgBouncer.

I’m biased, but I think it’s a pretty good read in entirety as well. There’s more sections on other gotchas, community pooling suggestions, and a look at work improving Postgres internals to lighten the need for poolers.

Thanks for reading, and I hope it answers your questions!