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

Postgres full-text search is good enough

blog.lostpropertyhq.com

Post reply on HN
Print view View original

51–60 of 60 posts

Re: Postgres full-text search is good enough

#51

Post by wuliwong »

why-el wrote: post #45
rachbelaid wrote: post #42

Earlier quoted context omitted.

To be honest, I'm quite surprise to see that somebody posted this article again.. I don't know HN rules when you can post again something. I'm not going to complain about it :)

I think you can repost about a year. It could have changed tough.

I don't know when it was posted, but it definitely was less than one year ago. My main data point is that it still is living in an open tab in Chrome. I have a couple of sites which allow submitting links. I know people can get around my requirement that they are unique pretty easily. I'm sure HN is more sophisticated but I think that just having different url parameters might qualify as a different link? Sometimes a url parameter change really does point to a different page, so it might not be possible to eliminate duplicates that have the same url with just different params.

Re: Postgres full-text search is good enough

#52

Post by wuliwong »

rachbelaid wrote: post #42
wuliwong wrote: post #40

Earlier quoted context omitted.

You just posted this today? This isn't the first time your article has been on the front page of HN. I actually still have your article "saved" in a tab in chrome from the last time it was on HN. It made quite an impression on me, I'm in the process of moving our dev environments from sqlite to postgres, so that we can start adding some of this functionality. Thanks.

To be honest, I'm quite surprise to see that somebody posted this article again.. I don't know HN rules when you can post again something. I'm not going to complain about it :)

It's definitely a cool article, I don't mind seeing it twice. I love the idea of having these tools with postgres and not having to use thinking sphinx or solr right as quickly.

Re: Postgres full-text search is good enough

#53

Post by the_watcher »

wuliwong wrote: post #51
why-el wrote: post #45

Earlier quoted context omitted.

I think you can repost about a year. It could have changed tough.

I don't know when it was posted, but it definitely was less than one year ago. My main data point is that it still is living in an open tab in Chrome. I have a couple of sites which allow submitting links. I know people can get around my requirement that they are unique pretty easily. I'm sure HN is more sophisticated but I think that just having different url parameters might qualify as a different link? Sometimes a…

URL parameters don't qualify as a different link (or else anyone submitting a url without removing them would have unique posts). I always thought the rule had to do with comments being allowed on the post, which, I believe, are based on comment activity. My assumption was as long as an article still allows comments, resubmissions don't become new posts.

Re: Postgres full-text search is good enough

#54

Post by tensor »

justin_vanw wrote: post #28
tensor wrote: post #24

Earlier quoted context omitted.

This post shows the problem when people ignore theory. Elasticsearch and Lucene are simply inverted indexes. PostgreSQL full text search is also an inverted index, but there are some small architectural issues that make the difference. Depending on your use case, you cannot replace like or ilike with full text search. You may need to use a trigram index, which you cannot do with ElasticSearch and Lucene. PostgreSQL t…

This is not a case of 'ignoring the theory', it's a case of you being pedantic. I didn't say "replace 'like' queries with identical functionality", I said 'replace', as in 'supersede' and 'improve upon'. Except for insane cases such as the search 'ant' matching the word 'pedANTic', which is to say whenever whole tokens are intended to be matched, postgresql's full text search will replace 'like' queries for you with…

You seem to be implying that the primary purpose of like queries is to do full text search, which is of course not true. E.g. perhaps you need prefix matching, or perhaps you are implementing an autocomplete box where you want partial matching, or you are searching over filenames and want partial matching, the latter two both being "insane" cases in your world.

And yes, there are GiST indexes for text in addition to inverted indexes. I didn't bring these up because you are comparing primarily to products using inverted indexes. My point is to illustrate that these products are actually mostly the same, with differences primarily around tooling.

No one is arguing that Solr and Lucene don't have advantages over PostgreSQL's text search. But I strongly disagree with your claim that "full text search lacks the feature set to be a reasonable solution to most search use cases."

You are quick to claim that postgresql cannot use customize tokenizers or cannot scale or doesn't have features. Then in the same paragraph say that well, yes, it can do both these things but it's harder to work with. You also wipe all the other advantages that postgresql brings under the rug with a dismissive "oh well these are minor situations." I guess they are minor in your products, but they are certainly not minor in others.

If you only need text search, or are using a database other than postgres, then Solr and Lucene are great choices. On the other hand, if you are using PostgreSQL already, its' full text search is entirely reasonable and feature rich for many if not most search cases.

Re: Postgres full-text search is good enough

#55

Post by rachbelaid »

jaddison wrote: post #50
rachbelaid wrote: post #32

As the author, it may be good that I clarify the goals behind this post. First I'm glad that this post made HN first page for a second time. I encourage to read this comment because it summarizes well few of the missing features in Postgres about search. https://news.ycombinator.com/item?id=8715624 It's not the first time that I get similar feebacks and I understand that "Enough" is subjective notion and maybe I shou…

Speaking of syncing strategies, I use Django with PostgreSQL as the main datastore and Elasticsearch as a secondary data store. PostgreSQL receives all admin and staff related updates and Elasticsearch is updated automatically using signals (and celery tasks if necessary). I created a Django app to help with this syncing - check it out at https://github.com/jaddison/django-simple-elasticsearch . I love feedback and p…

It's also a Django project, I like what you have done. It's a nice lib and it's intuitive. How do you handle a ES document which is a composition of multiple Django Model?

