Hydrating CQRS Read Models and Search Indexes in Real Time with CDC

In modern web applications, the database design optimized for transactional writes (ACID, 3NF normalization, relational constraints) is almost never optimal for high-speed user queries (denormalized JSON documents, full-text search, sub-millisecond caching).

Command Query Responsibility Segregation (CQRS) solves this by separating the Write Model from the Read Model. Instead of using hazardous dual-writes inside web request handlers, real-time Change Data Capture (CDC) streams database changes through Kafka to continuously hydrate downstream search indexes and caches.

---

1. End-to-End CQRS CDC Architecture

Why This Eliminates Dual-Write Hazards:

1. Zero Write-Side Latency: The Checkout API only writes to PostgreSQL. It does not wait for Elasticsearch or Redis to index the document.
2. Guaranteed Eventual Consistency: Even if Elasticsearch goes down for 30 minutes, Kafka retains the CDC stream. When Elasticsearch recovers, the hydrator worker catches up from its last committed offset with zero data loss.

---

2. The Out-of-Order Update Hazard in Search Indexes

In distributed networks, CDC events may arrive at the search indexer out of order due to consumer group rebalances or parallel worker processing.

---

3. The Solution: Elasticsearch External Versioning (version_type=external_gte)

Elasticsearch provides native optimistic concurrency control via External Versioning:

How to Implement External Versioning:

Use the PostgreSQL Log Sequence Number (LSN) or transaction commit timestamp as the document version:

---

4. Handling Deletes: Tombstones and TTL Expirations

When a row is deleted in PostgreSQL (DELETE FROM orders WHERE id = 'ord_101'), Debezium generates a Tombstone Event:

1. It emits a delete event containing op: "d" and the before snapshot.
2. It immediately follows with a Null-Payload Tombstone Record (key = 'ord_101', value = null).

The Indexer Cleanup Handler:

---

5. Summary & Best Practices

1. Never Perform Dual-Writes in Application Code: Decouple search and cache updates using Kafka CDC for guaranteed delivery and zero write latency.
2. Always Use External Versioning on Downstream Sinks: Protect search indexes and caches from out-of-order event overwrites using database LSNs or monotonic sequence IDs.
3. Design for Eventual Consistency: Educate frontend developers to anticipate minor sub-second replication latency between write confirmation and search query visibility.