Microservices Shared Database: Untangling Services That Share Tables

What the shared database anti-pattern looks like in practice

A microservices shared database setup is one where several independently deployed services read and write the same schema, so the database becomes the real integration point. The services look separate in the deployment diagram but are coupled through tables, columns and locks.

Teams rarely choose this on purpose. Early on, one database makes delivery fast. A new service can join against existing tables instead of calling an API. A single transaction can update an order and its inventory row together. No one has to design events or handle eventual consistency. The cost appears later, when the number of services and teams grows.

Symptoms

This guide is for backend and platform engineers running several services against one database. Fixing the problem is worth it when these symptoms block independent deploys or cause recurring incidents. You can tolerate it when a small number of services share data owned by one team, schema changes are rare, and the shared database causes no operational problems.

Building a data ownership map table by table

Before moving any data, find out who actually touches it. Documentation and team memory are usually wrong about this, so collect evidence instead.

Start with a full inventory: every table, and for wide or heavily used tables, every column. Then record which services read and which write each one. There are three common sources of this evidence, and each has trade-offs:

Per-service users pay off twice. They attribute traffic now, and they become the enforcement mechanism once you start separating access.

Run collection long enough to capture monthly jobs and reporting queries. A one-day sample will miss the cron job that breaks your migration.

Next, assign exactly one owning service to each table. The owner is the service whose business rules decide what a valid row is, not simply the service that writes most often. Readers are a manageable problem. Tables with several writers are the first ones to resolve, because no single service can guarantee their invariants, and every schema change to them needs coordination across teams.

The map also tells you where to focus. Tables that have one writer and stable schemas can wait. Concentrate engineering time on the multi-writer tables and on the tables behind blocked deploys, rather than spreading effort evenly across a schema where most of the coupling is harmless.

Copy-- PostgreSQL. Give each service its own login role so every statement is attributable.
CREATE ROLE svc_orders  LOGIN PASSWORD 'placeholder-orders';
CREATE ROLE svc_billing LOGIN PASSWORD 'placeholder-billing';

-- Evidence from query logs, audit output or per-service roles, one row per observation.
CREATE TABLE table_access (
  table_name text NOT NULL,
  service    text NOT NULL,
  access     text NOT NULL CHECK (access IN ('read', 'write')),
  source     text NOT NULL,  -- 'query_log', 'audit' or 'conn_user'
  last_seen  date NOT NULL,
  PRIMARY KEY (table_name, service, access)
);

-- One owner per table: the service whose rules define a valid row.
CREATE TABLE table_owner (
  table_name    text PRIMARY KEY,
  owner_service text NOT NULL,
  rationale     text
);

-- Multi-writer tables are the first to resolve.
SELECT table_name,
       count(DISTINCT service)            AS writer_count,
       string_agg(DISTINCT service, ', ') AS writers
FROM table_access
WHERE access = 'write'
GROUP BY table_name
HAVING count(DISTINCT service) > 1
ORDER BY writer_count DESC;

-- Tables in the schema that have no owner yet.
SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN table_owner o ON o.table_name = t.table_name
WHERE t.table_schema = 'public'
  AND t.table_type = 'BASE TABLE'
  AND o.table_name IS NULL;

A PostgreSQL sketch that creates per-service login roles, records observed readers and writers in an ownership map, and queries for multi-writer tables and tables that still have no owner.

Separating access before separating storage

Moving data is expensive and hard to reverse. Changing who is allowed to touch it is cheap. Start by giving each service its own database login and its own schema, and transfer table ownership to the role of the service that writes them. Revoke the broad grants every service used to share. From then on, the database enforces the ownership map instead of relying on a wiki page.

The SQL example that accompanies this section shows the shape. It creates an orders_svc role that owns the orders schema and has full rights on it, and a billing_svc role that gets no direct grants there. Billing gets SELECT on a view such as orders.order_summary_v1 instead. Treat that view as a temporary contract. It exposes only the columns the consumer needs, and the version suffix lets the owner change the base table without breaking readers.

Run the tightened permissions in staging first. Each "permission denied" error points to a dependency nobody declared: a nightly report, an admin script, a join buried in an ORM mapping. Record each one and decide whether it gets a view or is removed.

Views are a stepping stone. Over time, move each consumer's reads to the owning service's API, so the view can be dropped once nothing queries it.

