Single database vs multiple databases - Microservices Architecture

Question

I am planning to build a project using micro-services architecture. I was curious to know which design would be better in terms of database?

1. Keep a separate database for each service like in the given image

2. Multiple applications point to 1 database.

If I keep separate database for each service, how do we decide where to keep the mapping tables?

For example, we have Customers service (separate project talking to DB 1) and Products service (separate project talking to DB 2), where should I store customer and product mapping (products bought by a customer)?

How about reporting in the longer run where I would need joins over a number of tables (that are in different databases due to Architecture mentioned in point 1)?

Answer 1

You can use a single shared database with tables that owned by different microservices if your data is heavily related. Also in case you have strong requirements on data consistency and availiblty of a service. There is pros and cons.

Pros

Reliability

• You can have totally consistent incremental backup of the entire system without downtime with standard DB tools.
• In case of event-driven architecture you can store events with your data. If you store events in DB you can even lose your broker and survive without data loss.
• Fully constrained and correct data relations.

Maintenance:

• You don't have to write network interfaces to fetch data from another service, but you can if you want that level of separation!
• No data duplication and stale states.

Features:

• Complex queries are possible and damn fast!
• Transactions are not so hard. (Retries are still required).

Cons

Reliability:

• Another single point of failure(In simple case). You can use a replicated setup for HA, but it requires OPS resources.

Maintenance:

• Schema changes(migrations) should be made very carefully(as external API changes) even with single writer principle implemented because schema missmatch on read is also a permanent failure. With big project its hard to track who will be affected by table change.

Discipline:

• Only owner of table can perform write operations on it.
• You still need to push hard to decouple your data to lower rate of transactional conflicts.

Performance:

• It hard to scale in terms of storage capacity and performance. There is a clustering solutions in almost any mature DB but as ia said above it requires much of OPS resources

Hate:

• Everyone will hate you for no reason when you'll say that you designed your microservices to use single database. Bear with it.

Other thoughts:

In my personal expirience single database is a good choice if you're not a Netflix and your application can not tolerate any form of data loss.

1. "Shared database" is actually described as "Architecture pattern" on microservices.io with it's own pros and cons. So why put "anti" label on it?
2. This and this articles give the opposite point of view on shared database pattern. Which totally makes sense from my point of view.
3. This document describes the BAC theorem. It basically means that you can't have a full uptime AND consistent backup at the same time if you use multiple data storages.

Answer 2

The 2nd approach is an "Shared database" anti-pattern in the Microservices architecture.

In a micro-service architecture, it is preferable to use the Database per service pattern.

There are also advantages and disadvantages of these approaches in the links above at the end of the pages.

In response to your question about where to store the products purchased by the customer, you need to create a new microservice that will store the products purchased by the customer with its own database.

For Analytics and statistics, you need to send data from all databases to the reporting module. In other words, you need to build an etl process. A message broker, such as Apache Kafka, is usually used for this purpose. This page describes this approach well.

Building a micro service architecture is a very complex and extensive task, and it is associated with many problems and design patterns that allow you to minimize these problems. I recommend reading a book "Microservices patterns" that looks at various patterns of micro-service architecture design.