Transactional Outbox Pattern: Reliable Events from PostgreSQL

What the dual write problem is and why the transactional outbox pattern fixes it

A service that updates PostgreSQL and then publishes to a message broker performs two writes against two systems that share no transaction. Neither order of operations is safe:

• Commit, then publish. If the process crashes, the broker is unavailable, or the publish times out after the commit, the database holds the change but no event is ever sent. Downstream services silently diverge.
• Publish, then commit. If the commit fails because of a constraint violation, deadlock or crash, consumers have already acted on a change that never happened.

Retries do not fix this. A crash can occur between any two steps, and no retry loop survives a dead process. This is the dual write problem: without a shared transaction, any interleaving can lose an event or emit a false one.

The transactional outbox pattern reduces the two writes to one. The service inserts the event as a row in an outbox table within the same PostgreSQL transaction as the business change. Both commit together or neither does. A separate relay reads committed outbox rows and publishes them to the broker. It does this either by polling the table or by tailing the write-ahead log through change data capture.

Service → one transaction → business tables + outbox table → relay (polling or CDC) → broker → consumers

The guarantee is: if the transaction commits, the event is eventually published at least once. A rolled-back transaction leaves no outbox row, so it produces no event. The relay can crash after publishing but before recording progress, which causes duplicates. Consumers must be designed to handle them.

Copy  +-----------+
  |  Service  |
  +-----+-----+
        |  one transaction: UPDATE business row + INSERT outbox row
        v
+--------------------------------------------------+
| PostgreSQL                                       |
|  +-----------------+     +-----------------+     |
|  | business tables |     |  outbox table   |     |
|  +-----------------+     +--------+--------+     |
|  commit together, or not at all   |              |
+-----------------------------------+--------------+
                                    | poll outbox rows or tail WAL (CDC)
                                    v
                           +-----------------+
                           |      Relay      |
                           | (polling / CDC) |
                           +--------+--------+
                                    | publish, then record progress
                                    v
                           +-----------------+
                           |     Broker      |
                           +--------+--------+
                       +------------+------------------+
                       v                               v
              +-----------------+             +-----------------+
              |   Consumer A    |             |   Consumer B    |
              |  (idempotent)   |             |  (idempotent)   |
              +-----------------+             +-----------------+

The service writes the business change and the outbox row in one PostgreSQL transaction, and a separate relay publishes committed rows to the broker at least once, so consumers must tolerate duplicates.

Who should use it and when to avoid it

The pattern fits PostgreSQL-backed services that change their own state and must tell other systems about it. Typical examples are an order service emitting OrderPlaced to billing and fulfilment, an accounts service publishing balance changes to a ledger, or any service feeding a search index or cache that must not drift from the source of truth. In each case a missing event causes silent inconsistency that someone has to find and repair later. The outbox costs you a table, a relay process and some write amplification, and that cost is justified when lost or phantom events would be expensive.

When to skip it

• No external consumers. If only the same service reads the data, a database trigger, a materialized view or a direct query is simpler and stays inside one transaction.
• Losing an event is acceptable. Analytics pings, "someone viewed your profile" notifications and cache-warming hints can be published directly after commit. The occasional loss is cheaper than running a relay.
• Already event-sourced. If the event store is the system of record, appended events are the state. Consumers can subscribe to that store directly, and a second copy in an outbox adds nothing.
• You need transactional side effects across services. The outbox cannot make a payment capture and an inventory reservation commit or fail together.

That last limit matters most. The outbox guarantees that an event is published if and only if your local transaction committed. It says nothing about what consumers do with the event. If a downstream step fails, you still need a saga or explicit compensating actions to undo or retry the business process.

Designing the outbox table and writing to it

The outbox is an ordinary table in the same database as your business data, so a single local transaction can write to both. A practical schema has these columns:

• id (UUID): gives every event a stable identity that consumers can deduplicate on.
• aggregate_type and aggregate_id: name the entity the event concerns, such as order and the order's primary key. Relays use them to route events to topics and to keep each aggregate's events in order.
• event_type: describes what happened, for example OrderPlaced.
• payload (JSONB): holds the event body.
• created_at (timestamptz, defaulting to now()).
• published_at (nullable): the relay fills it in after the broker accepts the event. A status column also works, but a timestamp lets you measure relay lag at no extra cost.

