How to Split a Shared Database Between Microservices Without Downtime

What splitting a shared database involves, and who should do it

Splitting a shared database is a sequence of small steps, each of which you can reverse on its own: establish ownership, hide tables behind an API, copy and sync, verify, move reads, move writes, then remove the old path. Copying rows is the easy part. The hard part is knowing, at every step, which store is the source of truth. If that is ever unclear, you get silent divergence.

Teams do this to deploy independently, change schemas without cross-team coordination, and isolate load and failures so one service's slow query no longer degrades another. It fits backend and platform teams whose services are blocked by a shared schema.

Map the coupling first

Before changing anything, list four things for each table:

• Readers and writers: which services read and write the table.
• Cross-boundary links: foreign keys and joins that cross the proposed boundary.
• Batch work: batch jobs and reports that touch the table.
• Shared transactions: transactions that span both domains.

This map sets the boundary you can actually cut. The org chart does not.

Step 1: Establish logical ownership before moving any data

Once the boundary is drawn, make it real before any bytes move. Pick one service as the owner of each table and make it the only writer. Every other service either calls the owner's API or reads from a view the owner publishes. At this stage nothing changes physically. The tables stay in the same database. Only the rules about who may touch them change.

Enforce those rules in the database, not in a wiki page. Give each service its own role and credentials instead of one shared application login. The owner's role gets full DML on its tables. Consumer roles lose direct access through REVOKE ALL ON orders FROM billing_svc and instead get GRANT SELECT on a view such as orders_public. That view exposes only the columns the owner agrees to support. Any code path you missed in the dependency map now fails with a permission error at the moment it runs. A silent write that corrupts state later is much harder to trace. Roll out the revokes per consumer and watch the database error logs. Each denial you find is an undocumented dependency.

This is the cheapest de-risking step because it needs no data copy, no sync process, and no cutover. If a grant breaks something, re-granting it is the rollback. It also forces the hard conversations about contracts early, while mistakes are still easy to undo.

Ownership can also be where you stop. Plenty of teams get most of the benefit, which is clear accountability and an explicit interface, from enforced logical ownership on shared storage. That is the main alternative to a physical split. Move to separate databases only when you need independent scaling, isolation of failures, or separate schema release cycles.

Copy-- PostgreSQL syntax. One role per service instead of a shared app login.
CREATE ROLE orders_svc  LOGIN PASSWORD 'change-me-orders';
CREATE ROLE billing_svc LOGIN PASSWORD 'change-me-billing';

-- The owning service becomes the owner of its table.
ALTER TABLE orders OWNER TO orders_svc;

-- Remove inherited and direct access for everyone else, then grant the owner full DML.
REVOKE ALL ON orders FROM PUBLIC;
REVOKE ALL ON orders FROM billing_svc;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO orders_svc;

-- The published read contract: only the columns the owner agrees to support.
-- Internal columns such as internal_notes stay hidden.
CREATE VIEW orders_public AS
  SELECT id, customer_id, status, total_cents, created_at
  FROM orders;

-- The view belongs to the owner, so it reads orders with the owner's privileges.
ALTER VIEW orders_public OWNER TO orders_svc;

-- Consumers can read the view and nothing else.
REVOKE ALL ON orders_public FROM PUBLIC;
GRANT SELECT ON orders_public TO billing_svc;

-- Result for billing_svc:
--   SELECT * FROM orders_public;          -- allowed
--   SELECT * FROM orders;                 -- ERROR: permission denied for table orders
--   UPDATE orders SET status = 'paid' ... -- ERROR: permission denied for table orders
-- Each denial in the logs points to an undocumented dependency.

-- Rollback, if a revoke breaks a path you have not migrated yet:
-- GRANT SELECT ON orders TO billing_svc;

A PostgreSQL-style example that makes orders_svc the only writer of the orders table and gives billing_svc read-only access through the orders_public view, so any direct access to the table fails with a permission error.

Step 2: Replace cross-boundary joins, foreign keys and transactions

Take a common case: the order history page runs one query joining orders to customers on customer_id, and orders.customer_id has a foreign key to customers.id. Once customers move to another service's database, neither the join nor the constraint can exist. Choose a replacement for each query rather than one for the whole system.

