The Dual Write Problem

Why does try/catch not solve this?

Some short context first. In this system an order is written to PostgreSQL and then goes out to a Redis stream as an event. Three consumers read that event: the streaming engine shows it on the customer's screen, the bridge forwards the order to the liquidity provider, and the risk engine watches the position. The "write to the database, then publish the event" step is called a dual write, and 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. The real trap is this: an exception does not mean the message was not sent. The connection may have timed out after Redis had already written the message. When you land in the catch block, you do not know whether the event went out or not. Look at your options:

• Delete the order? If the event went out, the bridge has already processed it, and deleting creates a bigger inconsistency. If it did not, deleting was right — but you cannot tell which case you are in.
• 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,      -- taken at INSERT time (see below)
  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
);
-- partial index: only pending rows, stays small as the table grows
CREATE INDEX ON outbox (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, every 100 ms:
BEGIN;
  SELECT * FROM outbox
   WHERE NOT published
   ORDER BY id                 -- ← id order; not commit order (see below)
   LIMIT 200
   FOR UPDATE SKIP LOCKED;     -- ← several relays can run

  -- for each: redis.xadd(...) / kafka.send(...)
  -- (the transaction is open during this: keep it short)

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

Four details. The first three were learned afterwards; know the fourth in advance:

• 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.
• id order is not commit order. A bigserial value is taken at INSERT time. T1 takes id=10, T2 takes id=11; if T2 commits first, the relay publishes 11 while 10 is not even visible yet — it arrives on the next poll. So ordering can break even with a single relay. Run several relays with SKIP LOCKED and the guarantee is not weakened, it is gone: two relays publish different batches at the same time. Two separate problems, two separate fixes: for multiple relays, partition by aggregate_id (sharding). For commit order, make sure the transactions writing the same order are serialized by the lock on the orders row; in practice that is what keeps the order. If you want to be rid of both, move to outbox + CDC below: the WAL delivers transactions in commit order.
• Cleanup is essential. Thanks to the partial index, the relay's SELECT is not affected by table size; the problem is elsewhere. Every row gets one UPDATE, so every publish leaves a dead tuple behind: bloat, vacuum load and disk all grow. A daily job: delete published rows older than three days.
• The transaction is open during the network call. The row locks you took with FOR UPDATE stay until Redis or Kafka answers. If the broker slows down, long transactions and locks pile up. Keep the batch small and put a short timeout on the publish call.

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,
  attempts   int     NOT NULL DEFAULT 0    -- for poison messages
);

-- 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           -- arrival order; not the event's own order
   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. If the real work lives in the same database (position, reconciliation row), this is atomic. But external side effects like "notify the customer" are not part of the transaction; on reprocessing, the customer may get two notifications. Idempotency for external calls is still your job.
• 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 most of the problem. Add CDC when latency genuinely becomes an issue — the outbox table and the event schema already exist, so that migration is small. But the order matters: Debezium starts reading from the WAL position where it connects. If you stop the relay first, the events sitting at published = false at that moment are lost. Connect Debezium first, let the relay drain its queue, then stop the relay; dedup handles the short period of double publishing. After that, drop the published column and the partial index that depends on it, and add the DELETE after the INSERT in the application.

Checklist

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.