[HN Gopher] Full-text search engine with PostgreSQL (part 2): Po...
___________________________________________________________________
Full-text search engine with PostgreSQL (part 2): Postgres vs.
Elasticsearch
Author : rw-access
Score : 87 points
Date : 2023-07-19 18:56 UTC (4 hours ago)
(HTM) web link (xata.io)
(TXT) w3m dump (xata.io)
| PeterCorless wrote:
| It's interesting to me, because we [I work at StarTree, based on
| Apache Pinot] come at the issue from the entirely opposite end of
| the scales -- when Elasticsearch doesn't scale to certain very
| large data sets, and especially where you are looking for
| aggregations and low latency query results.
|
| So there are some workloads that are small enough you can use a
| different an alternate to Elasticsearch (and where performance is
| not paramount), and other workloads that are sort of too big for
| Elasticsearch (and where performance _is_ paramount).
| troupo wrote:
| FTS in all databases suck for non-English languages. Even the
| larger dedicated search systems often fail at even the simplest
| searches or need arcane configurations to work properly.
| withinboredom wrote:
| They mostly suck because non-english speakers don't contribute
| to the "magic" like stems and stop-words. It isn't the
| databases fault it doesn't have good default data.
| forinti wrote:
| It's not Postgresql vs. Elastic, it's Postgresql + Elastic:
|
| https://github.com/matthewfranglen/postgres-elasticsearch-fd...
| tudorg wrote:
| [author] Part 1 was on HN last week here:
| https://news.ycombinator.com/item?id=36699016
|
| Happy to answer any questions. As with any DB comparison, it's
| tricky and I might have errors or I might have missed obvious
| things.
| gregmfoster wrote:
| Thanks for writing this up! I really enjoyed the comparison. My
| team is considering moving some of our data to elastisearch out
| of postgres, and this analysis helps confirm some of our
| thoughts.
| gregmfoster wrote:
| To me, the critical points here are:
|
| > PostgreSQL has a single master and multiple read replicas,
| Elasticsearch has horizontal scalability via sharding.
|
| > if you have a large data set search and search relevancy is
| critical to your application (for example, in e-commerce), using
| a dedicated search engine like Elasticsearch is going to perform
| better
|
| I love the mentality of "default to using Postgres for
| everything, and specialize when you need to." In this case, it
| looks like you'll need to evolve text search out of Postgres the
| moment your dataset gets into the millions, which roughly matches
| my experience.
| mrelectric wrote:
| We have billions of records and fts still works on pg.
| tudorg wrote:
| That sounds interesting, are you using `ts_rank` or otherwise
| sorting by relevancy?
| arcticfox wrote:
| Millions is way too low; we do extremely heavy FTS on Postgres
| in the millions magnitude and it is extremely effective. We
| also use a relatively tiny hosted box with tons of room for
| vertical scaling when we need to.
|
| So I'm not sure at what point Postgres will start to be limited
| on this front but it's definitely a different order of
| magnitude, unless it's a wildly different workload than my team
| has.
| arichard123 wrote:
| I think the article needs to talk more about how you can combine
| a text search with a non text search criteria. I don't know about
| elasticsearch, but I guess that limiting it's search results to
| other bits of data, permissions, dates, relations, is harder than
| in postgresql.
| antonhag wrote:
| Elasticsearch is decent at using non-text criteria provided
| that they are:
|
| 1. In the same document (a document in ES is a JSON object) 2.
| You have indices for them. ES (and Lucene) supports indices on
| raw text values and numbers as well.
|
| ES does not do well with relations (joins). You can de-
| normalize data to deal with that.. but that makes data
| consistency harder.
___________________________________________________________________
(page generated 2023-07-19 23:02 UTC)