Database per Service: Ownership, Consistency and Recovery

Evaluate database-per-service boundaries without assuming a separate server for every service. Includes an outbox failure matrix, migration gates and distributed...

Separate data ownership from database placement

Database per service means that a service controls its persistent data and exposes supported interfaces to other services. It does not automatically mean buying a separate database server for every service.

Chris Richardson's database-per-service pattern describes private tables, private schemas and separate servers as possible implementations. Distinct credentials and permissions help enforce the boundary.

The deployment choice still affects failure isolation. Separate schemas on one instance share compute, storage limits, maintenance and some recovery operations. Separate instances can isolate some of those concerns, but add operational work and do not repair a poorly chosen service boundary.

| Placement | Boundary and remaining coupling | |---|---| | Private tables or schemas on shared infrastructure | Enforce ownership with roles and grants; plan for shared resource contention, administration and recovery. | | Separate database instances | Own additional provisioning, backup, upgrade and monitoring work; service contracts still coordinate business behavior. | | One transactional model in a modular application | Keep related invariants together when independent service data ownership does not justify distributed coordination. |

Shared deployment can be a deliberate choice. Uncontrolled cross-service access is a different issue: a consumer that writes another service's tables bypasses its validation and makes internal schema changes a public dependency.

Test the transaction boundary before splitting it

List the invariants that must hold together. Examples include reserving stock without making it negative, recording a balance change with its ledger entry, or ensuring a state transition happens only once.

If an invariant must be enforced atomically, first consider whether its data belongs under one transactional owner. Splitting the tables does not make that requirement disappear.

For a cross-service workflow, agree which intermediate states the business accepts and who resolves incomplete work. An order that remains pending while inventory is reserved can be reasonable. Reporting it as confirmed before the reservation is established changes the product contract.

Also inspect the change boundary. If every release requires synchronized schema and application changes across several services, private databases may be hiding a tightly coupled design. Revisit the responsibilities before adding coordination infrastructure.

Choose how consumers obtain data

A consumer should use a supported service interface rather than treating a private table as an undocumented API. That interface can serve operational requests or a deliberately published read model.

| Read approach | Consequence to design for | |---|---| | API composition | Calls can fail independently and observe different moments. Combining responses does not create an atomic cross-service snapshot. | | Event-fed projection | Reads can be fast and tailored to a use case, but lag, duplicate events, corrections and rebuilds need explicit handling. | | Published analytical dataset | The producer owns its contract and freshness expectations; analytical access should not silently become an operational write dependency. |

Use an authoritative transaction for decisions such as allocating stock. A lagging projection is not a safe replacement merely because the user interface reads quickly.

Change data capture can support integration or migration, but a raw table change does not automatically communicate business intent. Publishing private schemas directly can bind consumers to implementation details. Define ownership, ordering and compatibility for whatever interface you expose.

CQRS and event sourcing are separate design choices. Database per service does not require either, and storing an event history does not remove the need to test reconstruction and schema evolution.

Worked artifact: order and inventory with an outbox

Consider a hypothetical order workflow. The order service owns order state. The inventory service owns stock and reservations. A pending order should become confirmed only after a valid reservation result is recorded.

A local database commit followed by a separate broker publish can fail between the two operations. The transactional outbox pattern addresses that gap by saving the business change and the message record in one local transaction, then publishing through a relay.

A possible design is:

1. The order service atomically records a pending order and an outbox message with a stable message ID and order ID.
2. A relay publishes committed messages and records its progress.
3. The inventory consumer atomically records the processed message ID and the reservation decision in its own database.
4. Inventory publishes its result through an equivalent local outbox.
5. The order service applies the result through a guarded state transition.

The consumer's deduplication record and business effect must share the relevant transaction boundary. Recording “processed” before a separate stock update can lose the effect; recording it afterward in another transaction can duplicate the effect.

AWS's outbox guidance discusses duplicate delivery, ordering and idempotent consumption. Design those properties explicitly rather than calling the system exactly once.

