How to Split a Monolith Database Gradually Without Breaking Joins

What splitting a monolith database means, and why joins are the real constraint

To split a monolith database is to move from one shared database, where any service can read or write any table, to a model where each service owns its data. Only the owning service changes its tables, and everyone else goes through that service's API or the events it publishes. Database decomposition is the work of moving from the first state to the second.

Teams usually do this for three reasons:

• Independent deploys. A service whose schema nobody else touches can ship without waiting on other teams' release schedules.
• Schema changes without coordination. In a shared database, renaming a column means finding every query in every codebase that reads it. With per-service ownership, that blast radius shrinks to one team.
• Isolating load and failures. A runaway reporting query or a lock-heavy batch job can no longer stall unrelated checkout or login traffic.

The tables themselves are rarely the hard part. The connections between them are. A join across two future boundaries becomes a network call or a copy of the data. A foreign key becomes an invariant that no database enforces anymore. A transaction that updates orders and inventory together becomes two writes that can fail independently. The single database was providing these guarantees for free, and splitting it removes them.

The approach this guide follows is to plan around joins. For each cross-boundary join, decide whether to keep it, replace it, or give it up. Then move one ownership boundary at a time, and keep each step small enough that rolling it back is still cheap.

Who should do it, when it pays off, and when to avoid it

A split pays off when the shared database is the thing slowing teams down, not when it merely looks untidy. Three signals are worth acting on:

• Schema-change contention. Teams queue behind each other for migrations, or one team's column rename breaks another team's queries. Ownership is already the problem, and separate storage enforces it.
• Noisy neighbours. One domain's reporting queries, batch jobs or write bursts degrade latency for unrelated domains. You can't tune or scale the hot domain on its own while it shares a buffer pool and connection limit with everything else.
• Services deployed separately but coupled through tables. If services ship on their own schedules but read each other's rows directly, you carry the cost of distribution without its isolation.

Avoid the split, or stop early, when the conditions work against you. A small team gains little and takes on extra infrastructure to run. Without clear domain boundaries, you will draw lines through tables that change together. Heavy cross-domain transactional invariants are a warning: if an order and its inventory reservation must commit atomically, splitting them replaces one transaction with compensation logic you have to build and operate. Limited operational capacity matters too, because each new database adds backups, monitoring, upgrades and on-call load.

A shared database with strict schema ownership is a legitimate place to stop. In that setup each team owns its tables, and other teams reach them only through views or APIs. Choose your boundaries carefully. Once data has physically moved, merging two stores back together or redrawing the line between them costs far more than the original move.

Inventory joins and choose boundaries from the data

Start from what the application actually executes, not from the schema diagram. Foreign keys show which joins are possible. Query logs show which joins happen and how often. Use a normalized statement source, such as pg_stat_statements in PostgreSQL or ORM-level instrumentation that records each query with its call count and calling endpoint. Capture at least one full business cycle so that month-end jobs and batch reports show up.

Parse each distinct statement and list the tables it touches together. For example, SELECT … FROM orders o JOIN customers c ON … JOIN order_items i ON … running 40,000 times a day produces three pairs: orders–customers, orders–items and customers–items, each weighted 40,000. A nightly report joining invoices to customers adds invoices–customers with a weight of 1. Weight raw frequency further by importance: a join on the checkout path matters more than one in an admin export, even when the export runs more often.

Load the pairs into a weighted graph and look for clusters, meaning groups of tables with heavy edges inside the group and light edges to the rest. Each cluster is a candidate boundary. The light edges between clusters are the joins you will have to replace.

This method has a cost. Pure join clustering often produces a few large services, because core tables like users connect to everything. Boundaries drawn from the org chart produce the opposite problem: chatty services that need many cross-service calls to answer one request. Treat the clusters as a starting proposal. Then adjust them on purpose, and record which heavy edges you are choosing to cut.

Copyimport re
from collections import Counter
from itertools import combinations

