Rust Transactional Outbox & ClickHouse Engine

Rust Transactional Outbox & ClickHouse Engine is a production-grade distributed systems architecture built in Rust. It solves the classic dual-write problem across relational storage and analytics systems using the Transactional Outbox Pattern, coupled with atomic PostgreSQL migrations, high-throughput ClickHouse OLAP streaming, and OpenTelemetry observability.

1. System Architecture & The Dual-Write Problem

In distributed architectures, writing to a primary database and simultaneously publishing an event to a message broker (or analytics store) introduces race conditions and partial failures: either the database commit succeeds while the event fails to publish, or the event publishes but the database transaction rolls back.

This engine implements the Transactional Outbox Pattern:

1. Business entity updates and outbox events are committed together in a single atomic ACID transaction in PostgreSQL.
2. An asynchronous, high-frequency Rust relayer reads pending outbox events using SELECT ... FOR UPDATE SKIP LOCKED.
3. Events are safely streamed to ClickHouse OLAP tables with at-least-once delivery guarantees and idempotent deduplication.

                              [Incoming Client Request]
                                          │
                                          ▼
                             [Rust Domain Application]
                                          │
               (Single Atomic PostgreSQL ACID Transaction)
                                          ▼
                      ┌──────────────────────────────────────┐
                      ▼                                      ▼
               [Entities Table]                       [Outbox Table]
               (Users / Orders / State)               (Payload / Status: PENDING)
                                                             │
                                                             ▼
                                                [Asynchronous Rust Relayer]
                                                - SELECT ... FOR UPDATE SKIP LOCKED
                                                - Batch processing
                                                             │
                                                             ▼
                                                [ClickHouse OLAP Ingestion]
                                                - High-throughput analytics
                                                - Materialized aggregation

2. Core Technical Capabilities

• Guaranteed Message Delivery: Zero message loss between PostgreSQL transactional operations and downstream analytical consumers.
• High-Throughput Skip-Locked Polling: Worker instances use FOR UPDATE SKIP LOCKED to allow multiple concurrent relayer instances to poll the outbox table without database deadlocks.
• Declarative ClickHouse Migrations: Structured SQL migration engine that applies versioned schema alterations and ReplacingMergeTree engine configurations.
• POSIX Signal Handlers & Graceful Drain: Listens to SIGINT / SIGTERM signals, completing all in-flight outbox batches before releasing database connections.
• Structured Observability: End-to-end distributed tracing using tracing-subscriber and OpenTelemetry exports, recording span latencies across each batch execution.

3. Technology Stack

• Core Language: Rust (Tokio runtime, SQLx asynchronous PostgreSQL client, ClickHouse-rs)
• Primary Data Store: PostgreSQL (ACID transactional outbox and state)
• Analytical Storage: ClickHouse (ReplacingMergeTree / SummingMergeTree tables)
• Observability: OpenTelemetry, Prometheus metrics exporter, tracing crate
• Infrastructure: Docker Compose multi-service topology

4. Key Engineering Achievements

• Zero-data-loss event propagation under simulated process kills and database disconnections.
• Benchmarked at over 10,000 outbox events relayed per second on modest cloud container instances.
• Reusable distributed system architecture blueprint adhering to clean architecture and strict Rust safety guarantees.