Postgres Full-Text Search: A search engine in a database
blog.crunchydata.com
Postgres Full-Text Search: A search engine in a database
1–10 of 141 posts
Re: Postgres Full-Text Search: A search engine in a database
#2TBH I hadn't known you could do weighted ranking with Postgres search before.
Curious there's no mention of zombodb[0] though, which gives you the full power of elasticsearch from within postgres (with consistency no, less!). You have to be willing to tolerate slow writes, of course, so using postgres' built-in search functionality still makes sense for a lot of cases.
Re: Postgres Full-Text Search: A search engine in a database
#3Something 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 able to say, "keep in mind we'll need to budget a 3-mo transition to ES once we need X, Y, or Z".
Re: Postgres Full-Text Search: A search engine in a database
#4TBH I hadn't known you could do weighted ranking with Postgres search before. Curious there's no mention of zombodb[0] though, which gives you the full power of elasticsearch from within postgres (with consistency no, less!). You have to be willing to tolerate slow writes, of course, so using postgres' built-in search functionality still makes sense for a lot of cases. [0] https://github.com/zombodb/zombodb
Zombo is definitely super interesting and we should probably add a bit in the post about it. Part of the goal here was that you can do a LOT with Postgres, without adding one more system to maintain. Zombo is great if you have Elastic around, but want Postgres as the primary interface, but what if you don't want to maintain Elastic.
My ideal is always though to start with Postgres, and then see if it can solve my problem. I would never Postgres is the best at everything it can do, but for most things it is good enough without having another system to maintain and wear a pager for.
Re: Postgres Full-Text Search: A search engine in a database
#5Something 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 I recall correctly, Postgres search doesn't scale well. Not sure where it falls apart but it isn't optimized in the same way something like Solr is.
Re: Postgres Full-Text Search: A search engine in a database
#6I 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.
Re: Postgres Full-Text Search: A search engine in a database
#7Something 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…
Postgres was not good at (for us)
- IDF and other corpus based relevancy measures. had to hand roll
- thesaurus and missspelling - again possible of course with preprocessing and by adding config files
- non Latin alphabet languages. E.g. Arabic - needed filesystem access (we used aws rds so couldn’t do it) to add a dictionary based stemmed/word breaker
We used es or solr for those cases. For English FTS with 100k documents doing it in PG is super easy and one less dependency
Re: Postgres Full-Text Search: A search engine in a database
#8Postgres Full-Text search is a great way to get search running for a lot of standard web applications. I recently used just this in Elixir to set up a simple search by keyword. My only complaint was Ecto (Elixir's query builder library) doesn't have first class support for it and neither does Postgrex the lower level connector they use. Still, using fragments with sanitized SQL wasn't too messy at all.
Re: Postgres Full-Text Search: A search engine in a database
#9TBH I hadn't known you could do weighted ranking with Postgres search before. Curious there's no mention of zombodb[0] though, which gives you the full power of elasticsearch from within postgres (with consistency no, less!). You have to be willing to tolerate slow writes, of course, so using postgres' built-in search functionality still makes sense for a lot of cases. [0] https://github.com/zombodb/zombodb
Zombo is definitely super interesting and we should probably add a bit in the post about it. Part of the goal here was that you can do a LOT with Postgres, without adding one more system to maintain. Zombo is great if you have Elastic around, but want Postgres as the primary interface, but what if you don't want to maintain Elastic. My ideal is always though to start with Postgres, and then see if it can solve my pro…
Zombo does at least promise to handle "complex reindexing processes" for you (which IME can be very painful) but yeah, I assume you'd still have to deal with shard rebalancing, hardware issues, network failures or latency between postgres and elastic, etc etc.
The performance and cost implications of Zombo are more salient tradeoffs in my mind – if you want to index one of the main tables in your app, you'll have to wait for a network roundtrip and a multi-node write consensus on every update (~150ms or more[0]), you can't `CREATE INDEX CONCURRENTLY`, etc.
All that said, IMO the fact that Zombo exists makes it easier to pitch "hey lets just build search with postgres for now and if we ever need ES's features, we can easily port it to Zombo without rearchitecting our product".
Re: Postgres Full-Text Search: A search engine in a database
#10Something 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…
My experience has been that sorting by relevance ranking is quite expensive. I looked into this a bit and found https://github.com/postgrespro/rum (and some earlier slide decks about it) that explains why the GIN index type can't support searching and ranking itself (meaning you need to do heap scans for ranking). This is especially problematic if your users routinely do searches that match a lot of documents and you only want to show the top X results.
Edit: if any of the Crunchy Data people are reading this: support for RUM indexes would be super cool to have in your managed service.