How to Split a Shared Database Table Between Microservices

What does it mean to split a shared database table between microservices?

A shared table is one that several services read and write directly. An orders service updates users.billing_address while an auth service reads users.password_hash from the same row. The schema becomes a coupling point. You cannot rename a column, add a constraint, or change an index without coordinating every team whose code touches it, and you cannot deploy one service without checking that its queries still match what the others expect.

The goal is database per service: each service owns its data, and other services reach that data only through the owner's API or published events. To split a shared database between microservices safely, treat the split as a sequence of small, reversible steps rather than a single cutover. Each step should leave the system working and easy to roll back. A big-bang migration fails all at once and is hard to diagnose.

This guide is for backend and platform engineers who own services that touch a shared schema.

Inventory every reader and writer first

Before you move any data, find everyone who touches the table. No single source is complete, so combine three:

Then record one owning service for each column or row set in an ownership map. Every later step depends on that map, and gaps in it become production incidents.

Copy-- PostgreSQL: inventory who touches public.users before moving any data.

-- 1. Grants: every role that CAN read or write the table.
--    This does not show whether the access is still used.
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND table_name = 'users'
ORDER BY grantee, privilege_type;

-- Column-level grants, if any were issued.
SELECT grantee, column_name, privilege_type
FROM information_schema.column_privileges
WHERE table_schema = 'public'
  AND table_name = 'users'
ORDER BY grantee, column_name;

-- 2. Query logs: what each role actually ran.
--    Requires the pg_stat_statements extension. Counters cover only the
--    period since the last reset, so rare jobs may be missing.
--    The text match is crude. Review the results by hand.
SELECT r.rolname AS role,
       s.calls,
       left(s.query, 120) AS query_sample
FROM pg_stat_statements s
JOIN pg_roles r ON r.oid = s.userid
WHERE s.query ILIKE '%users%'
ORDER BY s.calls DESC
LIMIT 50;

-- 3. Compare the two lists. A role that has grants but no recorded
--    queries is either dormant or runs less often than the stats window
--    covers. Search the code before you revoke anything.

PostgreSQL queries that list which roles have grants on a shared users table and which roles actually ran queries against it, so you can cross-check both sources while building the ownership map.

When should you split a shared table, and when should you leave it alone?

A split is justified when the shared table is actively slowing teams down, not merely when it looks untidy. Look for these signals:

• Conflicting schema changes. One team needs a column renamed or retyped, and another team's queries break or need a coordinated release.
• Coupled deploys. Migrations on the table force two services to ship in lockstep, so neither team controls its own release schedule.
• Cross-service load interference. One service's batch jobs or hot queries cause lock contention or I/O pressure that raises another service's latency, and you cannot tune or scale them independently.
• Unclear ownership. Nobody can say which service is allowed to write a column. Invariants live in several codebases and drift apart.

Leave the table alone in these cases:

• The table is too entangled. Most columns are read and written by most services.
• Callers need cross-entity ACID transactions. Splitting replaces those transactions with eventual consistency and compensation logic.
• The team cannot run change data capture or outbox infrastructure. Without one of them, keeping the two stores in sync becomes guesswork.

The alternative: one owner, one interface

Assign the table to a single service. Other services read and write through that service's API, or read through a database view that the owner maintains as a stable contract. This fixes ownership and decouples schema changes without moving any data. It costs far less than a migration, and for many teams it is the correct end state rather than a halfway step.

How does the step-by-step migration sequence work?

When the data does need to move, run the migration as a sequence of phases. Each phase has an exit criterion, so you know when it is safe to start the next one.

1. Establish ownership. Name one service as the sole writer of the table. Exit: every other writer is identified and has a plan to stop writing.
2. Introduce an access seam. Other services call the owner's API or read through a view instead of querying the table directly. Until this is done, you cannot change storage without breaking someone. Exit: query logs show no direct access from non-owners.
3. Remove cross-service coupling. Replace foreign keys and joins with ID references, API lookups or locally cached read models. For each caller, decide what consistency it needs. A billing check may need a synchronous read, while a dashboard can tolerate seconds of lag. Exit: no remaining join spans the ownership boundary.
4. Backfill. Copy existing rows with idempotent, batched upserts keyed on the primary key. That way a failed batch can rerun without creating duplicates, and batching keeps lock and replication pressure bounded. Exit: row counts match at a recorded checkpoint.
5. Keep the new store current. Use change data capture or a transactional outbox, so every committed change reaches the new store and rolled-back changes never do. Exit: replication lag is stable and measured.
6. Verify and cut over. Compare the data in both stores, then switch reads, then switch writes. Keep the old table read-only for a rollback window. Exit: the rollback window closes without incident.

