8 ms·

Postgresql 9.6: Support parallel aggregation

267 points by eMerzh 11y ago

tiglionabbit 11y ago
Does it have generic upserts yet?
- ddorian43 11y ago
  What do you mean by generic ? Upserts were implemented in 9.5
  - tiglionabbit 11y ago
    As in you don't need to write a unique PL/pgSQL routine for each kind of upsert you want to do. Oh, it does have upserts now. Awesome =]
kbenson 11y ago
I would love to see some sample benchmarks for the type of gains you might see from this. I always see these interesting new PostgreSQL features being posted, and they sound cool, but it's hard to know how much they help in practice. I understand it's often highly workload and data dependent, but something would be better than nothing.
- olavgg 11y ago
  It would help a lot for typical data warehouse queries that involves aggregating millions to billions of rows. SELECT EXPLAIN ANALYZE will tell if its relevant for you ;-)
  - kbenson 11y ago
    > SELECT EXPLAIN ANALYZE will tell if its relevant for you That assumes I run PostgreSQL already, which I don't. I am interested in possibly switching at some point if it's worthwhile, but it's hard to muster the effort to do concerted testing of a representative sample of my data, including possibly changing how queries are done to take advantage of specific features, when I have little information to go on. Not that I expect PostreSQL to do in-depth analysis of everything, but it would be great from both a promotional and technical standpoint if there was something like "we've seen something like X% speedup of queries utilizing Y, and up to Z% speedup in extreme cases." I mean, I assume they at least have rudimentary numbers for this, otherwise they would be making blind changes without knowing whether it improved or degraded performance. Providing just enough to get people interested in doing their own benchmarking (and possibly publishing them) would be great for everyone. Edit: One of top comment is actually what I'm talking about (but apparently for a different feature). So it does get done, which is really nice. :) Edit2: Now there's the link to the blog post for this feature. :)
    - Someone 11y ago
      If you aren't using it now, and thus don't know how fast or slow it would be on your data and queries, why would you care about percentage speed ups? It could be a 1% speed up from insanely fast to slightly more insanely fast, or a 100% speed up from unbearably slow to just extremely annoyingly slow. I gather that people considering migrating who want some reassurance before investing time in testing should be more interested in such things things as benchmarks that show whether PostgreSQL can saturate your hardware for various query types, benchmarks that compare its query planner against that of competitors, and benchmarks that show how well it works under load.
      - kbenson 11y ago
        >> I am interested in possibly switching at some point if it's worthwhile > If you aren't using it now, and thus don't know how fast or slow it would be on your data and queries, why would you care about percentage speed ups? I'm working under the assumption that similar operations between PostgreSQL and MySQL for operations that aren't using an advanced feature that the other plainly doesn't support will be roughly comparable in performance. I understand this isn't true, but it's useful in that it gives me a baseline to work with when reasoning without forcing me to migrate my application (or at least a significant portion of the data) to PostgreSQL just to test. If I'm then confronted with a feature that one supports and the other doesn't that has the capability to in some cases reduce the query time by an order of magnitude, then it's starts approaching the criteria needed for me to invest that time to see if it's worth migrating. > I gather that people considering migrating who want some reassurance before investing time in testing should be more interested in such things things as benchmarks that show whether PostgreSQL can saturate your hardware for various query types Isn't that exactly what this is? Throwing CPU cores at aggregating data to reduce time? I asked for benchmarks regarding this specific feature, to see what type of gain we were talking about with this. I didn't ask for comparisons to other databases because those benchmarks are generally harder to perform fairly, so I would have been (and am) happy with comparative gain benchmarks.
- pgaddict 11y ago
  David Dowley (one of the authors of the patch) posted some measurements on TPC-H Q1: https://news.ycombinator.com/item?id=11332713 https://news.ycombinator.com/item?id=11332713 The machine has 4x E5-4620, so 32 physical cores. And with 30 workers it gets ~80% of the theoretical speedup. Not bad, I guess.
