Dual write solutions

Almost two years ago, in my Dual Write post, I introduced a few patterns for handling dual writes and said I might explore them in more detail later. That took a while, but here we are. If you haven’t read that post, it covers the problem and why simply reordering the writes or retrying in memory doesn’t solve it.

The idea behind these patterns is simple: when we can’t make two writes to independent systems atomic, we make one follow from a durable record of the other. In the e-commerce example from the previous post, the search service indexes a listing after its database record has been saved, or after an event to create it has been durably published. That distinction matters for the listen-to-yourself pattern, as we’ll see later. Either way, the search service needs a reliable way to learn about the listing so buyers can find it.

Let’s look at transactional outbox, listen to yourself, and change data capture, along with the tradeoffs of each. We’ll start with transactional outbox.

Transactional outbox pattern⌗

Instead of writing directly to multiple systems, we save the business data and a message describing the change in the same database transaction. In our catalog service, that means creating the product record and an outbox entry together. Either both commit or neither does. A separate process sends the message to the search service afterward.

The name comes from a mail outbox: you leave outgoing letters there for someone to collect and deliver. Here, the outbox holds messages waiting to be sent, and a message relay handles delivery.

We need two components:

• The outbox stores pending messages. In a relational database, this is typically a separate table. In a document database, it can be part of the same document as the business data. The important part is that saving the business data and the pending message is atomic.
• The message relay collects pending messages and sends them to the destination. It can run as a background task or a separate service. In this example, it periodically polls the outbox and asks the search service to index each new product.

Let’s make this more concrete with some SQL. For a simple indexing task, we can store a flag on the product row itself. Creating the product and marking it for indexing happen in a single statement:

-- Create the product and mark it for indexing.
INSERT INTO
    "product" ("name", "price", "need_search_indexing")
VALUES
    ('Battery', 3.25, TRUE);

-- The relay reads products waiting to be indexed.
SELECT * FROM "product" WHERE "need_search_indexing" IS TRUE;

This flag tells the relay that the product needs search indexing, but it doesn’t preserve each individual change as an event. If we want a separate entry for each event, we can use an outbox table instead. In this example, the application supplies the product ID, p0001:

-- Both inserts must be part of the same transaction.
BEGIN;

INSERT INTO
    "product" ("id", "name", "price")
VALUES
    ('p0001', 'Battery', 3.25);

INSERT INTO
    "product_event_outbox" ("product_id", "type", "payload", "processed_at")
VALUES
    ('p0001', 'CREATED', '{"name": "Battery", "price": 3.25}', NULL);

COMMIT;

-- NULL means the event has not yet been marked as processed.
SELECT * FROM "product_event_outbox" WHERE "processed_at" IS NULL;

What I like about transactional outbox is how simple and flexible the basic idea is. We can implement it using our existing database and a background task, without adding a message broker or adopting a framework. We can also have the relay publish to a message broker, or use a framework that manages the outbox for us.

Of course, we still have more code to maintain, along with the extra columns or tables. If we use a separate outbox table, we’ll likely need a scheduled cleanup job, such as a cron job, and a retention policy for processed events so the table doesn’t keep growing indefinitely. Events still waiting to be delivered need to stay in the outbox.

Delivery happens asynchronously, so the database and the search index are only eventually consistent: a product can be saved before it becomes searchable. If the relay polls periodically, the polling interval adds to that delay.

Listen to yourself pattern⌗

Listen to yourself is an event-driven pattern. Sticking with our product example, when a user creates a product, the catalog service first publishes an event.product.submitted event to a message broker instead of immediately saving the product to its database. Once the broker acknowledges that the event has been durably stored, the service tells the user that the request has been accepted. That response doesn’t mean the product is already in the database or searchable.

The catalog service then consumes its own event and saves the product to its database. That’s where the name comes from: the service listens to the events it publishes. The search service has its own subscription and updates the search index, while a notification service can consume the same event to send a notification. Each service needs an independent subscription or consumer group so it receives the event, rather than competing with the other services for a single copy.

