3

Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the query should be broken up and sent to as many connections as there are cores.

The documentation states that CTEs are executed "concurrently", but none linked say explicitly in effect that "CTEs will use all available cores to process any parallel sub-statements and/or the main query".

Do CTEs always use all available cores for any parallel tasks? If so, are there any caveats aside from what could be reasonably assumed from chaining with RETURNING?

7
  • 1
    Postgres does not run any statement multithreaded. That includes CTEs The "concurrently" is probably only there to indicated that there is no inherent order how they are processed
    – user1822
    Commented Dec 21, 2014 at 9:18
  • Multithreaded processing is something you do NOT want in the database(under normal circumstances). Databases goal is to protect data and also to provide reasonable resource sharing. Multithreaded processing would give all the servers resources to a single user, which is something you usually you do not want to happen.
    – ibre5041
    Commented Dec 21, 2014 at 9:34
  • @ibre5041 What if there's only one core?
    – Jim Bob
    Commented Dec 21, 2014 at 9:53
  • Even then you have same CPU time and some disk IOs to be split up among multiple users. If you spend it to perform a single statement then other users will have to wait.
    – ibre5041
    Commented Dec 21, 2014 at 17:26
  • 2
    @ibre5041 Huh? There's nothing at all wrong with careful and appropriate use of threading if it can be used to parallelise CPU-heavy portions of queries across multiple cores or to help with I/O wait concurrency in a blocking I/O model. PostgreSQL doesn't support any kind of concurrency within a single session/query, but it'd be nice if it did, as not everyone's workload is pure OLTP (large numbers of fairly simple selects and DML). Commented Dec 22, 2014 at 2:05

1 Answer 1

5

Postgres currently does not use parallelism when executing a single query. The entire execution engine is single threaded for each query (you can of course still execute multiple queries in parallel). This lack of "intra query parallelism" is one of the great drawbacks of running Postgres or MySQL. Basically, it makes those engines unfit for purpose on large datasets.

To get multi-threaded Postgres execution, you either need to split up the query into multiple queries yourself or run a Postgres compatible variant like Amazon Redshift or Greenplum (the former has added intra query parallelism to the code base, the latter does the splitting for you).

Whenever you feel the need to run a parallel query, first ask yourself: Could I add indexes that would make this fast enough without parallelism? If that answer is "Yes", pursue that option first. Very often though, you need brute force. And when it comes to brute force, vanilla Postgres is unfortunately not a particularly good database engine.

4
  • This is no longer accurate as of PostgreSQL 9.6.
    – kyrias
    Commented Jan 25, 2023 at 15:48
  • How so? The "parallelism" added there is so weak it still does not hold a candle to a fully parallel engine. Commented Jan 26, 2023 at 10:57
  • How good it may or may not be doesn't change the fact that the answer is now factually incorrect.
    – kyrias
    Commented Jan 26, 2023 at 14:26
  • The entire engine isn't multi threaded yet. Only the operators that have been explicitly made parallel. So I think its neither correct nor incorrect at this point Commented Feb 3, 2023 at 11:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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