# Each row: (normalized statement, calls per day, calling endpoint).
# In practice, export these from pg_stat_statements (query, calls) joined with ORM tags.
LOGGED = [
    ("SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id "
     "JOIN order_items i ON i.order_id = o.id WHERE o.id = $1", 40000, "checkout"),
    ("SELECT p.sku FROM products p JOIN order_items i ON i.product_id = p.id "
     "WHERE i.order_id = $1", 25000, "checkout"),
    ("SELECT u.email FROM users u JOIN customers c ON c.user_id = u.id", 300, "admin_export"),
    ("SELECT * FROM invoices inv JOIN customers c ON c.id = inv.customer_id "
     "WHERE inv.issued_at > $1", 1, "nightly_report"),
]

# Hot-path multipliers (illustrative values): checkout joins outrank a frequent admin export.
IMPORTANCE = {"checkout": 10, "admin_export": 0.5, "nightly_report": 1}

# Naive extraction: table names after FROM/JOIN. Comma joins, CTEs and subqueries
# need a real SQL parser; this is enough to get a first co-occurrence list.
TABLE_RE = re.compile(r"\b(?:FROM|JOIN)\s+([A-Za-z_][\w.]*)", re.IGNORECASE)

edges = Counter()
for sql, calls, endpoint in LOGGED:
    tables = sorted(set(TABLE_RE.findall(sql)))
    weight = calls * IMPORTANCE.get(endpoint, 1)
    # Every pair of tables in one statement is an edge, weighted by usage.
    for a, b in combinations(tables, 2):
        edges[(a, b)] += weight

# Tab-separated edge list, heaviest first; load into any graph/clustering tool.
for (a, b), w in edges.most_common():
    print(f"{a}\t{b}\t{w:g}")

A minimal Python script that turns logged SQL statements with call counts and endpoint tags into a weighted table co-occurrence edge list suitable for clustering into candidate service boundaries.

How to replace each cross-boundary join

Each heavy edge that still crosses a boundary needs one of four replacements. Choose per join, not per service. A single service often ends up using all four.

An API-side join is only acceptable when it is batched. The code example below replaces JOIN customers with a three-step version. It fetches the orders, deduplicates their customer_id values, and then makes one bulk request before merging the results in memory. Calling the customer service once per row turns one query into N+1 network calls, and tail latency grows with page size.

A local copy should hold only the columns the consumer actually reads. Its updates must be idempotent so that replays and out-of-order events don't corrupt it.

Reporting and ad-hoc queries that join anything to anything should not run against any service. Move them to an analytical store fed from every owner. That store always lags behind production, so every compliance report must carry an "as of" timestamp. Agree on the acceptable lag with the report's owners before cutover, not after the first audit question.

Copy// Before (monolith): SELECT o.*, c.name, c.tier FROM orders o JOIN customers c ON c.id = o.customer_id
// After: orders are local; customers belong to another service.

interface Order { id: string; customerId: string; totalCents: number }
interface Customer { id: string; name: string; tier: string }

// Placeholder clients: one local DB query, one bulk endpoint on the customer service.
declare function queryOrders(page: number, size: number): Promise<Order[]>;
declare function fetchCustomersByIds(ids: string[]): Promise<Customer[]>; // e.g. POST /customers:batchGet

export async function listOrdersWithCustomers(page: number, size = 50) {
  // 1. Fetch the page of orders from the service's own database.
  const orders = await queryOrders(page, size);
  if (orders.length === 0) return [];

  // 2. Deduplicate keys so 50 orders from 3 customers means 3 ids, not 50.
  const ids = [...new Set(orders.map((o) => o.customerId))];

  // 3. One bulk request instead of one call per row (avoids N+1).
  const customers = await fetchCustomersByIds(ids);
  const byId = new Map(customers.map((c) => [c.id, c] as [string, Customer]));

  // 4. Merge in memory. A missing customer is kept as null, like a LEFT JOIN,
  //    so the caller decides how to handle deleted or not-yet-replicated rows.
  return orders.map((o) => ({
    ...o,
    customer: byId.get(o.customerId) ?? null,
  }));
}

