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 |
|---|---|---|
| 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 | Yes, with some gotchas** |
| 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, andstandard_conforming_strings. PgBouncer detects their changes and so it can guarantee they remain consistent for the client.** PgBouncer 1.21 introduced protocol-level prepared plan support, though there are some current gotchas which may be resolved in Postgres 17. You can find out more in the original post.
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!