How to Split a Monolith Database Table Before Extracting a Service

What splitting a shared table first means, and who it is for

Splitting a shared table first means you decompose it inside the monolith's own database before any service extraction begins. The columns your future service will own move into a new table in the same schema, and the monolith learns to read and write them there. Only after that works do you move the new table behind a network boundary.

The point is to separate two risky changes. One is a data model change: new tables, new keys, new write paths. The other is a network hop: a separate process, a separate database, and calls that can time out or fail halfway. If you do both at once, a bug could come from either change and is hard to isolate. If you split the monolith database table while everything still shares one database, joins, foreign keys and transactions keep working while data moves. A dual write can be atomic, a backfill can be checked with a plain SQL comparison, and a bad change can be reverted without coordinating two deployments.

This approach is for backend and platform teams preparing to extract a service whose data is tangled in a heavily used table. A typical example is an orders or users table that several modules read, write and join against.

The database decomposition and data migration covered here follow five steps, each reversible on its own:

1. Map which code owns and touches each column.
2. Introduce a seam, expand the schema and start dual writes.
3. Verify the copies, switch reads and contract the old columns.
4. Replace cross-boundary joins and foreign keys.
5. Move the isolated table to the extracted service.

Mapping ownership before changing anything

Before touching the schema, build an inventory of every caller that reads or writes the table. Start with code you control: raw SQL, ORM models and their relationships, views and migrations. Then look outside the application, at reporting queries, batch jobs, triggers, stored procedures, BI dashboards and analysts with direct read access. This outside group matters most because a code search never finds it.

A code search shows what could run. Query logs and database statistics show what does run. Capture a window long enough to include month-end and quarterly jobs. For each column, record who reads it, who writes it and how often. Then sort the columns into two groups:

The boundary is wrong when both sides write the same column, or when one transaction must update it together with columns the monolith owns. That data is genuinely shared. Dual writes will not fix it. They will let the copies drift. Redraw the boundary or stop. Extracting now would turn a local transaction into a distributed one.

Expect the inventory to be incomplete. A forgotten script or an annual report will surface later. That is why the plan includes a deprecation window before any old column is dropped.

Copy-- PostgreSQL: inventory callers of public.orders (placeholder table).
-- Requires the pg_stat_statements extension. Its counters are cumulative since
-- the last reset, so read them after a window that includes month-end jobs.

-- 1. Per column: which roles read or write it, and how often.
-- Heuristic: matches column names in normalized query text. SELECT * and
-- queries that start with a CTE will not be classified, so review those by hand.
SELECT c.column_name,
       r.rolname,
       sum(s.calls) FILTER (WHERE s.query ~* '^\s*(insert|update)') AS write_calls,
       sum(s.calls) FILTER (WHERE s.query ~* '^\s*select')          AS read_calls
FROM information_schema.columns c
JOIN pg_stat_statements s
  ON s.query ~* ('\m' || c.column_name || '\M')
 AND s.query ~* '\morders\M'
JOIN pg_roles r ON r.oid = s.userid
WHERE c.table_schema = 'public' AND c.table_name = 'orders'
GROUP BY c.column_name, r.rolname
ORDER BY c.column_name, write_calls DESC NULLS LAST;

-- 2. Callers that a code search misses: triggers on the table.
SELECT tgname, tgfoid::regproc AS trigger_function
FROM pg_trigger
WHERE tgrelid = 'public.orders'::regclass AND NOT tgisinternal;

-- 3. Functions and procedures whose body mentions the table.
SELECT p.oid::regprocedure AS routine
FROM pg_proc p
WHERE p.prosrc ~* '\morders\M';

-- 4. Views built on the table (lists only views owned by roles you hold).
SELECT view_schema, view_name
FROM information_schema.view_table_usage
WHERE table_schema = 'public' AND table_name = 'orders';

-- 5. Roles with direct grants, such as BI tools and analysts.
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public' AND table_name = 'orders';

PostgreSQL catalog and pg_stat_statements queries that build a first-pass inventory of who reads and writes each column of a table, along with the triggers, routines, views and direct grants that a code search would miss.

How it works: the seam, expand and dual-write phases

Step 1: Build the seam

Route every read and write of the moving columns through one repository interface. Until that is true, any later change to storage means hunting down call sites. Make the ban on direct access enforceable rather than aspirational: a code review checklist, a lint rule, or a test that greps the codebase for raw references to the table and fails the build.

Step 2: Expand

Create the new table with its own key or a copy of the original key. Use no-downtime migrations: add columns as nullable, avoid defaults that force a table rewrite, and build indexes in a way that does not hold long locks. Backfill in small batches keyed on the primary key. Record the last processed key so a crash resumes instead of restarting. Throttle between batches. On a hot table, an aggressive backfill competes for row locks with live traffic, builds replication lag that makes replicas serve stale reads, and saturates disk I/O. Watch all three and slow down before users notice.

Step 3: Dual-write

