Database per Service: Data Ownership, Trade-offs and Pitfalls

What database per service means and why teams use it

The database per service pattern makes a service's persistent data private. Other services can read or change that data only through the owning service's API or the events it publishes. No other service connects to its tables, even for reads. The pattern is about access, not infrastructure. Ten services can share one database server and still follow it, as long as no service touches another's data.

Teams usually enforce this ownership at one of three isolation levels:

The pattern exists to avoid the shared database anti-pattern. When several services read and write the same tables, renaming a column or splitting a table needs sign-off from every team that queries it, so schema changes slow to the pace of the slowest team. An unindexed report run by one service can lock rows or use up I/O that another service depends on. The worst problem is hidden coupling: nothing in a service's API shows that another team relies on its table layout. You find out when a deploy breaks someone else.

Private ownership turns the schema into an implementation detail. That supports two of the forces described in the Microservice Architecture pattern: team autonomy, meaning a team can develop, test and deploy independently, and a fast deployment pipeline, because a schema migration no longer needs coordination across teams before it ships.

Who should use it, when it fits and when to avoid it

The pattern pays off when several teams own distinct business capabilities and each needs to ship on its own schedule. If the ordering team and the billing team release independently, each owning its own schema lets each team change its data model without a cross-team review.

Avoid or delay it if one team owns everything, the domain boundaries are still unclear, or two services change together in nearly every release. In those cases separate databases only add network calls and consistency work. Splitting too early, or along the wrong lines, produces chatty services that call each other several times per request to rebuild data that used to come from a single join. A failure or slow deploy in one service then blocks the others. The result is a distributed monolith: the coupling of a shared database without its transactions. If two services cannot be deployed separately in practice, merging them is usually the right call.

Two alternatives are often a better starting point:

Both alternatives keep a clear path to full separation later, provided no code reaches across the boundaries.

Drawing ownership boundaries

Start from the business entity, not the table. Every entity (an order, a customer account, a stock level) gets exactly one owning service, chosen because that service's subdomain enforces the rules governing the entity's state. If two services both need to change an order's status, one owns the order and the other asks it to make the change. Single-writer ownership keeps every invariant in one codebase, so a rule change ships in one deployment.

Other services have two options. They can call the owner's API when they need current data and can tolerate the latency and availability dependency. Or they can keep a read-only copy, built from the owner's events, when they need fast local reads or must keep working while the owner is down. The copy belongs to the consumer, but it is never a source of truth, and nothing writes back through it.

How coupling returns

Ownership usually erodes through side channels rather than through service code:

• Shared reporting replicas: analysts query a read replica of a service's tables. A column rename then breaks dashboards that the owning team never knew existed.
• ETL jobs reading tables directly: these make the internal schema a public contract.
• Unmanaged event schemas: consumers parse whatever fields happen to be present, so events become a second, unversioned API.

Detecting and preventing it

Give each service its own database credentials, and grant them access only to its own schema or database. Issue no cross-service grants, so a stray query fails immediately instead of working quietly. Audit grants and connection logs regularly for clients that are not the owner. Route reporting through events or an explicit export the owner publishes and versions.

Copy-- PostgreSQL: one role and one schema per service. Only the owner can write.
-- Run as a database admin. Passwords are placeholders; load real ones from a secret store.

-- Close the default PUBLIC access, so a role gets only what it is explicitly granted.
REVOKE ALL ON DATABASE shop FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- The orders service owns the order entity and has its own login.
CREATE ROLE orders_svc LOGIN PASSWORD 'change-me-orders';
CREATE SCHEMA orders AUTHORIZATION orders_svc;
GRANT CONNECT ON DATABASE shop TO orders_svc;

-- The inventory service owns stock levels and gets a separate login and schema.
CREATE ROLE inventory_svc LOGIN PASSWORD 'change-me-inventory';
CREATE SCHEMA inventory AUTHORIZATION inventory_svc;
GRANT CONNECT ON DATABASE shop TO inventory_svc;

-- No cross-service grants. If inventory_svc runs
--   SELECT * FROM orders.orders;
-- it gets "permission denied for schema orders" right away.
-- It never quietly reads the other service's tables.

-- Audit: list every role that holds a privilege on a table in a schema it does not own.
SELECT g.grantee, g.table_schema, g.table_name, g.privilege_type
FROM information_schema.role_table_grants g
JOIN pg_namespace n ON n.nspname = g.table_schema
WHERE g.table_schema IN ('orders', 'inventory')
  AND g.grantee <> pg_get_userbyid(n.nspowner)
ORDER BY g.table_schema, g.grantee;

