Or when you care at all about optimizing your search ranking. Postgresql has a few alright tools to do vector search and to do some simple things with trigrams. And that can work well for some narrow use cases. If that's all you need, great. But this doesn't give you a whole lot of control over search ranking. And if search is in anyway on the critical path to revenue for whatever it is your product does, you probably need to invest in making sure your users find what they need. And that means getting a bit systematic about things, using specialized tools, and making sure you have some skills on your team that know how to do this.

You can use custom ranking functions (shown in the article) add weights to columns, etc.

What techniques are you talking about that cannot be implemented in Postgres?

The hundreds of features things like Solr and Elasticsearch/Opensearch have. These are complex products but mostly this is a form of necessary complexity.

To be blunt, the intersection of people that know what they are doing on this front and people that choose to use Postgres for this is pretty narrow. It does happen and I've seen a few nice things built with Postgres. But mostly it's just people using the wrong tool for the job.

This is partially true. You're right that the feature set is necessary, but it's now possible to do this in Postgres.

ParadeDB pg_search bundles Tantivy, a Lucene-inspired library inside Postgres, to give users the feature set (BM25 ranking, tokenizers, faceted search, etc.) while keeping the benefits of Postgres (keep your data normalized, avoid ETL, etc.)

Disclaimer: I work on ParadeDB

I’ve been using pg_search for a tranche of emails in 2+ GB PDFs converted to PDF-page-per-row. Along with some materialized views for regex extracted timestamps it has given us a means for data forensics that has been very fruitful.

Orchestrating this outside of a few Python scripts and a single instance of postgres would have taken much more work!

So happy to hear you like it. We have a lot more coming up for it. Stay tuned!

I agree with you, but to be fair, that's true of virtually every technology I've encountered in my career.

I used to have this same argument but apart from the few that I've used on solr, it is not trivial to have general search using it. Won't even comment on ES b/c they already target analytics better than search. I think it is worth exploring pg and other tools as all search cases are narrow/specific (ecomm, graphs, domain-specific documents etc), specially if you need facets and filtering. Also multilanguage ok to consider for a tool but products usually look for better recall at their original lang then to have same results in other languages.

pgroonga is a good Postgres option in these cases

https://supabase.com/docs/guides/database/extensions/pgroong...

It's a bit weird that the pages claim to support all languages, and all examples are in English. Even the documentation for Groonga, on which it's based doesn't talk about multiple languages.

It looks like at best it makes Postgres aware of more characters

I saw that they bundle MeCab and Japanese dictionaries on Windows, but that's about it for multi-language support.

Eh, why? Have you ever heard of transliteration?

Because search in different languages needs to be aware of that language's peculiarities.

Including but not limited to things that English doesn't have or only has in vestigial forms like grammatical cases, complex word morphology, declensions etc.

For example, it took Google years before they properly implemented search in Russian. That allowed Russian Yandex to win Russian market.

Back atcha: Have you ever heard of stemming?

That'll be different depending on the language, you can't simply transliterate words to another language, the mapping may change things and the rules might be different.

For example, in English "manage / managers", are practically the same, but transliterating to Spanish can give you "gestionan / gerentes", which diverge very early on.

Alternatively you could use "gestor", which has the same stem. "Gestionar" and "gerente" have different origins, so no wonder they look very different.

But yes, most (all?) European languages have vastly more complex morphology than English.

But tsvector supports all sorts of different languages, at least western.

ParadeDB pg_search supports tokenizers in all major languages

Good find/tip!

It took me a while to find it, it looks like they support it via Snowball: https://github.com/snowballstem/snowball

I wish ParadeDB exposed multi-language search capability more prominently in the docs.