CASE STUDY · POSTGRES CDC + HYBRID SEARCH

POINT IT AT POSTGRES.
SEARCH SYNCS ITSELF.

The founder of daily.dev asked a public question: can XERJ replicate automatically from Postgres? Yes. A small CDC consumer reads the WAL through a logical-replication slot, and every INSERT, UPDATE, and DELETE flows into XERJ by itself — no reindex jobs. One hybrid query then fuses BM25 and vector rankings server-side.

FOR·POSTGRES TEAMS·SEARCH ENGINEERS·PLATFORM TEAMS

FUNCTIONAL PROOF·SYNTHETIC 8-ROW CORPUS·NOT A BENCHMARK

XERJ POSTGRES TSVECTOR + PGVECTOR

Queries per hybrid search 1 fused query 2 queries + app-side merge

App-side score merges 0 · RRF fuses server-side 1 · across two score scales

ARCHITECTURAL COUNT FROM THE RECORDED RUN · SOURCES IN docs/case-studies/daily-dev-postgres-cdc →

SEARCH ON THE PRIMARY

tsvector and pgvector don't fuse. two ranked lists, merged in app code across incompatible score scales.
Search load hits OLTP. ranking queries and business writes fight for the same primary.
A dedicated engine means a sync pipeline. you build it, run it, and babysit its reindex jobs.
Deletes are the trap. miss one and search keeps serving rows Postgres removed.

THE XERJ ANSWER

Replication is the pipeline. a logical-replication slot streams every INSERT/UPDATE/DELETE into XERJ.
One fused query. first-class hybrid search — BM25 + kNN merged server-side by reciprocal-rank fusion.
Crash-safe by LSN. a restarted consumer resumes from the confirmed LSN, replaying only what it missed.
Zero manual reindexes. upsert and delete-by-id converge XERJ to Postgres state.

RECORDED RUN·POSTGRES 16 + PGVECTOR 0.8.2·LOGICAL REPLICATION

3/3

MUTATION TYPES REPLICATED

INSERT + UPDATE + DELETE reflected in one slot drain

CHANGES LOST IN DOWNTIME

consumer down while Postgres kept writing

CHANGES REPLAYED ON RESTART

exactly the missed ones, from the confirmed LSN

MANUAL REINDEXES

every mutation applied by upsert or delete-by-id

HOW IT WORKS.

A ~20-line SQL bootstrap turns on logical replication; a small Python consumer drains the slot, re-embeds changed rows (EmbeddingGemma via Ollama, 768-dim cosine), and bulk-writes XERJ. Search is one request: XERJ's hybrid query fuses the BM25 ranking and the kNN ranking with reciprocal-rank fusion — no score normalization, no merge code.

THE ONE REQUEST — BM25 + VECTOR, FUSED SERVER-SIDE

$ curl -s localhost:9200/posts/_search -H 'Content-Type: application/json' -d '{
  "query": { "hybrid": {
    "queries": [
      { "query": { "match": { "summary": "ditching the JVM search stack for something cheaper" } } },
      { "query": { "knn": { "field": "vec", "query_vector": [/* 768-dim */], "num_candidates": 9 } } }
    ],
    "fusion": { "type": "rrf", "k": 60 }
  } }
}'

Recorded top-3 for that query — RRF rank-fusion scores, shown as the fused order:

Why we moved off Elasticsearch RRF 0.03279
The cost of microservices RRF 0.03175
Building a vector search engine from scratch RRF 0.03151

CHANGE POSTGRES, WATCH XERJ FOLLOW — THEN A CRASH-RECOVERY REPLAY

-- in Postgres:  UPDATE p7 · INSERT p9 · DELETE p5
$ python3 cdc_sync.py
[cdc] synced 2 upserts, 1 deletes to XERJ  [('p7','UPDATE'),('p9','INSERT'),('p5','DELETE')]

# consumer killed; UPDATE p1 + INSERT p11 happen while it is down
$ python3 cdc_stream.py
[stream] consuming WAL from confirmed LSN (push, no polling)
[stream] UPDATE p1  -> XERJ  LSN=27863424  [checkpointing]
[stream] INSERT p11 -> XERJ  LSN=27863976  [checkpointing]

WHAT THIS DOES NOT SHOW.

TINY SYNTHETIC CORPUS.

Eight seed posts (~11 after live changes), modeled on the open-source daily-api Post schema — not daily.dev's data. A functional proof: no latency, throughput, or cost numbers exist, and "within seconds" is qualitative.

DEMO SLOT, NOT PRODUCTION SHAPE.

The consumer reads Postgres' built-in test_decoding slot. The production shape is a pgoutput slot, wal2json, or Debezium in front of the same upsert/delete path.

AT-LEAST-ONCE, CONVERGENT.

Delivery is at-least-once, converging to Postgres state through idempotent upsert/delete-by-id plus LSN checkpointing — exactly-once-convergent, not bare exactly-once.

BRING YOUR OWN EMBEDDINGS.

XERJ stores and searches vectors; it does not embed. Quality is your model's job (EmbeddingGemma via Ollama here). RRF k and weights need per-corpus tuning; fusion:'learned' does not exist — only rrf and linear.

POINT IT AT POSTGRES.
SEARCH SYNCS ITSELF.

SEARCH ON THE PRIMARY

THE XERJ ANSWER

HOW IT WORKS.

WHAT THIS DOES NOT SHOW.

READ THE WALKTHROUGH.

RUN IT ON
YOUR POSTGRES.

POINT IT AT POSTGRES.SEARCH SYNCS ITSELF.

SEARCH ON THE PRIMARY

THE XERJ ANSWER

HOW IT WORKS.

WHAT THIS DOES NOT SHOW.

READ THE WALKTHROUGH.

RUN IT ONYOUR POSTGRES.

POINT IT AT POSTGRES.
SEARCH SYNCS ITSELF.

RUN IT ON
YOUR POSTGRES.