Full text search over Postgres: Elasticsearch vs. alternatives

AWS offers a solution which is OpenSearch serverless collections. A dedicated collection can be spun up for each user/tenant, e.g. «thing1-user345», «thing2-user123», instead of co-mingling the «user123» and «user345» in the same collection index. It increases the overall overhead but with the infrastructure repeatability, courtesy of IaaC, it is easy to roll out and process/consume «thing1-user123» and «thing2-user123» as discrete datasets.

Tangentially related. I have been finding multi-tenancy to have become more of a liability at worst or a nuisance at best due to the increasingly frequent customer demands to satisfy the data sovereignity, data confidentiality (each tenant wants their own, non-shared cryptography keys etc), data privacy, compliance, the right to forget/GDPR and similar requiremenets. For anything more complex than an anonymous online flower shop, it is simpler to partition the whole thing off – for each customer/tenant.

Doesn't that affect BM25 with a solution like Elasticsearch as well? Or is that smart enough to apply filters to the corpus statistics before calculating relevance?

You could solve that in SQLite by giving each user their own separate FTS table - not impossibly complex, but would grow increasingly messy if you have 10s of thousands of users.

If you're a Postgres pg_search user, partial indexes (https://docs.paradedb.com/search/full-text/index#partial-bm2...) can solve.

Take multi-tenancy.

Using a SQLite DB per tenant is a good alternative to handle that: https://turso.tech/

It could be solved with an index partitioning feature, no idea if it already exists somewhere...

The multi-tenancy problem actually already applies to almost every multitenant database in Postgres, Oracle and MySQL. Whether or not they use FTS. You just might not notice its impact in your case if query performance is "good enough".

War story time.

Awhile ago, I worked on a big SQL (Oracle) database, millions of rows per tenant, 10ks of tenants. Tenant data was highly dissimilar between most tenants in all sorts of ways, and the distributio n of data "shape" between tenants wasn't that spiky. Tenants were all over the place, and the common case wasn't that common. Tenants (multitenant tables and/or whole separate schemas) routinely got migrated between physical database hosts by hand (GoldenGate didn't exist at the beginning of this company, and was too expensive by the end).

A whole host of problems in this environment cropped up because, inside the DB, indexes and cache-like structures were shared between tenants. An index histogram for a DATETIME column of a multitenant table might indicate that 75% of the dates were in 2016. But it turns out that most of that 75% were the rows owned by a few big tenants, and the other 1000+ tenants on that database were overwhelmingly unlikely to have 2016 dates. As a result, query plans often sucked for queries that filtered on that date.

Breaking tables up by tenant didn't help: query plans, too, were cached by the DB. Issue was, they were cached by query text. So on a database with lots of separate (identical) logical schemas, a query plan would get built for some query when it first ran against a schema with one set of index histograms, and then another schema with totally different histograms would run the same query, pull the now-inappropriate plan out of the cache, and do something dumb. This was a crappy family of bug, in that it was happening all the time without major impact (a schema that was tiny overall is not going to cause customer/stability problems by running dumb query plans on tiny data), but cropped up unpredictably with much larger impact when customers loaded large amounts of data and/or rare queries happened to run from cache on a huge customer's schema.

The solve for the query plan issue? Prefix each query with the customer's ID in a comment, because the plan cacher was too dumb (or intended for this use case, who knows?) to strip comments. The SQL keys in the plan cache would end up looking like a zillion variants of "/* CUSTOMER-1A2BFC10 */ SELECT ....". I imagine this trick is commonplace, but folks at that gig felt clever for finding it out back in the bad old days.

All of which is to say: database multitenancy poses problems for far more than search. That's not an indictment of multitenancy in general, but does teach the valuable lesson that abandoning multitenancy, even as wacky and inefficient as that seems, should be considered as a first-class tool in the toolbox of solutions here. Database-on-demand solutions (e.g. things like neon.tech, or the ability to swiftly provision and detach an AWS Aurora replica to run queries, potentially removing everything but one tenant's data) are becoming increasingly popular, and those might reduce the pain of tenant-per-instance-ifying database layout.

