How to Split a Shared Database Safely in a Monolith Migration

What splitting a shared database means and when it is worth doing

To split a shared database is to move from many modules reading and writing the same tables to a model where every table has exactly one owning service. Other services get that data only through the owner's API or the events it publishes. Database decomposition is less about adding databases than about making write authority unambiguous.

Teams do it for three reasons. Deploys become independent, because a service no longer waits on another team's release to change its own tables. Schema changes stop needing cross-team coordination, because nobody else queries those columns directly. Load and failure stay isolated, so a runaway batch job in billing cannot exhaust connections that checkout depends on.

It suits teams already extracting services whose shared schema is the last real coupling. Avoid it if:

• one team owns everything,
• deploys don't contend,
• invariants span most tables, or
• you cannot run two data paths for months.

Alternatives

The thesis of this article is simple: split ownership before storage. Moving tables to separate servers is the easy, reversible part. Deciding and enforcing who may write each table is the hard part, and it should come first.

Mapping the coupling before changing anything

Before any table moves, you need an accurate picture of who touches what. Code search alone undercounts. ORMs hide table names, and batch jobs, admin scripts, and BI tools often live outside the main repository.

Two sources of truth

Start with the database catalog. It lists foreign keys, triggers, stored procedures, and views, and each of these is coupling the application code never shows. A trigger on orders that updates inventory is a cross-module write, even if no service calls it directly. Then sample query logs or statement statistics over a full business cycle, including month-end jobs. If each module connects with its own database user, attribution is trivial. If every module shares one user, add a module identifier to the connection's application name or a query comment first. That change alone often pays for itself.

Build the ownership matrix

Join both sources into one row per table:

Draw boundaries around data ownership, not around the current package layout. Code layout reflects how the system grew. Write patterns reflect how the business actually works.

Tables with multiple writers are the hardest cases. Each one needs a single owner and an API for everyone else. If you can't name a natural owner, the boundary is probably drawn in the wrong place.

Copy-- PostgreSQL: inventory coupling the application code does not show.

-- 1. Foreign keys: each row is a table dependency that crosses modules if the two tables have different owners.
SELECT conrelid::regclass AS child_table, confrelid::regclass AS parent_table, conname
FROM pg_constraint
WHERE contype = 'f'
ORDER BY 1, 2;

-- 2. Triggers: hidden writes. A trigger on one table that touches another is a cross-module write.
SELECT event_object_table, trigger_name, action_timing, event_manipulation, action_statement
FROM information_schema.triggers
ORDER BY event_object_table;

-- 3. Views: which base tables each view reads. Reporting views often join across future boundaries.
SELECT view_schema, view_name, table_name
FROM information_schema.view_table_usage
ORDER BY table_name, view_name;

-- 4. Functions and procedures whose body mentions a table. Text search is approximate; review each hit.
SELECT n.nspname, p.proname
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 '%customers%';

-- 5. Attribution: have each module tag its connection, e.g. in the connection string
--    or with: SET application_name = 'billing';
--    Then sample live activity over a full business cycle (including month-end jobs).
SELECT application_name, usename, left(query, 120) AS query_sample
FROM pg_stat_activity
WHERE datname = current_database()
  AND state <> 'idle'
  AND pid <> pg_backend_pid();

PostgreSQL catalog queries that surface foreign keys, triggers, view dependencies and stored-code references, plus a per-module application_name tag for attributing live queries when building the ownership matrix.

Logical separation inside the existing database

The first structural change keeps all data in the same database instance. Move each owner's tables into its own schema. If your engine handles schemas poorly, use a table-prefix convention that everyone enforces. Then give each service its own database role, with privileges only on the schema it owns, and revoke every cross-schema grant. Code that still reaches into another service's tables now fails with a permission error. Each of those failures shows you a dependency the coupling map missed.

Revoking grants stops runtime access. It does not stop someone writing a new cross-boundary query next week. Add a check in code review or CI that flags queries referencing another service's schema, so the coupling cannot grow back while you work on the existing cases.

Each remaining cross-boundary read needs a replacement. Either call the owner's API, or have the owner publish an owned read model you can query. For joins, you have three options:

• Join in application code. Fetch from each side and combine the results. This is simple but costly. One SQL join becomes several network calls, adding latency and fan-out. You also have to decide what to return when one call fails and the other succeeds.
• Denormalize a read copy. Keep a local projection of the fields you need, and accept that it can lag behind the source.
• Redraw the boundary. If two schemas join constantly, they probably belong to one owner.

Rollback in this phase is cheap because no data has moved. If a boundary turns out to be wrong, you fix it by restoring a grant or moving a table back to a different schema. It is far better to find a bad boundary now than after the data lives in two separate databases.

Copy-- PostgreSQL: logical separation inside the existing database. No data leaves the instance.
BEGIN;

-- 1. One schema per owning service.
CREATE SCHEMA orders;
CREATE SCHEMA billing;

