Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL 9.6 Released

postgresql.org

Post reply on HN
Print view View original

51–60 of 136 posts

Re: PostgreSQL 9.6 Released

#51

Post by buro9 »

fabian2k wrote: post #17

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?

I have found Postgres to be good enough for search.

As in... it works well enough, and the advantage of not having to add other tech makes it a no-brainer, I've had zero support issues or customer complaints and most of my applications use full text search heavily.

The big advantage over other approaches, because it's SQL and it's there in my database where I also store users and permissions knowledge... I can permission-limit my fulltext searches.

Re: PostgreSQL 9.6 Released

#52

Post by kuschku »

taneliv wrote: post #48
fabian2k wrote: post #17

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?

Can pgsql fts do stemming or more complex lemmatisation for languages other than English? Or ranking of results based on Okapi BM25 or similar? I was looking into this about two years ago and those were the features in favor of Lucene (basis of ES and Solr).

Pgsql can do stemming and everything it can do in English also in several dozen languages, including German, French, Spanish, and any for which you install dictionaries. It’s quite useful

Re: PostgreSQL 9.6 Released

#53

Post by rpedela »

taneliv wrote: post #48
fabian2k wrote: post #17

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?

Can pgsql fts do stemming or more complex lemmatisation for languages other than English? Or ranking of results based on Okapi BM25 or similar? I was looking into this about two years ago and those were the features in favor of Lucene (basis of ES and Solr).

As far as I am aware, it does not support BM25. See pilif's comment about multiple language support.

Re: PostgreSQL 9.6 Released

#55

Post by rpedela »

snowwolf wrote: post #32

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/

It is being worked on. There is a good chance it will be in the next major version.

https://commitfest.postgresql.org/10/701/

Re: PostgreSQL 9.6 Released

#56

Post by mslate »

snowwolf wrote: post #32

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/

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.

Re: PostgreSQL 9.6 Released

#57

Post by anarazel »

okket wrote: post #3
gtrubetskoy wrote: post #2

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

See discussion from 3 days ago (69 comments): 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.

BDR has last-updated-wins builtin and conflict handlers that can be called if that's not what you need.

Re: PostgreSQL 9.6 Released

#58

Post by pilif »

ngrilly wrote: post #50
pilif wrote: post #39

Earlier quoted context omitted.

While it's ok for our purposes, I would wish for a bit better customisability of the text parser and it definitely needs better support for compound words to be perfect. The first issue is with relation to https://www.postgresql.org/docs/9.6/static/textsearch-parser... : The documentation says > At present PostgreSQL provides just one built-in parser, which has been found to be useful for a wide range of applications…

Do you rank full-text search results using something like ts_rank? If yes, do you suffer from slow queries?

We use it, but we don't suffer slow queries in our case.

Re: PostgreSQL 9.6 Released

#59

Post by [deleted] »

okket wrote: post #3
gtrubetskoy wrote: post #2

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

See discussion from 3 days ago (69 comments): 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.

[deleted]

Re: PostgreSQL 9.6 Released

#60

Post by greggyb »

olavgg wrote: post #27
jbkkd wrote: post #4

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.

I tested parallel queries on PostgreSQL 9.6 on a few TBs of data, 5 billion rows on an older dual Xeon E5620 server. I also striped 4 Intel S3500 800GB drivers with ZFS and enabled LZ4 compression which has a compressratio of 4x. For a sequential full table scan I could process about 2000MB/s of data(only 125MB/s was read from each SSD), I was limited by CPU power. Anyway, same query took about 25 minutes on PostgreS…

Would you be willing to re-run that with SQL Server 2016? A Dev license is free, and there's been a lot of relational engine optimization since 2012. I'd be curious to see what the latest release can do compared to Postgres' latest.

I realize I'm asking a stranger on the internet to do something for free for me. If you don't have time or inclination to do this, no worries, but it seems like you've got a nice setup to be able to play with this. I'm sure I'm not the only one curious to see such a comparison.

Post reply on HN