Elastic license not on legal’s approved license list - need to get special approval from legal to use this.
Legal’s license list says AGPL needs case by case legal review - so need to get special approval from legal to use this.
Immediate thought: is there another solution which I don’t need special approval from legal to use?
Shameless plug:
https://github.com/jankovicsandras/plpgsql_bm25
BM25 search implemented in PL/pgSQL, might be useful if one can't use Rust extensions with Postgres, e. g. hosted Postgres without admin rights.
But, it appears that we are hitting our limits at this point. We have a table with tens of millions of rows and the use case is that it's faceted search.
Something like Zillow, with tends of millions of listings and tens of columns that can be queried. I'm having a tough time scaling it up. Does anyone have experience building something like that in pg?
The article mentions stop words, which is an outdated and primitive strategy to deal with often used words adding a lot of overhead to your search. These days with Elasticsearch the advice is actually to not rely on lists of stop words and avoid using them entirely.
Reason: the search engine is smart enough to handle very common words like, "to", "not, "be", and "or" efficiently. Bm25 relies on term frequencies for scoring. So these words would have very high term frequencies. The "match" query uses some optimizations that avoid most of the overhead associated with juggling a lot of high frequency term matches and scoring a lot of matches that would score very low. If you are only going to look at five results, scoring 500000 documents that potentially match the query that includes a stop word is really expensive (because you need to score each document and then sort the results). But if you prioritize scoring to low frequency terms first, you can narrow down the result list considerably and avoid scoring most of the documents.
Not filtering out stop words means that if you are indexing the works of Shakespeare, you'd actually be able to find the phrase "to be or not to be", which is entirely made up of stop words. Classic edge case to test for. Just an example of one of many optimizations that you'd find in Lucene based search systems that improve both precision and recall metrics as well as performance. There are a lot of wheels that need to be reinvented on the postgresql side to get to that level. It's not quite as simple as "apply bm25 for each hit and sort".
Solutions like discussed in the article provide you a some of the low level primitives but without most of the high level abstractions and optimizations that you'd need to build a proper search that is both accurate and fast. Fine if you know what you are doing and can compensate for that but probably not a great starting point if that's not the case.
And if you are thinking that it's convenient to index your database model, you'd be well advised to read up on ETL and the notion of optimizing what you index for search, rather than for simple storage. In most more sophisticated search systems what's indexed for search is not the same as what you put in your database and there are some good reasons for that.
Even if you do want to use postgres for search, you'd be well advised to consider setting up two completely separate database clusters. One for storing your data and one for indexing and searching through your data. The ETL pipeline in between those is where most of the magic happens. And you typically don't want that on the critical path of simple CRUD operations because that magic tends to be expensive. Which is why you do it in an ETL pipeline so you can keep your writes and queries fast and allocate hardware resources as is appropriate.
Basically the T in ETL (extract, transform, load) is where you do all the expensive stuff that you don't want to do when you are querying or interacting with your system and editing things. If you are Google 25 years ago, that's where you'd be calculating page rank, for example. These days they do quite a bit more work. Let's just say that BM25 doesn't quite cut it if you are Google these days. Vector search is a classic example of something that's fairly expensive as well that you don't want to block on while you are writing to a database or file system (e.g. when crawling the web). Doing things asynchronously and decoupling them via queues, databases, filesystems, etc. is basically what data engineering is all about.
Of course if you are setting up two systems anyway (a db, and a search index), you might want to consider using something that has all the right tools for the job. Which just isn't postgresql unless your ambition level is relatively low and search is a low priority thing for you. Whether you can afford to be naive and unambitious is ultimately a business choice. But you should be considering what your competitors do and what the consequences are of not being quite as good.
A lot of my consulting clients end up talking to me because they figure out they are not as good as they should be. I've helped transition several clients from naive postgresql based systems to Opensearch recently. Performance issues you can usually solve by throwing hardware at a problem. But quality issues require using better tools and knowing what to use.
Do you suggest use of lemmatization rather than stopwords? Vector embeddings instead? Keyword extraction to be included as a bm25 search field?
What are your thoughts on the various search engines (if I may call them that)? Eg elastic vs opensearch vs solr. Vectordbs - weaviate, pinecone, qdrant, milvus How about manticoresearch? It seems to be enormously powerful, flexible, mature, but not well known.
Opensearch is a fork of Elasticsearch and effectively the same thing for most things with exception of recently added features that a beginning user would typically not need. One of the exceptions is vector search which works slightly differently on both sides and mostly happened post fork.
There is no one size fits all solution for every search use case. Even realizing that there are multiple search use cases that you need to worry about and that they require different solutions is not something a lot of companies are even aware off. We can talk about text search, suggested queries, recommended results, did you mean, similarity search, etc. And that's before you throw in things like faceting/aggregations, various ways of putting your fingers on the scale (ever popular in ecommerce systems), premium/promoted content, etc.
Some wisdom with vector search is that it it is more effective when you combine it with traditional search. Doing vector search on lots of documents is just not cheap. So narrowing down results with a traditional search vastly cuts down on the amount of work you need to do. And it makes response times more reasonable too. Also, vector search with off the shelf models tends to perform quite poorly and models tend to have biases that might not match your needs/requirements. A good example from an ecommerce use case that somebody told me about is image search for clothes surfacing products that feature the same models rather than the same clothes. Because the off the shelf model was looking at the faces. It's not wrong. But probably not what was needed.
Lots of companies end up specializing models for their needs for this reason. And vector search is also not great for really short queries, like the type of queries that come out of a mobile UI. Users just don't tend to type coherently on mobile and there's only so much meaning you can get out of a 3 letter query. Vector search for search as you type is not a thing. And if you have a mobile UI that's most of the searches. Because why type long queries if the search gives you what you are looking for after just a few letters?
Most vector search products give you enough rope to hang yourself but don't fully solve all issues for you. Model selection and tuning tends to be all/most of the work and is typically left as an exercise to the user and is something that requires some skill. Once you have a model, there are plenty of things you can use. Each with their pros/cons. Setting up inference is the easy part of the job.
So, I wouldn't start with vector search until after you've done the legwork of being able to tell good results from bad results. Especially not if you are new to doing anything with search.
What is appropriate all depends on the use cases, the business value of search to your company, and the skills of the team that you have and what you are willing to spend on getting something better. But picking something because its "faster" because some silly benchmark says so is probably not a good reason. Is it faster because it skips essential work? Or because it does the same thing better? And is that the thing that you need? Or are there other things that you might need?
This would be rad
"""Another solution is ParadeDB, which pushes full-text search queries down to Tantivy for results. It supports BM25 scoring and complex query patterns like negative terms, aiming to be a complete replacement for ElasticSearch. However, it uses its own unique syntax for filtering and querying and delegates filtering operations to Tantivy instead of relying on Postgres directly. Its implementation requires several hooks into Postgres' query planning and storage, potentially leading to compatibility issues."""
So it is more apples to red than equal comparison.
This comparison isn't super fair -- ParadeDB does not have compatibility issues with Postgres and rather is directly integrated into Postgres block storage, query planner, and query executor
