What does splitting a shared database across microservices actually involve?

A shared database becomes a problem when several services read and write the same tables. A column rename in the orders schema can break the billing service. A slow report query can starve checkout of connections. No single team can approve a migration on its own, so schema changes need cross-team coordination and are often released together. The services deploy separately, but they still behave like one system.

The goal of database per service is that each table has exactly one owning service, and every other service reaches that data only through the owner's API or published events. It is tempting to treat this as a data migration: copy tables into new databases and repoint connection strings. That ordering is what causes rollbacks. If you move data while other services still query it directly, every unknown join, reporting job, and ad hoc script breaks at cutover. You only find them in production, and the quickest fix is to move the data back.

Do the split as a sequence of small, reversible ownership transfers:

  1. Establish owners. Assign every table to one service and one team.
  2. Route all access through them. Remove direct reads and writes from non-owners while the data still sits in the shared database.
  3. Move data. By this point only the owner touches the tables, so moving them is an internal change.

This guide is for teams whose services are coupled through one database and who need independent deploys and independent schema changes. The payoff is concrete:

  • Each team can run schema migrations on its own schedule.
  • On-call ownership is clear when a table misbehaves.
  • A bad query or failed migration has a smaller blast radius.
[0] Today: every service reads and writes every table
 +--------+  +---------+  +---------+
 | Orders |  | Billing |  | Reports |
 +--------+  +---------+  +---------+
     |            |            |
     +------------+------------+
                  v
 +--------------------------------------+
 | Shared DB    [orders]   [invoices]   |
 +--------------------------------------+
[1] Establish owners: orders -> Orders team, invoices -> Billing team
[2] Route all access through owners; data still in the shared DB
 +---------+  API/events  +--------+  API/events  +---------+
 | Billing |------------->| Orders |<-------------| Reports |
 +---------+              +--------+              +---------+
      |                       |
      v                       v
 +--------------------------------------+
 | Shared DB    [invoices]  [orders]    |
 +--------------------------------------+
[3] Move data: an internal change, no caller is affected
 +---------+  API/events  +--------+  API/events  +---------+
 | Billing |------------->| Orders |<-------------| Reports |
 +---------+              +--------+              +---------+
      |                       |
      v                       v
 +------------+          +-----------+
 | Billing DB |          | Orders DB |
 +------------+          +-----------+

Splitting a shared database as ownership transfers: assign an owner to each table, route all non-owner access through the owner's API or events while the data stays put, and only then move each owner's tables into its own database.

Who should use database per service?

This approach fits organizations where the shared database has become the main thing stopping teams from working independently. Typical signs:

  • Several teams change the same schema. Migrations need sign-off from people outside the team that wrote them, and releases get bundled to avoid breaking each other.
  • Workloads interfere with each other. Reporting or batch jobs compete with latency-sensitive paths such as checkout for connections, locks or I/O.
  • Incidents have unclear ownership. When a table bloats or a query plan regresses, nobody is sure whose pager should go off.
  • Services need different scaling or storage choices. One domain needs heavy write throughput, another mostly serves reads, and one instance cannot be tuned for both.
  • Domain boundaries are reasonably stable. You can say which service enforces which business rules, even if the code does not reflect it yet.

It is a poor fit for a single team running a few services, for systems whose core rules need ACID transactions across entities that would end up in different services, and for teams without the operational capacity to run several databases. Those cases are covered in more detail in the section on when to avoid splitting. Many teams in the middle get most of the benefit from the first two steps, ownership plus enforced access, and never need separate database instances.

How does the ownership-transfer approach work?

The method separates three things that a naive migration does at once: deciding who owns data, changing how others reach it, and physically moving it.

  1. Discover the real coupling. Give each service its own database login, log statements with the role that ran them, and query the system catalog for foreign keys, triggers, views and stored routines. This produces an inventory of who touches what.
  2. Assign one owner per table. The owner is the service that enforces the table's invariants. Record this next to the migrations and make the owning team a required reviewer.
  3. Enforce ownership inside the shared database. Move each service's tables into its own schema, grant full rights only to the owner's role, and record every remaining non-owner grant as a tracked exception.
  4. Replace each exception. Cross-service reads become either API calls to the owner or local read models fed by the owner's events. The events are published through a transactional outbox or change data capture, and consumers apply them idempotently. Cross-service foreign keys become write-time checks plus periodic reconciliation. Multi-service writes become sagas with compensating actions.
  5. Move data one bounded context at a time. Expand into the new database, propagate changes, backfill and verify, switch reads and then writes behind flags, soak through a full business cycle, and only then drop the old tables.

Every step before the physical move is reversible with a grant or a flag flip, and by the time data moves, only the owning service is affected. The following sections walk through each step.

How do you map coupling and assign schema ownership?

