High-Throughput Event Pipelines: From PostgreSQL to Sinks

Blue fiber-optic strands on a dark background
Photo by S A on Unsplash. Visual editorial reference, not a verified screenshot of the software discussed. · View original image

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.

Technical diagram accompanying High-Throughput Event Pipelines: From PostgreSQL to Processors and Sinks
Original editorial diagram. The semantics of retries and duplicates must be designed explicitly.

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.

Original conceptual illustration accompanying High-Throughput Event Pipelines: From PostgreSQL to Sinks; not a screenshot.
Original conceptual illustration of this article's topic; not a screenshot or measured result.

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:

ConceptWhat does it cover?What is not automatically covered
ReplicationLog availability/durability under quorum hypothesisIndependent backup, event correction
acks=allConfirmation under broker replication rulesConfirmation of writing to an external DB
idempotent producerDuplication of retries within the scope of that session/protocolThe entire chain of side effects
Kafka transactionsCoordination of writing and offsets in compatible Kafka workflowsArbitrary Payment/API/DB as Global Transaction
Consumer offsetRecorded reading progressAtomic commit with an external sink
Processor with checkpointInternal state recovery based on engine semanticsThat 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

Failedobservable signalReaction to verify
API confirms order and publication failsRegistration in outbox without event consumedCDC/poller reissues without duplicating effect
PostgreSQL crash after an old checkpointReissue of change from slotIdempotent sink and consistent progress
Broker loses a leaderRetries, leader changes and high latenciesBehavior of acks, producer and group recovery
Sink intermittentIncreasing lag, DLQ/retriesConcurrency Limits, Retries, and Draining
Incompatible schema changeDeserialization errorsEvolution of contract and quarantine of the event
Complete reindexingTemporal doubling or projection delayBackfill from snapshot + replay of changes
Messages out of orderAggregate version jumpDetection, waiting, reconciliation or compensation
Partition MigrationChange affinity for aggregate IDCutting 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:

  1. Consistent publishing: introduce controlled failures between commit, outbox capture and delivery to the broker; verify that each committed order is represented.
  2. Semantics: induce duplicates, replays, rebalances, consumer cuts and sink restarts; check exactly what effect is applied, what is deduplicated and where lag accumulates.
  3. 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