Understanding how many parallel workers remain from a worker pool across sessions for PostgreSQL parallel queries?

user_12974894 0
Viewed 2027

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?

postgresql-parallel-query postgresql-12 query-optimization postgresql sql
2 Answers

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';
user_64643080

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.

user_9398600
Related