Hacker News new | ask | show | jobs
Similarity in Postgres and Rails Using Trigrams (pganalyze.com)
66 points by moritzplassnig 2401 days ago
7 comments

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.
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.
(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.

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-1...
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...
It also works for group by.
This is a really powerful feature. Combined with metaphone/soundex you can have a nice lightweight fuzzy+phonetic search using vanilla postgres.
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.