Start with evidence rather than architecture diagrams. Give each service its own database credentials, even while everything still points at one database. Then the query log tells you exactly which service touched which table. Shared credentials hide consumers, and the consumers you miss are the ones that break on cutover day. Capture a full business cycle, including month-end jobs, so infrequent batch work shows up.

Logs only show runtime traffic, so also scan the schema itself:

Coupling source How to find it Why it matters
Cross-table joins Query logs, ORM code Each one becomes an API call or a replicated read model
Foreign keys System catalog They enforce integrity across what will become a network boundary
Triggers System catalog They make hidden writes to other tables
Views, stored procedures System catalog They embed logic that no service repo shows
BI and reporting queries Analyst credentials, scheduled jobs They are consumers nobody lists as dependencies

Assign each table to the service that enforces its invariants, not the one that writes to it most often. If billing decides whether an invoice total is valid, billing owns invoices, even if an import job inserts most of the rows.

Some tables are genuinely shared, for example a customers table where identity and credit limits come from different domains. For those, split ownership by column and plan to separate the table later.

Record ownership in the repository that holds the migrations. Use a per-table ownership file mapped to a code-owners rule, so that any migration that touches a table needs approval from the owning team. Without that review gate, ownership is only a convention.

-- PostgreSQL: expose consumers, then inventory coupling the query log cannot show.

-- 1. One login per service, even though all still share this database.
CREATE ROLE billing_svc  LOGIN PASSWORD 'placeholder-billing';
CREATE ROLE identity_svc LOGIN PASSWORD 'placeholder-identity';
CREATE ROLE bi_reporting LOGIN PASSWORD 'placeholder-bi';

-- 2. Tag every logged statement with the role that ran it (%u).
--    Leave this on for a full business cycle, including month-end jobs.
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_line_prefix = '%m [%p] user=%u db=%d ';
SELECT pg_reload_conf();

-- 3. Foreign keys: integrity rules that will cross a network boundary.
SELECT conrelid::regclass AS child_table,
       confrelid::regclass AS parent_table,
       conname
FROM pg_constraint
WHERE contype = 'f';

-- 4. Triggers: hidden writes that no service repo shows.
SELECT tgrelid::regclass AS on_table, tgname, tgfoid::regproc AS trigger_function
FROM pg_trigger
WHERE NOT tgisinternal;

-- 5. Views and the tables they read.
SELECT view_schema, view_name, table_schema, table_name
FROM information_schema.view_table_usage
WHERE view_schema NOT IN ('pg_catalog', 'information_schema');

-- 6. Functions and procedures mentioning a candidate table (crude text match).
SELECT p.oid::regprocedure AS routine
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND p.prosrc ILIKE '%invoices%';

PostgreSQL statements that give each service its own login, tag logged queries with the role that ran them, and query the system catalog for foreign keys, triggers, views and routines that couple tables.

How can you enforce data ownership before physically splitting the database?

Ownership on paper becomes ownership in the database once you move each service's tables into its own schema and give each service its own login role. The owning role gets full rights on its schema, and every other role loses them. In standard SQL this looks like REVOKE ALL ON orders.order_line FROM PUBLIC; followed by GRANT SELECT, INSERT, UPDATE, DELETE ON orders.order_line TO orders_svc;. Schema-level and default-privilege syntax differs between engines, so check your engine's documentation before you script it.

Non-owners start with no access. Where a consumer still needs to read, grant it explicitly, for example GRANT SELECT ON orders.order_line TO billing_svc;. Record each of these grants as a tracked exception with an owner and a planned replacement. That list of exceptions becomes your backlog for the next section's work on joins and foreign keys.

This step is cheap because no data moves and no connection strings change beyond credentials. It is also reversible, since a single GRANT undoes any revoke. Its main value is that it reveals the real blast radius. Permission errors in staging show you every hidden reader: forgotten batch jobs, reporting scripts, and ORM lazy loads that cross boundaries. Static analysis tends to miss these. Finding them now costs a log search. Finding them after a physical split costs an incident.

For a small team running a handful of services, enforced schemas with a short exception list may be enough isolation. In that case, stopping here is a reasonable outcome, not an unfinished project.

-- PostgreSQL syntax; schema and default-privilege commands differ on other engines.
-- 1. One schema and one login role per service (placeholder password).
CREATE SCHEMA orders;
CREATE ROLE orders_svc LOGIN PASSWORD 'change-me-orders';
CREATE ROLE billing_svc LOGIN PASSWORD 'change-me-billing';

-- 2. Move the owned table into the service's schema. No data is copied.
ALTER TABLE public.order_line SET SCHEMA orders;

-- 3. Remove implicit and inherited access for everyone else.
REVOKE ALL ON SCHEMA orders FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA orders FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA orders FROM billing_svc;

