The transactional outbox in practice with Postgres and Python: no lost events, and no duplicates when the receiver dedups

Picture this code at a customer site: save an order to Postgres, commit, then call a webhook on the customer’s ERP. If the process dies between those two lines, the order exists and the ERP never hears about it. Swap the order and the risk flips: the ERP receives an order that your database has rolled back.

This is the dual-write problem. The Debezium blog puts it bluntly: there is no shared transaction that spans both a service’s database and Apache Kafka. The same is true of a customer’s system, and it is harder still, because you do not control it.

For an FDE, this is the classic bug in an integration that “works in the demo”. This guide builds a transactional outbox from scratch with Postgres and Python, ready to take straight to a customer.

What you will build, and what you need first

As microservices.io describes it, the outbox answers exactly one question: how to update the database and send a message atomically. The service first stores the message in the database, in the same transaction as the business data, and a relay sends it on afterwards.

You need Postgres running locally, Python 3 and a Postgres driver. The code below is written in the style of psycopg 3 with an HTTP client such as httpx. It is a stripped-down version for learning: it has no logging, retry backoff or configuration, which you should add before production.

Step 1: the outbox table lives in your own database

The core idea, as Debezium describes it, is to put an “outbox” table inside the service’s own database. No intermediate broker, no separate queue.

CREATE TABLE orders (
  id          uuid PRIMARY KEY,
  customer_id text NOT NULL,
  status      text NOT NULL
);

CREATE TABLE outbox (
  id           uuid PRIMARY KEY,
  aggregate_id uuid NOT NULL,
  event_type   text NOT NULL,
  payload      jsonb NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  sent_at      timestamptz
);

CREATE INDEX outbox_unsent ON outbox (created_at)
  WHERE sent_at IS NULL;

Check: \d outbox in psql should show outbox_unsent, a partial index that covers only unsent rows. The id column is the event ID, which the receiver will later use to dedup.

Step 2: write the order and the event in one transaction

Everything else in the pattern rests on this step. The two INSERT statements must sit inside the same transaction block.

import uuid, json

def create_order(conn, customer_id):
    order_id = uuid.uuid4()
    event_id = uuid.uuid4()
    with conn.transaction():
        conn.execute(
            "INSERT INTO orders (id, customer_id, status) VALUES (%s, %s, 'created')",
            (order_id, customer_id),
        )
        conn.execute(
            "INSERT INTO outbox (id, aggregate_id, event_type, payload) "
            "VALUES (%s, %s, 'order_created', %s)",
            (event_id, order_id, json.dumps({"order_id": str(order_id), "customer_id": customer_id})),
        )
    return order_id

Test it by deliberately raising an exception between the two INSERTs. The correct result is that both orders and outbox are empty. An order without an event, or the reverse, means you have two transactions rather than one.

Note that this function makes no network call. It touches only your database, so it is fast and never hangs because the customer’s API is slow.

Step 3: a polling relay with SKIP LOCKED

Microservices.io lists two ways to build the relay: transaction log tailing (CDC) or a polling publisher. With plain Python and Postgres, polling is a sensible starting point because it needs no extra infrastructure.

To run several workers, you need SKIP LOCKED. The Postgres documentation explains that with this option, rows that cannot be locked immediately are skipped, and that it can be used to avoid lock contention when several consumers read a queue-like table.

def relay_once(conn, http, url, batch=50):
    with conn.transaction():
        rows = conn.execute(
            "SELECT id, event_type, payload FROM outbox "
            "WHERE sent_at IS NULL ORDER BY created_at "
            "LIMIT %s FOR UPDATE SKIP LOCKED",
            (batch,),
        ).fetchall()
        for event_id, event_type, payload in rows:
            resp = http.post(
                url,
                json={"id": str(event_id), "type": event_type, "data": payload},
                headers={"Idempotency-Key": str(event_id)},
                timeout=10,
            )
            resp.raise_for_status()
            conn.execute("UPDATE outbox SET sent_at = now() WHERE id = %s", (event_id,))

Run this function in a loop with a short sleep. Check: open two terminals running two workers, create 200 orders, then count on the receiving side. Each worker should receive a different share, and neither should sit waiting for the other.

This is a deliberate simplification: the whole batch sits in one long transaction, holding row locks across every HTTP call. The Postgres documentation also warns that SKIP LOCKED gives an inconsistent view of the data, so use it only for queue-like tables such as the outbox, not for ordinary queries.

Why “no lost events” still means “possible duplicates”

Walk through a concrete case with the version above. A batch holds 50 events; the first 30 POST successfully and the 31st returns a 500 error. raise_for_status throws, the transaction rolls back, and the 30 earlier UPDATEs are lost with it.

