Tin: full-text search for Postgres

Tin: full-text search for Postgres planetscale.com

TIN is a fast, full-featured, full-text search index for Postgres

230 points•ksec•12 days ago•98 comments•

#full #text #search #tin #postgres #text search #full text #full text search

98 comments

I think what we're seeing with every database company providing new full-text search capabilities is an example of AI coding productivity showing up in the real world.

It started with paradeDB and pg_search https://www.paradedb.com/blog/introducing-search

Timescale has pg_textsearch https://github.com/timescale/pg_textsearch

Neon and Databricks have Lakebase Search https://docs.databricks.com/aws/en/oltp/projects/lakebase-se...

Now PlanetScale.

AFAIK all of these are implementations of the BM25 algorithm. You can just tell an agent to read about BM25 and implement it in your system of choice. Cool to see. Seems like there's still a lot of juice to be squeezed out of how it's architected and integrated into each system, but you can't help but wonder if this will lead to aggressive commodification

samwillis11 days ago

There is a lot of truth to this, but it's also very much down to domain experts being able to do this to move faster.

Planetscale (assuming they used a agentic development practice) will have pulled this off, to the level of performance that they have, because they have a team of very highly experienced Postgres developers. Their knowlage of Postgres internals will have given them the insights needed to steer the models to a plan that used the architecture as described in the post. That's not something a model can do on its own*

World experts + LLMs = moving mountains.

