Data Management

Why Data Management is the Hardest Part

Most engineers underestimate this. They will spend three months extracting a payment service from a monolith, deploy it triumphantly, and then discover that the new service still JOINs six tables in the legacy database because nobody wanted to fight that battle. That is not a microservice. That is a deployment unit pretending to be a microservice while the data layer is still a monolith. The pattern has a name: distributed monolith. It has all the operational pain of microservices and none of the independence benefits.

Real microservices means real data ownership. Which means giving up the conveniences of a single relational database: foreign keys across boundaries, multi-table transactions, ad-hoc reporting joins, “just look it up” debugging. In exchange you get independent scaling, independent deploys, blast-radius containment, and the freedom to pick the right database for each workload. The trade is real and it is permanent.

Database-per-Service

Database-per-service is the foundational rule. Break it and most of the other patterns in this tutorial stop working. The rule is strict: no other service connects to your database. Period. Not for “just a quick read.” Not for the analytics team. Not for the BI dashboard. Other consumers either go through your service’s API or subscribe to events you publish.

Polyglot Persistence

Database-per-service unlocks something the monolith couldn’t do: pick the right database engine for each service’s data shape. The order service has transactional, relational data — Postgres. The product catalog needs full-text search and faceted filtering — Elasticsearch or OpenSearch. The cart is ephemeral key-value — Redis or DynamoDB. The recommendation graph is, well, a graph — Neo4j. Each service picks what fits.

Drawing the boundaries

The hard part of database-per-service is figuring out where to draw the lines. Service boundaries should align with bounded contexts (in DDD terms). The signs you drew the line wrong:

• Two services frequently call each other in the same request — you split a single transaction across services.
• You find yourself doing distributed joins — the data should be co-located.
• You need a saga for a flow that feels like one operation — you over-decomposed.
• One service’s deploy keeps breaking another — the contract is too thin or wrong.

If any of these are happening, the right answer is usually merge the services back together, not add more coordination patterns. Microservices are a deployment optimization, not a moral imperative.

The Distributed Transaction Problem

Two-phase commit and why nobody uses it

The classic textbook answer is two-phase commit (2PC). A coordinator asks every participant “can you commit?”, and only if everyone says yes does it tell them to actually commit. In theory this gives you ACID across services. In practice 2PC is dead for three reasons:

1. It blocks. Participants hold locks during the whole protocol. A slow participant freezes everyone else. At microservice scale this is unworkable.
2. It assumes the coordinator never dies. If the coordinator crashes between phase 1 and phase 2, participants are stuck holding locks waiting for an answer that never comes. Recovery is fragile and operationally painful.
3. Most modern databases don’t support it cleanly across vendors. XA exists but is poorly supported, especially for NoSQL engines, message brokers, and SaaS APIs. You can’t enlist Stripe or DynamoDB in a 2PC.

The serious database literature is brutal on 2PC: Pat Helland’s Life Beyond Distributed Transactions is the canonical “just stop” paper. The industry has spent twenty years agreeing with him.

Embracing eventual consistency

Once 2PC is off the table, you have to accept that your system will pass through temporarily inconsistent states. Order created, payment not yet processed. Payment processed, inventory not yet decremented. The question is no longer “how do I prevent inconsistency” but “how do I bound it, detect it, and recover from it.”

The hardest engineering work in microservices is naming the consistency budget for each business flow. “Inventory must be decremented within 30 seconds of payment success” is a budget. “Don’t worry, it’ll be fine” is not.

The Saga Pattern

The original saga paper is from 1987 (Garcia-Molina and Salem). The idea predates microservices by decades; microservices just made it suddenly relevant to everyone.

Choreography vs Orchestration

Sagas come in two flavors. Both are real; neither is universally better.

Rule of thumb: anything beyond three steps or with conditional branches gets an orchestrator. Choreography sounds elegant on a whiteboard and turns into an unreadable rats-nest of subscribers in production. The Confluent and Uber engineering blogs have both written extensively about migrating off pure choreography toward orchestration as flows grew.