-- Audit: list live connections from roles that are not service owners
-- (for example, a BI tool or an ETL job that reuses someone else's credentials).
SELECT usename, application_name, client_addr, backend_start
FROM pg_stat_activity
WHERE datname = 'shop'
  AND usename NOT IN ('orders_svc', 'inventory_svc');

This PostgreSQL example gives each service its own login role and schema with no grants across services, so a cross-service query fails right away, and it includes two audit queries that find stray grants and connections from clients that don't own the data.

How it works: sagas and reliable event publishing

Without a shared database, a write that spans services can't be one ACID transaction. A saga splits it into local transactions, one per service. Each step commits on its own and triggers the next. If a later step fails, earlier steps are undone by compensating actions, such as refunding a payment or cancelling an order.

In a choreographed saga, each service reacts to the others' events. It is simple for a few steps, but the overall flow is hard to see. An orchestrated saga puts the sequence and the compensations in one coordinator, which makes longer flows easier to trace and change.

Sagas give up isolation. Other transactions can see intermediate states between steps, which allows dirty reads and lost updates. The common countermeasure is a semantic lock: mark records with a pending state such as APPROVAL_PENDING so other operations know the data is not final yet. Commutative updates and rereading values before writing also help.

Each step must also publish its event reliably. Writing to the database and then to a broker is a dual write. If the process crashes between the two, state and events diverge. The transactional outbox fixes this. The service inserts the event into an outbox table in the same local transaction as the state change. A relay process, or change data capture reading the database log, then publishes it:

Order svc → [orders + outbox, one commit] → relay/CDC → broker → Payment svc

Delivery is at-least-once, so consumers must be idempotent.

Copy-- Outbox table. It lives in the same database as the service's own tables (PostgreSQL syntax).
CREATE TABLE outbox (
  id             uuid        PRIMARY KEY,           -- event ID; consumers use it to drop duplicates
  aggregate_type text        NOT NULL,              -- e.g. 'Order'; the relay can map this to a topic
  aggregate_id   text        NOT NULL,              -- e.g. the order ID; usable as the partition key
  event_type     text        NOT NULL,              -- e.g. 'OrderCreated'
  payload        jsonb       NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  published_at   timestamptz                        -- NULL until the relay has sent it
);

-- Saga step 1 in the Order service. The order and its event commit together or not at all.
BEGIN;

INSERT INTO orders (id, customer_id, total_cents, status)
VALUES ('ord-1001', 'cust-42', 4999, 'APPROVAL_PENDING');  -- semantic lock: not final yet

INSERT INTO outbox (id, aggregate_type, aggregate_id, event_type, payload)
VALUES (
  '7f3c2a9e-1b4d-4c8e-9a61-2d5f0e8b7c13', 'Order', 'ord-1001', 'OrderCreated',
  '{"orderId": "ord-1001", "customerId": "cust-42", "totalCents": 4999}'
);

COMMIT;  -- if this fails, neither the order nor the event exists, so nothing diverges

-- A polling relay claims a batch of unsent events. SKIP LOCKED lets several relay
-- instances run without picking the same rows. (A CDC connector would read the
-- write-ahead log instead and skip this query.)
SELECT id, aggregate_id, event_type, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY created_at
LIMIT 100
FOR UPDATE SKIP LOCKED;

-- Mark rows as sent only after the broker acknowledges them. A crash before this
-- line means the events get sent again, which is why delivery is at-least-once.
UPDATE outbox SET published_at = now() WHERE id = '7f3c2a9e-1b4d-4c8e-9a61-2d5f0e8b7c13';

The Order service writes a pending order and its OrderCreated event in one local transaction, and a polling relay claims and marks unsent outbox rows, which explains why consumers can receive an event more than once.

Querying across services and managing duplicated data

every consumer of these events must tolerate duplicates. That constraint shapes how you read data that spans services.

Rules for duplicated data

• Copy only the fields you query on. Each extra field is another schema change to track and another copy to purge.
• Make consumers idempotent. For example, record each processed event ID in the same transaction as the projection update, and skip any event whose ID is already recorded. A redelivered PaymentCaptured then becomes a no-op instead of a double credit.
• Preserve ordering per entity. Partition by aggregate ID, or store a per-entity version and ignore events older than the stored one.
• Plan replays. A new consumer or a corrupted projection has to rebuild from history. Keep the events available long enough to replay them, or have a snapshot-plus-tail backfill procedure.

Stale reads and deletion

A user who submits an order and then opens a projection-backed list may not see the order yet. Read the user's own writes from the owning service, or return the new version and have the UI wait until the projection reaches it. Deletion requests have to reach every copy. Publish an explicit deletion event and keep a list of each projection, cache, and backfill source it must purge.

Copy-- PostgreSQL. Dedup table: one row per event this consumer has applied.
CREATE TABLE processed_events (
  consumer   text        NOT NULL,
  event_id   uuid        NOT NULL,
  applied_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer, event_id)
);

-- Projection keeps only the fields that are queried, plus a per-order version.
CREATE TABLE order_summary (
  order_id    uuid    PRIMARY KEY,
  status      text    NOT NULL,
  amount_paid numeric NOT NULL DEFAULT 0,
  version     bigint  NOT NULL
);

