Skip to main content
← PROBLEMS / ARCHITECTURE
An end-to-end domain learning journey

Scaling the Transactional
Outbox with CDC.

Your payment ledger writes 15,000 transactions/sec into PostgreSQL 16, inserting one row into outbox_events per transaction. Eight relay workers poll outbox_events every 50ms using SELECT ... WHERE status = 'PENDING' ORDER BY id LIMIT 500 FOR UPDATE SKIP LOCKED and then UPDATE status = 'SENT' (or DELETE). Within 4 hours of peak load, outbox_events bloats from 200 MB to 48 GB despite holding only 50,000 pending rows, autovacuum lags behind due to long-running analytics queries, polling p99 jumps from 8ms to 1,400ms, and Kafka lag exceeds 400,000 events. Redesign the outbox ingestion and relay pipeline to sustain 20,000+ events/sec with < 50ms end-to-end publish latency and zero table bloat.
Start the journey5 stages
Loading diagram…
Scaling the Transactional Outbox with CDC architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

Your payment ledger writes 15,000 transactions/sec into PostgreSQL 16, inserting one row into `outbox_events` per transaction. Eight relay workers poll `outbox_events` every 50ms using `SELECT ... WHERE status = 'PENDING' ORDER BY id LIMIT 500 FOR UPDATE SKIP LOCKED` and then `UPDATE status = 'SENT'` (or `DELETE`). Within 4 hours of peak load, `outbox_events` bloats from 200 MB to 48 GB despite holding only 50,000 pending rows, autovacuum lags behind due to long-running analytics queries, polling p99 jumps from 8ms to 1,400ms, and Kafka lag exceeds 400,000 events. Redesign the outbox ingestion and relay pipeline to sustain 20,000+ events/sec with < 50ms end-to-end publish latency and zero table bloat.

From patterns to decisions.

Your payment ledger writes 15,000 transactions/sec into PostgreSQL 16, inserting one row into `outbox_events` per transaction. Eight relay workers poll `outbox_events` every 50ms using `SELECT ... WHERE status = 'PENDING' ORDER BY id LIMIT 500 FOR UPDATE SKIP LOCKED` and then `UPDATE status = 'SENT'` (or `DELETE`). Within 4 hours of peak load, `outbox_events` bloats from 200 MB to 48 GB despite holding only 50,000 pending rows, autovacuum lags behind due to long-running analytics queries, polling p99 jumps from 8ms to 1,400ms, and Kafka lag exceeds 400,000 events. Redesign the outbox ingestion and relay pipeline to sustain 20,000+ events/sec with < 50ms end-to-end publish latency and zero table bloat.

01
ARCHITECTURE · STAGE 01L4 · FUNDAMENTALS

PostgreSQL's MVCC creates dead tuples.

PostgreSQL's MVCC creates dead tuples on every UPDATE/DELETE; when autovacuum lags or is blocked by long-running snapshots, index scans must traverse millions of dead index entries.

ChoiceWhat it buysWhat it costs
Trade-off 1Aggressive `autovacuum_vacuum_scale_factor = 0.01` helps at medium scale but still fails when long-running `xmin` snapshots pin dead tuples.

Compute dead tuple generation

15,000 updates/sec = 54M dead tuples/hour.

Identify the `xmin` horizon blocker

any 15-minute analytics query prevents VACUUM from freeing tuples newer than that query's start.

Observe B-tree index bloat

status = 'PENDING' index retains dead pointers, turning an 8ms lookup into a 1,400ms scan.

Alternative 1

Move analytics to a separate logical replica with hot_standby_feedback = off.

Failure Mode 1

Transaction ID (XID) wraparound emergency vacuum freezing writes.

-- Diagnostic query to expose dead tuple bloat and blocked autovacuum
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / GREATEST(n_live_tup, 1), 2) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'outbox_events';
02
ARCHITECTURE · STAGE 02L5 · APPLIED DESIGN

Use an append-only range-partitioned table.

Use an append-only range-partitioned table with a filtered pgoutput publication so Debezium streams inserts sequentially from WAL with zero heap polling.

ChoiceWhat it buysWhat it costs
Trade-off 1Primary key must include the partition key (`created_at`), requiring UUIDv7 or `(event_id, created_at)` composite PK.

