Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres Full-Text Search: A search engine in a database

blog.crunchydata.com

Post reply on HN
Print view View original

41–50 of 141 posts

Re: Postgres Full-Text Search: A search engine in a database

#41

Post by kyrra »

theandrewbailey wrote: post #20

> You could also look into enabling extensions such as unaccent (remove diacritic signs from lexemes) or pg_trgm (for fuzzy search). Trigrams (pg_trgm) are practically needed for usable search when it comes to misspellings and compound words (e.g. a search for "down loads" won't return "downloads"). I also recommend using websearch_to_tsquery instead of using the cryptic syntax of to_tsquery.

Trigrams are amazing. I was doing a sideproject where I wanted to allow for substring searching, and trigrams seemed to be the only way to do it (easily/well) in postgres. Gitlab did a great writeup on this a few years ago that really helped me understand it:

https://about.gitlab.com/blog/2016/03/18/fast-search-using-p...

You can also always read the official docs:

https://www.postgresql.org/docs/current/pgtrgm.html

Re: Postgres Full-Text Search: A search engine in a database

#42

Post by nextaccountic »

rattray wrote: post #3

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…

If your search needs outgrow Postgres' native search engine, you can use Postgres search with an ElasticSearch backend, using Zombo

https://github.com/zombodb/zombodb

It basically gives you a new kind of index (create index .. using zombodb(..) ..)

Re: Postgres Full-Text Search: A search engine in a database

#43

Post by jcuenod »

Huh, just yesterday I blogged[0] about using FTS in SQLite[1] to search my PDF database. SQLite's full-text search is really excellent. The thing that tripped me up for a while was `GROUP BY` with the `snippet`/`highlight` function but that's the point of the blog post.

[0] https://jcuenod.github.io/bibletech/2021/07/26/full-text-sea...

[1] https://www.sqlite.org/fts5.html

Re: Postgres Full-Text Search: A search engine in a database

#44

Post by SigmundA »

sandGorgon wrote: post #30
rattray wrote: post #3

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…

it doesnt do TF-IDF or BM-25 - the current state of art in search relevance algorithms. that's where it cant be used for anything serious.

That why this really needs to get merged:

https://github.com/postgrespro/rum

Re: Postgres Full-Text Search: A search engine in a database

#45

Post by habibur »

oauea wrote: post #15
MushyRoom wrote: post #11

I was hyped when I found out about it a while ago. Then I wasn't anymore. When you have 12 locales (kr/ru/cn/jp/..) it's not that fun anymore. Especially on a one man project :)

Why support so many locales in a one man project?

To attract more visitors/customers I guess. I plan to extend to 10 languages too. 1 man project.

Re: Postgres Full-Text Search: A search engine in a database

#47

Post by MushyRoom »

oauea wrote: post #15
MushyRoom wrote: post #11

I was hyped when I found out about it a while ago. Then I wasn't anymore. When you have 12 locales (kr/ru/cn/jp/..) it's not that fun anymore. Especially on a one man project :)

Why support so many locales in a one man project?

It's just a project to learn all the things that are web. It's mainly a database for a game now with most of the information sourced from the game (including its localization files).

I'm slowly transitioning from MariaDB to Postgres - again as a learning experience. There is cool stuff and there is annoying stuff to reproduce things like case-insensitive + ignore accents (utf8_general_ci) in Postgres.

I've looked into FTS and searching for missing dictionaries to support all the locales but Chinese is one of the harder ones.

Re: Postgres Full-Text Search: A search engine in a database

#48

Post by vincnetas »

lettergram wrote: post #6

I actually built a search engine back in 2018 using postgresql https://austingwalters.com/fast-full-text-search-in-postgres... Worked quite well and still use it daily. Basically doing weighted searches on vectors is slower than my approach, but definitely good enough. Currently, I can search around 50m HN & Reddit comments in 200ms on the postgresql running on my machine.

Offtopic, but currious what are your use cases when searching all HN and reddit comments? Im at the beggining of this path, just crawled HN, but what to do with this, still a bit cloudy.

Re: Postgres Full-Text Search: A search engine in a database

#49

Post by kurko »

fizx wrote: post #18
rattray wrote: post #3

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…

Hi, I started an Elasticsearch hosting company, since sold, and have built products on PG's search and SQLite FTS search. There are in my mind two reasons to not use PG's search. 1. Elasticsearch allows you to build sophisticated linguistic and feature scoring pipelines to optimize your search quality. This is not a typical use case in PG. 2. Your primary database is usually your scaling bottleneck even without addin…

I agree with reason 1, but reason 2 is an answer for, "should I use PG search in the same PG instance I already have", and that's a different discussion. You can set up a replica for that.

Re: Postgres Full-Text Search: A search engine in a database

#50

Post by blacktriangle »

oauea wrote: post #15
MushyRoom wrote: post #11

I was hyped when I found out about it a while ago. Then I wasn't anymore. When you have 12 locales (kr/ru/cn/jp/..) it's not that fun anymore. Especially on a one man project :)

Why support so many locales in a one man project?

One (dev) project here, we're up to 5 locales at a surprisingly small number of customers. Problem is when your customers are global, all of a sudden a single customer can bring along multiple locales. I very much regret not taking localization far more seriously early in development but we were blindsided by the interest outside the Angleosphere.
Post reply on HN