7 ms·

PostgreSQL Parallel Query v2

265 points by Tostino 10y ago

fleetside72 10y ago
The relationship between EnterpriseDB and the at-large Postgres community has always intrigued me. Why do they turn over to the community what they could make part of their proprietary fork? Maybe they make a lot of money consulting and not just licenses and general at-large adoption is good for that side of business.
- Tostino 10y ago
  Part of it is that they build the basis for features, and work on getting them into core, so it makes their value added features in their proprietary fork easier to maintain. Sometimes giving back and having an easier time maintaining your own fork is worth the effort and costs associated I guess. I for one appreciate it.
- pstuart 10y ago
  I think their secret sauce is Oracle compatibility, so if Postgres runs better, they run better.
  - koolba 10y ago
    And the Oracle compatibility piece breaks so much that it would never get merged into community Postgres.
    - pstuart 10y ago
      Even if it didn't, for all the work they put into the community version one can't begrudge them trying to make a living on the enterprise side.
    - jeltz 10y ago
      There are also plenty of devs in the community, outside the EnterpriseDB employees, who do do not want to pollute core PostgreSQL with alternative ways to do things it already can do just because of Oracle compatibility. Aliases have been added for ISO SQL compatibility, but adding features purely (i.e. features with zero value unless you are migrating from Oracle) for Oracle compatibility is not something core PostgreSQL does.
- mulmen 10y ago
  I have never used EnterpriseDB and I am only aware of it from a distance but it has always struck me as the ideal open source arrangement. Sure, EnterpriseDB is never going to have the revenue numbers of Oracle but their management and business teams should have a much easier time sleeping at night. I think it is great that EnterpriseDB has identified that it is better for their company to exist as part of a healthy ecosystem instead of trying to pave paradise so to speak.
  - b34r 10y ago
    A healthy relationship with open source projects is a great way to attract talent and gain goodwill.
  - manigandham 10y ago
    > but their management and business teams should have a much easier time sleeping at night Why? What is Oracle doing so wrong that they cant sleep as well at night?
- rodgerd 10y ago
  Well, Red Hat have demonstrated you can make a lot of money by open sourcing things (including buying proprietary software like ManageIQ and oVirt and open sourcing it). Given that EDB has ex-RHAT folks involved in setting it up, I imagine they're trying to replay the model.
  - hendzen 10y ago
    RedHat Q4 FY2016 profit: $68M Oracle Q4 FY2016 profit: $2B
    - zzzzzzzza 10y ago
      will oracle be around in 10-20 years? will red hat? which will experience growth?
      - temp246810 10y ago
        All things being equal it would take red hat ~30 quarters to make 2bn dollars, or roughly 7 years. 7 years to make what Oracle makes in a quarter. I don't really have a dog in this fight but just FYI I think you missed ops point.
        holri 10y ago
        RedHat contributes to the common good, Oracle for a few millionaires. Therefore they create more value.
        hendzen 10y ago
        Does developing MySQL and Java somehow not contribute to the common good?
        pgaddict 10y ago
        http://www.theregister.co.uk/2016/12/16/oracle_targets_java_users_non_compliance/ http://www.theregister.co.uk/2016/12/16/oracle_targets_java_...
- takeda 10y ago
  They do, they for example provide fork of Postgres that is compatible with Oracle which is not free.
- X86BSD 10y ago
  Can't be for certain but one possible large factor is it reduces diffs from the upstream if you commit as much as you can back upstream. It's a major engineering drag to deal with that.
- manigandham 10y ago
  This is a very common model now - several companies have open-source software (either their own or major contributor to) that is then covered with extra functionality, support and consulting services. Some tradeoffs since the products are usually not as good and move slower than proprietary commercial offerings but the free access and large communities make up for it.
