The Dual-Write Trap in Distributed Microservices
Picture a typical checkout flow: your API updates an order in PostgreSQL and emits a message to Kafka or RabbitMQ. It sounds straightforward. Downstream shipping and invoicing services rely on that event to fulfill the order.
Most engineers initially write something like this:
// Antipattern: The Unsafe Dual-Write
_, err := db.ExecContext(ctx, "UPDATE orders SET status = 'PAID' WHERE id = $1", orderID)
if err != nil {
return err
}
// What happens if the network drops or the pod crashes right here?
err = messageBroker.Publish(ctx, "order.paid", eventPayload)
if err != nil {
// The order is marked PAID in the database, but downstream services are blind to it.
return err
}
Welcome to the dual-write trap. No distributed transaction links your relational database to your message broker. If your application crashes between the database commit and the broker dispatch, the event vanishes forever. The customer pays, but the warehouse never packs the box.
Swapping the execution order fares no better. Publishing the event first and committing second risks ghost events: if your database transaction rolls back, downstream services fulfill an unpaid order.
Two-Phase Commit (2PC) can theoretically coordinate distributed transactions. In practice, 2PC introduces crippling latency spikes of 50–100ms, creates single points of failure, and lacks native support in modern brokers like Kafka. We need reliable event dispatch that maintains high throughput.
How the Transactional Outbox Pattern Works
The Transactional Outbox Pattern bypasses distributed transactions entirely. Instead, it relies on your local database’s battle-tested ACID guarantees. Every domain mutation and its corresponding outbound event share a single, atomic database transaction.
The Three-Step Workflow
- Single Atomic Write: When modifying the
orderstable, the application simultaneously writes a record to anoutbox_eventstable. If the database transaction commits, both records persist to disk. If it fails, both roll back cleanly. Zero ghost events. - Asynchronous Message Relay: An independent background worker polls pending records from
outbox_events, publishes them to the broker, and flags them as processed. - At-Least-Once Delivery: A worker might publish an event to Kafka and crash before updating PostgreSQL. Downstream consumers will occasionally receive duplicates, so design them to be idempotent.
Change Data Capture (CDC) engines like Debezium shine for massive distributed architectures. However, for systems handling between 500 and 5,000 transactions per second, an in-process Go worker using PostgreSQL’s FOR UPDATE SKIP LOCKED delivers rock-solid reliability with virtually zero operational overhead.
Step-by-Step Implementation in Go
1. Designing the PostgreSQL Schema
Start with schemas for your business domain and the outbox event log:
CREATE TABLE orders (
id UUID PRIMARY KEY,
customer_id UUID NOT NULL,
amount_cents BIGINT NOT NULL,
status VARCHAR(32) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE outbox_events (
id UUID PRIMARY KEY,
aggregate_type VARCHAR(64) NOT NULL,
aggregate_id UUID NOT NULL,
event_type VARCHAR(64) NOT NULL,
payload JSONB NOT NULL,
status VARCHAR(16) NOT NULL DEFAULT 'PENDING',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
processed_at TIMESTAMPTZ
);
-- Partial index keeps queries sub-millisecond even with millions of historical rows
CREATE INDEX idx_outbox_pending ON outbox_events (created_at) WHERE status = 'PENDING';
Notice the partial index on status = 'PENDING'. This keeps index lookups under 2ms, even after the table accumulates millions of historical records.
2. Atomic Writes in Go
Wrap the domain mutation and outbox record insertion into a single database transaction:
package main
import (
"context"
"database/sql"
"encoding/json"
"time"
"github.com/google/uuid"
)
type OrderPaidEvent struct {
OrderID uuid.UUID `json:"order_id"`
AmountCents int64 `json:"amount_cents"`
Timestamp time.Time `json:"timestamp"`
}
func CompleteOrder(ctx context.Context, db *sql.DB, orderID uuid.UUID, amountCents int64) error {
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
if err != nil {
return err
}
defer tx.Rollback()
// 1. Mutate domain state
updateQuery := `UPDATE orders SET status = 'PAID' WHERE id = $1`
if _, err := tx.ExecContext(ctx, updateQuery, orderID); err != nil {
return err
}
// 2. Serialize event payload
payload, err := json.Marshal(OrderPaidEvent{
OrderID: orderID,
AmountCents: amountCents,
Timestamp: time.Now().UTC(),
})
if err != nil {
return err
}
// 3. Stage the event inside the same transaction
outboxQuery := `
INSERT INTO outbox_events (id, aggregate_type, aggregate_id, event_type, payload, status)
VALUES ($1, $2, $3, $4, $5, 'PENDING')
`
eventID := uuid.New()
if _, err := tx.ExecContext(ctx, outboxQuery, eventID, "Order", orderID, "OrderPaid", payload); err != nil {
return err
}
return tx.Commit()
}
3. Multi-Replica Safe Background Worker
Running multiple worker instances prevents bottlenecks, but naive polling causes lock contention. PostgreSQL solves this with FOR UPDATE SKIP LOCKED. Each worker grabs an exclusive lock on an unassigned batch while other workers immediately skip to the next available rows.
package main
import (
"context"
"database/sql"
"log"
"time"
"github.com/google/uuid"
)
type OutboxRecord struct {
ID uuid.UUID
EventType string
Payload []byte
}
type MessagePublisher interface {
Publish(ctx context.Context, topic string, data []byte) error
}
func StartOutboxWorker(ctx context.Context, db *sql.DB, publisher MessagePublisher, interval time.Duration) {
ticker := time.NewTicker(interval)
defer ticker.Stop()
for {
select {
case <-ctx.Done():
return
case <-ticker.C:
if err := processBatch(ctx, db, publisher); err != nil {
log.Printf("outbox relay error: %v", err)
}
}
}
}
func processBatch(ctx context.Context, db *sql.DB, publisher MessagePublisher) error {
// Bound the relay batch execution to 5 seconds
batchCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
defer cancel()
tx, err := db.BeginTx(batchCtx, nil)
if err != nil {
return err
}
defer tx.Rollback()
query := `
SELECT id, event_type, payload
FROM outbox_events
WHERE status = 'PENDING'
ORDER BY created_at ASC
LIMIT 50
FOR UPDATE SKIP LOCKED
`
rows, err := tx.QueryContext(batchCtx, query)
if err != nil {
return err
}
defer rows.Close()
var batch []OutboxRecord
for rows.Next() {
var rec OutboxRecord
if err := rows.Scan(&rec.ID, &rec.EventType, &rec.Payload); err != nil {
return err
}
batch = append(batch, rec)
}
if err := rows.Err(); err != nil {
return err
}
if len(batch) == 0 {
return nil
}
for _, event := range batch {
if err := publisher.Publish(batchCtx, event.EventType, event.Payload); err != nil {
// Rollback retains PENDING status for a retry on the next tick
return err
}
updateQuery := `
UPDATE outbox_events
SET status = 'PROCESSED', processed_at = $1
WHERE id = $2
`
if _, err := tx.ExecContext(batchCtx, updateQuery, time.Now().UTC(), event.ID); err != nil {
return err
}
}
return tx.Commit()
}
Key Takeaways and Production Tips
The Transactional Outbox Pattern decouples domain writes from external broker uptime. It guarantees that an event lands if and only if the underlying database state changes.
Before rolling this out to production, keep two operational safeguards in mind:
- Keep publish timeouts short: Cap
publisher.Publishcalls at 200–500ms. Because publishing happens inside an active transaction, slow broker calls hold open database connections and table locks. - Prune processed records aggressively: A table that adds 100,000 events daily will bloat quickly. Set up a daily cron job to delete or partition records where
status = 'PROCESSED'andprocessed_at < NOW() - INTERVAL '7 days'.
