Part 5 of the Apache Kafka series
An order is committed in PostgreSQL. The application then tries to publish an OrderCreated event to Kafka – and crashes between the two operations.
The database says the order exists. Payment, shipping and notification services never hear about it. The platform is now inconsistent, even though both technologies are working exactly as designed.
This is the dual-write problem. It sits at the centre of every serious architecture that connects a transactional database with Kafka. In this final part of the series, we look at two widely used production approaches that address it – Change Data Capture (CDC) and the Transactional Outbox Pattern – and show how Debezium, PostgreSQL, CQRS and replayable read models fit into the resulting architecture.
New to the series? Earlier parts cover event-driven architecture and why Kafka emerged (Part 1), Kafka’s internal architecture, partitions and delivery guarantees (Part 2), performance, storage and replay (Part 3), and Kafka Connect, Streams and ksqlDB (Part 4).
1. The Dual-Write Problem: A Silent Consistency Killer
Consider a standard order placement flow:
- The application saves an order to PostgreSQL.
- The application publishes an OrderCreated event to Kafka.
Both steps succeed in the happy path. But when the database write succeeds and the Kafka write fails, the order exists in the database while every downstream service behaves as though it does not. The system is silently inconsistent. The reverse is equally damaging: if the Kafka publish succeeds and the database transaction rolls back, downstream services process an order that never existed.
This is not an edge case. It is a structural flaw in any architecture that writes to two independent systems without coordination. Application-level retries and exception handling alone cannot make the two independent writes atomic. Two widely used production approaches address this problem: Change Data Capture and the Transactional Outbox pattern.
2. Change Data Capture: Reading the Transaction Log
CDC eliminates the dual write by removing the second write entirely. Instead of the application publishing events, CDC reads committed changes directly from the database transaction log and publishes them to Kafka as a separate, asynchronous step.
In PostgreSQL, that log is the Write-Ahead Log (WAL) — the append-only log maintained for crash recovery. PostgreSQL records changes in the WAL before the corresponding data pages are flushed to disk. Logical decoding then exposes the changes from committed transactions, while changes from rolled-back transactions are not published downstream. CDC tools convert these low-level changes into structured events.
The application performs only one transactional write; event publication is derived asynchronously from the committed database change. There is no application-level dual write.
The Architecture
PostgreSQL → WAL → Debezium (CDC connector) → Kafka → downstream systems
When CDC Is the Right Choice
- connecting an existing database to Kafka with minimal changes to the application,
- data synchronisation – keeping Elasticsearch, S3, a data warehouse or another downstream database up to date,
- capturing changes from multiple tables without modifying each service,
- building CQRS read models (Section 7),
- primarily data integration rather than domain event publishing.
3. Debezium: CDC in Practice
Debezium is an open-source CDC platform that runs as a set of Kafka Connect source connectors. It supports PostgreSQL, MySQL, MongoDB, SQL Server, Oracle and several other databases.
For PostgreSQL, Debezium uses logical replication, decoding WAL entries through a replication slot. Each committed change is converted into a structured event with before and after fields – one of which may be null depending on the operation – plus the operation type (c, u, d, r) and source metadata such as table, transaction ID and timestamp. The completeness of the before state depends on the table’s PostgreSQL REPLICA IDENTITY setting: with the default configuration, UPDATE and DELETE events normally expose only the previous key columns; full previous-row values require REPLICA IDENTITY FULL.
This structure suits audit logging, synchronisation and downstream materialisation – but the event shape is tied directly to the database schema.
Configuring PostgreSQL for Debezium
PostgreSQL must be configured for logical replication:
- wal_level = logical (rather than the default replica),
- sufficient max_replication_slots and max_wal_senders,
- a connector user with replication privileges and, on self-managed PostgreSQL, a matching pg_hba.conf entry
- a publication covering the captured tables,
- a failover strategy for the replication slot.
Debezium can create its replication slot and publication automatically when the connector user has the required privileges. In production environments, many teams manage these objects explicitly to retain tighter control over permissions, upgrades and failover procedures.
An illustrative excerpt of the connector configuration:
{
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"database.hostname": "postgres.example.internal",
"database.port": "5432",
"database.user": "debezium",
"database.password": "${file:/secrets/db.properties:password}",
"database.dbname": "orders",
"topic.prefix": "orders",
"plugin.name": "pgoutput",
"publication.autocreate.mode": "filtered",
"slot.name": "orders_cdc",
"table.include.list": "public.orders",
"snapshot.mode": "initial"
}Production values depend on privileges, failover design, table ownership and the required snapshot strategy. An Outbox implementation additionally configures the Debezium Outbox Event Router SMT (io.debezium.transforms.outbox.EventRouter) and normally applies it only to records originating from the outbox table.
Expert insight: Replication slots retain WAL segments until the slot consumer has processed them. If the connector is paused or falls behind, unconsumed WAL accumulates and can exhaust disk space. max_slot_wal_keep_size (PostgreSQL 13+) can serve as a final safety guard against unlimited WAL growth, but it is not a substitute for monitoring: if the connector falls further behind than the configured limit, PostgreSQL may remove WAL that the slot still requires, making the slot unusable and potentially forcing a new snapshot or a recovery procedure.
Monitoring the retained WAL takes one query:
SELECT
slot_name,
active,
pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS retained_wal FROM pg_replication_slots;4. The Transactional Outbox Pattern: Publishing Business Events Reliably
The Outbox Pattern uses the database’s own transactional guarantees. The application writes two things within a single database transaction:
- the business change to the primary table (e.g. a new row in orders),
- a record of the event to an outbox table (e.g. an OrderCreated event).
Both writes are atomic – either both commit or neither does. The dual-write problem is eliminated at the database level. A separate relay – typically Debezium capturing the outbox table, or a dedicated polling publisher – reads the outbox and publishes the events to Kafka.
The Architecture
Application transaction → business tables + outbox → Debezium Outbox Event Router → Kafka → consumers
Anatomy of an Outbox Record
A common application-level outbox schema contains id, aggregate_type, aggregate_id, event_type, payload, occurred_at:
- id – a unique event identifier that consumers use for deduplication,
- aggregate_id – a natural Kafka message key, preserving per-aggregate ordering within a partition,
- aggregate_type – identifies the business aggregate or domain and can be used to route events to the appropriate Kafka topic,
- event_type – the business meaning of the event,
- payload – the event body, treated as a versioned contract,
- occurred_at – records when the business event occurred; it can be mapped to the Kafka message timestamp or included in the event envelope or headers,
- schema_version (optional) – identifies the version of the event contract, stored in the payload, a header or an additional outbox column.
Debezium’s Outbox Event Router is configurable. Its default convention uses id, aggregatetype, aggregateid, type and payload; custom column names – including a timestamp such as occurred_at – must be mapped explicitly in the SMT configuration (e.g. table.field.event.timestamp for a custom timestamp).
When the Outbox Pattern Is the Right Choice
- designing a new service or microservice from scratch,
- publishing clean domain events that represent business intent rather than database changes,
- full control over the event schema as a stable contract for other teams,
- keeping the internal database model private,
- a single business operation spanning multiple database writes that should produce one event,
- workflow choreography across services.
Expert insight: The outbox table requires an explicit lifecycle policy. Kafka topic compaction does not determine when PostgreSQL outbox rows are safe to remove – they are two separate retention layers. Use time-based cleanup with a sufficient safety window, table partitioning with controlled partition removal, or a framework-specific cleanup mechanism whose failure and lag semantics are fully understood. Debezium’s Outbox Event Router expects INSERT operations and filters DELETEs, so cleanup deletes do not emit events. Monitor table size, dead tuples, index growth and cleanup failures from the first production deployment.
Note that a polling relay may publish the same message more than once, for example if it crashes after publishing but before marking the row as processed. Consumers that write to external systems or perform non-transactional side effects therefore still require idempotency, regardless of the relay mechanism.
5. Raw Table CDC vs. the Transactional Outbox Pattern
CDC and the Outbox Pattern are not mutually exclusive. CDC is a mechanism for capturing committed database changes, while the Outbox Pattern is an application design pattern for recording domain events atomically. In many production architectures, Debezium CDC is exactly the mechanism that publishes records from the outbox table. The comparison below therefore contrasts raw table CDC – capturing business tables directly – with the Outbox Pattern:
| Raw Table CDC | Transactional Outbox Pattern | |
| What it publishes | Data changes | Business events |
| Event shape | Reflects the database model | Designed as a domain contract |
| Best for | Integration and synchronisation | Domain event publishing |
| Application changes | Minimal | Moderate (outbox table + transaction logic) |
| Schema exposure | Database schema is visible in events | Internal model stays private |
| Existing systems | Yes | Possible, but requires application changes |
| New systems | Possible | Yes |
| Contract stability | Lower (schema changes propagate) | Higher (events are explicitly versioned) |
The choice is not binary. Complex architectures frequently use both: raw CDC for data synchronisation to downstream stores, and the Outbox Pattern for domain event publishing between services.
6. Practical Architectures
CDC: PostgreSQL to Elasticsearch
A product catalogue lives in PostgreSQL; the search layer is Elasticsearch. The goal is to keep Elasticsearch in sync without modifying the product service.
PostgreSQL → Debezium → Kafka → Kafka Connect (Elasticsearch sink) → Elasticsearch
Key design decisions:
- Elasticsearch is not the system of record; it is a read projection of PostgreSQL. If the Kafka topic retains a reconstructable history – or if a new source snapshot can be performed – the index can be rebuilt without ever treating Elasticsearch as authoritative.
- With the default snapshot.mode=initial and no previously stored connector offsets, Debezium first performs a consistent snapshot, and then continues streaming from the corresponding WAL position. A controlled resnapshot or backfill can later rebuild a current-state projection without requiring unlimited Kafka retention.
- Schema changes must be managed. Adding an optional column is usually straightforward; renaming, changing the type of, or removing a column requires coordinated schema and index changes.
Outbox: Order Service to Downstream Services
An order service receives a new order. The payment, shipping and notification services all need to react.
Order Service → PostgreSQL transaction (orders + outbox) → Debezium → Kafka → downstream services
Key design decisions:
- The OrderCreated event is designed explicitly, not derived mechanically from a database row. It contains the fields downstream services need, in the format they expect.
- Event versioning is planned from the start; a version field lets consumers handle multiple versions during migration.
- Idempotency is implemented in each consumer. Most end-to-end Kafka pipelines are designed for at-least-once processing: a record can be delivered or processed more than once after retries, consumer restarts or relay failures. Consumers must use event IDs, idempotency keys or transactional deduplication to prevent duplicate business side effects.
Important boundary: CDC and the Outbox pattern make event publication consistent with the database transaction. They do not make downstream business processing exactly-once. Retries, duplicates, poison messages and partially completed side effects must still be handled explicitly.
7. CQRS: Separating Write and Read Models
CQRS – Command Query Responsibility Segregation – separates the write model (how data is stored and mutated) from the read model (how data is queried and presented). In a Kafka-based architecture, the write model lives in PostgreSQL, optimised for transactional consistency, while each read model lives in a store optimised for its query patterns – Elasticsearch for full-text search, Redis for low-latency lookups, a data warehouse for analytics. Kafka carries the changes between them, keeping read models current without polling or tight coupling.
Raw CDC is well suited to read models that mirror the data. The Outbox Pattern is the better choice when read models should be built from business events, reflecting domain intent rather than internal database state.
Expert insight: read models in CQRS are intentionally disposable. If the Kafka topic retains a reconstructable history – sufficient retention, or a compacted current state with delete tombstones and compatible schemas – or if a new source snapshot can be taken, an inconsistent read model can be rebuilt rather than repaired.
8. Event Sourcing: A Related but Distinct Concept
Event sourcing is an application design pattern in which the authoritative state of an entity is represented by an append-only sequence of events rather than by a current-state record alone. The current state is reconstructed by replaying those events, often accelerated by snapshots, while separate projections may store query-optimised views. This is fundamentally different from the patterns above:
- CDC captures changes to a database that stores state directly.
- The Outbox Pattern publishes business events alongside a conventional database write; the database still stores current state.
- Event sourcing makes the event log itself the system of record.
Event sourcing is powerful for financial ledgers, audit-critical workflows and domains where history must be queryable and replayable – and it introduces significant complexity in modelling, querying and schema evolution.
Kafka can be an important distribution and processing layer in an event-sourced architecture, but it should not automatically be assumed to replace a purpose-built event store. Requirements such as optimistic concurrency on aggregates, expected versions, permanent retention and queries over event streams may call for a dedicated store. Most production Kafka architectures keep a conventional database as the system of record and bridge the gap with CDC or the Outbox Pattern.
9. Designing the Workflow Between Database and Kafka
Before implementation begins, answer these questions:
- What is the system of record? Kafka distributes changes from it; it does not replace it.
- Data change or business event? Synchronisation, search indexing and analytics feeds point to CDC; domain events – order created, payment authorised – point to the Outbox Pattern.
- What is the event schema? Design it explicitly and treat it as a versioned API; never publish raw rows as public events.
- How are schema changes managed? Use a Schema Registry with Avro, Protobuf or JSON Schema, and define compatibility rules before the first breaking change.
- What is the ordering guarantee? Ordering holds within a partition; choose partition keys deliberately and preserve them through each processing step.
- How do consumers handle duplicates? Assume at-least-once processing end to end; consumers that perform non-idempotent business side effects must use idempotency keys or deduplication.
- What is the replay strategy? Define how far back events can be replayed and configure retention or snapshot capability to support this.
- What is monitored? Connector task status, replication slot lag, consumer group lag and dead-letter topic volume, at a minimum.
- How is sensitive data handled? Apply field-level encryption, masking or filtering before regulated data reaches shared topics.
10. Common Mistakes
| Mistake | Production symptom | Recommended control |
| Direct database and Kafka writes | Missing or phantom events | CDC or the transactional outbox pattern |
| Raw CDC envelopes used as public integration contracts | Consumers break after database schema changes | Explicit, versioned domain events |
| Raw database rows published without transformation | Consumers coupled to internal implementation details | Events that carry business meaning, not table structure |
| No schema ownership or versioning | Uncontrolled drift; broken consumers | Assigned ownership; schema registry with compatibility rules |
| No consumer idempotency | Duplicate payments or notifications | Event IDs and deduplication |
| Unmonitored replication slot | WAL growth and database outage risk | Lag and retained-WAL alerts |
| Read model treated as system of record | Divergence with no recovery path | Disposable projections rebuilt from the source |
| Unmanaged outbox table | Index bloat and degraded write performance | Explicit clean-up policy with monitoring |
| Untested replay | Read model cannot be rebuilt when needed | Scheduled recovery test |
Key takeaways
- In the CDC and Outbox architectures described here, the database remains the system of record; Kafka distributes changes and events from it.
- Dual write – writing to the database and Kafka independently – is a structural consistency risk, not an implementation detail.
- CDC reads committed changes from the transaction log. It is ideal for synchronisation, search indexing and read models over existing systems.
- The Outbox Pattern writes a business event atomically with the business change. It is ideal for clean domain events from new services, and is often published via Debezium CDC.
- CQRS separates write and read models; Kafka carries changes between them via CDC or the Outbox, depending on the nature of the event.
- Event sourcing is a distinct pattern. It is not required to use Kafka effectively, and Kafka does not automatically replace a purpose-built event store.
- Publication consistency is not end-to-end exactly once: schema versioning, idempotency, monitoring and tested replay determine whether the architecture stays maintainable.
Conclusion
Apache Kafka and relational databases are complementary layers, not competitors. The database provides transactional consistency and acts as the system of record; Kafka reliably distributes changes and events across the organisation.
The dual-write problem is the most important constraint to understand when connecting the two. CDC and the Transactional Outbox Pattern are mature, production-proven approaches to resolving it – the right choice depends on whether the goal is data synchronisation or domain event publishing, and whether the system is existing or new.
This concludes our five-part Apache Kafka series. Planning a PostgreSQL-to-Kafka integration? Baremon can review your transaction boundaries, Debezium configuration, replication slot risks, event schemas and replay strategy before they turn into production incidents. Contact us to discuss the architecture of your CDC or Outbox pipeline.
