News for hackers. Stuff that matters.
Re:Setting Up a Fast, Comprehensive Search Routine with Post... by deedubaya on Sunday July 29, 2018 @06:38PM (#17640742)
I just yanked elasticsearch out of an app and replaced it with PG's full text search.

It's like shedding a 500# pack from my shoulders. Everything is so much simpler now -- dev is easier, testing is easier, deployment is easier. Infrastructure costs are down.

Elasticsearch is a pretty neat tool, but what I was using it for was heavy handed. I only chose it because it was what everyone else was using it.

Re:Setting Up a Fast, Comprehensive Search Routine with Post... by brightball on Sunday July 29, 2018 @08:34PM (#17641270)
I did the same exact same thing with Solr a few years back. PG performed equally for speed, the results were more accurate and the data inconsistencies vanished. Solr was such a black box to debug that it couldn’t be trusted.

After I left the dev hired to replace me put Solr back.

Re:Setting Up a Fast, Comprehensive Search Routine with Post... by ngrilly on Monday July 30, 2018 @04:50AM (#17643036)
What is the size of your dataset (number of documents and average size of documents)?
Re:Setting Up a Fast, Comprehensive Search Routine with Post... by brightball on Monday July 30, 2018 @07:47AM (#17643763)
It’s been a while so I’m going from memory here.

We had about 14 million records and the full text search included 3 varchar 255 and a text that could get very long depending on the user. These were product auction listings.

I don’t remember the size specifics though.

Re:Setting Up a Fast, Comprehensive Search Routine with Post... by ngrilly on Monday July 30, 2018 @10:45AM (#17644733)
Thanks. That's not a small dataset. It's significant you got good enough performance on this. How many docs matched a typical query? And what was a typical response time?
Re:Setting Up a Fast, Comprehensive Search Routine with Post... by brightball on Monday July 30, 2018 @01:51PM (#17646449)
It's been a long time. I honestly don't remember.

At the time, when we removed Solr and replaced it with an assembled query the performance was at least equal but our data issues vanished.

We were having serious issues with keeping the Solr dataset in sync with changes, invalidating old ones and getting new ones to appear. A user pays to create a listing, they expect to see the listing in the search...and sometimes that wasn't happening. When a listing expired, sometimes it wasn't going away. Being able to just keep this stuff up to date with a simple PG trigger completely eliminated the problems and made it easy to tune.

The search used a combination of full text, categories, geographic distance, user names and a couple of other filters. Being able to simply construct each of those pieces in a query, sometimes filtering parts with subqueries, etc was really effective. You had a few indexes involved.

Keep in mind, this is also before I knew anything about Postgres partitioning with PG Partman or how to create partial indexes. I could have made it a lot more efficient than it was, in hindsight.

The experience was good enough though that I don't even begin to think about pulling in a 3rd party search tool unless I have project requirements that make it unavoidable. Elastic Search is really good for use cases with constant streaming data ingestion, for example. If I just need search for data that's already in the database though...it's really hard to justify.

Re:Setting Up a Fast, Comprehensive Search Routine with Post... by ngrilly on Tuesday July 31, 2018 @03:44AM (#17651290)
Great info. Thanks!
Re:Setting Up a Fast, Comprehensive Search Routine with Post... by nickjj on Sunday July 29, 2018 @09:36PM (#17641471)
I did the same thing to one project a few years ago. It was a major win. Even with decent library support, keeping data synced and indexed from PG <-> ES was such a burden.
Re:Setting Up a Fast, Comprehensive Search Routine with Post... by benoror on Sunday July 29, 2018 @09:54PM (#17641541)
fwiw, I ran into issues when trying to perform more robust search algorithms in PG, such as fuzzy search: https://medium.com/the-backlog-by-nimbo-x/dev-notes-fuzzy-se...
Re:Setting Up a Fast, Comprehensive Search Routine with Post... by thejosh on Sunday July 29, 2018 @10:56PM (#17641736)
Yeah, if you have a simpleish app (or even not a simple app) you can probably benefit from playing around with how PG does search without needed ES/Solr/etc. But it really depends what you do, obviously.
Re:Setting Up a Fast, Comprehensive Search Routine with Post... by ngrilly on Monday July 30, 2018 @04:50AM (#17643035)
What is the size of your dataset (number of documents and average size of documents)?