19 ms·

PostgreSQL 9.6 Released

553 points by sheff 10y ago

gtrubetskoy 10y ago
Anyone know the state of BDR in 9.6? http://blog.2ndquadrant.com/bdr-is-coming-to-postgresql-9-6/ http://blog.2ndquadrant.com/bdr-is-coming-to-postgresql-9-6/
- okket 10y ago
  See discussion from 3 days ago (69 comments): https://news.ycombinator.com/item?id=12576116 https://news.ycombinator.com/item?id=12576116 TL;DR: It is not in mainline, but it does not need a patch anymore. You need to bring your own conflict resolution logic.
  - pgaddict 10y ago
    Or design the application so that there are no conflicts (e.g. modifying different subsets of users on different nodes).
    - jimktrains2 10y ago
      Which is a form of conflict resolution. It requires the application to be aware of the datastore. I wonder if, since BDR is just a plugin now, a plugin that used strong consistency guarantees could be built using the same changes that were required for BDR.
  - anarazel 10y ago
    BDR has last-updated-wins builtin and conflict handlers that can be called if that's not what you need.
  - deleted 10y ago
    [deleted]
- mamcx 10y ago
  What is BDR?
  - joeriel 10y ago
    Bi-directional replication
jbkkd 10y ago
Congratulations to the PostgreSQL Global Development Group on a much-anticipated release. Curious about this: > parallelism can speed up big data queries by as much as 32 times faster Why would it be only 32 times faster? The sky's the limit if there aren't major bottlenecks on the way.
- greggyb 10y ago
  No one has tested a query that got more than 32x faster, so they don't want to promise something they can't prove.
  - Jweb_Guru 10y ago
    There's also a limited amount of memory-level parallelism available... with 4-DIMM sockets you might need an 8-socket machine to get a 32x improvement on large (memory-bound) sequential scans, which I'd guess you can get on top-end Power machines. (You can probably get more memory level parallelism with random access, but your overall bandwidth will likely be lower... fully exploiting memory bandwidth is complicated and difficult to do for real applications).
  - joshberkus 10y ago
    That's pretty much the case, yes.
- noxin 10y ago
  They likely benchmarked it on a 32 core system. Like a dual Opteron board. If the task was single-threaded before a 32-fold improvement is reasonable.
  - pedrocr 10y ago
    It's very difficult to get a 32x speedup from 32 cores as there are always parts that are inherently serial, so it's more likely they tested it on a 64 core machine or something like that.
    - 10y ago