Transactions that span both domains need the same treatment. Suppose "place order" currently debits customer credit and inserts the order in one transaction. A saga splits this into local steps: reserve credit, then create the order, and release the reservation if order creation fails. Compensating actions must be idempotent and retried, because they can fail too. Often the simpler choice is to redesign the workflow. Accept the order as pending and confirm it when the credit result arrives.

Write down what users may briefly see, such as pending orders or an outdated customer name. Also list each invariant that moves from the database to application code, who owns it, and how violations are detected.

Copy// Before (single DB): SELECT o.id, o.total, c.name FROM orders o
//   JOIN customers c ON c.id = o.customer_id WHERE o.customer_id = $1;
// After: orders stay local; customer names come from the customer service.

type Order = { id: string; customerId: string; total: number };
type Customer = { id: string; name: string };

const CUSTOMER_API = ""; // placeholder host

async function orderHistory(orders: Order[]) {
  // Collect distinct IDs and make ONE batch call, not one call per order (avoids N+1).
  const ids = [...new Set(orders.map((o) => o.customerId))];
  let customers = new Map<string, Customer>();

  try {
    const res = await fetch(`${CUSTOMER_API}/customers?ids=${ids.join(",")}`, {
      signal: AbortSignal.timeout(300), // bound the added latency
    });
    if (!res.ok) throw new Error(`customer service returned ${res.status}`);
    const list: Customer[] = await res.json();
    customers = new Map(list.map((c) => [c.id, c]));
  } catch (err) {
    // Degrade instead of failing the page: orders still render without names.
    console.warn("customer lookup failed, rendering without names", err);
  }

  return orders.map((o) => ({
    ...o,
    // A missing entry can mean an outage or an orphaned order (no FK anymore).
    customerName: customers.get(o.customerId)?.name ?? "Unavailable",
  }));
}

API composition replacing the orders-to-customers join, using a single batch lookup to avoid N+1 calls, a timeout to bound latency, and a degraded fallback when the customer service is down or a customer no longer exists.

Step 3: Copy and sync the data with the strangler-fig pattern

Stand up the new store with the target schema, then fill it in two phases: a backfill of existing rows and a sync of ongoing changes. Start the sync first so that no change slips between the backfill snapshot and live traffic.

The backfill copies rows in batches ordered by a stable cursor, such as a monotonic primary key. Mutable timestamps make a poor cursor because a row's position can shift while the copy runs. Each batch upserts the rows and then records the last key it processed. This makes the job resumable after a crash and idempotent when a batch is replayed. When a row arrives from both the backfill and the sync, a per-row version check resolves the overlap.

Copy[service] --tx--> [old DB: tables + outbox] --relay/CDC--> [broker] --> [consumer] --> [new DB]

Naive dual writes fail in two ways:

• Partial failure. The old commit succeeds and the new write times out, so the stores now disagree.
• Concurrent updates. Request A writes the old store, then B writes the old store, then B writes the new store, then A writes the new store. The old store ends with B's value and the new store ends with A's.

The outbox avoids both problems. The service inserts the row and an event in the same local transaction, so either both commit or neither does. A relay then publishes the outbox rows.

Delivery is at-least-once, so consumers must be idempotent. They can dedupe on event ID or apply a change only when its entity version is newer than the stored one. To keep per-entity ordering, partition the broker by entity ID.

Budget for running the relay and broker, monitoring consumer offsets and lag, and versioning the event schema so producers and consumers can deploy independently.

Copyimport uuid
import psycopg
from psycopg.types.json import Jsonb

BATCH = 1000
# Upsert that never overwrites a newer row already written by the CDC/outbox consumer.
UPSERT = """INSERT INTO orders (id, customer_id, status, version) VALUES (%s, %s, %s, %s)
ON CONFLICT (id) DO UPDATE SET customer_id = EXCLUDED.customer_id,
  status = EXCLUDED.status, version = EXCLUDED.version
WHERE orders.version < EXCLUDED.version"""

def ship_order(old: psycopg.Connection, order_id: int) -> None:
    # Row change and event commit together or not at all; the relay publishes outbox rows later.
    with old.transaction():
        (version,) = old.execute("UPDATE orders SET status = 'shipped', version = version + 1 "
                                 "WHERE id = %s RETURNING version", (order_id,)).fetchone()
        old.execute("INSERT INTO outbox (event_id, aggregate_id, aggregate_version, event_type, payload) "
                    "VALUES (%s, %s, %s, 'OrderShipped', %s)",
                    (uuid.uuid4(), order_id, version, Jsonb({"id": order_id, "status": "shipped"})))

