Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Full-text search engine with PostgreSQL (part 2): Postgres vs. Elasticsearch

xata.io

Post reply on HN
Print view View original

11–17 of 17 posts

Re: Full-text search engine with PostgreSQL (part 2): Postgres vs. Elasticsearch

#11

Post by antonhag »

arichard123 wrote: post #6

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.

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.

Re: Full-text search engine with PostgreSQL (part 2): Postgres vs. Elasticsearch

#12

Post by arcticfox »

gregmfoster wrote: post #3

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, a…

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.

Re: Full-text search engine with PostgreSQL (part 2): Postgres vs. Elasticsearch

#13

Post by withinboredom »

troupo wrote: post #10

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.

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.

Re: Full-text search engine with PostgreSQL (part 2): Postgres vs. Elasticsearch

#14

Post by giovannibonetti »

arcticfox wrote: post #12
gregmfoster wrote: post #3

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, a…

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.

It depends whether you're doing just FTS or also faceted search. In the latter case, you need a completely different kind of index (a covering index with all the filtered fields), and it'll be slow enough to be perceived by the user in the order of 1 million rows.

Re: Full-text search engine with PostgreSQL (part 2): Postgres vs. Elasticsearch

#15

Post by giovannibonetti »

troupo wrote: post #10

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.

To overcome the need for those arcane configurations, semantic search with vectors from text embeddings are slowly getting traction.

Here [1] is an article that shows how to do that with the modern Postgres extension pgvector [2].

The downside is the reliance on a ML model to generate the embeddings, either from OpenAI as mentioned in the article from Supabase, or from an open-source library.

[1] https://supabase.com/blog/openai-embeddings-postgres-vector [2] https://github.com/pgvector/pgvector

Re: Full-text search engine with PostgreSQL (part 2): Postgres vs. Elasticsearch

#16

Post by potamic »

PeterCorless wrote: post #7

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 performanc…

Interesting comparison. I wouldn't have compared them together because Pinot is for OLAP use cases and elastic for full text search.

Re: Full-text search engine with PostgreSQL (part 2): Postgres vs. Elasticsearch

#17

Post by PeterCorless »

potamic wrote: post #16
PeterCorless wrote: post #7

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 performanc…

Interesting comparison. I wouldn't have compared them together because Pinot is for OLAP use cases and elastic for full text search.

You are correct that it's apples and oranges. However, a lot of people use whatever technology is familiar and to-hand to solve problems that turns out to be a bad fit. I am literally in the process of writing up a case study of a customer that used Google Cloud BigTable for analytics until costs and complexity drove them to search for a replacement — for which StarTree was perfect.

I'm not calling out Elastic or Google Cloud BigTable per se. For their core features, and their design-for-purpose both are wonderful products. They just weren't designed for real-time OLAP.

The same with Postgres. Or MongoDB. It tends to happen that we take what we know and we throw it at whatever the problem-of-the-day happens to be.

Over time, we discover design-for-purpose solutions and move appropriate use cases to it.

Post reply on HN