I think a different framing for the question might be more helpful. What is your overall goal? You cannot have everything. In fact, if you try to have everything, you will get nothing.
I would say that 99% of time the implicit goal is to cut down development time. And the best way to cut development time on long-term is to cut down complexity.
To cut down complexity, we should avoid complex problems, use existing solutions to solve them or at least be able to contain them. Sometimes, the price is that you need to solve some easier problems yourself.
For example, microservice architectures promise that you need less coordination between teams, because parts of the systems can be deployed independently. The price is that you cannot use database transactions to guarantee integrity.
I think data integrity is almost always much more important problem to solve, partly because it is so difficult to solve by yourself. Actually it is often so difficult that most people just ignore it.
For example, if you adopt microservices architecture, you often just ignore data integrity, and call your system "eventually consistent". Practically this means that you push the data integrity problems to the sink system.
It is better to think of data integrity as a meta-feature, rather than a feature. Having data integrity helps you in making other features of your system more simple. For example, migrating schema changes in your system is much more manageable if you use a database which can handle the migration within a transaction.
In your example, there are various ways where system can be left in an inconsistent state after a crash, even if the database is the "source of truth". For example, do you always reconstruct the ES cache after a crash? If not, how do you know whether it contains inconsistencies? Whose job is it to initiate the reconstruction? etc.
I would say that 99% of time the implicit goal is to cut down development time. And the best way to cut development time on long-term is to cut down complexity.
To cut down complexity, we should avoid complex problems, use existing solutions to solve them or at least be able to contain them. Sometimes, the price is that you need to solve some easier problems yourself.
For example, microservice architectures promise that you need less coordination between teams, because parts of the systems can be deployed independently. The price is that you cannot use database transactions to guarantee integrity.
I think data integrity is almost always much more important problem to solve, partly because it is so difficult to solve by yourself. Actually it is often so difficult that most people just ignore it.
For example, if you adopt microservices architecture, you often just ignore data integrity, and call your system "eventually consistent". Practically this means that you push the data integrity problems to the sink system.
It is better to think of data integrity as a meta-feature, rather than a feature. Having data integrity helps you in making other features of your system more simple. For example, migrating schema changes in your system is much more manageable if you use a database which can handle the migration within a transaction.
In your example, there are various ways where system can be left in an inconsistent state after a crash, even if the database is the "source of truth". For example, do you always reconstruct the ES cache after a crash? If not, how do you know whether it contains inconsistencies? Whose job is it to initiate the reconstruction? etc.