def backfill(old: psycopg.Connection, new: psycopg.Connection) -> None:
    # Resume from the last committed key; a replayed batch is harmless because of the version check.
    (last_id,) = new.execute("SELECT last_id FROM backfill_checkpoint WHERE job = 'orders'").fetchone()
    while True:
        rows = old.execute("SELECT id, customer_id, status, version FROM orders "
                           "WHERE id > %s ORDER BY id LIMIT %s", (last_id, BATCH)).fetchall()
        if not rows:
            break
        with new.transaction():  # rows and checkpoint advance atomically
            with new.cursor() as cur:
                cur.executemany(UPSERT, rows)
            last_id = rows[-1][0]
            new.execute("UPDATE backfill_checkpoint SET last_id = %s WHERE job = 'orders'", (last_id,))

if __name__ == "__main__":
    # autocommit=True so each transaction() block is a real BEGIN/COMMIT, not a savepoint.
    with psycopg.connect("postgresql://app@old-db.internal/shop", autocommit=True) as old, \
         psycopg.connect("postgresql://app@orders-db.internal/orders", autocommit=True) as new:
        backfill(old, new)

A transactional outbox write that commits the row change and its event in one local transaction, alongside a resumable keyset-cursor backfill whose versioned upsert and checkpoint commit together so replays and overlaps with the sync stream stay safe.

Step 4: Verify with reconciliation and shadow reads

Once the sync is running, you need evidence that the new store matches the old one. Running the checks continuously lets you see whether drift is growing, which a single check before cutover cannot show.

Reconciliation in three layers

• Row counts per table or tenant. These are cheap and catch gross failures such as a stalled consumer or a dropped partition. They miss rows that exist on both sides with different contents.
• Per-key checksums. Hash the business-relevant columns of each row on both sides and compare by primary key. Bucket keys by range or hash prefix so you can compare bucket checksums first and then drill down only into buckets that differ. Normalize the inputs before hashing (timestamp precision, trailing whitespace, numeric scale, NULL handling). Otherwise you will chase false positives caused by type mapping rather than real bugs.
• Sampled field-level diffs. For a random sample of keys, compare every field and log which ones disagree. This tells you why checksums differ.

Exclude rows modified within the replication lag window, or recheck them later. Otherwise in-flight writes will look like mismatches.

Shadow reads

Reconciliation compares data. Shadow reads compare behaviour. The service queries both stores, returns the old result to the caller, and asynchronously logs any difference. This catches problems that counts and checksums cannot, such as a rewritten query that sorts differently, filters soft-deleted rows inconsistently, or resolves a missing join to an empty list instead of an error. Run shadow reads on a sampled fraction of traffic and set a timeout on the new-store call so that it cannot add latency to the user-facing path.

Decide the gate first

Before you start, write down the mismatch rate and the duration that block the next step, for example "zero unexplained checksum mismatches across seven days." Setting the threshold before you see results keeps schedule pressure from redefining "good enough."

Copyimport asyncio, logging, random

log = logging.getLogger("shadow_read")
SHADOW_SAMPLE_RATE = 0.05   # compare 5% of reads; tune to the new store's capacity
SHADOW_TIMEOUT_S = 0.2      # cap time and resources spent on the new store
_pending = set()            # keep references so background tasks are not garbage-collected

async def get_order(order_id, old_repo, new_repo):
    # The old store stays authoritative. The caller only ever sees its result.
    result = await old_repo.get_order(order_id)
    if random.random() < SHADOW_SAMPLE_RATE:
        task = asyncio.create_task(_shadow_compare(order_id, result, new_repo))
        _pending.add(task)
        task.add_done_callback(_pending.discard)
    return result

