My team is migrating from a monolithic ASP.NET application to .NET Core and Kubernetes. The code changes seem to be going as well as can be expected but where my team is encountering a lot of discord is around the database.

We currently have rather large SQL Server database that houses all of the data for our entire business. I'm proposing that we split the database in a similar way to splitting the code--catalog data in one (logical) database, inventory data in another, orders in another, etc--and each microservice would be the gatekeeper for its database.

The implication here is that foreign keys that cross microservice boundaries would have to be removed and sprocs and views that reach across boundaries would be prohibited. All of the data models may or may not reside in the same physical database, but even if they do, they should not interact with each other directly. Orders might still reference catalog items by Id but the data integrity would not be strictly enforced at the database level and that data will have to be joined in code rather than in SQL.

I see the loss of these as necessary trade offs in moving to microservice and getting the scalability benefits that come with. As long as we choose our seams wisely and develop around them then it should be OK. Other team members are adamant that everything must stay in the same monolithic database so everything can be ACID and have referential integrity preserved everywhere.

This brings me to my question. First, is my stance on foreign key constraints and join plausible? If so, is anyone aware of any credible reading material I could offer to my colleagues? Their position is nearly religious and they don't seem like they will be swayed by anything short of Martin Fowler himself telling them they're wrong.

  • 7
    Referential integrity is extremely valuable. Is the scale of the database really the bottleneck here? Do you really need microservice-style scalability? You know better than me whether that change in architecture is appropriate for your organization, but please consider that it's simply not a good fit for many use cases. There might be other ways to scale with more attractive tradeoffs. E.g. if the database queries per second are too high, maybe database replication is all that's needed. And you can horizontally scale web servers without having to use microservices.
    – amon
    Sep 12, 2018 at 21:13
  • Good points. We're looking into some of those options for short term gains. The move to microservices is the long game, though. In my opinion, it will allow us to scale for years rather than months. Sep 12, 2018 at 23:03
  • 3
    Im sure you're customers will be thrilled that the order they placed 0.05ms faster gets canceled because someone else ordered the same product when there was only one left in stock.
    – Andy
    Sep 13, 2018 at 2:54
  • @amon Make this an answer and I'll upvote it. This is a good question and the pros and cons need to be fairly represented.
    – mcottle
    Sep 13, 2018 at 6:13
  • @mcottle ok, done!
    – amon
    Sep 13, 2018 at 9:52

3 Answers 3


There is no clear solution because this depends entirely on your context – in particular, along which dimensions your system is supposed to scale and what your actual problems are. Is the database really your bottleneck?

This (unfortunately rather lengthy) answer will read a bit like “microservices are bad, monoliths for life!”, but that's not my intention. My point is that microservices and distributed databases can solve various problems, but not without having some issues of their own. In order to make a strong argument for your architecture, you must show that these issues do not apply or can be mitigated, and that this architecture is the best choice for your business needs.

Distributed data is difficult.

The same flexibility that enables better scaling is the flip side of weaker guarantees. Notably, distributed systems are much much harder to reason about.

Atomic updates, transactions, consistency/referential integrity, and durability are extremely valuable and should not be waived rashly. There is little point in having data if it is incomplete, out of date, or outright wrong. When you have ACID as a business requirement but are using database technology that cannot offer it out of the box (e.g. many NoSQL databases, or a DB-per-microservice architecture), then your application must fill the gap and provide those guarantees.

  • This is not impossible to do, but tricky to get right. Very tricky. Especially in a distributed setting where there are multiple writers to each database. This difficulty translates to a high chance of bugs, possibly including dropped data, inconsistent data, and so on.

    For example, consider reading the Jepsen analyses of well-known distributed database systems, perhaps starting with the analysis of Cassandra. I don't understand half of that analysis, but the TL;DR is that distributed systems are so difficult that even industry-leading projects sometimes get them wrong, in ways that can seem obvious in hindsight.

  • Distributed systems also imply a larger development effort. To a certain degree, there's a direct trade-off between development costs or dropping money on beefier hardware.

