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.
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Aggressive `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';
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Primary 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');
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Capping 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
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Keyset 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])
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Zero 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 withDROP/TRUNCATErotation 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.
Transactional Outbox With Cdc — Normal → Load / Fault → Recovery
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.
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.
Putting Scaling the Transactional Outbox with CDC Together
- Define
outbox_eventspartitioned byRANGE (created_at)with nostatuscolumn needed for polling. - Create
PUBLICATION outbox_pub FOR TABLE outbox_events WITH (publish = 'insert'). - Insert ledger row and outbox row in the same Spring/JDBC
@Transactionalboundary.
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.
Make the trade-off yourself.
Practice Check
Test your understanding
Why store events in an outbox table?
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.