chris_wot 11y ago
There is going to come a point where Postgres passes Oracle in terms of features and performance. There is also a point that Postgres will perform with enough features needed by most businesses that they'll choose it even though it doesn't match Oracle on a feature by feature basis. We have hit peak Oracle. From this point forward it's going to be hard for Oracle to regain momentum. Expect a lot of FUD against Postgres - the more you see, the more worried you know Oracke execs are becoming.
- pbarnes_1 11y ago
  This migration has been happening for many years anyway. I used to work consulting doing these types of migrations for customers for a huge IT services company.
  - chris_wot 11y ago
    Yeah, that point is probably occurring as we speak :-)
- willlll 11y ago
  Oracle is writing Russian IT shops to try and convince them from moving to postgres http://www.postgresql.org/message-id/CANNMO++6tPiwBv2OKcy-HhiYmByhL+XSSUH3NDvOs3G1VVg++g@mail.gmail.com http://www.postgresql.org/message-id/CANNMO++6tPiwBv2OKcy-Hh...
- kbenson 11y ago
  > There is going to come a point where Postgres passes Oracle in terms of features and performance. I wouldn't be so sure. Oracle has deep pockets, and I'm not sure there's a reason why anything PostgreSQL wants to roll out they can't pay to prioritize and have done sooner. > There is also a point that Postgres will perform with enough features needed by most businesses that they'll choose it even though it doesn't match Oracle on a feature by feature basis. No argument there.
  - chris_wot 11y ago
    Oh, I'm not saying this is an overnight thing or that Oracle will be destroyed. In fact, I don't want to see them destroyed, but what I am interested in is that their market power and influence wanes over time. Ironically, this will be good for Oracle - they will be forced to dump immoral and illegal business practices. The problem commercial businesses have when it comes to open source is that you might be able to reduce customer take up, by you can't compete with it like you would other businesses. In the closed source world you can purchase a company and shut down its product and thus kill off the competition. As Microsoft have found, with open source software, that's not possible. The other unfortunate thing for Oracle is that when they attack Postgres they have to publish lists of competitive advantages. All this does is give Postgres developers a todo list, and they then work towards implementing the features that matter.
filereaper 11y ago
Short of doing a deep dive into Postgresql, do any universities use Postgresql as a basis for their DB implementation courses? I wanted to dive into Postgres and was hoping for some training wheels. Thanks.
- deleted 11y ago
  [deleted]
- ddorian43 11y ago
  Dive in the documentation + mailing list + postgresql planet.
- __jal 11y ago
  PG is, IMHO, probably one of the best code bases for this. I'm far and away not a database hacker, but have read parts of the code at various times to better understand what I was seeing. The codebase is incredibly well written and organized, and the documentation (both developer and user) is top-notch. The only problem I can think of with using it in a course would be choosing what to focus on with only a semester. Add in the long revision history and I think there are also multiple theses in there on the sociology of open source.
- aut_dan 11y ago
  https://courses.cs.washington.edu/courses/csep544/ https://courses.cs.washington.edu/courses/csep544/ might be helpful
- morgante 11y ago
  My university does, though unfortunately we don't have any public course information.
- spariev 11y ago
  There is a "Hacking PostgreSQL" course[1], which tries to explain PostgreSQL internals and architecture, but it is available in Russian only[2] [1] http://postgrespro.ru/education/courses/hacking http://postgrespro.ru/education/courses/hacking [2] https://www.youtube.com/watch?list=PLaFqU3KCWw6Jfb8IBNk3hZ07dxMxjfGtv&v=Z0DL11Xcxs0 https://www.youtube.com/watch?list=PLaFqU3KCWw6Jfb8IBNk3hZ07...
kazagistar 11y ago
Blog post with some details and benchmarks by one of the authors of the patch: http://rhaas.blogspot.com/2015/11/parallel-sequential-scan-is-committed.html http://rhaas.blogspot.com/2015/11/parallel-sequential-scan-i...
- pgaddict 11y ago
  That's not about parallel aggregate but about parallel scan. Also, Robert is not the author of the patch, he did a review and committed it. The actual author are listed in the commit message: David Rowley and Haribabu Kommi.
pgaddict 11y ago
FWIW, a simple benchmark by David Rowley (one of the authors of the patch) are here: http://blog.2ndquadrant.com/parallel-aggregate/ http://blog.2ndquadrant.com/parallel-aggregate/
- andruby 11y ago
  The article claims a near linear speedup for a very large query. On a 64 core machine, the query takes: 1375s with 0 workers 131s with 10 workers 56s with 30 workers
