4 ms·

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 s

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.

brightball 8y ago
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.
- ngrilly 8y ago
  What is the size of your dataset (number of documents and average size of documents)?
  - brightball 8y ago
    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.
    - ngrilly 8y ago
      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?
      - brightball 8y ago
        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.
        ngrilly 8y ago
        Great info. Thanks!
nickjj 8y ago
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.
benoror 8y ago
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-search-on-postgresql-beaae7b11f45 https://medium.com/the-backlog-by-nimbo-x/dev-notes-fuzzy-se...
thejosh 8y ago
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.
ngrilly 8y ago
What is the size of your dataset (number of documents and average size of documents)?

brightball 8y ago

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.

ngrilly 8y ago
What is the size of your dataset (number of documents and average size of documents)?
- brightball 8y ago
  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.
  - ngrilly 8y ago
    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?
    - brightball 8y ago
      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.
      - ngrilly 8y ago
        Great info. Thanks!

nickjj 8y ago

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.

benoror 8y ago

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-search-on-postgresql-beaae7b11f45 https://medium.com/the-backlog-by-nimbo-x/dev-notes-fuzzy-se...

thejosh 8y ago

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.

ngrilly 8y ago

What is the size of your dataset (number of documents and average size of documents)?