What is the dual write problem, and why don't retries fix it?

A service that handles an order typically does two things: it writes the order to Postgres and publishes an OrderPlaced event to a message broker. These are two separate systems with no shared transaction. Postgres cannot roll back a broker publish, and the broker cannot wait for a Postgres commit. When one succeeds and the other does not, downstream services act on a state that does not exist, or never learn about one that does. This is the dual write problem.

The failure windows are narrow but certain to occur at scale:

Sequence Failure point Result
Commit, then publish Process crashes or broker is unreachable after commit Row exists, event never sent
Publish, then commit Commit fails (constraint violation, serialization error, crash) Event announces a change that was rolled back
Either order Publish times out with unknown outcome Retrying may duplicate the event; not retrying may lose it

Retries do not close these windows. A retry loop lives in process memory. If the process dies between commit and publish, the loop dies with it, and nothing records that a publish was owed. Publishing first only reverses the error: you now emit events for writes that never happened, and consumers cannot tell them apart from real ones.

For anyone whose services emit domain events, this matters because events drive other systems' state. Billing, inventory, search indexes, and caches all trust them. A lost or phantom event leaves those systems quietly inconsistent, with no error to alert on.

How does the transactional outbox pattern work in Postgres?

The fix is to stop writing to two systems at once. The service writes the business row and an event row into the same Postgres transaction. A separate relay process then reads the event rows and publishes them to the broker. Because Postgres commits or rolls back the transaction atomically, the event exists if and only if the state change committed. There are no phantom events from rolled-back orders and no lost events from a crash between commit and publish.

Service --(one transaction: orders + outbox)--> Postgres --(poll)--> Relay --(publish)--> Broker

A minimal outbox table needs these columns:

  • id: a unique key, either a bigint identity or a UUID. Consumers use it to detect duplicates.
  • aggregate_type, aggregate_id: the entity the event describes. They determine routing and the partition key, which in turn determine per-entity ordering.
  • event_type: what happened, such as OrderPlaced. Consumers dispatch on it.
  • payload jsonb: the event body, captured at write time so it reflects the committed state.
  • schema_version: lets consumers parse old and new payload shapes side by side.
  • created_at: used for lag monitoring and retention.
  • published_at: null until the relay confirms the broker accepted the event. This is the "done" marker.
  • attempts, last_error: let you spot a poison row and alert on it, so it doesn't silently block the queue.

This design does not deliver exactly once. The relay can publish an event and crash before it sets published_at, and it will publish that event again on restart. The outbox turns a dual-write consistency problem into an at-least-once delivery problem, which is easier to handle.

-- Minimal outbox table: one row per event, written alongside the business change.
CREATE TABLE outbox (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- dedupe key for consumers
  aggregate_type text        NOT NULL,              -- e.g. 'Order'; drives topic routing
  aggregate_id   text        NOT NULL,              -- partition key; gives per-entity ordering
  event_type     text        NOT NULL,              -- e.g. 'OrderPlaced'; consumers dispatch on it
  payload        jsonb       NOT NULL,              -- event body captured at write time
  schema_version int         NOT NULL DEFAULT 1,    -- lets consumers parse old and new shapes
  created_at     timestamptz NOT NULL DEFAULT now(),-- lag monitoring and retention
  published_at   timestamptz,                       -- NULL until the broker confirms receipt
  attempts       int         NOT NULL DEFAULT 0,    -- surfaces poison rows
  last_error     text                               -- last publish failure, for alerting
);

-- Partial index keeps the relay's "what's pending?" scan small.
CREATE INDEX outbox_unpublished ON outbox (id) WHERE published_at IS NULL;

-- Business row and event row commit or roll back together.
BEGIN;
INSERT INTO orders (id, customer_id, total_cents, status)
VALUES ('ord_1001', 'cus_42', 4999, 'placed');

INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
VALUES ('Order', 'ord_1001', 'OrderPlaced',
        jsonb_build_object('order_id', 'ord_1001',
                           'customer_id', 'cus_42',
                           'total_cents', 4999));
COMMIT;

Creates a minimal outbox table, adds a partial index for unpublished rows, and writes an order and its OrderPlaced event in a single Postgres transaction so neither can commit without the other.

How do you implement a polling relay with SELECT FOR UPDATE SKIP LOCKED?

The relay is a loop around one short transaction. It opens a transaction and selects a batch of rows where published_at is null, ordered by id, with LIMIT and FOR UPDATE SKIP LOCKED. It publishes each row to the broker, waits for the broker's acknowledgement, then sets published_at (or deletes the rows) and commits.

