If you have been reading about microservices and databases, most sources seem to (loudly) agree that each microservice should use its own database. This ensures that each service owns its own data, and helps keep the services decoupled. The services then typically communicate with one another using a service bus, with the messages being part of the services’ public API.

However, having separate databases is not a necessary requirement for achieving decoupling. Given some prudent design decisions, a single, shared database can alleviate a lot of the pain points of multiple databases while still allowing control over a service’s public/​inter-​service API.

What ensures a good separation between services is data ownership (data is only modified by one service) plus well-​defined, contractual public APIs. A database can be just another inter-​service API, not too different from service bus messages.

Note that below, I will use the phrase “public API” to cover both a truly public API like a REST API, as well as the inter-​service contracts such as messages sent over a service bus.

Please do not consider the length of pros/​cons lists to indicate the “score” of anything. A single pro may outweigh all the cons, and vice versa, and this may be different for you than for me. Furthermore, there may be points I don’t mention that are critical in your situation. Simply put, there is no TL;DR here.

Introduction

Forces

  • Services should be decoupled/​encapsulated:
    • Each service should be understandable in isolation: If you understand the well-​defined contracts by which the service communicates with the outside world, you should be able to understand the service fairly well. You should not have to consider the internal complexities of other services.
    • Each service should be maintainable in relative isolation: You could extend, refactor and (to an extent) rewrite a service without impacting other services or external clients.
  • Services need access to each other’s data. Here are some examples of external data access in increasing order of complexity, for a hypothetical to-​do service: 
    • ID only: Someone is assigning this task to the user with this ID; does that user exist?
    • Additional entity information: Get the name of the requesting user user so we can notify this other user that “<name here> assigned a task to you”
    • Complex queries: The user has selected a set of tasks in the front-​end, and the front-​end needs to display a list of users that can be assigned to all of these tasks. In the back-​end, this requires a join between data owned by the task service (“what are the access rules for the tasks in question?”), the authorization service (“which users satisfy which access rules?”), and the user service (the name, avatar URL etc. to be returned).
  • Performance is important.
  • Data consistency is important. (Some degree of eventual consistency must necessarily be tolerated among different services as long as any caching is involved, though cache invalidation can be done quickly enough that this isn’t a big problem.)

My context

  • There are many (logical) relations between entities across the entire service ecosystem.
  • There are many common concepts among the services. Examples: 
    • While services need different user data (e.g. some only need names, others also need emails and phone numbers), all services agree on what a user is.
    • Most services need access to fundamental permission data.
  • Currently, I’m the only back-​end developer working on the system. In the future, there will likely only ever be at most a handful of back-​end developers who can communicate easily.
  • Our primary benefit of using different services is encapsulating complex domain logic and (in time) having different devs on different services, not the ability to use separate technology stacks (e.g. separate DB technologies).
  • The company has always used SQL Server and has significantly more mindshare in this area than for other DB technologies. After creating a good handful of services, I’m confident SQL Server will work very well for this system, too. While it would be interesting to experiment with e.g. document databases, which may be a good fit in some cases, SQL Server works well enough that I don’t see a need to move away. It’s simply not a pain point, nor do I see it becoming one any time soon.
  • We are hosting the DBs (and services) on Azure.

Alternative 1: Completely isolated DB per service

This is the approach we started with. Cross-​service data access is handled by each service pushing (contractual representations of) updated data over a service bus. Services subscribe to the data they need and store a copy of it in their own database.

In this approach, the service’s contracts are the web API and the bus message interfaces (which contain complete entity DTOs).

The benefits of this approach are significant, but there are pain points.

Pros

  • Maximum decoupling; the service bus is the only interface between services.
  • Each service’s DB is purely an implementation detail.

Cons

  • ETL (moving large amounts of data between services) is difficult. This is needed when first deploying a service, or when a service needs additional external data (e.g. new fields for an external entity). Our system is currently still in beta and has very little data, and we use the service bus for ETL, but I don’t expect this to scale well for future loads. One could also use the public web API for ETL, but that has a lot of overhead, especially in terms of code.
  • There is duplicated data, meaning it can get out of sync. If you send a whole DTO for every e.g. UserUpdated message, then missed updates (e.g. a bug in the source service caused the message to not be sent) are temporary and easy to fix, but what about missed deletions? We currently have no solution for that (this issue is not critical for us at the current moment).
  • Each service needs a lot of boilerplate code to subscribe to and store external data. It’s fairly mechanical, low-​risk code, but it is still noise in every service that I’d be glad to get rid of.
  • Events can always arrive out of order (heck, even get sent out of order), so services can’t just store whatever comes in. They need to check e.g. the entity’s UpdatedAt field and only perform the update if they have older data (or, as I quickly discovered, older than or equal – otherwise nothing happens if you e.g. need to store new external fields and thus need to refresh all data).
  • Ad-​hoc queries that span multiple services is not possible; you have to create a separate service and duplicate all necessary data.
  • There is high friction for surfacing additional related data in a service’s web API (think GraphQL or JSON:API), since the data has to be synced (and all data refreshed when the field is initially added).
  • There may be issues with DB restore integrity; restoring a DB means data in the system is out of sync. (This is alleviated by having a simple way to refresh external data, but still means that individual services can’t be considered entirely in isolation.)
  • Potentially non-​trivial stuff such as full-​text indexes may need to be duplicated along with the data. (For example, in the complex user query I mentioned previously, the user can also type a name to search for. This means the task service has to support full-​text search on certain user fields, just like the user service itself does.)

Alternative 2: DB per service, but inter-​service read access to parts of it

This is the approach I have been refactoring towards for a few days. A service can treat part of its DB (e.g. views, functions, tables, certain columns) as a public API, and other services has read-​only access to this part of the DB.

