Postgres Full-Text Search: A search engine in a database • Hacker News Board

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

#101

Post by zombodb » Wed, Jul 28, 2021, 1:07 AM UTC

mrslave wrote: post #95

zombodb wrote: post #85

Earlier quoted context omitted.

> Doing so would require ElasticSearch to reach consensus on every read/write ZomboDB only requires that ES have a view of its index that's consistent with the active Postgres transaction snapshot. ZDB handles this by ensuring that the ES index is fully refreshed after writes. This doesn't necessarily make ZDB great for high-update loads, but that's not ZDB's target usage. > They also claim that transactions will abo…

ZomboDB scratches an itch in a way I find fascinating, though I have yet to do more with it than shoehorn it into a prototype that was a bit square-peg-in-a-round-hole. It is in my catalogue of technologies I hope exploit someday. And I hope you're enjoying the work and making some $$$ too. While I'm here might I ask, are you finding the hosted PostgreSQL services (AWS, Azure, etc.) growing or shrinking your market o…

I don’t pay any attention to what the cloud providers are doing. I have no control over them so….. eh.

ZDB is still alive and well but I have a real job now too.

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

#102

Post by busymom0 » Wed, Jul 28, 2021, 1:47 AM UTC

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…

Regarding point 2, my implementation was to have a duplicate database server where all the search queries are sent. This would ensure that the search wouldn't slow down the main database. And most of the duplication would happen quick enough so that the search results were almost up to date.

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

#103

Post by busymom0 » Wed, Jul 28, 2021, 1:51 AM UTC

vincnetas wrote: post #48

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.

If you are just interested in searching HN and don't need full data dumps, the Algolia search for HN is quite good and fast:

https://hn.algolia.com

They also have a free API.

If you need data dumps, maybe look into Google BigQuery.

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

#104

Post by __jem » Wed, Jul 28, 2021, 1:58 AM UTC

nostrademons wrote: post #89

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…

Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…

I built a big feature with percolate as well and it really sold me on the possibilities of ES for product architecture.

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

#105

Post by justusw » Wed, Jul 28, 2021, 2:25 AM UTC

shakascchen wrote: post #86

No fun doing it for Chinese, especially for traditional Chinese. I had to install software but on Cloud SQL you can't. You have to do it on your instances.

Same story for performing searches in Japanese.

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

#106

Post by animeshjain » Wed, Jul 28, 2021, 3:11 AM UTC

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…

From what I know, full text search in Postgres (and MySQL) does not have faceted search. So it only supports returning full text results from the entire index.

Actually, it is possible, but doing a search on a particular segment of rows is a very slow operation - say text search for all employees with name matching 'x', in organization id 'y'.

It is not able to utilise the index on organization id in this case, and it results in a full scan.

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

#107

Post by kalev » Wed, Jul 28, 2021, 3:15 AM UTC

craigds wrote: post #84

jka wrote: post #72

Earlier quoted context omitted.

"Faceted search"[1] (aka aggregates in Elasticsearch) tends to be a popular one, to provide user-facing content navigation. That said, simonw has been on the case[2] demonstrating an implementation of that using Django and PostgreSQL. [1] - https://en.wikipedia.org/wiki/Faceted_search [2] - https://simonwillison.net/2017/Oct/5/django-postgresql-facet...

This is the key one for us that makes Postgres a non-starter for FTS (We use postgres for everything else ) We begrudgingly use Solr instead (we started before ES was really a thing and haven't found a need to switch yet) When you get more than about two different types of filters (e.g. types of filters could be 'tags', 'categories', 'geotags', 'media type', 'author' etc), the combinatorial explosion of Postgres quer…

This is exactly the issue I’m currently facing. We do a bunch of count queries to calculate facets and am looking for something that can do this out of the box. I’m glad I came to the same conclusion myself, either solr or elasticsearch might be the way to go. Starting this from scratch, which of the two would you recommend and why?

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

#108

Post by edwinyzh » Wed, Jul 28, 2021, 3:35 AM UTC

jcuenod wrote: post #43

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

A very well written article about SQLite FTS5! One question - it seems that your search result displays the matching paging number, how did you do that? because as far as I know, unlike FTS4, FTS5 has no `offsets` function.

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

#109

Post by manigandham » Wed, Jul 28, 2021, 4:52 AM UTC

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 can't go that far. Postgres is limited by dictionaries, stemming/processing, query semantics (like fuzzy searching), and the biggest issue of all being a lack of modern relevance algorithms. It's good for limited scenarios where you just need more than a SQL LIKE statement, and chaining some functions together can get you decent results [1] without adding another datastore.

However search tech is pretty mature with Lucene at the core and there are many better options [2] from in-process libraries to simple standalone servers to full distributed systems like Elastic. There are also other databases (relational like MemSQL, or documentstores like MongoDB/RavenDB) that are adding search as native querying functions with most of the abilities of ES. If search is a core or complex part of your application (like patterns in raw image data or similarities in audio waveforms) then that's where ES will excel.

1. https://stackoverflow.com/questions/46122175/fulltext-search...

2. https://gist.github.com/manigandham/58320ddb24fed654b57b4ba2...

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

#110

Post by IgorPartola » Wed, Jul 28, 2021, 5:01 AM UTC

nostrademons wrote: post #89

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…

Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…

So let’s say that you are building a search engine for performance car parts. There are going to be a bunch of technical terms you use there that are not necessarily going to stand out in the document itself but you know them to be important. For example, the amount of boost pressure a turbo can provide or the number of pistons in a brake caliper. Is there some structured way to specify the grammar which is used for such terms and treat those as important such that when a user puts in “19psi turbo” that they don’t get a bunch of results for just “turbo” which isn’t exactly what they want?