-- 4. The owning service gets full DML rights on its schema.
GRANT USAGE ON SCHEMA orders TO orders_svc;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA orders TO orders_svc;

-- Tables created later by the migration role inherit the same rule.
ALTER DEFAULT PRIVILEGES IN SCHEMA orders
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO orders_svc;

-- 5. Tracked exception: billing still reads order lines.
-- Owner: billing team. Replace with orders API or event feed.
GRANT USAGE ON SCHEMA orders TO billing_svc;
GRANT SELECT ON orders.order_line TO billing_svc;

-- Rollback, if staging surfaces a reader you cannot fix yet:
-- GRANT USAGE ON SCHEMA orders TO reporting_svc;
-- GRANT SELECT ON orders.order_line TO reporting_svc;

PostgreSQL-flavored script that moves a table into its owning service's schema, grants full rights only to that service's role, and records a non-owner's read access as an explicit, revertible exception.

How do you replace cross-service joins and foreign keys?

Once a service stops owning a table, each of its queries against that table has to go to one of two places. It can call the owner's API at request time (API composition), or it can consume the owner's events and maintain a local, denormalized read model.

Concern API composition Local read model
Latency Adds a network hop per call, multiplied by fan-out Local query, comparable to the old join
Availability coupling Caller degrades when the owner is down Caller keeps serving from its copy
Staleness Current at call time Lags by event delivery delay
Duplication None Copied fields plus sync code to maintain

Use composition when the caller needs the current value, such as checking a credit limit before a debit, and the owner can meet the caller's availability target. Use a read model for high-volume reads, list and search screens, or any path that must keep working through an outage of the owner.

A read model is only as reliable as its event feed. With a transactional outbox, the owner writes the event row in the same local transaction as the state change, and a relay publishes it afterward. This avoids the lost-update gap of writing to the database and the broker separately. The relay delivers at least once, so consumers must be idempotent. They can record processed event IDs, or apply upserts guarded by a version number.

For writes that span services, a saga sequences local transactions. If a later step fails, it runs compensating actions, which are new business operations rather than rollbacks, so expose intermediate states like pending. Foreign keys become application-level checks at write time against the owner's API or the read model. Behind those checks, run a periodic reconciliation job that finds orphaned references the checks missed.

-- PostgreSQL. OWNER SIDE (customers service): state change and event in ONE local transaction.
-- If the transaction rolls back, no event exists; if it commits, the relay will find the event.
BEGIN;
WITH updated AS (
  UPDATE customers
     SET email = 'ana@example.com', version = version + 1
   WHERE id = 'c-1042'
  RETURNING id, email, version
)
INSERT INTO outbox (event_id, aggregate_id, event_type, payload)
SELECT gen_random_uuid(), id, 'CustomerEmailChanged',
       jsonb_build_object('id', id, 'email', email, 'version', version)
  FROM updated;
COMMIT;
-- A separate relay reads unpublished outbox rows, publishes them to the broker,
-- then marks them sent. A crash between publish and mark causes a re-send (at-least-once).

-- CONSUMER SIDE (orders service): idempotent upsert into its local read model.
-- The version guard means duplicates and out-of-order replays are no-ops.
INSERT INTO customer_view (customer_id, email, version)
VALUES ('c-1042', 'ana@example.com', 7)
ON CONFLICT (customer_id) DO UPDATE
   SET email   = EXCLUDED.email,
       version = EXCLUDED.version
 WHERE customer_view.version < EXCLUDED.version;

-- RECONCILIATION (replaces the old foreign key): orders that point at customers
-- missing from the read model. Run periodically; review or repair the results.
SELECT o.id, o.customer_id
  FROM orders o
  LEFT JOIN customer_view cv ON cv.customer_id = o.customer_id
 WHERE cv.customer_id IS NULL;

PostgreSQL sketch of a transactional outbox write on the owner, a version-guarded idempotent upsert into the consumer's read model, and a reconciliation query that stands in for a cross-service foreign key.

How do you migrate the data incrementally without downtime?

Migrate one bounded context at a time, starting with the least-coupled table group. A small first move tests your tooling, verification and rollback before you touch anything critical. Each move follows the same sequence:

  1. Expand. Create the new schema in the owning service's database. Leave the old tables untouched. Every change at this stage is additive, so it can be reversed.

  2. Propagate writes. Avoid naive dual writes, where the application writes to both databases in separate transactions. Any crash or timeout between the two writes causes silent divergence. Instead, use a transactional outbox or change data capture (CDC) from the legacy tables. CDC brings its own risks:

    • Events can arrive out of order across partitions.
    • Replays after a connector restart redeliver events.

    Make consumers idempotent, and order events by a version or log position rather than arrival time.

  3. Backfill and verify. Copy historical rows while propagation keeps running. Compare the two stores in three ways:

    • Row counts per partition.
    • Checksums over key columns.
    • Sampled field-by-field comparisons, which catch errors that counts hide.
  4. Switch reads behind a flag. Shadow-compare before you cut over.

  5. Switch writes. Reverse the sync direction so the legacy tables stay current for rollback.

  6. Soak, then contract. Keep the soak period long enough to cover month-end closes, quarterly reports and annual jobs. A two-week window will miss them. Drop the old tables only after you have rehearsed the rollback path, not just written it down.