Saga orchestration in Temporal-style pseudocode

# Temporal-style workflow. Workflow code is deterministic and durable.
# Activities are the calls to actual services; they retry automatically.
from temporalio import workflow, activity
from dataclasses import dataclass

@dataclass
class OrderInput:
    customer_id: str
    items: list
    payment_method_id: str

@workflow.defn
class PlaceOrderSaga:
    @workflow.run
    async def run(self, input: OrderInput) -> str:
        compensations = []          # LIFO stack of cleanups

        try:
            # 1. Create order in PENDING state
            order_id = await workflow.execute_activity(
                create_order, input, start_to_close_timeout="5s",
            )
            compensations.append((cancel_order, order_id))

            # 2. Reserve inventory
            reservation_id = await workflow.execute_activity(
                reserve_stock, order_id, input.items, start_to_close_timeout="5s",
            )
            compensations.append((release_stock, reservation_id))

            # 3. Charge the card (idempotency key = order_id)
            charge_id = await workflow.execute_activity(
                charge_card, order_id, input.payment_method_id,
                start_to_close_timeout="10s",
            )
            compensations.append((refund_card, charge_id))

            # 4. Schedule shipment
            await workflow.execute_activity(
                schedule_shipment, order_id, start_to_close_timeout="5s",
            )

            # 5. Mark order CONFIRMED
            await workflow.execute_activity(
                confirm_order, order_id, start_to_close_timeout="5s",
            )
            return order_id

        except Exception as e:
            # Run compensations in reverse order. Each must be idempotent.
            for compensate, arg in reversed(compensations):
                try:
                    await workflow.execute_activity(
                        compensate, arg, start_to_close_timeout="30s",
                    )
                except Exception as comp_err:
                    workflow.logger.error(f"compensation failed: {comp_err}")
                    # Don't stop — keep trying the rest. Surface to ops if any fail.
            raise e

The Outbox Pattern

The dual-write problem is the most common bug in event-driven microservices. It is also one of the easiest to write and one of the hardest to detect — the missing event just looks like “the downstream system was a little behind today.” The outbox pattern eliminates the entire class of bug.

The outbox table

-- Postgres outbox schema
CREATE TABLE outbox (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_id  UUID        NOT NULL,
    aggregate_type TEXT       NOT NULL,        -- "Order", "Payment", etc.
    event_type    TEXT        NOT NULL,        -- "OrderCreated", "OrderShipped"
    payload       JSONB       NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    published_at  TIMESTAMPTZ                            -- NULL until shipped to Kafka
);

CREATE INDEX outbox_unpublished_idx
    ON outbox (created_at)
    WHERE published_at IS NULL;

Inside the service, the business write and the event are in one transaction:

async def create_order(cmd: CreateOrderCommand) -> str:
    async with db.transaction():
        order_id = await db.execute(
            "INSERT INTO orders (id, customer_id, total) "
            "VALUES ($1, $2, $3) RETURNING id",
            uuid.uuid4(), cmd.customer_id, cmd.total,
        )
        await db.execute(
            "INSERT INTO outbox (aggregate_id, aggregate_type, event_type, payload) "
            "VALUES ($1, $2, $3, $4)",
            order_id, "Order", "OrderCreated",
            json.dumps({"order_id": str(order_id), "customer_id": cmd.customer_id, "total": cmd.total}),
        )
        return order_id
    # Both rows commit together or neither does. No event is ever orphaned.

Two ways to ship the outbox to Kafka

Option A: a polling worker. A background process polls the outbox for unpublished rows, publishes them to Kafka, then marks them published_at = now(). Simple, ugly, works.

