How to Split Monolith Database Tables Into a New Schema Safely

What splitting a shared database means, and when to do it or avoid it

When you split monolith database tables, the hard part is not copying rows. It is removing the implicit coupling the shared database has allowed to build up: queries that join across module lines, foreign keys that enforce integrity between tables different teams own, and transactions that commit changes to both sides atomically. The physical move is the last step. Until those dependencies are gone, the tables are only separate on a diagram.

Why teams do it

A shared database couples release schedules. A migration on one team's table can lock rows another team depends on, and a schema change needs sign-off from everyone who reads it. Separating the data gives one module clear ownership: it can change its schema, deploy, and tune or scale its storage without coordinating with the rest of the codebase.

This work suits backend and platform engineers who can name a single owning module for each candidate table. It is warranted when that module already has its own team, roadmap, or load profile, and the shared database is the main thing slowing it down.

When to avoid it

• Ownership is unclear. If three modules write to the same table, splitting it only moves the argument somewhere else.
• Reporting depends on cross-boundary joins and has no replacement. Without a warehouse, replica, or reporting feed, those queries break.
• Workflows require immediate consistency. Once a single transaction no longer covers both sides, some state will lag. Payment or inventory flows that cannot tolerate that lag should stay together.

Inventory the coupling and draw the ownership boundary

Before moving anything, you need a complete list of who touches the target tables. No single source is enough, so combine three.

• Query logs. Enable statement logging or a statement-statistics view for a full business cycle, including month-end jobs. Logs catch ad hoc reports and scripts that code search misses.
• Code search. Grep every repository for table names, ORM model classes and raw SQL strings. ORM relationships (eager loads, cascades, lazy associations) create joins that never appear as literal SQL in the code.
• Catalog queries. Query information_schema (or your engine's system catalog) for foreign keys, views, triggers and stored procedures that reference the tables. These are hidden coupling, because they run inside the database whether or not application code mentions them.

Record each access path with its owning module, whether it reads or writes, and which other tables it joins. The write column matters most. Any table written by more than one module needs a decision about which module becomes the sole writer before it can move.

Move tables together when they are always written in the same transaction. Then choose how other modules will read the data:

Copy-- PostgreSQL catalog sweep for hidden coupling on the target tables.
-- Target tables (placeholders): public.invoices, public.invoice_lines

-- 1. Foreign keys in either direction. Each row is a constraint that must be dropped or replaced before the move.
SELECT conname, conrelid::regclass AS from_table, confrelid::regclass AS to_table
FROM pg_constraint
WHERE contype = 'f'
  AND (conrelid  = ANY (ARRAY['public.invoices', 'public.invoice_lines']::regclass[])
    OR confrelid = ANY (ARRAY['public.invoices', 'public.invoice_lines']::regclass[]));

-- 2. Views that read the tables. Their consumers inherit the coupling.
SELECT view_schema, view_name, table_name
FROM information_schema.view_table_usage
WHERE table_schema = 'public' AND table_name IN ('invoices', 'invoice_lines');

-- 3. Triggers that fire on the tables. These are writes that no application code shows.
SELECT event_object_table, trigger_name, event_manipulation, action_statement
FROM information_schema.triggers
WHERE event_object_schema = 'public' AND event_object_table IN ('invoices', 'invoice_lines');

-- 4. Functions and procedures whose body names a table. This is a crude text match, so review each hit by hand.
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 ~* '\m(invoices|invoice_lines)\M';

-- 5. Observed statements, which requires the pg_stat_statements extension.
--    Run this after a full business cycle so that month-end jobs and ad hoc reports appear.
SELECT calls, query
FROM pg_stat_statements
WHERE query ~* '\m(invoices|invoice_lines)\M'
ORDER BY calls DESC;

These PostgreSQL catalog and pg_stat_statements queries list the foreign keys, views, triggers, routines and observed statements that touch the target tables, so each access path can be assigned to an owning module.

Rewrite cross-boundary joins and remove foreign keys one at a time

Pick a strategy for each join from that table, then change the application code while both sides still live in one database. At this stage a bad rewrite is fixed with a code revert, not a data repair.

Every rewritten join adds risk:

• Latency. One SQL join becomes two round trips.
• N+1 queries. Looping over parent rows and fetching each child separately multiplies the round trips. Collect the IDs and issue one batched IN lookup per page instead.
• Partial results. The second call can fail, or return fewer rows than the first. Decide explicitly whether the endpoint returns partial data, a default value, or an error.

Remove foreign keys deliberately

List every constraint that crosses the boundary, along with its ON DELETE behavior. Choose a replacement for each one:

• Application validation: check that the referenced row exists before writing.
• Soft reference: store only the ID, and tolerate it not resolving.
• Tombstones or soft deletes: mark rows as deleted instead of removing them, so existing references still resolve.
• Orphan-detection query: a scheduled job that finds IDs with no matching row.

Cascades need the most care. If code relies on ON DELETE CASCADE to clean up child rows, move that cleanup into application logic or an event handler first. Then drop the constraints one at a time, and check orphan counts after each drop.

From here on, the database no longer prevents broken references. Your detection jobs find them after the fact. Give each orphan report an owner and an alert threshold.

Copy-- PostgreSQL. Step 1: inventory FKs that cross the boundary, with their ON DELETE action.
-- Here, billing tables referencing tables that will move to the new 'accounts' schema.
-- confdeltype: a = no action, r = restrict, c = cascade, n = set null, d = set default
SELECT conname                AS constraint_name,
       conrelid::regclass     AS child_table,
       confrelid::regclass    AS parent_table,
       confdeltype            AS on_delete
FROM pg_constraint
WHERE contype = 'f'
  AND conrelid::regclass::text  IN ('invoices', 'payments')
  AND confrelid::regclass::text IN ('accounts', 'account_users')
ORDER BY on_delete;  -- 'c' (cascade) rows need cleanup moved into app code first

-- Step 2: baseline the orphan count before dropping anything (should be 0).
SELECT count(*) AS orphan_invoices
FROM invoices i
WHERE i.account_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM accounts a WHERE a.id = i.account_id);

