4 ms·
There's full text search, but that only does exact matches. To make it more versatile, take a look at trigrams, which forgives you if you don't spell something
by theandrewbailey 8y ago
There's full text search, but that only does exact matches. To make it more versatile, take a look at trigrams, which forgives you if you don't spell something exactly right. It breaks everything into three letter segments, and searches with those.
https://www.postgresql.org/docs/current/pgtrgm.html https://www.postgresql.org/docs/current/pgtrgm.html
- jerrysievert 8y agoor, the best of both worlds: like or ilike using a trigram index CREATE INDEX trigram_index ON words_table USING GIN (sentence GIN_TRGM_OPS)
- jordanthoms 8y agoYeah that index is amazing if you are doing a lot of sliding LIKE queries (i.e. % placeholders at start and end). Speeds them up massively without having to switch to a full text index or change your queries at all.
- why-el 8y agoI have used this and combined it with a small autocomplete engine built on top of Redis a while back. It worked amazingly well. Users type three words and we fetch the entire list of completions from Redis, then we pass this through to Postgresql. We even built synonyms into the Redis cache, so that users who look for "car" and we have a "vehicle" in the DB, it still works. For whatever reason if the Redis instance is not up (less than 0.1% chance), then whatever the query is goes to PG's full text search backed by the trigrams. Beautiful. Granted for this to work the DB needs to be somewhat stable in size. For us, it was a list of products that rarely changes, so the Redis cache can be built on server bootstrap.
- ksec 8y agoI remember I read DuckDuckGo are still using Postgre for their Backend fairly recently. Couldn't find any source on it though Google didn't gave any useful information.