The “entity updated” kind of service bus messages (e.g. UserUpdated) can then be simplified to only contain entity IDs instead of the whole entity, and will mostly be subscribed to for cache invalidation purposes. Some services still need to store external data locally for complicated queries, but they can just fetch the data directly from the other DB when they receive a message with the ID. Heck, you may not even need to use the message bus for this; I just learned that SQL Server can send notifications on an internal message broker when tables are updated.

What originally put me on this trail was the realization that it is data ownership plus well-​defined, contractual public APIs that ensures a good separation between services. I see few significant differences between guaranteeing an API by means of service bus message formats and guaranteeing an API as read-​only views/​functions/​tables/​columns in your database.

The only significant difference I can think of right now, is that the optimal indexing of a service’s database is now influenced by other services. This can be alleviated to a significant, maybe complete extent by 1) good communication between team members, and 2) having indexes and/​or query patterns be a part of the contract. Some discipline required, but not too much.

Yes, you’re also locked to a certain database vendor, but that goes for the service bus technology anyway, and it may not be a problem (it will likely not be a problem for us).

The database is no longer a pure implementation detail, but you can still refactor it as long as you keep the public API intact. This is helped by views, synonyms, computed columns, and row-​level security (filters). I do not think it will be more challenging to keep this API stable than to keep the message bus contracts stable. Heck, you can even switch out your DB technology completely as long as you keep the old one around and up-​to-​date with the public data. (Not saying it’s great, but it’s possible without breaking anything.)

Pros

  • ETL is simple, since you can read directly from the source DB.
  • There is less data duplication than with isolated DBs.
  • There is less data sync boilerplate code than with isolated DBs.
  • There is low friction surfacing additional related data in a service’s web API, since this can typically be read directly from another DB.

Cons

  • Complicated queries may still require a local copy of external data, so the problem isn’t gone completely. (While SQL Server support cross-​DB joins, this is limited on Azure and will have worse performance than DBs on a single “real” SQL server.) Also, if such complicated queries are needed in many services, the benefit to the system may not be as significant as you’d like.
  • Therefore, there may still be some data duplication.
  • Also therefore, some data sync boilerplate code may still be needed.
  • There are performance concerns in allowing other services to query your data. This can be alleviated by having indexes or querying patterns be part of the contract.
  • Ad-​hoc queries that span multiple services are still not possible.
  • There may still be issues with DB restore integrity.

To be clear, I would still go with alternative 2 over alternative 1. Based on my refactoring so far, alternative 2 does simplify quite a bit, and I see no reason to go back.

But why stop there?

Alternative 3: Single DB with per-​service schema, inter-​service read access to parts other schemas

Having had the realization that a public DB API is not significantly different from other public API (nothing that we can’t work with, at least), why not just use a single DB with one schema per service, and then open up parts of this schema to other services?

This is the approach that dawned on me while refactoring to alternative 2, and noticing that several services still required a local copy of some data for complex queries. It is what prompted me to think this through carefully and collect my thoughts in this blog post.

This approach means services can easily make complex queries involving (public) data managed by other services. This eliminates more or less all the cons so far.

Pros

  • ETL is not needed; all publicly available data can be queried and joined at its source.
  • There is no data duplication.
  • There is no data sync boilerplate code.
  • There is still low friction surfacing additional related data in a service’s web API.
  • Ad-​hoc queries are possible.
  • You can, if you want, have fully constrained data (foreign keys etc. between services). Foreign keys between data owned by different services definitely has some drawbacks, since a service is then no longer in full control of its own data, but that may be desirable in some cases for data consistency purposes. In any case, at least you have the option.
  • You can have DB transactions spanning several services. (We will likely not use this; any data modification generally has to go through the domain logic of the service that owns the data, since it generally does other work than simply update the DB.)
  • There are no issues with DB restore integrity.

Cons

  • There are still performance concerns in allowing other services to query your data. Again, though, this is not necessarily a problem.

Summary

Services have a public API – a contract they guarantee. Between services, this is typically message formats on a message bus. It can, however, just as well be part of a database schema. Changes to the public API must be managed with care in either case. SQL Server (and other database technologies) have tools like views, aliases, computed columns, and row-​level security filters that make it possible to preserve backwards compatibility while extending or refactoring the service’s DB schema, just like a service bus message contract can be kept stable while refactoring the service domain model.

Personal notes

I don’t think isolated DBs are a bad idea. There are good reasons it’s one of the most recommended approaches for microservices. I just think that, at least in our case, we can do better.

Of course I wish I had realized this at the outset, but that’s not how the world works. This realization is a result of all my current experience, including the pain points of isolated DBs. The services will still be decoupled. If I had started with a single database from the start, there is a lot of my current experience I would not have, and I think we would be at a worse place with the architecture of the system.

Arguments you may have have wanted to see

  • Pro separated DBs: “A single database is a single point of failure.” Yes, but 1) so is the service bus anyway, 2) DBs are generally very stable, and 3) unless you take care to run your different service databases on different servers or other “availability units” (we don’t), the DBs is likely a single point of failure anyway.
  • Pro single DB: “Duplicated data is inefficient in terms of storage space.” True, but at least for our system, I can’t see this becoming a problem within any reasonable time frame (performance-​wise or storage-​cost-​wise). Storage space may have been a problem 20 years ago, but not today.

Resources

Posted byChrister van der Meeren 9 minutes to readPosted inTech

Leave a comment

Your email address will not be published. Required fields are marked *

Notify me of via e-mail. You can also subscribe without commenting.

Your email will not be published. It may be used to look up your Gravatar, and is used if you subscribe to replies or new comments. The data you enter in this form may be shared with Akismet for spam filtering.