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.
thank you,
-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
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
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.