Transactional Outbox Pattern: Publish Events Reliably from a Database

The transactional outbox pattern stops a service from losing events between its database and its message broker. Instead of updating the database and then sending a message as two separate operations, the service writes the message to an outbox table in the same database transaction as the business change. A relay process then reads the table and publishes the messages. If the transaction rolls back, no message exists; if it commits, the message is guaranteed to be sent eventually.

Who this is for

You should read this if a service of yours saves data and then publishes an event, and you have seen, or fear, a state where the order is saved but the OrderCreated event never left. By the end you will have an outbox table, code that writes to it, a polling relay for PostgreSQL that publishes to Azure Service Bus, and consumers that tolerate duplicates.

The dual-write problem

Consider this code path:

1. BEGIN; INSERT INTO orders ...; COMMIT;
2. broker.send(OrderCreated)

If the process crashes between step 1 and step 2, the order exists and the event does not. Reversing the order is no better: send first, then the database commit fails, and subscribers hear about an order that doesn't exist. Two-phase commit across a database and a broker is not an option for most brokers.

The pattern is described on microservices.io as the answer to the question "how can a service atomically update its database and send messages to a message broker, without using two-phase commit?" The service stores outgoing messages in an outbox as part of the same database transaction that updates its business data. A separate message relay reads the outbox and publishes the messages. In a relational database the outbox is a table; in a NoSQL database it can be a field on each record.

According to that description, the benefits are that two-phase commit is avoided, messages are sent only if the database transaction commits, and messages are sent in the order the application created them. The drawbacks are that developers can forget to write the outbox row after a change, and that the relay may publish a message more than once.

Design overview

  Service                         PostgreSQL                         Relay                    Service Bus
  --------                        ----------                         -----                    -----------
  handle command
    BEGIN
      INSERT orders  ------------> orders
      INSERT outbox  ------------> outbox (published_at = NULL)
    COMMIT
                                    outbox <---- poll unsent rows ---- claim rows (SKIP LOCKED)
                                                                       publish with MessageId = outbox.id ---> topic / queue
                                    outbox <---- set published_at ---- mark as published

Two relay strategies are named in the pattern description: a polling publisher, which periodically polls the outbox for unsent messages, and transaction log tailing, which reads the database's transaction log to capture outbox changes. This article implements polling, because it needs nothing beyond SQL and a worker.

Prerequisites

• A PostgreSQL database that the service owns.
• An Azure Service Bus namespace with a queue or topic to publish to.
• A worker process that can run continuously, such as a container, a hosted background service or a scheduled job with a short interval.

Step 1: Create the outbox table

Keep the table narrow and append-only. A sketch for PostgreSQL:

CREATE TABLE outbox (
    id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_id text        NOT NULL,
    event_type   text        NOT NULL,
    payload      jsonb       NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now(),
    published_at timestamptz
);
 
-- Fast lookup of unsent rows, oldest first
CREATE INDEX outbox_unsent_idx ON outbox (created_at) WHERE published_at IS NULL;

The partial index keeps polling cheap as the table grows, because it only covers rows that haven't been published.

Step 2: Write the business change and the event together

The business row and the outbox row must be inserted in one transaction.

BEGIN;
 
INSERT INTO orders (order_id, customer_id, total)
VALUES ('1001', 'C-42', 199.00);
 
INSERT INTO outbox (aggregate_id, event_type, payload)
VALUES ('1001', 'OrderCreated',
        '{"orderId":"1001","customerId":"C-42","total":199.00}');
 
COMMIT;

In application code, use your data access layer's transaction scope so both inserts share it. The weakness the pattern's authors point out is that developers can forget the second insert. Reduce that risk by routing all changes to an aggregate through one method that always writes both, and by adding a test that fails if a command handler commits without an outbox row.

Step 3: Build the relay

The relay repeatedly claims a batch of unsent rows, publishes them, and marks them as published. Multiple relay instances improve availability, but they must not publish the same rows at the same time. PostgreSQL's SELECT supports FOR UPDATE SKIP LOCKED for this. The documentation explains that with SKIP LOCKED, rows that can't be locked immediately are skipped; this gives an inconsistent view of the data, so it is not suitable for general purpose work, but it can be used to avoid lock contention with multiple consumers accessing a queue-like table. An outbox is exactly that kind of table.

BEGIN;
 
SELECT id, aggregate_id, event_type, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY created_at
LIMIT 50
FOR UPDATE SKIP LOCKED;
 
-- publish each row to Service Bus here, then:
UPDATE outbox SET published_at = now() WHERE id = ANY(@publishedIds);
 
COMMIT;

Publishing inside the open transaction keeps the claimed rows locked while the relay works. Keep batches small so locks are short. The matching C# publisher uses the Azure.Messaging.ServiceBus client with passwordless authentication:

