In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
Add a comment
|
4 Answers
No, for versions of PostgreSQL prior to v9.6. Please see the PostgreSQL FAQ: How does PostgreSQL use CPU resources?
The PostgreSQL server is process-based (not threaded). Each database session connects to a single PostgreSQL operating system (OS) process. Multiple sessions are automatically spread across all available CPUs by the OS. The OS also uses CPUs to handle disk I/O and run other non-database tasks. Client applications can use threads, each of which connects to a separate database process.
Since version 9.6, portions of some queries can be run in parallel, in separate OS processes, allowing use of multiple CPU cores. Parallel queries are enabled by default in version 10 (max_parallel_workers_per_gather), with additional parallelism expected in future releases.
-
2Cant believe in this modern age a design would favor heavy load process context switching to implement multitasking instead of using light weight high performance multi-threading. Thank you for the clarification. This explains why our systems suffer under load now when we switched to Postgres based upon some apparently bad advice.– CerniukMar 31, 2020 at 15:48
-
Multithreading is harder to write and reason about, shared memory and locking between threads can be troublesome. I don't think multithreading is necessarily inherently better. Mar 4 at 14:18
PostgreSQL 9.6+ onwards, would start to see Parallel-Query finally coming to PostgreSQL.
For e.g. Concepts like Parallel Scan / Parallel Join / Parallel Aggregates are now already baked in, with more to come soon.
What's really exciting is that there are reports confirming near-linear speed-up in some cases, which is pretty impressive!
No, but there is a workaround. :)
I found parsel (parallel select) PL/pgSQL function, which splits your query based on the primary key, then connects to the database via the dblink extension and waits for all subqueries.
The author also wrote an article about this function.
-
-
1
No. Each connections spawn a separate process on server.
You can "emulate" some parallelism using a threaded procedural language like pljava. Create a java procedure(function) that launches several threads and create the output result using several workers. The backend is syncronized so each worker can update the output asynchronous.
Java has good support for thread coordination/cooperation.
As examples, this would be nice for CPU intensive operations or network length operations.
