a pool for parallel worker

On Tue, Mar 11, 2025 at 5:39 AM Andy Fan <zhihuifan1213@163.com> wrote:

Currently when a query needs some parallel workers, postmaster spawns
some backend for this query and when the work is done, the backend
exit. there are some wastage here, e.g. syscache, relcache, smgr cache,
vfd cache and fork/exit syscall itself.

I am thinking if we should preallocate (or create lazily) some backends
as a pool for parallel worker. The benefits includes:

(1) Make the startup cost of a parallel worker lower in fact.
(2) Make the core most suitable for the cases where executor need to a
new worker to run a piece of plan more. I think this is needed in some
data redistribution related executor in a distributed database.

I don't want to discourage your investigation, but two things to consider:

1. In what state do existing parallel worker use cases expect to see
their forked worker processes? Any time you reuse processes in a pool,
you risk bugs if you don't set the pooled process's memory exactly
"right" -- you don't want to carry over any state from a previous
iteration. (This is easier in a thread pool, where the thread has
stack memory but not its own heap.)

2. For PQ, in particular, how does the cost of serializing +
deserializing the query itself compare to the cost of fork()ing the
process?

James

Hi,

On Tue, Mar 11, 2025 at 5:39 AM Andy Fan <zhihuifan1213@163.com> wrote:

Currently when a query needs some parallel workers, postmaster spawns
some backend for this query and when the work is done, the backend
exit. there are some wastage here, e.g. syscache, relcache, smgr cache,
vfd cache and fork/exit syscall itself.

I am thinking if we should preallocate (or create lazily) some backends
as a pool for parallel worker. The benefits includes:

(1) Make the startup cost of a parallel worker lower in fact.
(2) Make the core most suitable for the cases where executor need to a
new worker to run a piece of plan more. I think this is needed in some
data redistribution related executor in a distributed database.

I don't want to discourage your investigation, but two things to consider:

1. In what state do existing parallel worker use cases expect to see
their forked worker processes? Any time you reuse processes in a pool,
you risk bugs if you don't set the pooled process's memory exactly
"right" -- you don't want to carry over any state from a previous
iteration. (This is easier in a thread pool, where the thread has
stack memory but not its own heap.)

We do have such risk, but I want to know if it is something we can't
fix. In a normal backend, it supports different queries one by one with
"previous state", and in this case, we support different partial
plan one by one and there is no DML invoved (we don't support DML in
parallel worker yet), this may make things easier.

Generally I thought it is the duty of the user to reset them correctly.

2. For PQ, in particular, how does the cost of serializing +
deserializing the query itself compare to the cost of fork()ing the
process?

I didn't try that, however the cost serializing + deserializing should
be able to avoided with global plan cache since we have clean Plan and
PlanState division, leader just need to transfer a pointer to the partial
plan to worker, and worker build its own PlanState for it own. Due to
the lack of global plan cache for now, I did't metion this idea.

As I metioned above, my proposal does not only reduce the cost of fork,
it also want to reduce the cost of building kinds of caches (including
syscache, relcache, smgrcache, vfd and so on) repeatly, some of them may
invoves open, lseek syscall.

My purpose of this thread is wanting some feedback about what could
block a parallel worker from being reused by design. If yes, this idea
should be discard quickly. I just have a very rough idea without any
coding yet.

Thank you for showing interests on this!

--
Best Regards
Andy Fan

On Tue, 11 Mar 2025 at 17:38, Andy Fan <zhihuifan1213@163.com> wrote:

Hi,

Hi!

Currently when a query needs some parallel workers, postmaster spawns
some backend for this query and when the work is done, the backend
exit. there are some wastage here, e.g. syscache, relcache, smgr cache,
vfd cache and fork/exit syscall itself.

I am thinking if we should preallocate (or create lazily) some backends
as a pool for parallel worker. The benefits includes:

(1) Make the startup cost of a parallel worker lower in fact.
(2) Make the core most suitable for the cases where executor need to a
new worker to run a piece of plan more. I think this is needed in some
data redistribution related executor in a distributed database.

I guess the both cases can share some well designed code, like costing or
transfer the data between worker and leader.

Surely forking from the postmaster is costly.

The boring thing for the pool is it is [dbid + userId] based, which
I mean if the dbid or userId is different with the connection in pool,
they can't be reused. To reduce the effect of UserId, I think if we can
start the pool with a superuser and then switch the user information
with 'SET ROLE xxx'. and the pool can be created lazily.

I don't think this is secure. Currently, if your postgresql process
had started under superuser role, there is no way to undo that.
Consider a worker in a pool running a user query, which uses UDF. In
this UDF, one can simply RESET SESSION AUTHORIZATION and process with
anything under superuser rights.