-- Handle one PaymentCaptured event.
-- $1 = event_id, $2 = order_id, $3 = amount, $4 = order version from the event.
BEGIN;

WITH claimed AS (
  -- A redelivered event conflicts here, returns no row, and changes nothing below.
  INSERT INTO processed_events (consumer, event_id)
  VALUES ('order-summary', $1)
  ON CONFLICT DO NOTHING
  RETURNING event_id
)
INSERT INTO order_summary (order_id, status, amount_paid, version)
SELECT $2, 'PAID', $3, $4 FROM claimed
ON CONFLICT (order_id) DO UPDATE
  SET status      = EXCLUDED.status,
      amount_paid = EXCLUDED.amount_paid,  -- set the value, never add to it
      version     = EXCLUDED.version
  -- Ignore events older than the version already stored for this order.
  WHERE order_summary.version < EXCLUDED.version;

COMMIT;  -- the dedup record and the projection change commit together or not at all

This PostgreSQL consumer records each event ID and updates the projection in the same transaction, so a redelivered PaymentCaptured changes nothing and an out-of-order event older than the stored version is ignored.

Implementing it: storage choices and migrating off a shared database

Database per service means each service owns its data store. It does not mean each service has to run a different engine. You can give one service a document store or a search index, but every extra engine comes with its own backup and restore procedure, upgrade cycle, monitoring and failure modes. Someone on call has to understand all of that in the middle of the night. Default to one engine, with a separate schema or instance per service. Add another engine only when a service has a clear need the default can't meet, such as full-text search or an access pattern that works against the relational model.

Migrating off a shared database

1. Map table ownership. For every table, record which services write to it and which read from it. Tables with more than one writer are the hard cases, so settle who owns them before you move anything.
2. Stop cross-service writes first. Send every write to a table through its owner's API. Writes are where invariants break. Once only the owner writes, it can change the schema without corrupting another team's logic.
3. Replace cross-service reads. Use API calls, events that feed a local copy, or a read-only view that the owner maintains as a versioned contract.
4. Move data with a temporary sync. Copy the tables to the new store, keep both sides in sync, and shift traffic over gradually while you compare results. Remove the sync once it is no longer needed. A big-bang cutover leaves you no way back if something goes wrong.

You need observability from the first step. Pass correlation IDs through every request and message, and trace calls across services. Have tooling in place to inspect, redeliver and drain dead-letter queues before the migration starts, not after the first stuck message.

Copy-- PostgreSQL: lock down a shared database during migration.
-- One engine, one schema per service. Roles are placeholders.

-- Step 2: only the owner may write to its tables.
REVOKE INSERT, UPDATE, DELETE, TRUNCATE
  ON ALL TABLES IN SCHEMA orders
  FROM billing_svc, shipping_svc;

GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA orders
  TO orders_svc;

-- Step 3: replace direct reads of base tables with a
-- versioned, read-only view that the owner maintains.
REVOKE SELECT ON ALL TABLES IN SCHEMA orders
  FROM billing_svc, shipping_svc;

CREATE VIEW orders.order_summary_v1 AS
SELECT o.id          AS order_id,
       o.customer_id,
       o.status,
       o.total_cents,
       o.updated_at
FROM orders.orders AS o;

-- The view runs with its owner's privileges by default,
-- so consumers need no access to the base table.
-- They still need USAGE on the schema to resolve the view.
GRANT USAGE ON SCHEMA orders TO billing_svc, shipping_svc;
GRANT SELECT ON orders.order_summary_v1 TO billing_svc, shipping_svc;

-- Breaking changes ship as order_summary_v2 alongside v1.
-- Drop v1 only after every consumer has moved off it.

PostgreSQL grants that stop cross-service writes on a shared database and replace direct table reads with a versioned, read-only view owned by the orders service.

Impact on development cost and engineering time

That tooling is part of the bill. The pattern swaps coordination cost for infrastructure and design cost. Whether the swap pays off depends mostly on how many teams would otherwise share one schema.

Illustrative estimate (assumed numbers, not a benchmark)

• Shared-database coordination cost per month: C_shared = h × T(T−1)/2, where T is the number of teams and h is the hours of negotiation per team pair. The cost grows with the number of team pairs, because any schema change can affect any other team.
• Database-per-service overhead per month: C_split = s × S, where S is the number of services and s is the operational hours per service-owned store.

Assume h = 4, s = 6 and one service per team:

This model leaves out incident containment and debugging time. Adjust s upward if your saga logic is complex.

Checklist

• Do more than three or four teams change the same schema?
• Are coordinated migrations delaying releases?
• Can each team own its database operations, including backups, upgrades and credentials?
• Do you already have tracing, lag alerts and dead-letter tooling?
• Can the business tolerate eventual consistency on the affected reads?

If most answers are no, keep the shared database and enforce module boundaries inside it.

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 database per service, 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.