As others mentioned you want to do combine both semantic and td-idf like searches. The thing is that searches that carry no semantic weight (e.g. a part number) or that have special meaning compared to the corpus used to train your embedding model (e.g. average Joe thinks about the building when seeing the word "bank", but if you work in a city planning firm you might only consider the bank of a river) fail spectacularly when using only semantic search.

Alternatively you can finetune your embedding model so that it was exposed to these words/meanings. However, the best (from personal experience) is doing both and use some sort of query rewriting on the full-text search to keep only the "keywords".

bm25 is often used along side vector search with a reciprocal rerank algorithm.

As others in this thread have mentioned, semantic/vector-based solutions tend to be much better when any of the following is true:

1. There are natural language questions being asked 2. There's ambiguity in any of the query terms but which there's more clarity if you understand the term in context of nearby terms (e.g. "I studied IT", "I saw the movie It", "What is it?") 3. There are multiple languages in either the query or the documents (many embedding models can embed to a very similar vector across languages) 4. Where you don't want to maintain giant synonym lists (e.g. user might search for or document might contain "movie" or "film" or "motion picture")

Whether you need any of those depends on your use case. If you are just doing e.g. part number search, you probably don't need any of this, and certainly not semantic/vector stuff.

But semantic/vector systems don't work well with terms that weren't trained in. e.g. "what color is part NY739DCP?" Fine tuning is bad at handling this (fine tuning is generally good for changing the format of the response, and generally not all that good for introducing or curtailing knowledge). Whether you need a keyword search system depends on whether your information are more of the "general knowledge" type or something more specific to your business.

Most companies have some need for both because they're building a search on "their" data/business, and you want to combine the results to make sure you're not duplicating. But I'll say I've seen a lot of companies get this sort of combination business wrong. Keeping 2 datastores completely in sync is wrought with failure, the models all have different limitations than the keyword system limitations which is good in some ways but can cause unexpected results in others, and the actual blending logic (be it RRF or whatever) can be difficult to implement "right."

I usually recommend folks look to use a single system for combining together these results as opposed to trying to invent yourself. Full disclosure: I work for Vectara (vectara.com) which has a complete pipeline that does combine both a neural/vector system and traditional keyword search, but I would recommend that folks look to combine these into a single system even if they didn't use our solution because it just takes so much operational complexity off the table.

It kind of is now if you count deep-learning based solutions to an information need haha

That’s probably because SotA in IR is proprietary implementations like Google Search, the most bread-and-butter trade secrets of their business. So they’re not publishing that (at least in any holistic manner, and even if they did, it’s not like one product could implement all its functionality including the complexities around the periphery). And what you’re left with is some pretty decent parity between industry and academic SotA, same as a lot of other areas of CS (databases, decentralized systems, etc). If anything, in most cases, implementation lags behind academic theory.

I've built a bunch of Python (and CLI) code to try and help make that work better: https://sqlite-utils.datasette.io/en/stable/python-api.html#...

    import sqlite_utils
    
    db = sqlite_utils.Database("news.db")
    db["articles"].enable_fts(["headline", "body"])
    results = list(db["articles"].search("softball"))

    sqlite-utils enable-fts news.db articles headline body
    sqlite-utils search news.db articles softball

Arango is sweet! We've actually talked to many people who switched from Postgres to Arango because of its better FTS. This was one of the reasons for creating ParadeDB in the first place. Many of the users who made this switch wished they could have stayed in Postgres without compromising on FTS.

