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.