Context:
We are designing a new (micro)service and a suggestion was raised to use a separate database for the new service. What follows is my point of view with regards to sharing a single database VS having a dedicated database for each service.
Internal memo:
Schemas are fine(ish), splitting databases is not. By adding a second database:
- You lose the ability to perform ACID transactions.
- You lose referential integrity.
- You lose PITR.
These are major downsides. If someone suggested you to use a database for your new project that does not support even one of these, you would (hopefully) dismiss their advice (https://www.youtube.com/watch?v=b2F-DItXtZs).
To make things worse, it adds a ton of complexity:
- deployment & permission management is twice as complicated
- keeping database schemas in sync is a challenge
- comparing database states is time consuming
A non-technical problem that emerges over time:
- different databases / schemas adopt different conventions because they are seen as separate projects
- every project gets a new database because “clean start is easier”, which compounds all of the above
Microservice advocates might say:
first rule of microservice - every service has its own database
To that I say: the benefits of complete separation are a myth. Unless you are a separate business, it is an anti-pattern that leads to network-layer proxies that merge multiple databases or a ton of data duplication, e.g. payments system will need to access information about user's account, currencies, etc.
It also not the "first rule of microservices" that each service must have a separate database. In fact, shared database design and its pros are discussed in https://microservices.io/patterns/data/shared-database.html.
The intent behind the suggestion is good: separating databases ensures that other applications cannot perform side-effects that may change the behavior of microservices in unpredictable way. However, we can already achieve this with a well-defined access perimeter, which is achieved by being very explicit about what each (micro)service user can access.
I don’t support using separate schemas either, because it encourages lazy naming conventions, but since it does not break any of the 3 major downsides I mention, this is more of a preference than a technical guidance. With schemas, you end up with tables named like foo.project and bar.project, which isn't great DX. A single namespace forces to be thoughtful about it.
In short, setting up a separate database for a new project always sounds nice in theory, but in practice leads to a ton of overhead down the road. Keep things simple by using 1 database and give every user narrow permissions to make the changes to the database predictable.
Top comments (0)