RUM indices are a nice improvements, but they're not in Postgres core and likely won't ever be :( If you're looking for Elastic-level FTS in Postgres, your best bet is likely to overengineer pgvector+tsvector or simply to use ParadeDB pg_search

That exact scenario just happened to me a few days ago.

A problem here is that ES (and Solr too) are pathological with respect to garbage collection.

To make generational GC efficient, you want to have very short lived objects, or objects that live forever. Lots of moderately long-lived objects is the worst case scenario, as it causes permanent fragmentation of the old GC generation.

Lucene generally churns through a lot of strings while indexing, but it also builds a lot of caches that live on-heap for a few minutes. Because the minor GCs come fast and furious due to indexing, that means you have caches that last just long enough to get evicted into the old generation, only to become useless shortly thereafter.

The end result looks like a slow burning memory leak. I've seen the worst cases take down servers every hour or two, but this can accumulate over time on a slower fuse as well.

This seems to be a Lucene/SOLR problem. I used Lucene/SOLR years ago, and it died so often that we just auto-indexed nightly and had a re-index button in the UI.

I've been using and supporting ES (and OS lately) for well over a decade. Mostly its fine but a lot of users struggle with sizing their clusters properly (which is costly). Elasticsearch falling over is what happens when you don't do that. It scales fine until it doesn't and then you hit a brick wall and things get ugly.

Additionally, many companies learn the hard way that dynamic mapping is a bad idea because you might end up with hundreds of fields and a lot of memory overhead and garbage collection. I've fixed more than a few situations like this for clients that ended up with hundreds or thousands of fields, many shards and indices. Usually it's because they are just dumping a lot of data in there without thinking about how to optimize that for what they need.

A properly architected setup is not going to fall over randomly. But you need to know what you are doing and there are a lot of clients that I help that clearly don't have the in house expertise to do this properly and are a bit out of their depth.

Elastic’s serverless offering went GA recently: https://www.elastic.co/elasticsearch/serverless

What are you considering replacing it with for full-text search?

that one node is failing, why

We have a similar experience except we’re using AWS’ version, so any inquiry as to what happened ends with support saying “maybe if you upgrade your nodes this won’t happen again? idk”

Funny you mentioned because I also get that vibe from the Manticore website. We do judge books by their covers afterall.

I hate how slow the docs have become. Fwiw, we're working on fixing it :p

PostgreSQL is over 90GB, SOLR is under 1GB.

The PostgreSQL has to handle writes, reports, etc..., so I doubt it will cache as efficiently as full-text engine, you'll need to have full or partial replicas to distribute the load.

And, yes, I agree, almost all of this can be done with separate search table(s), but this table(s) will still live in a 'crowded house', so again replicas will be probably necessary at some point.

And using replicas brings new set of problems and costs ;-)

One client used MySQL for fulltext search, it was a single beefy RDS server, costing well over $1k per month and the costs kept raising. It was replaced with a single ~$100 EC2 machine running Meilisearch.

Yes, it always depends on application and purpose.

But once you will have to deal with a real FTS load, as you say, you have to use separate instances and replication, use materialized views etc.. and you find your self almost halfway to implementing ETL pipeline and because of replicas, with more complicated setup than having a FTS tool. And than somebody finds out what vector search is, and ask you if there is an PG extension for it (yes it is).

So IMHO with FTS in database, you'll probably have to deal with the almost same problems as with external FTS (materialized views, triggers, reindexing, replication, migrations) but without all its features, and with constrains of ACID database (locks, transactions, writes)...

