Postgres full-text search is good enough

It points to a pathology of HN that someone discussing their experience with a specific technology is getting downvoted and flagged for describing their own experiences. This is not reddit, the downvote is not meant for enforcing groupthink.

I'm in favor of Postgres, I make my living in large part from my expertise with it; but it rubs me the wrong way when people downvote an informative comment covering someone's experience where it didn't happen to be the right tool.

no_future I apologise to you for the poor reception your comment got from immature people who don't understand how to discuss things and who cannot handle new information. This did not used to be representative of the community here.

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 user click data, by generalizing click data onto the feature space of the document set (using mutual information). http://www.google.com/patents/US20140067786

So, why would I recommend against using Postgres' full text search:

- It is not configurable. A search engine like Solr or ElasticSearch allows you to configure the tokenization pipeline, what is indexed, how it is indexed, etc. Postgres hard-codes it's tokenization pipeline. You can turn stemming on and off, but you can't, for example, lowercase text, strip out html characters, handle special cases for tokenization (camel cased or delimited text, etc). You can write your own tokenizer, but it has to be as a C extension. Could you get it to do what you want? Mostly, but only with an order of magnitude more work than with Solr or ElasticSearch.

- Postgres has no facility to make it possible to do faceting (histograms). This is a pretty big deal breaker for most kinds of eCommerce. I've seen a few attempts at this, but they are incredibly complex and slow compared to other search engines.

- Postgres does not give you much control over ranking. It has some good generic ranking methods, but those will only get you so far. (Query Amazon for 'ipad -dkfjdkf' to see how well it's generic ranking function is. No generic ranking function can do very well compared to something tuned to the domain, and Postgres just isn't flexible enough to extend in any reasonable way.

- It is not scalable. Solr and ElasticSearch are both very very easy to scale up (and out). Postgres' built in replication is very heavy, as it has to stream all database changes to all slaves (yes there are things like SLONY but they are incredibly hard to deal with). In any real environment, you have to protect your database from load. It's the the hardest thing to scale, and consulting it for all traffic is a surefire way to run into issues that are very hard to get back out of.

Now, with all that said, I would recommend it for a few cases:

- You just want to do SQL queries with 'LIKE' queries, but indexed.

- You need to do arbitrary relational constraints on top of text queries. Doing a query like "give me all the tickets closed in November by Hugh where the project was ProjectX and the ticket name has the word "nested loop" in it's title OR it's description OR it's title is less than 15 characters long" is going to be more or less impossible in any other environment. If you were building something like Jira, or really anything that needs to support relational logic, Postgres' full text search would be a great way to go.

- 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. In that case, being able to integrate with your existing queries, and not having to support a seperate indexing step and corresponding index updating processes is a huge win.

Hi Justin, do you have a link to a technical paper on the anonymous? click algorithm. Was curious if you applied it to domains other than products/shopping.

What I don't like about shadow-banning is that it's a death sentence without warning appeal for a single misstep. Drank a bit too much and typed a comment you're going to regret the next morning? Shadowban. At least, that's what it looks like from the history of the few shadowbanned people I looked at.

I linked to the patent application (which has been denied).

The algorithm is actually very very simple.

1. produce a set of 'features' for every document. This is what you will index into the inverted index. Features can be words from the document, or they can be things like 'price_between_0_100'.

2. Build a set of pairs (search_phrase, clicked_on_document_id).

3. For each search_phrase in your set of pairs, run the search_phrase through your search engine. The set of all results we will call SET_A.

4. SET_B is the set of documents for a search_phrase that have been clicked on. SET_B is a subset of SET_A. Using mutual information, it is possible to determine which features best differentiate documents from SET_B from (SET_A - SET_B) for a specific search_phrase. These are the features you will 'boost' when you see that search_phrase.

As an example, one of the features this pulled out in practice was the word 'Oceanic' for the search_phrase "lost". It basically zeroed in on the fact that the word 'Oceanic' appearing in the description of a product differentiated 'good' results from 'bad' results for the query 'lost'.

Edit: Here is a hard to follow presentation on it: https://docs.google.com/presentation/d/1xzQbQ1cM3GnaS_LoCvmJ...

Edit2: Another presentation, easy to follow but without most of the technical depth: https://drive.google.com/file/d/0B_eNCTyNa0DHcy1UOGpSUk41NDA...