SKIP LOCKED is what makes horizontal scaling safe. A second relay instance running the same query does not wait on rows the first one has locked. It skips them and claims the next unlocked batch. No row is claimed twice while a claim is held. If a relay dies, its transaction aborts, the locks release, and another instance picks the rows up.

Publishing inside the claiming transaction is deliberate. If you commit the claim first and publish afterwards, a crash in between loses the event. Holding the lock until the broker acknowledges means a failure can only cause a republish, which consumers must already tolerate. The cost is that transactions stay open for a broker round trip, so keep batches small.

The poll interval trades latency against load. A short interval delivers events sooner but runs the query constantly, even when the outbox is empty. LISTEN/NOTIFY can wake the relay as soon as a row is inserted. It cannot replace polling, though. A notification sent while no relay is listening is simply lost, so keep a slower poll as the backstop.

Parking poison messages

Some events will fail on every attempt, for example because the payload is too large or the serializer breaks on it. When a publish fails, record attempts and last_error on the row in its own transaction. Once a row passes a threshold, mark it parked so the claim query excludes it. Without this, one bad row at the head of the queue blocks everything behind it.

Parking has a cost. Later events for the same aggregate keep flowing past the parked one, so consumers can see them out of order. If ordering matters, park every subsequent event for that aggregate too.

import time
import psycopg  # psycopg 3

CLAIM = """
SELECT id, topic, payload FROM outbox
WHERE published_at IS NULL AND parked_at IS NULL  -- parked rows are skipped
ORDER BY id LIMIT %s
FOR UPDATE SKIP LOCKED  -- other relays skip these rows instead of waiting
"""
RECORD_FAILURE = """
UPDATE outbox SET attempts = attempts + 1, last_error = %s,
  parked_at = CASE WHEN attempts + 1 >= %s THEN now() END  -- park past threshold
WHERE id = %s
"""
MAX_ATTEMPTS = 5

def relay(dsn, publish, batch=50, poll_seconds=1.0):
    # autocommit=True so each conn.transaction() block is one real transaction
    with psycopg.connect(dsn, autocommit=True) as conn:
        while True:
            rows, failed_id = [], None
            try:
                with conn.transaction():  # claim + publish + mark, then commit
                    rows = conn.execute(CLAIM, (batch,)).fetchall()
                    for row_id, topic, payload in rows:
                        failed_id = row_id
                        publish(topic, payload)  # must block until broker ack
                    failed_id = None
                    conn.execute("UPDATE outbox SET published_at = now() WHERE id = ANY(%s)",
                                 ([r[0] for r in rows],))
            except Exception as exc:
                # Claim rolled back; rows already sent will be republished (at-least-once).
                if failed_id is None:
                    raise  # database error, not a publish failure
                with conn.transaction():  # record the failure in its own transaction
                    conn.execute(RECORD_FAILURE, (str(exc)[:1000], MAX_ATTEMPTS, failed_id))
                continue
            if not rows:
                time.sleep(poll_seconds)  # backstop poll; NOTIFY could wake us sooner

A psycopg 3 relay loop that claims a batch with FOR UPDATE SKIP LOCKED, publishes while holding the lock, marks rows published before commit, and records attempts and last_error in a separate transaction, parking a row once it reaches the attempt limit.

Why will consumers see duplicates, and how do you make them idempotent?

The relay's failure window can't be closed. It publishes a message, the broker acknowledges it, and then the relay crashes before its UPDATE marking the row as sent commits. The row is still unpublished, so the next poll picks it up and sends it again. Network timeouts on the publish call cause the same thing: the relay can't tell "not delivered" from "delivered, ack lost," so it retries. Over enough time, duplicates will happen. The outbox gives you at-least-once delivery, and every consumer has to handle that.

Deduplicate on the event id, inside the consumer's transaction

Assign each event a stable id when the outbox row is inserted, and carry it in the message. Don't use the broker's message id or offset, because a republished copy gets a new one.

The consumer keeps a processed_events table with a unique constraint on that id. For each message it opens one transaction, inserts the event id, applies its state change, commits, and only then acknowledges the message. If the insert hits the constraint (with ON CONFLICT DO NOTHING, that shows up as zero rows affected), the event was already handled. Skip it and acknowledge.

The insert and the state change must share one transaction. If they are separate commits, a crash between them either loses the event or applies it twice. That is the dual write problem again, now on the consumer's side.

