Postgres text search: balancing query time and relevancy | Hacker News Reader

Postgres text search: balancing query time and relevancy

about.sourcegraph.com

116 pointsSerCe13 comments

A nice exploration of Postgres trigram extension.

A few things I'd try if I was building a dedicated code search tool is to introduce custom per-language tokenizers for Postgres FTS that actually tokenize according to language rules (thus making "def" or "if" a stopword for Python, but also splitting "doSomethingCrazy" into ("do", "something", "crazy").

Then, I'd do two searches: one using such a FTS query first for more relevant results, and trigram search after. Combining results might be tricky, but not overly so imho (though the devil is in the details).

As for "limit interval to", you can approximate that by doing a LIMIT on unsorted results in a subselect (get the number with heuristics and adjust it as the data set grows), and then sort those by relevance: the result is effectively the same, except that you are using dataset size as the boundary instead of time.

> you can approximate that by doing a LIMIT on unsorted results in a subselect

If you still have time left over, you can increase this LIMIT and run the query again (c.f. "iterative deepening") to get something like the desired time-limited query behaviour

A search with an order by/limit across 1 million rows with a GIST trigram index shouldn't be taking multiple seconds if the text being searched isn't huge. In the case of GitHub repository names, you might as well use `similarity` (<->) instead of `word_similarity` (<<->) which would be a speedup, because it's just a pure index scan.

How does text search in Postgres compare with Elasticsearch?

"relevancy" is often scoped to the _textual_ relevancy of matching documents, where things like boosting matches in the title vs. the body and tf/idf scores of the involved terms matter a lot.

In practice, a lot of relevancy tuning is about properly factoring in other "signals", such as something's recency, distance, popularity, stock availability, the section of the website being searched from, etc.

Thus, there's often a lot more than just the textual relevancy being involved.

Postgres will never compromise on MVCC correctness, and therefore doesn't do any caching of partial results beyond having things in shared buffers and/or the page cache.

Elasticsearch will err on prioritising speed and cacheability. The "signals" that appear again and again in the same queries will likely be pretty compactly cached as (roaring) bitmaps in a filter cache. That enables potentially being a _lot_ faster, because it's ok with "you're seeing approximately everything that was ingested about a second a go".

In addition to that, Elasticsearch(/Lucene) comes with a lot more tooling for text processing, stemming, compound word splitting, etc.

So does it kinda depend on what sort of searching you want to do?

I'm currently trying to decide between ES and PostgreSQL. As I have read replica's for PG and the plan is to flatten the data into a single table, there's only ~4 fields that are text betwee 1-256 in length. While the other ~30 fields are int/decimal/date fields. There's prob ~8m records roughly that grows by 2-3m / year. So I feel like ES might be overkill for this and PG would be totally fine.

one lense for the decision -- look at the rate of change of your dataset, and how important is total throughput.. if your dataset does not change very much, or changes at defined times in predictable ways, then I would say Postgres is a fine choice and your biggest problem will be defending against uninformed colleagues. Many small parts of Postgres are well thought out, and the manual has improved steadily over two decades.

New data has a few fields which could change a lot within the first few hours but after that the data is more or less set and never changes.

I love the PostgreSQL documentation!

You may want to check out the ZomboDB Postgres extension!

Introducing Elasticsearch to your stack increases complexity tenfold. Elasticsearch itself is a complex beast and if it's your first distributed cluster, beware the time spent operating it ^1. Apart from that, you now have to manage the data replication between your database (source of truth) and Elasticsearch (search optimized view). This can be easy or nightmarish, depending on the nature of your application.

So the first thing I recommend anyone contemplating this decision is: can Postgres FTS implement what you need? Then prefer staying inside Postgres. The scenario I'd feel justified in adopting Elasticsearch are: immense amounts of data (at least above a million of documents), complex user queries (e.g. boolean clauses and field specification), complex NLP and relevance engineering.

My opinion is the same for Apache Solr. There are other search options (Typesense, MeiliSearch, Vespa) that I never tried and can't compare.

1. Yes even managed Elasticsearch offerings like AWS OpenSearch and Elastic Cloud require quite a bit of operations. Sharding, index configuration, node sizing, disk monitoring, threadpool monitoring, allocation issues... frequent worries that managed offerings don't handle for you.

We use Elasticsearch and Kibana to store, search and visualize tranzactions, microservice calls and logs. We don't replicate anything from databases to Elasticsearch.

I was just curious of the performance delta, if any.

I love the “search optimized view” framing. That is indeed the case for us. It’s hard to store objects inside postgres with loose schema (eg not all attributes for the document are always present).

I keep dreaming that AWS will someday release the unholy child of dynamodb and solr! Maintenance of ES can wear you down.

I have never tried AWS managed elasticsearch, but Azure Search doesn't have many knobs. Most of the time it's just instance size, replica and partition count.

The downside is that the service is probably called "Azure Cognitive DeepMind Quantum DevOps Search" by now.

Reply on news.ycombinator.com