Postgres full text search is good enough
blog.lostpropertyhq.com
blog.lostpropertyhq.com
However, I still wouldn't recommend using it. The queries and indexes are all unintuitive, complex, not very readable and require learning a bunch of new syntax anyways (as you can see in the examples in the blog post). So just take the effort you would spend doing that and instead learn something with more features that will continue running no matter how large your scale.
While the blog mentions elasticsearch/solr (which are awesome and powerful), it doesn't mention sphinx (http://sphinxsearch.com/). If I were trying to make a simple search engine for my postgres/mysql powered site, I'd use sphinx. The indexer can be fed directly with a postgres/mysql query, and it includes simple APIs for all languages to interface with the searcher daemon. And of course it is super fast, has way more features and can scale larger than you'd ever need (sphinx powers craiglist's search).
If you are looking for caching and other more complex features, I'd recommend elasticsearch (and I do highly recommend it). But sphinx is simpler, and I think it is a good alternative for the type of functionality talked about in the blog post. Granted, I really don't think elasticsearch is all that complicated either, and it is also really well documented. But sphinx is just painfully simple for the basic use cases (like anything postgres can do).
As ever, it's a tradeoff. I used to keep everything required in for search results pages in the search index (solr, at the time). Eventually I decided that the additional db lookup was well worth the extra few milliseconds to make sure I was working with reliable data.
We started to notice that customers will use the menu and filtering options, rather than searching for an item. Need something from the "electronics" category, click the electronics menu tab, and pick the subcategory. From there customers browse or filter until they find what they're looking for.
Of cause so do use the search, and most people would have better results if they did search directly.
My point is: Maybe we could have just used Postgresqls full text search in this case. The queries will be rather simply anyway. The new syntax won't matter much, you need to learn something new anyway if you use Solr or ElasticSearch. Search is fare less important than people think, it just needs to be there and be "good enough".
Yes, Sphinx is awesome, if you're doing plain searches, filtering can get complicated pretty quickly.
But also: when navigating to a page, people are usually using the mouse. Switching between mouse and keyboard is somehow costly. While there are options (choosing from a combo box), users will use it and only switch to keyboard if there's no other way.
The interface provided by a "search as a service" company is going to be a lot more similar to elasticsearch/solr than it is to postgres, so it would be a much easier switch.
If you want to switch from PG full-text search to something else, you'll essentially have to start from square one because all of the ways you are interacting with your search mechanism (defining, inserting, updating, querying, retrieving documents) are completely different between PG and all the other search programs.
A blog post like this makes it all seem so easy, but I assure you maintaining these long queries, dealing with the extra load on your database, preventing XSS and just overall making administering your database a little more difficult also has a real cost.
The first goal is about using your current architecture to solve small/medium search needs and help your project to introduce search without requiring to add components to your current architecture. It's not a solution on the long run if your application revolve around search. I'm hoping that with the introduction of JSONB in 9.4, the idea of using Postgres as document store raise more attention on the search which can to be improved. But for an out-of-the-box feature, I think that the Postgres community did an amazing job. You can imagine that you get something working with Postgres in matter of hours if you are already using it (and you are comfortable with PG), but if you have never used SOLR/ElasticSearch it will take you much longer to introduce it in your project and get started (Ops, document sync, getting familiar with query, ...).
The second goal, is about introducing full-text search concepts. The post try to guide the user to build a search from nothing to a quite decent full text search in English / French (I cannot give feedbacks on other languages)
The third which is probably the less clear is that people are still using MySQL search sometimes which is IMHO an horrible search solution. I think this happen because some web framework like django provide easy access to match in the ORM. In this context, the post is aimed also to provide MySQL user some insights about what can be done with Postgres and being more aware about its features.
If you are interested by the topic then I suggest you to have a look to the amazings posts from Tim van der Linden who did an amazing job of going into more details about the subject.
http://shisaa.jp/postset/postgresql-full-text-search-part-1.... http://shisaa.jp/postset/postgresql-full-text-search-part-2.... http://shisaa.jp/postset/postgresql-full-text-search-part-3....
Postgres full-text search is not the silver bullet of search but matter of your needs, it's maybe good enough ;)
Although you have listed a few points where full text search is not enough, it might be a good followup to explain some cases where ElasticSearch / Solr / Sphinx etc. are needed.
Some probably use "text LIKE '%foo%'" SQL queries on any database.
MySQL supports several database engines, especially ISAM and InnoDB come with full text search support. You can also install Sphinx Search as MySQL DB engine. So your statement is not true in general. If you meant the old ISAM engine the newer InnoDB is default and usually the preferred solution.
I don't fully understand elasticsearch, because like you said it's insane. The query that I build to perform a simple search (think a google-like search box with a go button) is about 100 lines.
Should I try out postgres full text search for my purposes? I guess I could change to indexing page by page, and then I could group on the document hash to stamp out duplicates. That would also get me a list of page numbers where the results were. I'm not sure if I could get the snippets though. (What I call a snippet is the word surrounding the match - I'm returning that to datatables so that the user can search within their search to further refine the search.)
Here's what my search (and results) looks like now: http://imgur.com/Fjckva6 As you can tell, I'm not too familiar with elasticsearch (A shameful offset input!) and I'm more comfortable in a RDBMS.
I don't have experience with big data set but you may to read about GIST/GIN to pick the right index in your case (probably GIST) But from what I saw when I prepared this post, some people are getting some decent performance with full-text search on dataset like the size wikipedia.
https://wiki.postgresql.org/images/2/25/Full-text_search_in_...
I think that doesn't take long to try Postgres FTS if you are already using it but it may require more investment if you need to move from MySQL to PG.
I would definitely recommend it if you need a fast solution that is easy to implement (and doesn't really incur much tech debt) so you can get back to working on things that might matter more to your particular project.
But sure, it is another system to have to deal with.
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)
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.
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).
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.
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.
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).
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
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.
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).
For example, if you search for "Bob Peterson", Postgres will rank these two documents the same:
"I saw Bob."
"I saw Peterson."
In contrast, an IDF-aware search would notice that "Peterson" occurs in fewer documents than "Bob" and score "I saw Peterson" higher for that reason.
[1] http://en.wikipedia.org/wiki/Tf%E2%80%93idf
[2] http://stackoverflow.com/questions/18296444/does-postgresql-...
* Reduce GIN index size (Alexander Korotkov, Heikki Linnakangas)
* Improve speed of multi-key GIN lookups (Alexander Korotkov, Heikki Linnakangas)
Postgres full text search is very good, but once you get into the realms were Elasticsearch and SOLR really shine (complex scoring based on combinations of fields, temporal conditions or in multiple passes, all that with additional faceting etc.), trying to rebuild all that on top of Postgres will be a pain.
While that doesn't break the article, it runs into a nasty problem: `unaccent` doesn't handle denormalized accents.
# SELECT unaccent(U&'\0065\0301');
unaccent
----------
é
(1 row)
http://www.postgresql.org/message-id/53E1AB15.8050702@2ndqua...
So, probably making sure everything is in composed form before writing to the DB seems to be the best way to go.
SELECT to_tsvector(post.title) || to_tsvector(post.content) || to_tsvector(author.name) || to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) as document FROM post JOIN author ON author.id = post.author_id JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id JOIN tag ON tag.id = posts_tags.tag_id GROUP BY post.id, author.id;
be rewritten as:
SELECT to_tsvector(post.title) || to_tsvector(post.content) || to_tsvector(author.name) || to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) as document FROM post JOIN author ON author.id = post.author_id JOIN posts_tags ON posts_tags.post_id = post.id JOIN tag ON tag.id = posts_tags.tag_id GROUP BY post.id, author.id;
It allows you to do the things in this article with ActiveRecord models.
Neither GIN nor GiST indices support such document ordering. For a top-N query of the most recent results, the entire result set has to be processed. With common domain-specific words, this can be more than 50% of the entire document set. As you can imagine, this is insanely expensive. When you are in this situation, it helps to set gin_fuzzy_search_limit to a conservative number (20000 works for me, less if you expect heavy traffic) in postgresql.conf, so that pathological queries eventually finish without processing every result. Result quality will take a hit, because many documents are skipped over.
If you need any type of ordered search on more than a hundred thousand documents, do yourself a favor and use something else than Postgres.
I'm not sure what search back-end Wikipedia is using, but it seems like they are not quite immune to this problem either: https://en.wikipedia.org/w/index.php?search=a+the
The only times in which search slows down is when you search for common words that are not stop words and are also performing ordering (such as ranking) across that body of results before returning your window. That is to say, the search is still fast but sorting a large resultset can be slow.
The only gotchas I would call out to anyone thinking of using PostgreSQL full text search are:
1) Beware of row-level queries like ts_highlight() for extracting a fragment of the matched text, be sure that you are only doing this for the n lines that you are returning for a LIMIT and not the entire resultset (most of which you will discard).
2) Beware of accuracy and think about the text you are indexing. If you are receiving raw markdown and transforming to HTML, then you shouldn't index either (raw markdown may contain XSS attacks that can survive ts_highlight(), and the transformed HTML will contain markup that will be indexed). You should figure out a way to build a block of raw text that is safe to index and to show fragments for (ts_highlight()) which likely means running a striptags() over the HTML and also putting hyperlinks into the text (if you want those to be searchable) and indexing that.
We had no issues with performance, control over search ordering, or anything else. The only gotchas we encountered related to sorting and displaying results, and we resolved both easily enough, though too few of the high-level docs like the linked article pointed out the potential issues there.
What's the lightweight approach to create an inverted index nowadays with some basic stemming?
I'm sure there are packages that do the storage and retrieval for you, but it's the sort of lightweight thing you can do quickly and tweak from there.
For Go there's also Bleve (http://www.blevesearch.com/), but I haven't tried it yet.
It's the lightweight & fast alternative, similar to what Nginx is to Apache and IIS.
* SQLite FTS 4 (free, open source): http://www.sqlite.org/fts3.html
Lightweight & embedded solution (suitable for small to medium sized database and single process).
Features are limited and performance won't be the best, but sometimes ease of use and maintenance are all you need.
It's well-designed but fairly basic. Since it's a library, you'll have to write the server part yourself. (It has a server called Omega, but I've never used it.)
Indexing is slower than Lucene, though, but it's not too bad. The only significant problem I found with it the last time I was using it (which was 5+ years ago) was that writers occasionally would cause queries to abort, and you had to restart them, a cycle which could lead to long query times if you had lots of write churn.
My main complaint is that 'united states' @@ 'united states of america' is false, ie no token overlap similarity.
You can overcome this w the smlar extension. Have requested smlar integration in 9.5
I don't know if similarly nice things exist for other web frameworks.
As others have said, what's described in the OP may well be "good enough" for many purposes but it seems like quite a lot of effort to get there.
It's basically all that stuff minus the complexity of configuring it up for a rails app.
I was using it to search across user profiles, and while the fuzzy search was great for a slab of text in, say, an about me section, it wasn't good at all for short bits of text like name. Its just too fuzzy for short blobs.
Objectively, what are some data points when you need to switch over? E.g. more than 'x' values to index, index update time, or perhaps features like prefix/substring/phrase search, lack of facets, etc.
Those SQL queries are hideous. Most people have learned that, because of many developers like this, to avoid search because it's next to useless. Please do us a favor and leave it out of your product if you aren't willing to invest in a proper solution. Not one that is "good enough".