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.
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.