While both tables share a database, write to both inside one local transaction. Either both rows commit or neither does, so they cannot drift. That guarantee disappears once the tables live in separate databases, because no single transaction spans them. A crash between the two writes leaves them inconsistent. At that point, use a transactional outbox or change data capture. Either one turns the second write into a reliable, replayable event.

Copyimport time
import psycopg2

JOB = "customer_billing"
BATCH_SIZE = 1000   # small batches keep row locks short
PAUSE_S = 0.2       # throttle; raise it if replica lag or disk I/O climbs

conn = psycopg2.connect("dbname=app user=backfill host=db.internal")
cur = conn.cursor()

# Resume from the last committed key instead of starting over after a crash.
cur.execute("SELECT last_id FROM backfill_checkpoint WHERE job = %s", (JOB,))
row = cur.fetchone()
last_id = row[0] if row else 0

while True:
    # Find the upper bound of the next batch by walking the primary key.
    cur.execute("SELECT max(id) FROM (SELECT id FROM customers WHERE id > %s "
                "ORDER BY id LIMIT %s) b", (last_id, BATCH_SIZE))
    upper = cur.fetchone()[0]
    if upper is None:
        break  # no rows left

    # DO NOTHING keeps rows the dual-write path already wrote, which are newer.
    cur.execute("""
        INSERT INTO customer_billing (customer_id, billing_email, tax_id)
        SELECT id, billing_email, tax_id FROM customers
        WHERE id > %s AND id <= %s
        ON CONFLICT (customer_id) DO NOTHING""", (last_id, upper))

    # Save progress in the same transaction as the copied rows.
    cur.execute("""
        INSERT INTO backfill_checkpoint (job, last_id) VALUES (%s, %s)
        ON CONFLICT (job) DO UPDATE SET last_id = EXCLUDED.last_id""", (JOB, upper))
    conn.commit()

    last_id = upper
    time.sleep(PAUSE_S)

conn.close()

A restartable, throttled PostgreSQL backfill that copies rows in primary-key batches. Each batch commits together with its checkpoint, so a crash resumes from the last finished batch, and rows already written by the dual-write path are left alone.

Verifying, switching reads and contracting safely

Step 4: Reconcile, shadow-read, then shift reads gradually

Run a reconciliation job before any read moves. It should compare row counts for each key range and then a per-row checksum or field-by-field comparison between the old columns and the new table. Counts alone only show that a row arrived. Checksums show whether it arrived intact. Run the job on a schedule, not once, because dual writes keep producing new rows that can drift.

Then add shadow reads. Serve every request from the old table, also read from the new one, and log any mismatch with the key and the differing fields. This tests the new table against real access patterns that a batch job will not cover.

Reconciliation can pass and still miss semantic drift. Common causes:

• Timezone handling: a column stores local time in one table and UTC in the other.
• Default values: one schema fills a default and the other leaves the field null.
• Trigger side effects: a trigger on the old table updates a timestamp or counter, and nothing does the same on the new table.

Normalise these cases explicitly before you compare. Once mismatches reach zero and stay there, move reads behind a percentage-based flag. Start small and increase in steps, so a problem reaches only a fraction of traffic and the fix is a flag change rather than a deploy.

Step 5: Contract in separate releases

When the new table serves all reads, stop writing to the old columns. Keep them read-only for a defined rollback window, so you can switch reads back without restoring data. Drop the columns, triggers and foreign keys in a later deploy. Never drop them in the same release that switches reads. If both changes ship together and the switch fails, the rollback target no longer exists.

Copy-- Reconcile one key range of orders (old columns) against order_shipping (new table).
-- Run on a schedule for each range; dual writes keep creating rows that can drift.
WITH old_side AS (
  SELECT o.id,
         md5(ROW(
           o.shipping_street,
           o.shipping_city,
           -- Old schema leaves country NULL; new schema defaults it to 'US'.
           COALESCE(o.shipping_country, 'US'),
           -- Old column is local time without zone; convert to UTC before comparing.
           (o.shipping_updated_at AT TIME ZONE 'Europe/Berlin') AT TIME ZONE 'UTC'
         )::text) AS row_hash
  FROM orders o
  WHERE o.id BETWEEN 1 AND 100000
),
new_side AS (
  SELECT s.order_id AS id,
         md5(ROW(
           s.street,
           s.city,
           s.country,
           s.updated_at AT TIME ZONE 'UTC'  -- timestamptz rendered as UTC
         )::text) AS row_hash
  FROM order_shipping s
  WHERE s.order_id BETWEEN 1 AND 100000
)
-- FULL OUTER JOIN catches missing rows on either side (what a count check would flag)
-- as well as rows that exist on both sides but differ after normalisation.
SELECT COALESCE(o.id, n.id) AS id,
       CASE WHEN n.id IS NULL THEN 'missing_in_new'
            WHEN o.id IS NULL THEN 'missing_in_old'
            ELSE 'hash_mismatch'
       END AS problem
FROM old_side o
FULL OUTER JOIN new_side n ON o.id = n.id
WHERE o.row_hash IS DISTINCT FROM n.row_hash
ORDER BY id;

