ParadeDB 0.26.4 adds Rank Join to run multi-table Postgres search fast

ParadeDB, a Postgres search extension, has published a post on what it calls search joins: relevance-ranked or faceted queries that span several normalized tables, such as users, posts and comments. The post argues that such queries have forced a bad choice. You can denormalize through a fragile ETL pipeline into Elasticsearch and get speed, or you can stay in Postgres and accept it slowly scoring and joining millions of rows to return a handful of results. ParadeDB says it spent the last few months making search joins a first-class feature, and claims that on its StackOverflow benchmark a multi-table search query is up to 1000x faster than vanilla Postgres, with no extra infrastructure. The headline example is a natural 3-table Top-K search that drops from 2 minutes to 100 ms.
The benchmark uses the StackOverflow data model: users (2.3M rows), posts (20M), comments (29M) and badges (29.4M). The query joins users, posts and comments, matches the term 'ocaml' with an OR across users.about_me, posts.title and comments.text, orders by the sum of the three relevance scores, and takes the top 20.
The post says this is hard for both alternatives. In Elasticsearch you would wade through nested field types, enrich processors, terms lookup queries, runtime fields, LOOKUP JOIN (with its 10,000-row batch circuit breaker), client-side _update_by_query and join fields (only one per index), and still have to denormalize users or badges into every post and comment. Power-law skew makes write and space amplification worse than average row sizes suggest, you write to two databases, and the denormalized copy takes up to 3x the storage after replication.
In vanilla Postgres, on a B-Tree + GIN schema with 8-way parallelism, the same query takes about 2 minutes (118,000 ms). The cause is the OR across tables: no table is pre-ordered by the combined score, and a comment without the term may still match through its post or user, so Postgres cannot filter before joining. It has to join all 28.5M rows, sum the scores for every triple, then sort and take 20. Its row-at-a-time executor also fetches every column that might be needed from the heap, even for rows the LIMIT will discard. For aggregates, it lacks vectorization, and covering B-Tree indexes can help but hurt write throughput and cannot approach a column store. A hand-written rewrite as an 84-line UNION ALL of per-table queries in a CTE cuts the time to 1.2s for a rare term, but it is more code to maintain and balloons to 14s for a common term.
ParadeDB's answer is a separate executor. Its index combines columnar storage with BM25 and vector search and stores join keys and projected columns inside the index. After recognizing a search join, ParadeDB uses Postgres planner hooks to intercept the plan and push the whole multi-table JOIN and LIMIT / GROUP BY (and DISTINCT, OVER, FILTER and so on) to a columnar executor built with Apache DataFusion, which it calls one of the fastest OLAP query engines available. Non-search queries keep using Postgres's standard planner and executor, and for the queries it accelerates, ParadeDB says it property-tests for equivalence to Postgres. It extended DataFusion so that only compact internal document IDs and join keys flow through joins, with column fetches deferred (late materialization), and so that MVCC visibility checks wait until joins have narrowed the rows, checking the in-memory Visibility Map before touching the heap.
For Top-K, the key piece is the Rank Join, new in 0.26.4. Instead of joining everything and sorting at the end, it streams each input in descending score order (sorted access) and builds candidate results as it goes. The lowest score seen in a stream is its floor: if the users stream has reached 4.0, no unseen user can score above 4.0, so a post scoring 6.0 whose author has not been seen can total at most 10.0. Once no partial or unseen pair can beat the best complete pair, that pair can be emitted, and the executor stops early when no remaining combination can beat the current top 20. The basic form struggles when scores fall slowly or when a high-scoring post was written by a user who did not match at all (score 0.0), because thousands of rows must be pulled to push the floor low enough. The fix, drawn from further research, adds random access: the best partial candidates trigger direct lookups into the other table's index by join key to fetch the partner's exact score, which can be 0.0. In the post's small example this resolves the (u2, p2) pair at 6.0 and lets (u1, p1) at 9.0 be emitted while the streams are still near the top.
ParadeDB implemented this as a streaming binary join operator in DataFusion, so downstream operators can pull tuples until a LIMIT is satisfied or feed a second stacked Rank Join for the third table. Projected columns such as users.display_name and posts.title are materialized only for the final 20 rows. In 0.26.4 the 3-table OR Top-K query runs in 100-600 ms on a single core across all selectivity levels, which the post puts at 12x-50x faster than the 84-line UNION ALL rewrite and within range of a fully denormalized dataset. The Postgres plans in the chart use up to 8 workers, while the Rank Join runs on a single core. ParadeDB says future versions will parallelize and co-partition the operator across workers for lower runtimes. The post also promises a second half on joining and grouping into facets, using tuple tagging, index co-partitioning and late materialization, but its aggregate numbers are not part of this account.
Key facts
- ParadeDB's StackOverflow benchmark: a natural 3-table Top-K search drops from about 2 minutes (118,000 ms) on vanilla Postgres to 100 ms on a single core; the post claims up to 1000x for multi-table search queries.
- The speedup comes from a new Rank Join operator in ParadeDB 0.26.4, running inside a DataFusion-based columnar executor that takes over search joins via Postgres planner hooks.
- The Rank Join streams inputs by descending score and stops once no remaining combination can beat the current top 20, so it avoids joining all 28.5M rows first.
- The 84-line UNION ALL rewrite on vanilla Postgres takes 1.2s for a rare term and 14s for a common one; ParadeDB's 100-600 ms query is put at 12x-50x faster.
- Rank Join currently runs on a single core; parallelizing it across workers is planned for future versions, with no date given.
Why it matters
Search across normalized tables is a common need: a search box that must match users, posts and comments at once. The post says teams have either copied data into Elasticsearch through ETL, with extra storage (up to 3x after replication) and double writes, or lived with slow Postgres queries. If ParadeDB's numbers hold up for other workloads, staying with a normalized schema and one database becomes a more realistic option. The new piece in this release is the Rank Join, which lets a ranked query stop early instead of joining every row first.
Who it affects
Teams that run Postgres and need relevance-ranked search or facets over data split across several tables, especially those now maintaining a denormalized Elasticsearch copy or hand-written UNION ALL rewrites. It also touches ParadeDB users, since the Rank Join arrives in 0.26.4. Non-search queries are said to keep using Postgres's standard planner and executor untouched.
How to use it
The post's point is that you write the natural SQL: join users, posts and comments, put the search conditions in an OR in the WHERE clause, order by the sum of pdb.score() values and add a LIMIT. The example uses the ||| match operator on each searched column. ParadeDB intercepts the plan and handles it with the Rank Join, with no rewrite and no extra infrastructure, according to the post. It requires ParadeDB 0.26.4 or later for this operator. Pricing, licensing and availability terms are not given.
How solid is it
The numbers come from ParadeDB's own StackOverflow benchmark, published in its own blog post, and the post mentions no independent verification. The 1000x figure is an 'up to' upper bound, and the post does not say which query it applies to beyond the 2 minutes to 100 ms example. Benchmark hardware, Postgres configuration beyond 8-way parallelism and the dataset version are not given. The comparison is also uneven in parallelism: the Postgres plans use up to 8 workers while Rank Join runs on one core. ParadeDB says it property-tests accelerated queries for equivalence to Postgres.
Risks and caveats
The measured case is a single query shape on one dataset: a 3-table OR Top-K search. Facet and aggregate performance is only described as a mechanism in the visible part of the post, with no numbers. The Rank Join is single-core today, and parallel execution is promised without a date. Results of 100-600 ms across selectivity levels are a range, not a fixed 100 ms. The claim of being within range of a fully denormalized dataset is the vendor's own statement.
“We've spent the last few months making search joins a first-class experience in ParadeDB”
— ParadeDB blog post