4 ms·

Could you elaborate on exactly what it is? Will it mean the named elements of queries will automatically be parallelized?

by dtech 10y ago

koolba 10y ago
Postgres currently uses a single process per connection. That means at most one CPU core is used per query executing a connection. Parallel query allows breaking up query plans to allow multiple processes (and by extension cpu cores) to process a part of a query plan. The classic example is a sequential scan, say for a COUNT(*) or SUM(foo). A single process would scan through all the blocks in order. A parallel scan would split the blocks into N buckets, scan them via N separate processes, and then combine the results.
- Someone1234 10y ago
  There's nothing to complain about with this. But aren't most large database queries ultimately IO capped, rather than CPU capped? Wouldn't this just put more load on the IO and it would just bottleneck there?
  - gshulegaard 10y ago
    Yes and no. Take the following with grain of salt as it is just my personal opinion/understanding. Only 1 or 2 points are actually from challenges I have run into. Restricting every query to a single core places an artificial CPU cap on a system that would otherwise be IO capped. Disks/storage is also getting faster (http://www.geek.com/chips/new-intel-storage-is-1000-times-faster-than-your-ssd-1629656/ http://www.geek.com/chips/new-intel-storage-is-1000-times-fa...). As analytics keeps gaining popularity, the need/desire to move more and more CPU bound processing to the data storage layer is increasing. And finally, PostgreSQL's advanced query planning/partitioning support would immensely benefit from being able to split partition queries into multiple process buckets.
    - TylerE 10y ago
      And also RAM keeps getting cheaper. 64GB is absolutely nothing these days and can fit a LOT of data.
  - snuxoll 10y ago
    All depends on the workload, aggregates are more often CPU capped than IO cap in workloads we use in-house. We have a PostgreSQL server with 16GB of memory (the database total is over 80GB, but the table that is hit the most is under 3GB, the rest is just BLOB's) - more than enough to keep the relevant data in memory. Most of the time running some aggregate queries that hit the database server pretty hard are spent in USER, not IOWAIT. It's pretty annoying, because I frequently see my CPU usage at 50% (one of two cores maxed) when I know these queries could be parallelized and take advantage of the second core (or more).
  - stubish 10y ago
    Now queries can be parallelized, combining the results of several subqueries in different processes. This is a stepping stone to being able to run several subqueries on different machines. And if those different machines contain different shards of data, well, PostgreSQL has just evolved into a scale out 'Big Data' database without throwing out any relational features. Two phase commit, synchronous replication, logical replication - all the pieces are falling into place, and you can see the remaining pieces forming in extensions like BDR and Citrus Data and others.
  - snaky 10y ago
    Not anymore. >The age-old assumption that I/O is slow and computation is fast is no longer true: this invalidates decades of design decisions that are deeply embedded in today’s systems https://blog.acolyer.org/2016/01/15/non-volatile-storage/ https://blog.acolyer.org/2016/01/15/non-volatile-storage/
pkaye 10y ago
Multiple worker thread will work together to do sequential scans, joins and aggregation. Some mentions here: http://rhaas.blogspot.com/2016/03/parallel-query-is-getting-better-and.html http://rhaas.blogspot.com/2016/03/parallel-query-is-getting-... But this is just a start and there are lots more opportunities to improve as mentioned here: https://wiki.postgresql.org/wiki/Parallel_Query_Execution https://wiki.postgresql.org/wiki/Parallel_Query_Execution

Could you elaborate on exactly what it is? Will it mean the named elements of queries will automatically be parallelized?

by dtech 10y ago

Could you elaborate on exactly what it is? Will it mean the named elements of queries will automatically be parallelized?

koolba 10y ago

Postgres currently uses a single process per connection. That means at most one CPU core is used per query executing a connection. Parallel query allows breaking up query plans to allow multiple processes (and by extension cpu cores) to process a part of a query plan. The classic example is a sequential scan, say for a COUNT(*) or SUM(foo). A single process would scan through all the blocks in order. A parallel scan would split the blocks into N buckets, scan them via N separate processes, and then combine the results.

Someone1234 10y ago
There's nothing to complain about with this. But aren't most large database queries ultimately IO capped, rather than CPU capped? Wouldn't this just put more load on the IO and it would just bottleneck there?
- gshulegaard 10y ago
  Yes and no. Take the following with grain of salt as it is just my personal opinion/understanding. Only 1 or 2 points are actually from challenges I have run into. Restricting every query to a single core places an artificial CPU cap on a system that would otherwise be IO capped. Disks/storage is also getting faster (http://www.geek.com/chips/new-intel-storage-is-1000-times-faster-than-your-ssd-1629656/ http://www.geek.com/chips/new-intel-storage-is-1000-times-fa...). As analytics keeps gaining popularity, the need/desire to move more and more CPU bound processing to the data storage layer is increasing. And finally, PostgreSQL's advanced query planning/partitioning support would immensely benefit from being able to split partition queries into multiple process buckets.
  - TylerE 10y ago
    And also RAM keeps getting cheaper. 64GB is absolutely nothing these days and can fit a LOT of data.
- snuxoll 10y ago
  All depends on the workload, aggregates are more often CPU capped than IO cap in workloads we use in-house. We have a PostgreSQL server with 16GB of memory (the database total is over 80GB, but the table that is hit the most is under 3GB, the rest is just BLOB's) - more than enough to keep the relevant data in memory. Most of the time running some aggregate queries that hit the database server pretty hard are spent in USER, not IOWAIT. It's pretty annoying, because I frequently see my CPU usage at 50% (one of two cores maxed) when I know these queries could be parallelized and take advantage of the second core (or more).
- stubish 10y ago
  Now queries can be parallelized, combining the results of several subqueries in different processes. This is a stepping stone to being able to run several subqueries on different machines. And if those different machines contain different shards of data, well, PostgreSQL has just evolved into a scale out 'Big Data' database without throwing out any relational features. Two phase commit, synchronous replication, logical replication - all the pieces are falling into place, and you can see the remaining pieces forming in extensions like BDR and Citrus Data and others.
- snaky 10y ago
  Not anymore. >The age-old assumption that I/O is slow and computation is fast is no longer true: this invalidates decades of design decisions that are deeply embedded in today’s systems https://blog.acolyer.org/2016/01/15/non-volatile-storage/ https://blog.acolyer.org/2016/01/15/non-volatile-storage/

pkaye 10y ago

Multiple worker thread will work together to do sequential scans, joins and aggregation. Some mentions here: http://rhaas.blogspot.com/2016/03/parallel-query-is-getting-better-and.html http://rhaas.blogspot.com/2016/03/parallel-query-is-getting-... But this is just a start and there are lots more opportunities to improve as mentioned here: https://wiki.postgresql.org/wiki/Parallel_Query_Execution https://wiki.postgresql.org/wiki/Parallel_Query_Execution