pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWorkers in Postgres
3+ messages / 3 participants
[nested] [flat]
* Workers in Postgres
@ 2022-07-26 00:20 Teja Jakkidi <teja.jakkidi05@gmail.com>
2022-07-26 02:37 ` Re: Workers in Postgres David G. Johnston <david.g.johnston@gmail.com>
2022-07-26 02:44 ` Re: Workers in Postgres Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Teja Jakkidi @ 2022-07-26 00:20 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Admin team,
I am new to Postgres and please excuse me if these are silly questions:
1. What is a worker process exactly in Postgres?
2. is max_parallel_workers specific to a session or instance. My max_parallel_workers is set to 8. If it is specific to instance, does it mean at any point of time I can only see 8 parallel processes despite the number of transactions in the db?
3. Does number of worker process have anything to do with active connections to the database.
I have read the documentation but unable to catch up correctly. Any help from you is appreciated.
Thanks,
J. Teja.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Workers in Postgres
2022-07-26 00:20 Workers in Postgres Teja Jakkidi <teja.jakkidi05@gmail.com>
@ 2022-07-26 02:37 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2022-07-26 02:37 UTC (permalink / raw)
To: Teja Jakkidi <teja.jakkidi05@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Jul 25, 2022 at 5:20 PM Teja Jakkidi <teja.jakkidi05@gmail.com>
wrote:
> Hello Admin team,
>
> I am new to Postgres and please excuse me if these are silly questions:
>
> 1. What is a worker process exactly in Postgres?
> 2. is max_parallel_workers specific to a session or instance. My
> max_parallel_workers is set to 8. If it is specific to instance, does it
> mean at any point of time I can only see 8 parallel processes despite the
> number of transactions in the db?
> 3. Does number of worker process have anything to do with active
> connections to the database.
>
> I have read the documentation but unable to catch up correctly. Any help
> from you is appreciated.
>
>
I agree that some improvement here seems warranted. At minimum the setting
description should be linking to the main documentation for the feature:
https://www.postgresql.org/docs/current/bgworker.html
Plus, that chapter seems to only think that these workers are going to be
interacted with by people writing custom code, not those wishing to manage
a limited system resource in the face of parallelism.
However, the second question is answered by looking at what
max_worker_processes say:
"Sets the maximum number of background processes that the system can
support."
and
"This parameter can only be set at server start."
Any per-session parameter would usually be changeable within a given
session. This one is a DBA setting to say how many resources are to be
consumed by the server.
The nature of the background worker's task determines whether it consumes
an available connection slot. Parallel workers do not as the leader
process already has one assigned to it for the team. See the first link.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Workers in Postgres
2022-07-26 00:20 Workers in Postgres Teja Jakkidi <teja.jakkidi05@gmail.com>
@ 2022-07-26 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2022-07-26 02:44 UTC (permalink / raw)
To: Teja Jakkidi <teja.jakkidi05@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Teja Jakkidi <teja.jakkidi05@gmail.com> writes:
> 1. What is a worker process exactly in Postgres?
It's a background process that can be started if the planner decides
that a particular query would benefit from parallelization. If so,
the original session process (the "leader") cooperates with one or
more temporarily-spawned workers to perform the query.
> 2. is max_parallel_workers specific to a session or instance. My max_parallel_workers is set to 8. If it is specific to instance, does it mean at any point of time I can only see 8 parallel processes despite the number of transactions in the db?
Per instance. There is a different knob max_parallel_workers_per_gather
that gives you some control over how many workers a given leader will
try to use.
Before you say "oh, I've got max_connections set to 100, so I'd better
raise max_parallel_workers to a few hundred" ... don't do that unless
you've got a few hundred CPUs in the machine. While session processes
might be sitting around doing nothing much except waiting for the next
query, workers generally are busy throughout their lifespan. So it's
counterproductive for max_parallel_workers to be more than roughly the
number of available CPUs --- setting it higher just encourages the
system to create parallel workers that it can't use effectively.
> 3. Does number of worker process have anything to do with active connections to the database.
See above.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-07-26 02:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-26 00:20 Workers in Postgres Teja Jakkidi <teja.jakkidi05@gmail.com>
2022-07-26 02:37 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-26 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox