The Transactional Outbox Pattern: Reliable Events from Your Database

The dual write problem: why database-then-broker writes fail

A service that handles "place order" usually has two jobs: commit the order row and publish an OrderPlaced event so other services can react. The database and the broker are separate systems with separate commit protocols. Without a distributed transaction such as two-phase commit, which most brokers either don't support or support at a cost teams avoid, nothing makes the two writes succeed or fail together. That gap is the dual write problem.

The failure modes depend on which write happens first and where the process dies:

Retries and try/catch blocks don't close the gap because they run in the same process that can die. A catch block can't run after SIGKILL, an out-of-memory kill, or a node loss. An in-memory retry queue disappears with the process. A timeout is also ambiguous: the client can't tell whether the broker received the message, so retrying risks a duplicate and not retrying risks a loss. The fix has to make the event part of the same atomic commit as the business data. The transactional outbox pattern does exactly that.

How does the transactional outbox pattern work?

The transactional outbox pattern sidesteps the broker during the request entirely. Instead of publishing directly, the service writes the event as a row in an outbox table in the same database that holds its business data, inside the same local transaction. The database's atomic commit becomes the only commit that matters. If the transaction commits, both the state change and the event exist. If it rolls back, neither does. There is no window where one is visible without the other.

Delivery to the broker becomes a separate, asynchronous job. The end-to-end flow looks like this:

1. Service: handles the request and opens a transaction.
2. Database: within that transaction, the service inserts or updates the business row, for example orders, and inserts a row into outbox describing the event, for example OrderCreated. Then it commits.
3. Relay: a separate process reads committed outbox rows and publishes each one to the broker. It marks a row as sent only after the broker acknowledges it.
4. Broker: stores the event on a topic or queue.
5. Consumers: subscribe and react, independently of the original request.

The SQL example that follows shows step 2: one BEGIN/COMMIT block containing both the orders insert and the outbox insert.

The guarantee holds only because both writes land in one service's own database. ACID is used where it works, inside the service boundary, rather than stretched across the database and broker with a distributed transaction. The relay can crash, lag or retry without ever losing a committed event, because the event is durable data.

Copy-- PostgreSQL: write the business row and its event in ONE local transaction.
-- Either both rows commit or neither does; the broker is not involved here.

BEGIN;

-- 1. The state change itself.
INSERT INTO orders (id, customer_id, total_cents, status, created_at)
VALUES (
  '7f3c2a9e-4b1d-4e8a-9c6f-2d5e8b1a0c47',
  'cust_1042',
  4999,
  'PLACED',
  now()
);

-- 2. The event describing that change, stored as plain data in the same database.
--    The relay reads rows where published_at IS NULL, publishes them,
--    and sets published_at only after the broker acknowledges.
INSERT INTO outbox (id, aggregate_type, aggregate_id, event_type, payload, created_at, published_at)
VALUES (
  gen_random_uuid(),
  'Order',
  '7f3c2a9e-4b1d-4e8a-9c6f-2d5e8b1a0c47',
  'OrderCreated',
  jsonb_build_object(
    'orderId',    '7f3c2a9e-4b1d-4e8a-9c6f-2d5e8b1a0c47',
    'customerId', 'cust_1042',
    'totalCents', 4999
  ),
  now(),
  NULL  -- not yet delivered
);

-- 3. A single atomic commit makes the order and its event visible together.
--    A failure before this line rolls back both inserts.
COMMIT;

A single PostgreSQL transaction inserts an order row and its OrderCreated outbox row, so the event exists only if the order commits and is left unpublished for the relay to deliver.

Designing the outbox table and event payload

A minimal outbox table needs seven columns, and each one exists to serve either the relay or a consumer.

Columns

• event_id is a UUID generated by the application and set as the primary key. It goes into the published message unchanged. Consumers use it to discard duplicates, so it must never be regenerated on retry.
• aggregate_type and aggregate_id identify the entity that changed, such as order and 8412. The relay uses them as the broker partition key. All events for one aggregate then land in the same partition and keep their order.
• event_type names the business fact, such as OrderPlaced. Consumers route on it without parsing the body.
• payload holds the event body as JSON or binary, together with a schema version.
• created_at records commit-time order and lets you measure relay lag.
• processed_at is a nullable timestamp that the relay sets after the broker acknowledges the message.

Marking rows versus deleting them

Marking rows as processed keeps a short audit trail and makes replay simple. The cost is that the table grows, so you need a cleanup job. Deleting each row on publish keeps the table small, but it gives up that history and causes frequent row churn.

Indexing

If you mark rows, create a partial index on created_at filtered to processed_at IS NULL. The relay's "next unpublished batch" query then scans only pending rows. If you delete rows, an index on created_at alone is enough.

