ParadeDB pg_search 0.26 cuts ten-term BM25 queries 4.3x but slows single-term search

A storage rewrite, not a feature release

ParadeDB shipped pg_search 0.26.0 on 3 October, and the changelog frames it as a change to how the extension stores the per-document field-length figures that BM25 scoring depends on. Those numbers, called fieldnorms, previously lived in one shared array; in 0.26 they sit inside the postings list of each term. A hands-on benchmark published on dev.to set out to measure what that rewrite buys and what it costs, and found the answer runs in both directions.

pg_search is a Postgres extension that adds a BM25 full-text index type (USING bm25), so relevance-ranked search can run inside Postgres alongside ordinary btree indexes.

How it was tested

The author ran paradedb/paradedb:0.25.11-pg17 and 0.26.0-pg17 in separate containers, loaded an identical 3-million-row table into each, built a default bm25 index on a synthetic title column in both, and backed every timing with EXPLAIN (ANALYZE, BUFFERS). The title column drew from a vocabulary of roughly 260 words with a Zipfian frequency distribution, so some terms appear in 84% of rows and others in about 10%.

Where it wins

A query that OR-combines ten terms and returns the top ten by BM25 score — the ranked-disjunction shape ParadeDB's own benchmarking targets — dropped from about 129ms on 0.25.11 to about 29ms on 0.26.0, a 4.3x improvement that held across repeated runs rather than appearing once.

The plan output explains the gain. According to the dev.to analysis, the old version executed three separate scored sub-queries and merged them (shown as Queries: 3 in the plan), while the new version runs a single query, with fieldnorms sitting next to the postings instead of in a structure the executor has to visit three times.

Where it loses

A single-term query for the top ten told the opposite story: roughly 1.4ms on 0.25.11 versus roughly 8ms on 0.26.0, about five times slower, consistently, on a term present in 19% of rows. A rarer term covering about 10% of rows showed the same pattern at smaller absolute numbers: 1.5 to 1.6ms versus 4.8 to 5.7ms.

Buffer accounting confirms the cause: 496 buffer hits on 0.26.0 against 132 on the previous version, with 382 of the new hits attributed specifically to field norms. Carrying field-length data next to every term's postings adds cost without benefit when only one term is involved, and provides exactly the locality that pays off when ten terms collapse into one scored pass. The same design decision produces both numbers.

Where the trade flips

The author mapped the crossover using the fastest of three runs per configuration: one term 1.34ms versus 7.70ms, two terms 8.10ms versus 16.07ms, four terms 20.40ms versus 14.83ms, and ten terms 127.5ms versus 29.4ms. Somewhere between two and four OR-terms the new release stops losing and starts winning, and the gap widens quickly beyond that. A four-term AND query landed within sample noise (8.7 to 10.7ms versus 9.5 to 11.4ms), consistent with the optimisation targeting disjunctions rather than conjunctions.

Disk cost and rough edges

The index barely grew: 101,138,432 bytes against 100,917,248, about 216 KiB or 0.2% on a roughly 96MB index, so the layout is not storing a naive copy of the fieldnorm array per term.

Two operational quirks surfaced. Unknown keys inside text_fields are accepted silently — both a nonsense option name and a guessed 'pnorms' flag created indexes without any error — even though malformed JSON and an invalid tokenizer name fail cleanly. That means a mistyped or outdated option name will not warn you. The extension also still limits a table to one pg_search index, and the long-standing key_field option is now a documented no-op that emits a deprecation warning as of 0.26.0.

The most practical addition, in the author's assessment, is the new per-category buffer breakdown in EXPLAIN (ANALYZE, BUFFERS), which lets you see on a live system whether a given query is paying the fieldnorm overhead or benefiting from the single-pass disjunction path.

Why it matters

Whether 0.26.0 is a clear upgrade depends almost entirely on query shape. Applications issuing broad, multi-term search queries — the typical search-box pattern — gain a large, repeatable speedup. Workloads built on short queries, such as single-keyword lookups or autocomplete-style searches, take a real and consistent regression, roughly fivefold in the measured case. Teams should profile their own query mix with the new buffer instrumentation before upgrading, and the silent acceptance of unknown field options is worth flagging to anyone relying on index configuration validation to catch mistakes.