Step 1

Define outbox_events partitioned by RANGE (created_at) with no status column needed for polling.

Step 2

Create PUBLICATION outbox_pub FOR TABLE outbox_events WITH (publish = 'insert').

Step 3

Insert ledger row and outbox row in the same Spring/JDBC @Transactional boundary.

Alternative 1

Partitioned SQL polling with TRUNCATE on completed partitions if Kafka Connect cannot be deployed.

Failure Mode 1

Inserting a created_at outside pre-created partition bounds fails the transaction unless pg_partman pre-creates 48h ahead.

CREATE TABLE outbox_events (
    event_id      UUID NOT NULL,
    aggregate_id  VARCHAR(64) NOT NULL,
    event_type    VARCHAR(64) NOT NULL,
    payload       JSONB NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (event_id, created_at)
) PARTITION BY RANGE (created_at);

-- Filtered publication: decode ONLY inserts on outbox_events
CREATE PUBLICATION outbox_pub
FOR TABLE outbox_events
WITH (publish = 'insert');
03
ARCHITECTURE · STAGE 03L6 · SCALE & TRADE-OFFS

Configure Debezium's EventRouter SMT with.

Configure Debezium's EventRouter SMT with route.by.field and table.field.event.key = aggregate_id, and cap PostgreSQL slot retention with max_slot_wal_keep_size.

ChoiceWhat it buysWhat it costs
Trade-off 1Capping WAL at 50GB protects primary DB availability during multi-hour CDC outages, requiring a fallback replay job.

Step 1

Set plugin.name = pgoutput and publication.name = outbox_pub.

Step 2

Use EventRouter SMT so Kafka key = aggregate_id (guaranteeing partition ordering).

Step 3

Set max_slot_wal_keep_size = '50GB' in postgresql.conf.

Alternative 1

Unbounded WAL retention with automated PagerDuty escalation (risks full DB outage).

Using `publication.autocreate.mode

all_tables` accidentally decodes the entire database WAL.

# Debezium PostgreSQL Connector (Kafka Connect JSON/YAML)
connector.class: io.debezium.connector.postgresql.PostgresConnector
plugin.name: pgoutput
slot.name: outbox_slot
publication.name: outbox_pub
publication.autocreate.mode: disabled
transforms: outbox
transforms.outbox.type: io.debezium.transforms.outbox.EventRouter
transforms.outbox.table.field.event.id: event_id
transforms.outbox.table.field.event.key: aggregate_id
transforms.outbox.table.field.event.type: event_type
transforms.outbox.table.field.event.payload: payload
transforms.outbox.route.by.field: event_type
04
ARCHITECTURE · STAGE 04L6 · SCALE & TRADE-OFFS

Pause partition drops, run a.

Pause partition drops, run a bounded batch replay from the un-dropped hourly partitions starting at the last committed Kafka offset timestamp, then recreate the slot.

ChoiceWhat it buysWhat it costs
Trade-off 1Keyset pagination `(created_at, event_id) > (...)` avoids slow `OFFSET` scans during recovery.

Step 1

Halt the hourly DROP TABLE job so no un-published partitions are deleted.

Step 2

Query the latest created_at successfully published to Kafka.

Step 3

Run a Python/Java backfill worker scanning outbox_events WHERE created_at >= :last_ts in 5,000-row keyset batches.

Step 4

Recreate the outbox_slot and resume Debezium; idempotent consumers deduplicate any overlap.

Alternative 1

Increase max_slot_wal_keep_size if disk headroom allows > 4 hours of WAL.

Failure Mode 1

Dropping a partition before backfill finishes causes permanent event loss.

def backfill_missed_outbox(conn, kafka_producer, since_ts, batch_size=5000):
    last_ts, last_id = since_ts, '00000000-0000-0000-0000-000000000000'
    while True:
        rows = conn.execute("""
            SELECT event_id, aggregate_id, event_type, payload, created_at
            FROM outbox_events
            WHERE (created_at, event_id) > (%s, %s)
            ORDER BY created_at ASC, event_id ASC
            LIMIT %s
        """, (last_ts, last_id, batch_size)).fetchall()
        if not rows:
            break
        for eid, agg_id, etype, payload, ts in rows:
            kafka_producer.send(etype, key=agg_id.encode(), value=payload, headers=[('event_id', str(eid).encode())])
        kafka_producer.flush()
        last_ts, last_id = rows[-1][4], str(rows[-1][0])