async def outbox_publisher():
    while True:
        async with db.transaction():
            rows = await db.fetch(
                "SELECT id, aggregate_type, event_type, payload "
                "FROM outbox WHERE published_at IS NULL "
                "ORDER BY created_at LIMIT 100 FOR UPDATE SKIP LOCKED"
            )
            for r in rows:
                await kafka.send(
                    topic=f"{r['aggregate_type'].lower()}-events",
                    key=str(r["aggregate_id"]),       # preserves per-aggregate ordering
                    value=r["payload"],
                    headers={"event_type": r["event_type"]},
                )
                await db.execute(
                    "UPDATE outbox SET published_at = now() WHERE id = $1", r["id"],
                )
        await asyncio.sleep(0.5)

Option B: change-data-capture (CDC). Tools like Debezium tail the database’s write-ahead log (WAL on Postgres, binlog on MySQL) and emit a Kafka message for every row change. No polling, sub-second latency, scales to massive throughput. This is what LinkedIn, Wix, and Shopify run in production.

// Debezium connector for the outbox table
{
  "name": "orders-outbox-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "orders-db",
    "database.port": "5432",
    "database.user": "debezium",
    "database.dbname": "orders",
    "plugin.name": "pgoutput",
    "slot.name": "orders_outbox_slot",
    "publication.name": "orders_outbox_pub",
    "table.include.list": "public.outbox",
    "tombstones.on.delete": "false",

    // Use the EventRouter SMT to turn one outbox row into a properly-routed event
    "transforms": "outbox",
    "transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter",
    "transforms.outbox.route.by.field": "aggregate_type",
    "transforms.outbox.route.topic.replacement": "${routedByValue}-events",
    "transforms.outbox.table.field.event.id": "id",
    "transforms.outbox.table.field.event.key": "aggregate_id",
    "transforms.outbox.table.field.event.payload": "payload"
  }
}

CQRS (Command Query Responsibility Segregation)

CQRS is one of the most over-applied patterns in this entire space. It is genuinely powerful, and it is also genuinely overkill for 80% of the services that adopt it. The right test is asymmetry: does this service have very different read and write workloads?

A pragmatic CQRS setup

Most production CQRS does not look like the textbook. It looks like: writes go to Postgres, an outbox publishes events, and a worker projects those events into a denormalized read store (Elasticsearch for search, Redis for hot reads, materialized views in Postgres itself for reporting).

# Read-model projector. Subscribes to order events and maintains a denormalized view.
async def project_order_events():
    async for msg in kafka_consumer.subscribe("order-events"):
        evt = msg.value
        event_id = msg.headers["event_id"]

        # Idempotency: skip if we've already processed this event_id
        if await read_db.fetchval(
            "SELECT 1 FROM processed_events WHERE event_id = $1", event_id,
        ):
            await msg.ack()
            continue

        async with read_db.transaction():
            if evt["event_type"] == "OrderCreated":
                await read_db.execute(
                    "INSERT INTO order_summary "
                    "(order_id, customer_id, customer_name, total, status, created_at) "
                    "VALUES ($1, $2, $3, $4, 'PENDING', $5)",
                    evt["order_id"],
                    evt["customer_id"],
                    await lookup_customer_name(evt["customer_id"]),  # denormalize on write
                    evt["total"],
                    evt["created_at"],
                )
            elif evt["event_type"] == "OrderShipped":
                await read_db.execute(
                    "UPDATE order_summary SET status = 'SHIPPED', shipped_at = $2 "
                    "WHERE order_id = $1",
                    evt["order_id"], evt["shipped_at"],
                )
            # ... handle other event types

            await read_db.execute(
                "INSERT INTO processed_events (event_id, processed_at) VALUES ($1, now())",
                event_id,
            )
        await msg.ack()

The read model is now a single, fast, denormalized table optimized for the queries the API actually serves. No joins, no aggregations at read time, sub-millisecond response.

Event Sourcing

Event sourcing is the CQRS-on-steroids version of data management. It is genuinely beautiful when it fits. It is also operationally heavy, requires schema discipline, and is wrong for most services. The famous warning from Greg Young (who coined the term): “don’t do event sourcing unless you have a really good reason.”

How it works