On the next poll, all 30 events the customer already received are sent again. A process killed between the POST and the UPDATE produces the same outcome. Microservices.io spells out the consequence: the relay might publish a message more than once, so the consumer must be idempotent, for example by recording the IDs of messages it has already processed.

That is why the Idempotency-Key header carries the event ID itself. You cannot delete a duplicate delivery, but you can make it harmless.

A better version: one transaction per event

Duplicates are unavoidable, but resending 30 events because of one error is wasteful. The simplest fix is to commit per event: each transaction locks exactly one row, sends it, marks it and commits.

def relay_one(conn, http, url):
    with conn.transaction():
        row = conn.execute(
            "SELECT id, event_type, payload FROM outbox "
            "WHERE sent_at IS NULL ORDER BY created_at "
            "LIMIT 1 FOR UPDATE SKIP LOCKED"
        ).fetchone()
        if row is None:
            return False
        event_id, event_type, payload = row
        resp = http.post(
            url,
            json={"id": str(event_id), "type": event_type, "data": payload},
            headers={"Idempotency-Key": str(event_id)},
            timeout=10,
        )
        resp.raise_for_status()
        conn.execute("UPDATE outbox SET sent_at = now() WHERE id = %s", (event_id,))
    return True

Rerun the earlier scenario: the first 30 events have committed sent_at, and only the 31st goes back to the queue. A kill between the POST and the UPDATE now duplicates exactly one event. Note that with a plain ORDER BY created_at, the failed event is retried immediately, so you need to add backoff.

This version still holds a lock on one row while it waits for HTTP. If the customer’s API is slow, the next step is “claim first”: a short transaction marks the row as claimed with a timestamp and commits, the send happens outside the transaction, and only then is sent_at updated. You then have to add your own column and a rule to reclaim rows that have been claimed for too long, so do this only when you really need it.

Step 4: the receiver dedups by event ID

In Debezium’s example, the first thing the handler does is check whether the event with that UUID has already been processed. If you also write the receiving endpoint, or the customer lets you put a buffer layer in front of their system, do this:

CREATE TABLE processed_events (
  event_id     uuid PRIMARY KEY,
  processed_at timestamptz NOT NULL DEFAULT now()
);

def handle_event(conn, event):
    with conn.transaction():
        cur = conn.execute(
            "INSERT INTO processed_events (event_id) VALUES (%s) ON CONFLICT DO NOTHING",
            (event["id"],),
        )
        if cur.rowcount == 0:
            return  # already processed, skip
        apply_business_change(conn, event)

The crucial detail is that the dedup INSERT and the business change share one transaction. Split them and you have built yourself a miniature dual-write problem.

Rerun the scenario above: kill the relay right after the POST. Once the relay restarts, processed_events still holds only one row for that event, and the business change has been applied only once.

When the customer already has an idempotent API, learn from Stripe

Often you cannot touch the customer’s code. In that case, ask whether their API supports idempotency keys. Stripe is a good reference: the client generates the key so the server can recognise retries, Stripe suggests using V4 UUIDs, and keys can be up to 255 characters long.

Stripe stores the first response for each key and returns that same response on retries, including 500 errors. Keys are pruned after 24 hours, and if an old key comes back with different parameters, Stripe returns an error to prevent accidental misuse.

Those two details give you two questions to put to the customer. How long do they keep keys? If your relay can be down for longer than that window, resent events will be treated as new. And never generate a new key for each retry: the key belongs to the event, not to the request.

Common mistakes

The most common mistake is calling the API inside create_order “to save time” and treating the outbox as a fallback. That is dual-write again. The second is using an auto-incrementing request ID as the idempotency key, so every retry produces a new key.

The third is letting the outbox table grow forever. The partial index keeps the polling query fast, but you still need a periodic job to clear sent rows, for example:

DELETE FROM outbox WHERE sent_at < now() - interval '7 days';

The seven days here is only an example. The rule is that your retention must be longer than the receiver’s key retention, so that you still have data to compare against when investigating a duplicate delivery.

At a customer site, the outbox starts with a complaint

It usually sounds like this: “every now and then an order doesn’t make it into the accounting system”. The first thing to do is find every place where code writes to the database and then immediately calls out, and ask the customer how their endpoint handles repeated requests.

When reading job descriptions for FDE or integration engineer roles, look for phrases such as “at-least-once”, “idempotency”, “webhook” and “CDC”. On a CV, a line like “replaced dual-write with a transactional outbox, with kill-mid-flight tests proving no lost events and no duplicate processing” is far more convincing than a generic “event-driven”.

A small repo containing exactly these four steps, both relay versions and a failure-simulation script is worth bringing to an interview. The interviewer will ask what happens when the relay dies halfway, and you will have the answer ready in code.