Database Selection and Trade-offs Questions

Answer (Full‑Stack Developer perspective)

When it's reasonable
Polyglot persistence is sensible when different parts of the app have distinct data access patterns, consistency needs, or performance requirements that a single datastore can't satisfy without complexity or wasted resources.

Three concrete scenarios

• Analytics and OLTP separation: Use PostgreSQL for transactional user/account data and a columnar store or BigQuery for clickstream/event analytics to run heavy aggregations without impacting transactional latency.
• Document-centric features + relational domain: Use MongoDB for storing flexible product catalogs or user-generated content and MySQL/Postgres for normalized orders, payments and transactions.
• Low-latency caching and search: Use Redis for session and leaderboard caching, Elasticsearch for full-text search and faceted product search, and a primary RDBMS for authoritative data.

Three costs/risks

• Operational complexity: More backups, monitoring, schema/version management, and deployment automation across multiple systems.
• Data consistency & integration: Increased chance of stale or inconsistent views; requires ETL, change-data-capture, or careful transaction boundaries.
• Skill and cost overhead: Team needs expertise across systems; cloud costs and licensing can rise.

I’ve implemented patterns above in projects where separating analytics and OLTP improved response times and simplified queries while accepting added operational work.

Clarify constraints

• 5M items, heavy reads, rare writes (bulk updates possible), faceted search + complex filters, low latency for UI.

Primary datastore

• Document store (MongoDB / DynamoDB) as source of truth: stores product documents (id, title, category, price, attributes, inventory pointers, timestamps). Chosen for flexible schema (attributes vary) and efficient key-based reads.

Indexing & denormalization

• Denormalize commonly-read fields into product doc (price, primary category, thumbnail, searchable attributes).
• Maintain separate collections/tables for slowly changing reference data (brands, categories) referenced by id.
• Create DB indexes on product id, category, price, and any high-cardinality filter fields used by APIs to support fallback queries.

Search engine integration

• Use Elasticsearch/Opensearch for faceted search and complex filtering. Index denormalized product docs with mappings for keyword, text, numeric, and nested fields. Build aggregations for facets (category, brand, price ranges, attributes).

Caching

• CDN for images; Redis layer for hot-product JSON and popular search results; HTTP caching with ETags for API responses.

Sync & consistency

• On writes, update primary DB, then publish events to a durable queue (Kafka/SQS). Consumers apply changes to search index and invalidate/update Redis. Use version/timestamp in docs; indexer performs upsert to ES and logs failures for retry. Periodic full reindex (nightly/incremental with change-data-capture) to fix drift.

Operational notes

• Monitor search latency, refresh interval trade-offs (near-real-time vs write throughput), and implement bulk indexing for large updates.

Quick summary / goals

• Avoid hot shards, support efficient per-device range queries, enable downsampling + cold storage, and handle ~5,000 devices × 100 eps = 500k events/s.

Database choices

• TimescaleDB (Postgres+time-partitioning + continuous aggregates) or InfluxDB/ClickHouse for high ingest. Use object store (S3) for cold archives.

Shard / partition strategy

• Logical model: primary key = (device_id, time). Physical partitioning: time-range partitions (e.g., daily/hourly) + device sub-partitioning.
• Shard key: composite hash(device_id, salt) + time_bucket. Use hash(device_id, N_buckets) so writes from many devices distribute across N shards; include time bucket so older partitions can be dropped cheaply.
• To avoid hot shards when many devices write simultaneously, add a small randomized salt or modulo on device_id to distribute bursty device clusters across shards.

Retention & downsampling

• Hot tier (raw): keep high-res (second-level) for 7–30 days with TTL.
• Warm tier (aggregates): continuous aggregates / downsampling tasks produce 1-min and 1-hour rollups stored for 1 year.
• Cold tier: export aggregates and older raw data to Parquet on S3, lifecycle to Glacier.

Ingestion pipeline

• Use Kafka or Kinesis buffering, consumers batch writes into DB to reduce write amplification.
• Use background jobs (DB continuous aggregates or serverless workers) to downsample and export.

Per-device range queries

• Use composite index on (device_id, time) so range scans hit single partition; queries route to small set of partitions (time buckets).
• For long-range queries query aggregates first, then raw for recent windows.

Trade-offs