HoyaSaxa 11y ago
A slight tangent, but I am still shocked that many open source projects lack downloads via https (including PostgreSQL). Sure you can offer some layer of security by signing the distributions, but ultimately users are lazy.
- harel 11y ago
  Out of curiosity what does it matter if your postgres download is via http or https? Is there any benefit of hiding your download from prying eyes?
  - HoyaSaxa 11y ago
    One main benefit of https is confidentiality, but for OSS downloads it more importantly provides an integrity and authentication benefit as well. Sure checksums and/or package signing should provide the same benefit, but most users cannot say they have checked these for every installation (and checksums are useless if served via http because they can be tampered via a man-in-the-middle attack).
  - im_down_w_otp 11y ago
    You can validate the certificate used to establish the connection and thus the endpoint, if you want to, and thus avoid one potential vector of someone serving you a malicious postgres build. Because you're putting your trust in the network.
  - brazzledazzle 11y ago
    To put it another way it's less about prying eyes and more about MitM attacks.
    - RI_Swamp_Yankee 11y ago
      This is true, and MitM attacks are becoming less and less theoretical as more traffic moves to the various wireless protocols. Rogue AP's and cell towers are a thing in these modern times.
    - 11y ago
harel 11y ago
It looks like in 9.4 PG went after the document databases and now they are after Oracle. I don't know why I get excited with point releases of postgres, but I do.
- stuartaxelowen 11y ago
  Because they are building an open source industry leading database. What's not exciting about that!
  - harel 11y ago
    Its not just that. I waited patiently for 9.4 to come out as I needed just that functionality of indexable JSONB for something I'm working on. It was pointless for me to start the project without it. 9.4 came out better than I could have expected. Then followed by Upserts, and now this... I'm directly and personally affected by each point release. Its fantastic.
  - pbreit 11y ago
    Is anyone still starting with MySQL anymore?
    - harel 11y ago
      "mySql" is dead to me. Literally. Anything Oracle touches becomes a potential legal liability. Its Maria DB now and although I do maintain a legacy 100GB+ MARIA db, I will never use it again for new projects. The thing about postgres is that once you start using it you understand there is really very little competition in both the open or commercial worlds. My previous big project relied on mongoDB for statistical data. At the time there was no other compelling document database for our use case. Today, this decision is no longer relevant because if I can get the same functionality and performance out of Postgres I will choose postgres. The ability to combine the SQL with "NoSQL" is just too compelling.
- pgaddict 11y ago
  Because in the PostgreSQL world those are actually major versions, not "point updates" ;-)
