The Dual Write Problem

Why does try/catch not solve this?

The first instinct is usually this:

db.insert(order);                   // write 1
try {
    redis.xadd("orders", event);    // write 2
} catch (Exception e) {
    log.error("event not published", e);
    // and now what?
}

The problem here is not failing to catch the error; it is that there is nothing useful to do after catching it. Look at your options:

• Delete the order? The bridge may already have processed it. Deleting creates a bigger inconsistency.
• Retry? If the process dies on this line, the retry dies with it.
• Log and move on? What you have done is make the inconsistency invisible.

Reversing the order does not save you either: publish the event first and write to the database second, and now you have published an event for an order that does not exist, and the streaming engine shows a ghost.

The third row is the least known and it genuinely happened to us: the event was published, a consumer read it immediately and went to the database to fetch that record — but the transaction had not committed yet. The consumer got "record not found". The event had been published before the COMMIT.

Outbox: write the event to the database too

The idea is surprisingly simple: instead of sending the event to the messaging system, write it to a table in the same database, inside the same transaction.

CREATE TABLE outbox (
  id           bigserial PRIMARY KEY,      -- this defines the order
  event_type   text        NOT NULL,       -- 'OrderCreated'
  aggregate_id text        NOT NULL,       -- order_id — events of the same order
  payload      jsonb       NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published    boolean     NOT NULL DEFAULT false
);
CREATE INDEX ON outbox (published, id) WHERE NOT published;

-- In the application:
BEGIN;
  INSERT INTO orders (id, symbol, lots, ...) VALUES (991, 'EURUSD', 1.0, ...);
  INSERT INTO outbox (event_type, aggregate_id, payload)
  VALUES ('OrderCreated', '991', '{"orderId":991,"symbol":"EURUSD",...}');
COMMIT;   -- ← either both, or neither

"Order exists but no event" is now physically impossible. Either both are in the table, or neither is.

The relay: reading the outbox and publishing

-- A separate process, a few times a second:
BEGIN;
  SELECT * FROM outbox
   WHERE NOT published
   ORDER BY id                 -- ← ordering guarantee lives here
   LIMIT 200
   FOR UPDATE SKIP LOCKED;     -- ← several relays can run

  -- for each: redis.xadd(...) / kafka.send(...)

  UPDATE outbox SET published = true WHERE id = ANY(:ids);
COMMIT;

Three details, all learned afterwards:

• Publishing happens before marking. So if you crash after publishing, the event goes out twice. That is acceptable — at-least-once already works that way and the consumer deduplicates (details). Do it the other way around — mark first, publish second — and you lose events, which is far worse.
• Ordering is preserved via id. Events for the same order go out in sequence thanks to ORDER BY id. Running multiple relay copies weakens that guarantee; if ordering is critical, partition by aggregate_id (sharding).
• Cleanup is essential. Published rows pile up, the table grows huge and the SELECTs slow down. A daily job: delete published rows older than three days. The partial index above keeps the query independent of table size.

Inbox: the same problem on the receiving side

Now the other direction. A fill notification arrives from the bridge. You need to store it, update the position, notify the customer and write to reconciliation. What if you crash halfway?

The answer is the same idea: write first, process later.

CREATE TABLE inbox (
  event_id   text PRIMARY KEY,     -- ← dedup lives here
  payload    jsonb NOT NULL,
  received   timestamptz NOT NULL DEFAULT now(),
  processed  boolean NOT NULL DEFAULT false
);

-- Step 1: just store it and ack. Fast.
INSERT INTO inbox (event_id, payload) VALUES (:fill_id, :payload)
ON CONFLICT (event_id) DO NOTHING;     -- second delivery is ignored

-- Step 2: a separate process does the work
BEGIN;
  SELECT * FROM inbox WHERE NOT processed ORDER BY received
   LIMIT 100 FOR UPDATE SKIP LOCKED;
  -- ... do the real work ...
  UPDATE inbox SET processed = true WHERE event_id = ANY(:ids);
COMMIT;

It solves three problems at once:

• Duplication: the primary key stops the same event entering twice.
• Crashes: if you die while processing, the event is still in the table and gets reprocessed.
• Speed: you acknowledge the sender instantly; the heavy work happens in the background. The bridge does not time out.

CDC: without touching the application at all

A third route is to read the database's change log (the WAL in PostgreSQL, the binlog in MySQL) and produce events from it. Debezium does this.

The appeal is obvious: not one line of application code changes. Every row written to the positions table automatically becomes an event and flows to the risk engine.

application → BEGIN; INSERT orders + INSERT outbox; COMMIT;
                                    ↓
                          Debezium (reads the WAL)
                                    ↓
                          Kafka / Redis stream
                                    ↓
                               consumers

Which one should you pick?

Practical advice: start with outbox + polling. It takes an afternoon, needs no extra infrastructure and solves 90% of the problem. Add CDC when latency genuinely becomes an issue — and because the outbox table already exists, that migration is painless.

Checklist AI agent task list

Conclusion

Dual writes are the wall almost every team moving to microservices eventually hits. And most treat it as a bug and try to write more careful code. But it is not a bug; it is a structural consequence of two separate systems having no shared commit point.

The whole solution fits in one sentence: instead of doing two writes, do one write and derive the second from it. Outbox, inbox and CDC are all the same idea applied in different places.