Trade-offs
- Benefits: fit-for-purpose storage (ACID safety in RDBMS, flexible schemas in document stores, fast analytic queries in columnar stores); better performance and scalability for specialized workloads.
- Costs: operational complexity (more services, backups, security), data consistency/coupling challenges, duplicated storage and ETL costs, need for cross-system schema/versioning and monitoring.
- When to use: domain boundaries with different access patterns (transactions vs product catalog vs analytics). Avoid if team cannot operate multiple datastores.
Example architecture
- OLTP: PostgreSQL (orders, payments, users) for ACID transactionality and FK integrity.
- Catalog: MongoDB for flexible product models (variants, localized attributes) exposed to product-service via document APIs.
- Analytics: ClickHouse for event/aggregate analytics and fast ad-hoc queries.
- Integration backbone: Kafka as the event bus.
Data synchronization strategies
- Source-of-truth + CDC: Use PostgreSQL as source for transactional data; Debezium captures WAL changes and produces events to Kafka. Consumers:
- Product service listens and updates MongoDB documents when product-related rows change.
- ETL consumer streams cleansed events into ClickHouse (or writes to S3 partitioned files for bulk load).
Advantages: low-latency, reliable, ordering preserved. Requires idempotent consumers and schema evolution strategy.
- Event-first (event sourcing): Application writes domain events to Kafka; services project those events into their local stores (Postgres projections, MongoDB views, ClickHouse aggregates). Advantages: single write path, easier replay; trade-off: more design work and eventual consistency.
- Batch ETL for heavy analytics: Periodic Airflow jobs export transactional snapshots to Parquet on S3 and bulk-load into ClickHouse/Redshift for cost-efficient large-scale aggregates.
Consistency & conflict handling
- Accept eventual consistency; design APIs to surface freshness SLAs (e.g., “last updated” timestamps).
- Ensure idempotency (upserts with natural keys), deduplication (event IDs), and schema migration plans.
- For read-after-write strictness, route reads to the authoritative store or implement read-through caching.
Operational considerations
- Observability: trace events (OpenTelemetry), monitor lag, consumer offsets, schema registry for Avro/JSON schema compatibility.
- Backups & DR per datastore, unified access control.
- Test: contract tests between producers/consumers, replay tests, and disaster recovery drills.
This design balances correctness for transactions, flexibility for products, and performance for analytics while managing complexity via CDC/event streaming, idempotent consumers, and clear ownership of source-of-truth.
