# Transactional Outbox and CDC for Distributed Systems

URL: https://mepritam.dev/references/transactional-outbox-cdc-postgresql-kafka/

Eliminate dual-write inconsistencies and race conditions across distributed microservices using PostgreSQL WAL logical decoding and Kafka.

---

In distributed architectures, business workflows rarely live within a single service boundary. When a customer places an order, an order service must persist state to an ACID-compliant database and broadcast an event to an Apache Kafka cluster. Downstream services - inventory reservation, payment capture, real-time analytics, and customer notifications - depend on that event to trigger their respective state machines.

Connecting an operational database to an event broker introduces the classic **dual-write dilemma**. Because distributed transactions across relational databases and message brokers lack a shared atomic commit coordinator, one of the two writes inevitably fails under real-world network partitions.

When architecting distributed commerce pipelines handling millions of daily mutations, I deploy the **Transactional Outbox Pattern** paired with **Log-Based Change Data Capture (CDC)**. This pattern eliminates dual-write race conditions while maintaining low latency.

---

## The dual-write failure modes

Naive implementations typically attempt to coordinate database writes and event publishing within standard application logic. Both execution sequences introduce data corruption:

![Dual Write Failure Modes vs Transactional Outbox](/images/references/transactional-outbox-cdc-postgresql-kafka-1.webp)

### Failure sequence 1: write to database first, publish event second

```text
1. BEGIN TRANSACTION
2. INSERT INTO orders (...)
3. COMMIT TRANSACTION
4. kafkaProducer.send("order-placed", event) // NETWORK TIMEOUT OR PROCESS CRASH
```

If the application crashes, network timeouts strike, or Kafka brokers throttle requests between steps 3 and 4, the database commit stands. The order exists in the primary database, but the `order-placed` event vanishes. Warehouse fulfillment never picks the product, billing never charges the card, and support teams face missing data.

### Failure sequence 2: publish event first, write to database second

```text
1. kafkaProducer.send("order-placed", event)
2. BEGIN TRANSACTION
3. INSERT INTO orders (...) // DATABASE DEADLOCK OR UNIQUE CONSTRAINT FAILS
4. ROLLBACK TRANSACTION
```

If the database transaction aborts due to a serialization deadlock, unique constraint violation, or sudden out-of-memory crash, the Kafka event has already dispatched. Downstream microservices process an event for an order that does not exist in the primary database, producing phantom billing cycles and corrupted inventory balances.

### Why Two-Phase Commit (2PC) fails at scale

Historically, distributed systems used Two-Phase Commit (2PC) protocols and XA transactions to achieve atomic commits across systems. In modern cloud environments, 2PC collapses under high-throughput workloads:

- **Blocking Coordinator:** If the transaction coordinator hangs during the prepare phase, participating nodes hold row and table locks indefinitely, cascading resource exhaustion across the cluster.
- **Latency and Throughput Ceiling:** Network round-trips for voting phases cap system throughput at fewer than 500 operations per second.
- **Broker Incompatibility:** High-scale event streaming platforms like Apache Kafka and Redpanda do not support XA protocols by design.

---

## The Transactional Outbox Pattern

The Transactional Outbox pattern resolves dual-write failures by shifting event publication into the local ACID transaction.

Instead of calling the message broker directly over the network, the application writes both the entity mutation and a corresponding outbox record into the same database using a single commit.

```sql
BEGIN TRANSACTION;

-- 1. Mutate primary domain state
INSERT INTO orders (id, customer_id, total_cents, status)
VALUES ('ord_01J6X...', 'cust_998', 14900, 'PENDING');

-- 2. Stage event in local outbox table
INSERT INTO outbox_events (
  id,
  aggregate_type,
  aggregate_id,
  event_type,
  payload,
  traceparent
) VALUES (
  'evt_01J6X...',
  'Order',
  'ord_01J6X...',
  'OrderPlaced',
  '{"customerId": "cust_998", "totalCents": 14900}'::jsonb,
  '00-4bf92f3577b34da6a3ce929d0e0e4736-00f067aa0ba902b7-01'
);

COMMIT;
```