Copy+--------------------+  (2) API / view  +----------------------+
| Other services     |----------------->| Owner service        |
| (3) ID refs, API   |                  | (1) sole writer      |
| lookups, cached    |                  +----------+-----------+
| read models; no    |                             | (6) reads, then writes
| cross-owner joins  |                             | switch to new store
+--------------------+                             |
                 +---------------------------------+-----------+
                 |                                             |
                 v                                             v
    +-------------------------+                   +-------------------------+
    | Old shared table        |                   | New owner-only store    |
    | read-only during        |--(4) backfill---->| idempotent upserts      |
    | rollback window         |--(5) CDC/outbox-->| keyed on primary key    |
    +-------------------------+                   +-------------------------+

    (6) verify: compare both stores, switch reads, then writes

Exit criteria
  1 ownership    -> every other writer identified, with a plan to stop
  2 access seam  -> query logs show no direct access from non-owners
  3 decoupling   -> no join spans the ownership boundary
  4 backfill     -> row counts match at a recorded checkpoint
  5 keep current -> replication lag is stable and measured
  6 cut over     -> rollback window closes without incident

Migration phases for splitting a shared table: one owner behind an access seam, decoupled callers, backfill plus CDC or outbox into the new store, then verified cutover of reads before writes, each phase gated by its exit criterion.

Why are dual writes from application code the riskiest shortcut?

Writing to the old table and the new service from the same request handler looks like the fastest path, because it needs no new infrastructure. The problem is that the two writes share no transaction, so nothing keeps them consistent. The code example shows the gap: the handler commits to the old table, then calls the new service. If the process crashes, times out or is redeployed between those two lines, one store has the change and the other never will.

Concurrency adds a second failure. Two requests updating the same row can commit to the old table in order A, B and reach the new service in order B, A. The stores then disagree about the final value, and no error is raised. Retries make this worse. A retry built from a value read earlier can reapply that value after a newer write has landed, which silently overwrites fresh data with old data.

Dual writes are acceptable only for short-lived, low-stakes data, such as a cache-like field you could rebuild. Even then, run a reconciliation job that compares the two stores and repairs any drift it finds.

Copyimport requests  # HTTP client for the new service

ORDERS_SVC = ""  # placeholder host

def update_order_status(conn, order_id: str, status: str) -> None:
    # Write 1: the legacy shared table. This commit is durable on its own.
    with conn:  # DB-API connection; commits on clean exit
        conn.execute(
            "UPDATE orders SET status = ? WHERE id = ?",
            (status, order_id),
        )

    # --- FAILURE WINDOW ---
    # A crash, timeout or redeploy here leaves the legacy table updated
    # and the new service stale. No transaction spans both stores, so
    # nothing rolls back write 1 and nothing re-sends write 2.

    # Write 2: the new service, over the network.
    # Two concurrent requests (A then B) can commit above in order A, B
    # but arrive here in order B, A, leaving the stores with different
    # final values and raising no error.
    resp = requests.put(
        f"{ORDERS_SVC}/orders/{order_id}/status",
        json={"status": status},
        timeout=2,
    )
    # If this raises, the caller's retry may resend a 'status' it read
    # earlier, overwriting a newer value that has since landed.
    resp.raise_for_status()

A request handler that commits to the legacy table and then calls the new service, with comments marking the crash window, the reordering risk under concurrency and the stale-retry risk.

How do you split a users table into identity and profile services?

Start with a monolith users table holding id, email, password_hash, mfa_secret, locale and theme. The target is an identity store, credentials(user_id PK, email, password_hash, mfa_secret, updated_at), and a profile store, preferences(user_id PK, locale, theme, updated_at). Both keep the original user_id so no ID mapping is needed.

