4 ms·

I built a website around pg full text search and hit a lot of weird corner cases that ultimately will require me to move to something like elastic search. It's

by piyh 4y ago

I built a website around pg full text search and hit a lot of weird corner cases that ultimately will require me to move to something like elastic search. It's very powerful and useful, but if your main product is search, I wouldn't use it as a foundational pillar. They're fundamentally different products, but sometimes "pg full text search is good enough" isn't true.

adhoc_slime 4y ago
I'm coming up to a large segment of work where we'll adding search with elasticsearch and this was the assessment I came to after reviewing PG FTS. I would say, frankly, that if you already have a PG database and 1. you want better full text search than a non-existing solution. 2. you don't need the excellent searching like we've come to expect from search engines. then use the full text search feature your PG database already has. I wouldn't necessarily go out of my way to use a PG database for FTS but I think its a good solution when you're in the right position to use it and its not too shabby.
KaoruAoiShiho 4y ago
Agreed, it's okay for an MVP but not okay for a real app.
- piyh 4y ago
  The amount of time needed for pg fts integration is less than elastic, but if you don't replace it ~immediately~ after your MVP has been validated, you might as well have gone elastic from the beginning.
- sicp-enjoyer 4y ago
  Since most deployed applications search with: "for item in ... if item.containsSubstring(query)" I would say it's definitely useful for real apps.
  - piyh 4y ago
    It's a spectrum . On the left is the incredibly common use cases of inefficient substring search over small datasets that works more often than O(1) purists would like. To the right of that is where db fts rules, it's a comfortable but small niche. Once you start exiting that and start touching document search, being able to sort on relevance OR date with indexes, or any data ingestion killing search latency, you will have wished you went to the vast and uniform area to the right of db fts called elastic search. That is where I found myself. If search is not integral to your product, by all means avoid the complexity and curve that is elastic and be happy that you did.
JackC 4y ago
I also had this experience. I read blog posts like this one that said "just start with postgres," so tried that, and spent a bunch of time with GIN and RUM indexes trying to work around edge cases before going to Elasticsearch, where things worked out of the box because it's designed to be a search engine. I'm sorry I can't remember specifics anymore -- it was stuff like filtering and result counts. There just tended to be more surprises because everything was a custom addition instead of a feature enabled by default. In retrospect for an evolving product, adding Elasticsearch was more of a one time up front integration investment, while working around edge cases in Postgres felt like an ongoing limitation we'd be stuck with.
11235813213455 4y ago
You could have a bit of logic on top of PG to better handle the search query, for example using https://github.com/caub/pg-tsquery https://github.com/caub/pg-tsquery

by piyh 4y ago

adhoc_slime 4y ago

I'm coming up to a large segment of work where we'll adding search with elasticsearch and this was the assessment I came to after reviewing PG FTS. I would say, frankly, that if you already have a PG database and 1. you want better full text search than a non-existing solution. 2. you don't need the excellent searching like we've come to expect from search engines. then use the full text search feature your PG database already has. I wouldn't necessarily go out of my way to use a PG database for FTS but I think its a good solution when you're in the right position to use it and its not too shabby.

KaoruAoiShiho 4y ago

Agreed, it's okay for an MVP but not okay for a real app.

piyh 4y ago
The amount of time needed for pg fts integration is less than elastic, but if you don't replace it ~immediately~ after your MVP has been validated, you might as well have gone elastic from the beginning.
sicp-enjoyer 4y ago
Since most deployed applications search with: "for item in ... if item.containsSubstring(query)" I would say it's definitely useful for real apps.
- piyh 4y ago
  It's a spectrum . On the left is the incredibly common use cases of inefficient substring search over small datasets that works more often than O(1) purists would like. To the right of that is where db fts rules, it's a comfortable but small niche. Once you start exiting that and start touching document search, being able to sort on relevance OR date with indexes, or any data ingestion killing search latency, you will have wished you went to the vast and uniform area to the right of db fts called elastic search. That is where I found myself. If search is not integral to your product, by all means avoid the complexity and curve that is elastic and be happy that you did.

JackC 4y ago

I also had this experience. I read blog posts like this one that said "just start with postgres," so tried that, and spent a bunch of time with GIN and RUM indexes trying to work around edge cases before going to Elasticsearch, where things worked out of the box because it's designed to be a search engine. I'm sorry I can't remember specifics anymore -- it was stuff like filtering and result counts. There just tended to be more surprises because everything was a custom addition instead of a feature enabled by default. In retrospect for an evolving product, adding Elasticsearch was more of a one time up front integration investment, while working around edge cases in Postgres felt like an ongoing limitation we'd be stuck with.

11235813213455 4y ago

You could have a bit of logic on top of PG to better handle the search query, for example using https://github.com/caub/pg-tsquery https://github.com/caub/pg-tsquery