⚡ ~/naveed Interview Prep
⚡ Portfolio Home 👤 About Naveed Bio & Credentials 📫 Contact Naveed Get In Touch ✍️ Engineering Blog Deep Dives 🎯 Interview Hub 1,000+ Scenarios ☸️ Kubernetes Mastery Hub 24 Modules 🎮 DevOps Arcade & Quizzes Subnet Blitz ⚡ 🗺️ DevOps Roadmaps PDFs & Guides 🤖 Morpheus Analysis AI Quant ↗ 🛠️ Developer Tools Utilities 🧪 Labs & Experiments 📄 Interactive CV & Certs 🔗 All Links & Socials ⚡ Join The Dispatch (Weekly SRE Newsletter) →
← Back to All Databases & Storage Interview Questions Scenario 40 of 54 in Databases & Storage
Staff Search Infrastructure Architect Database Search & Streaming Architecture System Design
🎯 Target Role / Context: Principal Search Platform Architect / Staff SRE System Design Interview

Q: Your e-commerce catalog contains 100 million products. When a merchant updates an item's price or stock in the primary database, the updated information must be searchable on the website within 1 second. Dual-writing from application code causes race conditions and stale search results. How do you design an asynchronous, real-time search indexing pipeline with CDC, Kafka, and OpenSearch?

Engineering a real-time document search indexing pipeline synchronizing 100 million database entities to OpenSearch with sub-second update visibility, out-of-order event reconciliation, and zero reindexing downtime.

#System Design #OpenSearch #Elasticsearch #CDC #Kafka #Search Indexing #Debezium
🎙️ Candidate Opening & Architectural Context
"Writing directly to both the primary database and OpenSearch leads to inevitable consistency bugs due to network blips and out-of-order writes. We engineered an asynchronous search indexing platform combining Debezium Change Data Capture, Kafka stream enrichment, and OpenSearch external versioning."
Advertisement
⚡ Recommended Practice Lab

Want to master this scenario in a live sandbox? KodeKloud's PostgreSQL Database Administration & High Availability Course covers this exact problem with hands-on terminal drills.

🛠️ Production Runbook & Step-by-Step Resolution

Direct Answer: Writing directly to both the primary database and OpenSearch leads to inevitable consistency bugs due to network blips and out-of-order writes. We engineered an asynchronous search indexing platform combining Debezium Change Data Capture, Kafka stream enrichment, and OpenSearch external versioning.

1️⃣

Capture Database Changes via Debezium CDC into Apache Kafka

Extract committed mutations from the primary relational database with zero impact on queries:

  • WAL Capture: Debezium reads committed transaction logs from PostgreSQL/MySQL, capturing product inserts, updates, and deletes in real time.
  • Kafka Partitioning: Events publish to Kafka topic catalog.cdc.events partitioned strictly by product_id.
  • Per-Entity Ordering: Partitioning by product ID guarantees that all updates for a single product are processed in strict sequential order.
Pro Tip: Partitioning by entity ID guarantees FIFO ordering per product, eliminating race conditions across multiple updates.
2️⃣

Denormalize & Enrich Search Documents via Apache Flink / Kafka Streams

Transform relational normalized rows into comprehensive search documents:

  • Stream Join: Flink consumes product updates and joins with merchant profiles, category trees, and review ratings stored in an in-memory RocksDB state store.
  • Search Document Serialization: Emits a complete, fully denormalized JSON search document containing keywords, facets, price, and inventory status.
  • Write Buffer: Emits enriched documents to opensearch.indexing.stream.
Pro Tip: Denormalizing search documents in a stream processing engine offloads complex SQL JOINs from the primary transactional database.
Advertisement
3️⃣

Ingest Documents via OpenSearch Bulk API & External Versioning

Maximize indexing throughput while guaranteeing protection against out-of-order writes:

  • Bulk Worker Fleet: Autoscaled indexing workers consume from Kafka and flush documents using the OpenSearch Bulk API in micro-batches (5,000 docs or 500ms).
  • External Versioning: Documents are indexed with version_type=external_gte using the database transaction commit timestamp or LSN as the version.
  • Stale Write Rejection: If a delayed Kafka message arrives with an older version, OpenSearch rejects the write with HTTP 409 Conflict, preserving the newer state.
Pro Tip: External versioning guarantees that network delays can never cause an older price update to overwrite a newer price update.
4️⃣

Manage Schema Migrations via Index Aliases & Reindex Pipelines

Update search analyzers and field mappings without interrupting live search traffic:

  • Search Aliases: Application queries search alias products_search pointing to physical index products_v1.
  • Zero-Downtime Reindex: When analyzers change, pipeline creates products_v2, runs _reindex, catches up on Kafka CDC stream, and atomically swaps the alias in < 10 milliseconds: POST /_aliases { actions: [{ add: { index: 'products_v2', alias: 'products_search' } }, { remove: { index: 'products_v1', alias: 'products_search' } }] }.
  • End-to-End Latency: Product updates reflect in search results in 680ms.
Pro Tip: Atomic index alias swapping allows full schema reindexing across 100 million documents with zero downtime or stale search results.
💡 The Senior SRE Gold Nugget (Key Architectural Takeaway)
"Real-time search indexing combines Debezium CDC for mutation extraction, stream processing for document denormalization, OpenSearch external versioning to prevent out-of-order writes, and atomic index aliases for zero-downtime schema evolution."
⚡ 60-Second Elevator Pitch Talking Points
  • Capture database mutations via Debezium CDC partitioned by entity ID for strict ordering.
  • Denormalize relational entities into rich search JSON documents using stream processing.
  • Index into OpenSearch using bulk micro-batches and external versioning to eliminate race conditions.
  • Use atomic index aliases to execute seamless zero-downtime full reindexing.
⛔ Critical Candidate Mistakes to Avoid in This Scenario

Senior hiring managers and bar raisers evaluate how you prioritize during outages. Avoid these common anti-patterns:

  1. Applying exclusive-lock DDL table migrations on live production databases during peak traffic windows when configuring System Design Real DDL migrations that acquire an exclusive table lock block all concurrent queries, queuing connections until the database connection pool exhausts.
  2. Allowing high-frequency queries to trigger full table scans without appropriate composite indexing when configuring System Design Real Sequential table scans saturate database CPU and disk I/O, evicting frequently queried data from the buffer cache and causing cascading timeouts.
  3. Disabling referential integrity constraints during bulk ETL imports without post-import consistency validation when configuring System Design Real Skipping relational consistency checks risks introducing orphaned records and referential integrity violations into production tables.
  4. Relying on database snapshot backups that have never been verified through automated restoration drills when configuring System Design Real Backups that have never been tested with automated restore verification frequently fail during real disaster recovery scenarios.
Advertisement
Want more Databases & Storage scenarios?
Explore our complete collection of scenario-based Databases & Storage interview runbooks.
Browse All Databases & Storage Questions →