Add a partial index on created_at with the condition WHERE published_at IS NULL. The relay only reads unpublished rows. Because published rows drop out of the index, it stays small even as the table's history grows.

To write an event, the application opens one transaction, inserts the order, inserts the outbox row describing it, and commits. Either both rows exist or neither does.

Treat the payload as a public contract, because consumers you don't control will parse it. Put a schema version in every event, prefer additive changes, and build payloads explicitly instead of serializing ORM entities. Otherwise a column rename becomes a breaking change for consumers.

Expect extra write cost on every business transaction. Each one now writes an additional heap row, its index entries, and the WAL for both. Later it also pays for the published_at update, which PostgreSQL implements as a new tuple version, and for the eventual delete.

Copy-- gen_random_uuid() is built in from PostgreSQL 13 (earlier versions need pgcrypto).
CREATE TABLE outbox (
  id             uuid        PRIMARY KEY DEFAULT gen_random_uuid(),  -- consumers dedupe on this
  aggregate_type text        NOT NULL,                               -- e.g. 'order'; used for topic routing
  aggregate_id   text        NOT NULL,                               -- per-aggregate ordering key
  event_type     text        NOT NULL,                               -- e.g. 'OrderPlaced'
  payload        jsonb       NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  published_at   timestamptz                                         -- NULL until the broker accepts it
);

-- The relay reads only unpublished rows. Published rows leave this index, so it stays small.
CREATE INDEX outbox_unpublished_idx
  ON outbox (created_at)
  WHERE published_at IS NULL;

-- Write the business row and its event in one transaction: both commit or neither does.
-- Assumes an existing orders table with a generated id.
BEGIN;

WITH new_order AS (
  INSERT INTO orders (customer_id, total_cents, currency)
  VALUES ('cust-1042', 4999, 'EUR')
  RETURNING id, customer_id, total_cents, currency
)
INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
SELECT
  'order',
  id::text,
  'OrderPlaced',
  -- Build the payload field by field instead of dumping the row, so a column rename
  -- in orders cannot silently break consumers. schema_version marks the contract.
  jsonb_build_object(
    'schema_version', 1,
    'order_id',       id,
    'customer_id',    customer_id,
    'total_cents',    total_cents,
    'currency',       currency
  )
FROM new_order;

COMMIT;

Creates the outbox table and its partial index on unpublished rows, then inserts an order and its versioned OrderPlaced event in one transaction.

Building a polling relay with FOR UPDATE SKIP LOCKED

The relay is a loop that owns one short transaction per batch:

1. Begin a transaction.
2. Claim a batch. Select unpublished rows ordered by created_at, with a LIMIT and FOR UPDATE SKIP LOCKED. The LIMIT caps how long row locks are held. The ordering keeps output roughly in commit order.
3. Publish each row to the broker, using aggregate_id as the message key so all events for one aggregate land on the same partition. Wait for the broker's acknowledgement before moving on.
4. Mark the rows published or delete them. Deleting keeps the table small. Setting published_at keeps an audit trail but leaves dead tuples for vacuum to reclaim.
5. Commit.

SKIP LOCKED is what makes several relay instances safe. Without it, a second instance would block on rows the first had already locked, or both would read and send the same rows. With it, each instance skips locked rows and claims the next free ones. Instances divide the work without any coordination beyond PostgreSQL's row locks. This does not guarantee per-aggregate ordering across instances, which is covered later.

The polling interval sets the trade-off. A short interval lowers latency, but every poll is a query against the primary, even when the outbox is empty. A long interval saves load and makes delivery slower. A common approach is adaptive polling: run the next poll immediately when a batch comes back full, and back off when it comes back empty.

The duplicates come from the gap between steps 3 and 5. Suppose the broker has accepted the messages but the relay crashes before commit. The transaction rolls back, the rows are still unpublished, and the next relay sends them again. This is at-least-once delivery, and consumers must handle it.

Copyimport time
import psycopg2

BATCH_SIZE = 100
MIN_DELAY, MAX_DELAY = 0.05, 2.0  # seconds; tune against primary load