A batched API-side join that replaces a SQL JOIN on customers by fetching a page of orders, deduplicating customer IDs, making one bulk call to the customer service, and merging the results in memory.

Establish ownership, then migrate in reversible stages

Before moving any data, decide who owns it. Under single-writer ownership, every table has exactly one owning service. Only that service issues INSERT, UPDATE or DELETE against it. Every other service reads through the owner's API or through a view the owner publishes and agrees to keep stable. Without this rule, a split only moves a shared-write problem onto the network.

Enforce ownership while still on the shared database

Give each service its own schema and its own database role. Have each owner revoke default access on its schema, for example REVOKE ALL ON SCHEMA billing FROM PUBLIC, and then grant USAGE and SELECT only on the views it publishes. With grants in place, a forbidden cross-schema join fails with a permission error during testing. Without them, the same join is discovered during the physical cutover. Grants only cover the database layer, so also add a lint or code-review rule that flags any query referencing a schema the service does not own. This catches violations before they reach a test database.

Staged path

Each stage is reversible. You can widen a grant again, or point a connection string back at the old database. Run each stage long enough that breakage shows up in stages 1 and 2, where fixing it costs a code change. In stage 3, the same breakage can cost an outage.

Copy-- PostgreSQL. Run as an administrator on the shared monolith database.
-- One login role per service. Passwords are placeholders.
CREATE ROLE billing_svc LOGIN PASSWORD 'placeholder-billing';
CREATE ROLE orders_svc  LOGIN PASSWORD 'placeholder-orders';

-- Each service owns its schema, so only it can create or write tables there.
CREATE SCHEMA billing AUTHORIZATION billing_svc;
CREATE SCHEMA orders  AUTHORIZATION orders_svc;

-- Defensive: make sure no access is inherited through PUBLIC.
REVOKE ALL ON SCHEMA billing FROM PUBLIC;
REVOKE ALL ON SCHEMA orders  FROM PUBLIC;

-- Billing publishes its read contract in a separate schema.
CREATE SCHEMA billing_api AUTHORIZATION billing_svc;
REVOKE ALL ON SCHEMA billing_api FROM PUBLIC;

-- Act as the owner so that billing_svc owns the table and the view.
SET ROLE billing_svc;

CREATE TABLE billing.invoices (
  invoice_id bigint PRIMARY KEY,
  order_id   bigint NOT NULL,
  status     text   NOT NULL,
  paid_at    timestamptz
);

-- Expose only the columns billing agrees to keep stable.
CREATE VIEW billing_api.invoice_status AS
  SELECT invoice_id, order_id, status, paid_at FROM billing.invoices;

GRANT USAGE  ON SCHEMA billing_api TO orders_svc;
GRANT SELECT ON billing_api.invoice_status TO orders_svc;

RESET ROLE;

-- As orders_svc:
--   SELECT * FROM billing_api.invoice_status;  -- allowed
--   SELECT * FROM billing.invoices;            -- ERROR: permission denied for schema billing

This PostgreSQL setup for stage 2 gives each service its own schema and login role and revokes PUBLIC access, so orders can read billing only through a published view and a direct join on billing's tables fails with a permission error.

Moving data safely: expand and contract, integrity and transactions

The sequence runs in one direction: create the new store, backfill it, keep it current with dual-writes or change data capture (CDC), shadow-read and compare results, switch reads, switch writes, then drop the old tables after a soak period. The old tables stay authoritative until writes move. Until then, every step can be undone by repointing reads.

The outbox pattern works because the business row and the event are written in the same local transaction. The relay can then publish at least once without a distributed commit. The example below shows the table and the write.

Cross-database foreign keys become a set of rules:

• One service owns each table.
• Other services hold soft references by ID.
• Consumers handle events idempotently, keyed on event ID, so redelivery is harmless.
• A reconciliation job runs an anti-join to find orphaned IDs and fields that have drifted.

That job replaces the guarantee the constraint used to give, so schedule it and alert on its output.

Decide each multi-table transaction case by case:

