Hi,
On my PG11 I have set it to 64 upon setup and it propogated to postgresql.auto.conf and is set after restart. I've upgraded to PG12 since then, and parameter is read from postgresql.auto.conf correctly and is displayed via SHOW (just checked on 12beta3).
I also spent some time trying to get a plan that will give me 32 workers. Largest I ever got without taking a hammer was 16, which is half of available cores, or all non-HT ones. I still haven't found a way to set costs and limits to load all the system with a query.
ALTER TABLE ... SET (parallel_workers=32); is currently my most favorite hammer. I set max_worker_processes to 512 and letting OS scheduler resolve the hours when four queries run 128 CPU-bound processes on 32-core machine, it's not as good as if the limits were adjusted dynamically after the query start but much better than running a second query with just 1 worker even after first one finishes.
Try this:
alter system set max_parallel_workers = 20;
and restart the system.
max_parallel_workers is still 8, according to both SHOW and
pg_controldata, nor can you launch more than 8 workers.
Even odder, if you just do
regression=# set max_parallel_workers = 200;
SET
regression=# show max_parallel_workers;
max_parallel_workers
----------------------
200
(1 row)
which should certainly not happen for a PGC_POSTMASTER parameter.
We seem to have an awful lot of mechanism that's concerned with
adjustments of max_parallel_workers, for something that apparently
might as well be a compile-time #define ... so I assume it's supposed
to be changeable at restart and somebody broke it. But it's not
working as I'd expect in any branch from 10 onwards.
regards, tom lane
--