-- Step 3: drop ONE constraint, only after no code path relies on its cascade.
ALTER TABLE invoices DROP CONSTRAINT invoices_account_id_fkey;

-- Step 4: the same orphan query now becomes a scheduled detection job.
-- Report IDs, not just counts, so the owner can investigate and repair.
SELECT i.id AS invoice_id, i.account_id, i.created_at
FROM invoices i
WHERE i.account_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM accounts a WHERE a.id = i.account_id)
ORDER BY i.created_at DESC
LIMIT 500;

A PostgreSQL sequence that lists cross-boundary foreign keys with their ON DELETE behavior, baselines orphan counts, drops a single constraint, and reuses the orphan query as an ongoing detection job.

Handle transactions that used to span the boundary

Broken references are one failure mode. Lost atomicity is the other. Search for transactions that write to tables on both sides of the boundary. Examples include "place order", which inserts an order and decrements stock, and "close account", which flags the user and cancels subscriptions. Each relied on one commit, so either every write happened or none did. After the split that guarantee is gone, and each operation needs its own replacement.

If two tables can never disagree, move them to the same side of the boundary. That beats any protocol.

The outbox works because the event row commits in the same local transaction as the business change. The publisher can therefore never emit an event for a rolled-back write. It can still deliver an event twice. To handle that, the consumer stores each event ID under a unique constraint in the same transaction as its own write.

Compensations need a stable operation ID passed through every step. A retried "release reservation" then becomes a no-op, not a double release.

Each eventual option creates windows a user can see. An order might appear before its stock is reserved, or a cancelled account might still get one email. Write down each window and get product sign-off before you ship.