• TimescaleDB gives SQL + continuous aggregates; ClickHouse/Influx scale higher but may need more operational effort. Hash+time partitioning balances hotspot avoidance vs query locality.

Brief problem statement (example)
Placing an order can fail the "place → view history" workflow when the system uses asynchronous replication or eventual consistency: you POST /orders to the primary, the write is accepted and returned, but the read is served from a lagging read replica or cache so the new order doesn't appear immediately — confusing users and causing duplicate attempts.

Why it breaks UX & correctness

• Users retry and create duplicate orders.
• UI shows empty history, no in-progress state, or inconsistent totals.
• Business logic (inventory, payments) can be impacted.

Server-side patterns to mitigate

• Read-your-writes / session affinity: route reads after a write to the primary or to a replica pinned to the user session until the write is visible.
• Return canonical order object immediately and mark as PENDING in authoritative store (a separate "pending_orders" materialized state) so any read API can join pending + confirmed.
• Idempotency keys: require client-supplied idempotency-key on create to avoid duplicate orders.
• Publish events / change streams: emit order-created events and maintain a fast materialized view used by history endpoints; ensure low-latency propagation or synchronous write to materialized view for critical fields.
• Expose explicit consistency metadata: response includes order_id, status, and a monotonic session-token/cursor for causal reads.

Client-side patterns to mitigate

• Optimistic UI: immediately insert a client-side "Pending order" entry in history with server-returned order_id and status=PENDING.
• Show in-progress state & blocking affordances: disable "Place order" button, show spinner/toast, and display ETA or "Processing".
• Reconciliation: open WebSocket or poll order status; on confirmation replace PENDING with CONFIRMED; on failure roll back and surface error with next steps.
• Retry with idempotency-key and exponential backoff; if user navigates, persist pending items to local storage so history remains consistent.
• Visual distinctions: use badges (Pending, Confirmed, Failed) and tooltips explaining eventual-consistency.

Concrete flow example

1. Client POST /orders with idempotency-key → server returns { order_id, status: PENDING, session_token }.
2. Client adds local pending order in UI immediately.
3. Server writes to primary, enqueues replication, updates materialized pending view, emits event.
4. Client opens WebSocket listening for order_id updates; when CONFIRMED, it replaces entry and updates totals.

These combined server+client patterns prevent duplicates, surface in-progress state clearly, and provide a smooth, correct UX despite underlying eventual consistency.

Clarify goals & constraints

• Strong eventual consistency (reads "reasonably fresh"), cross-datastore queries, low developer friction, bounded latency for user-facing reads, support for multi-step transactions.

High-level architecture

• Single write model (authoritative) using relational DB + event store (append-only) for events.
• Change Data Capture (CDC) on relational writes + event stream (Kafka) as source of truth for downstream sync.
• Materialized read-models: document store for JSON-based queries, graph DB for relationships, relational for OLTP.
• Query federation layer exposes unified GraphQL/REST that routes to materialized stores and joins via lightweight federated engine.

Data flow & sync

• Writes go through transactional service: begin RDBMS transaction -> persist domain state -> append domain events to event store (in same tx using transactional outbox pattern) -> commit.
• Outbox is picked by CDC or outbox relay into Kafka; consumers build/update document and graph read-models (idempotent upserts).
• Use event versioning and optimistic concurrency to handle schema evolution.

Consistency & freshness

• Read models updated asynchronously; include per-entity version/timestamp in responses and optionally return "staleness" metadata.
• For critical flows, provide read-through or CQRS synchronous read-path: after commit, service can synchronously update necessary read-models or query primary DB.
• Use retry/backoff and dead-letter queues for consumer failures.

Query federation

• Expose GraphQL gateway that:
  • Routes heavy relational joins to RDBMS,
  • JSON-rich queries to document store,
  • relationship traversals to graph DB.
• For cross-store joins, prefer precomputed join read-models or orchestrated subqueries in gateway with parallel calls and correlation on IDs.

Operational concerns

• Idempotency, exactly-once semantics via consumer offsets and dedupe keys.
• Monitoring: lag metrics, consumer lags, outbox size.
• Testing: contract tests for events, chaos tests for consumer delays.

Outcome: predictable developer experience, low-latency reads, and controlled eventual consistency with opt-in strong reads.