Tostino 10y ago
That is really an amazing amount of work that has gone into this next release. This is really getting Postgres up to par with the commercial offerings, and hopefully it keeps progressing for v11. It's especially important now since servers with more and more cores are becoming the norm.
malisper 10y ago
Besides parallel queries (specifically parallel index scans), the Postgres 10 feature I'm most looking forward to is the work Andres Freund has done on using LLVM to JIT compile expressions[0]. Based on the the layout of the database I work on[1], an insane amount of CPU goes to processing of partial index predicates. If the improvements are anywhere near what is being described in the mailing list, we're going to start saving a ton of money on CPU. Parallel index scans will be an absolutely massive win for us. Currently Postgres does not perform any sort of prefetching. When performing an index scan, this results in serialized random I/O. In our case, queries wind up using ~1/10th the I/O they could be using if there were to do more I/O in parallel. We are expecting a dramatic speedup just from parallel index scans. This is in addition to the bugfix in 9.6 (we're currently on 9.5) which added proper support for index only scans over partial indexes. That bugfix should also result in a pretty large speedup for a lot of our queries. As soon as Postgres 10 comes out, we are immediately going to upgrade. There are just so many huge wins for us. [0] https://www.postgresql.org/message-id/20161206034955.bh33paeralxbtluv%40alap3.anarazel.de https://www.postgresql.org/message-id/20161206034955.bh33pae... [1] https://blog.heapanalytics.com/running-10-million-postgresql-indexes-in-production/ https://blog.heapanalytics.com/running-10-million-postgresql...
- colanderman 10y ago
  Don't forget native logical replication.
  - malisper 10y ago
    Logical replication doesn't matter as much to us. We are using CitusDB (in other words sharded Postgres), and we have our own code for handling replication.
    - qeternity 10y ago
      Citus should already be saturating i/o during a scan unless you're only hitting one shard. What am I missing about your use case?
anarazel 10y ago
Robert and his team are really doing an impressive amount of progress and work on this.
- pgaddict 10y ago
  Definitely agreed.
deleted 10y ago
[deleted]

265 points by Tostino 10y ago

The relationship between EnterpriseDB and the at-large Postgres community has always intrigued me. Why do they turn over to the community what they could make part of their proprietary fork? Maybe they make a lot of money consulting and not just licenses and general at-large adoption is good for that side of business.

Tostino 10y ago
Part of it is that they build the basis for features, and work on getting them into core, so it makes their value added features in their proprietary fork easier to maintain. Sometimes giving back and having an easier time maintaining your own fork is worth the effort and costs associated I guess. I for one appreciate it.
pstuart 10y ago
I think their secret sauce is Oracle compatibility, so if Postgres runs better, they run better.
- koolba 10y ago
  And the Oracle compatibility piece breaks so much that it would never get merged into community Postgres.
  - pstuart 10y ago
    Even if it didn't, for all the work they put into the community version one can't begrudge them trying to make a living on the enterprise side.
  - jeltz 10y ago
    There are also plenty of devs in the community, outside the EnterpriseDB employees, who do do not want to pollute core PostgreSQL with alternative ways to do things it already can do just because of Oracle compatibility. Aliases have been added for ISO SQL compatibility, but adding features purely (i.e. features with zero value unless you are migrating from Oracle) for Oracle compatibility is not something core PostgreSQL does.
mulmen 10y ago
I have never used EnterpriseDB and I am only aware of it from a distance but it has always struck me as the ideal open source arrangement. Sure, EnterpriseDB is never going to have the revenue numbers of Oracle but their management and business teams should have a much easier time sleeping at night. I think it is great that EnterpriseDB has identified that it is better for their company to exist as part of a healthy ecosystem instead of trying to pave paradise so to speak.
- b34r 10y ago
  A healthy relationship with open source projects is a great way to attract talent and gain goodwill.
- manigandham 10y ago
  > but their management and business teams should have a much easier time sleeping at night Why? What is Oracle doing so wrong that they cant sleep as well at night?
rodgerd 10y ago
Well, Red Hat have demonstrated you can make a lot of money by open sourcing things (including buying proprietary software like ManageIQ and oVirt and open sourcing it). Given that EDB has ex-RHAT folks involved in setting it up, I imagine they're trying to replay the model.
- hendzen 10y ago
  RedHat Q4 FY2016 profit: $68M Oracle Q4 FY2016 profit: $2B
  - zzzzzzzza 10y ago
    will oracle be around in 10-20 years? will red hat? which will experience growth?
    - temp246810 10y ago
      All things being equal it would take red hat ~30 quarters to make 2bn dollars, or roughly 7 years. 7 years to make what Oracle makes in a quarter. I don't really have a dog in this fight but just FYI I think you missed ops point.
      - holri 10y ago
        RedHat contributes to the common good, Oracle for a few millionaires. Therefore they create more value.
        hendzen 10y ago
        Does developing MySQL and Java somehow not contribute to the common good?
        pgaddict 10y ago
        http://www.theregister.co.uk/2016/12/16/oracle_targets_java_users_non_compliance/ http://www.theregister.co.uk/2016/12/16/oracle_targets_java_...
takeda 10y ago
They do, they for example provide fork of Postgres that is compatible with Oracle which is not free.
X86BSD 10y ago
Can't be for certain but one possible large factor is it reduces diffs from the upstream if you commit as much as you can back upstream. It's a major engineering drag to deal with that.
manigandham 10y ago
This is a very common model now - several companies have open-source software (either their own or major contributor to) that is then covered with extra functionality, support and consulting services. Some tradeoffs since the products are usually not as good and move slower than proprietary commercial offerings but the free access and large communities make up for it.

Tostino 10y ago

That is really an amazing amount of work that has gone into this next release. This is really getting Postgres up to par with the commercial offerings, and hopefully it keeps progressing for v11. It's especially important now since servers with more and more cores are becoming the norm.

malisper 10y ago

Besides parallel queries (specifically parallel index scans), the Postgres 10 feature I'm most looking forward to is the work Andres Freund has done on using LLVM to JIT compile expressions[0]. Based on the the layout of the database I work on[1], an insane amount of CPU goes to processing of partial index predicates. If the improvements are anywhere near what is being described in the mailing list, we're going to start saving a ton of money on CPU. Parallel index scans will be an absolutely massive win for us. Currently Postgres does not perform any sort of prefetching. When performing an index scan, this results in serialized random I/O. In our case, queries wind up using ~1/10th the I/O they could be using if there were to do more I/O in parallel. We are expecting a dramatic speedup just from parallel index scans. This is in addition to the bugfix in 9.6 (we're currently on 9.5) which added proper support for index only scans over partial indexes. That bugfix should also result in a pretty large speedup for a lot of our queries. As soon as Postgres 10 comes out, we are immediately going to upgrade. There are just so many huge wins for us. [0] https://www.postgresql.org/message-id/20161206034955.bh33paeralxbtluv%40alap3.anarazel.de https://www.postgresql.org/message-id/20161206034955.bh33pae... [1] https://blog.heapanalytics.com/running-10-million-postgresql-indexes-in-production/ https://blog.heapanalytics.com/running-10-million-postgresql...

colanderman 10y ago
Don't forget native logical replication.
- malisper 10y ago
  Logical replication doesn't matter as much to us. We are using CitusDB (in other words sharded Postgres), and we have our own code for handling replication.
  - qeternity 10y ago
    Citus should already be saturating i/o during a scan unless you're only hitting one shard. What am I missing about your use case?

anarazel 10y ago

Robert and his team are really doing an impressive amount of progress and work on this.

pgaddict 10y ago
Definitely agreed.

deleted 10y ago

[deleted]