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).
- Looking to understand how different PgBouncer modes work? See session mode, statement mode, and transaction mode
- Want to understand connection pooling a little better? See this section on pooling approaches
- Looking at the PgBouncer compatibility table and wanting more detail on the implications of each feature? See the next section.
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|
|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|
|Session-level advisory locks||Yes||Never|
* Startup parameters are:
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!