Payload as a contract

Do not serialize the ORM entity into payload. If you do, every column rename or lazy-loaded relation becomes a breaking change for consumers you don't control. Define an explicit event schema instead and evolve it the way you would evolve a public API:

• Add only optional fields.
• Never change the meaning of an existing field.
• Put a version number on every event.
• When a breaking change can't be avoided, publish a new event type and run it alongside the old one until consumers have migrated.

Copy-- PostgreSQL. The application inserts into this table in the same
-- transaction as the business change it describes.
CREATE TABLE outbox (
    -- Generated by the application, never regenerated on retry;
    -- consumers deduplicate on this value.
    event_id       uuid        PRIMARY KEY,
    -- Together these form the broker partition key, so all events
    -- for one aggregate keep their relative order.
    aggregate_type text        NOT NULL,          -- e.g. 'order'
    aggregate_id   text        NOT NULL,          -- e.g. '8412'
    -- Consumers route on this without parsing the body.
    event_type     text        NOT NULL,          -- e.g. 'OrderPlaced'
    -- An explicit, versioned event contract, not a serialized ORM entity,
    -- e.g. {"schema_version": 2, "order_id": "8412", "total_cents": 4999}
    payload        jsonb       NOT NULL,
    -- Order of insertion; also used to measure relay lag.
    created_at     timestamptz NOT NULL DEFAULT now(),
    -- NULL until the broker acknowledges the message.
    processed_at   timestamptz
);

-- Partial index: the relay scans only pending rows, so the index
-- stays small no matter how many processed rows are retained.
CREATE INDEX outbox_pending_idx
    ON outbox (created_at)
    WHERE processed_at IS NULL;

-- Relay: claim the next batch. SKIP LOCKED lets several relay
-- instances run without blocking each other on the same rows.
SELECT event_id, aggregate_type, aggregate_id, event_type, payload
FROM outbox
WHERE processed_at IS NULL
ORDER BY created_at
LIMIT 100
FOR UPDATE SKIP LOCKED;

-- After the broker acks, mark the published rows (IDs from the batch).
UPDATE outbox SET processed_at = now()
WHERE event_id = ANY($1::uuid[]);

A PostgreSQL outbox table with a partial index on pending rows, plus the relay's queries to claim a batch and mark it processed.

Implementing the relay: polling publisher vs transaction log tailing

The relay moves committed outbox rows to the broker. There are two ways to build it.

Polling publisher

The relay runs a loop. It selects unpublished rows in insertion order, publishes them in a batch, and then marks or deletes them. A typical claim-and-publish query uses SELECT id, payload FROM outbox WHERE published_at IS NULL ORDER BY id LIMIT 100 FOR UPDATE SKIP LOCKED. The relay publishes the batch and waits for broker acknowledgements. It then sets published_at and commits.

The poll interval sets a latency floor. An event committed just after a poll waits a full interval. Shortening the interval reduces that wait but adds load, because every poll queries the primary even when the outbox is empty.

You can run several relays safely in two ways:

• SKIP LOCKED. Each instance claims rows that no other instance has locked, so instances don't block each other. PostgreSQL and MySQL 8.0+ support it. The trade-off is that batches can complete out of order across instances.
• Leader election. Run a single active relay. This keeps ordering simple and leaves the other instances as standbys.

Transaction log tailing

A change data capture (CDC) reader streams committed outbox inserts from the write-ahead log or binlog. This gives lower latency and no polling queries. The costs are:

• Extra infrastructure to deploy and run.
• Retention risk. A stalled reader stops the database from discarding old log segments, so the log can fill the disk.
• Coupling to database internals. Upgrades and failovers can break the reader.

Copy-- PostgreSQL outbox table. The partial index keeps each poll cheap:
-- it covers only unpublished rows, so the index stays small.
CREATE TABLE outbox (
  id           bigserial   PRIMARY KEY,
  aggregate_id text        NOT NULL,
  event_type   text        NOT NULL,
  payload      jsonb       NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);

CREATE INDEX outbox_unpublished_idx
  ON outbox (id)
  WHERE published_at IS NULL;

-- One relay iteration. Every relay instance runs the same steps.
BEGIN;

-- Claim up to 100 rows in insertion order. SKIP LOCKED makes this
-- instance pass over rows another instance has already locked,
-- so concurrent relays take separate batches without blocking.
SELECT id, event_type, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 100
FOR UPDATE SKIP LOCKED;

-- The application publishes these rows to the broker here and waits
-- for acknowledgements. If publishing fails, it runs ROLLBACK instead,
-- which releases the locks so the rows are retried on a later poll.

