ParadeDB shipped pg_search 0.26.0 on 3 October, and the changelog describes it as a rewrite of how the extension stores BM25 field-length data for scoring. The company's own framing is that search got faster. I wanted to know faster at what, and slower at what, because no rewrite is free.
pg_search is a Postgres extension that adds a BM25 full-text index (USING bm25) alongside the normal btree world, so you can run relevance-ranked search without leaving Postgres. The 0.26 release moves "fieldnorms" — the per-document field-length numbers BM25 needs for scoring — from one shared array into the postings list of each term. I ran both the previous stable release (0.25.11) and 0.26.0 against the same data and the same queries to see what that move actually costs and buys.
The setup
I pulled paradedb/paradedb:0.25.11-pg17 and paradedb/paradedb:0.26.0-pg17, ran one container per version, and loaded an identical 3 million row table into each: a synthetic title column built from a vocabulary of about 260 words with a Zipfian frequency distribution, so some terms ("index") appear in 84% of rows and others ("udp") in about 10%. I built a default bm25 index on title in both, with no custom options, timed with \timing on in psql, and backed every number with EXPLAIN (ANALYZE, BUFFERS).
The headline number
A query that OR-combines ten terms and asks for the top 10 by BM25 score is the shape ParadeDB's own benchmarking targets — a disjunction over many terms, ranked, with a LIMIT:
SELECT id, title, paradedb.score(id) AS score
FROM posts
WHERE title @@@ 'index OR which OR to OR price OR an OR ssl OR transaction OR tcp OR api OR kernel'
ORDER BY score DESC
LIMIT 10;
| Version | Run 1 | Run 2 | Run 3 |
|---|---|---|---|
| 0.25.11 | 130.3ms | 127.5ms | 129.2ms |
| 0.26.0 | 31.5ms | 29.5ms | 29.4ms |
That's a 4.3x drop, and it held across repeats, not a one-off. EXPLAIN (ANALYZE, BUFFERS) shows why. On 0.25.11 the plan lists Queries: 3 under the custom scan node — pg_search is running three separate scored sub-queries and merging them. On 0.26.0 the same SQL produces Queries: 1, plus a buffer breakdown that didn't exist in the old plan:
Buffer Hits:
Total: 645
Columnar Fields: 29
Field Norms: 190
Heap: 7
Postings: 407
Term Dictionary: 12
Collapsing three internal queries into one, with fieldnorms now sitting next to the postings instead of in a separate structure the planner has to visit three times, is where the 4.3x comes from — not a cache warm-up difference or a fluke of my particular table.
The number that argues against it
Before I ran anything else, my working assumption was that a release billed as "faster BM25" couldn't make a query slower, at worst it would be a wash. A single-term query, no disjunction, same LIMIT 10, broke that assumption straight away:
SELECT id, title, paradedb.score(id) AS score
FROM posts WHERE title @@@ 'kernel'
ORDER BY score DESC LIMIT 10;
| Version | Run 1 | Run 2 | Run 3 |
|---|---|---|---|
| 0.25.11 | 1.56ms | 1.34ms | 1.36ms |
| 0.26.0 | 8.06ms | 7.70ms | 8.55ms |
That's roughly five times slower, consistently, on a term that appears in 19% of rows. A rarer term ("udp", about 10% of rows) showed the same pattern at smaller absolute numbers: 1.5–1.6ms on 0.25.11, 4.8–5.7ms on 0.26.0.
My first reaction was that I'd broken the harness — maybe the second container's cache was cold, maybe I'd mistyped the query. I reran it five times in a row on each version and got the same split every time, then pulled EXPLAIN (ANALYZE, BUFFERS) to check before concluding anything:
-- 0.25.11, single term 'kernel'
Buffers: shared hit=132
-- 0.26.0, single term 'kernel'
Buffer Hits:
Total: 496
Columnar Fields: 6
Field Norms: 382
Heap: 9
Metadata: 16
Postings: 71
Term Dictionary: 12
496 buffer hits against 132, and 382 of those are fieldnorms specifically. Moving fieldnorms next to postings means every term's postings list now carries its own copy of the field-length data, rather than all terms sharing one compact array. For one term that's pure overhead. For ten terms combined into one scored pass, it's exactly the locality that avoids the three-query split above. The same design decision explains both numbers.
Where the crossover sits
I tried 2 and 4 terms to see where the trade flips:
| Terms (OR) | 0.25.11 (fastest of 3) | 0.26.0 (fastest of 3) |
|---|---|---|
| 1 | 1.34ms | 7.70ms |
| 2 | 8.10ms | 16.07ms |
| 4 | 20.40ms | 14.83ms |
| 10 | 127.5ms | 29.4ms |
Somewhere between 2 and 4 OR-terms, 0.26.0 stops being the slower option and starts pulling ahead, and the gap widens fast after that. A query with one or two search terms gets no benefit from this release and pays a real cost; a query with four or more gets a win that keeps growing.
I also checked a 4-term AND query (conjunction, not disjunction), since the optimisation is specifically about disjunction pruning: 8.7–10.7ms on 0.25.11 versus 9.5–11.4ms on 0.26.0, a small regression within the noise of my three-run samples. AND queries are not what this release targets, and the numbers show that plainly.
What it costs on disk
Fieldnorms duplicated into every term's postings sounded like it should bloat the index. It does not, at least at this scale:
SELECT pg_total_relation_size('search_idx');
-- 0.25.11: 100,917,248 bytes
-- 0.26.0: 101,138,432 bytes
That's 216 KiB more on a 96MB index, about 0.2%. Whatever the new on-disk layout does, it is not storing a naive duplicate of the fieldnorm array per term.
What it refuses
I tried to find the exact config flag to control the new field-length storage, since the project's own PR notes mention reindexing and a field option to opt into full scoring benefits. I couldn't locate the flag's name in the documentation pages I could reach, so I tested how pg_search handles configuration I'm not sure is real:
CREATE INDEX idx ON posts_small USING bm25 (id, title)
WITH (text_fields='{"title": {"pnorms": true}}');
-- CREATE INDEX (no error, no visible effect)
CREATE INDEX idx ON posts_small USING bm25 (id, title)
WITH (text_fields='{"title": {"not_a_real_option_xyz": true}}');
-- CREATE INDEX (also no error)
Both succeeded silently. That's not a loophole in strictness generally — malformed JSON and an invalid tokenizer name both get caught cleanly:
ERROR: failed to deserialize field config: Error("key must be a string", line: 1, column: 2)
ERROR: field config should be valid for SearchFieldConfig::title: unknown tokenizer type: not_a_real_tokenizer
So the extension validates known, strongly-typed fields like tokenizer, but an unrecognised key inside text_fields is dropped without complaint. If the real option name for the new behaviour is spelled differently from what you guessed, or from what an older doc page says, you will not find out from an error message. I also found that a table is capped at one pg_search index at a time (ERROR: a relation may only have one ParadeDB index), and that the long-standing key_field option is now a documented no-op: WARNING: key_field is deprecated as of 0.26.0 and is a no-op.
How I'd watch this in production
The buffer breakdown in EXPLAIN (ANALYZE, BUFFERS) is new in 0.26 and is the most directly useful thing I found for diagnosing this trade-off on a live system. Running it against your own slow query tells you whether you're paying the fieldnorm tax on a narrow query or collecting the disjunction discount on a broad one, without needing to guess from timing alone.
Under load
I ran both query shapes through pgbench at 10 concurrent clients for 10 seconds against each version:
| Query | 0.25.11 tps | 0.26.0 tps | 0.25.11 avg latency | 0.26.0 avg latency |
|---|---|---|---|---|
| 10-term OR | 14.3 | 51.6 | 698.5ms | 193.8ms |
| single term | 2658.8 | 477.3 | 3.76ms | 20.95ms |
Concurrency doesn't change the direction of either effect, it amplifies both. The disjunction win holds up (3.6x more throughput), and the single-term regression gets worse under contention (5.6x less throughput) than it looked in a single connection.
What I got wrong on the way
My first attempt at the data generator used plain random.paretovariate for word selection, and I didn't look closely at what came out until after it had already written 3 million rows to disk. A lot of the titles read like "Account alert and account account account" — one word crowding out everything else in the same line. That matters for BM25 specifically, since the score leans on how often a term shows up inside one document, and a title where the same word appears five times isn't something a real search box would ever send me. I threw the file away, changed the generator so each title only uses a word once, checked a sample by eye, and only then reloaded both databases and started timing anything.
Run it yourself
docker run -d --name pdb_old -e POSTGRES_PASSWORD=pw -p 55432:5432 paradedb/paradedb:0.25.11-pg17
docker run -d --name pdb_new -e POSTGRES_PASSWORD=pw -p 55433:5432 paradedb/paradedb:0.26.0-pg17
sleep 8
Generate a comparable dataset and load it into both:
# gen_data.py
import random
import numpy as np
random.seed(42); np.random.seed(42)
VOCAB = np.array(sorted(set("""the a an of to in and is for on with that this it
database postgres index query search performance api cache kernel transaction
tcp udp ssl shard worker storage container cluster token startup price""".split())))
random.shuffle(VOCAB)
N = len(VOCAB)
weights = 1.0 / (np.arange(1, N + 1) ** 1.05)
weights /= weights.sum()
with open("posts.csv", "w") as f:
for i in range(1, 500_001):
length = random.choice([4, 6, 8, 10])
words, seen = [], set()
while len(words) < length:
w = np.random.choice(VOCAB, p=weights)
if w not in seen:
seen.add(w); words.append(w)
title = " ".join(words)
f.write(f"{i},{title[0].upper() + title[1:]},{random.randint(1, 5000)}\n")
python3 gen_data.py
docker cp posts.csv pdb_old:/tmp/posts.csv
docker cp posts.csv pdb_new:/tmp/posts.csv
for c in pdb_old pdb_new; do
docker exec $c psql -U postgres -c "CREATE TABLE posts (id bigint PRIMARY KEY, title text NOT NULL, points int);"
docker exec $c psql -U postgres -c "\COPY posts FROM '/tmp/posts.csv' WITH (FORMAT csv);"
docker exec $c psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pg_search;"
docker exec $c psql -U postgres -c "CREATE INDEX search_idx ON posts USING bm25 (id, title) WITH (key_field='id');"
done
# key_field is required on 0.25.11 and a deprecated no-op on 0.26.0, so this works on both
Then compare, with \timing on:
SELECT id, title, paradedb.score(id) AS score FROM posts
WHERE title @@@ 'index OR which OR to OR price OR an OR ssl OR transaction OR tcp OR api OR kernel'
ORDER BY score DESC LIMIT 10;
SELECT id, title, paradedb.score(id) AS score FROM posts
WHERE title @@@ 'kernel'
ORDER BY score DESC LIMIT 10;
Run each three times against pdb_old on port 55432 and pdb_new on port 55433, and compare the Time: lines.
What matters is which category your own queries fall into, and the only way to know is to check. If your search boxes mostly take one or two words, this upgrade is a wash at best for you, going by what I measured — the crossover in my numbers sits around three to four OR'd terms. Faceted search, tag filters, anything that OR's together a handful of values, is a different situation, and that's where the gain showed up every single time I tried it. Run EXPLAIN (ANALYZE, BUFFERS) on whatever query is actually slow for you, on both versions, and look at the Field Norms number before deciding which side of that line you're on.
Top comments (0)