Full-text search for Postgres

[thanhtoan1196]

Please support "Full-text search for MySQL and Postgres"

[scarroll32]

Looks like a pretty big feature, could you add a description of how it would work?

[yagudaev]

@seanfcarroll there is a great episode on it here: http://railscasts.com/episodes/343-full-text-search-in-postgresql. We can start with postgres only.

I've also been trying to get all of this to work and be more like Elastic Search/Algolia while keeping things simple for my team at work.

I'd love to contribute back to ransack and add some of these features.

This is the documentation for it in PG: https://www.postgresql.org/docs/current/textsearch.html -- it is pretty terse and hard to grep I find, but through.

There is also a fuzzy search feature part of this: https://www.postgresql.org/docs/13/fuzzystrmatch.html

[yagudaev]

Out of curiosity, I looked at mysql and it seems their full-text search is even simpler than pg. https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html.

Also wanted to note that Django actually has full-text with pg built right in https://docs.djangoproject.com/en/3.1/topics/db/search/

[scarroll32]

An interesting blog post on full text search with Postgres and Rails https://pganalyze.com/blog/full-text-search-ruby-rails-postgres

[yagudaev]

@seanfcarroll what do you think about starting off simpler, we can provide a typo tolerant predict like so:

*_fuzzy_matches - uses Levenstein distance to compare strings if the distance is smaller than 3 it matches

An example would be something like this:

# | last_name |
# -------------
#  smith

User.ransacker(last_name_fuzzy_matches: 'smth') # matches since the levensthin distance is 1
User.ransacker(last_name_fuzzy_matches: 'smithsons') # no match, distance is 4

Postgres Reference

This is very similar to LIKE in performance, in that it will require a table scan. It is possible to make it really fast by using an inverted index. But that is a bigger topic that I think we can avoid for the time being.

For MySQL, I wasn't able to find a native implementation of Levenshtein, but I found this: https://gist.github.com/Kovah/df90d336478a47d869b9683766cff718. Thus we could include it in a database migration to make this functionality work for MySQL too.

It is possible to configure how distance is calculated and tolerance, I'm not sure what would be a good configuration. There are also Soundex and Doublemetahpone in Postgres which convert a string into a hash representation and can be used for quicker matching.

[scarroll32]

@yagudaev I think the fuzzy match could be a great start, and to make it even smaller it could start with Postgres first and then add MySQL later.

A couple of interesting posts on the topic:

• https://www.rdegges.com/2013/easy-fuzzy-text-searching-with-postgresql/
• https://severalnines.com/database-blog/full-text-searches-mysql-good-bad-and-ugly

[scarroll32]

The https://github.com/textacular/textacular library might be a great way to handle full text search under Postgres. WDYT @gregmolnar ?

[scarroll32]

Splitting out the MySQL component of this into a new issue, as there will be a different solution.

#1219

[yagudaev]

Thanks @seanfcarroll I've started playing around with adding this as a new predicate to Ransack. I'll try to put a draft up at least in the next week or so to get us started.

Some of the search algorithms require persisting a reverse index to get reasonable performance. Postgres (and I'm sure other DBs) allow building this index on the fly, which means part of the query time is spent building an index. In practical terms, I don't know where it starts to go crazy 1K? 10K? 100K? 1M? I'll try it out and see.

Are there any performance benchmarks for ransack to keep in mind?

[gregmolnar]

The https://github.com/textacular/textacular library might be a great way to handle full text search under Postgres. WDYT @gregmolnar ?

I agree.

[yagudaev]

Maybe I'm missing something but I always thought that gem was no longer maintained. There is however pg_search: https://github.com/Casecommons/pg_search -- the name is confusing it should be pg_fts as in access to full-text search features in postgres.

Do you like the API better for textacular?

[gregmolnar]

Maybe I'm missing something but I always thought that gem was no longer maintained.

What makes you think that?

[yagudaev]

Quick Start
Rails 3, Rails 4
In the project's Gemfile add

Requirements
Ruby 1.9.2

Copyrights 2011

All these amounts to red flags that say "it hasn't been cared for in a long time". Compare this to the pg_search gem documentation which also started in 2011, but kept getting updated. It is also better laid out so it feels more "trustworthy" to me. I realize those are subjective, thus I may have misread the optics of it.