v0.28.0 — Transactional Inbox & Outbox Patterns

Full technical details: v0.28.0.md-full.md

Status: ✅ Released | Scope: Large (~6 weeks)

Reliable event messaging built entirely inside PostgreSQL — no Kafka, no Debezium, no external infrastructure required.

What problem does this solve?

Modern applications often need to communicate between services: “an order was placed, notify the warehouse system.” The naive approach — write to the database, then publish the event to a message broker — has a fatal flaw: if the application crashes between those two steps, either the database row exists with no event published, or the event was published but the database write failed. This is called the dual-write problem, and it causes data inconsistencies that are extremely difficult to debug.

v0.28.0 solves this entirely inside PostgreSQL, with no external infrastructure required.

The Transactional Outbox

Think of the outbox as a guaranteed delivery queue built directly into your database. When a stream table is refreshed and its result changes, pg_trickle automatically writes a record of those changes into a companion table — the outbox — in the same database transaction as the refresh. Either both succeed, or neither does. There is no window where the data changed but the event was not recorded.

An external relay process (your code, or the built-in one from v0.29.0) then reads the outbox and publishes to whatever downstream system you use: Kafka, NATS, a webhook, an SQS queue, or anything else. The relay can crash and restart safely — it just picks up where it left off.

For large batches of changes (more than 10,000 rows by default), pg_trickle uses a claim-check pattern: the outbox row carries only a lightweight summary, and the full row data is stored in a companion table that the relay reads in bounded memory chunks. This means delivery is never blocked by the size of a delta.

The Transactional Inbox

The inbox is the mirror image: a production-grade table for receiving events from outside. Call create_inbox('my_inbox') and pg_trickle automatically creates:

  • A pending messages stream table showing all unprocessed events
  • A dead-letter queue stream table for messages that have failed too many times
  • A statistics stream table tracking processing throughput and error rates

Applications insert events into the inbox with ON CONFLICT DO NOTHING for automatic deduplication — the same event published twice only creates one row. If a message processor crashes mid-flight, the message stays pending and will be picked up again.

Consumer Groups (Kafka-style, built into PostgreSQL)

For high-throughput scenarios where multiple relay processes share a single outbox, consumer groups let them coordinate safely — exactly like Kafka consumer groups, but with zero extra infrastructure. Each relay claims a batch under a visibility timeout (similar to Amazon SQS), and if the relay crashes its batch automatically becomes available for another relay to claim after the timeout expires.

Live dashboards of consumer health — lag, last heartbeat, active leases — are maintained as stream tables that can feed directly into Grafana.

Ordered Message Processing

For use cases where the order of messages matters — financial transactions, audit trails, order management — enable_inbox_ordering() creates a next_<inbox> stream table that surfaces only the next expected message for each entity (customer, order, account). Out-of-order arrivals are withheld until the preceding message has been processed. A separate gap detection stream table automatically alerts when a message appears to be permanently missing.

Priority queues let critical messages use a one-second refresh schedule while background messages use thirty seconds, with no interference between tiers.

Scope

v0.28.0 is a substantial release: six weeks of solo engineering effort covering the full outbox/inbox stack, consumer groups, ordered processing, benchmarks, and documentation. The result is a self-contained, reliable event-driven messaging system that needs nothing outside PostgreSQL.

Next: v0.29.0 — Relay CLI