Do you need to have different database servers for each CQRS read model, or you can put them in the same database but in different tables.
You would normally expect different logical databases. They might happen to be hosted on the same durable storage appliance, but none of the code should expect that data to be stored in the same place.
If you have different databases then how are you supposed to join table.
You are supposed to NOT join the tables. Instead, you copy the data -- translating from the representation that makes sense for writing to the representation that makes sense for reading.
if we use the same database then wouldn't our microservices be tightly coupled and that would defeat the whole purpose of using this architecture
The coupling really comes from the assumption that the different services are touching the same rows. In other words, we don't want the processes communicating through the database. Instead, we pass messages between the services, and the services manage their own data.
If you get that right, then the fact that you happen to store that data in the same appliance is irrelevant, because your services never actually look outside of their own little bubble.
Another way of expressing the same idea: you should be able to change "your" schema without needing to coordinate with anybody else.
let’s say we have a users microservice and a posts microservice, how am I supposed to query the posts with each user that published them, how can I manage related data between different microservices?
You copy the data to a common database where you can then create the reports that you need.
