The Context: "The 3 AM Trigger"
It was October 2023. Our client "AgileLease" (name changed), an enterprise property management service, had a core AGREEMENTS table in their Oracle RAC database. The table had 400 columns, 12 nested JSON structures, and was updated 50 times a second. The problem wasn't the writes; it was the reads. Asset managers needed to search agreements using complex full-text queries like "Find all commercial leases expiring in 2024 containing the phrase 'force majeure'".
Executing these full-table scans with LIKE '%...%' on a massive transactional table caused instant CPU starvation. At 3 AM, a report ran that stalled the primary database entirely, preventing new leases from being signed globally. Total baigan index strategy. We needed to physically sever the read operations from the write operations.
The Problem Statement
Our constraints for the search architecture:
- Separation of Concerns: Complex searches must execute on a system optimized for inverted indexing (Elasticsearch), completely offloading the Oracle database.
- Latency: Searching must return results in <100ms.
- Sync: The read-model (Elasticsearch) must remain continually synchronized with the write-model (Oracle) with eventual consistency < 3 seconds.
- Data Mutability: Handle both updates and hard deletes seamlessly.
Visual Architecture
The Core Logic / Process
Data Flow
We implemented the Command Query Responsibility Segregation (CQRS) pattern. The application was split into two distinct APIs: The Command Service (for mutations) and the Query Service (for reads).
When a lease is updated, the Command Service commits a standard ACID transaction to the highly normalized Oracle Database. This satisfies the business logic invariants instantly. We rely on Debezium hooking into Oracle LogMiner to harvest the transaction and emit an event to a Kafka topic.
The Projection Service consumes this Kafka topic. It takes the highly normalized relational data and flattens it into a singular, denormalized JSON document representing the optimized 'View Model' for a lease. This document is then upserted into Elasticsearch using its native _bulk API.
Concurrency & Locking
In CQRS, the write side (Command Service) handles all pessimistic and optimistic locking natively within Oracle. The read side (Elasticsearch) requires zero transactional locking. Elasticsearch uses an implicit versioning system based on the _version field. We map the Oracle SCN (System Change Number), carried through the Debezium event, to the Elasticsearch explicit version. If out-of-order events arrive via Kafka retries, Elasticsearch rejects the older SCN, preventing the view model from travelling backward in time.
Failure Modes
What happens if the Projection Service goes offline? The view model (Elasticsearch) becomes temporarily stale, but the system doesn't crash. Asset managers can still search the leases as they existed 5 minutes ago. Because Kafka acts as a durable log buffer, when the Projection Service comes back online, it rapidly consumes the backlog and achieves eventual consistency within seconds.
The "Baigan" Take (Expert Insights)
Look, everyone loves CQRS in theory until they realize they have to handle "hard deletes."
When an admin deletes a lease in Oracle, there is no longer a row to derive a schema from. Debezium handles this by producing a "Tombstone" message (a message where the Kafka value is null). If you write your Projection Service carelessly, it throws a NullPointerException when trying to parse the deleted entity to update Elasticsearch.
The baigan trap is that you must configure a specific projection processor for tombstones. When the Projection Service encounters a null payload but sees the entity key in the Kafka metadata, it must explicitly translate that into an Elasticsearch DELETE /index/_doc/{id} command. Ignoring tombstones leads to a "zombie" read model that displays data that no longer exists in your actual source of truth.
Performance Checklist
- Completely segregate HTTP Command APIs from Query APIs into separate Spring Boot microservices.
- Use Kafka + Debezium as an event bus to sync the Read Model asynchronously.
- Explicitly map Oracle sequence numbers/SCNs to Elasticsearch explicit versions to handle out-of-order Kafka delivery.
- Build explicit logic in the Projection Service to handle Kafka Tombstone (null value) markers for data deletion.