• Strict atomicity required: keep those tables in the same database.
• One side reacts to the other: use an outbox.
• Multi-step workflows: use a saga with compensating actions. Accept that intermediate states will be visible.

For invariants such as a denormalised count that can lag by seconds, accept eventual consistency and document the allowed lag.

Copy-- PostgreSQL. The outbox lives in the same database as the orders table it describes.
CREATE TABLE outbox (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- consumers dedupe on this
  aggregate_type text        NOT NULL,
  aggregate_id   text        NOT NULL,
  event_type     text        NOT NULL,
  payload        jsonb       NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  published_at   timestamptz            -- set by the relay after the broker acks
);
-- The relay polls only the unpublished rows.
CREATE INDEX outbox_unpublished ON outbox (created_at) WHERE published_at IS NULL;

-- The business row and its event commit together, or neither commits.
BEGIN;
INSERT INTO orders (id, customer_id, total_cents, status)
VALUES (90210, 4211, 2599, 'placed');
INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
VALUES ('order', '90210', 'OrderPlaced',
        '{"order_id": 90210, "customer_id": 4211, "total_cents": 2599}');
COMMIT;

-- Reconciliation: orders whose soft reference points at no known customer.
-- customer_snapshot is a periodically refreshed copy of IDs from the
-- customers service, because a join across the two databases is not possible.
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customer_snapshot c ON c.id = o.customer_id
WHERE c.id IS NULL
  -- Grace window so that events still in flight are not reported as orphans.
  AND o.created_at < now() - interval '15 minutes';

A PostgreSQL outbox table, a transaction that writes an order and its event atomically, and an anti-join reconciliation query that flags orders that reference customers which no longer exist.

Verifying the cutover, and the impact on cost and engineering time

Before cutting over, compare the old and new stores per table. Match row counts first, then aggregate checksums over a deterministic ordering, bucketed by primary-key range so a mismatch points to a narrow slice. The checksum query example later in this section shows the pattern. Run shadow reads that query both stores and log any diffs. Gate each read and write switch behind a feature flag, and write down rollback criteria per stage before you start, such as "diff rate above zero for 24 hours" or "p99 regression above budget." Deciding these thresholds in advance stops a team from rationalising a bad cutover once it is under way.

What it costs

Illustrative estimate (assumed numbers, not a benchmark): ongoing ops hours per month = databases × hours per database. If you assume 3 databases × 6 hours, that is 18 hours per month, compared with 6 for the single monolith.

Where it saves time

The join inventory catches bad boundaries before any code depends on them. Cheap early stages, such as flags and shadow reads, expose breakage while the data still lives in one place. Explicit completion criteria and a scheduled cleanup step, where you delete the dual writes and drop the old columns, keep migrations from stalling half-finished. A stalled migration is expensive because you pay the costs of both architectures indefinitely.

Alternatives to consider

Not every team that feels database pain needs separate databases. Several alternatives solve part of the problem at a lower cost, and each one is worth ruling out explicitly before you commit.

Logical separation inside one database

Give each module its own schema, grant it write access only to that schema, and expose other modules' data through views or a narrow read API. Joins keep working, transactions stay local, and ownership becomes enforceable through permissions. This fixes the coordination problem, where anyone can alter any table, without adding network hops. What it does not fix is shared resource contention: one module's heavy queries still compete for the same CPU, memory, and locks.

Scaling the single database

If the real issue is load and not team autonomy, use read replicas, table partitioning, and query tuning. These approaches buy capacity without changing any data contracts. They fail when write volume outgrows a single primary, or when teams need to deploy schema changes independently.

Derived read models only

You can leave writes in the monolith and stream changes into purpose-built read stores for specific services. This decouples read paths with little risk, because the source of truth never moves. The cost is eventual consistency on those reads, plus a pipeline you have to monitor.

Big-bang extraction

Moving a service and its data in one cutover can be faster for small, weakly coupled domains. For anything with dense joins it concentrates risk, because there is no partial state to fall back to if something breaks.

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 split monolith database, 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.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

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.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• 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.