allan_s 11y ago
it's pretty interesting especially for those of us who are creating analytics tools on top of postgresql, the increase performance would certainly permit less denormalized work-around , like creating "manual" aggregate directly in the database (with all the problem of keeping them in sync with the rest) and I'm still here waiting for 9.5 to arrive in RDS of amazon :(
pilif 11y ago
I am totally impressed by the work 2ndQuadrant is doing: many of the recent innovations to Postgres have been done by them and all of that without any obligation for them to be doing so. The BSD license would allow them to add all of these things to a proprietary fork that they could be selling. Or they could just release their own fork under an open license and focus on just adding features. But that's not how they work. All of their contributions are pushed upstream which is a very considerable effort with how conservative Postgres is at accepting new functionality. Aside of that: there are 2ndQuadrant employees in the #postgres IRC chat room, helping people with daily support issues. This is their core business and yet they still help people for free (within reason). This is bloody impressive. If I'm ever at a point when I need help with a Postgres issue, then they will be very first of the list of companies I would consider. Thank you very much for all that you are doing.
- sandGorgon 11y ago
  I wish they would build a RDS alternative. I would use them in a heartbeat. There are all these companies/startups who are not in the target market for consulting... But who would gladly pay for hosted postgres.
  - jpgvm 11y ago
    If you don't mind hosting it yourself check out Flynn our pg appliance is very similar to how RDS is constructed. Or if you are more adventurous look at Joyent Manatee which the Flynn code is based off.
    - sandGorgon 11y ago
      But that is precisely what I want to avoid. For most startups at an early stage, what we are short of is people. It's the github vs self hosted argument - most people with limited people resources would default to github. RDS is the most expensive thing we pay for. It's worth it.
      - jpgvm 11y ago

267 points by eMerzh 11y ago

tiglionabbit 11y ago

Does it have generic upserts yet?

ddorian43 11y ago
What do you mean by generic ? Upserts were implemented in 9.5
- tiglionabbit 11y ago
  As in you don't need to write a unique PL/pgSQL routine for each kind of upsert you want to do. Oh, it does have upserts now. Awesome =]

kbenson 11y ago

I would love to see some sample benchmarks for the type of gains you might see from this. I always see these interesting new PostgreSQL features being posted, and they sound cool, but it's hard to know how much they help in practice. I understand it's often highly workload and data dependent, but something would be better than nothing.

olavgg 11y ago
It would help a lot for typical data warehouse queries that involves aggregating millions to billions of rows. SELECT EXPLAIN ANALYZE will tell if its relevant for you ;-)
- kbenson 11y ago
  > SELECT EXPLAIN ANALYZE will tell if its relevant for you That assumes I run PostgreSQL already, which I don't. I am interested in possibly switching at some point if it's worthwhile, but it's hard to muster the effort to do concerted testing of a representative sample of my data, including possibly changing how queries are done to take advantage of specific features, when I have little information to go on. Not that I expect PostreSQL to do in-depth analysis of everything, but it would be great from both a promotional and technical standpoint if there was something like "we've seen something like X% speedup of queries utilizing Y, and up to Z% speedup in extreme cases." I mean, I assume they at least have rudimentary numbers for this, otherwise they would be making blind changes without knowing whether it improved or degraded performance. Providing just enough to get people interested in doing their own benchmarking (and possibly publishing them) would be great for everyone. Edit: One of top comment is actually what I'm talking about (but apparently for a different feature). So it does get done, which is really nice. :) Edit2: Now there's the link to the blog post for this feature. :)
  - Someone 11y ago
    If you aren't using it now, and thus don't know how fast or slow it would be on your data and queries, why would you care about percentage speed ups? It could be a 1% speed up from insanely fast to slightly more insanely fast, or a 100% speed up from unbearably slow to just extremely annoyingly slow. I gather that people considering migrating who want some reassurance before investing time in testing should be more interested in such things things as benchmarks that show whether PostgreSQL can saturate your hardware for various query types, benchmarks that compare its query planner against that of competitors, and benchmarks that show how well it works under load.
    - kbenson 11y ago
      >> I am interested in possibly switching at some point if it's worthwhile > If you aren't using it now, and thus don't know how fast or slow it would be on your data and queries, why would you care about percentage speed ups? I'm working under the assumption that similar operations between PostgreSQL and MySQL for operations that aren't using an advanced feature that the other plainly doesn't support will be roughly comparable in performance. I understand this isn't true, but it's useful in that it gives me a baseline to work with when reasoning without forcing me to migrate my application (or at least a significant portion of the data) to PostgreSQL just to test. If I'm then confronted with a feature that one supports and the other doesn't that has the capability to in some cases reduce the query time by an order of magnitude, then it's starts approaching the criteria needed for me to invest that time to see if it's worth migrating. > I gather that people considering migrating who want some reassurance before investing time in testing should be more interested in such things things as benchmarks that show whether PostgreSQL can saturate your hardware for various query types Isn't that exactly what this is? Throwing CPU cores at aggregating data to reduce time? I asked for benchmarks regarding this specific feature, to see what type of gain we were talking about with this. I didn't ask for comparisons to other databases because those benchmarks are generally harder to perform fairly, so I would have been (and am) happy with comparative gain benchmarks.
pgaddict 11y ago
David Dowley (one of the authors of the patch) posted some measurements on TPC-H Q1: https://news.ycombinator.com/item?id=11332713 https://news.ycombinator.com/item?id=11332713 The machine has 4x E5-4620, so 32 physical cores. And with 30 workers it gets ~80% of the theoretical speedup. Not bad, I guess.

chris_wot 11y ago

There is going to come a point where Postgres passes Oracle in terms of features and performance. There is also a point that Postgres will perform with enough features needed by most businesses that they'll choose it even though it doesn't match Oracle on a feature by feature basis. We have hit peak Oracle. From this point forward it's going to be hard for Oracle to regain momentum. Expect a lot of FUD against Postgres - the more you see, the more worried you know Oracke execs are becoming.

