PostgreSQL BM25 全文搜尋：透過這些技巧加速效能
➤ 破解效能慢的迷思，提升 PostgreSQL 全文搜尋的真實速度
✤ https://blog.vectorchord.ai/postgresql-full-text-search-fast-when-done-right-debunking-the-slow-myth
這篇文章探討了 PostgreSQL 內建全文搜尋（FTS）的效能問題，並針對 Neon 公司先前分析中使用的基準測試設定提出修正。文章指出，Neon 的基準測試可能未採用最佳化設定，導致標準 FTS 效能顯著落後於使用 Rust 語言的 Tantivy 函式庫所建構的 pg_search 擴充功能。透過預先計算並儲存 `tsvector`，以及關閉 GIN 索引的 `fastupdate` 選項，標準 FTS 的效能可提升約 50 倍。此外，文章也探討了排序效能（`ts_rank`）的限制，並推薦使用 VectorChord-BM25 擴充功能以獲得更快速、更具相關性的搜尋結果。
+
#PostgreSQL #全文搜尋 #BM25 #效能優化

Wow, #PostgreSQL just got a shiny new toy to play well with others! Apparently, adding #BM25 ranking makes it 3x faster than Elasticsearch—because, you know, exaggerated #performance boasts never get old. So, strap on folks, because your slightly quicker searches in databases are the next big thing!
https://blog.vectorchord.ai/vectorchord-bm25-revolutionize-postgresql-search-with-bm25-ranking-3x-faster-than-elasticsearch #Elasticsearch #database #search #speed #HackerNews #ngated

BM25 in PostgreSQL – 3x Faster Than Elasticsearch — https://blog.vectorchord.ai/vectorchord-bm25-revolutionize-postgresql-search-with-bm25-ranking-3x-faster-than-elasticsearch
#HackerNews #BM25 #PostgreSQL #Elasticsearch #FastSearch #DatabaseOptimization

Do lots of metadata really help in semantic search?
https://old.reddit.com/r/MachineLearning/comments/1h6f39a/d_do_lots_of_metadata_really_help_in_semantic

Comment: new thread on utility of metadata, BM25, LLM, search, semantics ...

After a few weeks of being unable to work on it, I've just published a new version of housaku, my personal search engine powered by SQLite's FTS5!

This update includes:

- Simplified Web UI.
- Enhanced TUI.
- New command to reset the database.
- Theming support.

I also updated the README, which was a bit outdated.

https://github.com/dnlzrgz/housaku

These past days, I've been working on and having lots of fun with Housaku (豊作), a personal search engine built on SQLite's FTS5. Currently it has:

- Support for file formats like .txt, .md, .pdf, .epub, and .docx
- Basic web scraping functionality
- BM25 for search results, thanks to SQLite's FTS5

And files are processed in parallel, making indexing faster than I expected!

If you're interested, check it out at:

https://github.com/dnlzrgz/housaku

Postgres as a search engine
https://anyblockers.com/posts/postgres-as-a-search-engine
https://news.ycombinator.com/item?id=41343814

Build a retrieval system with semantic, full-text, & fuzzy search in Postgres to be used as a backbone in RAG pipelines.

We’ll combine 3 techniques:

* full-text search with tsvector
* semantic search with pgvector
* fuzzy matching with pg_trgm

* bonus: BM25

https://en.wikipedia.org/wiki/Okapi_BM25
https://blog.paradedb.com/pages/elasticsearch_vs_postgres
https://news.ycombinator.com/item?id=41173288

Qdrant 1.7.0
https://qdrant.tech/articles/qdrant-1.7.x/
#ycombinator #vector_search #new_features #sparse_vectors #discovery #exploration #custom_sharding #snapshot_based_shard_transfer #hybrid_search #bm25 #tfidf #splade #qdrant

On a roll: Qdrant brings sparse vectors, discovery search and custom sharding
https://qdrant.tech/articles/qdrant-1.7.x/
#ycombinator #vector_search #new_features #sparse_vectors #discovery #exploration #custom_sharding #snapshot_based_shard_transfer #hybrid_search #bm25 #tfidf #splade #qdrant