Re: Extensibility of the PostgreSQL wire protocol

> On 19 Feb 2021, at 19:30, Jan Wieck <jan(at)wi3ck(dot)info> wrote:
>
> An "extended" libpq protocol could allow the pool to give clients a unique ID. The protocol handler would then maintain maps with the SQL of prepared statements and what the client thinks their prepared statement name is.

Or, the connection pooler could support a different wire protocol that has some form of client cookies and could let the client hold on to an opaque token to present back with every query and use that to route to the right backend with a prepared statement for that client (or match the appropriate cached p statement from the cache), even across client disconnections.

> Most of that would of course be possible on the pool side itself. But the internal structure of pgbouncer isn't suitable for that. It is very lightweight and for long SQL queries may never have the complete 'P' message in memory. It would also not have direct access to security related information like the search path, which would require extra round trips between the pool and the backend to retrieve it.

>
> So while not suitable to create a built in pool by itself, loadable wire protocols can definitely help with connection pooling.

I think loadable wire protocols will have a positive effect on developing more sophisticated connection poolers.

> I also am not sure if building a connection pool into a background worker or postmaster is a good idea to begin with. One of the important features of a pool is to be able to suspend traffic and make the server completely idle to for example be able to restart the postmaster without forcibly disconnecting all clients.

Agreed. Going even further, a connection pooler supporting a protocol like quic (where the notion of connection is decoupled from the actual socket connection) could help a lot with balancing load between servers and data centers, which also would not be convenient for the actual Postgres to do with present architecture. (And here, too, a pluggable wire protocol would help with keeping tabs on individual backends).

--
Damir