apache kafka – How to design a durable and consistent event bus for a read repository synchronization

Suppose you have a complex aggregate in your systems constructed from multiple entities. This structure is saved in your RDBMS in multiple tables, hence when you do a write you have to update them all, and when you make a read you have to make multiple joins on these tables. We face the same issue and we need to scale our system.

We figured it’s better to separate the reads from the writes, so we can save the data in a denormalized matter in a single place for faster retrieves. On paper, the general idea seems simple: construct an event bus and publish an event on it every time something gets updated or a new thing is added(state changes/commands). The event bus pushes the data into a durable queue and a consumer pops the data from the other side. The consumer then inserts the data into an appropriate database(which we decided to use Elasticsearch).

Here are the requirements:

1. The sequentiality of the events is important
2. No events should be lost
3. Eventual consistency is ok but it shouldn’t take too much time for the changes to take place

My question is, what is the best choice for the queue service(nats, RabbitMQ, Kafka, etc …) and how should the event bus be designed? by design, I mean the actual components of the systems that deliver an event from any producer to the consumer and make sure that if something fails (even at the moment of inserting into the second database) event sequentiality, durability, and data consistency are guaranteed.