Data Consistency with Outbox Pattern in System Integrations

Data Consistency and Challenges in Distributed Architectures

In modern software projects, ensuring that different services and modules work in harmony is a critical requirement. Especially in System Integrations processes, reliable data flow must be established between ERP, accounting, payment, SMS, email, and third-party systems. However, in distributed architectural structures, momentary disconnections can occur between local database operations and the step of firing an event to a message queue system.

Even though a database transaction successfully completes, the immediately triggered message queue dispatch may fail due to network errors or server outages. This leads to data consistency issues and gaps between services. Traditional approaches fall short in such scenarios.

The Working Principle of the Transactional Outbox Pattern

To eliminate these inconsistencies, the Transactional Outbox pattern is utilized. The core principle of this design pattern is to record the message into a relevant database table (outbox table) within the same transaction, rather than directly connecting to the target [message queue] system where the message will be sent.

• Business logic is executed, and the main data table is updated.
• Within the same transaction boundaries, the event data that needs to be produced is written to the outbox table.
• The database transaction is successfully confirmed (committed).

Thanks to this method, the main data and the event data to be transmitted to the outside world are stored atomically in relational databases such as [postgresql]. If the database is updated, the outbox record is guaranteed to exist as well.

Step-by-Step Implementation with PostgreSQL and Message Queue

This process can be securely structured using the [postgresql] database infrastructure. The outbox table must have columns to hold the identity, type, payload, and processing status of the events to be processed.

1. Table Design: Fields such as id, aggregate_type, payload, created_at, and processed are defined in the outbox table.
2. Transactional Recording: When an operation occurs, the writing operation to the main database table and the writing operation to the outbox table are executed under a single transaction.
3. Background Reader (Message Relay): A separate background process (via polling or transaction log mining methods) periodically scans unprocessed records in the outbox table.
4. Queue Transmission: The read data is transmitted to the external [message queue] system, and when transmission is confirmed, the status of the relevant record in the outbox table is updated.

Conclusion

Building reliable integrations in distributed systems requires being resilient against potential error scenarios. The Outbox pattern prevents data loss in your [system integration] processes, enabling systems to operate synchronously and stably.

Hypothetical Scenario: E-Commerce Order Process and Payment Integration

Let's consider a customer's order creation process on our e-commerce platform. While the order is being saved to the database, payments must be processed and stock levels must be reduced through external services. While updating the order table, the business logic writes an order-created event to the outbox table within the same transaction boundaries. Suppose a network outage occurs right as the database is committed. In traditional approaches, the notification could not reach the message queue, and stocks might not have been updated. However, thanks to the Outbox pattern, the background message reader component catches the unprocessed record in the outbox table when the system comes back online. It securely transmits the record to the external message queue and updates the record upon successful transmission.

The biggest commercial trade-off encountered at this stage is between performance and consistency. While polling-based readers put an extra load on the database, Transaction Log Mining approaches increase complexity. In error management, processing messages idempotently is of critical importance because the same event may be delivered to the queue twice due to network timeouts. Using unique keys for consumer services to handle this situation increases system stability.

Step-by-Step Implementation and Checklist

The technical steps to take into account to ensure the system works reliably end-to-end when deploying the Outbox pattern to a live environment are as follows:

• Atomic Operation Verification: Test that main database operations and outbox records are strictly performed within the same database transaction block.
• Table Indexing: Optimize background reader performance by adding appropriate indexes to the processing status and creation time fields in the outbox table.
• Idempotency Support: Design an idempotent architecture assuming that services consuming the message queue may receive the same event multiple times.
• Error Management and Retry: Set up exponential backoff mechanisms to prevent the system from locking up when queue transmission fails.
• Dead Letter Queue: Define a Dead Letter Queue (DLQ) mechanism for erroneous records that exceed a certain number of attempts and cannot be processed.
• Traceability: Integrate logging and metric collection infrastructures to track the lifecycle of outbox records.

References

• System Integrations — Cy Logic Software