[HN Gopher] Show HN: Postgres extension for BM25 relevance-ranke...
___________________________________________________________________
Show HN: Postgres extension for BM25 relevance-ranked full-text
search
Last summer we faced a conundrum at my company, Tiger Data, a
Postgres cloud vendor whose main business is in timeseries data. We
were trying to grow our business towards emerging AI-centric
workloads and wanted to provide a state-of-the-art hybrid search
stack in Postgres. We'd already built pgvectorscale in house with
the goal of scaling semantic search beyond pgvector's main memory
limitations. We just needed a scalable ranked keyword search
solution too. The problem: core Postgres doesn't provide this; the
leading Postgres BM25 extension, ParadeDB, is guarded behind AGPL;
developing our own extension appeared daunting. We'd need a small
team of sharp engineers and 6-12 months, I figured. And we'd
probably still fall short of the performance of a mature system
like Parade/Tantivy. Or would we? I'd be experimenting long enough
with AI-boosted development at that point to realize that with the
latest tools (Claude Code + Opus) and an experienced hand (I've
been working in database systems internals for 25 years now), the
old time estimates pretty much go out the window. I told our CTO I
thought I could solo the project in one quarter. This raised some
eyebrows. It did take a little more time than that (two quarters),
and we got some real help from the community (amazing!) after open-
sourcing the pre-release. But I'm thrilled/exhausted today to share
that pg_textsearch v1.0 is freely available via open source
(Postgres license), on Tiger Data cloud, and hopefully soon, a
hyperscalar near you: https://github.com/timescale/pg_textsearch
In the blog post accompanying the release, I overview the
architecture and present benchmark results using MS-MARCO. To my
surprise, we were not only able to meet Parade/Tantivy's query
performance, but exceed it substantially, measuring a 4.7x
advantage on query throughput at scale:
https://www.tigerdata.com/blog/pg-textsearch-bm25-full-text-...
It's exciting (and, to be honest, a little unnerving) to see a
field I've spent so much time toiling in change so quickly in ways
that enable us to be more ambitious in our technical objectives.
Technical moats are moats no longer. The benchmark scripts and
methodology are available in the github repo. Happy to answer any
questions in the thread. Thanks, TJ (tj@tigerdata.com)
Author : tjgreen
Score : 78 points
Date : 2026-03-31 16:29 UTC (6 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| jascha_eng wrote:
| FWIW TJ is not your average vibe coder imo:
| https://www.linkedin.com/in/todd-j-green/
|
| In september he burned through 3000$ in API credits though, but I
| think that's before we finally bought max plans for everyone that
| wanted it.
| simonw wrote:
| This is really cool. I've built things on PostgreSQL ts_vector()
| FTS in the past which works well but doesn't have whole-index
| ranking algorithms so can't do BM25.
|
| It's a bit surprising to me that this doesn't appear to have a
| mechanism to say "filter for just documents matching terms X and
| Y, then sort by BM25 relevance" - it looks like this extension
| currently handles just the BM25 ranking but not the FTS
| filtering. Are you planning to address that in the future?
|
| I found this example in the README quite confusing:
| SELECT * FROM documents WHERE content <@>
| to_bm25query('search terms', 'docs_idx') < -5.0 ORDER BY
| content <@> 'search terms' LIMIT 10;
|
| That -5.0 is a magic number which, based on my understanding of
| BM25, is difficult to predict in advance since the threshold you
| would want to pick varies for different datasets.
| tjgreen wrote:
| I actually don't love this example either, for the reasons you
| mention, but at some point we had questions about how to filter
| based on numeric ranking. Thanks for the reminder to revisit
| this.
|
| Re filtering, there are often reasonable workarounds in the SQL
| context that caused me to deprioritize this for GA. With your
| example, the workaround is to apply post-filtering to select
| just matches with all desired terms. This is not ideal
| ergonomics since you may have to play with the LIMIT that
| you'll need to get enough results, but it's already a familiar
| pattern if you're using vector indexes. For very selective
| conditions, pre-filtering by those conditions and then ranking
| afterwards is also an option for the planner, provided you've
| created indexes on the columns in question.
|
| All this is just an argument about priorities for GA. Now that
| v1.0 is out, we'll get signal about which features to
| prioritize next.
| mbreese wrote:
| While we're talking about filtering -- is there a way to set
| a WHERE clause when you're setting up the index? I've been
| working on this a lot recently for a hybrid vector search in
| pg. One of the things that I'm running up against is setting
| a good BM25 index for a subset of a table (the where clause).
| I have a document subsets with very different word
| frequencies, so I'm trying to make sure that the search works
| on a set subset.
|
| I think I can also setup partitions for this, but while
| you're here... I'm very excited to start to roll this out.
| tjgreen wrote:
| Partitions would be one option, and we've got pretty robust
| partitioned table support in the extension. (Timescaledb
| uses partitioning for hypertables, so we had to front-load
| that support). Expression indexes would be another option,
| not yet done but there is a community PR in flight:
| https://github.com/timescale/pg_textsearch/pull/154
| gplprotects wrote:
| > ParadeDB, is guarded behind AGPL
|
| What a wonderful ad for ParadeDB, and clear signal that
| "TigerData" is a pernicious entity.
| tjgreen wrote:
| Okay then!
| lsaferite wrote:
| You: > "TigerData" is a pernicious entity
|
| TigerData: > pg_textsearch v1.0 is freely available via open
| source (Postgres license)
|
| They deemed AGPL untenable for their business and decided to
| create an OSS solution that used a license they were
| comfortable with and _they_ are somehow "pernicious"? Perhaps
| take a moment to reflect on your characterization of a group
| that just contributed an alternative OSS project for a specific
| task. Not only that, but they used a VERY permissive license.
| I'd argue that they are being a better OSS community member for
| selecting a more permissive license.
| shreyssh wrote:
| Nice work. pg_search has been on my radar for a while, having
| BM25 natively in Postgres instead of bolting on Elasticsearch is
| a huge DX win. Curious about the index build time on larger
| datasets though. I'm working with ~2M row tables and the
| bottleneck for most Postgres extensions I've tried isn't query
| speed, it's the initial indexing. Any benchmarks on that?
| tjgreen wrote:
| Yep, there are numbers in the blog post and repo. We are able
| to index MS-MARCO v2 (138M documents, around 50GB of raw data)
| in a bit under 18 minutes.
| tjgreen wrote:
| For 2M scale dataset, you should be able to index in about 1
| minute on low-end hardware. See the MS-MARCO v1 (8M
| documents) numbers, measured on cheap Github runners.
| gmassman wrote:
| Very exciting! Congrats on the release, this will be a huge
| benefit to all folks building RAG/rerank systems on top of
| Postgres. Looking forward to testing it out myself.
| 3abiton wrote:
| This is pretty much my case right now. BM25 is so useful in
| many cases and having with with postgres is neat!
| jackyliang wrote:
| VERY excited about this, literally just looking to build hybrid
| search using Postgres FTS. When will this be available on
| Supabase?
| tjgreen wrote:
| You'll have to ask Supabase!
| Unical-A wrote:
| Impressive benchmarks. How does the BM25 implementation handle
| high-frequency updates (writes) while maintaining search latency?
| Usually, there's a trade-off between ingest speed and search
| performance in Postgres-based full-text search.
| tjgreen wrote:
| There is indeed such a tradeoff. The architecture is designed
| with an eye towards making this tradeoff tunable (frequency of
| memtable spills, aggressiveness of compaction) but the work
| here is not yet finished. We chose to prioritize optimizing
| bulk-indexing and query performance for GA, since this is
| already enough for many applications. I'm excited to get to the
| point where we have brag-worthy benchmark numbers for high-
| frequency updates as well!
| andai wrote:
| Can you explain this in more detail? Is this for RAG, i.e.
| combining vector search with keyword search?
|
| My knowledge on that subject roughly begins and ends with this
| excellent article, so I'd love to hear how this relates to that.
|
| https://www.anthropic.com/engineering/contextual-retrieval
|
| Especially since what Anthropic describes here is a bit of a rube
| Goldberg machine which also involves preprocessing (contextual
| summarization) and a reranking model, so I was wondering if
| there's any "good enough" out of the box solutions for it.
| tjgreen wrote:
| Yes, hybrid search is one of the main current use cases we had
| in mind developing the extension, but it works for old-
| fashioned standalone keyword-only search as well. There is a
| lot of art to how you combine keyword and semantic search
| (there are entire companies like Cohere devoted to just this
| step!). We're leaving this part, at least for now, up to
| application developers.
| zephyrwhimsy wrote:
| Input quality is almost always the actual bottleneck. Teams spend
| months tuning retrieval while feeding HTML boilerplate into their
| vector stores.
| timedude wrote:
| When is this available on AWS in Aurora? Anyone from AWS here,
| add it pronto
| mattbessey wrote:
| Please oh please let GCP add this to the supported managed
| Postgres extensions...
| tjgreen wrote:
| A little birdie told me that efforts are underway to support
| the extension in Alloy, at least!
| devmor wrote:
| This is really cool to see! I've been using BM25+sqlite-vec for
| contextual search projects for a little while, it's a great
| performance addition.
| maweaver wrote:
| I've been doing some RAG prototypes with hybrid search using
| pg_textsearch plus pgvector and have been very pleased with the
| results. Happy to see a 1.0 release!
| bradfox2 wrote:
| Thank you!! Goodbye manticore if this works.
| piskov wrote:
| On a tangent note it's amazing how hard it is to have a good
| case-insensitive search in Postgres.
|
| In SQL Server you just use case-insensitive collation (which is a
| default) and add an index (it's the only one non-clustered) and
| call it a day.
|
| In postgres you need to go above and beyond just for that. It's
| like postgres guys were "nah dog, everybody just uses lowercase;
| you don't need to worry of people writing john doe as John Doe)".
|
| And don't get me started with storing datetime with timezone (e.g
| "4/2/2007 7:23:57 PM -07:00"). In sql server you have
| datetimeoffset; in Postgres you fuck off :-)
| robotswantdata wrote:
| "Just use Postgres" greybeards right again. Looking forward to
| giving this a go soon
___________________________________________________________________
(page generated 2026-03-31 23:00 UTC)