Some tables mix concerns, such as a customers table that holds both login fields and credit limits. Split these into separate tables in each domain's schema while you are still on one database. A transaction or backfill can keep the two halves consistent, which becomes much harder once they live on different servers.

Copy-- PostgreSQL. Run as the migration admin (superuser or equivalent).
-- One login role per service. Passwords are placeholders; load real ones from your secret store.
CREATE ROLE orders_svc LOGIN PASSWORD 'placeholder-orders';
CREATE ROLE billing_svc LOGIN PASSWORD 'placeholder-billing';

-- The service that writes the data owns its schema and tables.
CREATE SCHEMA orders AUTHORIZATION orders_svc;
ALTER TABLE public.orders SET SCHEMA orders;
ALTER TABLE orders.orders OWNER TO orders_svc;

-- Remove the broad grant every service used to share (app_shared is the legacy role).
REVOKE ALL ON orders.orders FROM app_shared;

-- Temporary contract for non-owners: only the columns billing needs.
-- The _v1 suffix lets orders_svc change the base table and publish a _v2 later.
CREATE VIEW orders.order_summary_v1 AS
  SELECT id, customer_id, total_cents, currency, status, placed_at
  FROM orders.orders;
ALTER VIEW orders.order_summary_v1 OWNER TO orders_svc;

-- Billing can resolve names in the schema and read the view, but not the base table.
-- The view runs with its owner's privileges, so billing needs no grant on orders.orders.
GRANT USAGE ON SCHEMA orders TO billing_svc;
GRANT SELECT ON orders.order_summary_v1 TO billing_svc;

-- Expected in staging: queries against the base table now fail with permission denied,
-- and each failure points to a dependency nobody declared.
-- SET ROLE billing_svc;
-- SELECT credit_note FROM orders.orders;           -- ERROR: permission denied for table orders
-- SELECT id, status FROM orders.order_summary_v1;  -- allowed
-- RESET ROLE;

PostgreSQL statements that give the orders service ownership of its own schema, revoke the old shared grant, and give the billing service read access to a single versioned view as a temporary contract.

How decoupling works: replacing joins, foreign keys and shared transactions

Once tables sit in separate databases, the join goes away. Replace it with one of three tools.

API composition. The caller fetches orders, then asks the customer service for those IDs in a single batch call. This is simple and the data is current. Without batching, every row adds a network call, and chains of services calling each other add latency and failure points.

Local read models. The consuming service keeps its own copy of the fields it needs and updates that copy from domain events. Reads are fast and keep working when the owner is down. The trade-off is that reads can be stale. When an event schema changes, you also have to backfill the copy by replaying history or running a bulk export.

References by ID. Keep customer_id as a plain column with no foreign key. The database no longer guards referential integrity, so the application must handle a dangling ID, for example by showing "unknown customer" or by reconciling later.

Shared transactions need a closer look. Ask which invariants actually require atomicity. Often the answer is only those inside one aggregate, and those can stay local.

To publish reliably, use a transactional outbox. In the same local transaction as the business write, insert a row into an outbox table with columns such as id, aggregate_id, event_type, payload and created_at. A relay process then publishes that row:

write + outbox row (1 tx) → relay → broker → consumer → read model

When a process spans several services, use a saga. Each step commits locally, and each step has a compensating action that undoes it if a later step fails. Some effects cannot be compensated, such as a sent email or a captured payment. Put those last. Expect saga testing to be expensive, because every partial-failure path needs its own test. Consistency now lives in your application code, not in the database.

Copy-- Outbox table lives in the same database as the orders table it describes.
CREATE TABLE outbox (
  id            UUID PRIMARY KEY,
  aggregate_id  UUID        NOT NULL,          -- e.g. the order ID
  event_type    TEXT        NOT NULL,          -- e.g. 'OrderPlaced'
  payload       JSONB       NOT NULL,          -- versioned event body
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at  TIMESTAMPTZ                    -- set by the relay after the broker accepts the message
);

-- Lets the relay find unpublished rows quickly, in order.
CREATE INDEX outbox_unpublished_idx ON outbox (created_at) WHERE published_at IS NULL;

-- The business write and the outbox row commit or roll back together.
-- customer_id is a plain reference: no foreign key into the customer service's data.
BEGIN;

INSERT INTO orders (id, customer_id, status, total_cents)
VALUES ('3f6c1e2a-8b4d-4c1e-9a7f-2d5e6b8c9a01',
        '7a2b9c4d-1e3f-4a5b-8c6d-9e0f1a2b3c4d',
        'PLACED', 4999);

