Data Management in Microservices

In a modular monolith, one PostgreSQL instance gives you ACID transactions that span orders, payments, and inventory. Schema migrations run once; reporting JOINs answer cross-domain questions in milliseconds; foreign keys enforce referential integrity in the database. Microservices push toward database per service—each team owns schema evolution, backup strategy, and scaling knobs. The instant storage splits, there is no atomic commit across services without distributed protocols that most teams deliberately reject for online traffic.

The replacement toolbox is explicit: eventual consistency with documented lag budgets, sagas for multi-step writes, CQRS and API composition for reads, transactional outbox for reliable integration events. None of these are free—product UX must show “pending” states, operations monitors projector lag, and engineers reason about failure mid-saga with correlation IDs.

Why separate databases

Independent schema evolution — Order Service adds a delivery_slot column on Friday without a company-wide migration window or risking Inventory Service’s deployment calendar. Technology fit — choose storage for access patterns, not corporate standardization on one RDBMS shape. Failure isolation — a runaway analytics query on a shared reporting replica should not lock the checkout row. Team autonomy — bounded context from Service Design maps cleanly to an owned schema and backup/restore runbook.

Polyglot persistence — right store per job

Polyglot persistence means different services (or different aggregates within a platform) use different storage engines. The goal is not novelty—it is matching consistency, query shape, and throughput to the problem.

Order Service might use PostgreSQL for transactional state while Search Service projects the same domain events into Elasticsearch— two stores, one logical bounded context split by command vs query (CQRS), or two services with clear ownership boundaries. Operational cost rises with each engine: backups, monitoring, on-call runbooks, and hiring expertise multiply.

Shared database anti-pattern — why it persists and why it is wrong

Shared databases remain common because they are the path of least resistance during early microservice adoption: teams split HTTP services first but keep one database “temporarily” so reports still JOIN and transactions still work. That temporary state often lasts years.

What goes wrong:

• Coupled deployments — renaming a column in “your” table breaks three other services’ ORM mappings; every release becomes coordinated.
• Unclear ownership — no one owns migration policy; DBAs become bottlenecks; teams blame each other for locks and slow queries.
• Leaky boundaries — Service A runs SQL against tables owned by Service B; domain language leaks into foreign schemas.
• False microservices — independent deployability is a lie if schema is a shared contract; you have a distributed monolith with network overhead.
• Scaling trap — cannot scale Order write path independently from Analytics read load on the same instance.

Acceptable exception: strangler migration phase where legacy monolith DB is read through an anti-corruption layer while data is copied to new service stores— with a written exit date and feature flags, not “we’ll fix it later.” See Strangler Fig.

What ACID gives you in a single DB — and what breaks when split

Checkout that debits inventory and charges a card in one monolith transaction becomes: local transaction in Inventory, local transaction in Payment, coordination in between over unreliable network with timeouts and retries. Retries without idempotency double-charge; synchronous coupling without timeouts hangs thread pools—see Resilience → Timeouts.

Two-Phase Commit (2PC) — coordinator and participants

Classic 2PC uses a coordinator and participants (databases or resource managers):

1. Phase 1 — Prepare — coordinator asks each participant to vote; participants write durable prepare records and hold locks/resources, reply YES/NO.
2. Phase 2 — Commit or Abort — if all YES, coordinator sends COMMIT; any NO or timeout → ABORT all.

sequenceDiagram
  participant C as Coordinator
  participant A as Order DB
  participant B as Payment DB

  C->>A: prepare
  C->>B: prepare
  A-->>C: vote YES
  B-->>C: vote YES
  C->>A: commit
  C->>B: commit

Why microservices avoid 2PC for request paths:

• Blocking — participants hold locks through prepare until commit/abort; coordinator crash leaves participants stuck in doubt until recovery.
• Availability — any participant slow or down blocks the global transaction; CAP forces choosing consistency over availability under partition.
• Latency — two network round-trips minimum across every resource; multi-region checkout becomes unusable.
• Operational fragility — XA transactions between Java and heterogeneous stores are brittle; cloud-managed DBs discourage external 2PC coordinators.