pbarnes_1 11y ago
This migration has been happening for many years anyway. I used to work consulting doing these types of migrations for customers for a huge IT services company.
- chris_wot 11y ago
  Yeah, that point is probably occurring as we speak :-)
willlll 11y ago
Oracle is writing Russian IT shops to try and convince them from moving to postgres http://www.postgresql.org/message-id/CANNMO++6tPiwBv2OKcy-HhiYmByhL+XSSUH3NDvOs3G1VVg++g@mail.gmail.com http://www.postgresql.org/message-id/CANNMO++6tPiwBv2OKcy-Hh...
kbenson 11y ago
> There is going to come a point where Postgres passes Oracle in terms of features and performance. I wouldn't be so sure. Oracle has deep pockets, and I'm not sure there's a reason why anything PostgreSQL wants to roll out they can't pay to prioritize and have done sooner. > There is also a point that Postgres will perform with enough features needed by most businesses that they'll choose it even though it doesn't match Oracle on a feature by feature basis. No argument there.
- chris_wot 11y ago
  Oh, I'm not saying this is an overnight thing or that Oracle will be destroyed. In fact, I don't want to see them destroyed, but what I am interested in is that their market power and influence wanes over time. Ironically, this will be good for Oracle - they will be forced to dump immoral and illegal business practices. The problem commercial businesses have when it comes to open source is that you might be able to reduce customer take up, by you can't compete with it like you would other businesses. In the closed source world you can purchase a company and shut down its product and thus kill off the competition. As Microsoft have found, with open source software, that's not possible. The other unfortunate thing for Oracle is that when they attack Postgres they have to publish lists of competitive advantages. All this does is give Postgres developers a todo list, and they then work towards implementing the features that matter.

filereaper 11y ago

Short of doing a deep dive into Postgresql, do any universities use Postgresql as a basis for their DB implementation courses? I wanted to dive into Postgres and was hoping for some training wheels. Thanks.

deleted 11y ago
[deleted]
ddorian43 11y ago
Dive in the documentation + mailing list + postgresql planet.
__jal 11y ago
PG is, IMHO, probably one of the best code bases for this. I'm far and away not a database hacker, but have read parts of the code at various times to better understand what I was seeing. The codebase is incredibly well written and organized, and the documentation (both developer and user) is top-notch. The only problem I can think of with using it in a course would be choosing what to focus on with only a semester. Add in the long revision history and I think there are also multiple theses in there on the sociology of open source.
aut_dan 11y ago
https://courses.cs.washington.edu/courses/csep544/ https://courses.cs.washington.edu/courses/csep544/ might be helpful
morgante 11y ago
My university does, though unfortunately we don't have any public course information.
spariev 11y ago
There is a "Hacking PostgreSQL" course[1], which tries to explain PostgreSQL internals and architecture, but it is available in Russian only[2] [1] http://postgrespro.ru/education/courses/hacking http://postgrespro.ru/education/courses/hacking [2] https://www.youtube.com/watch?list=PLaFqU3KCWw6Jfb8IBNk3hZ07dxMxjfGtv&v=Z0DL11Xcxs0 https://www.youtube.com/watch?list=PLaFqU3KCWw6Jfb8IBNk3hZ07...

kazagistar 11y ago

Blog post with some details and benchmarks by one of the authors of the patch: http://rhaas.blogspot.com/2015/11/parallel-sequential-scan-is-committed.html http://rhaas.blogspot.com/2015/11/parallel-sequential-scan-i...

pgaddict 11y ago
That's not about parallel aggregate but about parallel scan. Also, Robert is not the author of the patch, he did a review and committed it. The actual author are listed in the commit message: David Rowley and Haribabu Kommi.

pgaddict 11y ago

FWIW, a simple benchmark by David Rowley (one of the authors of the patch) are here: http://blog.2ndquadrant.com/parallel-aggregate/ http://blog.2ndquadrant.com/parallel-aggregate/

andruby 11y ago
The article claims a near linear speedup for a very large query. On a 64 core machine, the query takes: 1375s with 0 workers 131s with 10 workers 56s with 30 workers

