PlanetScale Launched Text Search and We Have a Lot to Say (Part I)
By Ming Ying on October 1, 2026
Two weeks in the past, PlanetScale unveiled TIN, a full-text search extension for Postgres. Their launch submit reported spectacular efficiency wins over a subset of ParadeDB’s textual content search performance, particularly BM25-ranked textual content search and doc counts.
We’d like to increase kudos to the PlanetScale group 1. It’s nice to see one other Postgres platform investing in search (seems individuals wish to search their relational information), and it’s clear that loads of considerate engineering went into TIN. We’re additionally glad to see PlanetScale’s adoption of ParadeDB’s benchmarker tool, which we constructed for precisely this sort of testing.
Let’s be very clear about one factor: TIN is quick (at the least 8x quicker than ParadeDB 0.25 in each PlanetScale benchmark). So quick that the one response which made sense was to close up and placed on our efficiency optimization hats. Two weeks later, right here’s the BM25-ranked earlier than and after, utilizing the identical StackExchange benchmark dataset, harness, and machine varieties (though TIN isn’t open-source so it’s working on PlanetScale)2:
↑ Higher is healthier
Warm-cache, read-only runs. TIN makes use of dense_ratio=2 with search elision disabled; see Handling Common Terms. One-second buckets; latency makes use of nearest-rank percentiles. Lines use a centered 9-second shifting common; legend values are unsmoothed full-run outcomes.
What’s attention-grabbing just isn’t that we shortly closed the hole, however how we closed it. TIN’s submit claims that their efficiency is because of a elementary architectural distinction that makes use of Postgres’ inside ctid fields as doc identifiers. However, we closed this hole by way of a couple of optimization passes that had little to do with how paperwork are recognized. We additionally tweaked some benchmark settings that didn’t give a completely truthful comparability — extra on this later.
Let’s unpack our fixes and the configuration modifications one after the other.
An Overview of Text Search, and How TIN Claims to Be Faster
Without Text Index
With Text Index
The coronary heart of any textual content search index is a postings checklist: a per-term checklist of doc identifiers containing that time period. For occasion, if an index has paperwork 1 to 10 and the phrase “database” seems in paperwork 2 and 4, the postings checklist for “database” is solely [2, 4]. Postings lists permit you to determine paperwork matching particular phrases very effectively.
Tantivy, the search library behind ParadeDB, makes use of sequential u32 doc IDs for its postings. These identifiers are inside to Tantivy, and are assigned primarily based purely on insertion order. For the rest of this submit, DocId refers back to the u32 doc identifier utilized by Tantivy and ParadeDB.
Postgres identifies its rows by ctid values. A ctid is a tuple pointing to a row’s bodily location in Postgres’ block-based storage. (190, 17) identifies the row which is presently present in slot 17 of block 190.
Because ParadeDB is a Postgres index powered by Tantivy, there has to exist a map between DocId and ctid values. The crux of TIN’s submit is that utilizing ctid values instantly as doc identifiers eliminates this map and allows environment friendly bitmap operations and visibility checks. PlanetScale attributes a lot of TIN’s efficiency benefit to the downstream advantages of that selection.
But Is It All About a Different Document Identifier?
The launch submit benchmarks two broad question varieties: Top Ok matches by BM25 rating and COUNT queries over matching paperwork.
For counts, the ctid argument made sense. When thousands and thousands of matches require visibility checks, translating DocId values into ctid values provides up. Organizing postings round Postgres pages creates alternatives to learn much less information and batch that work.
For BM25 Top Ok queries, we had been skeptical. ParadeDB defers ctid lookups till the ultimate Top Ok paperwork have been gathered. For a high 10 question, meaning 10 lookups. These lookups aren’t free, however they’re tiny within the profile and don’t clarify an orders-of-magnitude hole.
Instead, we suspected that we may shut the hole with numerous optimization alternatives elsewhere in our code.
This submit focuses on our Top Ok BM25 optimizations. We’ve additionally optimized
COUNT, which is able to are available Part II.
Optimization 1: Reducing Random Access During BM25 Scoring
We began with a easy question: give me the ten most related paperwork containing a single time period ordered by BM25 rating. For quicker native iteration, we used the smaller 28.7M Hacker News dataset.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, by
FROM hn_items
WHERE title === 'database'
ORDER BY pdb.rating(id) DESC
LIMIT 10;
TIN touched far fewer Postgres pages than ParadeDB on this question, so we suspected that was the primary purpose it was quicker. This would additionally compound on the StackExchange dataset when some reads come off disk. ParadeDB has a brand new function that attributes web page accesses to the information constructions saved in these pages. It instructed us right away that we had a problem:
| Data construction | Share of web page accesses |
|---|---|
| Fieldnorms | 1,513 (83%) |
| Everything else (postings, metadata, and so forth.) | 311 (17%) |
“Fieldnorms” encode the size of a doc’s listed discipline, which BM25 makes use of to normalize scores. A fieldnorm in Tantivy is tiny: a doc size quantized right into a single-byte fieldnorm_id worth. How may one thing tiny account for thus many reads?
The drawback was locality. Tantivy shops fieldnorms individually from postings, in an array listed by DocId. Reading a time period’s postings is sequential, however fetching the corresponding fieldnorms can leap throughout that array. With Tantivy’s ordinary memory-mapped storage, this structure is probably going high quality3 as a result of every resident fieldnorm is an affordable reminiscence lookup, however in Postgres it meant touching roughly 1,500 distinct fieldnorm pages for this question.
Our repair was to retailer a fieldnorm array alongside every postings checklist, in the identical order because the postings’ DocId values. Scoring may then learn fieldnorms sequentially alongside postings, eliminating the scattered lookups. After this modification, fieldnorm accesses dropped from 1,500 pages to simply 30 (!).
Before: Shared fieldnorm array
Postings Fieldnorms
"database": [DocId values] [fieldnorm IDs for all documents]
"rust": [DocId values]
...
After: Fieldnorm array per time period
Postings Fieldnorms
"database": [DocId values] "database": [fieldnorm IDs]
"rust": [DocId values] "rust": [fieldnorm IDs]
... ...
The tradeoff is storage, since a doc’s fieldnorm is now repeated for every distinct time period it accommodates. Fortunately, this doesn’t essentially imply multiplying fieldnorm storage by the variety of phrases. In real-world corpora, most phrases have brief postings lists and correspondingly small fieldnorm arrays. For occasion, this modification grew the 28.7M HN index by about 9%.
Optimization 2: Choosing the Right Blockmax Pruning Algorithm
Breaking out fieldnorms delivered an enormous speedup for queries with a small variety of phrases, however we had been nonetheless not happy with our efficiency in disjunction queries with many phrases. For occasion, this question matches paperwork containing any of those phrases:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, by
FROM hn_items
WHERE textual content ||| 'rust arc clone reminiscence security borrow checker possession lifetime guidelines'
ORDER BY pdb.rating(id) DESC
LIMIT 10;
In this question, we seen that despite the fact that buffer reads after the earlier optimization fell by roughly 80%, question instances solely dropped by 5%, suggesting that the bottleneck on this case was algorithmic.
We profiled and found that more often than not was spent in one thing known as the Blockmax WAND loop.
For context: Blockmax is the usual algorithm utilized by search engines like google to effectively skip previous chunks of postings when executing disjunction (e.g. termA OR termB) queries. There are two households of Blockmax: WAND and MAXSCORE. We gained’t go into the intricacies of how they work (there are many good technical blogs for this), however at a excessive stage:
- “Blockmax” comes from the truth that we are able to partition postings into blocks and for every block precompute and retailer the utmost attainable rating that any time period from this block may contribute to the ultimate BM25 rating.
- A block is skipped if its max rating can’t presumably beat the present Top Ok threshold. WAND and MAXSCORE are two alternative ways of doing this skipping.
The tradeoff between WAND and MAXSCORE is how a lot work they spend deciding what to skip. WAND skips extra, however spends extra CPU cycles to take action. MAXSCORE skips much less, however incurs much less overhead. When queries include extra phrases, WAND’s overhead grows and may outweigh the work it skips.
Tantivy makes use of WAND. Lucene additionally used WAND till 2023, after they launched MAXSCORE for sure queries. Today, Lucene dynamically chooses both WAND or MAXSCORE relying on the question form.
We carried out a MAXSCORE path with a easy choice heuristic that makes use of MAXSCORE for disjunctions with at the least three phrases and sufficiently dense postings and WAND for every part else. For the question above containing 10 phrases, we not solely introduced p50 latency down by ~6x and p95 by ~8x, we are actually 2x quicker vs. TIN on our 28.7M HN dataset:
↑ Higher is healthier
Warm-cache, read-only runs. TIN makes use of dense_ratio=0.1 with search elision enabled; see Handling Common Terms. One-second buckets; latency makes use of nearest-rank percentiles. The match selector doesn’t apply to one-term queries. Lines use a centered 9-second shifting common; legend values are unsmoothed full-run outcomes.
Benchmark Configuration Changes
PlanetScale’s benchmarks had been constructed pretty, except for two anomalies that unintentionally favored TIN: a ParadeDB syntax oversight and the way TIN handles widespread phrases.
ParadeDB Syntax
We seen that the TIN benchmarks used ParadeDB’s question string parser, which accepts Tantivy’s mini question language by way of the @@@ operator. The drawback is that these queries weren’t certified with a discipline identify, e.g.
When queries are unqualified, ParadeDB searches over all listed textual content fields by default. In the StackExchange dataset, each the id and physique columns had been listed, which implies ParadeDB was deprived as a result of it was looking out over two columns per question whereas TIN searched just one.
To guard in opposition to this, we moved all ParadeDB queries to make use of our native ||| (disjunction), &&& (conjunction), and ### (phrase) operators.
Handling Common Terms
We had been simply beating TIN on the BM25 search queries in our HN benchmark. But once we loaded PlanetScale’s StackExchange dataset and queries, we had been nonetheless 30% behind on throughput due to our for much longer tail latencies. How may we be a number of instances quicker on our benchmark however slower on PlanetScale’s?
It seems the hole got here from a scoring shortcut for widespread phrases that TIN calls dense-term elision, and we predict its use over the StackExchange dataset particularly is debatable.
A short explainer: widespread phrases like “the” and “is” have huge postings lists which might be costly to learn and rating. Yet BM25 weights them so low that they barely transfer the ultimate rating. Most search engines like google deal with this at indexing time with a stopword dictionary (each engines assist this, but it surely wasn’t enabled within the benchmark). TIN takes a unique method: at question time, it skips scoring for any time period that seems in additional than 10% of the corpus (configurable by way of dense_ratio). It’s essentially the most attention-grabbing concept in TIN from a search practitioner’s viewpoint, and we are going to spend extra time serious about this.
Of course there’s all the time a tradeoff, and right here it’s correctness. With elision on, TIN computes an approximation of BM25 by ignoring widespread phrases, inflicting outcomes to doubtlessly come again in a unique order than true BM25 would produce. This normally isn’t an issue for many real-world queries, except the question is made up fully of widespread phrases.
When we checked out what the Stack Overflow benchmark ran, we had been stunned to search out complete queries made up of those widespread phrases. That’s as a result of they had been generated by sampling consecutive phrase spans from the Stack Exchange corpus, which produced queries like “is it”, “to a”, and “is to” 4. When we ranked the biggest latency gaps between ParadeDB and TIN, those self same queries (which aren’t actual search queries) dominated the checklist. On them, TIN skipped a lot of the scoring work whereas ParadeDB computed precise scores.
For TIN, with elision enabled, we discovered that 5:
- 47.8% of queries returned at the least one consequence within the Top 10 that was not within the “true” Top 10.
- 6.4% of queries returned outcomes the place not one of the Top 10 had been within the “true” Top 10 — in different phrases, all the outcomes had been incorrect.
- Disjunctions had been particularly inaccurate: at the least one non-Top-10 consequence appeared within the Top 10 for 88.2% of disjunction queries.
| TIN Query Style | At least one consequence exterior the “true” Top 10 |
|---|---|
| Conjunction | 39.9% |
| Disjunction | 88.2% |
| Phrase | 13.8% |
| Overall | 47.8% |
To be clear, we’re not saying that “completely different from precise BM25” mechanically means “worse”. Elided phrases have low weight by design, and figuring out whether or not the elided outcomes are much less related would require human relevance judgments, which this benchmark doesn’t have. But the workload is framed as BM25 top-Ok search, and with elision enabled, TIN and ParadeDB will not be computing the identical rating.
For this purpose our headline comparability makes use of precise BM25 for each engines, with TIN configured with dense_ratio=2. Below we additionally present TIN’s default elision-enabled configuration, the place they “beat” us, as a result of that’s what the unique benchmark used.
We’re sharing each units of outcomes so readers can draw their very own conclusions. We don’t need benchmark settings to distract from the efficiency enhancements we made to ParadeDB. At the identical time, we’d be remiss to not point out them, as they make such an enormous distinction to the initially printed outcomes. For good measure we’ve got additionally included ParadeDB with stopwords enabled.
↑ Higher is healthier
Warm-cache, read-only mixed-query runs. One-second buckets; latency makes use of nearest-rank percentiles. Lines use a centered 9-second shifting common; legend values are unsmoothed full-run outcomes.
So Is There a Superior Document Identifier?
As for the selection of TIN’s ctid vs. ParadeDB’s u32 doc identifiers, we see it as a tradeoff as effectively.
A premise of the TIN submit is that ctid values are a universally good selection for postings lists. The drawback with that is that nothing compresses higher than dense, sorted, distinctive integers. Consequently, most search programs use u32 DocId values. Switching to a 48-bit doc identifier just isn’t inherently extra environment friendly, particularly because the 48 bits in a ctid are the concatenation of numbers from two completely different domains (block numbers, numbered within the thousands and thousands, and tuple offsets, at most 291).
Dense u32 DocId values have one other benefit: they make it simple to attach postings to columnar storage. Postings let you know which paperwork match; columns allow you to effectively entry these paperwork’ metadata attributes (like numeric values or class labels).
Column shops are sometimes related to OLAP databases, however they’re additionally essential for search queries:
- Top K by field: “Give me merchandise matching my question, ordered by worth.”
- Range filters: “Give me matching merchandise between $50 and $100.”
- Faceting: “Give me the highest 10 merchandise, and side by the variety of matches in every class.”
All of those search queries require a columnar format. With DocId values, the connection is easy. Within a section, doc 42 corresponds to row 42 in every column. Once a postings checklist offers us that ID, we are able to lookup its columnar worth instantly.
A ctid doesn’t give us that column place. It identifies a bodily Postgres location, similar to web page 190, slot 17. To retrieve that doc’s worth from a column, we first want to find out which column row corresponds to (190, 17). That requires a mapping or an equal lookup.
TIN is nice at BM25 scoring and doc counting, however that’s simply the tip of what makes up a search engine like Elasticsearch. For the “remainder of search”, you want a columnar illustration. If TIN decides to make one, we suspect they’ll should pay the identical ctid/DocId translation price (however within the reverse route).
Why Tantivy Remains the Right Choice for Us
TIN’s submit attributes its efficiency benefit to utilizing ctid values as a substitute of DocId values. But we closed the hole with out altering our doc identifiers by:
- Writing denser information constructions with higher locality.
- Using a Blockmax algorithm with increased throughput.
- A number of different small optimizations associated to lazy studying of different items of knowledge.
Most of those modifications occurred in Tantivy, our search library.
Over the previous few years we’ve typically debated whether or not constructing on Tantivy was the suitable selection for ParadeDB, versus what seems to be the TIN method of writing a brand new search engine from scratch. This investigation has bolstered our perception in our determination to make use of Tantivy. It brings over a decade of improvement, battle testing by a number of the globe’s largest firms, and noteworthy velocity. Tantivy doesn’t all the time mesh completely with Postgres’ block structure proper off the shelf, however its extensibility and options greater than make up for that.
You would possibly ask: why hadn’t we accomplished these optimizations already? Performance work by no means ends, and our engineering sources are finite. After reaching Elasticsearch parity on our textual content search benchmarks, we shifted our consideration towards increasing ParadeDB’s capabilities past “simply textual content search” into effectively executing search queries that contain complicated filters, sides, and joins. By now our search API could be very broad, so we’re glad that TIN introduced our consideration to this chance for core optimization.
Closing Thoughts
The open supply search group has a longstanding custom of collaboration. For occasion, regardless of being aggressive search libraries, Lucene and Tantivy incessantly share concepts and benchmark in opposition to one another in a pleasant approach. We explored how this advantages each initiatives in our conversation with Paul Masurel, creator of Tantivy. We hope this turns into one other instance of this. It’s been a enjoyable dash for us.
We admire that the TIN authors shared a few of their engineering selections of their weblog, even when the venture itself isn’t open supply. All of our work is open, and we’ve already begun to upstream the related enhancements from this spike to Tantivy.
We’ve minimize a 0.26.0-rc.2 launch candidate so these outcomes are reproducible. For current ParadeDB customers, these enhancements can be folded into the subsequent secure launch, 0.26.0, focused for subsequent week. We’ve made positive that these modifications are backwards appropriate, though a reindex can be required to inherit all of the optimizations.
To our group contributors: this investigation was time-boxed, and we predict there are numerous extra optimization strings to tug on (particularly within the route of extra environment friendly Blockmax pruning and lowering buffer entry). We welcome any contributions that push the efficiency frontier additional.
In the subsequent half we’ll talk about the optimizations we made round our COUNT efficiency (trace: in addition they didn’t require altering our doc identifiers). Until then, glad looking out! We’re excited to ship quicker textual content queries to the Postgres and search communities.
