ParadeDB 0.20.0: Simpler and Faster
Every developer building modern applications faces the same dilemma: your users want real-time search and analytics, but your database architecture forces you to choose between consistency and performance. The story often begins with "just a simple search box," but inevitably evolves into a complex distributed architecture with eventual consistency, operational overhead, and the constant fear that your search results aren't reflecting reality.
When Ming and I started ParadeDB, we had a simple vision: developers shouldn't have to choose between the safety of ACID transactions and the power of modern search. Today, with ParadeDB 0.20.0 we are making that reality more performant and easier to use.
ParadeDB 0.20.0 delivers on three fronts that matter for real applications: search aggregations that eliminate the analytics gap, a cleaner API that reduces cognitive overhead, and write performance that scales with your application instead of against it.
Search Aggregations: Analytics Without the Architecture
Modern applications often require more than just search: they need facets, counts, histograms, and other analytics on their search results. While dedicated search engines excel at aggregations, when you're building on a transactional database you often need to do a bit of gymnastics with the query planner to get things working without visiting your indexes twice.
We have had fast aggregations on top of search queries for a while now, but it's not something we have talked about a lot. With 0.20.0 we have overhauled this feature, making search faceting (calculating counts for groups of records) a first-class citizen.
Our search analytics are powered by a new pdb.agg() function can be used in two ways:
- as an aggregate function, running a search query and providing aggregate results
- as a window function, running a search query and providing aggregate results alongside the TopN search result
Both variants take control of parts of query planning and execution, pushing as much work as possible down into the ParadeDB index. This removes the need for multiple queries, CTEs, or manual aggregations.
Our aggregations excel for large search result sets (millions of records), with search faceting performing an order of magnitude faster than the next best method (a ParadeDB search returning all results, feeding into a PostgreSQL aggregate) over more than 40 million results (full details to come in a search aggregations post).
Faceting (search and aggregation) query time for 42 million results
pdb.agg() can also be used with a JSON argument that closely mirrors the Elasticsearch aggregations API, and in many cases is mapped to plain SQL constructs (like COUNT(*)).
Let's have a look at some examples.
The most basic call doesn't use pdb.agg() at all, it just uses a COUNT(*) which is caught and planned using a search aggregation.
SELECT count(*)
FROM mock_items
WHERE description ||| 'shoes';
count
-------
3
(1 row)
This can also be done with a window function, which adds an extra column containing the count for the whole result set (not just the returned rows).
SELECT id, description,
count(*) OVER (),
FROM mock_items
WHERE description ||| 'shoes'
ORDER BY rating DESC
LIMIT 2;
id | description | count
----+---------------------+-------
3 | Sleek running shoes | 3
5 | Generic shoes | 3
(2 rows)
A more complex example uses the JSON API to express a terms facet over a TopN query, which is then also added as an extra JSON column (this data is duplicated per row, we may optimize this in the future). This is useful when you want to display the TopN search results, but also show the distribution of the whole search query, doing this in one efficient index pass is one of the niceties of the Elasticsearch API that we loved and made possible in SQL.
SELECT id, description,
pdb.agg('{"terms": {"field": "rating"}}') AS facets
FROM mock_items
WHERE description ||| 'shoes'
ORDER BY rating DESC
LIMIT 2;
id | description | facets
----+---------------------+---------------------------------------------
3 | Sleek running shoes | {"buckets": [{"key": 4, "doc_count": 1},
{"key": 5, "doc_count": 1},
{"key": 3, "doc_count": 1} ]}
3 | Generic shoes | {"buckets": [{"key": 4, "doc_count": 1},
{"key": 5, "doc_count": 1},
{"key": 3, "doc_count": 1} ]}
(2 rows)
Behind the scenes, these queries use our BM25 indexes for both full-text search and columnar analytics. You get sub-second facet calculations across millions of documents, all while maintaining perfect consistency with your transactional data.
v2 API: Less Magic, More Clarity
None of these aggregation capabilities would matter if they were difficult to use, so alongside the our performance work we've also promoted our V2 API to the default experience, and the difference is immediately apparent with a much more intuitive SQL UX.
One of the most significant advantages of the v2 API is that it eliminates the schema duplication that is present in most other search systems. With Elasticsearch you must define your data structure twice: once in your application database and again in your Elasticsearch mapping:
// Elasticsearch mapping - separate from your database schema
{
"mappings": {
"properties": {
"id": { "type": "integer" },
"description": { "type": "text", "analyzer": "english" },
"category": { "type": "keyword" }
}
}
}
Even our own v1 API required separate JSON configuration for the indexes. The v2 API leans into our definition of 'search database' and infers most of your index schema from your PostgreSQL table schema. You define text processing and search behavior directly in the index creation statement, eliminating the need to maintain parallel schema definitions.
We've had many users see unexpected behaviors in their first few queries due to incorrectly configured tokenizers. In v0.20.0, we've revamped our tokenizer defaults and made them a first-party experience when creating indexes, removing any ambiguity. Index creation is now cleaner, specifying the tokenizers and options inline:
CREATE INDEX search_idx ON mock_items
USING bm25 (id,
title,
(description::pdb.simple('stemmer=english',
'stopwords_language=english')),
category::pdb.literal)
WITH (key_field='id');
And search has improved too, with new conjunction (&&&) and disjunction (|||) operators, and simple ways of accessing common features like boosting.
SELECT id, pdb.score(id), description, category
FROM mock_items
WHERE description ||| 'shoes'::pdb.boost(2) OR category ||| 'footwear'
ORDER BY score DESC
LIMIT 5;
id | score | description | category
----+-----------+----------------------+----------
5 | 7.660918 | Generic shoes | Footwear
3 | 6.872712 | Sleek running shoes | Footwear
4 | 6.872712 | White jogging shoes | Footwear
13 | 1.9362776 | Sturdy hiking boots | Footwear
23 | 1.9362776 | Comfortable slippers | Footwear
(5 rows)
Keep an eye out for another post showing off all the new v2 API features. For now, you can explore the full API reference in our documentation.
Write Performance: Updates That Scale With You
Our performance story from 0.19.0 to 0.20.0 reveals a truth about how we think search should work in transactional systems. Traditional search engines treat updates as expensive operations that require careful batching and scheduled maintenance windows. At first glance this might seem reasonable, because search indexes are complex data structures that need time to reorganize.
The underlying issue stems from how search engines like Lucene and Tantivy handle data. These systems use immutable data structures for their segments, which means updating a single field in a document necessitates rewriting all other fields in that segment, creating write amplification.
But that's exactly backwards for real applications. Most search workloads aren't bulk imports; they're streams of individual updates as users create content, modify records, and interact with your system. ParadeDB treats these small, frequent updates as first-class citizens that should perform as well as inserts.
Our solution is based on two key optimizations from 0.19.0, now enabled by default in our LSM engine: mutable segments and background merging.
- Mutable segments introduce a small buffer optimized specifically for writes. Instead of immediately writing changes to immutable segments, we store a pointer to the new row in this buffer, eliminating the write amplification problem entirely. Once the buffer becomes full, background merging serializes it to an immutable structure without blocking ongoing write operations.
- Background merging moves all merging (including mutable segment merging) to the background, and increases the number of workers which we dedicate to this task.
Since we first introduced these features in 0.19.0 we've increased single row update performance by more than two orders of magnitude; the kind of improvement that can change how you think about transactional search.
Updates Per Second on a Wide Table with a BM25 index
Again, you can expect a follow up post with all the gory data-structure details.
Why This Matters
Version 0.20.0 strengthens ParadeDB as an alternative to Elasticsearch by adding fast search aggregations, a streamlined v2 API, and significantly improved write performance directly into PostgreSQL. For teams that value transactional consistency and operational simplicity, we provide a path to modern search capabilities without the complexity of managing separate systems.
Ready to see search aggregations and the new API in action? Try ParadeDB 0.20.0 and experience what it's like when search, analytics, and transactions work together seamlessly.