It's just personal opinion but I try to avoid having my application responsible of the data integrity so I went into the way of ES River plugin to pull data. I used the JDBC one, https://github.com/jprante/elasticsearch-river-jdbc ... I met some problems but at the end, it works quite well and don't need logic in the application keep the data synced.

Two other reason that I used River was to not make the app slower by saving data in ES and also of being able to run the application during development without the need of ES and being installed able to substitute the search with a stub.

Re: Postgres full-text search is good enough

#56

Post by rachbelaid »

wuliwong wrote: post #51
why-el wrote: post #45

Earlier quoted context omitted.

I think you can repost about a year. It could have changed tough.

I don't know when it was posted, but it definitely was less than one year ago. My main data point is that it still is living in an open tab in Chrome. I have a couple of sites which allow submitting links. I know people can get around my requirement that they are unique pretty easily. I'm sure HN is more sophisticated but I think that just having different url parameters might qualify as a different link? Sometimes a…

I don't know why and I can't help you, in both case I didn't post it. But we don't have multiple url for our blog. I find it odd to be honest as the post was posted on HN the 29th of September (base on Google Analytic)

Re: Postgres full-text search is good enough

#57

Post by rachbelaid »

mdemare wrote: post #8

no_future (dead) says: I've actually tried both Postgres FTS and Elasticsearch(in conjunction with a Postgres DB) and found Elasticsearch easier for search. It's much more robust - for my specific case I needed a bunch of Japanese text to be searchable alongside english text; Postgres has very poor support for this - and its API is excellent and its very quick and painless to set up(documentation is not stellar thoug…

Following the project for which I wrote this blog post, we recently moved to ES mainly because of Japanese support. It's an important point to highlight and not all the internet is english. I was satisfy with PG for english, french, spanish but PG extension for Japanes was not looking inspiring

Re: Postgres full-text search is good enough

#58

Post by jaddison »

rachbelaid wrote: post #55
jaddison wrote: post #50

Earlier quoted context omitted.

Speaking of syncing strategies, I use Django with PostgreSQL as the main datastore and Elasticsearch as a secondary data store. PostgreSQL receives all admin and staff related updates and Elasticsearch is updated automatically using signals (and celery tasks if necessary). I created a Django app to help with this syncing - check it out at https://github.com/jaddison/django-simple-elasticsearch . I love feedback and p…

It's also a Django project, I like what you have done. It's a nice lib and it's intuitive. How do you handle a ES document which is a composition of multiple Django Model? It's just personal opinion but I try to avoid having my application responsible of the data integrity so I went into the way of ES River plugin to pull data. I used the JDBC one, https://github.com/jprante/elasticsearch-river-jdbc ... I met some pr…

Good question - django-simple-elasticsearch is definitely focused on generating a document from a single instance of a specific model. Of course, you can add supplementary data from associated models via M2M or FK models as you see fit (nested objects, etc.)

I've thought of adding in support for pushing bulk index request data to redis (for example) so that an Elasticsearch river could pull from it; this would decouple the app somewhat - but not completely as you've noted. It would likely help with throughput however, and still provide you with the ability to do pre-processing on the data as needed within your app's/project's context.

The Elasticsearch JDBC river isn't as flexible for processing data if I'm not mistaken, as it doesn't have context for the data? Please correct me if I'm wrong, but it's somewhat limited?

Re: Postgres full-text search is good enough

#59

Post by rachbelaid »

jaddison wrote: post #58
rachbelaid wrote: post #55

Earlier quoted context omitted.

It's also a Django project, I like what you have done. It's a nice lib and it's intuitive. How do you handle a ES document which is a composition of multiple Django Model? It's just personal opinion but I try to avoid having my application responsible of the data integrity so I went into the way of ES River plugin to pull data. I used the JDBC one, https://github.com/jprante/elasticsearch-river-jdbc ... I met some pr…

Good question - django-simple-elasticsearch is definitely focused on generating a document from a single instance of a specific model. Of course, you can add supplementary data from associated models via M2M or FK models as you see fit (nested objects, etc.) I've thought of adding in support for pushing bulk index request data to redis (for example) so that an Elasticsearch river could pull from it; this would decoup…

I think that you nailed it, Elasticsearch JDBC is not flexible and you need to be good in SQL if you have a complex model with many relation. Because JOIN create a cartesian product you can end with duplicate and you have to find a way to avoid that because duplicate affect the ranking. In my case I ended using UNION queries.

Also you cannot also have multiple queries but not to update and existing document(eg: adding extra fields) The SQL query for me ended being ~100 lines but the model is quite complex with multiple languages support.

Re: Postgres full-text search is good enough

#60

Post by Argorak »

rachbelaid wrote: post #32

As the author, it may be good that I clarify the goals behind this post. First I'm glad that this post made HN first page for a second time. I encourage to read this comment because it summarizes well few of the missing features in Postgres about search. https://news.ycombinator.com/item?id=8715624 It's not the first time that I get similar feebacks and I understand that "Enough" is subjective notion and maybe I shou…

I gave a talk at the Elasticsearch usergroup about that topic and evaluated a few solutions. I think Postgres fulltext falls into the interesting spot of being "just there" if you are using PG already and being very robust. It has a visible ceiling, but if you are okay with that, go with it. (take care that PG does not implement UTF-8 full but UCS-2, but does not refuse UTF-8, as I pointed out in the original post)

If you get started with building a secondary system for searching anyways, I wouldn't recommend it.

Also, don't use MongoDB fulltext, it's just broken.

Post reply on HN