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

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

xata.io

110 pointsrw-access16 comments

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.

We have billions of records and fts still works on pg.

That sounds interesting, are you using `ts_rank` or otherwise sorting by relevancy?

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.

[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.

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.

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.

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.

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

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).

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.

It's not Postgresql vs. Elastic, it's Postgresql + Elastic:

https://github.com/matthewfranglen/postgres-elasticsearch-fd...

Reply on news.ycombinator.com