Slashdot Mirror

← Back to Stories (view on slashdot.org)

PostgreSQL Getting Parallel Query

Posted by samzenpus on from the new-and-improved dept.

New submitter iamvego writes: A major feature PostgreSQL users have requested for some time now is to have the query planner "parallelize" a query. Now, thanks to Robert Haas and Amit Kapila, this has now materialized in the 9.6 branch. Robert Haas writes in his blog entry that so far it only supports splitting up a sequential scan between multiple workers, but should hopefully be extended to work with multiple partitions before the final release, and much more beside in future releases.

50 of 83 comments (clear)

  1. Re:This is a troll. Respond appropriately. by viperidaenz · · Score: 1

    Not a dual core Pentium D or Athlon X2 from 2005?

  2. Re:This is a troll. Respond appropriately. by Tablizer · · Score: 1

    Maybe what they meant is that the bottleneck should be disk, RAM, bus, and/or network I/O for the vast majority of sub-queries if "done right". In other words, a single core should be able to computationally sift rows fast enough to keep up with row scans from multiple "sources" for up to say 8 sources, which may be the max practical number of sources.

    However, I imagine that a complex or "mathy" equation in a query could make it be CPU intensive. But maybe that's relatively rare.

    --
    Table-ized A.I.
  3. Re:This is a troll. Respond appropriately. by Anonymous Coward · · Score: 1

    *Your

  4. Re:This is a troll. Respond appropriately. by Tablizer · · Score: 3, Funny

    You mean 500 johns or 500 joins? Sounds painful either way.

    --
    Table-ized A.I.
  5. This improvement a plus by kboodu · · Score: 2

    This will greatly improve the already impressive PostgreSQL database engine and help it compete against the more well-known Enterprise Relational Database Engines at a much better price point (free).

    1. Re:This improvement a plus by kimanaw · · Score: 1

      Hmmm, if so, then there are probably at least 6 other RDBMS's - mostly owned by deep pocket corps, and actually based on Pg - that ORCL could sue and actually hope to collect a check. Given Pg's open source nature, suing them would at best remove the code, but not contribute a single dime for that new dock on Larry's island.

      --
      007: "Who are you?"
      Pussy: "My name is Pussy Galore."
      007: "I must be dreaming..."
  6. New features every few months by rbrander · · Score: 5, Informative

    Just a few months back, lwn.net had a longish story on PostgreSQL. They were scoring a victory with the "UPSERT" command addition in 9.5, which with speed updates old records, OR inserts a new one, if none. A big feature on your commercial databases. Apparently, PostgreSQL's biggest worry lately is that it has so many developers adding cool new features that there's some resource lacks maintaining and cleaning the base code. (Possibly unfair oversimplification of lwn.net story.)

    I discovered PostgreSQL to get a free geodatabase for mapping, with the PostGIS plug-in...the open plug-in architecture being one of the greatest things about a FLOSS database. After nearly 25 years with Oracle and thinking everything else was a toy by comparison, PG blew me away. Amazing features, high performance, reliable. It's an amazing project, and this news is both impressive and unsurprising.

    1. Re:New features every few months by Kjella · · Score: 1

      This one on the other hand seems like baby steps:

      One rather enormous limitation of the current feature is that we only generate Gather nodes immediately on top of Parallel Seq Scan nodes. This means that this feature doesn't currently work for inheritance hierarchies (which are used to implement partitioned tables) because there would be an Append node in between. Nor is it possible to push a join down into the workers at present. (...) With things as they are, about the only case that benefits from this feature is a sequential scan of a table that cannot be index-accelerated but can be made faster by having multiple workers test the filter condition in parallel.

      No partitioning, no joins, right now the only thing you can speed up is a simple table scan where you can't/won't use an index. This is more "proof of concept" parallelism than a useful feature right now. I guess in a release or two this will be a big thing.

      --
      Live today, because you never know what tomorrow brings
    2. Re:New features every few months by guruevi · · Score: 2

      The biggest issue with parallelism is that a lot of stuff can't be parallelized in a way that makes sense. The way it is done (dispatching and gathering nodes) only makes sense if the query takes a really long time, otherwise there is a lot of overhead that destroys any type of speedup and could actually make everything else slower. Typically multi-threading in databases is done to speed up multiple independent queries, not a single query.

      --
      Custom electronics and digital signage for your business: www.evcircuits.com
    3. Re:New features every few months by phantomfive · · Score: 4, Insightful

      PostgreSQL has one of the best-commented and cleanest code-base I've ever reviewed. FWIW

      --
      "First they came for the slanderers and i said nothing."
    4. Re:New features every few months by Kjella · · Score: 1

      The biggest issue with parallelism is that a lot of stuff can't be parallelized in a way that makes sense. The way it is done (dispatching and gathering nodes) only makes sense if the query takes a really long time, otherwise there is a lot of overhead that destroys any type of speedup and could actually make everything else slower. Typically multi-threading in databases is done to speed up multiple independent queries, not a single query.

      Define "really long time", in the example he's running a query that takes less than a second and gets it down to <250ms. Sure, it's not useful for transaction processing but I got many queries running on millions of rows where anything from a minute to an hour is more like it and milliseconds are peanuts. I would think most people have at least some reports that would benefit.

      --
      Live today, because you never know what tomorrow brings
    5. Re:New features every few months by Anonymous Coward · · Score: 1

      It's like the FreeBSD of RDBMS!

    6. Re:New features every few months by short · · Score: 1

      SQLite already has that as "INSERT OR REPLACE", MySQL as "REPLACE INTO" etc.

    7. Re:New features every few months by Anonymous Coward · · Score: 1

      Having worked with some of the core committers, and attended conferences (and dinner) with others, I'm not the least bit surprised. I consider myself a pretty competent developer, and every single one of them made me feel like a total rookie. Also, none of them had the chest thumping ego that you see from the heads of a lot of other opensource projects. It was refreshing.

  7. I miss pgsql by pi_rules · · Score: 1

    I only ever got to develop a single project on pgsql and I regret that. This was back in 2001. MySQL was pretty immature at the time but had the enormous install base. I went with PostgreSQL because it was more mature. It never let me down. The deployment went fine, it ran great, customer used it on and off for about 6 years and then it was just no longer needed.

    Fast forward to 2011, ten years later, and now I'm running the show and developing a point of sale for the family business I'm in and I run with MySQL just because I'm so much more familiar with it. That and the master-master replication suite from Percona. MySQL is way better than it was in 2001 but still lacking here and there. I still question that choice. I will eventually migrate off MySQL but I don't know if it'll be MariaDB or PostgreSQL.

    1. Re:I miss pgsql by mysidia · · Score: 1, Interesting

      That and the master-master replication suite from Percona.

      I think that is a good reason to pick MySQL.

      As much as I like Postgres..... it seems to be a heck of a lot easier to do replication with MySQL and put together a highly-survivable system.

      I'm not even sure how to even start to go about doing it with Postgres.... although in the past; I have had a Cold/Warm standby Postgres with Slony-L based replication; It was quite frankly, a PITA.

    2. Re:I miss pgsql by theshowmecanuck · · Score: 1

      I agree. It is the biggest issue for PostgreSQL. A straight forward native high availability solution should be one of the highest priorities if not the highest.

      --
      -- I ignore anonymous replies to my comments and postings.
    3. Re:I miss pgsql by OverlordQ · · Score: 1

      > I will eventually migrate off MySQL but I don't know if it'll be MariaDB

      So from MySQL to still MySQL?

      --
      Your hair look like poop, Bob! - Wanker.
    4. Re:I miss pgsql by OverlordQ · · Score: 1

      Pg replication systems are dime a dozen, pick one, and implement it.

      --
      Your hair look like poop, Bob! - Wanker.
    5. Re:I miss pgsql by mcrbids · · Score: 1, Insightful

      ... and the replication systems are typically not worth much more than a dime, sadly.

      We have a pretty beefy set up; 4x 16 Core Xeon DB servers with 128 GB of RAM each and Enterprise SSDs, serving hundreds of instances of like-schema databases, one per (organizational) customer, serving an aggregate peak of about 1,000 queries/second in a mixed read/write load.

      And we've never been able to get replication to work reliably, ever. In every case we've ever tried, we've seen a net reduction in reliability. Every single time. Not that we've stopped trying, it has just never reached "just works" territory.

      Replication is PG's Achilles's heel.

      --
      I have no problem with your religion until you decide it's reason to deprive others of the truth.
    6. Re:I miss pgsql by nullchar · · Score: 2

      The native solution (streaming replication with many options) has been production stable since 9.1 and only increased in features, speed, and reliability.

    7. Re:I miss pgsql by nullchar · · Score: 5, Informative

      MySQL/MariaDB are still toys in comparison to PostgreSQL.

      Postgres has recursive CTEs, regex replacement, native JSON support (as a record type and trivially convert every query return type), and even base64 decoding and xpath parsing.

      MySQL has had some nice features for years, like REPLACE, but since the 9.x branch, CTEs can do that and more. And now PG has UPSERT for simplicity. Replication has always been great with MySQL, but PG's replication is now easy to administer. I've relied on Postgres' streaming replication since 9.1 in production and it's been great for years.

    8. Re:I miss pgsql by nullchar · · Score: 1

      Even with streaming replication in 9.3 onward? And tuning it? This a single instance with multiple schemas? Or separate instances?

    9. Re:I miss pgsql by mysidia · · Score: 1, Informative

      MySQL has had some nice features for years, like REPLACE, but since the 9.x branch

      The features MySQL has are good enough for 99% of real-world web applications.

      Yes, Postgres has more, but the extra features it has don't necessarily add much value for most programs.

      MySQL multi-master replication features are immensely valuable by comparison, and Postgres lacking them has prevented me from using Postgres, more than once.

    10. Re:I miss pgsql by dskoll · · Score: 1

      You're doing something wrong, then. Streaming replication works fine for us on a fairly similarly-sized setup.

    11. Re:I miss pgsql by jedidiah · · Score: 1

      > The features MySQL has are good enough for 99% of real-world web applications.

      It doesn't even have complete SQL support.

      While that might not matter for a trivial web application. For the more interesting ones that include non-trivial development teams, that will cause problems.

      --
      A Pirate and a Puritan look the same on a balance sheet.
    12. Re:I miss pgsql by andreak · · Score: 1

      http://bdr-project.org/docs/st...

    13. Re:I miss pgsql by rhodium_mir · · Score: 1

      How many grams of that righteous mineral rhodium do you charge?

      --
      You can't spell "oneiromancy" without "roman".
    14. Re:I miss pgsql by nullchar · · Score: 1

      Yes, Postgres has more, but the extra features it has don't necessarily add much value for most programs.
      MySQL multi-master replication features are immensely valuable

      I guess it depends on your programs. It does take effort to maintain two or more connections (one for write, the others for reads).

      If your use case needs balanced multi-master replication, but simple features, you should use a NoSQL solution.

      Postgres is amazing for reporting where you can bring anything you want in a single query, including JSON output (without using plsql procedures), and bulk updates are fantastic, with CTEs for selects and layers of them for updates/deletes, especially great with regex_replace and windowing functions. (Not even MS SQLServer has regex replace! Nor does it have a query planner to guide you through optimization of giant queries.)

      If your never directly touch the database outside of basic application code, then don't use an SQL solution! The benefit of using Structured Query Language is for human interaction. One-off reports can be tuned and converted to simple tools or repeatable cron jobs that use single queries to extract any data you want.

      If you are using MySQL, you're doing it wrong.

  8. Re:How does this compare with other databases? by theshowmecanuck · · Score: 3, Informative

    No. "Sharding" is the MYSQL bunch's relabelling of the established RDBMS partitioning paradigm. What this is, is parallel processing of a query. Queries normally run in a single process on a single processor. Now PostgreSQL can have a query split into multiple possesses on two or more processors. Oracle has had this capability for at least a decade.

    --
    -- I ignore anonymous replies to my comments and postings.
  9. Re:How does this compare with other databases? by danda · · Score: 1

    The parent did not reference "sharding". He referenced shard-query, which is a mechanism that can parallelize a single query using relational algebra.

    https://www.percona.com/blog/2011/05/14/distributed-set-processing-with-shard-query/

  10. Re:This is a troll. Respond appropriately. by phantomfive · · Score: 1

    However, I imagine that a complex or "mathy" equation in a query could make it be CPU intensive. But maybe that's relatively rare.

    Good point.

    --
    "First they came for the slanderers and i said nothing."
  11. Re:Patent trolls in 3 2 1 by sribe · · Score: 1

    I believe Oracle owns using more than 1 cpu in a query if I am correct...

    Well, Sybase had it well over 20 years ago...

  12. Fuck Yeah. PostgreSQL FTW by nullchar · · Score: 1

    I've been using Postgres for well over a decade now, and I still love it. Yes, you have to tune it, like any powerful tool.

    Granted this first pass is only for sequential scans, but those are the simplest to parallelize and generally the slowest. Some queries rely on table scans as not every column can be indexed.

    Postgres' growing feature set is amazing. Thanks team!

  13. Re:Greenplum's contributions? by nullchar · · Score: 1

    Interesting, it appears Greenplum has recently been open sourced.

  14. Re:This is a troll. Respond appropriately. by davester666 · · Score: 2

    particularly in parallel.

    --
    Sleep your way to a whiter smile...date a dentist!
  15. Re:How does this compare with other databases? by Anonymous Coward · · Score: 2, Informative

    It's worth pointing out that while this is a useful feature for some applications, it won't make any difference whatsoever for many more. PostgreSQL has been multi-threaded for executing concurrent queries for a very long time, so if you're running a database for many users, you probably won't notice any difference with this functionality. If you have some particular query that takes a heck of a long time like MRP runs, then this will matter to you.

  16. Re:How does this compare with other databases? by Anonymous Coward · · Score: 2, Insightful

    Oracle has had this capability for at least a decade.

    On the other hand it is often worth it to rewrite your application entirely or ditch the customer that needs that particular function to not have to deal with Oracle.

  17. Re:How does this compare with other databases? by theshowmecanuck · · Score: 2
    Blockquote from the MariaDB site (notice the terms 'sharded' and 'partition'). Also, I would hazard a guess that the PostgreSQL code for parallel queries is written in C, not something like PHP.

    Shard-Query is a high performance MPP (massively parallel processing) query engine for MariaDB and MySQL which offers increased parallelism compared to stand-alone servers. This increased parallelism is achieved by taking advantage of MariaDB/MySQL partitioning, sharding, common query clauses like BETWEEN and IN, or some combination of the above.

    Shard-Query is implemented with PHP and Gearman.

    Shard-Query is targeted mainly at big data problems, and OLAP queries in general. The primary goal of Shard-Query is to enable low-latency query access to extremely large volumes of data utilizing commodity hardware and open source database software. Shard-Query is a federated query engine which is designed to perform as much work in parallel as possible over a sharded dataset, that is one that is split over multiple servers (shards) or partitioned tables .

    --
    -- I ignore anonymous replies to my comments and postings.
  18. Re:How does this compare with other databases? by theshowmecanuck · · Score: 1

    I'll take your word for it. But multithreaded isn't the same thing as multi-process, especially on *nix. And yeah, although I don't program so much now, I have seen people write Oracle queries with hints for running parallel queries when not needed. Since these grab processors it can impact other services on the server.

    --
    -- I ignore anonymous replies to my comments and postings.
  19. I hope this doesn't compromise overall performance by Chrisq · · Score: 1

    This optimisation caters for a niche (admittedly a relatively large one) where there are relatively few queries but large ones. A more typical usage is where there are many smaller queries. I hope that this does not compromise the total throughput, so that the total parallelisation of multiple concurrent queries is not slowed to allow parallelisation withing individual queries. Either that or it should be a switchable option.

  20. Sounds like Terradata by tomhath · · Score: 1

    A leader in parallel query processing is Terradata, it uses custom hardware for massive parallelism. Will be interesting to see if Postgresql can scale on commodity/cloud hardware.

  21. Re:I hope this doesn't compromise overall performa by rhaas · · Score: 2

    As the fine blog post explains, it is a switchable option. You can set max_parallel_degree=0 to turn it off. Actually, right now, it's off by default, and you have to set max_parallel_degree>0 to turn it on.

  22. Re:How does this compare with other databases? by jedidiah · · Score: 1

    > Is this similar to MySQL / MariaDB's shard query? Do the commercial databases have this?

    Since the beginning of time by comparison...

    --
    A Pirate and a Puritan look the same on a balance sheet.
  23. Re:I hope this doesn't compromise overall performa by jedidiah · · Score: 1

    I wouldn't call it a "niche" exactly. It's one of the major main use cases for employing an RDBMS.

    We just have a lot of "database people" with very limited experience and a limited mindset.

    --
    A Pirate and a Puritan look the same on a balance sheet.
  24. Re:I hope this doesn't compromise overall performa by kupan787 · · Score: 1

    A more typical usage is where there are many smaller queries.

    Typical usage of what? If I have an OLTP system, for a transactional web based system, sure i'd agree. But if I am operating a data warehouse with fact tables housing hundreds of millions of rows, or trying to run largish reports on top of my OLTP system (say for state/fed reporting, or financial reporting), my "typical usage" is not many smaller queries.

    The last two projects I have been on, turning on auto-parrallism in Oracle has made huge performance gains. Not just for reads, but also when enabled for DML, we saw a lot of our bulk inserts and merges ran significantly faster. And this was on SQL that didn't change at all, just our DBAs making a few parameter changes on the DB.

  25. Working on similar feature by DidgetMaster · · Score: 1

    I am building an object store where some of my data objects can each be a key-value store that is used as a column in a relational table. Some queries against a table require a full scan (e.g. SELECT * from my_table where address like '%Main Street%';). If I had a table with a billion rows, it can take awhile to scan the whole address column looking for matches (I dedup the values in each column, but there can still be 100 million unique address values in such a table.) The solution is to break each column into multiple segments and let separate threads scan each segment looking for matches. The scan can occur in parallel on multi-core machines and complete in a much quicker manner than forcing a single thread to scan the whole thing. It sounds very similar to what they are trying to do with PostgreSQL (except that database is row based where the whole row is stored together, instead of a columnar database like mine). Here are two short demo videos of the system in action. https://www.youtube.com/watch?... https://www.youtube.com/watch?...

  26. Re: Patent trolls in 3 2 1 by MemeRot · · Score: 1

    So it was baked into SQL Server since the beginning

  27. Re: Patent trolls in 3 2 1 by sribe · · Score: 1

    So it was baked into SQL Server since the beginning

    I believe so, but that was a long time ago. Could be my timeline is off and it was added after the MS purchase.

  28. Re:How does this compare with other databases? by danda · · Score: 1

    I've used shard-query -- an early version of it. See the link I posted. It uses relational algebra to break a single query up into discrete parallel queries and execute them in parallel then combine the result set, on either a single server or multiple servers. It is able to do this outside the DB, and it could probably be ported to pgsql as well.