In SQL Server we can have many worker threads per CPU core. Does anyone know if the same is true in PostgreSQL and can point me at a reference? (assume version 11).

The background is that we have some OLTP workloads currently in SQL Server that are being migrated to PostgreSQL. I'm wondering if there is a risk of running out of threads if parallelism is enabled and connections being refused (which can happen on SQL Server when we run out of threads), and whether I should recommend that parallelism is disabled by default.

  • "I should recommend that parallelism is disabled by default." - the default configuration for that is very conservative, so I don't think you need to disable it. Not sure what you mean with "running out of threads". I am not aware of any hard limit that you can "run out" on. Obviously if your server only has 16 CPUs and you are running 30 queries with 4 workers each, this isn't going to make things faster – a_horse_with_no_name Oct 25 '19 at 11:15

PostgreSQL does not use threads, it uses coordinated single-thread processes. It will not use more than max_parallel_workers, system-wide, at one time. If it would exceed that, the queries will not error out, they simply run with fewer worker processes than they "wanted".

If the number of connections you have trying to run queries at the same time is always greater than your number of CPUs, then you probably might as well turn parallelism off. OLTP usually are not great candidates for parallel execution anyway.

  • Thanks, that's very useful. If all the CPUs are currently in use and/or the current number or processes is equal to max_parallel_workers, and a new query is issued, will that simply wait until a CPU/process is free? – Matthew McGiffen Oct 25 '19 at 15:24
  • PostgreSQL doesn't know anything about CPUs being in use, max_parallel_workers is the only thing it knows. In that case, the query will execute with no workers, i.e. the leader will do everything itself. When PostgreSQL reports 2 workers, that means it was parallelized 3 ways, as the leader also participates. A bit confusing until you are used to it. – jjanes Oct 25 '19 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.