In this article, we will explore a search & retrieval design for natural language queries, expecting data in real-time consistency with updates. We will build a write-path, segregate the read-path and a streaming pipeline in cloud. We will also look into the scalability and performance of the architecture.
The complete Git repository for the benchmarking is provided at the end of this article.
To make a retrieval mechanism over data being updated in real-time, we need the ingredients:
- Updates on a database
- Real-time streaming, indexing and inference
- Natural queries expecting updated output
Everytime we make an update in our database, we will asynchronously replicate or stream the write to our search engine.
Building the streaming engine
Kafka
Kafka is a distributed append-only log that uses a publish-subscribe model to let producers write data streams and consumers can read them independently. It separates the data streams into topics, distributed partitions and consumer groups, and retains them on disk based on topic specific retention policies, which enables multiple consumers to read data simultaneously with each having their independent lag. The tolerance of lag makes Kafka the correct message bus for streaming our updates to the retrieval engine.
Debezium
Any relational database first writes the updates to a log before committing the write to the storage — Write Ahead Log. Reading these logs and copying these writes somewhere else is called replication. Debezium is a set of source connectors designed for the Kafka Connect framework. It reads the logical WAL of the database, which is designed for its own replication and PITR (point-in-time-recovery), and translates it to a Kafka readable message. This process is called Change Data Capture (CDC).
Building a search engine
Keyword Search
Given a query with keywords “A”, “B” & “C”, we can return the data with the highest frequencies of these keywords, thus making them more relevant. This simple approach is called a bag of words and the most commonly used function to calculate the score is Okapi BM25. Querying requires an Inverted File Index , in which keywords are mapped with a list of document IDs along with its term frequency. This index is implemented at scale by Lucene — the engine behind ElasticSearch and OpenSearch.
Semantic Search
Keyword search works well when the query and the document share the same words. However, consider a query such as
“How do I reserve a seat on a flight?”
A document containing:
“Passengers can temporarily hold an available seat before completing payment.”
is clearly relevant to the query, even though many of the words are different.
Keyword search has no inherent understanding of this relationship. We therefore need a representation of text that captures its meaning, rather than just the words it contains.
This is where embeddings come in.
An embedding model converts a piece of text into a numerical vector reprsesentation:
“How do I reserve a seat on a flight?”
↓ embedding model
[0.12, -0.34, 0.87, …, 0.21]
The individual numbers are not particularly meaningful to us. What matters is that text with similar meaning tends to produce vectors that are close together in the embedding space.
“How do I reserve a seat?”
↓
[0.12, -0.34, …]
“Can I temporarily hold a flight seat?”
↓
[0.10, -0.31, …]
These vectors can then be compared using a similarity metric such as cosine similarity.
This gives us a fundamentally different search mechanism:
- Keyword search:
query → terms → inverted index → matching documents
- Semantic search:
query → embedding → vector similarity → similar documents
Vector Databases
To power the query for vectors, we need specialised databases that operate on Aproximate Nearest Neighbours (ANN).
However, at a scale of millions of vectors, the neighbour calculations may require millions of similarity calculations for every query. Just like keyword search required Inverted File Index, vector databases use specialized indexing structures. A popular approach is Hierarchical Navigable Small World (HNSW).
HNSW organises vectors into multiple layers of a graph. Higher layers provide long-range connections that allow the search to quickly move towards the relevant region of the vector space, while lower layers provide increasingly fine-grained connections. This allows us to find approximate nearest neighbours without comparing the query against every vector.
For our use case, we can use Qdrant as the vector database. It stores the generated embeddings and provides efficient approximate nearest-neighbour search using HNSW index.
Embedding generation then becomes a separate concern, we can use an embedding model hosted by a cloud provider or run a local inference service.
Combining Keyword & Semantic Search
In practice, we need to look for documents that have both similar meaning as well as exact words. Therefore, we will perform both keyword and vector search in parallel and combine the results by using Reciprocal Rank Fusion (RRF).
RRF is a simple algorithm that combines multiple ranked lists into a single unified list. It does not require the scores produced by the different retrieval systems to compare the documents. Instead, it uses the rank of a document in each result list to calculate a combined score.
RRF(d) = Σ 1 / (k + rank(d))
Hence, a document that appears on top of multiple lists is more likely to rank higher in the final set. We can have the detailed implementation later.
For now, our final ETL architecture loads data both into two separate data stores.
Infrastructure
We will split the architecture between managed cloud services, preferrably over multiple regions for network latencies, and lightweight local infrastructure. This will keep the setup simple while allowing us to demonstrate a realistic CDC-driven search pipeline.
PostgreSQL (AWS RDS) acts as the source of truth for a users dataset.
Kafka + Debezium run locally in Docker containers. Debezium reads PostgreSQL’s logical WAL and publishes database changes to Kafka.
Amazon OpenSearch is used for traditional keyword search and BM25 indexing.
Qdrant Cloud stores the document embeddings and provides vector similarity search.
Kafka consumers independently process the CDC stream:
- 3 consumer processes will consume 3 kafka paritions
- we will keep parallel indexing methods for Opensearch and Qdrant indexing.
- we could keep separate consumers for each indexing workflow, however, it will be easier to collect benchmark reports from a single script.
Setting up PostgreSQL for CDC
We will use Amazon RDS for PostgreSQL and configure it to expose logical changes through the PostgreSQL WAL.
Create a PostgreSQL RDS instance
- Provision a small PostgreSQL instance on AWS.
- Configure network access so the local CDC infrastructure can connect to it.
Create the application database and table
The users table contains the fields that will eventually be indexed by both OpenSearch and Qdrant.
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT,
email TEXT,
company TEXT,
job_title TEXT,
location TEXT,
skills TEXT,
bio TEXT,
experience INT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
Enable logical replication
PostgreSQL needs to retain the logical changes in its WAL so that Debezium can consume them.
On RDS, this involved enabling:
rds.logical_replication = 1
After applying the parameter-group change and restarting the instance, PostgreSQL should report:
wal_level = logical
Create a dedicated replication user
We need to create a replication only user for our CDC connector:
CREATE USER debezium
WITH PASSWORD '...';
GRANT rds_replication TO debezium;
GRANT CONNECT ON DATABASE appdb TO debezium;
GRANT USAGE ON SCHEMA public TO debezium;
GRANT SELECT ON TABLE public.users TO debezium;
Create a publication for the table
PostgreSQL logical replication uses a publication to define which tables and operations are exposed through logical replication.
CREATE PUBLICATION users_cdc
FOR TABLE public.users;
This publication exposes changes to the users table, which Debezium subsequently reads from the PostgreSQL WAL.
Verify the replication configuration
We can now verify the PostgreSQL configuration for our CDC pipeline.
SHOW wal_level;
-- should return `logical`
SELECT *
FROM pg_publication_tables;
-- should contain the expected table `users`
Pre-loading Opensearch and Qdrant
Before setting up our CDC pipeline, we will first populate the AWS Opensearch and Qdrant cloud instances.
For Opensearch, we can use a Logstash pipeline as a convenient way to perform the initial bulk load directly from PostgreSQL.
input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://<RDS_ENDPOINT>:5432/appdb"
jdbc_user => "indexer"
jdbc_password => "<PASSWORD>"
jdbc_driver_library => "/path/postgresql.jar"
jdbc_driver_class => "org.postgresql.Driver"
statement => "
SELECT
id, name, email, company, job_title,
location, skills, bio, experience,
created_at, updated_at
FROM users
"
}
}
output {
opensearch {
hosts => ["<OPENSEARCH_ENDPOINT>"]
user => "<USERNAME>"
password => "<PASSWORD>"
index => "users"
# Keep the Postgres primary key as the OpenSearch document ID
document_id => "%{id}"
ecs_compatibility => disabled
ssl_certificate_verification => true
}
stdout {
codec => rubydebug
}
}
We can then query the Opensearch instance:
GET users/_search
{
"size": 1,
"_source": ["id"],
"query": {
"multi_match": {
"query": "backend engineer kafka",
"fields": [
"job_title",
"skills",
"bio",
"company",
"location"
]
}
}
}
and expect a response as:
{
"took": 378,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 8621,
"relation": "eq"
},
"max_score": 2.1194136,
"hits": [
{
"_index": "users",
"_id": "2970",
"_score": 2.1194136,
"_source": {
"id": 2970
}
}
]
}
}
Similarly, we can bulk-load the Qdrant instance using a Python script:
from qdrant_client import QdrantClient, models
client = QdrantClient(
url="<QDRANT_URL>",
api_key="<QDRANT_API_KEY>",
cloud_inference=True,
)
collection = "users_semantic"
points = []
for user in users:
text = f"""
Name: {user["name"]}
Company: {user["company"]}
Job title: {user["job_title"]}
Location: {user["location"]}
Skills: {user["skills"]}
Bio: {user["bio"]}
"""
points.append(
models.PointStruct(
id=user["id"],
vector=models.Document(
text=text,
model="sentence-transformers/all-MiniLM-L6-v2",
),
payload=user,
)
)
client.upsert(
collection_name=collection,
points=points,
)
Each Qdrant point uses the PostgreSQL id as its point ID, keeping the identity consistent across the source database, OpenSearch and Qdrant.
Local CDC infrastructure
For the streaming layer, we will use docker compose to run Kafka and Debezium locally. This will make it possible to experiment with the CDC pipeline without having to operate a complete Kafka cluster at cloud.
services:
kafka:
image: apache/kafka:4.0.0
container_name: kafka
hostname: kafka
ports:
- "9092:9092"
- "9094:9094"
environment:
# KRaft - single node
KAFKA_NODE_ID: 1
KAFKA_PROCESS_ROLES: broker,controller
# PLAINTEXT is for other containers on the "cdc" network (e.g.
# debezium), which resolve "kafka" via Docker's internal DNS.
# EXTERNAL is for clients running on the host machine (e.g.
# consumer/index_users.py via the venv), which can't resolve
# "kafka" and need "localhost" instead.
KAFKA_LISTENERS: PLAINTEXT://:9092,EXTERNAL://:9094,CONTROLLER://:9093
KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092,EXTERNAL://localhost:9094
KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: CONTROLLER:PLAINTEXT,PLAINTEXT:PLAINTEXT,EXTERNAL:PLAINTEXT
KAFKA_CONTROLLER_LISTENER_NAMES: CONTROLLER
KAFKA_CONTROLLER_QUORUM_VOTERS: 1@kafka:9093
# Single-node development settings
KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
KAFKA_TRANSACTION_STATE_LOG_REPLICATION_FACTOR: 1
KAFKA_TRANSACTION_STATE_LOG_MIN_ISR: 1
KAFKA_GROUP_INITIAL_REBALANCE_DELAY_MS: 0
KAFKA_NUM_PARTITIONS: 3
volumes:
- kafka-data:/var/lib/kafka/data
networks:
- cdc
debezium:
image: quay.io/debezium/connect:3.3
container_name: debezium
hostname: debezium
depends_on:
- kafka
ports:
- "8083:8083"
environment:
BOOTSTRAP_SERVERS: kafka:9092
# Kafka Connect worker
GROUP_ID: 1
CONFIG_STORAGE_TOPIC: connect_configs
OFFSET_STORAGE_TOPIC: connect_offsets
STATUS_STORAGE_TOPIC: connect_status
CONFIG_STORAGE_REPLICATION_FACTOR: 1
OFFSET_STORAGE_REPLICATION_FACTOR: 1
STATUS_STORAGE_REPLICATION_FACTOR: 1
# JSON for easy inspection while learning
KEY_CONVERTER: org.apache.kafka.connect.json.JsonConverter
VALUE_CONVERTER: org.apache.kafka.connect.json.JsonConverter
KEY_CONVERTER_SCHEMAS_ENABLE: "false"
VALUE_CONVERTER_SCHEMAS_ENABLE: "false"
# REST API
REST_PORT: 8083
REST_ADVERTISED_HOST_NAME: debezium
networks:
- cdc
volumes:
kafka-data:
networks:
cdc:
driver: bridge
We can get our Kafka+Debezium cluster up by executing
docker compose up -d
Once the cluster is up and running, we will do a health check and then register our connector:
# Verify the container processes
docker compose ps
# kafka running
# debezium running
# Check that Kafka is responding
docker exec kafka /opt/kafka/bin/kafka-topics.sh \
--bootstrap-server kafka:9092 \
--list
# connect_configs
# connect_offsets
# connect_status
# Later once debezium is registered we will also see -
# usersdb.public.users
# schemahistory.users
# Verify the Kafka Connect internal topics
docker exec kafka /opt/kafka/bin/kafka-topics.sh \
--bootstrap-server kafka:9092 \
--describe \
--topic connect_configs
# PartitionCount: 1
# Configs: cleanup.policy=compact
docker exec kafka /opt/kafka/bin/kafka-topics.sh \
--bootstrap-server kafka:9092 \
--describe \
--topic connect_offsets
# PartitionCount:
# Configs: cleanup.policy=compact
docker exec kafka /opt/kafka/bin/kafka-topics.sh \
--bootstrap-server kafka:9092 \
--describe \
--topic connect_status
# PartitionCount:
# Configs: cleanup.policy=compact
Now, we can validate our Debezium instance:
curl http://localhost:8083/
curl http://localhost:8083/connectors
# should return []
curl http://localhost:8083/connector-plugins
# {
# "class": "io.debezium.connector.postgresql.PostgresConnector",
# "type": "source",
# "version": "..."
# }
It is better to check for the debezium logs as well —
docker logs debezium --tail 100
docker logs -f debezium
# We should not see -
# ConfigException
# Kafka Connect stopping
# Uncaught exception in herder work thread
Registering the PostgreSQL connector requires a connector configuration:
{
"name": "users-cdc",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"database.hostname": "",
"database.port": "5432",
"database.user": "",
"database.password": "",
"database.dbname": "",
"database.sslmode": "require",
"topic.prefix": "usersdb",
"plugin.name": "pgoutput",
"slot.name": "users_cdc_slot",
"publication.name": "users_cdc",
"publication.autocreate.mode": "disabled",
"table.include.list": "public.users",
"snapshot.mode": "no_data",
"tombstones.on.delete": "false",
"schema.history.internal.kafka.topic": "schemahistory.users",
"schema.history.internal.kafka.bootstrap.servers": "kafka:9092"
}
}
Then we can call the registration API:
curl -X POST \
-H "Content-Type: application/json" \
--data @users-cdc.json \
http://localhost:8083/connectors
# check the connector
curl http://localhost:8083/connectors/users-cdc
curl http://localhost:8083/connectors/users-cdc/status
# {
# "name": "users-cdc",
# "connector": {
# "state": "RUNNING"
# },
# "tasks": [
# {
# "id": 0,
# "state": "RUNNING"
# }
# ]
# }
Indexing Consumer
The consumers read CDC events from Kafka and translate them into operations on the respective search systems.
For each database change:
- INSERT → create/upsert the document
- UPDATE → update/re-embed the document
- DELETE → remove the document from the indexes
This also makes the pipeline change-driven : instead of periodically scanning the entire database, only the records that changed need to be propagated.
if operation in ("c", "u", "r"):
user = payload.get("after")
opensearch_future = executor.submit(
index_opensearch, user
)
qdrant_future = executor.submit(
index_qdrant, user
)
wait([opensearch_future, qdrant_future])
For Opensearch we will upload the complete document:
document_id = str(user["id"])
opensearch.bulk(
body=[
{
"index": {
"_index": "users",
"_id": document_id,
}
},
user,
]
)
For Qdrant we will build the search text and then publish:
def build_search_text(user):
fields = [
user.get("name"),
user.get("job_title"),
user.get("company"),
user.get("location"),
user.get("skills"),
user.get("bio"),
]
return " ".join(
str(value)
for value in fields
if value
)
models.PointStruct(
id=int(user["id"]),
vector=models.Document(
text=build_search_text(user),
model="sentence-transformers/all-MiniLM-L6-v2",
),
payload=user,
)
The complete consumer script is shared at —
real-time-retrieval/consumer/index_users.py at master · umangaggarwal2/real-time-retrieval
Querying: Hybrid Search
We are finally at the last piece of our architecture — retrieval. We will use both keyword and semantic search, i.e. retrieve from Opensearch and Qdrant and combine the results using RRF.
The steps for writing a retrieval script are —
- BM25: top 50
- semantic: top 50
- BM25 weight: 0.6
- semantic weight: 0.4
- k = 60
- final results: top 10
Where the RRF score is defined as:
score(d) =
0.6 * 1 / (60 + bm25_rank(d)) +
0.4 * 1 / (60 + semantic_rank(d))
RRF lets the two retrieval systems contribute based on rank rather than trying to directly compare BM25 scores with cosine similarity scores, which live on different scales.
Find the complete querying script at —
real-time-retrieval/search/query.py at master · umangaggarwal2/real-time-retrieval
Benchmarking the performance
We may have the system ready and working.
But this architecture becomes much more interesting when we understand how it behaves under load — and, more importantly, how much “real-time” we actually get.
We will benchmark the write and read paths with a target load of 100 QPS for 2–5 minutes.
Peformance — defines how fast a system works for a single task or at a pre-defined benchmark scale.
Hence, we will measure the latency, while keeping our benchmark 100 QPS, but for a small dataset of 10K records.
Rather than measuring only end-to-end latency, we instrument each stage of the pipeline so that we can distinguish database latency, CDC lag, queueing, network overhead, and downstream indexing/inference time.
- Write → database
- Database WAL →CDC
- CDC → Kafka
- Kafka → Consumer
- Consumer → OpenSearch Indexing: including network overhead and indexing latency
- Consumer → Qdrant Indexing: including network overhead, embedding generation and indexing latency
# Write benchmarking
def run_writer(csv_rows, rate, duration, concurrency):
def _one_update():
# Pick an existing target user.
target_row = random.choice(csv_rows)
target_id = int(target_row["id"])
# Pick another row as the source of new values.
source_row = random.choice(csv_rows)
return perform_update(
_pg_worker.cursor,
target_id,
source_row,
)
def _on_success(result):
user_id, updated_at, db_latency_ms = result
# Convert PostgreSQL timestamp into an epoch timestamp.
if updated_at.tzinfo is None:
updated_at = updated_at.replace(tzinfo=timezone.utc)
update_time = updated_at.timestamp()
with state.lock:
state.total_updates += 1
state.db_latencies_ms.append(db_latency_ms)
state.pending[user_id] = {
"updated_at": update_time,
"created_at": time.perf_counter(),
}
state.all_target_ids.append(user_id)
# Read benchmarking
def run_benchmark(
queries,
rate,
duration,
warmup,
concurrency,
):
def _one_query():
query_text = random.choice(queries)
return query_text, query(query_text)
def _on_success(result):
query_text, result_dict = result
latency = result_dict["latency"]
overlap = result_dict["overlap"]
with state_lock:
bm25_latencies.append(latency["bm25_ms"])
semantic_latencies.append(latency["semantic_ms"])
rrf_latencies.append(latency["rrf_ms"])
total_latencies.append(latency["total_ms"])
overlap_percentages.append(overlap["percentage"])
Write Performance: P99
- PostgreSQL write: 762.62 ms
- PostgreSQL -> OpenSearch: 65431.88 ms
- PostgreSQL -> Qdrant: 65431.88 ms
Write Latency Breakdown:
Postgres commit -> Debezium capture: 10906.02 ms
Debezium -> consumer receive (Kafka): 302706.23 ms
OpenSearch: network overhead: 543.66 ms
OpenSearch: server-side indexing: 14.00 ms
Qdrant: network overhead: 261.87 ms
Qdrant: server-side embed + HNSW (time): 192.16 ms
Read Performance: P99
OpenSearch / BM25 latency (total round-trip, network + server): 7634.46 ms
Qdrant / semantic latency (total round-trip, network + server): 2107.44 ms
RRF computation latency: 0.29 ms
End-to-end hybrid latency: 8517.81 ms
BM25 / semantic top-50 overlap: _ **_40%**
Bottlenecks
The benchmark gives us a few interesting observations about where the system starts to struggle under load.
1. Consumer throughput is the bottleneck in the write path
We are generating around 100 database updates per second , while the indexing consumer can process only around 1–3 updates per second per instance.
Hence, the database is able to accept the writes, but the downstream consumer is not able to process the changes at the same rate.
This creates a backlog in Kafka.
2. OpenSearch is the bottleneck in the read path
The read benchmark gives us a different bottleneck.
We are querying OpenSearch for BM25 results and Qdrant for semantic results and then combining both using RRF.
The p99 latency for OpenSearch was: 7634.46 ms
The RRF computation is negligible compared to the time spent retrieving the results. Qdrant also remains significantly healthier than OpenSearch under this workload.
The OpenSearch instance starts struggling when we increase concurrent BM25 queries. This results in request timeouts and causes the achieved query throughput to fall significantly below the requested 100 QPS.
Hence, increasing the application concurrency alone does not solve the problem. The bottleneck is the query capacity of the OpenSearch deployment.
For a production system handling this level of traffic, we would need to increase the capacity of the search infrastructure rather than simply increasing the number of application threads.

Top comments (0)