-- 2. Move each table to its owner's schema. This is a catalog change, not a data copy.
ALTER TABLE public.orders      SET SCHEMA orders;
ALTER TABLE public.order_items SET SCHEMA orders;
ALTER TABLE public.invoices    SET SCHEMA billing;

-- 3. One login role per service (placeholder password; load the real one from your secret store).
CREATE ROLE orders_svc  LOGIN PASSWORD 'change-me-orders';
CREATE ROLE billing_svc LOGIN PASSWORD 'change-me-billing';

-- 4. Each role gets privileges only on the schema it owns.
GRANT USAGE ON SCHEMA orders TO orders_svc;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA orders TO orders_svc;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA orders TO orders_svc;

GRANT USAGE ON SCHEMA billing TO billing_svc;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA billing TO billing_svc;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA billing TO billing_svc;

-- 5. Remove cross-boundary access. Any code that still reads invoices from the
--    orders service now fails with "permission denied", which exposes a hidden dependency.
REVOKE ALL ON SCHEMA billing FROM orders_svc;
REVOKE ALL ON ALL TABLES IN SCHEMA billing FROM orders_svc;
REVOKE ALL ON SCHEMA orders FROM billing_svc;
REVOKE ALL ON ALL TABLES IN SCHEMA orders FROM billing_svc;
REVOKE ALL ON SCHEMA orders, billing FROM PUBLIC;

COMMIT;

-- Rollback is cheap because nothing was copied. If invoices turns out to belong to orders:
--   ALTER TABLE billing.invoices SET SCHEMA orders;
-- To unblock a dependency temporarily while the API or read model is built:
--   GRANT USAGE ON SCHEMA billing TO orders_svc;
--   GRANT SELECT ON billing.invoices TO orders_svc;

A PostgreSQL script that moves tables into per-owner schemas, gives each service a role scoped to its own schema, revokes cross-schema access, and shows the one-statement rollback paths available while the data still lives in one database.

Data migration patterns: CDC, outbox, backfill plus sync, and dual writes

Once the boundaries hold, the data has to move to the new service's store. There are four common ways to do it, and each one fails in its own way.

To keep a backfill safe, throttle it, save resumable checkpoints, and set a last-writer rule, for example "a newer updated_at wins."

Dual writes without a shared transaction will drift apart. If the first write commits and the second times out, nothing rolls the first one back. Retrying the second write can also reorder updates. Neither problem raises an error.

To make a bridge between the two stores safe:

• Keep one source of truth.
• Derive the second write asynchronously, through an outbox or CDC.
• Run a reconciliation job continuously to compare the two stores.
• Set an exit date for removing the bridge.

Copy Monolith DB (source of truth)           New service DB
 ┌──────────────┬────────┐   relay/CDC   ┌──────────────┐
 │ orders       │ outbox │ ────────────▶ │ orders       │
 └──────┬───────┴────────┘ (idempotent)  └──────┬───────┘
        │                                       │
        └──────▶ reconciliation job ◀───────────┘
                 (compares rows, reports and repairs drift)

Copy-- 1. Monolith (source of truth): row change and outbox event commit in ONE transaction
BEGIN;
UPDATE orders SET status = 'shipped', updated_at = now() WHERE id = 1042;
INSERT INTO outbox (event_id, aggregate_id, event_type, payload, created_at)
VALUES (gen_random_uuid(), 1042, 'order.updated',
        jsonb_build_object('id', 1042, 'status', 'shipped', 'updated_at', now()),
        now());
COMMIT;

-- 2. Prune rows the relay has already published so the outbox does not grow forever
DELETE FROM outbox
WHERE published_at IS NOT NULL
  AND published_at < now() - interval '7 days';

-- 3. New service: idempotent apply with a last-writer rule.
--    $1..$3 are bound by the consumer from the event payload.
--    A redelivered event or a stale backfill row matches no newer data, so it is a no-op.
INSERT INTO orders (id, status, updated_at)
VALUES ($1, $2, $3)
ON CONFLICT (id) DO UPDATE
  SET status = EXCLUDED.status,
      updated_at = EXCLUDED.updated_at
  WHERE orders.updated_at < EXCLUDED.updated_at;

-- 4. Reconciliation: run the same query on BOTH databases per id range and
--    compare results; a mismatch flags that range for row-level diffing and repair.
SELECT count(*) AS row_count,
       md5(string_agg(id || ':' || status || ':' || updated_at::text, ','
                      ORDER BY id)) AS range_checksum
FROM orders
WHERE id BETWEEN 1 AND 10000;

PostgreSQL sketch of the outbox bridge. It writes the event in the same transaction as the data, prunes published outbox rows, applies events idempotently with a newer-updated_at-wins rule, and checksums id ranges so the reconciliation job can compare both stores.

Replacing foreign keys and cross-service transactions

