Posted inDevelopment

Transactional Outbox Pattern: The Ultimate Guide to Preventing Data Loss with PostgreSQL and Node.js

Development tutorial - IT technology blog
Development tutorial - IT technology blog

The Nightmare: When Database and Message Broker Get “Out of Sync”

Imagine this: A customer clicks to pay for a 2 million VND order, the system successfully deducts the money and saves it to the database. However, at that exact moment, a network flicker prevents the warehouse service from receiving the message to pack the goods. The result: the customer loses money but the order remains stagnant, and you have to stay up until 2 AM to reconcile logs and fix data manually.

I once personally handled an incident where nearly 5% of event messages were lost in a large payment system. At that time, the team used a simple approach: Save the order to PostgreSQL and then call publishMessage() to RabbitMQ. When traffic spiked, the Database commit succeeded, but the message sending command timed out. Orders sat dead in the DB, while downstream infrastructure services were completely blind to them.

That incident cost us 3 full days to write data recovery scripts. The painful lesson learned here is: Never trust two independent systems to always remain synchronized without a strict protection mechanism.

Why Conventional Approaches Often Fail

1. Dual Write

This is the most common mistake. You perform two independent actions in your code: Writing to the DB and Writing to the Message Queue.

async function createOrder(orderData) {
  const order = await db.orders.create(orderData); // (1) Success
  await rabbitMQ.publish('order_created', order);  // (2) If the network fails here, data will be out of sync!
}

The problem lies in the fact that steps (1) and (2) are not part of a single transaction. If step (2) fails, step (1) cannot automatically rollback, leading to an inconsistent state.

2. Distributed Transactions (2PC)

This approach attempts to group both the DB and the Broker into a single shared transaction. However, 2PC is extremely complex and significantly degrades system performance. In modern Microservices environments, 2PC is often a “nightmare” for maintenance and scalability.

Transactional Outbox Pattern: A Solution Powered by ACID Properties

Instead of trying to send the message immediately, we leverage the ACID properties of PostgreSQL. The core idea is very practical:

  • Create an auxiliary table called outbox right inside your Database.
  • When saving an order, you insert an additional record into the outbox table within the same Transaction.
  • A separate process (Relay) will scan the outbox table, send the messages, and mark them as processed.

Since saving the order and saving the message to the outbox share the same transaction, either both succeed, or nothing changes. Your data always remains in a state of absolute consistency.

Practical Implementation Guide with PostgreSQL and Node.js

Step 1: Designing the Outbox Table

We need a table structure flexible enough to hold various types of events.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    total_amount DECIMAL NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE outbox (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type TEXT NOT NULL, -- Example: 'Order'
    aggregate_id TEXT NOT NULL,   -- Order ID
    type TEXT NOT NULL,            -- Event: 'OrderCreated'
    payload JSONB NOT NULL,        -- Actual data
    status TEXT DEFAULT 'PENDING', -- PENDING, PROCESSED, FAILED
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Writing Transaction-Safe Business Logic

Using the pg library, you must use the same client to ensure Atomicity. If any command fails, all changes will be discarded.

async function createOrder(customerName, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // 1. Save order to the main table
    const orderRes = await client.query(
      'INSERT INTO orders (customer_name, total_amount) VALUES ($1, $2) RETURNING id',
      [customerName, amount]
    );
    const orderId = orderRes.rows[0].id;

    // 2. Save message to the outbox table (Same transaction)
    const outboxPayload = { orderId, customerName, amount };
    await client.query(
      'INSERT INTO outbox (aggregate_type, aggregate_id, type, payload) VALUES ($1, $2, $3, $4)',
      ['Order', orderId.toString(), 'OrderCreated', JSON.stringify(outboxPayload)]
    );

    await client.query('COMMIT');
    console.log('Transaction successful!');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Step 3: Building the Message Relay (Forwarding Worker)

To move messages from the DB to the Broker, Polling is the simplest and most effective approach for small and medium-sized systems.

async function relayMessages() {
  const client = await pool.connect();
  try {
    // Fetch 10 unprocessed messages, lock rows to prevent other workers from reading the same ones
    const res = await client.query(
      "SELECT * FROM outbox WHERE status = 'PENDING' FOR UPDATE SKIP LOCKED LIMIT 10"
    );

    for (const row of res.rows) {
      try {
        await messageBroker.publish(row.type, row.payload);
        await client.query(
          "UPDATE outbox SET status = 'PROCESSED' WHERE id = $1",
          [row.id]
        );
      } catch (err) {
        console.error(`Error sending message ${row.id}:`, err);
      }
    }
  } finally {
    client.release();
  }
}
setInterval(relayMessages, 5000);

The FOR UPDATE SKIP LOCKED technique is key here. It allows you to run multiple Worker instances simultaneously without the risk of sending the same message multiple times.

Optimization Tips for Real-world Production

When applying this pattern to projects with millions of transactions, keep these three points in mind:

  • DB Capacity Management: The outbox table will grow very quickly. If there are 1 million orders per day, the table will have 30 million rows after a month, slowing down queries. Schedule a cleanup to delete PROCESSED records after 3-7 days.
  • Idempotency: This pattern ensures “at least once” delivery. However, if a worker crashes right after sending but before updating the DB, the message will be resent. The receiver (Consumer) must implement duplicate checking.
  • Consider CDC: If the system reaches a threshold of >1000 transactions/second, constant Polling will put pressure on the DB. In this case, consider using Debezium to read the PostgreSQL WAL files, which forwards data without direct querying.

Conclusion

Transactional Outbox is not just a coding technique; it is a resilient system design mindset. Instead of relying on network luck, we rely on Database consistency. Good luck with your implementation, and may you sleep better during peak traffic!

Share:
Tags: