CQRS (Command Query Responsibility Segregation) is an effective pattern for scaling complex applications, but it introduces a significant challenge: data synchronization. If your read and write stores are out of sync, your application is effectively providing stale or incorrect information to your users. Balancing the speed of the read store with the consistency of the write store requires a two-tiered synchronization strategy.
In a project I’ve been working on, we needed a very performant search system (to ensure a high quality user experience, and because we had to manage a lot of data). It was a search system about hotel availabilities.
A Single RDBMS Wasn’t Enough
We have a table with the hotel definition. Linked to another table with the room definition. Linked to another table with the availability definition and price. Having 10,000 hotels leads to 10,000,000 availabilities if you open the booking window for the next 6 months.
| Hotel ID | Name | Category | City |
|---|---|---|---|
| 1 | Deluxe Hotel | 5 | Paris |
| 2 | Lovely Hotel | 3 | Madrid |
| Room ID | Hotel ID | Capacity |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 1 | 4 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| Availability ID | Room ID | Price | Date |
|---|---|---|---|
| 1 | 1 | 100 € | 01/06/2025 |
| 2 | 1 | 100 € | 02/06/2025 |
| 3 | 1 | 100 € | 02/06/2025 |
Crawling a table of 10 million rows is not easy. Adding some indexes may help. But what about search for a hotel in a given location? With a given category or room capacity? The database will need more space for the indexes than for the data itself.
Let’s Try a CQRS Architecture
Imagine refactoring the database to have a single table, let’s call it search-availabilities, which has all the searchable info. Add enough indexes and absolutely no joins. It will be a terrific table.
Implementing this on PostgreSQL, which is the main database, won’t be the best choice. So, we chose MongoDB as a read-only database for the search system. Adding the adequate indexes, even spatial indexes for geolocation, the result was perfect.
Let’s now tackle another problem. Once a user books a weekend, the data in PostgreSQL will be updated immediately, as it’s the main database. But the hotel will keep showing on the search results. So, we need both databases to be in sync.
Near-Real-Time Sync
To update the MongoDB database with the values written in the PostgreSQL database, we had multiple choices. The first, every time an update is done on PostgreSQL we replicate the change on MongoDB. This way, the MongoDB is perfectly updated. Here is the catch. The MongoDB has a lot of data, a lot of indexes, and mainly a lot of read operations. A single write will block the read operations for a while. If multiple write operations occur together, the benefits of having a dedicated read database are lost.
Another option is to lose a little bit the immediate update, and ensure the MongoDB database remains fast. How? We chose the asynchronous updates. Every time a write operations occurs on PostgreSQL, we publish a message to a message broker (Kafka, RabbitMQ, AWS SQS, GCP Pub/Sub…). And when the MongoDB database seems to have lower volume of read operations, it handles as much messages as possible. This way, the write operations are still fast (we don’t need to wait the write to occur on two databases), and the MongoDB just waits some seconds (or minutes) to be updated.
But we all know how asynchronous operations work: they’re triggered, but nothing validates that the operation occurred successfully. So, how can I ensure the MongoDB database is really updated?
Scheduled Batch Rebuilds to Correct Data Drift
Hopefully, the application is hosted and used in a single timezone. So we can rebuild the full MongoDB database at nights. This may take one or two hours. But at this time, the risk is lower. This way, we ensure the MongoDB data is up to date at least 24 hours after a write operation was performed.
But this solution also has a problem. What happens for the people booking a weekend at 3 in the morning? Will they see an empty search page and lose the booking opportunity? Well, let’s have a fallback mechanism. To simplify the batch operation, we created a view in the PostgreSQL database. This way, the batch just requests the view to fetch all the data. The same for the fallback search engine, fetch the view (with the criteria of the user) and the data will be returned (slower but there).
Balancing Performance and Data Consistency
Relying on a single synchronization method is a common architectural mistake. Incremental jobs provide the low latency required for a good user experience, while scheduled rebuilds provide the mathematical certainty required for business logic. Think of incremental sync as your daily maintenance and the scheduled rebuild as your periodic deep clean.
The Dev World - Sergio Lema 
Leave a comment