81–90 of 141 posts
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 :)
Earlier quoted context omitted.
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.
I built a search engine that quantified the expertise of authors of comments. Then I created what I called “expert rank” that allowed me to build a really good search engine. Super good if you’re at a company or something https://twitter.com/austingwalters/status/104189476543920128...
Zulip's search is powered by this built-in Postgres full-text search feature, and it's been a fantastic experience. There's a few things I love about it: * One can cheaply compose full-text search with other search operators by just doing normal joins on database indexes, which means we can cheaply and performantly support tons of useful operators ( https://zulip.com/help/search-for-messages ). * We don't have to bui…
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…
"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...
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 queries required to provide facet counts gets unmanageable.
For example, when I do a query filtered by `?tag=abc&category=category1`, I need to do these queries:
- `... where tag = 'abc' and category_id = 1` (the current results)
- `count(*) ... where tag = 'def' and category_id = 1` (for each other tag present in the results)
- `count(*) ... where tag = 'abc' and category_id = 2` (for each other category present in the results)
- `count(*) ... where category_id = 1`
- `count(*) ... where tag = 'abc'`
- `count(*)`
Whereas solr/elasticsearch have faceting built in and handle this with ease.
Earlier quoted context omitted.
Do you see any particular reasons to use or not use ZomboDB [1]? It claims to lets you use ElasticSearch from PG seamlessly e.g. it manages coherency of which results ought to be returned according to the current transaction. (I've never quite ended up needing to use ES but it's always seemed to me I'd be likely to need ZomboDB if I did.) [1] https://github.com/zombodb/zombodb
Though I have only a recreational interest in datastores, I would be pretty wary of a service that claims to strap a Consistant-Unavailable database (Postgres) to an Inconsistent-Available database (ElasticSearch) in a useful way. Doing so would require ElasticSearch to reach consensus on every read/write, which would remove most of the point of a distributed cluster. Despite this, ZomboDB's documentation says "compl…
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 abort if ElasticSearch runs into network trouble...
I had to search my own repo to see where I make this claim. I don't. I do note that network failures between PG & ES will cause the active Postgres xact to abort. On top of that, any error that ES is capable of reporting back to the client will cause the PG xact to abort -- ensuring consistency between the two.
Because the ES index is properly refreshed as it relates to the active Postgres transaction, all of ES' aggregate search functions are capable of providing proper MVCC-correct results, using the parallelism provided by the ES cluster.
I don't have the time to detail everything that ZDB does to project Postgres xact snapshots on top of ES, but the above two points are the easy ones to solve.
I had to install software but on Cloud SQL you can't. You have to do it on your instances.
Earlier quoted context omitted.
I have a table with over a billion rows and most full-text searches still respond in around a few milliseconds. I think this will depend on a lot of factors, such as proper indexing, and filtering down the dataset as much as possible before performing the full-text ops. I've spent a considerable amount of time on optimizing these queries, thanks to tools like PgMustard [0]. Granted, I do still have a couple slow quer…
Silly question, I'm using pg right now and most of my queries are something like this (in english) Find me some results in my area that contain these categoryIds and are slotted to start between now and next 10 days. Since its already quite a filtered set of data, would that mean I should have little issues adding pg text search because with correct indexing and all, it will usually be applied to a small set of data?…
The GIN indexes for FTS don't really work in conjunction with other indices, which is why https://github.com/postgrespro/rum exists. Luckily, it sounds like you can use your existing indices to filter and let postgres scan for matches on the tsvector. The GIN tsvector indices are quite expensive to build, so don't add one if postgres can't make use of it!
Earlier quoted context omitted.
Exact phrase matching. This generally requires falling back to ILIKE, which is not performant.
Exact phrase searching works in PostgreSQL full-text search - here's an example: https://simonwillison.net/search/?q=%22nosql+database%22 I'm using search_type=websearch https://github.com/simonw/simonwillisonblog/blob/a5b53a24b00... That's using websearch_to_tsquery() which was added in PostgreSQL 11: https://www.postgresql.org/docs/11/textsearch-controls.html#...
Are there any workarounds for that?
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…
The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated and subtle problem, and frequently depends on signals that are not in the document itself. Google's big insight is that how other people talk about a website is more important than how the website talks about itself, and its ranking algorithm weights accordingly.
ES has the basic building blocks to construct such a ranking algorithm. In terms of fundamental infrastructure I found ES to be just as good as Google, and better in some ways. But its out-of-the-box ranking function sucks. Expect to put a domain expert just on search ranking and evaluation to get decent results, and they're going to have to delve pretty deeply into advanced features of ES to get there.
AFAICT Postgres search only lets you tweak the ranking algorithm by assigning different weights to fields, assuming that the final document score is a linear combination of individual fields. This is usually not what you want - it's pretty common to have non-linear terms from different signals.
Earlier quoted context omitted.
That why this really needs to get merged: https://github.com/postgrespro/rum
TF/IDF is listed as a TODO on that repo, and I don't see a PR which promises to provide it.
I really think the is a neglected area and if PG was able to merge in TF-IDF and BM25 there would be little reason to use a separate search db / engine and many advantages with it being integrated.