Because relational engines like PostgreSQL guarantee atomicity, either both rows commit or both roll back. Network failures between your application and external message brokers cannot cause state drift.

---

## Change Data Capture via Write-Ahead Log streaming

Once events sit in the `outbox_events` table, an asynchronous process must relay them to Apache Kafka.

### Why table polling degrades under load

Early designs often deployed a background polling cron:

```sql
SELECT * FROM outbox_events
WHERE processed = false
ORDER BY created_at ASC
LIMIT 100 FOR UPDATE SKIP LOCKED;
```

At enterprise scale (10,000+ writes per second), polling creates severe database strain:

- **Index Thrashing:** Continuous read-update-delete cycles on hot index pages cause high table bloat.
- **Vacuum Starvation:** Heavy write locks prevent autovacuum from cleaning dead tuples efficiently.
- **High Latency:** Events sit in the outbox table until the next polling tick, adding hundreds of milliseconds of artificial latency.

### The log-based streaming architecture

Modern architectures extract outbox events using **Log-Based Change Data Capture (CDC)** via PostgreSQL Write-Ahead Log (WAL) logical decoding.

![Log-Based CDC Pipeline Architecture with Debezium and Kafka](/images/references/transactional-outbox-cdc-postgresql-kafka-2.webp)

PostgreSQL records every committed mutation to its append-only WAL before applying changes to data pages on disk. The CDC pipeline operates without polling:

1. **Logical Decoding Plugin:** PostgreSQL uses the `pgoutput` plugin to convert binary WAL changes into a logical stream of row modifications.
2. **Replication Slots:** The CDC engine (such as Debezium or a dedicated Go service) connects to a dedicated PostgreSQL replication slot. The database retains WAL segments until the connector confirms receipt.
3. **Kafka Partition Dispatch:** The connector routes outbox records to Kafka topics. Setting the message key to `aggregate_id` guarantees that all state transitions for a given order route to the exact same Kafka partition, preserving strict sequential ordering.
4. **LSN Acknowledgment:** Once Kafka confirms message write via producer acknowledgments (`acks=all`), the connector updates its Log Sequence Number (LSN) position on the PostgreSQL replication slot.

Because CDC reads the append-only WAL directly from memory buffers or sequential disk blocks, it incurs negligible CPU overhead on the primary database and delivers events to Kafka in under 10 milliseconds.

---

## Production implementation in TypeScript

The following TypeScript module provides a resilient transactional outbox writer alongside an idempotent consumer designed for high-throughput event processing.