Two caveats apply. Keep processed ids longer than any realistic redelivery window. Side effects outside the database, such as emails or third-party HTTP calls, aren't covered by this transaction. Pass the event id to those systems as an idempotency key where they support one.

The outbox doesn't remove this complexity. It moves it downstream, and every consumer team has to implement it.

import json
import psycopg

# Schema, created once in the consumer's own database:
#   CREATE TABLE processed_events (
#     event_id     uuid PRIMARY KEY,  -- unique constraint does the dedup
#     processed_at timestamptz NOT NULL DEFAULT now()  -- for retention cleanup
#   );

def handle(conn: psycopg.Connection, msg) -> None:
    event = json.loads(msg.body)
    # Stable id assigned when the outbox row was inserted.
    # Never the broker's message id or offset: a republished copy gets a new one.
    event_id = event["event_id"]

    # One transaction: the dedup marker and the state change commit together or not at all.
    with conn.transaction():
        cur = conn.execute(
            "INSERT INTO processed_events (event_id) VALUES (%s) "
            "ON CONFLICT (event_id) DO NOTHING",
            (event_id,),
        )
        if cur.rowcount == 1:
            # First delivery of this event, so apply the state change.
            conn.execute(
                "UPDATE orders SET status = %s WHERE id = %s",
                (event["status"], event["order_id"]),
            )
        # rowcount == 0: already processed. Skip, but still acknowledge below.

    # Acknowledge only after commit. A crash before this line causes redelivery,
    # and the unique constraint absorbs it.
    msg.ack()

# Retention: keep ids longer than any realistic redelivery window, e.g. a periodic job:
#   DELETE FROM processed_events WHERE processed_at < now() - interval '30 days';

A consumer that inserts the event id into a uniquely constrained processed_events table and applies its state change in the same Postgres transaction. It acknowledges the message only after commit, so a redelivered duplicate becomes a no-op.

Why is outbox id order not commit order, and what ordering can you guarantee?

A Postgres sequence hands out its value when nextval() runs, not when the transaction commits. A created_at defaulting to now() is worse: it records when the transaction started. Suppose transaction A takes id 101 and transaction B takes id 102, but B commits first. A relay that polls WHERE id > last_seen publishes 102, advances its high-water mark, and never returns for 101 once A commits. Sequences are also non-transactional, so rolled-back inserts leave permanent gaps. Waiting for gaps to fill does not work, because the relay cannot tell a gap that will fill from one that never will. Long-running transactions widen this window from milliseconds to however long the slowest writer holds its transaction open.

Several mitigations close the hole:

  • Claim-and-mark, not high-water marks. Select unpublished rows, publish them, and mark them done. A late-committing row is still unpublished, so the next poll picks it up.
  • Per-aggregate ordering. Use aggregate_id as the broker partition key. Events for one order or account then land in one partition and are consumed in sequence.
  • Per-aggregate sequence numbers. Store a version column with a unique constraint on (aggregate_id, version). Concurrent relays with SKIP LOCKED can still publish one aggregate's events out of order, and the version lets consumers detect and reorder or reject them.
  • CDC. Logical decoding emits changes in commit order, which removes the race at the source.

Most consumers only care about order within one entity, so global ordering is rarely needed. You need it when correctness depends on cross-entity causality, such as an append-only ledger rebuilt by replay, or a read model where event B is only valid after event A on a different aggregate. In those cases, use CDC into a single partition and accept the throughput ceiling that comes with it.

Polling relay vs logical replication CDC: what are the alternatives?

A polling relay is not the only way to move outbox rows to a broker. Log-based change data capture (CDC) reads committed inserts from Postgres logical replication, so the database pushes changes instead of the relay asking for them. Because logical decoding emits transactions in commit order, CDC avoids the id-versus-commit-order gap that polling has to work around.

The cost is the replication slot. A slot makes the primary retain WAL until the consumer confirms it has processed that WAL. If the connector stalls, crashes, or is decommissioned without dropping its slot, WAL accumulates until the primary's disk fills and writes stop. Running CDC means alerting on retained WAL per slot (from pg_replication_slots) and having a runbook for dropping or recreating a slot. That runbook also has to cover replaying from the outbox table afterwards, which works only if rows are kept long enough. Failover also needs planning, because slots must exist on the promoted replica.

