Postgres full-text search is good enough • Hacker News Board

Re: Postgres full-text search is good enough

#31

Post by chicofarm » Mon, Dec 08, 2014, 11:52 AM UTC

justin_vanw wrote: post #13

As much as I would like it to be 'good enough', Postgres' full text search lacks the feature set to be a reasonable solution to most search use cases. Background: I wrote a product search engine for a company called iTrackr using Postgres full text search, and later wrote the product search for milo.com using Solr. I also designed a simple (but very effective) algorithm to boost search rankings based on historical us…

what about ILIKE '%KeyWords%'? I know it doesn't get ride of 'ing' suffixes, what about performance?

Re: Postgres full-text search is good enough

#32

Post by rachbelaid » Mon, Dec 08, 2014, 11:58 AM UTC

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 should've pick an other title like "Postgres full-text search for basic search need".

Just to clarify, this post is not against ES or SOLR. This tools are great and really powerfull but obviously this tools will better than Postgres Search. ES / SOLR are tools build to be anly around document store and search and on the otherside the search in Postgres is only one feature in many.

I think there is multiple reason when you would want to give Postgres search a go:

- You have no experience with ElasticSearch or SOLR

- The project is still early stage and you don't want to invest time/money in a secondary datastore (eg: ES cluster)

- You want to give quickly a search feature (maybe not perfect) in the hand of your user

- Team knowledge, you may want to be careful before introducing a new tech to your team and ensure that people understand it. There is nothing worst than introducing a tech and being the only one who can maintain it.

The tech world is big and doesn't only apply to Startup usecase, I worked in very different environment: Financial institution, Open Source company, Startup and Service company. A thing that I learn is that you don't always have flexibility. In some company having new servers is long process and even if I hate you have to do with this constraint then you have 2 choices front of you: You do nothing and wait or you move yourself to offer to your users a workaround solution without any extra dependency ... That's what Postgres search can be in some usecases.

From my experience, if you are already using Postgres and you find the right documentation (this post try to describe a start to end example) then I think you could get a search working in 1 day of work without any extra servers or resources.

I don't have any experience with SOLR so I will have to pick ES, but if you never used it then you are going to have loads of "fun":

- Sync. ElasticSearch IMHO cannot be used as a cannonical datasource so you will need to figure out a sync strategy. Some people may argue on that but I don't think that ES started with this goal in mind. They may change direction now but still I wouldn't store my data only in ES. http://aphyr.com/posts/317-call-me-maybe-elasticsearch

- Hosting. In the case that you are not using a hosted ES service then you will need to learn to host and build a cluster and also monitor it.

- Learning to use it. After reading ~50pages of doc then you start to figure out how to use type mapping, custom analyser and the 42 ways to do a search query.

I can only speak from my experience but it took me ~2weeks to get my head around all this point without any previous experience. The project for which I used PG search and wrote this blog post recently moved to ES. More or less, 1y later and I'm glad that the PG solution was good enought until now. In our case we needed to support more languages: Japanese, .. and I also wanted to get rid of a bunch of triggers.

The results are good and very fast but for english the results are quite similar to Postgres solution. I think that's mainly because I'm using much only ES basic feature, but we have loads of room for improvement which is a good position to be.

Now there is few other usecases where you may want to consider PG search:

  - you are using MySQL and you are using the builtin then moving to Postgres can give your search a bit more power

  - Side project using Postgres and you want to start a search feature.

  - Search on machine output, case where analyser doesn't really matter and you don't need stemming. Lucene features for tokenization and stemming are great.

To summarize, I don't think that PG search is a silver bullet but it may be "Good Enough" for your need to get you started. Spend 20min and read the post and then maybe it's also good enough for you. On the other hand if you know already ES/SOLR and you got the resources then it become less relevant to use Postgres Search. ES/SOLR (tools base on Lucene) are amazing and really feature rich, if you have important search need and long term plan about search then you should look into them.

If you are interrested about the PG search and you want to know more about it than I suggest you to have a look to this blog posts. I think that the author did an amazing job.

http://shisaa.jp/postset/postgresql-full-text-search-part-1....

http://shisaa.jp/postset/postgresql-full-text-search-part-2....

http://shisaa.jp/postset/postgresql-full-text-search-part-3....

I don't know if people will be interrested but I thought few times about writing a following blog post "When Postgres search is not Enough" to describe how to integrate Postgres with ElasticSearch with JDBC River to pull content from the DB.. Let me know if it's something that you will be interrested about.

