Skip to content

Trade-offs · Senior and Staff

Shared database vs database per service

When to let services share one database and when each should own its data, with connection, join, cost and team-speed numbers.

Asked at: SeniorStaff

The decision

When a system is split into services, you must decide where the data lives. In a shared database, many services read and write the same tables. In a database per service, each service owns its data, and others reach it only through the service's API or events. The shared way keeps joins and transactions simple. The separate way lets each team change its schema and scale on its own, but a query that was one join becomes calls between services. A middle path exists: one database server with a separate schema for each service, and no service reads another's tables.

Choose a shared database when

  • You have one team, or a few teams that sit together and release together.
  • Business rules need transactions across tables that belong to different services, and you cannot accept the middle states of a saga.
  • The system is young. The boundaries between services are still moving, and the cost of getting them wrong is high.
  • Load is moderate, so one database server handles it with room to spare.

Choose a database per service when

  • Different teams release at different speeds, and schema changes are blocked by other teams.
  • One service has a very different load or data shape, such as a feed or a search index, and needs another kind of store.
  • One service's heavy queries slow the others, or the database is the shared failure point.
  • You have a clear owner for each set of data, and the boundaries have been stable for several months.

The numbers that change the answer

All of these are Assumptions unless marked as cited or calculation.

Connections (calculation, plus a cited default). Take twelve services, each with 3 instances, each with a pool of 20 connections. That is 12 x 3 x 20 = 720 connections to one database. The PostgreSQL manual says the default maximum is typically 100 connections (see Sources). You can raise it or add a connection pooler. But each connection uses memory, and 720 is a warning sign. With a database per service, each one sees 3 x 20 = 60.

Joins become calls (calculation). A page lists 50 orders and needs each customer's name. In one database, one join takes about 5 ms. With separate services, one call per order at 3 ms each takes 50 x 3 = 150 ms if done one after another. One batch call for 50 customers takes about 8 ms. So the loss is small if you design the batch call, and large if you do not. A stored copy of the customer name in the order service needs no call at all, but it can be out of date.

Availability (calculation). Assumption: each database is up 99.95 percent of the time. Say one request needs three services, each with its own database. All three must be up: 0.9995 x 0.9995 x 0.9995 = 0.9985, which is 99.85 percent. That is about 65 minutes of downtime a month (0.0015 x 43,200). With one shared database, the same request needs one database: 99.95 percent, about 22 minutes. Separate databases do not improve availability for a request that needs all of them. They help only when a service can work without the others.

Cost (assumption). One shared database server costs Rs 4 lakh a month (one lakh is 100,000). Twelve smaller servers cost Rs 45,000 each, which is Rs 5.4 lakh. That is 35 percent more. The cited Azure guidance notes that services can safely share the same physical server. The problems come when they share schemas or tables. A server with one schema per service gives clear ownership at close to the shared price. It does not give separate load or failure isolation.

Team speed (assumption). A column change in a shared table needs coordination with every service that reads it. Assumption: 4 services read it, each needs a release, and the change takes 2 weeks from plan to the end of the old column. With a private database, a team changes its own table in days and publishes the change in the API. If your teams make 10 such changes a quarter, shared costs 10 x 2 = 20 weeks of waiting, spread across teams.

Cited fact. Microsoft Azure guidance says two services should not share a data store. A schema change must then be coordinated across every service that uses it (see Sources). This is guidance for microservices, not a law. The list "Choose a shared database when" shows when sharing is fine.

What breaks with each choice

Shared database.

  • Hidden coupling: a service reads another's table, and a rename breaks it. Fallback: schemas for each service, grants that block cross-schema writes, and views for what is shared.
  • One bad query or one migration slows everyone. Fallback: separate users with limits, a replica for reports, and a review for every migration.
  • The connection limit. Fallback: a pooler and a cap on each service.
  • A single failure point. Fallback: replicas and a tested failover.

Database per service.

  • Data that appears in many places can disagree. Fallback: one owner for each fact, events to update copies, and a daily check.
  • No transaction across services. Fallback: sagas with undo steps, and short in-between states.
  • More databases to run, patch and back up. Fallback: one template and automation.

What it costs

  • Money: about 35 percent more in the example (Rs 5.4 lakh against Rs 4 lakh), or near zero if you only split schemas on one server.
  • People (Assumption): splitting one service's tables out takes 2 to 6 engineer-weeks, including the data copy, the API and the tests. A saga for each cross-service transaction takes about 2 to 3 engineer-days per step.
  • Changing your mind: shared to separate is a project for each service, done one at a time. Separate back to shared is rare, because the services have by then built their own models. This is why the first step should be reversible.

How each level answers

SDE-1: A shared database is one database used by many services. A database per service gives each its own data.

SDE-2: I give each service its own data so teams can change schemas alone. I replace joins with API calls or a stored copy. I use events to keep copies up to date.

Senior: With 12 services on one database I would have 720 connections against a default of about 100, so I would either pool or split. Splitting costs about 35 percent more. A page of 50 orders must use one batch call of 8 ms, not 50 calls of 150 ms in total. I accept sagas for cross-service writes. A request that needs three databases is up 99.85 percent, not 99.95, so I make optional data optional.

Staff: I look at where teams wait on each other. If more than a third of schema changes need other teams (my threshold, an assumption), the shared database slows the company more than it costs. I start with a schema for each service on the same server, block cross-schema writes, and give each table one owner. I move a service to its own server when its load or its release speed needs it. I own the rule with an architecture group. I track cross-service schema changes, connection count and slow queries. I revisit it each year as team count grows.

Say it in the interview

With one team and a young product I would keep one database server but give each service its own schema and block writes across schemas. That gives clear ownership at almost the same price, and it can be split later. I would move a service to its own server when its load hurts the others. I would also move it when teams wait on each other for more than a third of their schema changes. The risk is cross-service transactions, so I would use sagas and one batch call, not one call for each row.

Common mistakes

  • Splitting the services but sharing the tables. You pay the cost of services and keep the coupling.
  • Replacing every join with a loop of calls. Fifty calls of 3 ms is 150 ms. Batch them.
  • Expecting separate databases to raise availability. A request that needs three services is up less often, not more.
  • Splitting too early. If boundaries still move, you will move the data twice.

Sources

Understand the trade-off. Practise explaining your choice.

In an AI mock interview, the interviewer can push on a choice like this one: it changes a number and asks if your answer still holds.

See the mock interviewsAll trade-offs