ignoramous 10y ago
A tangential question: Everyone speaks about InnoDB and how performant and reliable it is... and multiple firms even use it as a KV-store (Uber/Pinterest/AWS) bypassing MySQL entirely. I have never heard much about storage engines in Postgres, why could this be so? Wikipedia has a (stub) article on InnoDB, but nothing on Postgres' storage engines... just wondering why that is.
- dhd415 10y ago
  PostgreSQL, for better or worse, doesn't have pluggable storage engines. There's some discussion on their dev mailing list about the possibility of adding that capability in PG10, though: http://postgresql.nabble.com/Pluggable-storage-td5916322.html http://postgresql.nabble.com/Pluggable-storage-td5916322.htm... Some earlier (2013) discussion on the same topic: https://wiki.postgresql.org/wiki/2013UnconfPluggableStorage https://wiki.postgresql.org/wiki/2013UnconfPluggableStorage
  - asah 10y ago
    tl;dr: Foreign Data Wrappers (FDW) provide 99% of the same functionality, but with even more flexibility incl smart query optimizer support.
    - pgaddict 10y ago
      That is far too optimistic, IMHO. FDW are a great way to access external data sources, but it lacks proper support for visibility and transactions, and so on. Also, the FDW API follows the "tuple at a time" execution model, which prevents a lot of optimizations in the upper part of the stact (vectorized execution etc.). There are several products using FDWs to change storage, but I'd call it a misuse of a feature designed for very different purpose. IMHO it's hardly a way forward without significant changes/improvements (which may happen, I don't know).
- calpaterson 10y ago
  Postgres does not have pluggable storage engines - there is essentially just one way to store things.
fabian2k 10y ago
Just from reading the documentation, the full text search features on Postgres already look pretty powerful. And it is encouraging that they are actively being worked on. I'm wondering how this compares to a dedicated search engine like Solr or Elasticsearch. Are there huge differences in performance, features or search quality? At which scale does using Postgres for full text search still make sense?
- fatbird 10y ago
  I used it at 9.4 for a document management system with thousands, not millions, of PDFs that got indexed on upload, and it worked extremely well at that scale--fast, and with all the basic text search features well-covered (tokenization, stemming, etc.). A big win for me was that doing it well in Postgres meant the site could stay a simple Django site rather than adding another service.
  - ngrilly 10y ago
    Did you store the plain text of each PDF in PostgreSQL or just the ts_vector resulting from the plain text?
    - fatbird 10y ago
      IIRC, I stored the plain text too because the engine can return contextually marked up plaintext after finding it in the ts_vector.
      - ngrilly 10y ago
        You're right, PostgreSQL needs the plain text to highlight it with ts_headline. It's similar to Elasticsearch keeping the original document in the _source attribute. Thanks!
  - pumainmotion 10y ago
    Curious to know since you mentioned that it was fast for thousands of PDFs... any rough timing information on some of your queries for that kind of dataset?
qaq 10y ago
Congrats on great release. With availability of E7-8800 v4 based servers (up to 192 cores in a single box) PG can cover a huge number of use cases without complicated setups.
tmaly 10y ago
I am interested in the full text search as well as Index-only scans for partial indexes
deleted 10y ago
[deleted]
snowwolf 10y ago
Please can the Postgres team put some major focus on completing logical replication [1]. It's the missing piece to making upgrading across major versions painless and quick on large databases so that we can take advantage of all these nice new features. We're on a Heroku's hosted Postgres service so can't install the pglogical extension. 1. http://blog.2ndquadrant.com/why-logical-replication/ http://blog.2ndquadrant.com/why-logical-replication/
- deleted 10y ago
  [deleted]
- rpedela 10y ago
  It is being worked on. There is a good chance it will be in the next major version. https://commitfest.postgresql.org/10/701/ https://commitfest.postgresql.org/10/701/
- mslate 10y ago
  I don't think you would be able to take advantage of logical replication on Heroku Postgres regardless--they don't allow you to replicate to your own instances, only other Heroku-hosted instances. This makes migrating off Heroku for Postgres a PITA and requiring down-time.
  - snowwolf 10y ago
    True, that would be an extra bonus if Heroku started allowing replication to non Heroku instances, but as long as they support logical replication to a Heroku Follower instance then you can upgrade to new major versions with near zero downtime - set up logical follower running latest postgres version and then promote to master once it has caught up. Currently you can't have a follower that is a different version to master - meaning an upgrade requires either a full backup and restore to new version resulting in significant downtime if you have a large database or using the pg_upgrade utility which is generally not recommended as it is not guaranteed to work.
  - deleted 10y ago
    [deleted]
mgberlin 10y ago
Does anyone know when this will be available on AWS RDS?
- nhumrich 10y ago
  Probably in 3-4 months. AWS has historically had a 3 month gap time for postgres. Their policy (from what they have said on the forums at least) is they wait for at least x.x.1 release before they start working on it.
- htn 10y ago
  While not AWS product, Aiven (https://aiven.io/postgresql https://aiven.io/postgresql) has a hosted offering with 9.6 support on AWS as well as on Azure, GCE and DigitalOcean.
- aidos 10y ago
  On that topic - what's the general feeling about RDS? I'm running pg on ec2 with a hot standby slave. I need the postgis extension but am not doing anything particularly esoteric. Ideally I'd like to have the certainty of aws handling backups for me. I was researching moving to RDS today and would love to hear thoughts on whether it's a good general solution or not. What happens about downtime during upgrades or swapping instance sizes?
  - luhn 10y ago
    > What happens about downtime during upgrades or swapping instance sizes? This is one of my favorite features of RDS: You can set a maintenance window and have the option to not have changes take effect until that window. So if I want to upgrade Postgres or change the instance size, I set it up and the downtime happens when I'm fast asleep and nobody is using the site. I also think (but not 100% sure) that if you have Multi-AZ enabled, changes are done by upgrading the slave, failing over, and then upgrading the ex-master, so downtime is limited to the failover period.
    - aidos 10y ago
      Ah ok. That's useful info about the multiAZ setup - I'll have a look into that. In my case, we now have customers around the world so we don't get the "night time" luxury. Part of the work I'm now completing is to split the system into an accounts db and customer data db. I was thinking to dip my toe in the water by just moving the account db to RDS to see how it goes.
Roboprog 10y ago
Is it just selection bias from posted links on HN, or has the PostgreSQL team been doing many (feature) releases lately? Sounds good!
- dragonwriter 10y ago
  > Is it just selection bias from posted links on HN, or has the PostgreSQL team been doing many (feature) releases lately? I think more like the former -- as I recall, the recent articles have mostly been about specific work going on for the 9.6 release, prereleases of 9.6, and now the actual release of 9.6.
- pgaddict 10y ago
  There's still only one major PostgreSQL release per year. There were a few posts about cool stuff built on top of PostgreSQL, a few posts about progress of the 9.6 development (e.g. when the parallel query got committed) etc.
  - anarazel 10y ago
    > There's still only one major PostgreSQL release per year. Well, due to the delayed 9.5 release (January 7th), there have been two this year ;)
    - pgaddict 10y ago
      Well, that really depends on where exactly you place start of a year ;-) Chinese New Year was February 8, 2016. Orthodox New Year was January 14, 2016. So it's 2:1 for me.
      - zejn 10y ago
        Well, to the best of my knowledge I think PostgreSQL currently only supports Gregorian calendar system ... ;)
