docs(case-study): auto-replicate Postgres → XERJ (CDC) + hybrid search (daily.dev) - #61
Merged
Merged
Conversation
added 2 commits
July 27, 2026 20:31
…ch (daily.dev) For Ido Shamun (daily.dev) — "is it possible to replicate automatically from pg?". End-to-end tested: Postgres 16 + pgvector 0.8.2 with logical replication, a CDC consumer that streams every INSERT/UPDATE/DELETE into XERJ with EmbeddingGemma vectors, then hybrid (BM25 + vector) search. Grounded in daily.dev's real Post entity (dailydotdev/daily-api): title, summary, tagsStr, sourceId, views/upvotes/comments/score/trending, metadataChangedAt watermark + a pgvector embedding column. Proven live: initial load of 8 posts, then UPDATE (upvotes) + INSERT (new post) + DELETE flowed pg->XERJ in one sync pass (slot as change-signal; consumer reads current row, embeds, upserts/deletes by id -> convergent state). Hybrid query "ditching the JVM search stack for something cheaper" ranks the migration post via fused BM25+vector where vector-alone drifts and lexical-alone misses synonyms -- the thing pg tsvector+pgvector can't do in one ranked query. Includes runnable setup.sql (schema + trigger + logical slot) and cdc_sync.py (consumer), a production-CDC options table (test_decoding/pgoutput/wal2json/ Debezium/watermark), scaling notes, and an honest pg-vs-XERJ comparison + limits. Co-Authored-By: Xerj Squad A <noreply@xerj.org>
…reaming, LSN) — rc.6 Addresses the three limitations flagged in the daily.dev CDC case study, all implemented and proven live: 1. RRF hybrid — turned out to ALREADY exist first-class (`hybrid` + `fusion:rrf`, with weights + `linear`); the earlier "not yet" was MY request-syntax error. Corrected the docs to the real shape; verified live (rrf_score ~1/(60+rank)): fuses BM25+vector with no manual score normalization. 2. Push-streaming CDC — cdc_stream.py uses psycopg2 LogicalReplicationConnection.consume_stream() (true push, blocks on WAL, no polling). Proven: an INSERT in pg appears in XERJ within seconds. 3. LSN checkpointing (exactly-once-convergent) — send_feedback(flush_lsn=...) after each durable apply advances confirmed_flush_lsn. Proven: stop consumer -> change data while down (UPDATE p1, INSERT p11) -> XERJ stale -> restart resumes from the last checkpoint and applies EXACTLY the missed changes, no loss, no re-sync. Rewrote the honest-limitations section (embedding-model quality, RRF k/weight tuning, no learned fusion, backfill cost) and moved the three resolved items to a "Resolved" list. Complements the rc.6 knn+aggregations feature (PR #60). Co-Authored-By: Xerj Squad A <noreply@xerj.org>
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
For Ido Shamun / daily.dev
Answers "is it possible to replicate automatically from pg?" — yes, tested end-to-end.
Postgres 16 + pgvector 0.8.2 with logical replication → a CDC consumer streams every INSERT/UPDATE/DELETE into XERJ (with EmbeddingGemma vectors) → hybrid (BM25 + vector) search, moving the search workload off
pg tsvector + pgvector.Grounded in daily.dev's real schema
Modeled on the actual
Postentity indailydotdev/daily-api:title,summary,tagsStr,sourceId, engagement counters,metadataChangedAtwatermark, + a pgvector column.Proven live (real output)
UPDATE upvotes+INSERTa new post +DELETEone → one sync pass reflected all three in XERJ (update, insert, delete) — no manual reindex.tsvector+pgvectorcan't do in one ranked result.Contents
setup.sql— daily.dev-modeled schema + change-watermark trigger + logical replication slot.cdc_sync.py— the CDC consumer (slot → embed → XERJ bulk upsert/delete).README.md— step-by-step runnable guide, production-CDC options (test_decoding / pgoutput / wal2json / Debezium / watermark), scaling notes, and an honest pg-vs-XERJ comparison + limitations.Docs/examples only — no engine code.
🤖 Generated with Claude Code