CLAIM_SQL = """
    SELECT id, aggregate_id, payload
    FROM outbox
    ORDER BY created_at
    LIMIT %s
    FOR UPDATE SKIP LOCKED  -- other relays skip these rows instead of blocking
"""

def run_relay(conn, publish):
    # publish(key, value) must block until the broker acknowledges the message.
    delay = MIN_DELAY
    while True:
        # psycopg2: 'with conn' commits on success and rolls back on exception.
        with conn:
            with conn.cursor() as cur:
                cur.execute(CLAIM_SQL, (BATCH_SIZE,))  # steps 1-2: begin + claim
                rows = cur.fetchall()
                for _id, aggregate_id, payload in rows:
                    publish(key=aggregate_id, value=payload)  # step 3
                # A crash here leaves the broker holding messages while the
                # transaction rolls back: the rows are resent (at-least-once).
                if rows:
                    cur.execute("DELETE FROM outbox WHERE id = ANY(%s)",
                                ([r[0] for r in rows],))  # step 4
        # step 5: commit happened on leaving 'with conn'

        # Adaptive polling: drain immediately when full, back off when empty.
        if len(rows) == BATCH_SIZE:
            delay = 0
        elif not rows:
            delay = min(max(delay * 2, MIN_DELAY), MAX_DELAY)
        else:
            delay = MIN_DELAY
        time.sleep(delay)

# Usage: run_relay(psycopg2.connect("dbname=app user=relay host=db.internal"), my_publish)

A psycopg2 relay loop that claims a batch with FOR UPDATE SKIP LOCKED, publishes each row keyed on aggregate_id, deletes the rows, and commits, with adaptive polling and a comment marking where a crash after publish causes duplicates.

Log-based relaying with logical replication and Debezium outbox routing

Log-based relaying keeps that at-least-once guarantee but removes the polling query. Instead of asking the outbox table for new rows, a change data capture (CDC) connector reads the write-ahead log (WAL). PostgreSQL's logical decoding turns WAL records into row-level changes. A replication slot records how far the consumer has read, so the server keeps every WAL segment the consumer has not yet confirmed. Committed outbox inserts reach the relay in commit order, usually within milliseconds, and the primary does no extra table scans.

Debezium streams those inserts, and its outbox event router transformation reshapes each one. The router reads the aggregate type column to pick the destination topic, so Order rows go to an order topic. It uses the aggregate id as the message key, which places all events for one aggregate on the same partition. The payload column becomes the message body. Because the relay only needs inserts, the application can delete outbox rows soon after writing them.

The cost is operational. You run and upgrade the connector, and you own how schema changes to the outbox table are handled. The main risk is the slot. If the connector stops or stalls, PostgreSQL keeps WAL for it until the primary's disk fills. Monitor pg_replication_slots: check the active flag, and compute retained bytes with pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn). Alert on growth.

Copy-- Run on the primary: pg_current_wal_lsn() raises an error during recovery (on a standby).
-- Lists every logical slot, whether a consumer is attached, and how much WAL
-- the server is holding back because of it.
SELECT
  slot_name,
  plugin,                     -- output plugin, e.g. pgoutput
  active,                     -- false = no connector currently attached
  active_pid,                 -- backend serving the connector, NULL if inactive
  restart_lsn,                -- oldest WAL the slot still needs
  confirmed_flush_lsn,        -- last position the consumer acknowledged
  -- Bytes of WAL retained for this slot; this is the number to alert on
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
  ) AS retained_wal,
  -- Consumer lag behind the current WAL position
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
  ) AS confirmed_lag,
  wal_status                  -- PostgreSQL 13+: reserved / extended / unreserved / lost
FROM pg_replication_slots
WHERE slot_type = 'logical'
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- Alerting query: slots with no consumer attached, or retaining more than 10 GB.
-- Tune the threshold to your free disk space and WAL write rate.
SELECT slot_name, active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots
WHERE NOT active
   OR pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 10 * 1024^3;

-- PostgreSQL 13+: cap on WAL a slot may retain (-1 means unlimited).
-- If a slot exceeds it, the slot is invalidated and the connector must re-snapshot.
SHOW max_slot_wal_keep_size;