(* we're obviously seeing something a little different from inside the research teams in the labs. They are showing that the models, when you burn the level of tokens only they can, are able to do novel things from the models own insights.)

nozzlegear11 days ago

> There is a lot of truth to this, but it's also very much down to domain experts being able to do this to move faster.

Yeah, I don't think I could tell Qwen3.8 (my LLM of choice) to study up on bm25 and then implement full text search in the couchdb instances I maintain without studying both bm25 and couchdb internals myself.

cjonas11 days ago

Seems like a lot of this knowledge was encoded into the blog post. I wonder if given this post and access to a planet scale instance to compare with, how close an agentic agent could get.

geraneum11 days ago

I think we can frame it as LLMs materializing existing potential. It seems like there needs to be an underlying potential to tap into, without which, the results could be slop.

bddicken11 days ago

THIS++

throwaway778311 days ago

LLMs are becoming a world expert in everything.

I am exploring this exact area of search and analytics for vanilla postgres as replicas. Guess what, the LLM came up with this exact conclusion of using ctids as docids, all by itself. It was surreal for me to read the blog above , when I hit that paragraph about ctids.

I am no postgres internals expert.

sandeepkd11 days ago

Was not able to find any mention of AI or LLM usage on the article. The article is very detailed and goes in depth about how they have been able to do it. If anything it just shows the database level expertise and understanding of the existing implementations to find the optimization opportunities.

Unless its explicitly mentioned lets not dilute the credit of the folks who worked on.

CodesInChaos11 days ago

ParadeDB's implementation builds on the Tantivy crate, which predates AI coding.

Boxxed11 days ago

BM25 is the easy part. It's probably a dozen lines of code, maybe two. The real work is in the design of the index that enables you to write that dead-simple function -- the in-memory data structures, the on-disk data structures, keeping them in sync, fault tolerance, batching, and a bunch of other things.

If you ask Claude to "implement BM25" you will not get what you want. I see a whole lot of this: people that don't know what they're doing get garbage results out of LLMs.

ceuk11 days ago

I don't completely disagree with your hypotheses but it feels like the hard part of his TIN stuff isn't BM25 (which has been around for donkeys years) it's all the hardcore storage engine work around it. And is an LLM particularly good at e.g. segment merging under a thousand updates a second? I've had a few situations where I've been told "we've hit the perf floor" by Claude only to have persisted myself and shaved substantial amounts off still.

More damning for the theory might be that I think paradedb's pg_search predates the agentic coding by a few years?

philippemnoel10 days ago

ParadeDB/pg_search developer here. Yes, we started the project before LLMs. Moreover, our project is now much more than just full-text search.

I do agree with the sentiment here that many of the other FTS-in-Postgres projects in recent years have been heavily enabled by vibe coding.

Tiberium12 days ago

If anyone's curious - https://planetscale.com/docs/postgres/search/get-started#loc...:

They're not providing a local extension with the same performance at the time - it's only offered on their cloud services.

The local version https://github.com/planetscale/lead is mainly just for testing the syntax, it doesn't have the same perf characteristics.

noir_lord12 days ago

Becoming more the norm for them, Neki is the same.

Immediately rules out ever using them (though I don't currently have any problems that would benefit from that level of scale currently, have in the past though).

Postgres's license allows this but for me (personally) it leaves a bad taste.

Also it's not really "full-text search for Postgres" it's "full-text search for our hosted version of Postgres" so the title is a little misleading.

Cyph0n11 days ago

> Becoming more the norm for them, Neki is the same.

Uh, isn’t this becoming the norm everywhere ever since LLMs have been trained on OSS without credit or attribution? Why wouldn’t you want to hide your stuff going forward?

In my view, OSS is only going to move more and more towards one of two models: open core + proprietary functionality (e.g. MongoDB) OR open source + private tests (e.g. SQLite).

samlambert11 days ago

why?

dbbk11 days ago

Why would you need super fast search for local testing?

zombodb11 days ago

Why don’t you like Postgres’ license? It’s as permissive as a license gets.

pqdbr11 days ago

The problem is that they don’t support bare metal. I’d love to use PlanetScale in our bare metal servers.

samlambert11 days ago

we support bare metal inside AWS, GCP, and very soon Azure

groundzeros201511 days ago

Please read the Postgres manual. It has incredible built-in search capability.

simonw11 days ago

If you mean tsvector/tsquery - https://www.postgresql.org/docs/9.6/textsearch-intro.html - it's very good, but it's missing an important feature: ranking based on the overall document collection.

PostgreSQL built-in FTS provides a score for each row based just on the data for that row.

Relevance algorithms like BM25 take overall corpus statistics into account. If you search for a bunch of words and some of them are less common than others in the overall set of documents, documents that match THOSE words will score higher than matches for other words in your search.

That's what all of these additional extensions are providing.

groundzeros201510 days ago

> ranking based on the overall document collection.

Thanks for pointing out a real difference.

dragonwriter11 days ago

If you read deep into this, they claim much better performance than the built in search; they also imply that the built-in search is missing features they provide but don’t make clear which ones (I think it is just support in the same index for queries covering other conditions on other columns, because every other feature they claim seems to line up with the built in search features, which have been around for about 20 years.)

zombodb11 days ago

Over Postgres' FTS, TIN provides at least:

  - superior performance
  - superior operational overhead
  - no second copy of data in tsvector form
  - BM25 scoring support with optimized top-k output
  - runtime configurable scoring knobs
  - expression-attached score boosting
  - sophisticated span query support -- this is proximity search on steroids (https://github.com/planetscale/lead/tree/main/tinql/docs)
  - lossless term positions
  - index-answerable negative expressions (find all docs that don't contain a word)
  - full document hit highlighting
  - optimized exact `count(\*)`
  - term expansion via any of fuzzy matching, wildcards, regular expressions, and dictionary ranges
  - intentionally smaller user-facing SQL API surface

There's a lot we didn't cover in the announcement blog. I'm sure we'll do more as time goes on.

As an aside, something I personally think is cool, and I suppose you can do this with Postgres' built-in `@@` too, is that you can use TIN's full query language (linked above) against any text datum. This is a valid query:

  SELECT pid, query 
  FROM pg_stat_activity 
  WHERE query ==> 'select OR copy'

in other words, you don't need an index at all to use TIN's full query language against any text field in any query.

Boxxed11 days ago

The built in search can't do any scoring mechanism that involves corpus-wide stats, so things like tfidf and bm25 are right out. If you don't need that then great, but in my experience the results are much worse.

paulddraper11 days ago

It has it.

Incredible? No.

groundzeros201511 days ago

I think it’s fantastic.

You want to use this thing instead?

usernametaken2911 days ago

Interestingly enough SQLites FTS supports Lucene queries out of the box with great performance characteristics. IIRC only writes become pretty slow after a while. I’ve always wondered what exactly would prevent PostgreSQL from strapping that implementation into its own database. My experience with ts_query hasn’t been particularly rosy. It can be better than LIKE but only marginally so and at the cost of insane index sizes… If this extension becomes open source and we can test it out in the real world I’m sure there’s a sweet spot

xcc364111 days ago

SQLite FTS relies on shadow B-trees under single-writer locks. Postgres index access methods must map postings directly to physical ctid tuples, surviving MVCC visibility checks and heap tuple churn.

znpy11 days ago

This was already posted and ignored at https://news.ycombinator.com/item?id=49751888 so i’ll ask the same question: again:

I don’t see any github link, is this 21st century embrace, extend, extinguish ?

rs_rs_rs_rs_rs11 days ago

>is this 21st century embrace, extend, extinguish

I assume here you're talking about Amazon's modus operandi?

znpy10 days ago

ot reply, congrats

Read the full thread on Hacker News →

98 comments

Related stories