-- Mark only the acknowledged rows. $1 is a bigint[] of the claimed ids,
-- bound by the database driver.
UPDATE outbox
SET published_at = now()
WHERE id = ANY($1);

COMMIT;

A PostgreSQL polling-relay iteration that claims a batch of unpublished outbox rows with FOR UPDATE SKIP LOCKED, so several relay instances can run at once, and marks the rows published only after the broker acknowledges them.

Delivery guarantees: at-least-once, idempotent consumers and per-key ordering

Either relay design can publish a message and then crash before it marks the row as sent. On restart it sends the row again. Retrying the broker call after a timeout has the same effect, because the first attempt may have succeeded. Delivery is therefore at-least-once. Consumers must handle duplicates from the first release, not after the first incident. The outbox makes the producer side reliable, and that moves the remaining work into consumer idempotency.

Idempotent consumers

Each consumer keeps a processed_events table keyed by event ID with a unique constraint. It inserts the event ID in the same local transaction as its state change. If the insert hits the constraint, the event was already applied, so the consumer rolls back and acknowledges the message. Some operations are idempotent on their own and can skip the table: setting a field to an absolute value, an upsert keyed by entity ID, or a delete by ID. Incrementing a counter or appending a ledger entry is not idempotent.

Ordering

The pattern guarantees order within one aggregate. It does not guarantee global order. Use the aggregate ID as the message key so all events for that aggregate go to the same partition. The relay must also never publish events for the same key in parallel.

Polling with a "max ID seen" cursor has a less obvious failure. Sequence values are assigned at insert time, but rows become visible at commit time. Suppose transaction A takes ID 100 and transaction B takes ID 101, and B commits first. The poller reads 101 and moves its cursor past 100. When A commits, row 100 is never read. To avoid this, select rows where published_at IS NULL instead of tracking a cursor, or use log tailing, which reads changes in commit order.

Copy-- Consumer-side dedup table (PostgreSQL). The primary key is the guard.
CREATE TABLE processed_events (
  event_id     uuid PRIMARY KEY,
  processed_at timestamptz NOT NULL DEFAULT now()
);

-- Applying one 'PaymentCaptured' event. The ledger append is NOT
-- idempotent, so it may run only if this event ID has not been seen.
BEGIN;

WITH claimed AS (
  -- Record the event ID in the same transaction as the state change.
  -- On a redelivery the conflict yields zero rows instead of an error.
  INSERT INTO processed_events (event_id)
  VALUES ('3f1c2b9e-8a47-4d2e-9b6a-0c5e7d1f4a21')
  ON CONFLICT (event_id) DO NOTHING
  RETURNING event_id
)
-- The append happens only when 'claimed' returned a row, i.e. first delivery.
INSERT INTO account_ledger (account_id, event_id, amount_cents)
SELECT 'acct_1042', event_id, 2599
FROM claimed;

COMMIT;
-- Both inserts commit together or not at all. Acknowledge the message
-- after COMMIT; if zero rows were inserted, it was a duplicate, so ack anyway.

-- Contrast: an absolute upsert keyed by entity ID is idempotent by itself
-- and needs no processed_events row. Replaying it leaves the same result.
INSERT INTO order_status (order_id, status, updated_at)
VALUES ('ord_7781', 'paid', now())
ON CONFLICT (order_id)
DO UPDATE SET status = EXCLUDED.status, updated_at = EXCLUDED.updated_at;

A PostgreSQL consumer records the event ID and appends the ledger entry in one transaction so a redelivered event has no effect, and an absolute upsert is shown for comparison because it is idempotent without the dedup table.

Operating the outbox: monitoring, cleanup and an illustrative sizing estimate

Alerting on lag and backlog

A broken relay does not fail requests. Transactions keep committing and rows keep piling up, so nothing looks wrong until a downstream team notices missing events. Alert on two metrics: outbox lag, the age of the oldest unpublished row, and backlog size, the count of unpublished rows. Lag is the better signal because it catches a stalled relay even when traffic is low.

Cleanup, poison messages and outages

Delete published rows in small batches, or partition the table by time and drop old partitions. Dropping a partition avoids the dead-tuple and index bloat that large DELETEs leave behind. For a message that fails on every attempt, track an attempt count and move the row to a dead-letter table after N tries. If you need per-key ordering, block that key rather than skip the row, because skipping it publishes later events for the key out of order. During a long broker outage, keep accepting writes and let the backlog grow. Decide ahead of time how much disk you are willing to spend on that backlog.

Illustrative estimate (assumed numbers, not a benchmark)

Assume 200 writes/s, a 500 ms poll interval, a batch size of 500, 1 KB rows and a 50 ms publish time per batch.