HoyaSaxa 11y ago

A slight tangent, but I am still shocked that many open source projects lack downloads via https (including PostgreSQL). Sure you can offer some layer of security by signing the distributions, but ultimately users are lazy.

harel 11y ago
Out of curiosity what does it matter if your postgres download is via http or https? Is there any benefit of hiding your download from prying eyes?
- HoyaSaxa 11y ago
  One main benefit of https is confidentiality, but for OSS downloads it more importantly provides an integrity and authentication benefit as well. Sure checksums and/or package signing should provide the same benefit, but most users cannot say they have checked these for every installation (and checksums are useless if served via http because they can be tampered via a man-in-the-middle attack).
- im_down_w_otp 11y ago
  You can validate the certificate used to establish the connection and thus the endpoint, if you want to, and thus avoid one potential vector of someone serving you a malicious postgres build. Because you're putting your trust in the network.
- brazzledazzle 11y ago
  To put it another way it's less about prying eyes and more about MitM attacks.
  - RI_Swamp_Yankee 11y ago
    This is true, and MitM attacks are becoming less and less theoretical as more traffic moves to the various wireless protocols. Rogue AP's and cell towers are a thing in these modern times.
  - 11y ago

harel 11y ago

It looks like in 9.4 PG went after the document databases and now they are after Oracle. I don't know why I get excited with point releases of postgres, but I do.

stuartaxelowen 11y ago
Because they are building an open source industry leading database. What's not exciting about that!
- harel 11y ago
  Its not just that. I waited patiently for 9.4 to come out as I needed just that functionality of indexable JSONB for something I'm working on. It was pointless for me to start the project without it. 9.4 came out better than I could have expected. Then followed by Upserts, and now this... I'm directly and personally affected by each point release. Its fantastic.
- pbreit 11y ago
  Is anyone still starting with MySQL anymore?
  - harel 11y ago
    "mySql" is dead to me. Literally. Anything Oracle touches becomes a potential legal liability. Its Maria DB now and although I do maintain a legacy 100GB+ MARIA db, I will never use it again for new projects. The thing about postgres is that once you start using it you understand there is really very little competition in both the open or commercial worlds. My previous big project relied on mongoDB for statistical data. At the time there was no other compelling document database for our use case. Today, this decision is no longer relevant because if I can get the same functionality and performance out of Postgres I will choose postgres. The ability to combine the SQL with "NoSQL" is just too compelling.
pgaddict 11y ago
Because in the PostgreSQL world those are actually major versions, not "point updates" ;-)

allan_s 11y ago

it's pretty interesting especially for those of us who are creating analytics tools on top of postgresql, the increase performance would certainly permit less denormalized work-around , like creating "manual" aggregate directly in the database (with all the problem of keeping them in sync with the rest) and I'm still here waiting for 9.5 to arrive in RDS of amazon :(

pilif 11y ago

I am totally impressed by the work 2ndQuadrant is doing: many of the recent innovations to Postgres have been done by them and all of that without any obligation for them to be doing so. The BSD license would allow them to add all of these things to a proprietary fork that they could be selling. Or they could just release their own fork under an open license and focus on just adding features. But that's not how they work. All of their contributions are pushed upstream which is a very considerable effort with how conservative Postgres is at accepting new functionality. Aside of that: there are 2ndQuadrant employees in the #postgres IRC chat room, helping people with daily support issues. This is their core business and yet they still help people for free (within reason). This is bloody impressive. If I'm ever at a point when I need help with a Postgres issue, then they will be very first of the list of companies I would consider. Thank you very much for all that you are doing.

sandGorgon 11y ago
I wish they would build a RDS alternative. I would use them in a heartbeat. There are all these companies/startups who are not in the target market for consulting... But who would gladly pay for hosted postgres.
- jpgvm 11y ago
  If you don't mind hosting it yourself check out Flynn our pg appliance is very similar to how RDS is constructed. Or if you are more adventurous look at Joyent Manatee which the Flynn code is based off.
  - sandGorgon 11y ago
    But that is precisely what I want to avoid. For most startups at an early stage, what we are short of is people. It's the github vs self hosted argument - most people with limited people resources would default to github. RDS is the most expensive thing we pay for. It's worth it.
    - jpgvm 11y ago