A broker can accept hundreds of thousands of events and still be part of a system that loses changes, retries indefinitely, or returns incorrect information. The throughput of a pipeline is not equivalent to the throughput of the broker: it also depends on how it is published from the source database, what contract each event represents, how the partitions are distributed, the processor’s guarantees and the behavior of the sink.
This article covers the conceptual design of a PostgreSQL → outbox/CDC → Kafka or Redpanda → processor → query projectionpipeline, with a case of e-commerce orders. It is based on documentation for PostgreSQL 18, Debezium, Apache Kafka 4.4, Redpanda 26.2 and Apache Flink.Not a report of a deployed infrastructure: Examples are illustrative and experiments are proposed, but have not been executed or measured. The selection of a definitive sink depends on the workload; ClickHouse, PostgreSQL, and a search system are not interchangeable just because they receive events.
The real problem: a transaction in two systems
Let’s imagine an API that creates a request in PostgreSQL and then publishes a message OrderCreated.
BEGIN en PostgreSQL
INSERT order
COMMIT en PostgreSQL
publicar OrderCreated en el broker
The first commit may work and the release fail: the base contains a request that no consumer knows about. Reversing the order also does not solve the dilemma: the broker could receive an event whose write to the database failed. Two non-atomic operations performed on independent systems leave a window of inconsistency.
The problem is not solved by an idempotent producer or a transaction confined to the broker. The architectural decision is to record the business change and the intent to publish it in a single local transaction; then another mechanism delivers the events.
Transactional outbox: a source of truth and a publishing queue
In PostgreSQL, the application transaction can insert the order and a row into the outbox table before committing. The following outline is a minimal pedagogical example, not an audited business outline:
CREATE TABLE orders (
id uuid PRIMARY KEY,
account_id uuid NOT NULL,
status text NOT NULL,
version bigint NOT NULL DEFAULT 1
);
CREATE TABLE outbox_events (
id uuid PRIMARY KEY,
aggregate_type text NOT NULL,
aggregate_id uuid NOT NULL,
event_type text NOT NULL,
payload jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
BEGIN;
INSERT INTO orders(id, account_id, status)
VALUES ('11111111-1111-4111-8111-111111111111',
'22222222-2222-4222-8222-222222222222', 'created');
INSERT INTO outbox_events(id, aggregate_type, aggregate_id,
event_type, payload)
VALUES ('33333333-3333-4333-8333-333333333333',
'order', '11111111-1111-4111-8111-111111111111',
'OrderCreated',
'{"version":1,"status":"created"}'::jsonb);
COMMIT;
With the transaction committed, both rows exist or neither exist. What is not yet guaranteed is that the consumer will see the event only once. Delivery may be repeated; the outbox resolves the initial dual write, not all the downstream semantics.
The outbox also needs a policy for retention, cleaning and evolutionary scheme. If publishing is based on polling, multiple workers require coordination; if based on CDC, the connector requires a replication slot, offsets and monitoring. It is not advisable to confuse the logical content of the event with the technical queue that carries it.
CDC: reading changes from WAL does not delete replays
Change Data Capture through logical decoding allows events to be derived from the PostgreSQL WAL. Debezium can capture changes from an outbox and apply its Outbox Event Router to publish logs with an appropriate event envelope. The aggregateid field can be mapped to the Kafka key: this way changes to the same aggregate follow the chosen partitioning strategy.
The logical slot maintains a read position. PostgreSQL 18 documents that slots are persistent and crash-resistant, but the position may have been persisted only at the last checkpoint. After a failure, already processed changes can be reissued. The source of truth remains the committed transaction; the consumer must tolerate duplicates.
The other risk is operational: a slot that does not advance retains WAL and can exhaust storage. The architecture requires alarms on slot lag, retained WAL volume, connector status, and disk capacity, as well as a recovery procedure that supports data not yet processed.
Source: PostgreSQL 18: Logical Decoding Concepts and PostgreSQL 18: Replication Slots.
The event contract must survive a replay
An event should not be defined solely as “the JSON returned by the API today.” Requires sufficient identity, version, time, and semantics to be processed in a controlled manner:
{
"event_id": "33333333-3333-4333-8333-333333333333",
"event_type": "OrderCreated",
"event_version": 1,
"aggregate_id": "11111111-1111-4111-8111-111111111111",
"aggregate_version": 1,
"occurred_at": "2026-11-17T11:00:00Z",
"data": { "status": "created" }
}
All identifiers are fictitious. event_id identifies a logical publication for deduplication; aggregate_id identifies the entity whose order you can import; aggregate_version allows detecting discontinuities. occurred_at describes business time, not necessarily processing time.
A schema evolution must answer practical questions: can producers and consumers of different versions coexist? What fields can be added without breaking? How are old replays compatible? Schema Registry and versioned contracts help make these decisions explicit; having a scheme does not guarantee that all clients will respect it.
For domain order and problems increasing partitions, see Kafka: claves de partición, orden y distribución.
What the broker guarantees and what the sink has to guarantee
The most cited properties are often confused:
| Concept | What does it cover? | What is not automatically covered |
|---|---|---|
| Replication | Log availability/durability under quorum hypothesis | Independent backup, event correction |
acks=all | Confirmation under broker replication rules | Confirmation of writing to an external DB |
| idempotent producer | Duplication of retries within the scope of that session/protocol | The entire chain of side effects |
| Kafka transactions | Coordination of writing and offsets in compatible Kafka workflows | Arbitrary Payment/API/DB as Global Transaction |
| Consumer offset | Recorded reading progress | Atomic commit with an external sink |
| Processor with checkpoint | Internal state recovery based on engine semantics | That a side effect without cooperation physically happens only once |
Kafka describes exactly-once for cases where offsets and results can be committed transactionally within the Kafka ecosystem. For external sinks, additional agreements are required: transaction that includes progress advancement, idempotency by event ID, version-dependent update, or a compatible transactional sink.
The phrase “exactly-once end-to-end” only makes sense if boundaries, observable effect and recovery procedure are specified. Kafka: message delivery semantics.
Example of idempotent sink in PostgreSQL
A consumer updating a projection can save the update and the consumed event flag in the same transaction. The example uses a projection of orders with current status, without attempting to resolve sequence conflicts between several producers:
CREATE TABLE processed_events (
event_id uuid PRIMARY KEY,
processed_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_projection (
order_id uuid PRIMARY KEY,
current_status text NOT NULL,
last_version bigint NOT NULL
);
A sink transaction must attempt to insert event_id into processed_events using ON CONFLICT DO NOTHING, check to see if the flag was inserted, and only if it was new modify order_projection. If the projection fails, the mark is also reversed. After the local commit, the consumer can register its offset. A crash between both steps causes replay, but the unique key prevents applying the same event_id a second time.
Caution: this resolves the duplication of a specific event, it does not correct the order of business. A new version of an aggregate may arrive before another and require waiting, reconciliation, last_version comparison, or a stream sorted by aggregate ID. Additionally, deduplication flags require a retention policy that supports the replay horizon; deleting them too soon reintroduces duplicates.
If the sink is an analytical store or a search index, the procedure must adapt to its semantics: upsert, version replacement, ingestion queues, eventual consistency and backfill are not the same. El artículo sobre índices PostgreSQL dives deeper into queries on relational projections.
Backpressure: the slowest system decides the pace
Consider a conceptual chain:
PostgreSQL → CDC → broker → processor → sink
If the sink can only consolidate a fraction of the events received by the consumer, the system accumulates lag. Increasing the broker’s throughput does not improve the bottleneck. Rates must be measured in each section and differentiate recoverable tail from loss or permanent error.
An operational design needs limits: number of workers, maximum batches, memory per process, timeout, retry policy with backoff and jitter, treatment of invalid messages and DLQ storage. A DLQ without reliable inspection and reprocessing is not a recovery strategy; it simply displaces the problem.
With multiple stages in parallel, observability must correlate event_id, aggregate ID, position/offset, source latency, broker latency, and sink latency. Reducing averages does not prove that p95/p99 queue SLOs are met or that recovery from a backlog of hours is acceptable.
Event time, processing time and watermarks
For analytical streams, distinguishing clocks is essential. Event timedescribes when the event occurred;processing time indicates when the system processed it. Retries, buffers, disconnections, and unsynchronized clocks separate the two values.
Apache Flink uses watermarks to estimate event timing advancement and determine when a window can emit a result. A watermark is not proof that late events will not arrive: a design must decide whether to discard, update, or redirect data that arrives after the supported limit.
Checkpoints allow Flink-managed state to be rebuilt based on configured guarantees. The internal state’s exactly-once promise does not magically transfer atomicity to every external HTTP call. For a compatible sink a coordinated commit protocol can be used; for another, idempotent operations and reconciliation are needed. Flink: time and watermarks and Flink: fault tolerance.
Failures that must be part of the design
| Failed | observable signal | Reaction to verify |
|---|---|---|
| API confirms order and publication fails | Registration in outbox without event consumed | CDC/poller reissues without duplicating effect |
| PostgreSQL crash after an old checkpoint | Reissue of change from slot | Idempotent sink and consistent progress |
| Broker loses a leader | Retries, leader changes and high latencies | Behavior of acks, producer and group recovery |
| Sink intermittent | Increasing lag, DLQ/retries | Concurrency Limits, Retries, and Draining |
| Incompatible schema change | Deserialization errors | Evolution of contract and quarantine of the event |
| Complete reindexing | Temporal doubling or projection delay | Backfill from snapshot + replay of changes |
| Messages out of order | Aggregate version jump | Detection, waiting, reconciliation or compensation |
| Partition Migration | Change affinity for aggregate ID | Cutting or versioning partitioning strategy |
The recovery plan should define RPO, RTO, resumption point, responsible parties, lag tolerance and what controls will be executed after returning to “green”. A positive health check does not in itself prove that the final data is correct.
Laboratory and evaluation: proposal without results
The dossier proposes three families of tests based on a synthetic dataset of orders and status changes, with reproducible identifiers and without personal data:
- Consistent publishing: introduce controlled failures between commit, outbox capture and delivery to the broker; verify that each committed order is represented.
- Semantics: induce duplicates, replays, rebalances, consumer cuts and sink restarts; check exactly what effect is applied, what is deduplicated and where lag accumulates.
- Capacity: change event size between 1 KiB and 16 KiB, partitions (12/48/192), batching, Zipfian/uniform distribution, concurrency, compression and sink limits. Record p50/p95/p99, useful throughput, error rate, RAM, CPU and recovery time.
The success condition should not be “X million events per second”. A more defensible measure is rate of correctly projected committed orders, with room for replays, maximum acceptable latency and absence of invariant violation, always together with the exact test conditions.
Until the tests are run, no new performance or reliability figures are attributed to Kafka, Redpanda, Debezium or Flink. Hardware, versions, replication factor, ack guarantee, measurement method, warm-up, repetitions, base and dataset characteristics must be declared. A benchmark published by a manufacturer only represents that manufacturer’s scenario.
When not to build this pipeline
If the product requires simple searches on a single PostgreSQL and there is no case for asynchronous integration, the broker, CDC, processing engine, and an additional sink may introduce more potential for failure than value. A traditional job queue, a small poller outbox, or even an idempotent periodic query can better solve the problem.
Complexity is justified when independent consumers appear, the need for replay, integration of multiple services, temporary events or analytical loads that require decoupling the OLTP. Even so, each added component must have an owner, a consistency boundary, and a recovery test.
The guiding principle is: optimize the throughput of the correct result, not the messages that cross a broker. If we are still choosing messaging technology, Kafka frente a Redpanda examines the broker differences; if the problem is the order by entity, el diseño de partition keys is the previous piece.
Sources and primary documentation
- PostgreSQL 18: Logical Decoding Concepts
- PostgreSQL 18: replication slots y WAL
- Debezium: Outbox Event Router
- Apache Kafka: garantías de entrega
- Apache Kafka 4.4: documentación
- Apache Flink: event time y watermarks
- Apache Flink: checkpointing y tolerancia a fallos