Example: dangling references

In practice, you should not look at computer science but at your business requirements to see whether and how ACID can be relaxed. E.g. many foreign-key relationships might not be as important as they seem. Consider a product – category n:m relationship. In a RDBMS we might use a foreign-key constraint so that only existing products and existing categories can be part of that relationship. What happens if we introduce separate product and category services, and a product or category is deleted?

In this case, that might not be a big problem and we can write our application so that it filters out any products or categories that no longer exist. But there are tradeoffs!

  • Note that this might require an application-level JOIN over multiple databases/microservices, which merely moves processing from the database server to your application. This increases total load and has to move extra data through the network.

  • This can mess with pagination. E.g. you request the next 25 products from a category, and filter out unavailable products from that response. Now your application displays 23 products. In theory, a page with zero products would also be possible!

  • You will want to occasionally run a script that cleans up dangling references, either after each relevant change or on regular intervals. Note that such scripts are fairly expensive because they have to request every product/category from the backing database/microservice to see whether it still exists.

  • This should be obvious, but for clarity: do not reuse IDs. Autoincrement-style IDs may or may not be fine. GUIDs or hashes give you more flexibility, e.g. by being able to assign an ID before the item is inserted into a database.

Example: concurrent orders

Now instead consider a product – order relationship. What happens to an order if a product is deleted or changed? Ok, we can simply copy the relevant product data into the order entry to keep it available – trading disk space for simplicity. But what if the product's price changes or the product becomes unavailable just before an order for that product is made? In a distributed system, effects take time to propagate and the order will likely go through with outdated data.

Again, how to approach this depends on your business requirements. Maybe the outdated order is acceptable, and you can later cancel the order if it cannot be fulfilled.

But maybe that's not an option, e.g. for highly concurrent settings. Consider 3000 people rushing to buy concert tickets within the first 10 seconds, and let's assume a change in availability will require 10ms to propagate. What is the probability of selling the last ticket to multiple people? Depends on how those collisions are handled, but using a Poisson distribution with λ = 3000 / (10s / 10ms) = 3 we get a P(k > 1) = 1 - P(k = 0) - P(k = 1) = 80% chance of collision per 10ms interval. Whether selling and later cancelling the majority of your orders is possible without committing fraud might lead to an interesting conversation with your legal department.

Pragmatism means cherry-picking the best features.

The good news is that you don't have to move to a distributed database model, if that's not required otherwise. No one will revoke your Microservice Club membership if you don't do microservices “properly”, because there is no such club – and no one true way to build microservices.

Pragmatism wins every time, so mix and match various approaches as they solve your problem. This could even mean microservices with a centralized database. Really, don't go through the pain of distributed databases if you don't have to.

You can scale without microservices.

Microservices have two major benefits:

  • The organizational benefit that they can be developed and deployed independently by separate teams (which in turns requires the services to offer a stable interface).
  • The operational benefit that each microservice can be scaled independently.

If independent scaling is not required, microservices are a lot less attractive.

A database server already is a kind of service which you can scale (somewhat) independently, e.g. by adding read replicas. You mention stored procedures. Reducing them might have such a large effect that any other scalability discussions are moot.

And it's perfectly possible to have a scalable monolith which includes all services as libraries. You can then scale by launching more instances of the monolith, which of course requires each instance to be stateless.

This tends to work well until the monolith is too large to be reasonably deployed, or if some services have special resource requirements so that you might want to scale them independently. The problem domains that involve extra resources might not involve a separate data model.

Do you have a strong business case?

You are aware of the business needs of your organization, and can therefore create an argument for a database-per-microservice architecture, based on an analysis:

  • that a certain scale is required, and this architecture is the most cost-effective approach to obtain that scalability, taking into account the increased development effort for such a setup and alternative solutions; and
  • that your business requirements allow relevant ACID guarantees to be relaxed, without leading to various problems like those discussed above.

Conversely, if you are unable to demonstrate this, in particular if the current database design is able to support sufficient scale into the future (as your colleagues seem to believe), then you also have your answer.

