Part 189: The Transactional Outbox Pattern with SQL

Guaranteeing 100% reliable event publishing across microservices without distributed transactions.

1. The Dual-Write Problem

In microservices, you often need to save state to a database AND publish a message to Kafka / RabbitMQ: - If the DB commit succeeds, but network fails before Kafka publish: Event lost! - If Kafka publish succeeds, but DB commit rolls back: Ghost event sent!

2. The Solution: The Outbox Table

Save business data AND the event into the database inside the exact same ACID transaction:

CREATE TABLE outbox_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type VARCHAR(50) NOT NULL,
    aggregate_id VARCHAR(50) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    processed_at TIMESTAMPTZ NULL
);

3. Atomic Write

BEGIN;

-- 1. Save business record
INSERT INTO orders (id, customer_id, total, status) 
VALUES ('ord_123', 42, 99.95, 'placed');

-- 2. Save event to outbox table in SAME transaction!
INSERT INTO outbox_messages (aggregate_type, aggregate_id, event_type, payload)
VALUES (
    'Order', 
    'ord_123', 
    'OrderPlaced', 
    '{"order_id": "ord_123", "customer_id": 42, "total": 99.95}'::jsonb
);

COMMIT;

4. De-queuing with FOR UPDATE SKIP LOCKED

A background worker polls the outbox without blocking other workers:

WITH next_messages AS (
    SELECT id 
    FROM outbox_messages 
    WHERE processed_at IS NULL
    ORDER BY created_at ASC
    LIMIT 100
    FOR UPDATE SKIP LOCKED -- Multiple workers can run simultaneously!
)
UPDATE outbox_messages
SET processed_at = NOW()
WHERE id IN (SELECT id FROM next_messages)
RETURNING *;

Key Takeaways

• The Outbox pattern guarantees at-least-once message delivery in microservice architectures.

In Part 190, we explore Distributed Transactions: 2PC vs Saga Orchestration in SQL!