Transactional Outbox Without Kafka on Postgres

Dual writes are the quietest way to lose money. You INSERT an order, then LPUSH a job, and the process dies between the two. The customer has a row and no invoice. Or you enqueue first and the INSERT rolls back: a worker bills a ghost. Kafka is not the fix. A transactional outbox is. You write the domain row and the event row in the same Postgres transaction. A poller later copies unpublished events onto Redis. The queue can lag. It cannot disagree with the database about what was committed.

The Dual-Write Failure

APIs like to be helpful. After a successful checkout they fire webhooks, send mail, and update search. Each of those is another system with another failure mode. If those side effects are issued inside the request after COMMIT, a crash drops them. If they are issued before COMMIT, a rollback leaks them. Distributed transactions across Postgres and Redis do not exist in the form you want. Two-phase commit is not an application pattern you should reinvent on a VPS.

The outbox is a table in the same database as the domain data. It is not a log product. It is a row with a payload, a created_at, and a published_at that stays null until the poller succeeds.

CREATE TABLE outbox (
  id           bigserial PRIMARY KEY,
  aggregate    text NOT NULL,
  aggregate_id text NOT NULL,
  event_type   text NOT NULL,
  payload      jsonb NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);
CREATE INDEX outbox_unpublished ON outbox (id) WHERE published_at IS NULL;

The application never talks to Redis on the write path. It only COMMITs. That is the whole architectural claim.

Write Path and Poller

Keep the INSERT of the order and the INSERT of the outbox row in one transaction. Use the same connection. If either statement fails, nothing is visible. The poller is a worker that SELECTs unpublished rows, pushes to Redis, then marks published_at. Marking happens only after Redis confirms the LPUSH. If the mark fails after a successful push, the poller will retry and the worker must be idempotent. That is cheaper than a missing invoice.

import json, psycopg, redis

r = redis.Redis(host='10.0.0.12', port=6379, decode_responses=True)

def create_order(conn, order):
    with conn.transaction():
        conn.execute(
            'INSERT INTO orders (id, total_paise) VALUES (%s, %s)',
            (order['id'], order['total_paise']),
        )
        conn.execute(
            'INSERT INTO outbox (aggregate, aggregate_id, event_type, payload) '
            'VALUES (%s, %s, %s, %s)',
            ('order', order['id'], 'order.created', json.dumps(order)),
        )

def publish_batch(conn):
    rows = conn.execute(
        'SELECT id, event_type, payload FROM outbox '
        'WHERE published_at IS NULL ORDER BY id LIMIT 100'
    ).fetchall()
    for row in rows:
        r.lpush('jobs:' + row[1], json.dumps(row[2]) if isinstance(row[2], dict) else row[2])
        conn.execute(
            'UPDATE outbox SET published_at = now() WHERE id = %s',
            (row[0],),
        )
        conn.commit()

Idempotency keys on the consumer are not optional. Store processed event ids in a table with a unique constraint. A duplicate LPUSH then becomes a no-op instead of a double charge. SKIP LOCKED on the poller SELECT lets you run two pollers without them fighting over the same rows.

What You Are Not Building

You are not building a company-wide event bus. You are not replacing Postgres with a log. You are not standing up a Kafka cluster on a single VPS and calling it streaming. The outbox is a reliability seam between a database you already trust and a queue you already run. If you later need fan-out to many independent consumers, you can have the poller push to more than one Redis list. That is still not a log.

Retention is a disk conversation. Published rows can be archived or deleted after N days once you know consumers do not need to replay from SQL. High-Speed SSD fills up quietly if you keep every event forever. A nightly DELETE WHERE published_at < now() - interval '14 days' is an architecture decision, not housekeeping trivia.

Operations on One Datacenter

The poller is a systemd unit on the worker node or on the database node. Prefer the worker node so a runaway poller cannot starve Postgres CPU. Give it a database role that can only SELECT and UPDATE outbox. Measure unpublished count and the age of the oldest unpublished row. If that age exceeds your RPO-adjacent budget for downstream side effects, page. L3/L4 DDoS on the public API does not protect you from a poller you forgot to enable after a reboot.

Backups must include the outbox table. A restore of orders without unpublished events will skip invoices that had not yet been pushed. Restore drills should enqueue from remaining unpublished rows and confirm workers are idempotent against already-processed ids.

Takeaway

The outbox turns a dual write into one COMMIT plus a retryable copy. Postgres remains the source of truth. Redis remains a buffer. Workers remain idempotent. You get at-least-once delivery without standing up a log cluster you do not want to operate.

Spin up a Postgres node and a worker node on Netbay in Lucknow (DC01) and rehearse the crash-between-write-and-push case before it happens in production — netbayhosts.in.