2PC still appears inside single distributed databases (Spanner, Galera) where the engine is the coordinator—different problem than orchestrating Payment + Inventory microservices over HTTP.

BASE — the pragmatic microservices posture

Where ACID is the monolith default, microservices often adopt BASE:

• Basically Available — system responds even under partial failure or partition; degraded responses beat total outage.
• Soft state — state may change without new input (async projectors catching up, replication lag, saga in COMPENSATING).
• Eventually consistent — if updates stop, replicas and read models converge to the same state.

BASE is not “ignore consistency”—it means explicit consistency boundaries: strong inside one service’s aggregate transaction; eventual across services with sagas, outbox, and idempotent consumers. Product and support must understand intermediate states (payment pending, inventory reserved).

Eric Brewer’s CAP theorem: for a distributed data store, during a network partition (messages lost or delayed between nodes), you cannot simultaneously guarantee:

• Consistency (C) — every read receives the most recent write or an error (linearizable / strong consistency).
• Availability (A) — every request to a non-failing node receives a non-error response (no guarantee it is the latest write).
• Partition tolerance (P) — system continues despite arbitrary message loss between nodes (required in real networks).

Since partitions happen in production, the practical choice during a split is CP or AP for that operation—not a one-time logo pick for the whole company.

Amazon Dynamo shopping cart: availability during partition means cart merges on reconciliation—better empty cart confusion than hard outage during network blip. Bank ledger inside the same company often stays CP on PostgreSQL: reject write if quorum unavailable rather than double-spend. Microservice teams map this per operation: inventory reservation may need strong consistency within Inventory DB; recommendation scores can be AP with minutes of lag.

PACELC — latency when the network is healthy

Daniel Abadi’s PACELC extends the lens: if Partition, choose A or C; Else (normal operation), choose between Latency and Consistency.

Even without partition, synchronous replication to three regions increases write latency. Async replication to read replicas is fast but readers see stale data—classic EL trade-off. Conversation template: “For operation X, max staleness is Y seconds; for operation Z, we require linearizable reads and accept +40 ms.”

There is no global two-phase commit. Instead you accept visible intermediate state: inventory reserved while payment is in flight; order marked PAYMENT_FAILED after compensation. Sagas require idempotent steps, durable saga state, correlation IDs, and reliable event publication—usually via transactional outbox.

Choreography-based saga

Services react to each other’s domain events without a central orchestrator. Order Service publishes OrderPlaced after local commit; Inventory listens, reserves stock, publishes InventoryReserved or ReservationFailed; Payment listens and charges card; Order listens for outcome and confirms or cancels.

Pros:

• Loose coupling—no central orchestrator dependency or deployment bottleneck
• Aligns with event-driven architecture and Kafka topology
• Each service owns its reactions—fits team boundaries

Cons:

• Hard to track global state — “where is order 48291?” requires tracing events across five topics
• Cyclic dependencies — A waits for B waits for A if event chains poorly designed
• Implicit workflow — onboarding engineers must read event catalog, not one state machine diagram
• Compensation scatter — each service must know which compensating event to emit on failure

flowchart LR
  O[Order Placed event] --> I[Inventory reserves]
  I --> E1[InventoryReserved event]
  E1 --> P[Payment charges]
  P --> E2[PaymentFailed event]
  E2 --> I2[Inventory releases]

Orchestration-based saga

A central saga orchestrator (dedicated service or workflow engine) sends commands to participants, tracks state machine (STARTED → INVENTORY_RESERVED → PAYMENT_PENDING → COMPLETED), and decides next step or compensation on failure.

Pros:

• Visible end-to-end flow in one place—easier debugging and support tooling
• Centralized timeouts, retries, and policy (“skip loyalty points if Payment slow”)
• Easier to change workflow without rewiring five event consumers

Cons:

