Why we built yet another Postgres connection pooler

Example: legacy client A connects to MySQL via the bouncer and says 'I want all of our conversations to use latin-1, not utf-8'. This changes the character set that MySQL parses queries with and returns responses in. The legacy client does some queries and then disconnects.

Now a new client connects to MySQL, and the bouncer just assigns it to the still-open connection from before. The new client is fully UTF-8 compatible and since this is the default for our database it doesn't explicitly say so; it just assumes that UTF-8 is the way to go. Unfortunately, the database server is still thinking in latin-1, meaning that if this new client sends UTF-8 data it will be parsed as latin-1; latin-1 is a subset of UTF-8, meaning that queries will actually work fine unless they need to use a character outside of latin-1, in which case they will get an error, or corrupted data, from the server.

The only solutions around this are:

1. Ensure that every client is using the same settings; if your database is for a single app that uses the same ORM, then this is automatic.

2. Ensure that every client is always explicit about everything it might need to change e4very time, so that every UTF-8 client explicitly sets UTF-8 connections even when that's the default; clients that need utf8mb4 ask for it explicitly and clients that can't handle it ask for something else. One way of ensuring this happens is to configure the server (or the bouncer) to use defaults which are not valid for anyone, or which are going to cause errors frequently and not rarely (e.g. setting the default character set to 7-bit swedish, which would cause frequent errors).

3. Use a bouncer which can either disallow these changes or detect and revert them after the original client has disconnected. I'm not sure if this exists for MySQL at least.

4. Use separate bouncers for each application that might be different (extension of #1); in other words, instead of having a bouncer or set of bouncers for each pool of database servers, you have them for each application; your web app gets one, your legacy reporting tool gets one, your ODBC connector gets one, and so on.

It's kind of a huge mess in theory; in practice, a lot of installations fall into the #1 case so it never matters, but that makes the occasional instance where it does matter extremely difficult to debug.

in pgbouncer the connection is reset via a customisable command [0] which should reset the connection to a clean state.

[0] https://www.pgbouncer.org/config.html#server_reset_query

We show that it's possible to come close without breaking the DB or the app, but I suspect, it's not quite yet at the level you'd expect from a _durable_ work queue, e.g., Kafka. Not going to replace that one anytime soon.

The notify/listen fix and automatic query routing to read replicas and auto sharding might bringt Postgres finally closer to vitess