| Failure injection | Expected observation and recovery | |---|---| | Order commits, relay has not published | Order stays pending; the committed outbox entry remains eligible for delivery. | | Relay publishes, then crashes before recording progress | Redelivery is possible; inventory recognizes the message and does not reserve twice. | | Inventory transaction rolls back | Neither the reservation nor its deduplication marker commits; processing can retry safely under the defined policy. | | Reservation succeeds, result delivery is delayed | The order remains pending until the result is delivered or its status is reconciled. | | An older result arrives after cancellation | The order state machine rejects an invalid transition and initiates any required reservation cleanup. |

Define ordering per business entity and handling for missing or out-of-order versions. Do not infer global order from messages arriving on different partitions or streams.

This matrix is a reusable test artifact, not complete implementation code. It assumes databases can provide the stated local transactions. External effects outside those transactions need their own idempotency or reconciliation mechanisms.

Make compensation resumable

A saga coordinates local transactions and recovery actions across services. It does not provide the same isolation or rollback semantics as one database transaction.

In the example, a definitive reservation rejection can lead to order cancellation. A timeout is different: inventory may have reserved stock even though the caller did not receive the result. Reconcile by the stable business operation ID before declaring the action absent.

If cancellation requires releasing a reservation, record that work durably. Bound retries, make release idempotent and expose unresolved compensation to an operator. Do not mark the workflow fully canceled while required cleanup is merely scheduled and its outcome unknown.

Microsoft's compensating-transaction guidance explains that compensation can fail, may require manual intervention and need not restore the exact starting state. Once goods have shipped, deleting a shipment record cannot undo the physical action.

Document the point after which the workflow requires a different business process, such as a return. Surface that boundary in the API and operational tooling.

Migrate with one authoritative writer

Avoid an uncoordinated sequence that writes the old database and then the new one. A partial failure leaves conflicting records without establishing which is authoritative.

A staged migration can retain the old owner while copying a consistent baseline and applying captured changes to the target. The snapshot boundary and change-stream position must align so updates are neither lost nor misapplied. Choose tooling and a procedure that establish those guarantees for the actual database.

Use explicit cutover gates:

• Identify every writer, reader, scheduled task and reporting dependency.
• Establish the authoritative source and capture boundary.
• Reconcile keys, values, deletions and relevant business invariants at a known progress point.
• Fence the old write path during ownership transfer and account for in-flight requests.
• Switch the supported interface to the new owner, then observe writes and downstream propagation.

Read comparisons need the same logical point or known lag; comparing unrelated moments creates misleading differences. Row counts alone cannot prove equivalence.

After the target accepts new writes, returning traffic to the old database may discard or contradict them. Define reverse synchronization, reconciliation or a forward-repair plan before cutover. The database migration guide covers this release boundary in more detail.

Restore the workflow, not only the databases

Backups taken at similar times do not establish a consistent distributed recovery point. Each service may have committed different parts of a workflow, and message brokers and external systems have their own state.

PostgreSQL's point-in-time recovery documentation describes restoring a database cluster from a base backup and archived write-ahead logs. That database-level recovery does not reconcile another service's reservations or replayed messages for you.

A useful recovery exercise for the order example is to restore the order database to a point before confirmation while leaving inventory's later reservation intact. Keep production writes and uncontrolled message consumers fenced while establishing the recovery plan.

Then:

1. Identify the recovery positions of each authoritative store and relevant message stream.
2. Find orders, reservations and workflow records whose states disagree.
3. Reconcile through stable operation IDs and the owning services' rules.
4. Replay only from a known boundary using compatible consumers and sufficient retained history.
5. Verify business invariants before reopening affected operations.

Restoring an older consumer database may also restore an older deduplication ledger. A message whose effect occurred before the incident may then appear unprocessed. Account for that possibility before replaying external effects or deleting retained messages.

Record what cannot be reconstructed and who decides its resolution. Independent database restore tests are necessary, but they do not prove the full business workflow recovers correctly.

Review one ownership boundary

Before choosing separate instances, complete an ownership record for one service: data it controls, invariants it enforces, supported read interfaces, cross-service intermediate states, event replay rules and recovery responsibilities.

Use the outbox failure matrix to exercise at least one partial-commit scenario and one duplicate-delivery scenario. Use the recovery exercise to test a disagreement between stores.

Bring those results to a system architecture review. They establish whether the proposed boundary supports independent operation, or whether keeping related data together would be the safer design.