Re: Let's make PostgreSQL multi-threaded

On 8/6/23 15:56, Robert Haas wrote:
> Yeah, I've had similar thoughts. I'm not exactly sure what the
> advantages of such a refactoring might be, but the current structure
> feels pretty limiting. It works OK because we don't do anything in the
> postmaster other than fork a new backend, but I'm not sure if that's
> the best strategy. It means, for example, that if there's a ton of new
> connection requests, we're spawning a ton of new processes, which
> means that you can put a lot of load on a PostgreSQL instance even if
> you can't authenticate. Maybe we'd be better off with a pool of
> processes accepting connections; if authentication fails, that
> connection goes back into the pool and tries again.

    This. It's limited by connection I/O, hence a perfect use for
threads (minimize per-connection overhead).

IMV, "session state" would be best stored/managed here. Would need a way
to convey it efficiently, though.

> If authentication
> succeeds, either that process transitions to being a regular backend,
> leaving the authentication pool, or perhaps hands off the connection
> to a "real backend" at that point and loops around to accept() the
> next request.

Nicely done by passing the FD around....

But at this point, we'd just get a nice reimplementation of a threaded
connection pool inside Postgres :\

> Whether that's a good ideal in detail or not, the point remains that
> having the postmaster handle this task is quite limiting. It forces us
> to hand off the connection to a new process at the earliest possible
> stage, so that the postmaster remains free to handle other duties.
> Giving the responsibility to another process would let us make
> decisions about where to perform the hand-off based on real
> architectural thought rather than being forced to do a certain way
> because nothing else will work.

At least "tcop" surely feels like belonging in a separate process ....

    J.L.