5 ms·

Similarity in Postgres and Rails Using Trigrams

oliverx0 7y ago
Out of curiosity, how does this compare to using tsvector? https://coderwall.com/p/vngr0a/simple-full-text-search-using-postgres-on-rails https://coderwall.com/p/vngr0a/simple-full-text-search-using... EDIT: Found my answer https://stackoverflow.com/questions/15884309/postgresql-full-text-search-and-trigram-confusion https://stackoverflow.com/questions/15884309/postgresql-full...
stanislavb 7y ago
Nice one. Postgres trigrams are definitely a powerful feature. I've written on this topic as well https://dev.to/saashub/postgres-trigram-indexes-vs-algolia-1oma https://dev.to/saashub/postgres-trigram-indexes-vs-algolia-1...
nooyurrsdey 7y ago
Nicely written and easy to follow. Also more proof that you don't need a specialized library / dependency for every thing - sometimes you can get most of the way there with a few lines of your own code.
pmontra 7y ago
Oh wow, thanks to this post I learned that SQL has "order by <number>", the index of the column in the select list. That after 30 years of SQL...
- iaabtpbtpnn 7y ago
  It also works for group by.
nieve 7y ago
I'm somewhat confused why the author didn't use bind variables since as far as I know the standard PostgreSQL adapters fully support them. Even if you trust that quote_string() will never have a vulnerability it's safer to not use string interpolation at all and it doesn't cost you anything.
- lfittl 7y ago
  (not the author, but I've discussed this with him previously) This is a problem with `order` in Active Record in particular, where you can't pass in a bind variable the same way you'd expect it to work with `where`. Therefore the slightly less elegant version that goes through quote_string. You could use bind variables by going directly to the pg driver, but I think there is no quick shorthand to do this in Active Record/Arel.
bgorman 7y ago
This is a really powerful feature. Combined with metaphone/soundex you can have a nice lightweight fuzzy+phonetic search using vanilla postgres.
ilitirit 7y ago
I used this to replace an aging ElasticSearch implementation our company was using. Simple and fast, and 1 less piece of tech in the stack to maintain. Postgres really is a brilliant piece of software. Yeah I know paid solutions which arguably work better exist, but for a "free" product Postgres does astoundingly well in many areas.

Out of curiosity, how does this compare to using tsvector? https://coderwall.com/p/vngr0a/simple-full-text-search-using-postgres-on-rails https://coderwall.com/p/vngr0a/simple-full-text-search-using... EDIT: Found my answer https://stackoverflow.com/questions/15884309/postgresql-full-text-search-and-trigram-confusion https://stackoverflow.com/questions/15884309/postgresql-full...

stanislavb 7y ago

Nice one. Postgres trigrams are definitely a powerful feature. I've written on this topic as well https://dev.to/saashub/postgres-trigram-indexes-vs-algolia-1oma https://dev.to/saashub/postgres-trigram-indexes-vs-algolia-1...

nooyurrsdey 7y ago

Nicely written and easy to follow. Also more proof that you don't need a specialized library / dependency for every thing - sometimes you can get most of the way there with a few lines of your own code.

pmontra 7y ago

Oh wow, thanks to this post I learned that SQL has "order by <number>", the index of the column in the select list. That after 30 years of SQL...

iaabtpbtpnn 7y ago
It also works for group by.

nieve 7y ago

I'm somewhat confused why the author didn't use bind variables since as far as I know the standard PostgreSQL adapters fully support them. Even if you trust that quote_string() will never have a vulnerability it's safer to not use string interpolation at all and it doesn't cost you anything.

lfittl 7y ago
(not the author, but I've discussed this with him previously) This is a problem with `order` in Active Record in particular, where you can't pass in a bind variable the same way you'd expect it to work with `where`. Therefore the slightly less elegant version that goes through quote_string. You could use bind variables by going directly to the pg driver, but I think there is no quick shorthand to do this in Active Record/Arel.

bgorman 7y ago

This is a really powerful feature. Combined with metaphone/soundex you can have a nice lightweight fuzzy+phonetic search using vanilla postgres.

ilitirit 7y ago

I used this to replace an aging ElasticSearch implementation our company was using. Simple and fast, and 1 less piece of tech in the stack to maintain. Postgres really is a brilliant piece of software. Yeah I know paid solutions which arguably work better exist, but for a "free" product Postgres does astoundingly well in many areas.