I am working on a project where I would like to use this pattern. I have read for days, but I am still not sure how to implement this pattern properly and keep the multiple DBs in sync.
Let's suppose the following (this is just an example to demonstrate):
We have a simple REST service (spring-boot java app): it receives a date and a water-meter status (the current consumption number). The service saves the new reported water meter status if the incoming date/status is after the last recorded consumption.
Get requests are served via HTTP REST GET endpoint and save/write requests arrive at the service via Kafka topics (using the message/command pattern).
This app is installed on multiple host machines. Each app uses its local database: instance-A and instance-B
The endpoint rest URLs are registered automatically to a service registry (consul).
Instance-A saves the new consumption properly.
But instance-B throws an exception while trying to save the same incoming data. Instance-B starts handling the error: it rolls back the message, repeats the process 10 times, then sends the incoming request to a dead-letter-topic or does something similar.
In the meantime - while instance-B is playing the error handling game -, the same customer ID asks to see the last recorded consumption. 1st request arrives at GET endpoint-A (URL is coming from service-registry, round-robin) and the service provides a valid response with the latest consumption.
The next request from the same customer arrives at GET endpoint-B. This provides a different response (previous consumption) until the error that appeared in step 6 is fixed somehow, manually maybe.
-- What is the solution here? --
One solution that comes up in my mind is this: When the exception appears in step 6, the service cuts automatically its HTTP GET connection using its own circuit breaker (or whatever), and the service registry automatically unregisters this particular unavailable (but still running) service URL. So no more GET requests will be served by this instance until the issue is fixed somehow.
Maybe Kafka connection must be cut as well, to prevent saving incoming data in the wrong order! Because message orders are important. But not sure.
-- Is this pattern too complex for small projects? --
But this is a complex flow, and I think projects with limited resources do not have enough time/people/budget to implement this properly.
So maybe this pattern can only be used by huge companies?
Better if small projects pay more for a powerful database server and put this giant one big DB behind all instances of the same microservices.
-- Question --
Is there any simple way to keep sync automatically multiple databases behind multiple instances of the same microservices?
Lets check the pattern you refer: https://microservices.io/patterns/data/database-per-service.html
It does not say that we should use separate database per instance of the same microservice. It is "database per service", not "database per instance".
Iff you really need a local database per instance, then look for other pattern. Alternatively, reconsider if you really need the local database, maybe you'll come up with simpler architecture?