Btw. I've SOLR right behind the OpenResty, so no hops. With database there would be one more hop and bunch of SQL queries, because it doesn't speaks HTTP (although I'm sure there is an PG extension for that ;-)

Solr is seen (probably unfairly) as passé by many people.

I used Solr back in 2008-2009 or so and it did a great job, but people didn't like that it used XML rather than JSON. Then we had a requirement for something like Elasticsearch's percolate query functionality, which Solr didn't support. So we switched, and subsequent projects I've joined have all used ES.

As I understand, Solr now has a JSON REST API and it's improved in other ways over the years, but ES has quite a bit more mindshare these days: https://books.google.com/ngrams/graph?content=solr%2Celastic...

So now in addition to the "does it do the job?" question, teams might also ask "can we hire and retain people to work with this technology rather than the more popular alternative?".

It seems to be a slightly better version of what postgresql offers. I have no experience or insights in what it does as it seems to be invite only currently.

But it joins a long list of not quite Elasticsearch alternatives with a much smaller/narrower/ more limited feature set. It might be good enough for some.

As for the criticism regarding Elasticsearch:

- it's only unstable if you do it wrong. Plenty of very large companies run it successfully. Doing it right indeed requires some skill; and that's a problem. I've a decade plus of experience with this. I've seen a lot of people doing making all sorts of mistakes with this. And it's of course a complicated product to work with.

- ETL is indeed needed. But if you do that properly, that's actually a good feature. Optimizing your data for search is not optional if you want to offer good search experience. You need to do data enrichment, denormalization, etc. IMHO it's a rookie mistake not to bother with architecting a proper ETL solution. I'd recommend doing this even if you use posgres or paradedb.

- Freshness of data. That's only a problem if you do your ETL wrong. I've seen this happen; it's actually a common problem with new Elasticsearch users not really understanding how to architect this properly and taking short cuts. If you do it right, you should have your index updated within seconds of database updates and the ability to easily rebuild your indices from scratch when things evolve.

- Expensive to run. It can be; it depends. We spend about 200/month on Elastic Cloud for a modestly sized setup with a few million documents. Self hosting is an option as well. Scaling to larger sizes is possible. You get what you pay for. And you can turn this around: what's a good search worth to you? Put a number on it in $ if you make money by users finding stuff on your site or bouncing if they don't. And with a lot of cloud based solutions you trade off cost against convenience. Bare metal is a lot cheaper and faster generally.

- Expensive engineers. Definitely a challenge and a good reason for using things like Algolia or similar search as a service products. But on the other hand if your company's revenue depends on your search quality you might want to invest in this.

ELK stack is unrelated, this is the analytical/OLAP use-case not the logging stack stuff. Although you can say that logging is a specific use-case of the analytical/OLAP.

Search for the discussion around "facets".

Basically the way to think of this is, elastic/solr are MapReduce with weak database semantics layered on top. The ingest stage is the "map", you ingest JSON documents and optionally perform some transformation on them. The query is the "reduce", and you are performing some analytical summation or search on top of this.

The point of having the "map" step is that on ingest, you can augment the document with various things like tokenizations or transforms, and then write those into the stored representation, like a generated column. This happens outside your relational DB and thus doesn't incur RDBMS storage space for the transformed (and duplicated!) data, or the generation expense at query time. Pushing that stuff inside your relational store is wasteful.

It's an OLAP store. You have your online/OLTP store, you extract your OLAP measurements as a JSON document and send it to the OLAP store. The OLAP store performs some additional augmentations, then searches on it.

What facets let you do is have "groups within groups". So you have company with multiple employees inside it, you could do "select * from sales where company = 'dell' group by product facet by employee". But it's actually a whole separate layer that runs on top of grouping (and actually behaves slightly differently!), and because of the way this is done (inverted indexes) this is actually incredibly efficient for many many groups.

It's built on a "full-scan every time" idiom rather than maintaining indexes etc... but it actually makes full-scans super performant, if you stay on the happy path. And because you're full-scanning every time, you can actually build the information an index would have contained as you go... hence "inverted index". You are collecting pointers to the rows that fit a particular filter, like an index, and aggregating on them as it's built.

And the really clever bit is that it can actually build little trees of these counts, representing the different slices through OLAP hyperspace you could take when navigating your object's dimensions. So you can know that if you filter by employee there are 8 different types of products, and if we filter by "EUV tool" then there is only 1 company in the category. Which is helpful for hinting that sidebar product navigation tree in stores etc.

A lot of times if you look carefully at the HTML/JS you can see evidence of this, it will often explicitly mention facets and sometimes expose Lucene syntax directly.

That's a lot of posts for a blog :)

Nice! I've seen other extensions that don't have transactional semantics, which runs counter to the norm for PG.

So since it was previously weakly consistent due to performance reasons, how does strong consistency affect transactional inserts/updates latency?

Correct. If you run your own Postgres, you can install pg_search directly, otherwise we suggest our users integrate with their existing Postgres deployments (say AWS RDS) via logical replication: https://docs.paradedb.com/replication/pg_search