A PostgreSQL reconciliation query that normalises timezone and default-value differences before hashing each row, then reports rows missing on either side or whose contents differ within one key range.

Replacing cross-boundary joins and foreign keys

Once the foreign key is dropped, the database no longer prevents an orders row from referencing a customer that doesn't exist. That job moves into application code, and there are three common ways to handle it.

All three lose the same guarantees. Inserts are no longer existence-checked by the database. ON DELETE CASCADE and RESTRICT stop working. Both tables can no longer be updated in one transaction.

Some business rules depend on those guarantees and need redesign. For example, "a customer with open orders cannot be deleted" now spans two owners. Two common fixes:

• Switch to soft deletes, so the customer is marked deleted but the row remains.
• Add a compensating action, such as cancelling open orders when a CustomerDeleted event arrives.

To find damage, run an anti-join. Left-join the child table to the parent on the reference column and keep the rows where the parent key is null. Once the tables are separated, run it against a replica or a snapshot. Run it on a schedule and publish the count as a metric.

Exclude rows newer than your propagation window. Eventual consistency creates short-lived orphans that will resolve themselves. Alert when the count grows, not when it is above zero.

Copy-- Orphan check: orders whose customer_id has no matching customer.
-- Run on a schedule against a replica or snapshot that holds both tables
-- (PostgreSQL syntax). Never run it on the primary write path.

-- 1) Count for the metric. Export as a gauge, e.g. orders_orphaned_total.
SELECT count(*) AS orphaned_orders
FROM orders o
LEFT JOIN customers c
  ON c.id = o.customer_id
WHERE c.id IS NULL
  -- Skip rows still inside the propagation window. A brand-new order may
  -- reference a customer the replica has not received yet.
  AND o.created_at < now() - interval '15 minutes';

-- 2) Orders that point at soft-deleted customers. These are not orphans,
-- but they show where the compensating action (cancel open orders on
-- CustomerDeleted) has not run yet.
SELECT count(*) AS open_orders_for_deleted_customers
FROM orders o
JOIN customers c
  ON c.id = o.customer_id
WHERE c.deleted_at IS NOT NULL
  AND o.status = 'open'
  AND c.deleted_at < now() - interval '15 minutes';

-- 3) Sample rows to investigate when either count goes above zero.
SELECT o.id, o.customer_id, o.created_at
FROM orders o
LEFT JOIN customers c
  ON c.id = o.customer_id
WHERE c.id IS NULL
  AND o.created_at < now() - interval '15 minutes'
ORDER BY o.created_at DESC
LIMIT 50;

An anti-join counts orders with no matching customer and a second query counts open orders tied to soft-deleted customers; both skip rows newer than an assumed 15-minute propagation window so their counts can be published as scheduled metrics.

Moving the isolated table to the extracted service, with a rollback plan for each step

Give the service database credentials that can read and write only its own tables, with no grants on the rest of the schema. Any query that fails after this change exposes a dependency your ownership map missed. It is cheaper to find that while both stores still sit on the same server.

Next, move the data physically. Logical replication or change data capture keeps downtime close to zero. A dump and restore with a brief write freeze is simpler if the table is small enough for the freeze to fit your maintenance window. At cutover, point the service at the new store and revoke the monolith's access to the old table. From then on, the monolith writes through the service API or publishes to an outbox that feeds the service's event stream. It never reconnects to the table directly, because that would bring back the coupling you just removed.

Write down the rollback path for each phase before you start it:

Once old-column writes stop, every rollback requires a reverse backfill. Set the abort thresholds before that phase begins. While each phase runs, watch three signals: the reconciliation mismatch rate, the error rate at the seam, and seam latency at the tail, not just the median.

When to avoid it, alternatives, and impact on cost and engineering time

Skip this approach when the boundary is still contested, because you will pay to build a seam that later moves. Skip it when the data is truly shared, such as a table that every module writes with no clear owner, because splitting it only relocates the coupling. Skip it if the team cannot sustain a migration that spans weeks. A split that stalls halfway leaves permanent dual writes, two schemas, and a reconciliation job that nobody owns.

The costs are concrete. The split requires many deploys and extra code paths at the seam. Two schemas exist at once, so storage for the copied table is roughly doubled during the expand phase. Engineers also carry extra cognitive load, because they must know which copy is authoritative in each phase. The savings are also concrete. Each deploy is small and reversible. Data-model bugs surface inside one process, without network failures in the mix. The final extraction moves a schema that is already isolated.

Illustrative estimate (assumed numbers, not a benchmark). Effort = phases × engineer-days per phase + dual-write weeks × upkeep days per week. Assume 8 phases at 1 engineer-day each, plus 6 weeks of dual writes at 0.5 days of upkeep per week. That gives 8 × 1 + 6 × 0.5 = 11 engineer-days. If upkeep continues for 26 weeks because the migration stalled, the total rises to 8 + 13 = 21 engineer-days.

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 table, 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 route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

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.
• Consumers learn about a breaking change at review time rather than from production errors.
• 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.