2

Let's say I have a pool of 4 parallel workers in my PostgreSQL database configuration. I also have 2 sessions.

In session#1, the SQL is currently executing, with my planner randomly choosing to launch 2 workers for this query.

So, in session#2, how can I know that my pool of workers has decreased by 2?

baceda9220
  • 41
  • 1
  • 4

2 Answers2

3

You can count the parallel worker backends:

SELECT current_setting('max_parallel_workers')::integer AS max_workers,
       count(*) AS active_workers
FROM pg_stat_activity
WHERE backend_type = 'parallel worker';
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Let's say that an SQL query reserves 2 parallel workers. Will the max_parallel_worker variable decrease by 2 until the SQL query is finished executing? The parameter that does this - if it even exists at all - is what I am looking for. – baceda9220 Mar 04 '20 at 01:02
  • There is no parameter for that. I didn't read up in the source; perhaps there is a global variable that you could access from a C function, but certainly not from SQL. What's wrong with a query? – Laurenz Albe Mar 04 '20 at 06:46
  • simply subtract the two numbers my query returns, and you have your result. – Laurenz Albe Mar 04 '20 at 07:18
  • I had tested this on a Postgres 10.7 **Aurora** instance. Interestingly, it reported "background worker" instead of "parallel worker" for parallel workers. (Verified parallelism with `EXPLAIN ANALYZE`.) Testing the same in a pure Postgres instance, it reported "parallel worker" as expected. May be due to Aurora implementing parallelism differently behind the scenes. – Erwin Brandstetter Mar 04 '20 at 11:49
  • 2
    @ErwinBrandstetter Thanks for looking. Well, using closed source PostgreSQL forks has certain disadvantages. – Laurenz Albe Mar 04 '20 at 12:14
0

Internally, Postgres keeps track of how many parallel workers are active with two variables named parallel_register_count and parallel_terminate_count. The difference between the two is the number of active parallel workers. See comment in in the source code.

Before registering a new parallel worker, this number is checked against the max_parallel_workers setting in the source code here.

Unfortunately, I don't know of any direct way to expose this information to the user.

You'll see the effects of an exhausted limit in query plans. You might try EXPLAIN ANALYZE with a SELECT query on a big table that's normally parallelized. You would see fewer workers used than workers planned. The manual:

The total number of background workers that can exist at any one time is limited by both max_worker_processes and max_parallel_workers. Therefore, it is possible for a parallel query to run with fewer workers than planned, or even with no workers at all.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks; this is quite similar to what I am looking for. Just for clarification, if I issue a query reserving 2 workers, then when I do `parallel_terminate_count` - `parallel_register_count`, I shall see 2? I suppose that is what it says but I do not really understand the purpose of `parallel_terminate_count`. – baceda9220 Mar 04 '20 at 01:11
  • @baceda9220: Reversed: `parallel_register_count - parallel_terminate_count` - it's the number of registered (used) parallel workers minus the number of terminated. But those are internal variables in the C code. I don't know of a way to expose them to the user. Laurenz has a more practical approach. – Erwin Brandstetter Mar 04 '20 at 08:23