malisper 10y ago
> Index-only scans for partial indexes This one is huge for my company. Almost every single query of ours could use an index-only scan, but the planner would never choose to perform one because of the weirdness around partial indexes. We expecting a several x speedup once we upgrade to 9.6. All the need to improve now is a way to keep the visibility map up to date without relying on vacuums.
- snuxoll 10y ago
  I don't see ever going away from using vacuum to maintain the visibility map, but hopefully the changes in 9.6 will make it a non-issue on large tables.
  - malisper 10y ago
    I think there is already a patch for Postgres 10 that runs the vacuum on insert only tables. While not completely solving the problem, that will be helpful.
  - deleted 10y ago
    [deleted]
  - anarazel 10y ago
    > I don't see ever going away from using vacuum to maintain the visibility map I don't think that's that unlikely to change. There's two major avenues: Write it during hot-pruning (which is done on page accesses), and perform a "lower impact" vacuum on insert-only tables more regularly > but hopefully the changes in 9.6 will make it a non-issue on large tables. You mean the freeze map? That doesn't really change the picture for regular vacuums, it changes how bad anti-wraparound vacuums are. The impact of the table vacuum itself is most of the time not that bad these days (due to the visibility map), what's annoying is usually the corresponding index scans. They have to scan the whole index, which is quite expensive.
- pgaddict 10y ago
  Thanks, it's nice to see the patch is likely beneficial for other people!
n4nagappan 10y ago
Does Postgres offer search based on tf-idf?
- ris 10y ago
  Yes and it's quite flexible in doing so https://www.postgresql.org/docs/9.6/static/textsearch-controls.html#TEXTSEARCH-RANKING https://www.postgresql.org/docs/9.6/static/textsearch-contro...
