The Transaction Outbox Pattern: Reliably Sending Messages from Database Transactions

Sending messages from database transactions is a common requirement in distributed systems, but it can be challenging to do so in a reliable and scalable way.
The Transaction Outbox Pattern is a design pattern that addresses this challenge by using a transactional outbox to store messages that need to be sent. In this article, we will explore the Transaction Outbox Pattern, its advantages, limitations, and how it works in practice.

What is the Transaction Outbox Pattern?

The Transaction Outbox Pattern is a design pattern that allows services to atomically update the database and send messages/events without using 2-phase commit (2PC). Instead, it relies on a transactional outbox table that stores messages to be sent by the service.

How Does the Transaction Outbox Pattern Work?

The Transaction Outbox Pattern works by inserting messages into the outbox table as part of the database transaction. After the transaction commits, a separate process reads the outbox table and sends the messages to the message broker. This separate process is often called the Outbox Processor.

Outbox Processor

To implement the Transaction Outbox Pattern, there are two main options for how to implement the Outbox Processor: using Write-Ahead Logging (WAL) or using polling.

WAL-based Processor:

The transaction log tailing pattern involves reading the transaction log of a database in real-time and publishing any changes to a message broker. This pattern is useful when you want to capture all changes to the database, including updates, inserts, and deletes.

One of the benefits of this pattern is that it provides real-time updates, which can be useful for applications that require up-to-date data. Additionally, it can be relatively simple to implement, as most databases provide transaction logs that can be read in real-time.

However, one of the downsides of this pattern is that it can be resource-intensive, especially if the database is frequently updated.
Additionally, if the message broker goes down, messages may be lost, as there is no way to replay the transaction log.
Finally, implementing a WAL-based processor can be complex and requires low-level knowledge of the database’s internal architecture. It also requires that the database supports WAL and that the processor has access to the WAL files.

Polling-based Processor:

The polling publisher pattern involves periodically polling a database for changes and publishing any changes to a message broker. This pattern is useful when you only need to capture changes at a certain frequency, and when the database does not provide a transaction log.

This approach is simpler to implement than the WAL-based processor and doesn’t require any low-level knowledge of the database’s internals. It’s also more flexible since it doesn’t require access to the database’s WAL files. Additionally, if the message broker goes down, messages can be replayed from the database.

However, the polling-based processor can have higher latency since it only checks for new messages at regular intervals. Additionally, it can be less efficient since it reads the entire outbox table at each poll, even if there are no new messages to send.

Ensuring Exactly-Once Message Delivery

The Outbox Processor may ensure that messages are sent to the message broker exactly once. It does this by using an atomic update to mark messages as processed once they have been sent. This prevents messages from being sent multiple times in case of failures or restarts.
However, this requires more resources. Therefore, in most cases, it is better to support message deduplication at the client level.

Preserving Message Ordering

The Outbox Processor may also ensure that messages are sent in the order they were added to the outbox table. It can do this by ordering messages based on their timestamp or sequence number.

Advantages of the Transaction Outbox Pattern

The Transaction Outbox Pattern has several advantages over other approaches for sending messages from a database transaction:

1. It allows services to update the database and send messages atomically, without using 2PC.
2. It decouples the service from the message broker, which reduces complexity and improves scalability.
3. (Optional) It ensures that messages are sent exactly once, even in the face of failures or restarts.
4. (Optional) It preserves message ordering, which is essential in many applications.

Limitations of the Transaction Outbox Pattern

The Transaction Outbox Pattern also has some limitations that should be considered:

1. It requires an additional table in the database to store messages, which adds complexity.
2. It introduces a delay between committing the transaction and sending messages, which might not be acceptable in some scenarios.
3. It requires an additional process (the Outbox Processor) to read messages from the outbox table and send them to the message broker.

Conclusion

The Transaction Outbox Pattern is a useful design pattern for sending messages/events from a database transaction in a reliable and scalable way. It allows services to update the database and send messages atomically, without using 2PC.
It decouples the service from the message broker, which improves scalability and reduces complexity. It ensures that messages are sent exactly once, and it preserves message ordering, which is essential in many applications.
However, it also has some limitations that should be carefully considered when choosing a messaging pattern for a particular application.