Distributed architectures often introduce complexity, especially when maintaining data consistency across multiple microservices. While specialized message brokers like Kafka or RabbitMQ are excellent for high-throughput messaging, they add significant operational overhead for growing projects.
This guide details how to build highly reliable, event-driven microservices using PostgreSQL as both the transactional system and the reliable event broker.
The Transactional Outbox Pattern
A common issue in microservices is updating a database and notifying other services atomically. If the database update succeeds but the network call to the message broker fails, the system enters an inconsistent state.
The transactional outbox pattern solves this by writing the event directly into an Outbox table in the same database transaction as the business operation.
Outbox Transactional Integrity
- Atomic Commit: Both the business record and the outbox event record are committed in a single, atomic SQL transaction.
- Zero Event Loss: If the transaction fails, everything rolls back. If it succeeds, the event is guaranteed to be in the database.
- Polling or Listen/Notify: A background worker polls the outbox table or uses PostgreSQL's native
LISTEN/NOTIFYmechanism to process and dispatch events.
BEGIN;
-- Update the primary entity
UPDATE orders
SET status = 'PAID', updated_at = NOW()
WHERE id = 'ord_12345';
-- Log the event in the outbox table within the same transaction
INSERT INTO outbox_events (id, aggregate_type, aggregate_id, event_type, payload, created_at)
VALUES (
gen_random_uuid(),
'Order',
'ord_12345',
'ORDER_PAID',
'{"amount": 45.99, "customer_email": "user@example.com"}',
NOW()
);
COMMIT;Leveraging PostgreSQL LISTEN and NOTIFY
To dispatch outbox events with sub-millisecond latency without constantly pounding the database with SELECT queries, we can use PostgreSQL's built-in LISTEN and NOTIFY feature.
Trigger-Based Notifications
We attach a trigger to the outbox_events table. Whenever a new row is inserted, the trigger executes a NOTIFY command containing the event ID or payload.
The background event-dispatcher process maintains a persistent connection and runs LISTEN outbox_channel;. When it receives the notification, it processes the event immediately.
Deduplication and Idempotent Consumers
In event-driven architectures, network glitches mean events are occasionally delivered more than once (at-least-once delivery). Consumers must be idempotent to prevent duplicate processing.
Database Constraints for Deduplication
On the consumer side, we track processed event IDs in an idempotency_keys table with a unique constraint. If a duplicate event arrives, the unique constraint violation blocks the insert, and the transaction is safely ignored.
- Unique Indexing: Ensure event IDs are primary keys.
- Conflict Resolution: Use
ON CONFLICT DO NOTHINGin SQL inserts to handle duplicates gracefully.
Conclusion
Before introducing external brokers like Kafka, maximize the capabilities of your existing database. Using PostgreSQL with the Transactional Outbox pattern and LISTEN/NOTIFY provides high transactional safety, zero event loss, and lower operational overhead.