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.
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).
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
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).
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.
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.
That is not true. You can configure analyzers on a field basis, both for search and index.
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.
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.
Ranking is another story, though.
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).
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.
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.
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.
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)