There's also a big YAGNI component to scalability. In the face of uncertainty, it is a strategic business decision on building for scalability now (lower total costs, but involves opportunity costs and may not be needed) versus deferring some work on scalability (higher total costs if needed, but you have a better idea of the actual scale). This is not primarily a technical decision.

  • Excellent answer, thank you. Can you elaborate on this statement? Do you mean reducing the number of procedures might have a large effect on performance? You mention stored procedures. Reducing them might have such a large effect that any other scalability discussions are moot.
    – alan
    Oct 30, 2019 at 21:06
  • 2
    @alan Stored procedures can be used for good, but they pose two performance problems: (1) More complex queries are more difficult for the database to optimize. (2) Using sprocs means doing more work on the DB server. OP wants to split up the DB in order to scale further, but avoiding complicated sprocs might already provide that headroom. Of course, sprocs and complex queries can also be good for performance, e.g. when they minimize the amount of data that must be transferred out of the DB for a query response. Splitting the DB would make that problem worse when cross-server JOINs are needed.
    – amon
    Nov 2, 2019 at 14:06

I believe both approaches are plausible. You can choose to gain scalability sacrificing the benefits of ACID and monolithic databases, as well as keep with the current architecture and sacrifice the scalability and agility of a more distributed architecture. The right decision will come from the current business model and buz strategy for the next years. Purely from the technology point of view there are pains keeping it monolithic as well as moving to a more distributed approach. I would analyze the system and see what applications/modules/business processes are more critical to scale and evaluate the risks, costs and benefits to decide the ones that should wait or continue in the monolithic architecture.


Your stance is plausible and correct.

How to convince dyed in the wool db addicts though is another question. I would say you have two options.

  1. Find a concrete example where the DB has reached its limits. Do you have 'archive tables' for example? Why is that ok? What is the maximun number of orders per second you can take? etc. Show that the DB doesnt meet requirements and your solution fixes them.

  2. Hire expensive contractors to come in an tell you the best solution. Because they are expense and have blogs everyone will believe them

  • 1
    I'm not the -1 but for this to be a good answer I'd lose the snark of point 2 and expand on when it would be appropriate to need multiple databases. I don't think archive tables are necessarily an antipattern in databases that don't support, partitioning. The database I'm currently using has around 130GB of data with 26 tables >10M rows and performance is not remotely enough of an issue to need to split the database; so I'm highly skeptical and I'd like to hear why this is a good idea, and when it needs to be done - this answer is the closest I've seen so far.
    – mcottle
    Sep 14, 2018 at 4:46
  • well. I mention archive tables because they muck up FK constraints. its a chink in the armour. splitting by microservice isnt a size of db thing its a keeping your microservices seperable thing. If you can't turn one off and throw it away, its not really a microservice. re point 2. the OP mentions MF, they could literally hire him/thoughtworks to come and tell them to split the db
    – Ewan
    Sep 14, 2018 at 6:46
  • "If you can't turn one off and throw it away, its not really a microservice." That's true of the service itself, but not necessarily an argument for why the service needs it's own database. Ultimately, the database is itself a service being used by the microservice. The microservice doesn't really know or care whether the data it's utilizing is in a separate database or a shared database. You can spin up or down copies of this microservice and nothing really changes. Feb 27, 2019 at 19:36
  • The best argument for database-per-service is connection limits. It's not uncommon for connection pooling to be used, so each microservice already requires multiple connections to the database instance, then you could have multiple instances of each of these microservices, each with their own pools. Eventually things could come to a head, where you've simply exhausted the ability of the database to handle all the connections it's getting. Feb 27, 2019 at 19:38
  • 1
    Unfortunately I've seen point 2 in action: Management/Product are often swayed easier by a visit from a knowledgeable contractor that they spent good money on, just to tell them that their teams were right in their analysis. There's nothing bad about it, I've worked with some excellent contractors that have a sharp eye for identifying problems
    – Yarek T
    Oct 26, 2022 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.