async def _shadow_compare(order_id, old_result, new_repo):
    try:
        new_result = await asyncio.wait_for(new_repo.get_order(order_id), SHADOW_TIMEOUT_S)
    except asyncio.TimeoutError:
        log.warning("shadow_timeout order_id=%s", order_id)
        return
    except Exception:
        # Errors from the new store are findings to record. They must never reach the caller.
        log.exception("shadow_error order_id=%s", order_id)
        return
    old, new = old_result or {}, new_result or {}
    if old_result is None or new_result is None:
        if old_result is not new_result:   # one side found the row, the other did not
            log.warning("shadow_mismatch order_id=%s presence old=%s new=%s",
                        order_id, old_result is not None, new_result is not None)
        return
    # Field-level diff shows which column disagrees, not just that the rows differ.
    diff = sorted(k for k in old.keys() | new.keys() if old.get(k) != new.get(k))
    if diff:
        log.warning("shadow_mismatch order_id=%s fields=%s", order_id, diff)
    else:
        log.debug("shadow_match order_id=%s", order_id)  # count matches to get a mismatch rate

A sampled, timeout-bounded shadow-read wrapper that returns the old store's result and asynchronously logs any field-level differences from the new store, producing the match and mismatch counts that the pre-agreed gate is checked against.

Step 5: Cut over reads, then writes, with a rollback for each step

The cutover follows a fixed order, and each stage sits behind its own feature flag so it can be reversed without a deploy. Reads move first because a bad read can be rolled back cleanly: no data has diverged yet. Shift them gradually, by percentage or by tenant, so a defect shows up in a small slice of traffic. Writes move only after reads have run cleanly against the new store.

When you flip writes, reverse the sync direction so the old store keeps receiving every change. That reverse sync is your rollback. If you skip it, or let it lag or fail without alerting, the old store goes stale and rollback becomes one-way. Treat reverse sync health as a blocking metric during the soak.

To retire the old path, drop tables only after database access logs or grant audits show no remaining readers. Batch jobs and ad hoc reporting queries are the readers teams most often forget. Move reporting to an event stream or a warehouse feed so it no longer reads another service's tables directly.

Details that cause outages during a database split

ID generation across stores

An auto-increment sequence only guarantees uniqueness inside one database. While the old and new stores both accept inserts, during dual writes or after a rollback, each generates IDs independently, and the sync job will hit primary-key collisions. You can switch new rows to UUIDs, preferably time-ordered ones so B-tree indexes don't fragment. Or you can split the integer space. An ID-range allocation might look like this (illustrative): the legacy table's maximum ID is 4.2 million and grows by about 20,000 a day, so the new service's sequence starts at 50 million. That leaves years of headroom before the ranges meet. Record the boundary somewhere both teams will see it.

Timestamps and uniqueness

Values set by a database default reflect the database server's clock, time zone and precision. Values set by the application reflect each host's clock. Copied rows must keep their original timestamps. Conflict resolution such as "last write wins" can't trust clocks across machines, so compare version numbers instead. Constraints that spanned tables, such as an email unique across both users and pending invites, can't be enforced by either database once those tables are split. The owning service must enforce the constraint, typically through a reservation table it controls.

Backfills under production load

A bulk copy competes with live traffic for I/O, buffer cache and replication bandwidth. Copy in batches keyed by primary-key ranges rather than OFFSET, which rescans earlier rows. Pause between batches, run during off-peak hours, and have the job stop itself whenever replica lag exceeds a threshold. Raise the batch size only while lag stays flat.

Alternatives, cost and engineering time

Not every system needs a full split. The options differ mainly in how much parallel infrastructure you run, and for how long.

The parallel run is the expensive part. Every migrated write lands twice, and storage for those tables roughly doubles until the old copy is dropped. Feature flags and compatibility shims tend to outlive their purpose. A split abandoned halfway is worse than either endpoint. It leaves two sources of truth plus a sync path that someone must still operate.

Illustrative estimate (assumed numbers, not a benchmark). The formula is:

extra cost = (migrated storage × parallel months × storage price) + engineer-weeks

Assume 300 GB of migrated tables, a 2-month parallel run and two engineers for 6 weeks. That gives 600 GB-months of extra storage and 12 engineer-weeks. Write capacity on the target is sized for the full duplicated write rate.

The time comes back through reversibility. Each cutover step has a flag-flip rollback, so a bad step costs minutes rather than a multi-day restore. Enforced ownership and continuous reconciliation also catch drift while it affects a few rows, before it spreads across months of data.

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.