• Orchestrator becomes critical dependency—must be HA and versioned carefully
• Risk of “god orchestrator” accumulating business rules that belong in domain services
• Participants can drift toward anemic CRUD if all logic lives in orchestrator

sequenceDiagram
  participant O as Order Service
  participant S as Saga Orchestrator
  participant I as Inventory
  participant P as Payment

  O->>S: startPlaceOrder
  S->>I: reserveStock
  I-->>S: OK
  S->>P: chargeCard
  P-->>S: declined
  S->>I: releaseStock
  S-->>O: failed

Compensating transactions

Compensations are semantic undo, not automatic DB rollback:

• Reserve inventory → compensate with release reservation
• Charge card → compensate with refund (may be async and subject to payment provider SLA)
• Send email → compensate with send cancellation email (often no undo—design idempotent “ignore if already sent”)

Compensations must be idempotent—orchestrator may retry releaseStock three times after timeout. Some steps are non-compensatable (physical shipment)—sagas must not start irreversible work until prior steps succeed or use human workflow.

Forward recovery vs backward recovery

Production sagas combine both: forward retry with exponential backoff on 503/timeout; backward compensation on business rejection or exhausted retries. Saga state table records COMPENSATING until all compensations ack—never delete saga row on first failure.

Axon Framework and Temporal as orchestration tools

Axon Framework (Java) — DDD-friendly command/event model, saga manager with annotation-based flows, integrates with Axon Server or Kafka for event bus. Fits teams already doing event sourcing/CQRS in JVM estate. Saga state persisted; associates events with @SagaEventHandler; good when orchestration stays in-process with domain language.

Temporal (polyglot workflow engine) — durable execution: workflow code looks sequential but survives process restarts; activities call Inventory/Payment with configured retries; workflow history is source of truth. Strong when sagas are long-running (days), need human approval steps, or span many languages. Spring integration via Temporal Java SDK; workers poll task queues—ops runs Temporal cluster separately.

@WorkflowInterface
public interface PlaceOrderWorkflow {
  @WorkflowMethod
  void placeOrder(PlaceOrderRequest request);
}

public class PlaceOrderWorkflowImpl implements PlaceOrderWorkflow {
  private final InventoryActivities inventory = Workflow.newActivityStub(
      InventoryActivities.class,
      ActivityOptions.newBuilder().setStartToCloseTimeout(Duration.ofSeconds(30)).build());

  public void placeOrder(PlaceOrderRequest req) {
    try {
      inventory.reserve(req.orderId(), req.lines());
      payment.charge(req.orderId(), req.amount());
      order.confirm(req.orderId());
    } catch (PaymentDeclinedException e) {
      inventory.release(req.orderId());
      order.fail(req.orderId());
    }
  }
}

Outbox pattern + saga — reliable saga events

Choreographed sagas fail if Order commits but OrderPlaced never reaches Kafka—Inventory never reserves, payment never runs, order stuck forever. Every saga step that publishes an event must use transactional outbox in the same DB transaction as business write. Orchestrated sagas likewise persist orchestrator state + outbox for outbound commands/events atomically.

Why separate commands and queries

Writes want normalized tables, invariants, small transactional boundaries, optimistic locking, and audit trails. Reads want denormalized shapes for screens, full-text search, aggregations, and cache-friendly DTOs. One JPA entity serving REST GET and POST leads to over-fetching, lazy-load explosions, and cache invalidation spanning the whole aggregate graph. CQRS accepts that the model optimized for PlaceOrderCommand is not the model for OrderSearchQuery.

Simple CQRS — same database, separate models

Single PostgreSQL instance; different classes and repositories: OrderCommandRepository loads aggregate for writes; OrderSummaryQueryDao runs tailored SQL or views for reads. No async lag; lowest operational cost. Good first step when read complexity grows but load is moderate.

Full CQRS — separate read and write stores

Write store: normalized PostgreSQL holding aggregates and business invariants. Read store: Elasticsearch index, Redis materialized view, or dedicated order_read tables optimized per screen. Writes never query read store; reads never touch write tables directly—only projectors update read side.