using Azure.Identity;
using Azure.Messaging.ServiceBus;
 
await using var client = new ServiceBusClient(
    "sb-contoso.servicebus.windows.net", new DefaultAzureCredential());
ServiceBusSender sender = client.CreateSender("orders-events");
 
foreach (var row in claimedRows)
{
    var message = new ServiceBusMessage(BinaryData.FromString(row.PayloadJson))
    {
        MessageId = row.Id.ToString(),   // stable: same row, same MessageId
        Subject = row.EventType,
        ContentType = "application/json"
    };
    await sender.SendMessageAsync(message);
}

Using the outbox row ID as the MessageId is the key detail. If the relay crashes after sending but before marking the row as published, the next attempt sends the same MessageId again.

Optional: let Service Bus drop the duplicates

Service Bus can enable duplicate detection on a queue or topic. It tracks the application-controlled MessageId of messages sent within a time window; a message with an ID already seen in that window is reported as accepted but dropped. The window defaults to 10 minutes, with a minimum of 20 seconds and a maximum of 7 days. The Basic tier does not support duplicate detection; Standard and Premium do. A larger window affects throughput, so keep it as short as your relay's worst-case retry delay allows.

This narrows the duplicate problem on the publishing side only. It does not remove the need for idempotent consumers.

Step 4: Make consumers idempotent

Because the relay can publish a message more than once, the outbox gives at-least-once delivery. The pattern description says consumers must therefore be idempotent, for example by tracking processed message IDs, and notes that this is usually already required since brokers can deliver duplicates. A common implementation is an inbox table keyed by message ID:

CREATE TABLE processed_messages (
    message_id   text        PRIMARY KEY,
    processed_at timestamptz NOT NULL DEFAULT now()
);
 
-- In the consumer's transaction:
INSERT INTO processed_messages (message_id) VALUES (@messageId)
ON CONFLICT (message_id) DO NOTHING;
-- if zero rows were inserted, the message is a duplicate: skip the work and complete the message

Run the insert and the business update in the same transaction, so a crash cannot record the ID without doing the work or the reverse. See Webhooks that don't double-charge for a longer treatment of deduplication keys and dead letters.

Step 5: Clean up and monitor

An outbox grows forever unless you prune it. Delete rows once they have been published for longer than your replay window:

DELETE FROM outbox
WHERE published_at IS NOT NULL
  AND published_at < now() - interval '7 days';

Choose the retention based on how long you want to be able to re-publish events for debugging or recovery, and delete in batches for large tables. Track these metrics:

• Outbox lag: the age of the oldest row where published_at IS NULL.
• Backlog size: count of unsent rows.
• Relay errors: failed sends and retries.
• Dead-lettered messages on the consuming side.

An alert on outbox lag is the earliest sign that the relay is stuck.

Polling or log tailing

Both are listed on microservices.io as relay implementations. The tradeoffs in the table are practical guidance rather than quotes; measure before deciding.

Troubleshooting

Events arrive twice. Expected with an outbox. Check that consumers deduplicate on MessageId, and that the relay uses the outbox row ID rather than a new GUID for each attempt.

Events are delayed. Check the poll interval, batch size and the outbox lag metric. If the relay processes one row per round trip, increase the batch size.

Two relay instances publish the same row. Confirm the claim query uses FOR UPDATE SKIP LOCKED inside the same transaction as the update, and that the transaction stays open while publishing.

The outbox table keeps growing. Add the retention job and verify published_at is being set. A partial index on unsent rows keeps polling fast even when the table is large.

An event exists for a business change that rolled back. The outbox insert must be in the same transaction as the business change. Check that both statements use the same connection and transaction object.

Checklist

• Outbox table with a partial index on unsent rows.
• Business change and outbox insert in one transaction.
• Relay claims rows with FOR UPDATE SKIP LOCKED and publishes with a stable MessageId.
• Consumers are idempotent and track processed IDs.
• Retention job removes old published rows.
• Alert on outbox lag.

If you are choosing a broker for the events, see Kafka vs RabbitMQ vs AWS SQS. Sagas that react to these events are covered in Saga Pattern for Distributed Transactions.

References

• microservices.io, Pattern: Transactional outbox: https://microservices.io/patterns/data/transactional-outbox.html
• PostgreSQL documentation, SELECT (locking clause, NOWAIT and SKIP LOCKED): https://www.postgresql.org/docs/current/sql-select.html
• Microsoft Learn, Service Bus duplicate detection: https://learn.microsoft.com/en-us/azure/service-bus-messaging/duplicate-detection
• Microsoft Learn, Service Bus queues, topics and subscriptions: https://learn.microsoft.com/en-us/azure/service-bus-messaging/service-bus-queues-topics-subscriptions