Chayanon1981 10y ago
kk

553 points by sheff 10y ago

Anyone know the state of BDR in 9.6? http://blog.2ndquadrant.com/bdr-is-coming-to-postgresql-9-6/ http://blog.2ndquadrant.com/bdr-is-coming-to-postgresql-9-6/

okket 10y ago
See discussion from 3 days ago (69 comments): https://news.ycombinator.com/item?id=12576116 https://news.ycombinator.com/item?id=12576116 TL;DR: It is not in mainline, but it does not need a patch anymore. You need to bring your own conflict resolution logic.
- pgaddict 10y ago
  Or design the application so that there are no conflicts (e.g. modifying different subsets of users on different nodes).
  - jimktrains2 10y ago
    Which is a form of conflict resolution. It requires the application to be aware of the datastore. I wonder if, since BDR is just a plugin now, a plugin that used strong consistency guarantees could be built using the same changes that were required for BDR.
- anarazel 10y ago
  BDR has last-updated-wins builtin and conflict handlers that can be called if that's not what you need.
- deleted 10y ago
  [deleted]
mamcx 10y ago
What is BDR?
- joeriel 10y ago
  Bi-directional replication

jbkkd 10y ago

Congratulations to the PostgreSQL Global Development Group on a much-anticipated release. Curious about this: > parallelism can speed up big data queries by as much as 32 times faster Why would it be only 32 times faster? The sky's the limit if there aren't major bottlenecks on the way.

greggyb 10y ago
No one has tested a query that got more than 32x faster, so they don't want to promise something they can't prove.
- Jweb_Guru 10y ago
  There's also a limited amount of memory-level parallelism available... with 4-DIMM sockets you might need an 8-socket machine to get a 32x improvement on large (memory-bound) sequential scans, which I'd guess you can get on top-end Power machines. (You can probably get more memory level parallelism with random access, but your overall bandwidth will likely be lower... fully exploiting memory bandwidth is complicated and difficult to do for real applications).
- joshberkus 10y ago
  That's pretty much the case, yes.
noxin 10y ago
They likely benchmarked it on a 32 core system. Like a dual Opteron board. If the task was single-threaded before a 32-fold improvement is reasonable.
- pedrocr 10y ago
  It's very difficult to get a 32x speedup from 32 cores as there are always parts that are inherently serial, so it's more likely they tested it on a 64 core machine or something like that.
  - 10y ago

ignoramous 10y ago

A tangential question: Everyone speaks about InnoDB and how performant and reliable it is... and multiple firms even use it as a KV-store (Uber/Pinterest/AWS) bypassing MySQL entirely. I have never heard much about storage engines in Postgres, why could this be so? Wikipedia has a (stub) article on InnoDB, but nothing on Postgres' storage engines... just wondering why that is.