flowchart LR
  CMD[Commands] --> W[(Write store)]
  W --> EV[Domain events]
  EV --> PR[Projector]
  PR --> R[(Read store)]
  Q[Queries] --> R

Synchronizing read and write stores via events

After successful command transaction, emit event (via outbox): OrderPlaced. Projector consumer updates read model: insert row in order_summaries, index document in Elasticsearch. Lag is typically milliseconds to seconds—UI shows “Processing…” or polls until read model catches up for read-your-writes scenarios. Projectors must be idempotent (at-least-once delivery) and handle out-of-order events where business allows.

CQRS + Event Sourcing as natural pairing

Event sourcing makes the write side an append-only event log; CQRS read models are projections of that log—see Event Sourcing. You can do CQRS without ES (CRUD write DB + event/projector to read store), or ES without full CQRS (replay to single materialized view). Together they give audit, replay after projector bug fix, and independent scaling of read tiers—but operational complexity is highest.

Events as source of truth

CRUD updates rows in place—previous values lost unless you bolt on audit tables. Event sourcing persists immutable facts in past tense: OrderCreated, LineItemAdded, OrderPlaced, OrderCancelled. Aggregate reload: fold(empty, events) → current Order state. Deletes become compensating events (LineItemRemoved), never physical DELETE of history.

Replaying events — rebuild, time travel, audit

• Rebuild read models — projector had bug; fix code, replay log from offset 0 into new Elasticsearch index.
• Time travel — “account balance at 2024-12-31 23:59” by replaying events until timestamp cutoff.
• Audit log — regulators and support ask what happened; answer is the event stream, not guessing from current row.
• Debugging — reproduce production aggregate state in test by replaying same event sequence.

Snapshots — performance optimization

Replaying 80,000 events per aggregate on every command is too slow. Periodic snapshot persists materialized state at sequence N (JSON blob or serialized aggregate). Load path: fetch latest snapshot + events with sequence > N. Tune snapshot frequency: every 100 events vs every 1000—balance storage vs replay CPU on hot aggregates.

Event schema evolution — versioning and upcasting

Events are immutable history—you cannot UPDATE old JSON in the log. Strategies:

• Upcasting — on read, transform OrderPlacedV1 → OrderPlacedV2 in memory before apply.
• Dual schema period — writers emit V2 only; projectors handle both V1 and V2 during migration.
• Weak schema — optional fields, unknown fields ignored (careful with semantic changes).
• New stream type — OrderPlacedV2 as separate event type; projectors map both to same read model.

Breaking changes (split currency field into amount + code) need explicit migration plan—replay old events through upcaster in projector tests.

Eventual consistency implications for queries

Commands append to log; read models update asynchronously—queries immediately after command may not see write on read side. Mitigations: return command acceptance with write-model version; confirmation screen reads write aggregate synchronously; client polls read API until version ≥ expected; or synchronous projection for critical path only (defeats some CQRS benefits).

Event stores — EventStore, Axon, Kafka

When event sourcing is overkill

• Simple CRUD admin entities with no audit requirement
• Low-value domains where replay and versioning cost exceeds benefit
• Teams without ops maturity to monitor lag, disk growth, and poison event handling
• Reporting that only needs nightly batch ETL from current state

Event sourcing shines for: financial ledgers, order lifecycle, collaborative domains with conflict resolution, and systems requiring regulatory audit trails.

The problem

Product detail page needs: title and images from Catalog, price from Pricing, stock from Inventory, reviews from Reviews, shipping estimate from Fulfillment. Each owns a private database—one query cannot span them. Options: composition at request time, or pre-joined read model updated asynchronously.

API Gateway / BFF aggregation

API Gateway (Spring Cloud Gateway, Kong) or BFF fans out parallel HTTP/gRPC calls, merges JSON, handles partial failure (show product without reviews if Reviews is down). See Communication → BFF and API Gateway.