```typescript
import { type PoolClient, type Pool } from "pg";
import { z } from "zod";

// Schema for an outbox event
export const OutboxRecordSchema = z.object({
  id: z.string().uuid(),
  aggregateType: z.string(),
  aggregateId: z.string(),
  eventType: z.string(),
  payload: z.record(z.unknown()),
  traceparent: z.string().optional(),
});

export type OutboxRecord = z.infer<typeof OutboxRecordSchema>;

export interface OrderInput {
  orderId: string;
  customerId: string;
  totalCents: number;
}

/**
 * Persists an order and stages its domain event within a single ACID transaction.
 */
export async function createOrderTransactional(
  pool: Pool,
  order: OrderInput,
  traceparent?: string,
): Promise<void> {
  const client: PoolClient = await pool.connect();

  try {
    await client.query("BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;");

    // 1. Insert domain entity
    const insertOrderQuery = `
      INSERT INTO orders (id, customer_id, total_cents, status, created_at)
      VALUES ($1, $2, $3, 'PENDING', NOW());
    `;
    await client.query(insertOrderQuery, [
      order.orderId,
      order.customerId,
      order.totalCents,
    ]);

    // 2. Insert outbox record within the exact same database boundary
    const insertOutboxQuery = `
      INSERT INTO outbox_events (
        id, aggregate_type, aggregate_id, event_type, payload, traceparent, created_at
      ) VALUES (
        gen_random_uuid(), 'Order', $1, 'OrderPlaced', $2, $3, NOW()
      );
    `;
    await client.query(insertOutboxQuery, [
      order.orderId,
      JSON.stringify({
        customerId: order.customerId,
        totalCents: order.totalCents,
      }),
      traceparent || null,
    ]);

    // 3. Commit both writes atomically
    await client.query("COMMIT;");
  } catch (error) {
    await client.query("ROLLBACK;");
    throw error;
  } finally {
    client.release();
  }
}

/**
 * Idempotent consumer pattern preventing duplicate execution on Kafka replays.
 */
export class IdempotentEventConsumer {
  private pool: Pool;

  constructor(pool: Pool) {
    this.pool = pool;
  }

  async processEvent(
    eventId: string,
    handler: (client: PoolClient) => Promise<void>,
  ): Promise<boolean> {
    const client = await this.pool.connect();

    try {
      await client.query("BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;");

      // Attempt to register processed event ID
      const dedupeQuery = `
        INSERT INTO processed_events (event_id, processed_at)
        VALUES ($1, NOW())
        ON CONFLICT (event_id) DO NOTHING
        RETURNING event_id;
      `;
      const dedupeResult = await client.query(dedupeQuery, [eventId]);

      // If zero rows returned, this event was already executed
      if (dedupeResult.rowCount === 0) {
        await client.query("ROLLBACK;");
        return false; // Skip duplicate message safely
      }

      // Execute business state mutation
      await handler(client);

      await client.query("COMMIT;");
      return true;
    } catch (error) {
      await client.query("ROLLBACK;");
      throw error;
    } finally {
      client.release();
    }
  }
}
```

---

## Engineering tradeoffs and operational safeguards

While the Transactional Outbox pattern eliminates dual-write inconsistencies, operating CDC pipelines at scale introduces systems engineering challenges. Across 12M+ daily catalog mutations, shifting to WAL-based logical replication cut event delivery lag from 420ms to under 8ms while saving 18% in database CPU overhead. Here is how we hardened the architecture.

### Managing replication slot disk exhaustion

PostgreSQL retains Write-Ahead Logs on disk until all active replication slots confirm receipt. If a downstream Kafka cluster becomes unavailable or the CDC consumer crashes, PostgreSQL preserves WAL files on disk indefinitely.

On a database processing 15,000 writes per second, WAL accumulation can exhaust disk capacity within hours, placing the entire database in read-only panic mode.

**Production Safeguard:** Configure `max_slot_wal_keep_size` in `postgresql.conf` (e.g., `40GB`). If the replication connector falls behind this safety ceiling, PostgreSQL unlinks the oldest WAL segments and invalidates the replication slot, preserving database availability at the expense of requiring an outbox re-snapshot.

### At-least-once delivery and consumer idempotency

Log-based CDC provides **at-least-once delivery**. If a network glitch interrupts Kafka acknowledgments after message dispatch, the connector replays events from the previous Log Sequence Number upon reconnecting.

Downstream consumers must treat all event deliveries as potentially duplicate. Maintain a `processed_events` table storing event UUIDs with unique constraints, or use database-level upserts (`INSERT ... ON CONFLICT DO NOTHING`) to guarantee idempotent execution.

### Outbox table pruning and table bloat

If you stage millions of events daily, the `outbox_events` table grows rapidly. Standard row-by-row `DELETE` operations create heavy table bloat and lock contention.

**Production Safeguard:** Use the **Debezium Outbox Event Router** transform, or partition the `outbox_events` table daily by `created_at` (`PARTITION BY RANGE (created_at)`). You can drop old daily partition tables instantly with `DROP TABLE outbox_events_2026_09_01`, executing a fast metadata wipe that bypasses tuple vacuuming overhead.