dhd415 10y ago
PostgreSQL, for better or worse, doesn't have pluggable storage engines. There's some discussion on their dev mailing list about the possibility of adding that capability in PG10, though: http://postgresql.nabble.com/Pluggable-storage-td5916322.html http://postgresql.nabble.com/Pluggable-storage-td5916322.htm... Some earlier (2013) discussion on the same topic: https://wiki.postgresql.org/wiki/2013UnconfPluggableStorage https://wiki.postgresql.org/wiki/2013UnconfPluggableStorage
- asah 10y ago
  tl;dr: Foreign Data Wrappers (FDW) provide 99% of the same functionality, but with even more flexibility incl smart query optimizer support.
  - pgaddict 10y ago
    That is far too optimistic, IMHO. FDW are a great way to access external data sources, but it lacks proper support for visibility and transactions, and so on. Also, the FDW API follows the "tuple at a time" execution model, which prevents a lot of optimizations in the upper part of the stact (vectorized execution etc.). There are several products using FDWs to change storage, but I'd call it a misuse of a feature designed for very different purpose. IMHO it's hardly a way forward without significant changes/improvements (which may happen, I don't know).
calpaterson 10y ago
Postgres does not have pluggable storage engines - there is essentially just one way to store things.

fabian2k 10y ago

Just from reading the documentation, the full text search features on Postgres already look pretty powerful. And it is encouraging that they are actively being worked on. I'm wondering how this compares to a dedicated search engine like Solr or Elasticsearch. Are there huge differences in performance, features or search quality? At which scale does using Postgres for full text search still make sense?

fatbird 10y ago
I used it at 9.4 for a document management system with thousands, not millions, of PDFs that got indexed on upload, and it worked extremely well at that scale--fast, and with all the basic text search features well-covered (tokenization, stemming, etc.). A big win for me was that doing it well in Postgres meant the site could stay a simple Django site rather than adding another service.
- ngrilly 10y ago
  Did you store the plain text of each PDF in PostgreSQL or just the ts_vector resulting from the plain text?
  - fatbird 10y ago
    IIRC, I stored the plain text too because the engine can return contextually marked up plaintext after finding it in the ts_vector.
    - ngrilly 10y ago
      You're right, PostgreSQL needs the plain text to highlight it with ts_headline. It's similar to Elasticsearch keeping the original document in the _source attribute. Thanks!
- pumainmotion 10y ago
  Curious to know since you mentioned that it was fast for thousands of PDFs... any rough timing information on some of your queries for that kind of dataset?

qaq 10y ago

Congrats on great release. With availability of E7-8800 v4 based servers (up to 192 cores in a single box) PG can cover a huge number of use cases without complicated setups.

tmaly 10y ago

I am interested in the full text search as well as Index-only scans for partial indexes

deleted 10y ago

[deleted]

snowwolf 10y ago

Please can the Postgres team put some major focus on completing logical replication [1]. It's the missing piece to making upgrading across major versions painless and quick on large databases so that we can take advantage of all these nice new features. We're on a Heroku's hosted Postgres service so can't install the pglogical extension. 1. http://blog.2ndquadrant.com/why-logical-replication/ http://blog.2ndquadrant.com/why-logical-replication/

deleted 10y ago
[deleted]
rpedela 10y ago
It is being worked on. There is a good chance it will be in the next major version. https://commitfest.postgresql.org/10/701/ https://commitfest.postgresql.org/10/701/
mslate 10y ago
I don't think you would be able to take advantage of logical replication on Heroku Postgres regardless--they don't allow you to replicate to your own instances, only other Heroku-hosted instances. This makes migrating off Heroku for Postgres a PITA and requiring down-time.
- snowwolf 10y ago
  True, that would be an extra bonus if Heroku started allowing replication to non Heroku instances, but as long as they support logical replication to a Heroku Follower instance then you can upgrade to new major versions with near zero downtime - set up logical follower running latest postgres version and then promote to master once it has caught up. Currently you can't have a follower that is a different version to master - meaning an upgrade requires either a full backup and restore to new version resulting in significant downtime if you have a large database or using the pg_upgrade utility which is generally not recommended as it is not guaranteed to work.
- deleted 10y ago
  [deleted]

mgberlin 10y ago

Does anyone know when this will be available on AWS RDS?

nhumrich 10y ago
Probably in 3-4 months. AWS has historically had a 3 month gap time for postgres. Their policy (from what they have said on the forums at least) is they wait for at least x.x.1 release before they start working on it.
htn 10y ago
While not AWS product, Aiven (https://aiven.io/postgresql https://aiven.io/postgresql) has a hosted offering with 9.6 support on AWS as well as on Azure, GCE and DigitalOcean.
aidos 10y ago
On that topic - what's the general feeling about RDS? I'm running pg on ec2 with a hot standby slave. I need the postgis extension but am not doing anything particularly esoteric. Ideally I'd like to have the certainty of aws handling backups for me. I was researching moving to RDS today and would love to hear thoughts on whether it's a good general solution or not. What happens about downtime during upgrades or swapping instance sizes?
- luhn 10y ago
  > What happens about downtime during upgrades or swapping instance sizes? This is one of my favorite features of RDS: You can set a maintenance window and have the option to not have changes take effect until that window. So if I want to upgrade Postgres or change the instance size, I set it up and the downtime happens when I'm fast asleep and nobody is using the site. I also think (but not 100% sure) that if you have Multi-AZ enabled, changes are done by upgrading the slave, failing over, and then upgrading the ex-master, so downtime is limited to the failover period.
  - aidos 10y ago
    Ah ok. That's useful info about the multiAZ setup - I'll have a look into that. In my case, we now have customers around the world so we don't get the "night time" luxury. Part of the work I'm now completing is to split the system into an accounts db and customer data db. I was thinking to dip my toe in the water by just moving the account db to RDS to see how it goes.

Roboprog 10y ago

Is it just selection bias from posted links on HN, or has the PostgreSQL team been doing many (feature) releases lately? Sounds good!

dragonwriter 10y ago
> Is it just selection bias from posted links on HN, or has the PostgreSQL team been doing many (feature) releases lately? I think more like the former -- as I recall, the recent articles have mostly been about specific work going on for the 9.6 release, prereleases of 9.6, and now the actual release of 9.6.
pgaddict 10y ago
There's still only one major PostgreSQL release per year. There were a few posts about cool stuff built on top of PostgreSQL, a few posts about progress of the 9.6 development (e.g. when the parallel query got committed) etc.
- anarazel 10y ago
  > There's still only one major PostgreSQL release per year. Well, due to the delayed 9.5 release (January 7th), there have been two this year ;)
  - pgaddict 10y ago
    Well, that really depends on where exactly you place start of a year ;-) Chinese New Year was February 8, 2016. Orthodox New Year was January 14, 2016. So it's 2:1 for me.
    - zejn 10y ago
      Well, to the best of my knowledge I think PostgreSQL currently only supports Gregorian calendar system ... ;)

