3 ms·
"relevancy" is often scoped to the _textual_ relevancy of matching documents, where things like boosting matches in the title vs. the body and tf/idf scores of
by brasetvik 5y ago
"relevancy" is often scoped to the _textual_ relevancy of matching documents, where things like boosting matches in the title vs. the body and tf/idf scores of the involved terms matter a lot.
In practice, a lot of relevancy tuning is about properly factoring in other "signals", such as something's recency, distance, popularity, stock availability, the section of the website being searched from, etc.
Thus, there's often a lot more than just the textual relevancy being involved.
Postgres will never compromise on MVCC correctness, and therefore doesn't do any caching of partial results beyond having things in shared buffers and/or the page cache.
Elasticsearch will err on prioritising speed and cacheability. The "signals" that appear again and again in the same queries will likely be pretty compactly cached as (roaring) bitmaps in a filter cache. That enables potentially being a _lot_ faster, because it's ok with "you're seeing approximately everything that was ingested about a second a go".
In addition to that, Elasticsearch(/Lucene) comes with a lot more tooling for text processing, stemming, compound word splitting, etc.
- philliphaydon 5y agoSo does it kinda depend on what sort of searching you want to do? I'm currently trying to decide between ES and PostgreSQL. As I have read replica's for PG and the plan is to flatten the data into a single table, there's only ~4 fields that are text betwee 1-256 in length. While the other ~30 fields are int/decimal/date fields. There's prob ~8m records roughly that grows by 2-3m / year. So I feel like ES might be overkill for this and PG would be totally fine.
- mistrial9 5y agoone lense for the decision -- look at the rate of change of your dataset, and how important is total throughput.. if your dataset does not change very much, or changes at defined times in predictable ways, then I would say Postgres is a fine choice and your biggest problem will be defending against uninformed colleagues. Many small parts of Postgres are well thought out, and the manual has improved steadily over two decades.
- philliphaydon 5y agoNew data has a few fields which could change a lot within the first few hours but after that the data is more or less set and never changes. I love the PostgreSQL documentation!
- whitepoplar 5y agoYou may want to check out the ZomboDB Postgres extension!