The Synchronization Challenge: Near-Real-Time (NRT) Indexing
Primary transactional records live in relational or NoSQL databases (PostgreSQL, DynamoDB, MongoDB), while read queries target Elasticsearch or OpenSearch. Keeping the search index synchronized with the primary database under high write volumes presents severe race conditions and consistency challenges.
CDC Pipeline with Debezium and Kafka
Under the Hood: Solving Out-of-Order Updates
Because Kafka partitions are processed by concurrent worker threads, network delays can cause an older update (V₁) to arrive after a newer update (V₂). If the worker naively writes V₁, the search index is left with permanently corrupted stale data.
Source revisions and Elasticsearch concurrency tokens are different tools. A CDC event can carry a source-owned, strictly monotonic revision and use external versioning where supported. Elasticsearch's _seq_no and _primary_term are assigned by Elasticsearch and can guard a read-modify-write against the version already observed there; they are not primary-database sequence numbers. Wall-clock timestamps are unsafe unless the source guarantees monotonic, unique ordering. Whichever mechanism is used, deletes and updates must share one ordering rule.
Worked example: the out-of-order update
Product 42 changes price twice in quick succession. The database commits version 6 (price 20) then version 7 (price 18). Because of a retry, the sync worker receives version 7 first and version 6 second.
| Step | Naive worker | Worker with external versioning |
|---|---|---|
| Receive v7, price 18 | Index price 18 | Index with version 7; accepted |
| Receive v6, price 20 | Index price 20, now stale forever | Rejected with a version conflict because 6 is not greater than 7 |
| Final state | Wrong | Correct |
The version must be source-owned and monotonic per document, for example a row version column, or a log sequence number from the WAL. Elasticsearch supports this with version_type=external. Deletes also need versions; Elasticsearch keeps delete tombstones for index.gc_deletes, 60 seconds by default, so a very late update after that window can still resurrect a deleted document unless the pipeline guards against it.
Sync strategies compared
| Strategy | Consistency | Complexity | When it fits |
|---|---|---|---|
| Application dual write | Can diverge permanently on partial failure | Low | Prototypes only |
| Transactional outbox | Eventually consistent, ordered per aggregate | Medium | When you control the app schema |
| Log-based CDC such as Debezium | Eventually consistent, commit order | Medium to high | Multiple consumers, legacy apps |
| Periodic batch reindex | Stale by the batch interval | Low | Small catalogues, nightly freshness |
Rebuilding without downtime
- Create a new index, for example products_v2, with the new mapping.
- Record the current CDC offset, then bulk-load a snapshot into products_v2.
- Replay CDC from the recorded offset until lag is near zero; versioning makes the overlap safe.
- Atomically switch the read alias from products_v1 to products_v2, keep v1 for rollback.
Latency math: with a 500 ms bulk batch, Kafka lag near zero and a 1 s refresh interval, new writes typically become searchable in about 1 to 3 seconds. Setting refresh_interval lower increases segment churn and merge cost.