INSERT INTO outbox (id, aggregate_id, event_type, payload)
VALUES ('b1d2e3f4-5a6b-4c7d-8e9f-0a1b2c3d4e5f',
        '3f6c1e2a-8b4d-4c1e-9a7f-2d5e6b8c9a01',
        'OrderPlaced',
        '{"schema_version": 1, "order_id": "3f6c1e2a-8b4d-4c1e-9a7f-2d5e6b8c9a01", "customer_id": "7a2b9c4d-1e3f-4a5b-8c6d-9e0f1a2b3c4d", "total_cents": 4999}');

COMMIT;

-- Relay (separate process): read a batch, publish to the broker, then mark as sent.
-- Delivery is at-least-once, so consumers must deduplicate on outbox.id.
SELECT id, aggregate_id, event_type, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY created_at
LIMIT 100;

A PostgreSQL outbox table and a single local transaction that writes an order and its OrderPlaced event together, plus the query a relay uses to pick up unpublished rows.

Migrating data to a database per service incrementally

Moving the data itself starts with one decision: how changes reach the new store while the old one is still authoritative. There are two common options.

Dual writes look cheap, but the two writes are never atomic. A crash between them silently diverges the stores. CDC is more reliable because the log already records every committed change. The cost is that it adds infrastructure, and the pipeline breaks when someone renames a column in the old schema.

With changes flowing, use this order:

1. Backfill historical rows in batches, idempotently, so a failed run can restart safely.
2. Verify by comparing row counts and per-row checksums between stores. Keep the checks running, because divergence often shows up days later.
3. Switch reads first, behind a flag. If the new store is wrong, flipping the flag back restores correct results, and no data has been lost.
4. Switch writes last. Before you do, reverse the sync direction so the old store stays current. That keeps a rollback path open until you decommission it.

Reports that relied on ad-hoc joins across the old tables will stop working after the split. Feed them from a separate analytical store or pipeline rather than reconnecting them to service databases.

When to stop, and alternatives to a full split

Not every shared table needs to be split. Decoupling has a running cost: duplicated data, eventual consistency, and more pipelines to operate. Stop when that cost exceeds the coupling it removes.

Reference data such as country codes, currencies, or tax categories changes rarely and has no business owner among your services. Copying it into every service's database creates sync jobs that only spread staleness around. A single owner that publishes it, with every other service reading it as read-only, is usually enough.

Tightly coupled aggregates show up when two services keep needing the same transaction. Orders and order lines are a typical case. If every saga you design between them is really just a distributed version of one atomic write, the boundary is wrong. Move both tables into one service.

Unclear boundaries are the hardest call. If the ownership map keeps changing, or most features touch both services anyway, splitting storage locks a bad domain model into network calls and event contracts. Merging the services back together is cheaper to undo than a hardened split.

Impact on development cost and engineering time

Each additional database is a recurring cost. It needs restore-tested backups, monitoring and alerting, connection pool tuning, engine upgrades, and an on-call engineer who understands its failure modes. Event infrastructure adds a broker to operate, message schemas to version, and dead-letter queues that someone has to drain. The savings come from work that stops happening: cross-team schema negotiations, incidents where one team's migration locks another team's queries, and release trains that exist only because two services read the same columns.

Illustrative estimate (assumed numbers, not a benchmark)

Monthly net hours = (T × C × h + I × h_i) − (D × o + E)

• T = teams, C = shared-table changes per team per month, h = coordination hours per change
• I = cross-team incidents per month, h_i = hours per incident
• D = new databases, o = operating hours per database, E = event infrastructure hours

With 4 teams, 6 changes each, 3 hours per change, 1 incident at 10 hours, 4 databases at 6 hours, and 20 hours of event infrastructure: (72 + 10) − (24 + 20) = +38 hours.

With 2 teams, 2 changes each, no incidents, and 2 databases: (12 + 0) − (12 + 20) = −20 hours.

These figures leave out the one-time migration effort. If you amortize it over the months you expect to keep the split, it raises the bar further for small teams with low change frequency.

Incremental steps limit how much you lose if the inputs are wrong. Steps such as routing access through an API, adding a read replica, or dual-writing can each be measured and reversed. If coordination costs turn out lower than you assumed, you stop and discard one step. A big-bang migration commits the full cost before you have any of that evidence.

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