Polling relay Logical replication CDC
Latency Poll interval plus batch time Near commit time
Ordering Id order, needs care Commit order
Database load Repeated queries, index churn WAL decoding, no polling queries
Operational complexity One worker and a SQL query Slots, connector, failover handling
Failure modes Backlog grows in the table Retained WAL fills the primary's disk

Sometimes you don't need an outbox at all. If consumers can read the source tables or a Postgres-backed queue in the same database, a single transaction both changes state and enqueues work. With no broker involved, there is no dual write. This works until consumers need independent scaling, fan-out across teams, or isolation from your primary's load.

How do you keep the outbox table healthy and design payloads that last?

Table hygiene

An outbox row is inserted, updated once when published, and then never read again. In Postgres every update writes a new tuple version, so each event produces at least two heap tuples plus index entries and the WAL for all of them. If published rows are never removed, the table and its indexes keep growing. The relay query then scans dead tuples and stale index entries, and autovacuum falls behind. Delete published rows promptly, or purge them in bounded batches so each pass doesn't create one large burst of WAL and locking. Index only the unpublished predicate with a partial index. That keeps the relay's index small even when the table is large. Watch for long-running transactions, including idle-in-transaction sessions. Vacuum cannot remove tuples that an open snapshot might still see, so a single forgotten session can let bloat build up.

Payload design

Self-contained events carry the state that consumers need, plus a schema version. They tolerate the producer being unavailable and record what was true at commit time. ID-only events are smaller, but every consumer has to call back to the producer. That couples availability, adds load, and returns current state rather than event-time state. Either way, the event contract now ships with your database migrations. Version it accordingly.

What to monitor

  • Oldest unpublished row age: the primary lag signal.
  • Relay error rate.
  • Parked message count.
  • Replication slot lag if you use CDC. An abandoned slot retains WAL indefinitely.
-- Relay reads only unpublished rows; the partial index stays small as the table grows.
CREATE INDEX IF NOT EXISTS outbox_unpublished_idx
  ON outbox (id) WHERE published_at IS NULL;

-- Bounded purge: run repeatedly (e.g. from a scheduler) until 0 rows are deleted,
-- so each pass produces a small amount of WAL and holds locks briefly.
DELETE FROM outbox
WHERE id IN (
  SELECT id FROM outbox
  WHERE published_at < now() - interval '1 hour'
  ORDER BY id
  LIMIT 5000
);

-- Primary lag signal: age of the oldest unpublished row (uses the partial index).
SELECT now() - created_at AS oldest_unpublished_age
FROM outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 1;

-- Bloat check: dead tuples waiting on vacuum, and when autovacuum last ran.
SELECT n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'outbox';

-- Long-running or idle-in-transaction sessions that hold back vacuum.
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start < now() - interval '5 minutes'
ORDER BY xact_start;

-- CDC only: WAL retained by each replication slot; an inactive slot keeps growing.
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Postgres SQL for a partial index on unpublished outbox rows, a batched purge of published rows, and queries for the oldest-unpublished-row lag, dead tuples, vacuum-blocking transactions and replication slot WAL retention.

When should you use the transactional outbox pattern, and what does it cost?

Use the transactional outbox pattern when a service changes state in Postgres and must announce that change through a broker, and a missing event is a correctness bug. Examples include an order that is committed but never billed, or an account that is deleted but stays in a search index. Skip it in these cases:

  • No broker is involved. If you only call another service synchronously, the dual write problem takes a different form.
  • Loss is tolerable. Cache-warming hints and sampled analytics can lose an event without harm.
  • Consumers can read from Postgres. If they can claim work from a queue table in the same database, that table already is the queue, and a relay adds a hop for nothing.
Cost you take on Why it exists What it replaces
Extra insert per transaction Event and state must commit atomically Events lost when a publish fails after commit
A relay to deploy and operate Something must move rows to the broker Publish calls scattered through request handlers
Idempotent consumers Delivery is at-least-once Silent gaps that nobody notices
Ordering design (keys, partitions) Id order is not commit order Ordering assumptions that fail under concurrency
Monitoring and cleanup jobs Lag and table growth must stay bounded Reconciliation scripts and manual data repair

The pattern saves engineering time by changing the type of failure you deal with. Without an outbox, inconsistency has no bound and stays invisible until a customer reports it. The incident is then hard to reproduce because the failed publish left no trace. With an outbox, every undelivered event is a row you can query and alert on. That gives you a bounded, observable pipeline instead of periodic diff jobs and one-off repair scripts.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to transactional outbox pattern, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.