1. Commands express intent: PlaceOrder, CancelOrder, ApplyDiscount.
2. Events are the immutable result: OrderPlaced, OrderCancelled, DiscountApplied.
3. Events are appended to an event store (EventStoreDB, Kafka with infinite retention, a custom Postgres table).
4. Current state is rebuilt by folding events: state = events.reduce(apply_event, initial_state).
5. Projections derive read models from the same event stream.
6. Snapshots save intermediate state so you don’t replay 10 million events on every read.

Data Replication and Read Models

This is the workhorse pattern of mature microservices. Most production systems are mostly this: one service publishes events, several other services maintain their own denormalized projections of those events. Each downstream owns the shape of its read model. No one calls anyone else on the synchronous path for read data.

The eventual consistency budget

For every replicated read model, you need to know — explicitly — how stale is acceptable. This is the consistency budget. It belongs in your service’s SLO, your monitoring, and your runbook.

• P50 lag: half the events are projected within X ms. Usually 50–500 ms.
• P99 lag: 99% within Y seconds. Usually 1–10 s.
• Max acceptable lag: beyond this, page someone. The projector is broken.
• Lag at restart: after a deploy, how long to catch up? Bound this with parallel consumers or partitioned topics.

Without these numbers, “eventual consistency” is a hand-wave. With them, it’s an SLO you can measure, alert on, and improve.

Dealing with stale reads in the UI

The classic gotcha: user submits a form, gets redirected to the “your changes” page, and sees stale data because the read model hasn’t caught up yet. Three working tactics:

1. Read-your-writes via session affinity. Route the user’s next read to the write model for a few seconds after a write.
2. Optimistic update on the client. Show what the user just submitted, then reconcile with the server’s view when it arrives.
3. Wait-for-projection. The write API returns a version token; the read API blocks (briefly) until the projector has caught up to that version. LinkedIn calls this pattern read-your-writes consistency in their Kafka-based architecture.

Real-World Examples

Amazon: sagas and cell-based isolation

Amazon famously sits on top of thousands of services, each owning its data. Their order pipeline is a textbook saga: place order, reserve inventory, charge card, allocate shipping — with explicit compensations for each step. Internally Amazon also uses cell-based architecture where each service is split into independent cells with their own data; a bug in one cell affects ~1/N of customers instead of 100%. The data layer is what makes that isolation real.

eBay: the eventual consistency story

eBay was one of the earliest large-scale microservices adopters, and their engineering blog from the late 2000s is one of the canonical “here’s why we gave up on distributed transactions” documents. They embraced eventual consistency across their auction, inventory, and payment services and built compensation flows for every cross-service operation. Their core lesson: the cost of strong consistency at their scale was unsustainable; eventual consistency with explicit reconciliation was the only viable path.

LinkedIn: Kafka as the data backbone

LinkedIn invented Kafka largely to solve the data replication problem at scale. The pattern they pioneered: every service publishes its changes as events; downstream services maintain their own read-optimized projections. Confluent later commercialized this as the canonical event-streaming architecture for microservices. LinkedIn also originated much of the “read-your-writes” tooling for projections, because their feed and profile services had to handle “I just edited my profile, where are my changes” queries at billions per day.

Confluent and the data-mesh framing

Confluent has been pushing the framing of data products — each service exposes its data as a versioned, schema-stable, well-documented stream that downstream consumers can subscribe to. This is data mesh applied to microservices: the service team owns not just their code and database, but also the public event stream they publish. The contract is the events, not the database schema.

Uber and Netflix: polyglot persistence at scale

Uber operates on Postgres, MySQL, Cassandra, Redis, DynamoDB, and several internal systems — chosen per workload. Netflix runs DynamoDB, Cassandra, EVCache (their Memcached fork), and Elasticsearch across thousands of services. Both companies will tell you the polyglot story is true but understated — the operational cost of running so many engines is enormous, and they get away with it only because they have entire dedicated teams per database technology. For most companies, the right number of database engines is two or three.

Best Practices