• Relay capacity = batch ÷ interval = 500 ÷ 0.5 s = 1,000 rows/s, which is 5× the write rate.
• Worst-case latency ≈ interval + publish time = 0.5 s + 0.05 s ≈ 550 ms.
• Growth without cleanup = rate × 86,400 s × row size = 200 × 86,400 × 1 KB ≈ 17 GB/day, plus indexes, with no upper bound.
• With 1-hour retention, the table holds 200 × 3,600 = 720,000 rows, about 0.7 GB at steady state.
• Drain time after a 1-hour outage = backlog ÷ (capacity − write rate) = 720,000 ÷ 800 ≈ 15 minutes.

Who should use the transactional outbox pattern

The pattern is aimed at teams that meet all of these conditions:

• The service owns a transactional database. The outbox depends on a local ACID transaction covering both the business table and the outbox table. A relational database such as PostgreSQL or MySQL fits naturally. A store without multi-row or multi-table transactions in the relevant scope cannot provide the guarantee.
• Other services act on its events. Billing, fulfilment, notifications, search indexing or analytics pipelines consume what the service publishes, and the producing team does not control those consumers.
• Correctness of events matters more than sub-millisecond delivery. The relay adds a delay between commit and publish, ranging from near real time with log tailing to at least one poll interval with polling.
• The team can own another running component. Someone has to deploy the relay, watch its lag and respond when it stalls.

In practice this usually means backend teams running services in an event-driven or microservice architecture, where each service has its own database and communicates through a broker. Platform teams are also natural owners: they can build the relay and the outbox table conventions once and offer them as a shared library or service, so product teams only write the extra INSERT inside their existing transactions. Consumer teams are users too, because they must build idempotent handlers on the assumption of at-least-once delivery.

Teams that run a single monolith with no external event consumers, or whose events are purely advisory, usually don't need it. The next section covers those cases in more detail.

When to use it, when to avoid it, and the alternatives

Adopt the outbox when a service owns its database and other systems depend on its events: order placed, payment captured, account closed. If a lost or phantom event would cause a customer-visible error or a manual reconciliation, the pattern earns its place. It is unnecessary when events are advisory, such as cache invalidation backed by a TTL, or when consumers can periodically re-read state.

It is the wrong fit in three cases. With event sourcing, the event store is already the source of truth, so there is no second write to protect. With listen-to-yourself designs, the service publishes first and updates its own state from its own event, which trades read-your-writes consistency for one write path. When the database and broker share a transactional resource, a single commit already covers both.

The outbox only makes events from one database's writes reliable. Atomic side effects across services still need sagas or compensating actions.

The costs are real. Every business write also writes to the outbox and later deletes from it. The relay becomes a critical component that needs on-call ownership. Payloads become public contracts that you must version. The return is time you no longer spend tracing events that vanished, or events that announced rollbacks as if they had committed.

How the outbox saves engineering time

The time savings come from removing a category of work, not from making any single request faster. Without the pattern, teams tend to pay for the dual write problem in several recurring ways, and the outbox removes or shrinks each of them.

• Fewer inconsistency investigations. A lost or phantom event typically shows up days later as a downstream mismatch: an order with no invoice, or a shipment for an order that was rolled back. Tracing that back through logs from two systems is slow, and the root cause is often a crash that left no trace. With an outbox, every committed change has a durable event row, so a missing event becomes a question with a direct answer: is the row still unpublished, or did the consumer fail?
• No hand-written reconciliation jobs. Teams that live with dual writes often build scripts that compare database state against what consumers received and patch the gaps. The outbox makes the producer side consistent by construction, so these scripts are no longer needed for that purpose.
• One delivery mechanism instead of per-feature retry code. Without the pattern, each code path that publishes events tends to grow its own retry loops, timeout handling and fallback queues. With an outbox, application code does one INSERT in the transaction it already has, and retries, batching and backoff live in the relay, written and tested once.
• Replay without special tooling. If published rows are retained for a period, re-sending events after a consumer bug is a matter of resetting processed_at (or published_at) to NULL for the affected range. The relay delivers them again, and idempotent consumers absorb the duplicates.
• Simpler tests. Producer tests can assert that the expected outbox row exists after a transaction, using only the database. They don't need a running broker or a mock that simulates partial failures, because the failure modes the outbox protects against are handled by the database's commit.
• Clearer operational signals. Outbox lag and backlog size, described above, give one place to check when event delivery is slow. That replaces guessing at whether the producer, the network or the broker dropped a message.

The cost side covered earlier still applies: someone builds and runs the relay, cleans up the table and versions the payloads. The trade is predictable, scheduled work in place of unpredictable incident and reconciliation work. How much time that recovers depends on how often dual write failures were happening and how expensive each one was to diagnose, which each team has to judge from its own incident history.

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.