Any comments on this idea?

--
Best Regards
Andy Fan

--
Best regards,
Kirill Reshke

Hi,

The boring thing for the pool is it is [dbid + userId] based, which
I mean if the dbid or userId is different with the connection in pool,
they can't be reused. To reduce the effect of UserId, I think if we can
start the pool with a superuser and then switch the user information
with 'SET ROLE xxx'. and the pool can be created lazily.

I don't think this is secure. Currently, if your postgresql process
had started under superuser role, there is no way to undo that.
Consider a worker in a pool running a user query, which uses UDF. In
this UDF, one can simply RESET SESSION AUTHORIZATION and process with
anything under superuser rights.

oh.. yes! "RESET SESSION AUTHORIZATION" does the very bad thing for
me. Per my testing, UDF is not essential, just a sql command can unset
the role. I am not sure why do we design like this. Currently I want
some knowledge:

(a). Is there any well known "RESET SESSION AUTHORIZATION" usage. I
didn't realize this command before. and if it is not common used, I even
want to forbid this command in our internal version.

(b). Is there any other ways to allow different user with the same
database sharing the same connection? Current "SET ROLE x" is exciting
but "RESET SESSION AUTHORIZATION" is dispointing.

--
Best Regards
Andy Fan

Andy Fan <zhihuifan1213@163.com> writes:

Hi,

The boring thing for the pool is it is [dbid + userId] based, which
I mean if the dbid or userId is different with the connection in pool,
they can't be reused. To reduce the effect of UserId, I think if we can
start the pool with a superuser and then switch the user information
with 'SET ROLE xxx'. and the pool can be created lazily.

I don't think this is secure. Currently, if your postgresql process
had started under superuser role, there is no way to undo that.
Consider a worker in a pool running a user query, which uses UDF. In
this UDF, one can simply RESET SESSION AUTHORIZATION and process with
anything under superuser rights.

oh.. yes! "RESET SESSION AUTHORIZATION" does the very bad thing for
me. Per my testing, UDF is not essential, just a sql command can unset
the role. I am not sure why do we design like this. Currently I want
some knowledge:

(a). Is there any well known "RESET SESSION AUTHORIZATION" usage. I
didn't realize this command before. and if it is not common used, I even
want to forbid this command in our internal version.

Besides the "RESET SESSION AUTHORIZATION", "SET ROLE x" does some bad
thing as well. e.g.

=# set role x; -- as superuser.
=> set role y; -- as role x. it switch to role y easily.

I can understand why we need the above behavior now. Just that I don't
want to discard this goal(sharing the same connection to the same
database amongo the different users) so quickly. I hope we have other
clean method to do it. If no, do you think will disable"re/reset
session authorization" "set role x" in the normal backend, and only
allowing them in some internal connections (like the connection in
parallel worker pool) work.

For now, it is unclear for me what is the purpose for "set role x"
command due to it can be undo so easily.

--
Best Regards
Andy Fan

On Wed, 26 Mar 2025, 11:10 Andy Fan, <zhihuifan1213@163.com> wrote:

Hi,

The boring thing for the pool is it is [dbid + userId] based, which
I mean if the dbid or userId is different with the connection in pool,
they can't be reused. To reduce the effect of UserId, I think if we can
start the pool with a superuser and then switch the user information
with 'SET ROLE xxx'. and the pool can be created lazily.

I don't think this is secure. Currently, if your postgresql process
had started under superuser role, there is no way to undo that.
Consider a worker in a pool running a user query, which uses UDF. In
this UDF, one can simply RESET SESSION AUTHORIZATION and process with
anything under superuser rights.

oh.. yes! "RESET SESSION AUTHORIZATION" does the very bad thing for
me. Per my testing, UDF is not essential, just a sql command can unset
the role. I am not sure why do we design like this. Currently I want
some knowledge:

(a). Is there any well known "RESET SESSION AUTHORIZATION" usage. I
didn't realize this command before. and if it is not common used, I even
want to forbid this command in our internal version.

So, I am assuming you are running PostgreSQL with some private
modifications, where you use this worker pool approach to speed up query
execution? If so, here is my suggestion: do not use SET ROLE design.
Currently in PG, if you did InitPostgres(...) under superuser, there is no
way back. I'm pretty sure that there always will be a way to fool postgres
and restore superuser access after SET ROLE, even if you forbit RESET
command. There was just so many CVE s about this issue, that I don't this
it is generally avoidable. The secure solution here need to do a major
rework of how it is currently done, no fast path here