Copy-- PostgreSQL. Outbox lives in the producer's schema; dedup table in the consumer's.
CREATE TABLE orders.outbox (
  event_id   uuid PRIMARY KEY,
  event_type text NOT NULL,
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE inventory.processed_events (
  event_id uuid PRIMARY KEY  -- unique constraint is the idempotency guard
);

-- Producer: business row and event row commit or roll back together.
BEGIN;
INSERT INTO orders.orders (id, customer_id, status)
VALUES ('3f1c9a2e-0b7d-4e6a-9c1e-2a5b7d8e9f01', 42, 'placed');
INSERT INTO orders.outbox (event_id, event_type, payload)
VALUES ('8d4e2b6a-1c3f-4a5b-9e7d-0f2a4c6e8b10', 'OrderPlaced',
        '{"order_id": "3f1c9a2e-0b7d-4e6a-9c1e-2a5b7d8e9f01", "sku": "SKU-123", "qty": 2}');
COMMIT;

-- Consumer: the publisher may deliver this event more than once.
-- Claim the event ID and apply the effect in one local transaction.
BEGIN;
WITH claimed AS (
  INSERT INTO inventory.processed_events (event_id)
  VALUES ('8d4e2b6a-1c3f-4a5b-9e7d-0f2a4c6e8b10')
  ON CONFLICT (event_id) DO NOTHING
  RETURNING event_id
)
UPDATE inventory.stock
SET reserved = reserved + 2
WHERE sku = 'SKU-123'
  AND EXISTS (SELECT 1 FROM claimed);  -- duplicate delivery: claimed is empty, 0 rows updated
COMMIT;

A PostgreSQL outbox: the event row commits atomically with the order, and the consumer records each event ID under a primary key in the same transaction as its stock update, so a redelivered event changes nothing.

How it works: schema separation first, then data migration to a separate database

Stage one: same database, separate schema

Create a new schema for the extracted module and move its tables into it. In PostgreSQL, ALTER TABLE ... SET SCHEMA changes catalog metadata and does not rewrite the data, so the move is cheap. Give the new service a dedicated role that has privileges only on that schema. Then revoke the monolith's privileges on it. A missed query now fails with a permission error in testing instead of quietly reaching across the boundary in production. Because the data is still in one database, you can test the boundary without paying for a network hop or dealing with replication lag.

Stage two: a separate database

1. Backfill. Copy existing rows in primary-key-ordered batches. Pause between batches and watch replication lag and I/O on the source. An unthrottled backfill competes with production traffic.
2. Keep the copy current. Naive dual writes, where the application writes to both databases, leave the two inconsistent whenever one write succeeds and the other fails. Use one of these instead:
   • Outbox: record the change in an outbox table inside the same local transaction, then relay it to the new database.
   • Change data capture (CDC): read the source's write-ahead log and replay the changes.
3. Switch reads behind a feature flag, one percentage of traffic at a time.
4. Verify with row counts and per-range checksums.
5. Switch writes. Reverse the sync direction so the old database stays current. Rollback is then a flag flip, not a restore from backup.

Copy-- Stage one: move the billing tables into their own schema and enforce the boundary.
-- Run as a superuser or a role that is a member of both roles below.
BEGIN;

CREATE SCHEMA billing;

-- Metadata-only move. Sequences owned by these tables (serial/identity) move with them.
ALTER TABLE public.invoices      SET SCHEMA billing;
ALTER TABLE public.invoice_lines SET SCHEMA billing;

-- Dedicated role for the extracted service. Use a placeholder here; inject the real secret at deploy time.
CREATE ROLE billing_svc LOGIN PASSWORD 'change-me-placeholder';
ALTER ROLE billing_svc SET search_path = billing;

-- Owners bypass GRANT/REVOKE, so the monolith must not own these tables.
ALTER TABLE billing.invoices      OWNER TO billing_svc;
ALTER TABLE billing.invoice_lines OWNER TO billing_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, SELECT ON ALL SEQUENCES IN SCHEMA billing TO billing_svc;

-- Cut the monolith off so any missed cross-boundary query fails with
-- "permission denied" in testing instead of silently working.
REVOKE ALL ON ALL TABLES    IN SCHEMA billing FROM monolith_app;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA billing FROM monolith_app;
REVOKE ALL ON SCHEMA billing FROM monolith_app, PUBLIC;

COMMIT;

-- Sanity check: both should return false.
SELECT has_schema_privilege('monolith_app', 'billing', 'USAGE');
SELECT has_table_privilege('monolith_app', 'billing.invoices', 'SELECT');

PostgreSQL script for stage one. It moves the module's tables into a new schema, gives a dedicated role access to that schema, transfers table ownership away from the monolith, and revokes the monolith's privileges so cross-boundary queries fail with a permission error.

Verification, cutover and rollback runbook

Checks and pass thresholds

Agree on thresholds before cutover so nobody negotiates them during an incident.

• Row counts and checksums per key range. Compare by primary-key range, not whole tables, so a mismatch points to a specific slice you can re-sync. Pass: zero differences.
• Sampled record comparison. Field-by-field diffs on a random sample catch encoding, timezone and precision drift that checksums computed differently on each side can hide.
• Shadow reads. Serve from the old path, read the new one in parallel, and log mismatches with the key. Pass: no unexplained mismatches over a full business cycle, so batch jobs and month-end paths run at least once.
• Orphan references. With foreign keys gone, scheduled queries must find IDs in either database that point at rows that no longer exist.

Runbook

1. Enable the read flag in stages while watching error rates and mismatch counts.
2. Stop writes briefly, or route them through a single ordered writer, until CDC or replication lag reaches zero. Without this, the last in-flight writes can land in the wrong order.
3. Flip the write flag and reverse the sync direction.
4. Revert if error rates exceed baseline, lag keeps growing, or new mismatches appear. To revert, flip the write flag, then the read flag, confirm reverse sync has caught up, and investigate.

Keeping dual paths too long doubles the write paths and lets schemas drift apart. Removing them too early turns a flag flip into a restore from backup. Set a removal date after the full-cycle check passes.

Alternatives and impact on cost and engineering time

Not every team needs to reach a separate database. The options trade isolation against cost differently:

Stopping at a separate schema is a legitimate end state. If the main pain was unclear ownership rather than load or availability, the schema boundary already solves it.

Where the cost comes from

Most of the expense comes from keeping things running during the move, not from copying data. Dual-path code has to be written, tested and later deleted. Reports that relied on cross-table joins need a replacement, usually a warehouse or a read model fed by events or change data capture, and that pipeline needs an owner. Without foreign keys, orphaned rows build up and need a recurring cleanup or reconciliation job.

Small, reversible steps lower the total because each change has only one plausible cause when something breaks. Debugging one dropped constraint takes far less time than untangling a failed weekend cutover where ten things changed together.

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 tables, 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.