The backfill walks users in primary-key ranges and upserts each batch with INSERT … ON CONFLICT (user_id) DO UPDATE … WHERE excluded.updated_at > preferences.updated_at. Because of that guard, a rerun or an overlap with relay events cannot overwrite newer data with older data. The outbox table (id, aggregate_id, payload, created_at, published_at) is written in the same transaction as the business change. The relay polls unpublished rows in id order, delivers them, then sets published_at. Delivery is at-least-once, so consumers must also apply the updated_at guard.

The old JOIN users query becomes an ID lookup against the profile service. Calling that lookup once per row in a list endpoint creates an N+1 regression. Collect the IDs and send one batch request, such as GET /preferences?ids=…, or keep a local read model that the relay populates.

Copy-- PostgreSQL. :name placeholders are bound by the backfill job / relay driver.
-- Assumes users.updated_at exists (add it in phase 1 if the monolith lacks it).

-- 1. Monolith: read one primary-key range (keyset pagination, :last_id starts at 0).
SELECT id, locale, theme, updated_at
FROM users
WHERE id > :last_id
ORDER BY id
LIMIT 1000;

-- 2. Profile store: upsert the batch as parallel arrays.
--    The WHERE guard makes reruns and overlaps with relay events safe:
--    an older row never overwrites a newer one.
INSERT INTO preferences (user_id, locale, theme, updated_at)
SELECT *
FROM unnest(:user_ids::bigint[], :locales::text[],
            :themes::text[], :updated_ats::timestamptz[])
ON CONFLICT (user_id) DO UPDATE
SET locale     = EXCLUDED.locale,
    theme      = EXCLUDED.theme,
    updated_at = EXCLUDED.updated_at
WHERE EXCLUDED.updated_at > preferences.updated_at;

-- 3. Relay (single instance, preserving id order): fetch unpublished events.
SELECT id, aggregate_id, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 100;

-- 4. After the consumer acknowledges delivery, mark the rows as published.
--    A crash before this step causes redelivery, so consumers apply the
--    same updated_at guard as step 2.
UPDATE outbox
SET published_at = now()
WHERE id = ANY(:delivered_ids::bigint[]);

The SQL reads users in keyset-paginated batches and upserts them into preferences with an updated_at guard so reruns are safe. It also shows the outbox relay's poll-then-mark queries, which give at-least-once delivery.

How do you verify the data and cut over safely?

Before any traffic moves, the new store has to prove it matches the old table. Run three checks, in rising order of cost:

• Row counts. Compare counts per partition, such as by creation date or ID range, rather than across the whole table. A mismatch then points to a specific slice instead of "somewhere."
• Checksums on key columns. Hash the columns each service owns, such as email and status, in ID-range batches on both sides and compare the batch hashes. Counts can match even when values differ. Checksums catch truncation, encoding drift, and missed updates.
• Shadow reads. Serve the response from the old table, query the new store in parallel, and log every mismatch with the entity ID. This tests real access patterns, including joins and filters that batch checks never touch.

Throttle every check. Run batches with a fixed delay, read from a replica where possible, and sample shadow reads at a low percentage. Verification that degrades production defeats its own purpose.

Cut over in this order. First, move reads behind a feature flag and raise the percentage gradually. Next, move writes. Then keep the old table read-only for a defined rollback window before you drop it.

Rollback is cheap while only reads have moved, because you just flip the flag back. After writes move, the new store holds rows the old table has never seen. Rolling back then means copying those rows back first. Plan for this before you cut over: keep a change log or CDC stream from the new store, and rehearse the reverse sync.

What does splitting a shared table cost, and where does it save engineering time?

That rollback path is one item in a longer bill. The other costs are ongoing, and you should weigh them against the time the split gives back.

Judge the payoff by how often the shared table blocks work today, not by architectural preference. Count the coordinated releases, migration reviews and cross-team incidents that touch it.

Illustrative estimate (assumed numbers, not a benchmark):

• Monthly saving = coordinated changes per month × engineer-hours per change. Assume 6 changes × 10 hours = 60 hours per month.
• Assume the migration takes 400 hours.
• Assume pipelines and sagas then cost 15 hours per month to run.
• Net monthly saving = 60 − 15 = 45 hours.
• Break-even = 400 ÷ 45 ≈ 9 months.

Leave the table shared in either of two cases:

• Break-even is later than the point where you expect the service boundaries to change.
• The table rarely changes, so there is little coordination to save.

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 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.
• 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.