Eventual SQL is a design approach that treats a relational database not just as a transactional system but as an append-only stream of truth. In this article we explain how Eventual SQL patterns let teams retain ACID-backed audit trails while scaling reads and analytics via NoSQL consumers for low-latency insights, and we provide practical patterns to implement this hybrid architecture.
Why treat relational databases as append-only streams?
Relational databases are excellent at guaranteeing transactional integrity, complex joins, and durable audit trails. But at scale, read-heavy analytics and low-latency dashboards often require horizontally scalable, partitioned systems that relational engines struggle to provide. Treating the relational transaction history as an append-only stream unlocks both strengths: relational ACID for writes and durable, ordered events that feed NoSQL consumers optimized for reads and analytics.
Core building blocks
Successful Eventual SQL architectures rely on a few proven components. Use them together to provide reliability, replayability, and consumer scale.
- Change Data Capture (CDC) — capture committed changes (INSERT/UPDATE/DELETE) from the RDBMS transaction log rather than polling tables.
- Transactional Outbox — write events atomically alongside business data so outbound messages cannot be lost or duplicated.
- Durable messaging layer — Kafka, Pulsar, or similar to provide ordered, replayable streams to many consumers.
- NoSQL materialized views — target stores (Cassandra, MongoDB, ClickHouse, Elastic) that support low-latency reads and analytic queries.
- Schema evolution and governance — explicit versioning, Avro/Protobuf schemas, and a registry to handle changes safely.
Practical architecture patterns
1. Postgres WAL → Debezium → Kafka → NoSQL
Debezium captures Postgres Write-Ahead Log (WAL) entries and publishes them to Kafka topics. Downstream consumers materialize those streams into NoSQL stores for fast queries. This pattern preserves the authoritative audit trail in Postgres while enabling many consumer services to scale independently.
2. Transactional Outbox + Polling Dispatcher
When WAL access is constrained, use a transactional outbox table: within the same DB transaction that mutates domain tables, insert an outbox row describing the event. A dispatcher job reads pending outbox rows and publishes them to the message bus, marking them sent. This ensures atomicity between state changes and emitted events.
3. Event Sourcing façade for audit-first systems
For domains requiring full history, implement an append-only event store in the RDBMS (or separate event table) and treat every operation as an event write. Materialize projections into relational views or NoSQL stores for queries. This favors auditability and replay, albeit at increased design complexity.
Design considerations for correctness and scale
Idempotency and deduplication
Consumers must handle duplicates from retries or replay. Include stable event IDs, apply-idempotent upserts, or use Kafka log compaction and consumer-side dedupe by key+sequence.
Ordering guarantees
Relational WAL preserves transactional ordering; ensure your messaging topology and partitioning strategy keep related keys in the same partition so consumers can apply events in order where required.
Exactly-once vs. at-least-once
Design for at-least-once delivery by default and make operations idempotent; only invest in exactly-once semantics if the application cannot tolerate duplicates and the added complexity is justified.
Schema evolution
Use explicit schema registries and backward/forward-compatible formats (Avro, Protobuf). When modifying SQL schemas, plan corresponding change events and projection migrations so consumers remain resilient during transitions.
Operational patterns
- Backpressure and throttling — monitor consumer lag and apply flow control or adaptive batching to avoid overwhelming downstream stores.
- Retention and compaction — keep a single source of truth for long-term audit (the RDBMS or an archival stream) and use compaction for topic retention to reclaim space while retaining the latest state for consumers.
- Replayability — keep streams replayable for recovery and rehydration of materialized views; store offsets or use timestamps for targeted replays.
- Monitoring and observability — track producer/consumer lag, transaction latencies, error rates, and schema compatibility checks.
Example implementation blueprint
Concrete fast-path setup delivering ACID-backed audit plus NoSQL scale:
- Primary write DB: Postgres configured with logical replication and a retained WAL for safe CDC.
- CDC: Debezium connectors tail WAL and publish change events to Kafka topics, partitioned by business key.
- Message bus: Kafka with topic per aggregate/table and compacted topics for stateful consumption.
- Materialization: Stream processors (Kafka Streams, Flink, or consumer apps) transform events into denormalized views and upsert into ClickHouse for analytics and Cassandra for OLTP-scale reads.
- Transactional outbox: For services that can’t expose WAL, implement outbox with a reliable dispatcher using exactly-once semantics at the application level.
Testing, migration, and rollout tips
Start with a small set of tables and run CDC in shadow mode: publish events to a test topic and compare materialized view outputs to existing queries to validate correctness. Use dual-writes or read-side feature flags to incrementally migrate read traffic to NoSQL projections. Always support full replay to rebuild projections if a bug is found.
When to use Eventual SQL
Eventual SQL is ideal when you must retain ACID guarantees for writes and audits but also need horizontally scalable, low-latency reads or analytics. It’s valuable for fintech audit trails, high-throughput ecommerce catalogs, telemetry pipelines, and any domain where replayability and provenance matter.
Common pitfalls and how to avoid them
- Ignoring schema compatibility: adopt a schema registry from day one.
- Over-sharding streams: choose partition keys that align with domain access patterns to avoid hotspots.
- Underestimating operational complexity: invest in monitoring, alerting, and automation for replay and backfill workflows.
Eventual SQL is not a silver bullet, but when applied deliberately it combines the auditability and transactional guarantees of relational databases with the consumer scale and low-latency analytics of NoSQL systems—delivering a pragmatic, resilient hybrid architecture.
Conclusion: By treating the relational database as an append-only stream—via CDC, transactional outbox, and durable messaging—you can keep ACID-backed audit trails while enabling wide-scale, low-latency NoSQL consumers for analytics and operational reads. Start small with one table, validate projections, and evolve schema and tooling as consumers mature.
Ready to design your Eventual SQL pipeline? Start by identifying the one domain table to capture with CDC and sketch the downstream materialized views you need.