malisper 10y ago

> Index-only scans for partial indexes This one is huge for my company. Almost every single query of ours could use an index-only scan, but the planner would never choose to perform one because of the weirdness around partial indexes. We expecting a several x speedup once we upgrade to 9.6. All the need to improve now is a way to keep the visibility map up to date without relying on vacuums.

snuxoll 10y ago
I don't see ever going away from using vacuum to maintain the visibility map, but hopefully the changes in 9.6 will make it a non-issue on large tables.
- malisper 10y ago
  I think there is already a patch for Postgres 10 that runs the vacuum on insert only tables. While not completely solving the problem, that will be helpful.
- deleted 10y ago
  [deleted]
- anarazel 10y ago
  > I don't see ever going away from using vacuum to maintain the visibility map I don't think that's that unlikely to change. There's two major avenues: Write it during hot-pruning (which is done on page accesses), and perform a "lower impact" vacuum on insert-only tables more regularly > but hopefully the changes in 9.6 will make it a non-issue on large tables. You mean the freeze map? That doesn't really change the picture for regular vacuums, it changes how bad anti-wraparound vacuums are. The impact of the table vacuum itself is most of the time not that bad these days (due to the visibility map), what's annoying is usually the corresponding index scans. They have to scan the whole index, which is quite expensive.
pgaddict 10y ago
Thanks, it's nice to see the patch is likely beneficial for other people!

n4nagappan 10y ago

Does Postgres offer search based on tf-idf?

ris 10y ago
Yes and it's quite flexible in doing so https://www.postgresql.org/docs/9.6/static/textsearch-controls.html#TEXTSEARCH-RANKING https://www.postgresql.org/docs/9.6/static/textsearch-contro...

Chayanon1981 10y ago