Once orders and customers live in different databases, the foreign key between them is gone, and nothing at the storage layer stops an order from pointing at a customer that does not exist. That check now has to happen in the application. The order service validates the customer ID when it accepts a write, either by asking the owning service or by checking a local replica fed by events. Consumers of those events must be idempotent, because redelivery is normal and a duplicate "customer deleted" event should not trigger two cleanups. Where a check can race, design for eventual consistency and give each failure case a compensating action, such as flagging or cancelling orders whose customer was removed. A scheduled integrity job then scans for orphans and reports them, so drift shows up as a number you can track.

Transactions that used to span tables now owned by different services leave you three options:

• Redraw the boundary so the invariant stays inside one service and one local transaction. This is often the right answer.
• Use a saga, where each step commits locally and has a defined compensation if a later step fails.
• Accept a weaker guarantee and write it down where product and support teams can see it.

Two-phase commit across services is rarely used. The coordinator becomes a component everything depends on, locks are held across network calls, and many message brokers and managed datastores don't take part in XA-style protocols.

Losing referential integrity and atomicity is a real cost. Put alerts on the orphan counts, on compensation rates and on saga steps that get stuck.

Copy-- PostgreSQL, run in the order service's own database.
-- Handles one "customer deleted" event in a single local transaction.
BEGIN;

WITH first_delivery AS (
  -- Record the event ID; a redelivered event inserts nothing here.
  INSERT INTO processed_events (event_id, processed_at)
  VALUES ('evt-7f3a2c', now())
  ON CONFLICT (event_id) DO NOTHING
  RETURNING event_id
),
removed AS (
  -- Only touch the local replica on the first delivery.
  DELETE FROM customer_replica
  WHERE customer_id = 'cust-1042'
    AND EXISTS (SELECT 1 FROM first_delivery)
  RETURNING customer_id
)
-- Compensation: flag open orders for review instead of deleting them.
UPDATE orders
SET status = 'needs_review', status_reason = 'customer_deleted'
WHERE customer_id IN (SELECT customer_id FROM removed)
  AND status IN ('pending', 'confirmed');

COMMIT;

-- Scheduled integrity check: count orders whose customer is missing
-- and that no compensation has handled. Export this as a metric and alert on it.
SELECT count(*) AS orphan_orders
FROM orders o
LEFT JOIN customer_replica c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
  AND o.status <> 'needs_review';

An idempotent PostgreSQL consumer for a "customer deleted" event that runs a compensating update only on first delivery, plus a scheduled orphan-count query that stands in for the foreign key that no longer exists.

Phased cutover with a rollback point at every step

Each phase below is reversible on its own. Do not start the next one until the current one has held steady under real traffic.

Switching reads exposes sync lag as read-your-own-writes breakage. A user saves a change, reads from the replica, and sees the old value. A common mitigation is to record each user's last write time and route that user's reads to the source of truth for a short window afterwards. Size the window from the observed p99 lag, not the average.

The cutover is done when all of the following are true:

• Row counts and checksums match for every partition.
• Sampled field-level diffs show no unexplained mismatches.
• Lag on the sync path stays within its target.
• The comparison job is inside its error budget.
• The rollback has been rehearsed, not just written down.

Reconciliation only catches what it checks. Before you drop the old store, add explicit checks for soft-deleted rows, timestamp precision and time zones, and columns that are rarely written. A full-row checksum can miss these if the comparison normalises values first or samples only recent rows.

Keeping reporting working, and the impact on cost and engineering time

Analysts will want to keep running the joins they always ran. If they point those queries at service databases, each service's internal schema becomes a public contract again, and every column rename needs sign-off from the reporting team. That rebuilds the shared database in the analytics layer. Instead, feed a warehouse or read replica from the CDC stream or published events you already built, and let reporting own its own denormalised model.

What it costs

During the parallel run, every feature that touches migrated data must work on two paths. That slows delivery and makes reviews harder, because engineers must keep both models in mind. Sync pipelines are production infrastructure. They need monitoring and on-call, and they eventually need to be deleted. A bridge with no exit date becomes permanent and drifts away from both sides.

Where it saves time

• Logical separation first: If a boundary turns out to be wrong, you fix it by moving tables between schemas, not by re-platforming data.
• Reconciliation: It finds divergence while the gap is small, before it becomes a data-repair project.
• Independent schemas: Teams stop coordinating migrations with each other.

Illustrative estimate (assumed numbers, not a benchmark)

Parallel-run weeks = (table groups × soak weeks ÷ groups run concurrently) × (1 + buffer)

Assumptions:

• 6 table groups
• A 4-week soak per group, so each group runs through one monthly billing cycle
• 2 groups in parallel
• A 25% buffer

Result: (6 × 4 ÷ 2) × 1.25 = 15 weeks of carrying two data paths.

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 shared 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.
• Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

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.
• Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
• 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.