If a future feature needs the same event, we can add another consumer without changing the existing ones. This makes it flexible to add features and scale consumers independently. The database write also has its own retry path through the catalog consumer, rather than relying on the request handler to write to both systems.

The tradeoff is that we need a message broker if we don’t already have one, and this way of organizing the application can be unfamiliar. Even the catalog service’s own database update now happens asynchronously, so reading the product immediately after the request is accepted may return nothing. The initial response can be quick because it only waits for the broker, but the product still takes time to reach the database and search index.

Change data capture⌗

As the name suggests, change data capture (CDC) captures changes made to a database and sends them to other systems. Here, we’ll focus on log-based CDC, which uses the logs databases maintain for recovery or replication. Examples include PostgreSQL’s write-ahead log (WAL), MySQL’s binlog, and MongoDB’s oplog. In our product example, the catalog service saves the product to its database, and a CDC pipeline picks up the committed change and sends it downstream.

The log format matters. MySQL, for example, supports statement-based and row-based replication. Statement-based replication records SQL statements for a replica to execute again. This can be compact, but some nondeterministic operations, such as generating a UUID, aren’t safe to replay this way. Row-based replication records the affected row values instead of rerunning the original statement. That can produce larger logs, especially when one statement changes many rows, but it gives the CDC connector the actual data changes. Debezium’s MySQL connector requires row-based logging.

A typical CDC pipeline has three parts:

• A source connector reads committed changes from the database’s log and publishes change events.
• A transport, often a message broker, holds those events until downstream consumers process them.
• A sink connector or application consumer reads the events and applies the changes to the destination, such as a search index or another database.

Kafka and Debezium are a common combination, but CDC doesn’t require that particular stack. Other tools and database integrations can provide similar pipelines. The tradeoff is the infrastructure and operational work: connectors, the event transport, and consumers all need to be managed. I’d generally be more inclined to choose CDC when that infrastructure is already available and the team is familiar with it.

Another tradeoff is that changes to business tables describe stored data, rather than the business intent behind it. A change event can tell us whether a row was inserted, updated, or deleted. Comparing the values before and after an update can also tell us that a price or category changed, but it won’t necessarily tell us why. For example, here’s a simplified Debezium-style update event with the schema envelope and some metadata omitted:

{
  "payload": {
    "before": {
      "id": "p0001",
      "name": "Battery",
      "price": 3.25,
      "category_id": "c001"
    },
    "after": {
      "id": "p0001",
      "name": "Battery",
      "price": 2.95,
      "category_id": "c001"
    },
    "source": {
      "connector": "mysql",
      "db": "catalog",
      "table": "product"
    },
    "op": "u"
  }
}

Here, op: "u" identifies an update, and comparing before with after shows that the price dropped from 3.25 to 2.95. It doesn’t tell us whether that was a promotion or a correction. If consumers need explicit business events, we can combine CDC with the outbox pattern: the application writes the event to an outbox, and CDC handles publishing it.

What I like about CDC is that it separates the logic for publishing database changes from the business code. The catalog service handles the business operation and writes to its database; the CDC pipeline reads the database log and publishes the changes. We can then add consumers without adding publishing logic to the catalog service for each downstream integration.

Conclusion⌗

The main difference is what we persist first, and what the application can promise once that write succeeds:

These approaches give us a durable place to resume work, but delivery still needs retries and recovery. For example, an outbox relay can crash after the search service has indexed a product but before marking the event as sent. On restart, it sends that event again. Consumers need to handle duplicates safely, for example by tracking processed event IDs or making repeated writes have the same effect. That’s the idempotency part we still have to implement.

The choice depends on whether the service needs to save its own data synchronously, what downstream consumers need, and which infrastructure the team is prepared to operate.

Read more⌗

• Transactional outbox
• Understanding the Dual-Write Problem and Its Solutions
• Distributed Data for Microservices: Event Sourcing vs. Change Data Capture
• Debezium connector for MySQL