Thread: Multiple startup messages over the same connection

Multiple startup messages over the same connection

From
Vladimir Churyukin
Date:
Hello,

A question about protocol design - would it be possible to extend the protocol, so it can handle multiple startup / authentication messages over a single connection? Are there any serious obstacles? (possible issues with re-initialization of backends, I guess?)
If that is possible, it could improve one important edge case - where you have to talk to multiple databases on a single host currently, you need to open a separate connection to each of them. In some cases (multitenancy for example), you may have thousands of databases on a host, which leads to inefficient connection utilization on clients (on the db side too). A lot of other RDBMSes  don't have this limitation.

thank you,
-Vladimir Churyukin

Re: Multiple startup messages over the same connection

From
Heikki Linnakangas
Date:
On 22/01/2024 21:58, Vladimir Churyukin wrote:
> A question about protocol design - would it be possible to extend the 
> protocol, so it can handle multiple startup / authentication messages 
> over a single connection? Are there any serious obstacles? (possible 
> issues with re-initialization of backends, I guess?)
> If that is possible, it could improve one important edge case - where 
> you have to talk to multiple databases on a single host currently, you 
> need to open a separate connection to each of them. In some cases 
> (multitenancy for example), you may have thousands of databases on a 
> host, which leads to inefficient connection utilization on clients (on 
> the db side too). A lot of other RDBMSes  don't have this limitation.

The protocol and the startup message are the least of your problems. 
Yeah, it would be nice if you could switch between databases, but the 
assumption that one backend operates on one database is pretty deeply 
ingrained in the code.

-- 
Heikki Linnakangas
Neon (https://neon.tech)




Re: Multiple startup messages over the same connection

From
Vladimir Churyukin
Date:
On Mon, Jan 22, 2024 at 11:43 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
On 22/01/2024 21:58, Vladimir Churyukin wrote:
> A question about protocol design - would it be possible to extend the
> protocol, so it can handle multiple startup / authentication messages
> over a single connection? Are there any serious obstacles? (possible
> issues with re-initialization of backends, I guess?)
> If that is possible, it could improve one important edge case - where
> you have to talk to multiple databases on a single host currently, you
> need to open a separate connection to each of them. In some cases
> (multitenancy for example), you may have thousands of databases on a
> host, which leads to inefficient connection utilization on clients (on
> the db side too). A lot of other RDBMSes  don't have this limitation.

The protocol and the startup message are the least of your problems.
Yeah, it would be nice if you could switch between databases, but the
assumption that one backend operates on one database is pretty deeply
ingrained in the code.
  
Yes, I suspected that's the reason why it was not implemented so far,
but what's the main problem there?
Is the issue with the global data cleanup / re-initialization after the database is changed?
Is it in 3rd party extensions that assume the same and may break?
Anything else?

-Vladimir Churyukin

 
 

Re: Multiple startup messages over the same connection

From
Vladimir Churyukin
Date:
On Mon, Jan 22, 2024 at 11:43 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
On 22/01/2024 21:58, Vladimir Churyukin wrote:
> A question about protocol design - would it be possible to extend the
> protocol, so it can handle multiple startup / authentication messages
> over a single connection? Are there any serious obstacles? (possible
> issues with re-initialization of backends, I guess?)
> If that is possible, it could improve one important edge case - where
> you have to talk to multiple databases on a single host currently, you
> need to open a separate connection to each of them. In some cases
> (multitenancy for example), you may have thousands of databases on a
> host, which leads to inefficient connection utilization on clients (on
> the db side too). A lot of other RDBMSes  don't have this limitation.

The protocol and the startup message are the least of your problems.
Yeah, it would be nice if you could switch between databases, but the
assumption that one backend operates on one database is pretty deeply
ingrained in the code.

--
Heikki Linnakangas
Neon (https://neon.tech)


Sorry to revive this old thread, just want to check on one thing:
Let's say we keep one database per backend rule, I understand at this point it would be really hard to change.
What if on a new startup message we just signal the postmaster about it, so it takes over the socket and spawns a new backend.
After that we terminate the old one. How does it sound like in terms of implementation complexity?
I guess the process of passing control from child processes to the parent could be a bit tricky for that one, but doable?
Is there anything I'm missing that can be a no-go for this?
The end goal is to minimize a large overhead for clients having to deal with a large number of connections on multi-tenant systems (say, one client deals with thousands of databases on the same database server). 

-Vladimir Churyukin

Re: Multiple startup messages over the same connection

From
Jelte Fennema-Nio
Date:
On Sat, 18 May 2024 at 23:10, Vladimir Churyukin <vladimir@churyukin.com> wrote:
> I guess the process of passing control from child processes to the parent could be a bit tricky for that one, but
doable?
> Is there anything I'm missing that can be a no-go for this?

One seriously difficult/possibly impossible thing is passing SSL
session state between processes using shared memory.