Queries against pg_replication_slots that show whether each logical slot has a connector attached and how much WAL it forces the primary to keep, plus an alerting filter for inactive or oversized slots.

Delivery semantics, idempotent consumers and per-aggregate ordering

Neither relay can promise exactly-once delivery. A polling relay can publish a batch and crash before it marks the rows as sent. A log-based connector can restart from its last committed offset and replay events that the broker already accepted. Both failures produce duplicates, and no relay design removes them, because publishing to the broker and recording progress are still two separate writes. The realistic guarantee is at-least-once. A consumer that is not idempotent does not fix the dual write problem. It moves the problem one hop downstream.

Making consumers idempotent

The general technique is a processed_messages table keyed by event id. The consumer runs INSERT ... ON CONFLICT DO NOTHING for the event id in the same database transaction as its own business write. If the insert affects zero rows, the event was already handled and the transaction does nothing else. This only works when both writes commit together. Recording the id in a separate transaction recreates the gap you were trying to close.

When the effect is a state assignment, such as "order 42 is now SHIPPED", a natural upsert keyed on the entity is idempotent without any extra table.

Ordering

Use the aggregate id as the message key. This keeps all events for one aggregate in a single partition, and the broker preserves order within that partition. Global ordering across aggregates would require a single partition and a single consumer. Business invariants rarely span aggregates, so it is seldom worth that cost.

Per-aggregate order still breaks in several ways:

• Producers key the same aggregate inconsistently.
• Parallel relays publish overlapping rows.
• A retry lands after a newer event.
• Repartitioning changes the key-to-partition mapping.

Including a per-aggregate version in each event lets consumers detect and discard stale events.

Operating the outbox: housekeeping, lag monitoring and poison messages

Housekeeping

Published rows have no further use, so remove them on a schedule. Delete in small batches, for example a few thousand rows per transaction keyed on the primary key. One large DELETE holds locks longer, produces a burst of WAL, and leaves a large number of dead tuples for vacuum at once. At high volume, partitioning by created_at (daily or hourly) is usually better: dropping an old partition is a metadata operation and leaves nothing to vacuum.

The outbox gets a constant stream of inserts, updates and deletes, so the default autovacuum thresholds, which scale with table size, react too slowly. Lower autovacuum_vacuum_scale_factor on the outbox table so vacuum runs after a fixed number of changes. The partial index on published_at IS NULL should stay close to the size of the backlog. If it keeps growing while the backlog stays flat, dead entries are not being reclaimed.

Lag monitoring

Outbox lag is the age of the oldest unpublished row: SELECT now() - min(created_at) FROM outbox WHERE published_at IS NULL;. The partial index makes this query cheap. Alert when lag keeps growing over a time window, not on a single spike. For log-based relays, also alert on WAL retained by the slot, measured with pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) from pg_replication_slots. A stalled consumer causes the slot to retain WAL until the disk fills.

Poison messages

Add an attempts counter and a last_error column. After N failures, set the row's status to parked and stop retrying it. Do not skip a parked row and keep publishing later events for the same aggregate, because that breaks per-aggregate ordering. Have the relay exclude every aggregate that has a parked row, while it keeps publishing for all other aggregates. Once an operator fixes the parked row or discards it, that aggregate's queue resumes in order.

Alternatives and the impact on cost and engineering time

The outbox is not free. You build the table, the relay, deduplication in every consumer, and lag and poison-message alerts before you ship a single feature event. The alternatives defer that cost. Without an outbox, you pay later through unplanned work: tracing why a downstream system never saw an order, writing one-off reconciliation scripts, and patching rows by hand while support waits.

Illustrative estimate (assumed numbers, not a benchmark). Assume the outbox build takes 80 engineer-hours and ongoing outbox upkeep takes 2 hours/month. Without it, assume 1 event-loss incident per month at 6 hours each, plus 4 hours/month maintaining reconciliation jobs.

Payback months = build hours ÷ (avoided hours/month − outbox upkeep/month) = 80 ÷ ((1 × 6 + 4) − 2) = 10 months.

Plug in your own incident rate. Investigation time usually dominates the result.

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.
• Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

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.
• Consumers learn about a breaking change at review time rather than from production errors.
• 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.