News for hackers. Stuff that matters.
Re:Postgres full text search is good enough by pilif on Monday September 29, 2014 @02:04AM (#8381837)
While it's certainly good enough for english bodies, its half-assed support for compounds is a problem for bodies in languages that use compounds (German for example).

There is sorta-support for compounds, but only really for ispell dictionaries and the ispell format isn't very good at dealing with compounds (you have to declare all permutations and manually flag them as compoundable) plus the world overall has moved over to hunspell, so even just getting an ispell dictionary is tricky.

As a reminder: This is about finding the "wurst" in "weisswürste" for example.

Furthermore, another problem is that the decision whether the output of a dictionary module should be chained into the next module specified or not is up to the C code of the module itself, not part of the FTS config.

This bit me for example when I wanted to have a thesaurus match for common misspellings and colloquial terms which I then further wanted to feed into the dictionary, again for compound matching.

Unfortunately, the thesaurus module is configured as a non-chainable one, so once there's a thesaurus match, that's what's ending up in the index. No chance to ever also looking it up in the dictionary.

Changing this requires changing the C code and subsequently deploying your custom module, all of which is certainly doable but also additional work you might not want to have to do.

And finally: If you use a dictionary, keep in mind that it's by default not shared between connections. That means that you have to pay the price of loading that dictionary whenever you use the text search engine the first time over a connection.

For smaller dictionaries, this isn't significant, but due to the compound handling in ispell, you'll have huge-ass(tm) dictionaries. Case in point is ours which is about 25 Megs in size and costs 0.5 seconds of load time on an 8 drive 15K RAID10 array.

In practice (i.e. whenever you want to respond to a search in less than 0.5 secs, which is, I would argue, always), this forces you to either use some kind of connection pooling (which can have other side-effects for your application), or you use the shared_ispell extension (http://pgxn.org/dist/shared_ispell), though I've seen crashes in production when using that which led me to go back to pgbouncer.

Aside of these limitations (neither of which will apply to you if you have an english body, because searching these works without a dictionary to begin with), yes, it works great.

(edited: Added a note about chaining dictionary modules)

Re:Postgres full text search is good enough by rachbelaid on Monday September 29, 2014 @05:59AM (#8382219)
All your points are very interresting and I will probably reuse them if I attend to write a more extended post.

Maybe the goal of this post was not clear `enough`. I'm presenting it more like a solution for small/medium needs for search without the needs to add extra dependencies in your architecture.

In my opinion this can help younger project/startup to raise their project from the ground by minimizing the complexity of their system (less maintenance)

But it's not a solution viable on the long run until the Postgres address loads of the problem on the search. I'm hoping that now that we have JSONB coming 9.4 that the search get some serious attention so PG can become a serious candidate as document store.

Re:Postgres full text search is good enough by pilif on Monday September 29, 2014 @06:56AM (#8382334)
>Maybe the goal of this post was not clear `enough`. I'm presenting it more like a solution for small/medium needs for search without the needs to add extra dependencies in your architecture.

it was very clear and I agree 100% with you. I just wanted to point out that like so often in life, it's not the be-all-end-all solution, but there are some caveats.

For what it's worth, we're using tsearch with quite a big (~600 GB total) body of bilingual german/french documents. Yes. We've run into the issues I outlined, but a) we could work around them (custom dictionary, thesaurus in-application before indexing, pgbouncer) and b) it's very nice to not having to maintain yet another piece of software (ElasticSearch or similar) on a completely different architecture (Java vs. PHP and Node.js).

Re:Postgres full text search is good enough by donw on Monday September 29, 2014 @02:12AM (#8381841)
I've got a project that needed multilingual support over very, very small documents, and ended up just rolling that myself -- neither ElasticSearch nor Postgres cope very well when you start mixing multiple languages in the same document.
Re:Postgres full text search is good enough by polyfractal on Monday September 29, 2014 @10:57AM (#8383131)
As other's have mentioned, it's entirely possible to have multiple fields with different analysis chains. Elastisearch handles multi-languages very well. You just have to start changing the defaults, since the defaults assume a single language for all fields (namely English).

E.g. "title_german" and "title_english" can each have their own analyzer specific to the language. Or you could have a single field "title" which then uses multifields to index a "title.english" and "title.german" field.

The key is that at search time you need to use a query that understands analysis. So you should use something like the `match` query, or `multi_match` for multiple fields. These queries will analyze the input text according to the analyzer configured on the underlying field (e.g. english or german)

There is a ton more information in The Definitive Guide book, in the chapter on languages: http://www.elasticsearch.org/guide/en/elasticsearch/guide/cu...

Topics include: language analysis chains, pitfalls of multiple languages per doc (at index vs search time), one language per field schemas, one language per doc schemas, multi-language per field schemas, etc

Re:Postgres full text search is good enough by jrochkind1 on Monday September 29, 2014 @08:25AM (#8382571)
> neither ElasticSearch nor Postgres cope very well when you start mixing multiple languages in the same document.

I've done okay with that with Solr -- but it's a hard problem. If you really want to meet the needs of fluent speakers of all languages. You can do kind of sort okay in Solr trying a multi-lingual unicode-aware analysis, but to do really good you probably need separate indexed fields (with different analysis) for each language, and may need code to try to guess which passages are in which language.

It's a hard problem, but I suspect Solr gives you more flexibility than ElasticSearch to try and work out a solution (in some cases having to write some Java). Although it doesn't come with an out-of-the-box best-practice solution (I don't think there are enough solr devs who have this use case day to day; solr dev is definitely driven by it's core devs business cases).

Re:Postgres full text search is good enough by radicalbyte on Monday September 29, 2014 @04:40AM (#8382091)
I had the same experience and the same conclusion.

Were I to do it again, I would simply split the document into multiple parts and include a "composite-document-id" in the metadata to combine them all.

Re:Postgres full text search is good enough by pilif on Monday September 29, 2014 @02:21AM (#8381869)
If it's indeed the same document, you're screwed. Yes. But in Postgres, I could at least have different column (or join tables with different language codes) for different languages and then query them with either dictionary.

ElasticSearch doesn't support that: All fields in a document are assumed to be of the same language which leads to a huge duplication of documents in cases where most fields of a document are language-independent.

Re:Postgres full text search is good enough by Argorak on Monday September 29, 2014 @03:01AM (#8381926)
> All fields in a document are assumed to be of the same language

That is not true. You can configure analyzers on a field basis, both for search and index.

Re:Postgres full text search is good enough by pilif on Monday September 29, 2014 @03:10AM (#8381940)
Oh. Cool. Thanks. When I was looking into this, it wasn't yet possible. That's very interesting for me.

ElasticSearch has MUCH better support for language analysis to the point where it might be worth it to move from Postgres tsearch to ElasticSearch, but this limitation was a huge hurdle for me not being able to move.

Re:Postgres full text search is good enough by Argorak on Monday September 29, 2014 @03:23AM (#8381959)
I am surprised, that's possible since any version I've been working with (0.12x and up).

What you cannot do is control that via client (e.g. "this index request uses the chinese analyzer for that field only"), because the analyzer setting in the index request overwrites all other configs. Mappings supported that for a long time.

One of the arts of search optimization on Elasticsearch is finding fields that you want to index multiple times with different analyzers and combine in a clever fashion.

Re:Postgres full text search is good enough by donw on Monday September 29, 2014 @02:23AM (#8381871)
That's basically what I ended up doing -- writing an analyzer that made a reasonable guess as to what language each word was, and filing it into the appropriate bucket, with a generic bucket for "everything else".

Ranking is another story, though.

Re:Postgres full text search is good enough by izacus on Monday September 29, 2014 @02:49AM (#8381915)
I don't know about ElasticSearch, but the more popular alternative Solr, can do that easily by using multiple cores (each configured and tweaked for each language) and doing sharding queries, which merge results for you.
Re:Postgres full text search is good enough by jrochkind1 on Monday September 29, 2014 @08:27AM (#8382575)
you don't need to use multiple cores for that, although you could -- but in general, anything you can do in multiple cores you can do identically in one core, the only reason to use multiple cores is for performance when you have so much corpus that one machine can't handle it.

But yeah, Solr gives you a lot of flexibility, perhaps more than ElasticSearch (which is both it's pro and it's con, for 'on the path' use cases I get the impression ElasticSearch requires less thinking in configuration than solr. although i have not any direct experience with ElasticSearch).

Re:Postgres full text search is good enough by sologoub on Monday September 29, 2014 @02:14AM (#8381852)
This maybe an incredibly naive question, but couldn't you work around the compounding issue with wild carding?
Re:Postgres full text search is good enough by pilif on Monday September 29, 2014 @02:19AM (#8381868)
Nope, because that wouldn't work with stemming and pluralizing.

Think finding "weisswürste" when people search for wurst. Unless you correctly build a tvector of (weiss, wurst) from "weisswürste", even with wildcarding hacks, you'd have to search for "würste" to find it.

Also: Not all words are compoundable. Doing a blind wildcard search would lead to false-positives which is somewhere between annoying and very annoying for the user.

Re:Postgres full text search is good enough by aidos on Monday September 29, 2014 @05:54AM (#8382208)
Midly OT: I don't really know German at all but I've wondered in the past how easy it is to split the compound words in the language. Is it something that can be easily automated? How many words can be in a single compound?
Re:Postgres full text search is good enough by ygra on Monday September 29, 2014 @06:30AM (#8382282)
As evidenced by the (somewhat humorous) Donaudampfschiffahrtsgesellschaftskapitänsmütze there isn't really a limit. Usually, if compounds get longer hyphens are used in select places to aid readability (they have sort of a semantic meaning as well, depending on the compound, so simply saying that you could write the above one as Donaudampfschiffahrts-Gesellschaftskapitänsmütze would be misleading (and in this case really mutilates the compound)).
Re:Postgres full text search is good enough by jeltz on Monday September 29, 2014 @07:18AM (#8382385)
In Swedish there is no theoretical limit, but more than 4 is uncommon. To split words you will need a dictionary and stemming/compounding rules for Swedish and process the word from left to right. Note also that in rare cases there can be multiple legal ways to split a given compound word.

As far as I can tell it is the same for German other than that they have a slightly different set of compounding rules, e.g. hyphen can be used in more cases in German.

Re:Postgres full text search is good enough by llllllllllll on Monday September 29, 2014 @11:06AM (#8383196)
It can be done quickly and accurately. Check out Peter Norvig's implementation of Viterbi algorithm for text segmentation.

Segmentation is typically used for scripts of languages like Thai and Khmer that don't feature word boundaries. I don't know the ins and outs of German word compounding, but it should work for breaking apart compounds too given a fully conjugated/declined word list as input.