05
ARCHITECTURE · STAGE 05L7 · STAFF / PRINCIPAL

Use a two-table or ring-buffer.

Use a two-table or ring-buffer partition swap (outbox_active / outbox_draining) where workers drain the frozen partition and reclaim 100% of space with TRUNCATE instead of DELETE.

ChoiceWhat it buysWhat it costs
Trade-off 1Zero external CDC infrastructure, but adds ~100-500ms batching delay and requires careful lock timeout (`lock_timeout = '50ms'`) during `ALTER TABLE`.

Step 1

Rotate outbox_active every 60 seconds using a view or ALTER TABLE ... RENAME.

Step 2

Workers read sequentially from the frozen outbox_draining_N table without FOR UPDATE SKIP LOCKED.

Step 3

Once drained, execute TRUNCATE TABLE outbox_draining_N in O(1) time.

Alternative 1

PostgreSQL LISTEN/NOTIFY for low-volume (< 1,000 TPS) triggers.

Failure Mode 1

ALTER TABLE acquires ACCESS EXCLUSIVE lock; without lock_timeout, a slow insert queues behind it and stalls writes.

-- Atomic table rotation avoids row-level DELETE and VACUUM
BEGIN;
ALTER TABLE outbox_active RENAME TO outbox_drain_1042;
CREATE TABLE outbox_active (LIKE outbox_drain_1042 INCLUDING ALL);
COMMIT;
-- After relay drains outbox_drain_1042:
TRUNCATE TABLE outbox_drain_1042;

Theory & Tips and Tricks

Practical heuristics & interview traps

  • 01Interview anchor: Replace SQL polling on high-throughput outbox tables with PostgreSQL WAL Logical Replication (Debezium pgoutput), or pair time-partitioned outbox tables with DROP/TRUNCATE rotation to eliminate MVCC bloat.
  • 02Trade-off tip: Capping WAL at 50GB protects primary DB availability during multi-hour CDC outages, requiring a fallback replay job.
  • 03Watch out for: Dropping a partition before backfill finishes causes permanent event loss.
Behavioral Progression · Transactional Outbox With Cdc

Transactional Outbox With Cdc — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Replace SQL polling on high-throughput outbox tables with PostgreSQL WAL Logical Replication (Debezium pgoutput), or pair time-partitioned outbox tables with DROP/TRUNCATE rotation to eliminate MVCC bloat.

Synthesis / One connected design

Putting Scaling the Transactional Outbox with CDC Together

Replace SQL polling on high-throughput outbox tables with PostgreSQL WAL Logical Replication (Debezium `pgoutput`), or pair time-partitioned outbox tables with `DROP`/`TRUNCATE` rotation to eliminate MVCC bloat.

A candidate architecture, not a universal answer

Putting Scaling the Transactional Outbox with CDC Together

Step 1
Step 2
Step 3
  1. Define outbox_events partitioned by RANGE (created_at) with no status column needed for polling.
  2. Create PUBLICATION outbox_pub FOR TABLE outbox_events WITH (publish = 'insert').
  3. Insert ledger row and outbox row in the same Spring/JDBC @Transactional boundary.
Pause partition drops, run a bounded batch replay from the un-dropped hourly partitions starting at the last committed Kafka offset timestamp, then recreate the slot.
Interview Readiness Check

Pause partition drops, run a bounded batch replay from the un-dropped hourly partitions starting at the last committed Kafka offset timestamp, then recreate the slot.

Walk through bottlenecks, back-of-the-envelope math, and failure recovery.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

Why store events in an outbox table?

Key takeaway

Scaling the Transactional Outbox with CDC

Replace SQL polling on high-throughput outbox tables with PostgreSQL WAL Logical Replication (Debezium pgoutput), or pair time-partitioned outbox tables with DROP/TRUNCATE rotation to eliminate MVCC bloat.