public Mono<ProductPageDto> loadPage(String sku) {
  Mono<ProductDto> product = catalogClient.getProduct(sku);
  Mono<PriceDto> price = pricingClient.getPrice(sku);
  Mono<StockDto> stock = inventoryClient.getStock(sku);
  Mono<ReviewsDto> reviews = reviewsClient.getReviews(sku)
      .onErrorReturn(ReviewsDto.empty());

  return Mono.zip(product, price, stock, reviews)
      .map(t -> ProductPageDto.of(t.getT1(), t.getT2(), t.getT3(), t.getT4()));
}

Downsides: latency ≈ slowest dependency; availability = product of availabilities; composer becomes change hotspot when any downstream API changes. Mitigate with timeouts, circuit breakers (Resilience), caching stable catalog fields, and limiting fan-out depth.

CQRS read model as pre-joined denormalized view

Instead of five REST calls per page view, a projector listens to ProductUpdated, PriceChanged, StockAdjusted and maintains product_page_documents in Elasticsearch or PostgreSQL read table—one query serves the screen. Trade-off: eventual consistency and projector maintenance vs request-time composition simplicity. Hot paths with high QPS almost always move to materialized read models after composition pain appears in production metrics.

The dual-write problem

Naive sequence A: commit order row → publish to Kafka. If broker is down after commit, downstream never sees OrderPlaced—saga stuck, search index stale forever. Sequence B: publish first → DB rollback on error. Consumers saw ghost event for order that does not exist—Inventory reserves phantom stock. There is no distributed atomicity between PostgreSQL and Kafka without outbox or XA (which you rejected).

Outbox table — same transaction as business data

In the same @Transactional method: INSERT into orders AND INSERT into outbox. Either both commit or neither. Relay process publishes from outbox to message broker asynchronously.

CREATE TABLE outbox (
  id            UUID PRIMARY KEY,
  aggregate_id  VARCHAR(64) NOT NULL,
  event_type    VARCHAR(128) NOT NULL,
  payload       JSONB NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at  TIMESTAMPTZ
);
CREATE INDEX outbox_unpublished ON outbox (created_at) WHERE published_at IS NULL;

@Transactional
public OrderId placeOrder(PlaceOrderCommand cmd) {
  Order order = orderRepository.save(Order.create(cmd));
  outboxRepository.save(OutboxEntry.builder()
      .aggregateId(order.id().toString())
      .eventType("OrderPlaced")
      .payload(json.writeValueAsString(OrderPlacedEvent.from(order)))
      .build());
  return order.id();
}

Polling publisher vs Debezium CDC

sequenceDiagram
  autonumber
  participant App as Order Service
  participant PG as PostgreSQL
  participant RL as Relay or Debezium
  participant KA as Kafka

  rect rgb(30, 40, 70)
    Note over App,PG: Single local transaction
    App->>PG: INSERT orders
    App->>PG: INSERT outbox
    PG-->>App: COMMIT
  end

  RL->>PG: Read new outbox rows
  RL->>KA: Publish OrderPlaced
  RL->>PG: Mark published or offset

Transactional outbox in Spring with Debezium

Typical stack: PostgreSQL with wal_level=logical, Debezium PostgreSQL connector monitoring public.outbox, Kafka topic per event type or single topic with headers. Application only writes business + outbox rows—no KafkaTemplate.send in request thread. Consumer services use idempotent handlers keyed by outbox.id or business idempotency key.

{
  "name": "order-outbox-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "postgres",
    "database.dbname": "orders",
    "table.include.list": "public.outbox",
    "plugin.name": "pgoutput",
    "transforms": "outbox",
    "transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter",
    "transforms.outbox.route.field.event.key": "aggregate_id"
  }
}

Debezium Outbox Event Router maps event_type column to Kafka topic name and payload envelope— reduces custom relay code. Sagas depend on this reliability: every choreography step and orchestrator command publication should originate from outbox. Deeper relay comparison: Communication → Outbox.