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:
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.