6 ms·
Postgres as a Search Engine
- troupo 2y agoI would add: you should look for alternative solutions when you need to search anything other than English.
- indulona 2y agoEh, why? Have you ever heard of transliteration?
- troupo 2y agoBecause 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.
- deleted 2y ago[deleted]
- fpoling 2y agoFor example, it took Google years before they properly implemented search in Russian. That allowed Russian Yandex to win Russian market.
- Terr_ 2y agoBack 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.
- pezezin 2y agoAlternatively 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.
- kiwicopple 2y agopgroonga is a good Postgres option in these cases https://supabase.com/docs/guides/database/extensions/pgroonga https://supabase.com/docs/guides/database/extensions/pgroong...
- troupo 2y agoIt'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
- jillesvangurp 2y agoOr 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.
- 0x008 2y agoYou 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?
- jillesvangurp 2y agoThe 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.
- danielheath 2y agoI agree with you, but to be fair, that's true of virtually every technology I've encountered in my career.
- philippemnoel 2y agoThis 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
- 0x008 2y agoBut tsvector supports all sorts of different languages, at least western.
- philippemnoel 2y agoParadeDB pg_search supports tokenizers in all major languages
- troupo 2y agoGood find/tip! It took me a while to find it, it looks like they support it via Snowball: https://github.com/snowballstem/snowball https://github.com/snowballstem/snowball I wish ParadeDB exposed multi-language search capability more prominently in the docs.
- feverzsj 2y agoSQLite ver: 1. Full-text search with FTS5 2. Semantic search with sqlite-vec 3. Fuzzy matching with FTS5 trigram tokenizer 4. Bonus: FTS5 bm25() function
- bityard 2y agoFTS5 is amazing. Is there anything that's as small and easy to use as FTS5 for indexing text files or JSON documents on the file system?
- elesbao 2y agoI'd like to see that ! been fiddling with sqlite and fts5 to drop algolia from my application.
- otoolep 2y agoI wrote about this too[1], and since rqlite[2] puts a HTTP API in front of SQLite, you've got a SQLite-backed search engine available over the network. [1] https://www.philipotoole.com/building-a-highly-available-search-engine-using-sqlite/ https://www.philipotoole.com/building-a-highly-available-sea... [2] https://rqlite.io https://rqlite.io Disclaimer: I'm the creator of rqlite, and it's not the only piece of software to make SQLite available over the network.
- deleted 2y ago[deleted]
- ahaapple 2y ago1. Compared with column storage, the performance of vectorized search is relatively poor. 2. Postgre is not serverless, so it is not easy to separate read and write, and it is not easy to auto scaling
- smallerfish 2y ago> 2. Postgre is not serverless, so it is not easy to separate read and write, and it is not easy to auto scaling By the time you're hitting the limitations of vertical scaling a single do-everything instance of Postgres on cloud infrastructure, you're making boatloads of money and can afford to stand up something else for search. And besides which, creating read replicas horizontally is very doable. Though to be fair, I wouldn't implement moderately complex search on postgres, just because there are better tools for the job. Keeping data consistent between multiple systems though is "involved", and there's therefore a good argument for doing search in Postgres if your needs are simple.
- hamandcheese 2y agoYou don't have to be that big for mixed workloads to cause issues for a do-everything PG instance. Imagine a scenario where read-heavy but infrequent search queries end up pushing, say, your sessions table out of cache. Postgres has no facilities for earmarking cache for one table vs another, so the noisy neighbor problem is real, and hard to fix. You can throw money/ram at it, but that's needlessly expensive if you have some workloads that don't require that level of performance.
- ForHackernews 2y agoParent comment already mentioned read-replicas. The main problem I've seen is companies allowing tables to grow enormous because they never partition (by year, for instance) or archive out old stale data.
- LunaSea 2y agoYou can get 512GB RAM and 96 core machines for $1000 / month and at that point you can throw any workload on it.
- mirror_dude 2y agoI mean I guess, but why not just use a lucene based system?
- philippemnoel 2y agoKeeping it in Postgres avoids needing to denormalize the data, which is a big pain point. ParadeDB pg_search is based on Tantivy, a Lucene-inspired library, so in some ways it allows you to both benefit from a Lucene-based system and also keep it in Postgres
- cpursley 2y agoBecause that's another thing to set up, operate and pay for.
- moralestapia 2y agoGreat article but some benchmarks/profiling is missing. FTS and trigram can perform quite poorly unless the data and indices are tuned properly.
- lettergram 2y agoI wrote a post how to do full-text search back in 2018: https://austingwalters.com/fast-full-text-search-in-postgresql/ https://austingwalters.com/fast-full-text-search-in-postgres... Imo custom indexes are the real key to more accuracy and speed. That said, if you have <100m documents the built in search functions are great and really depends on your speed requirements.
- deleted 2y ago[deleted]
- krick 2y agoIt may be a silly question, but isn't there really a simple to use full-text search solution that has all complicated multi-language tricks baked in for all major languages? Or, well, at least European ones. It was a really, really hard task 20 years ago, but I'd imagine that now there must be a drop-in grep/ag replacement for natural languages that you run once to build an index and it takes care of all this stemming, semantic embeddings and all other clever specialized things for you. Isn't there one? And if no, what tools/libraries do exist in this area? To make something more sophisticated than in this post?
- philippemnoel 2y agoI would recommend you give ParadeDB pg_search a look. It's got support for tokenizers in all major languages. You can think of it as the next generation tsvector for more complex workloads. Disclaimer: I work at ParadeDB