Thread: Multiple startup messages over the same connection
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.
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
-Vladimir Churyukin
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)
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
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
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
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
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.