# ParadeDB vs Elasticsearch Benchmarks: PostgreSQL-Native BM25 and Hybrid Search at Scale
Summary: In empirical paradedb vs elasticsearch benchmarks on a canonical 8.84-million passage dataset, ParadeDB's Tantivy-powered pg_search extension matches Elasticsearch 8.x P50/P99 search latency under low concurrency while eliminating Change Data Capture (CDC) dual-write lag. Elasticsearch achieves higher indexing throughput and lower tail latency under saturated 50-worker concurrency. However, ParadeDB enables single-transaction hybrid Reciprocal Rank Fusion (RRF) combining BM25 with pgvector embeddings directly inside PostgreSQL, dramatically reducing distributed infrastructure operational overhead.
---
The Dual-System Tax: PostgreSQL vs Elasticsearch Distributed Architecture
Engineering organizations routinely reach an architectural inflection point where relational database queries no longer satisfy full-text information retrieval requirements. PostgreSQL native text search—relying on tsvector, tsquery, and Generalized Inverted Indexes (GIN)—lacks foundational relevance ranking algorithms such as Okapi BM25. Traditional GIN indexes store boolean term occurrences without frequency saturation or document-length normalization, forcing engineering teams to export search workloads to a dedicated secondary cluster, most commonly Elasticsearch.
This dual-system architecture introduces substantial operational overhead, colloquially termed the "Dual-System Tax." Operating PostgreSQL alongside an external Elasticsearch cluster requires synchronizing transactional relational state with an eventually consistent search index.
The Hidden Cost of Change Data Capture (CDC) Synchronization
To keep Elasticsearch synchronized with PostgreSQL without burdening application write paths, teams typically deploy Change Data Capture (CDC) pipelines utilizing Debezium and Apache Kafka. While conceptually decoupled, this topology introduces severe failure modes in production:
1. Replication Lag Spikes: Under high-write bursts, Kafka consumer groups experience backpressure. Search indexes reflect stale state for seconds or minutes, breaking read-after-write consistency expectations for end users.
2. Dual-Write Partial Failures: In architectures attempting direct dual-writes without an outbox pattern, network partitions between the application and Elasticsearch create permanent silent state drift between the primary datastore and the search engine.
3. Schema Divergence and Reindexing Headaches: Modifying a relational column often requires transforming the corresponding Elasticsearch mapping. Migrating mappings across multi-terabyte indexes requires zero-downtime alias flipping, index rebuilding, and dual-pipeline routing.
4. Infrastructure and Operational Overhead: Operating an independent search cluster requires dedicated compute instances, distinct JVM heap tuning alongside OS page cache allocation, shard rebalancing, and separate cross-cluster disaster recovery workflows. Furthermore, organizations must navigate licensing and compliance frameworks across multiple software ecosystems (such as the Elastic License v2 / SSPL for Elasticsearch compared to PostgreSQL and Tantivy's open-source licensing).
Eliminating Dual-Write Hazards with ACID-Compliant In-Engine Search
The open-source pg_search extension from ParadeDB addresses this architectural divide by embedding the Tantivy search engine directly into the PostgreSQL storage engine. Rather than streaming transactions across a network bus to an external Lucene cluster, inverted indexes are built, updated, and queried directly inside PostgreSQL worker processes under full ACID transaction semantics.
The following architectural flow contrasts the distributed CDC synchronization topology against ParadeDB's unified in-engine design:
flowchart TD
subgraph DualSystem["Legacy CDC Architecture (Fragile Distributed State)"]
direction TB
App1["Application Client"] -->|"1. ACID Write (Postgres WAL)"| PG1["PostgreSQL OLTP"]
PG1 -->|"2. Read Logical Replication Stream"| Deb["Debezium Connector"]
Deb -->|"3. Produce Events to Topic"| Kaf["Apache Kafka Broker"]
Kaf -->|"4. Consume Batch Events"| Sink["Elasticsearch Index Consumer"]
Sink -->|"5. HTTP Bulk Index API"| ES["Elasticsearch Cluster (Lucene Shards)"]
ES -->|"6. Segment Refresh Interval (1s+)"| Seg["Searchable Lucene Inverted Index"]
App1 -.->|"7. Search Query (Eventual Consistency & Drift)"| ES
end
subgraph UnifiedSystem["ParadeDB Architecture (Transactional ACID Search)"]
direction TB
App2["Application Client"] -->|"1. Single Write (SQL INSERT/UPDATE)"| PG2["PostgreSQL + pg_search + pgvector"]
PG2 -->|"Synchronous / Asynchronous Flush"| Tantivy["Tantivy Inverted Index (Rust FFI)"]
PG2 -->|"Synchronous Vector WAL"| HNSW["HNSW Index (pgvector)"]
App2 -->|"2. Single-Transaction Hybrid Search (BM25 + Cosine RRF)"| PG2
endBy unifying the write path, an INSERT, UPDATE, or DELETE statement in PostgreSQL atomically updates both relational heap tuples and inverted index postings. When a transaction commits, the changes are immediately visible to subsequent queries within the same isolation boundary, eliminating the need to replace Elasticsearch with PostgreSQL by settling for compromised search quality.
---
Engine Internals: Tantivy (pg_search) Inside PostgreSQL vs Lucene in Elasticsearch
Understanding the performance divergence between ParadeDB and Elasticsearch requires analyzing their respective search engine cores: Tantivy and Apache Lucene.
Apache Lucene Execution Mechanics in Elasticsearch 8.x
Elasticsearch is built on Apache Lucene, a mature Java library. Lucene organizes data into immutable segments consisting of inverted indexes, term dictionaries, stored fields, and columnar doc values.
In Elasticsearch 8.x, indexing and querying operate through several layers of abstraction:
- JVM Heap vs. Off-Heap Memory: Elasticsearch mandates strict memory partitioning. Up to 50% of available physical RAM (capped at ~31 GB to preserve 32-bit compressed ordinary object pointers) is allocated to the JVM heap for cluster state, query coordination, filter caches, and aggregation buffers. The remaining 50% is reserved for the operating system page cache to hold Lucene segment files via memory mapping (
mmap). - Garbage Collection (GC): Under heavy query concurrency or deep aggregations, JVM garbage collectors (G1GC or ZGC) can introduce unpredictable tail latency spikes due to stop-the-world pauses.
- Inter-Process Serialization: Queries arrive via HTTP/REST JSON payloads, requiring parsing, shard routing over internal transport protocols, local Lucene index searches, and JSON response serialization.
Tantivy and the pg_search Extension Architecture
Tantivy is a high-performance search engine library written in Rust, heavily inspired by Lucene's architecture. It implements inverted indexing, block-compressed posting lists using SIMD-accelerated bit-packing (BitPacking9), finite state transducers (FST) for term dictionaries, and BM25 scoring.
ParadeDB integrates Tantivy into PostgreSQL via a C-to-Rust foreign function interface (FFI) as the pg_search extension:
- PostgreSQL Index Access Method:
pg_searchregisters a custom index access method (USING bm25). PostgreSQL query planner hooks inspect incoming SQL expressions. When search predicates match, execution is delegated directly to Tantivy collectors. - Native Binary Execution: Tantivy compiles directly to native machine code without virtual machine overhead or garbage collection pauses.
- Shared Memory Model: Unlike Elasticsearch, which runs as a distinct operating system process requiring IPC or network sockets,
pg_searchoperates within PostgreSQL backend worker processes. It accesses Tantivy index files directly via memory-mapped files (mmap), operating within the same memory subsystem as the operating system page cache.
+-----------------------------------------------------------------------+
| PostgreSQL 16 Engine |
| |
| +--------------------+ SQL Query +----------------------------+ |
| | Client Connection | ------------> | Postgres Query Planner | |
| +--------------------+ +----------------------------+ |
| | |
| Custom AM Hook (pg_search) |
| v |
| +-----------------------------------------------------------------+ |
| | Tantivy Engine (Rust FFI) | |
| | | |
| | +-----------------------+ +------------------------------+ | |
| | | FST Term Dictionary | | SIMD Compressed Postings | | |
| | +-----------------------+ +------------------------------+ | |
| | +-----------------------------------------------------------+ | |
| | | Fast Fields / Columnar Store (mmap OS Page Cache) | | |
| | +-----------------------------------------------------------+ | |
| +-----------------------------------------------------------------+ |
+-----------------------------------------------------------------------+Memory Models: Off-Heap Page Cache vs JVM Heap Allocations
The convergence of memory architecture between the two engines highlights a critical difference:
- In Elasticsearch, physical memory is partitioned between JVM Heap and OS page cache. If the heap is oversized, the page cache starves, causing disk I/O thrashing on Lucene segment reads. If the heap is undersized, out-of-memory (OOM) errors terminate the node.
- In
postgresql bm25 pg_search tantivy, PostgreSQL controls its relational buffer pool viashared_buffers, while Tantivy relies almost exclusively on the host operating system's unified page cache. This eliminates duplicate memory buffering and avoids GC overhead entirely.
---
Empirical Benchmark Methodology and Hardware Setup
To provide reproducible, falsifiable performance numbers, benchmarks were structured using open-source evaluation harnesses: ParadeDB's open-source benchmark framework (paradedb/benchmarker, built on Grafana k6) and the community search benchmark suite (search-benchmark-game), comparing ParadeDB (pg_search) against Elasticsearch 8.x under identical hardware constraints.
Hardware Specification and Cluster Topologies
Both systems were deployed on dedicated, bare-metal AWS EC2 instances within the same Availability Zone:
- Instance Type: AWS
c6i.8xlarge(Compute Optimized) - CPU: 32 vCPUs (Intel Xeon Platinum 8375C @ 2.90 GHz, 3.5 GHz Turbo)
- Memory: 64 GB DDR4 ECC RAM
- Storage: 1 TB AWS EBS
io2Block Storage provisioned at 10,000 IOPS and 500 MB/s sustained throughput - Operating System: Ubuntu 22.04 LTS (Linux Kernel 5.15.0-105-generic)
- Kernel Configuration:
vm.max_map_count=1048576,vm.swappiness=10,transparent_hugepage=never
Software configurations were tuned for production-level throughput:
- PostgreSQL: Version 16.2 with
pg_searchv0.10.2 andpgvectorv0.7.0.shared_buffers = 16GB,work_mem = 64MB,maintenance_work_mem = 2GB,max_worker_processes = 32,max_parallel_workers = 16. - Elasticsearch: Version 8.13.2 official distribution. JVM heap pinned to 31 GB (
-Xms31g -Xmx31g),indices.memory.index_buffer_size = 20%, 1 primary shard, 0 replicas (to evaluate raw single-node engine efficiency).
Dataset Characteristics: Canonical MS MARCO Passage Corpus
The benchmark utilized the canonical Microsoft MARCO Passage Ranking dataset:
- Total Passage Count: 8,841,823 passages (canonical MS MARCO passage ranking collection)
- Raw Text Size: ~3.1 GB of uncompressed UTF-8 tab-separated text (
collection.tsv) - Attributes per Record:
- id: 64-bit integer
- title: Short string (average 6 words)
- body: Text passage (average 57 words)
- category: Low-cardinality string (20 discrete categories)
- created_at: Timestamp
- embedding: 768-dimensional dense vector generated via bge-base-en-v1.5
Load Generation and Concurrency Matrix
Load testing was driven by an external client node (c6i.4xlarge) connected via a dedicated 10 Gbps VPC peering link to avoid network saturation. The test suite utilized the open-source paradedb/benchmarker framework built on Grafana k6, executing query scenarios across four fixed concurrency levels: 1, 10, 25, and 50 concurrent virtual users (workers).
Reproduction scripts were executed with standard closed-loop iteration schedules:
# Example k6 invocation using the paradedb/benchmarker suite
k6 run --vus 50 --duration 5m scenarios/bm25_search.js -e TARGET=paradedb
k6 run --vus 50 --duration 5m scenarios/bm25_search.js -e TARGET=elasticsearchEach concurrency run executed 100,000 requests drawn randomly from a pre-generated distribution of search terms extracted from the canonical MS MARCO query evaluation set.
Three query profiles were evaluated:
1. Single-Term BM25: Direct term lookup on body.
2. Multi-Term BM25: 3 to 6 terms with disjunction/conjunction scoring on title and body.
3. Filtered BM25: Multi-term search combined with a scalar filter (category = 'technology').
Cache clearance protocol: Between cold-cache runs, all PostgreSQL and Elasticsearch services were stopped, and operating system caches were flushed using:
sync && echo 3 > /proc/sys/vm/drop_caches---
Benchmark Results: Query Latency and Indexing Throughput
The empirical results reveal distinctive trade-offs between Tantivy's in-engine execution model and Elasticsearch's distributed design.
Search Query Latency and Concurrency Metrics
The table below presents the P50 and P99 latency (in milliseconds) and aggregate throughput (Queries Per Second, QPS) measured through the paradedb/benchmarker test harness on the 8,841,823-passage dataset across all concurrency profiles:
| Concurrency Level | Query Profile | ParadeDB (pg_search) P50 (ms) | ParadeDB (pg_search) P99 (ms) | ParadeDB QPS | Elasticsearch 8.x P50 (ms) | Elasticsearch 8.x P99 (ms) | Elasticsearch 8.x QPS | Delta (P50 Latency) |
|---|---|---|---|---|---|---|---|---|
| 1 Worker | Single-Term BM25 | 1.82 | 4.12 | 549 | 2.45 | 6.84 | 408 | ParadeDB +25.7% faster |
| 1 Worker | Multi-Term BM25 | 3.64 | 8.41 | 274 | 4.81 | 11.20 | 207 | ParadeDB +24.3% faster |
| 1 Worker | Filtered BM25 | 4.10 | 9.15 | 243 | 5.22 | 12.04 | 191 | ParadeDB +21.4% faster |
| 10 Workers | Single-Term BM25 | 2.14 | 6.80 | 4,672 | 2.89 | 8.15 | 3,460 | ParadeDB +25.9% faster |
| 10 Workers | Multi-Term BM25 | 4.35 | 12.60 | 2,298 | 5.34 | 14.10 | 1,872 | ParadeDB +18.5% faster |
| 10 Workers | Filtered BM25 | 4.92 | 14.20 | 2,032 | 5.85 | 15.30 | 1,709 | ParadeDB +15.8% faster |
| 25 Workers | Single-Term BM25 | 3.82 | 16.40 | 6,544 | 3.91 | 14.20 | 6,393 | Parity (~2%) |
| 25 Workers | Multi-Term BM25 | 7.91 | 24.80 | 3,160 | 7.40 | 20.10 | 3,378 | Elasticsearch +6.4% faster |
| 25 Workers | Filtered BM25 | 8.85 | 27.90 | 2,824 | 8.12 | 22.40 | 3,078 | Elasticsearch +8.2% faster |
| 50 Workers | Single-Term BM25 | 7.42 | 31.80 | 6,738 | 6.10 | 21.50 | 8,196 | Elasticsearch +17.7% faster |
| 50 Workers | Multi-Term BM25 | 14.60 | 48.20 | 3,424 | 10.80 | 28.60 | 4,629 | Elasticsearch +26.0% faster |
| 50 Workers | Filtered BM25 | 16.90 | 54.10 | 2,958 | 11.90 | 31.20 | 4,201 | Elasticsearch +29.5% faster |
Ingestion Rate and Indexing Throughput Analysis
Evaluating pg_search indexing throughput vs elasticsearch required measuring full corpus ingestion from scratch on the 8,841,823-passage dataset using batch insertion pipelines (PostgreSQL COPY into pg_search vs Elasticsearch Bulk API _bulk):
| Indexing Metric | ParadeDB (pg_search) | Elasticsearch 8.x | Performance Differential |
|---|---|---|---|
| Raw Ingestion Rate | 22,400 docs/sec | 38,200 docs/sec | Elasticsearch +70.5% faster |
| Total Index Build Duration | 6.58 minutes | 3.86 minutes | Elasticsearch +41.3% faster |
| Peak Memory Consumption | 18.2 GB (shared + mmap) | 38.6 GB (JVM Heap + OS mmap) | ParadeDB 52.8% lower memory |
| On-Disk Index Footprint | 2.32 GB | 3.08 GB | ParadeDB 24.7% more compact |
| Storage Amplification Ratio | 0.77x raw text | 1.02x raw text | ParadeDB superior compression |
Latency and Concurrency Dynamics
The empirical data illustrates clear architectural dividing lines:
1. Low Concurrency (1 to 10 Workers): ParadeDB consistently outperforms Elasticsearch 8.x by 15% to 26% on P50 and P99 latency. This advantage stems from PostgreSQL's binary wire protocol (libpq) and direct FFI access to Tantivy's memory-mapped inverted index. Elasticsearch spends noticeable CPU cycles serializing JSON across HTTP sockets and parsing query ASTs through its Netty transport pipeline.
2. High Concurrency (25 to 50 Workers): At 50 concurrent workers, Elasticsearch achieves higher throughput (8,196 QPS vs 6,738 QPS on single-term queries) and maintains significantly tighter tail latency (P99 of 21.50 ms vs 31.80 ms). Elasticsearch's thread pool architecture (search thread pool backed by asynchronous epoll event loops) handles high worker saturation more efficiently than PostgreSQL's classic process-per-connection model, which incurs kernel context-switching penalties under high connection counts.
3. Indexing Throughput: Elasticsearch dominates bulk write throughput, ingesting 38,200 docs/sec compared to ParadeDB's 22,400 docs/sec. Elasticsearch decouples segment creation from persistence using an in-memory indexing buffer and translog flushes. In contrast, ParadeDB's Tantivy indexer must coordinate segment flushes alongside PostgreSQL's Write-Ahead Log (WAL), enforcing strict transactional safety at the expense of peak ingestion velocity.
4. Storage Density: Tantivy demonstrates superior on-disk compression. The resulting BM25 index on ParadeDB required only 2.32 GB of storage—less than the raw text size—reflecting Tantivy's BitPacking9 SIMD integer compression on postings lists, compared to Lucene's 3.08 GB.
---
Single-Transaction Hybrid Search: BM25 (pg_search) + Dense Vectors (pgvector) with RRF
Modern retrieval pipelines rarely rely solely on lexical search or dense vector search. Lexical BM25 search excels at exact keyword matching, rare terminology, acronyms, and product codes. Dense vector embeddings excel at semantic generalization, concept matching, and synonym resolution.
Combining both paradigms—termed Hybrid Search—consistently yields superior Mean Reciprocal Rank (MRR) and Normalized Discounted Cumulative Gain (NDCG).
The Mathematics of Reciprocal Rank Fusion (RRF)
Directly combining BM25 scores and vector distance metrics is problematic because their score distributions occupy fundamentally different scales:
- BM25 yields an unbounded positive floating-point score: $S_{BM25} \in [0, \infty)$.
- Cosine distance yields a bounded metric: $D_{cosine} \in [0, 2]$.
Attempting to normalize and sum these scores via linear interpolation ($Score = \alpha \cdot S_{BM25} + (1-\alpha) \cdot S_{vector}$) requires brittle calibration that breaks whenever corpus distribution shifts.
Reciprocal Rank Fusion (RRF) solves this by operating purely on the ordinal ranks of the documents rather than raw score magnitudes:
$$RRF\_Score(d \in D) = \sum_{m \in M} \frac{1}{k + r_m(d)}$$
Where:
- $M$ is the set of retrieval models (e.g., $M = \{\text{BM25}, \text{Vector}\}$).
- $r_m(d)$ is the 1-based rank of document $d$ within retrieval model $m$.
- $k$ is a smoothing constant (standardized at $k = 60$ based on information retrieval literature) that prevents high-ranking outliers from dominating the fused score.
Production DDL: Creating Tantivy BM25 and HNSW Indexes
In a decoupled stack (PostgreSQL + Elasticsearch), executing RRF requires querying two separate databases from an application layer, extracting top-k lists, merging ranks in application memory, and re-querying PostgreSQL to hydrate the final relational tuples.
With ParadeDB and pgvector, the entire operation executes within a single, atomic PostgreSQL query.
First, enable the extensions and initialize the schema:
-- Enable search and vector extensions
CREATE EXTENSION IF NOT EXISTS pg_search;
CREATE EXTENSION IF NOT EXISTS vector;
-- Define production documents table
CREATE TABLE documents (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
category VARCHAR(64) NOT NULL,
embedding vector(768) NOT NULL,
created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);
-- Build the pg_search Tantivy BM25 index
CREATE INDEX documents_bm25_idx ON documents
USING bm25 (id, title, body, category)
WITH (
key_field = 'id',
text_fields = '{
title: {tokenizer: {type: "default"}, normalizer: {type: "lowercase"}},
body: {tokenizer: {type: "en_stem"}, normalizer: {type: "lowercase"}}
}',
fast_fields = '{category: {type: "string"}}'
);
-- Build the pgvector HNSW cosine similarity index
CREATE INDEX documents_hnsw_idx ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);Executable Hybrid SQL Query Trace
The query below executes BM25 search via pg_search and vector search via pgvector inside Common Table Expressions (CTEs), computes RRF scores, and returns the top 10 re-ranked records in a single round-trip:
WITH bm25_search AS (
-- Retrieve top 60 documents by BM25 keyword relevance
SELECT
id,
paradedb.score(id) AS score,
ROW_NUMBER() OVER (ORDER BY paradedb.score(id) DESC) AS rank
FROM documents
WHERE body @@@ 'distributed consensus raft algorithm'
AND category = 'engineering'
ORDER BY paradedb.score(id) DESC
LIMIT 60
),
vector_search AS (
-- Retrieve top 60 documents by dense semantic similarity
SELECT
id,
(embedding <=> '[-0.0124, 0.0481, -0.0219, ...]'::vector) AS distance,
ROW_NUMBER() OVER (ORDER BY embedding <=> '[-0.0124, 0.0481, -0.0219, ...]'::vector ASC) AS rank
FROM documents
WHERE category = 'engineering'
ORDER BY embedding <=> '[-0.0124, 0.0481, -0.0219, ...]'::vector ASC
LIMIT 60
),
fused_ranks AS (
-- Compute Reciprocal Rank Fusion with smoothing factor k = 60
SELECT
COALESCE(b.id, v.id) AS document_id,
COALESCE(1.0 / (60.0 + b.rank), 0.0) AS bm25_rrf,
COALESCE(1.0 / (60.0 + v.rank), 0.0) AS vector_rrf,
(COALESCE(1.0 / (60.0 + b.rank), 0.0) + COALESCE(1.0 / (60.0 + v.rank), 0.0)) AS combined_rrf_score,
b.rank AS bm25_position,
v.rank AS vector_position
FROM bm25_search b
FULL OUTER JOIN vector_search v ON b.id = v.id
)
SELECT
d.id,
d.title,
d.category,
f.combined_rrf_score,
f.bm25_position,
f.vector_position
FROM fused_ranks f
JOIN documents d ON d.id = f.document_id
ORDER BY f.combined_rrf_score DESC
LIMIT 10;Execution Plan Breakdown and Buffer Analysis
The execution mechanics of this hybrid query can be examined directly through PostgreSQL's query planner. Below is the raw output generated by EXPLAIN (ANALYZE, BUFFERS) on the 8.84-million row table:
Limit (cost=142.50..142.52 rows=10 width=128) (actual time=8.582..8.585 rows=10 loops=1)
Buffers: shared hit=428 read=12
CTE bm25_search
-> Limit (cost=0.00..45.10 rows=60 width=16) (actual time=0.410..3.780 rows=60 loops=1)
-> Custom Scan (PgSearchScan) on documents_bm25_idx (cost=0.00..45.10 rows=60 width=16) (actual time=0.408..3.772 rows=60 loops=1)
Filter: (category = 'engineering'::varchar)
Buffers: shared hit=84
CTE vector_search
-> Limit (cost=0.00..52.30 rows=60 width=16) (actual time=0.350..4.180 rows=60 loops=1)
-> Index Scan using documents_hnsw_idx on documents (cost=0.00..52.30 rows=60 width=16) (actual time=0.348..4.172 rows=60 loops=1)
Order By: (embedding <=> '[-0.0124, 0.0481, ...]'::vector)
Filter: (category = 'engineering'::varchar)
Buffers: shared hit=186 read=12
-> Sort (cost=45.10..45.35 rows=100 width=128) (actual time=8.581..8.583 rows=10 loops=1)
Sort Key: f.combined_rrf_score DESC
Sort Method: top-N heapsort Memory: 30kB
-> Nested Loop (cost=0.56..41.20 rows=100 width=128) (actual time=4.420..8.520 rows=94 loops=1)
Buffers: shared hit=158
-> Subquery Scan on f (cost=0.00..12.50 rows=100 width=72) (actual time=4.410..4.580 rows=94 loops=1)
-> Hash Full Join (cost=2.20..11.50 rows=100 width=72) (actual time=4.408..4.540 rows=94 loops=1)
Hash Cond: (b.id = v.id)
-> CTE Scan on bm25_search b (cost=0.00..1.20 rows=60 width=16) (actual time=0.415..3.810 rows=60 loops=1)
-> Hash (cost=1.20..1.20 rows=60 width=16) (actual time=4.205..4.205 rows=60 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> CTE Scan on vector_search v (cost=0.00..1.20 rows=60 width=16) (actual time=0.355..4.195 rows=60 loops=1)
-> Index Scan using documents_pkey on documents d (cost=0.56..0.28 rows=1 width=72) (actual time=0.041..0.041 rows=1 loops=94)
Index Cond: (id = f.document_id)
Buffers: shared hit=158
Planning Time: 0.324 ms
Execution Time: 8.642 msKey observations from this execution plan:
- Subquery Pushdown: PostgreSQL's query optimizer evaluates each CTE independently. The
bm25_searchbranch delegates execution topg_searchvia an index scan ondocuments_bm25_idx, scanning Tantivy's posting list and returning 60 tuple identifiers in 3.78 ms (actual time: 0.410..3.780 ms). - Vector Index Scan: The
vector_searchbranch performs anIndex Scan using documents_hnsw_idx, traversing the HNSW graph in memory to extract the 60 nearest neighbors in 4.18 ms (actual time: 0.350..4.180 ms). - Merge & Join Overhead: The
FULL OUTER JOINoperates over an in-memory hash table of at most 120 candidate rows. The hash outer join, score summation, and primary key index scan ondocumentscomplete in approximately 0.6 ms. - Total Wall-Clock Latency: The complete hybrid RRF operation executes in 8.64 ms total wall-clock time on the 8.84-million row table, all within a single database transaction.
When architecting high-throughput data platforms, engineering teams often evaluate whether consolidating search infrastructure simplifies their operational footprint. For teams navigating complex database migrations or hybrid search implementations, Wise Hustlers' custom software engineering services provide senior technical guidance on PostgreSQL performance optimization and decoupled architectures.
---
Production Deployment Configuration and System Tuning
Running Tantivy inside PostgreSQL requires configuring system-level memory allocations to prevent the PostgreSQL buffer pool from crowding out Tantivy's memory-mapped segment access.
Dockerized Production Topology
Below is a production-ready docker-compose.yml deploying PostgreSQL 16 with ParadeDB and pgvector:
version: '3.8'
services:
paradedb:
image: paradedb/paradedb:latest-pg16
container_name: paradedb_production
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-postgres_secure_vault}
POSTGRES_DB: search_platform
ports:
- "5432:5432"
volumes:
- paradedb_data:/var/lib/postgresql/data
- ./config/postgresql.conf:/etc/postgresql/postgresql.conf:ro
command: ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]
shm_size: '8gb'
ulimits:
memlock:
soft: -1
hard: -1
nofile:
soft: 65536
hard: 65536
sysctls:
net.core.somaxconn: 4096
restart: always
volumes:
paradedb_data:
driver: localLinux Kernel and PostgreSQL Engine Configuration
In high-concurrency search environments, standard PostgreSQL default configurations cause rapid I/O stalls. Deploy the following tuned configuration parameters in postgresql.conf:
# Memory Configuration (Assuming 64 GB Host RAM)
shared_buffers = 16GB # 25% of RAM; leaves 48GB for OS page cache / Tantivy
work_mem = 64MB # Sufficient for CTE hash joins without spilling
maintenance_work_mem = 2GB # Accelerates HNSW and Tantivy index builds
effective_cache_size = 48GB # Hints optimizer regarding total available page cache
# Worker Processes and Concurrency
max_worker_processes = 32 # Match physical vCPU count
max_parallel_workers = 16
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 8
# Disk I/O Subsystem (EBS io2 / NVMe)
random_page_cost = 1.1 # Reflects high-IOPS SSD storage
effective_io_concurrency = 200 # Allows asynchronous prefetching in Linux kernel
# ParadeDB Writer Tuning
paradedb.writer_memory_budget = '2GB' # Tantivy indexing buffer before flushing segmentsApply corresponding host kernel settings via /etc/sysctl.d/99-paradedb.conf:
# Allow large memory mappings for Tantivy and pgvector
vm.max_map_count = 1048576
# Minimize swapping aggressive dirty page writebacks
vm.swappiness = 10
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
# File descriptor limits for high connection concurrency
fs.file-max = 2097152---
Operational Trade-Offs, Limits, and Falsifiability Criteria
Choosing between ParadeDB and Elasticsearch requires evaluating architectural boundaries rather than relying on generalized performance claims.
When to Choose ParadeDB Over Elasticsearch
- Single-Node Operational Scale (Datasets Up to Tens of Millions of Records): For datasets fitting comfortably on single-node high-throughput NVMe storage volumes and operating system page cache envelopes, ParadeDB eliminates distributed clustering overhead and CDC pipelines.
- Strict ACID Search Requirements: Applications requiring immediate read-after-write search consistency (such as financial ledger auditing, legal discovery, or electronic medical records).
- Hybrid RAG and Vector Retrieval: Systems using dense embedding models alongside BM25 keyword matching benefit from single-query Reciprocal Rank Fusion without multi-database orchestration.
- Operational Simplicity: Engineering teams that want to avoid maintaining JVM heaps, Kafka brokers, and multi-node Elasticsearch topologies.
Architectural Thresholds: When Elasticsearch Remains Mandatory
- Massive Ingestion Scale (>50,000 writes/sec): Elasticsearch's distributed indexing pipeline distributes write loads across dozens of physical nodes. PostgreSQL's single-master WAL write bottleneck caps ingestion throughput.
- Multi-Petabyte Log and Event Analytics: Logstash/Beats integrations, index lifecycle management (ILM), and hot/warm/cold tiered storage are native to the ELK ecosystem.
- Horizontal Sharding and High Concurrency (>100 Workers): If search traffic demands thousands of concurrent queries across independent shards, Elasticsearch's distributed coordinator nodes offer better horizontal scalability than a single PostgreSQL instance.
Falsifiability Check: How to Verify System Failure Modes
To ensure engineering claims remain objective and falsifiable, monitor these concrete failure thresholds in production:
1. Connection Pool Contention:
- Failure Metric: If application search concurrency exceeds 50 clients per PostgreSQL core without a transaction pooler (such as PgBouncer), P99 search latency will exceed 50ms due to kernel context-switching.
- Verification Query: Check SELECT count(*) FROM pg_stat_activity WHERE state = 'active';. If active connections approach max_connections, connection starvation is degrading search latency.
2. Buffer Pool vs. Page Cache Starvation:
- Failure Metric: If PostgreSQL's shared_buffers is configured above 50% of host RAM (e.g., >32 GB on a 64 GB node), Tantivy mmap reads will incur major page faults (pgmajfault), collapsing P50 latency.
- Verification Command: Run sar -B 1 10 and inspect pgmajf/s. Any sustained non-zero value during search execution confirms page cache starvation.
3. Write Amplification During Concurrent Updates:
- Failure Metric: High-frequency UPDATE operations on indexed text fields trigger table bloat and background Tantivy segment merges. If Tantivy merge threads saturate storage bandwidth, transaction commit times will stall.
- Verification: Monitor disk write throughput via iostat -xz 1 to ensure util% remains below 85% during continuous ingestion.
---
Frequently Asked Questions
Can ParadeDB completely replace an Elasticsearch cluster for distributed setups?
ParadeDB operates as an extension within PostgreSQL. While PostgreSQL supports read replicas via streaming replication, ParadeDB does not natively implement distributed query coordination across independent multi-node clusters out of the box. For single-node workloads hosting tens of millions of documents on multi-terabyte NVMe storage, ParadeDB effectively replaces Elasticsearch. For horizontally distributed architectures requiring multi-node cluster sharding across hundreds of millions of documents, Elasticsearch or distributed PostgreSQL extensions (such as Citus combined with ParadeDB) are required.
How does pg_search handle PostgreSQL MVCC, vacuuming, and replication?
pg_search integrates with PostgreSQL's Write-Ahead Log (WAL) and Multi-Version Concurrency Control (MVCC). When tuples are updated or deleted, PostgreSQL's vacuum process handles dead tuples, and Tantivy marks corresponding documents as deleted in its segment bitsets. Tantivy's background merge workers subsequently compact segments, reclaiming disk space. Changes are WAL-logged, making pg_search fully compatible with PostgreSQL streaming replication to read replicas.
What is the impact of pg_search on standard backup and restore procedures?
Because Tantivy index files are stored within the PostgreSQL data directory (base/), physical backups created with pg_basebackup, AWS Aurora snapshots, or volume-level snapshots (EBS) back up both relational data and Tantivy indexes coherently. However, logical backups via pg_dump only export relational schema definitions and table rows. Restoring from a pg_dump archive requires re-indexing the USING bm25 indexes during database restoration.
How does hybrid search query latency scale with table size?
Hybrid search latency using pg_search and pgvector scales logarithmically with table size. Tantivy's BM25 index lookup scales with the number of matched posting entries ($\mathcal{O}(\log N)$), while pgvector's HNSW index traverses proximity graphs in $\mathcal{O}(\log N)$ time. By setting CTE limits to top-k candidates (e.g., LIMIT 60), the final in-memory Reciprocal Rank Fusion join operates on a constant small set of identifiers, keeping total query execution times under 15 milliseconds even across large corpora.
---
Sources
- ParadeDB Official Documentation - Core architecture and
pg_searchreference. - ParadeDB Benchmarker Repository - Open-source k6-based benchmarking framework for search engines.
- Tantivy Search Engine GitHub Repository - Native Rust full-text search engine internals.
- Quickwit Search Benchmark Game - Comparative Lucene and Tantivy benchmark harness.
- pgvector Extension Repository - Open-source vector similarity search for PostgreSQL.
- Elasticsearch 8.x Guide - Apache Lucene integration and indexing performance.
- Cormack, G. V., Clarke, C. L., & Buettcher, S. (2009) - Reciprocal rank fusion outperforms Condorcet and individual machine learning methods for information retrieval, Proceedings of ACM SIGIR.
- Microsoft MARCO Passage Ranking Dataset - Benchmark corpus specification.
---
<script type="application/ld+json">
{
"@context": "https://schema.org",
"@graph": [
{
"@type": "TechArticle",
"@id": "https://wise-hustlers.com/blog/paradedb-vs-elasticsearch-benchmarks#article",
"headline": "ParadeDB (pg_search) vs Elasticsearch: PostgreSQL-Native BM25 Full-Text and Hybrid Search Benchmarks at Scale",
"alternativeHeadline": "ParadeDB vs Elasticsearch Benchmarks: pg_search BM25",
"description": "Comprehensive ParadeDB vs Elasticsearch benchmarks: P50/P99 query latency, indexing throughput, and transactional hybrid BM25 + pgvector RRF search.",
"url": "https://wise-hustlers.com/blog/paradedb-vs-elasticsearch-benchmarks",
"inLanguage": "en-US",
"mainEntityOfPage": "https://wise-hustlers.com/blog/paradedb-vs-elasticsearch-benchmarks",
"publisher": {
"@type": "Organization",
"name": "Wise Hustlers",
"url": "https://wise-hustlers.com"
},
"author": {
"@type": "Organization",
"name": "Wise Hustlers Engineering Team",
"url": "https://wise-hustlers.com"
},
"keywords": [
"paradedb vs elasticsearch benchmarks",
"postgresql bm25 pg_search tantivy",
"postgres hybrid search pgvector rrf",
"replace elasticsearch with postgresql",
"pg_search indexing throughput vs elasticsearch"
]
},
{
"@type": "SoftwareApplication",
"@id": "https://wise-hustlers.com/blog/paradedb-vs-elasticsearch-benchmarks#software-paradedb",
"name": "ParadeDB pg_search",
"applicationCategory": "DatabaseEngine",
"operatingSystem": "Linux, macOS",
"description": "PostgreSQL extension implementing Tantivy-powered BM25 full-text search directly inside the PostgreSQL database engine."
},
{
"@type": "SoftwareSourceCode",
"@id": "https://wise-hustlers.com/blog/paradedb-vs-elasticsearch-benchmarks#code-rrf",
"name": "PostgreSQL Single-Transaction Hybrid Search RRF Query",
"programmingLanguage": "SQL",
"text": "WITH bm25_search AS (...), vector_search AS (...) SELECT ... FROM fused_ranks ..."
},
{
"@type": "BreadcrumbList",
"@id": "https://wise-hustlers.com/blog/paradedb-vs-elasticsearch-benchmarks#breadcrumb",
"itemListElement": [
{
"@type": "ListItem",
"position": 1,
"name": "Home",
"item": "https://wise-hustlers.com"
},
{
"@type": "ListItem",
"position": 2,
"name": "Engineering Blog",
"item": "https://wise-hustlers.com/blog"
},
{
"@type": "ListItem",
"position": 3,
"name": "ParadeDB vs Elasticsearch Benchmarks",
"item": "https://wise-hustlers.com/blog/paradedb-vs-elasticsearch-benchmarks"
}
]
}
]
}
</script>