Sorry for the numerous grammatical mistakes but I'm not a native english speaker and I don't want to spend too much time reviewing my comment grammar

Thanks again to upvote this post on the 1st page of HN

Re: Postgres full-text search is good enough

#33

Post by percept » Mon, Dec 08, 2014, 12:50 PM UTC

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…

Thanks for the article and sharing so much of your experience.

Re: Postgres full-text search is good enough

#34

Post by dayone » Mon, Dec 08, 2014, 1:18 PM UTC

this is a awesome post. for most web applications, installing solr/elasticsearch for full-text search capability in addition to a sql database is highly time and maintainence intensive. Postgres for both storing and searching is really useful here.

Re: Postgres full-text search is good enough

#35

Post by dayone » Mon, Dec 08, 2014, 1:21 PM UTC

justin_vanw wrote: post #21

grey-area wrote: post #18

Earlier quoted context omitted.

This is probably the most common case I would recommend it: You already are using Postgres, and you just want to add on some basic search functionality to data that is already in there. Perhaps this is true for more sites than you imagine - you started your comment saying that it is not a reasonable solution to most search use cases, but the vast majority of site search on the web is subpar and way below psql FTS. So…

I'm not convinced that you can magically improve search on those 'tail' sites. Postgres' full text search is nontrivial to set up and use, even if you have some SQL experience. I've done substantial work on text search in the past, and I still have trouble following the docs for FTS. The interface is cumbersome at best. Finally, if search on a site is especially bad, probably the people that made it can't do better,…

Elasticsearch is much more cumbersome to setup. I think Postgres' wins in relative ease of setting up.

Re: Postgres full-text search is good enough

#36

Post by threeseed » Mon, Dec 08, 2014, 1:40 PM UTC

dayone wrote: post #35

justin_vanw wrote: post #21

Earlier quoted context omitted.

I'm not convinced that you can magically improve search on those 'tail' sites. Postgres' full text search is nontrivial to set up and use, even if you have some SQL experience. I've done substantial work on text search in the past, and I still have trouble following the docs for FTS. The interface is cumbersome at best. Finally, if search on a site is especially bad, probably the people that made it can't do better,…

Elasticsearch is much more cumbersome to setup. I think Postgres' wins in relative ease of setting up.

No it isn't. DEB/RPM package install and you're pretty much done.

Re: Postgres full-text search is good enough

#37

Post by grey-area » Mon, Dec 08, 2014, 2:19 PM UTC

justin_vanw wrote: post #21

grey-area wrote: post #18

Earlier quoted context omitted.

This is probably the most common case I would recommend it: You already are using Postgres, and you just want to add on some basic search functionality to data that is already in there. Perhaps this is true for more sites than you imagine - you started your comment saying that it is not a reasonable solution to most search use cases, but the vast majority of site search on the web is subpar and way below psql FTS. So…

I'm not convinced that you can magically improve search on those 'tail' sites. Postgres' full text search is nontrivial to set up and use, even if you have some SQL experience. I've done substantial work on text search in the past, and I still have trouble following the docs for FTS. The interface is cumbersome at best. Finally, if search on a site is especially bad, probably the people that made it can't do better,…

Postgres' full text search is nontrivial to set up and use, even if you have some SQL experience.

Seemed pretty straightforward to me.

Re: Postgres full-text search is good enough

#38

Post by haney » Mon, Dec 08, 2014, 2:40 PM UTC

ddebernardy wrote: post #5

haney wrote: post #2

I've used tsearch2 on several projects and it's always been super handy. It's much easier than adding another dependency just to support search.

In case anyone ends up confused by the parent post, tsearch2 is not an additional Postgres contrib module; rather, it's an old contrib module that the built-in full text search features were built on top of. It has been obsolete forever.

Huh, one of the senior engineers at a previous employer turned me on to it. I completely missed that it was built in now, I feel kind of silly now.

Re: Postgres full-text search is good enough

#39

Post by aaronem » Mon, Dec 08, 2014, 2:42 PM UTC

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…

Thanks for the article, and I'd certainly be interested in seeing "When Postgres Search is Not Enough" -- it'd make an excellent companion piece to the one you've already written.

Re: Postgres full-text search is good enough

#40

Post by wuliwong » Mon, Dec 08, 2014, 3:27 PM UTC

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…

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.