Before reporting queries lose direct table access, give analytics a replacement feed from events or CDC.

How can splitting data ownership save engineering time?

The time savings come from removing coordination, not from making any single task faster. They show up in a few places:

  • Schema changes stop needing cross-team scheduling. When one team owns a table and every other service reaches it through a versioned API or event contract, the owner can rename, index or restructure it without a joint release. Review is limited to the owning team.
  • Breakage is found earlier and more cheaply. Enforced grants turn hidden dependencies into permission errors in staging. Tracing a permission error to a role name is a log search. Tracing a production failure after a cutover to an unknown reporting script is an incident.
  • Incident triage is shorter. A slow query or a bloated table maps to one service and one on-call rotation, so less time goes into working out who should respond.
  • Failures are contained. A bad migration or runaway query in one service's database does not take down others, so fewer engineers get pulled into each incident.
  • Rollbacks are smaller. Because each ownership transfer is a separate, reversible step, undoing a mistake means reverting one grant, flag or table group rather than a whole migration.

A worked example shows how to estimate the coordination savings for your own system. Illustrative estimate, with assumed inputs: assume four teams share one database, each team ships five schema changes a month that touch tables other teams read, and each such change needs a one-hour sync with one engineer from each of the four teams plus a coordinated release. That is 20 changes × 4 engineer-hours = 80 engineer-hours a month spent on coordination, before counting delayed releases. If ownership and contracts remove most of those syncs, much of that time goes back to the teams. These inputs are assumptions, not measurements. Substitute your own change frequency and meeting cost.

Weigh this against the new costs: building and maintaining APIs, event feeds and read models, and running more databases. If schema changes rarely cross team boundaries today, the savings will be small, and the costs in the next section may outweigh them.

When should you avoid splitting a shared database?

Splitting is not free, and the bill arrives in several places at once:

  • Consistency gets weaker. Users can see intermediate states, such as an order marked paid before the inventory reservation lands. Every such state needs product decisions and compensating logic.
  • Operations multiply. Each new database brings its own backups, restore drills, credentials rotation, monitoring, capacity planning and on-call pages.
  • Latency becomes coupled. If service A calls service B synchronously for data it used to join, A's latency is now A plus B, and B's outage is A's outage.
  • Personal data spreads. Replicated copies of customer records make deletion requests, retention rules and access audits harder, because every copy must be found and governed.
  • Half-migrated states are risky. Months of dual writes and backfills widen the window for drift, and teams often stall there.
  • Reporting gets harder. Questions that were one SQL query now need a pipeline.

Development slows too. A feature that touched three tables may now require coordinated changes, contract versioning and deploys across three teams.

Watch your invariants. If a rule such as "an account balance never goes negative across these two entities" truly needs one ACID transaction, the boundary is probably in the wrong place. Forcing a split there produces a distributed monolith. That means services that must deploy together, call each other in chatty sequences and fail together. You get the costs of distribution without any independence. Redraw the boundary so the invariant lives inside one service, or keep the data shared.

What are the alternatives to database per service, and how do you know you are done?

Database per service is one end of a spectrum, and the right choice can differ from one boundary to the next.

Option Isolation Cost Operational burden
One shared database, schema per service, enforced grants Logical only. Services cannot touch each other's tables, but they still share CPU, connections, locks and upgrade windows. Lowest One instance to patch and back up. Noisy neighbours remain.
Shared table under a single owner Writes are isolated. Readers stay coupled to the table's shape. Low The owner must version the table like a public API.
Merge tightly coupled services Not needed, because the invariant stays inside one transaction Often lower than today Fewer deployables, but a larger codebase per team
Database per service Full. Failures, scaling and engine upgrades are independent. Highest Each team owns its own backups, failover and monitoring.

How you know you are done

You are done when each of the following is true:

  • The old shared credentials are revoked, not just unused. A credential that still works will be used again during an incident.
  • Query logs show no cross-schema reads or writes over a full business cycle. That cycle includes month-end jobs and rarely run reports, which are where hidden dependencies tend to live.
  • Each service ships schema migrations on its own schedule. No migration needs another team to sign off or to deploy at the same time.
  • Every datastore has a named on-call owner. That team gets paged when the datastore runs out of disk or replication lags.
  • Every copy of the data has a deletion path. This covers caches, read models, event logs and analytics exports. If a record must be erased, you need to know every place it has been copied to.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to shared database microservices, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.