NoSQL and Polyglot Persistence Questions

Requirements clarification:

• Low-latency messaging (chat) — sub-100ms write/read, ephemeral+durable history
• User profiles — read-heavy, occasional writes, low-latency reads, flexible schema
• Time-series activity/events — append-only, high-ingest, time-windowed queries
• Social graph — fast neighbor queries, traversal, recommendations
• Analytics — large-scale OLAP for ad-hoc and batch analysis

High-level recommendation (polyglot persistence):

• Messaging: Key-value store with stream pub/sub
  • Tech: Redis (primary in-memory for recent messages) + Cassandra or DynamoDB for durable history
  • Why: Redis gives sub-ms reads/writes and pub/sub for realtime; Cassandra/DynamoDB provides write-scalable, replicated durable storage with time-based TTLs.
• User profiles: Relational or document store
  • Tech: PostgreSQL (for strong schema/ACID) or MongoDB (if flexible schema needed) behind a read replica cache (Redis or CDN)
  • Why: Profiles benefit from transactions for updates and complex queries; read replicas and caching handle heavy reads.
• Time-series activity: Time-series DB
  • Tech: ClickHouse or InfluxDB / TimescaleDB
  • Why: Optimized for high ingest, time-window aggregations, TTLs, compression (ClickHouse/Timescale good for analytics-ready events).
• Social graph: Graph database
  • Tech: Neo4j or Amazon Neptune / JanusGraph on Cassandra
  • Why: Efficient traversals, shortest path, neighborhood queries, and recommendation algorithms.
• Analytics / Data Warehouse: Columnar OLAP
  • Tech: Snowflake, BigQuery, or Redshift + Parquet in data lake (S3/GS)
  • Why: Scalable, cost-effective large-scale aggregations and BI.

Data flow and integration:

• Ingest events/messages via Kafka (or Kinesis) as the central event bus.
• Producers write to primary DB (e.g., messaging to Redis & durable store) and publish events to Kafka.
• Consumers:
  • ETL jobs (Spark/Flink) subscribe to Kafka, transform, and write to analytics warehouse (Parquet -> Snowflake/BigQuery).
  • Graph updates (follows/unfollows) processed asynchronously into graph DB.
  • Time-series writes go to TSDB or are materialized from event stream.

Consistency and cross-system concerns:

• Use asynchronous eventual consistency for non-critical cross-store copies (e.g., profile cache, analytics).
• For operations needing strong consistency (profile updates + account metadata), use the authoritative store (Postgres) and implement read-after-write via synchronous writes; use transactions in Postgres.
• For multi-step business operations spanning services, implement application-level Sagas with compensating actions and idempotent event consumers.
• Use Kafka as source-of-truth for async replication; employ exactly-once processing semantics where supported (Kafka Streams, Spark Structured Streaming with checkpoints).

Backup, DR, and retention:

• Each system uses native backups + cross-region replication:
  • Postgres: point-in-time recovery (PITR) + streaming replicas
  • Cassandra/DynamoDB: incremental backups + multi-region replication
  • Redis: AOF + RDB snapshotting with replica failover; treat it's cache/durable design carefully
  • TSDB/ClickHouse: periodic snapshots to object storage (S3), partition-level retention
  • Graph DB: scheduled snapshots and incremental exports
  • Data warehouse: table-level snapshots, retained Parquet in data lake for audit
• Implement lifecycle policies: TTLs for messages, partitions for events, cold storage for older data.
• Test backups and DR runbooks regularly; maintain schema evolution strategy and migration tooling.

Operational considerations:

• Monitoring: end-to-end latency SLOs, consumer lag, replication lag, cardinality metrics.
• Cost/performance trade-offs: keep hot paths (recent messages, profile reads) in-memory/cached; cold data in cheaper durable stores.
• Security & governance: encryption at rest/in transit, RBAC, audit logs, GDPR-compliant deletion propagated via event-driven deletion workflow.

This design leverages best-fit databases per workload, uses Kafka for durable event-driven consistency, and balances strong consistency where required with scalable eventual consistency for analytics and graph updates.

Situation: We need a Cassandra schema for per-user, time-range reads and very high write volume (activity feed/time-series).

Schema / primary key

• Partition key: (user_id, bucket) — bucket is a time bucket (e.g., YYYYMM or epoch-minute window) to bound partition size and avoid a single huge/wide row or hotspot.
• Clustering key: (activity_ts DESC, activity_id) — activity_ts for time-range ordering; DESC lets reads for "latest N" be efficient. activity_id (UUID) ensures uniqueness and stable clustering.

Example CQL:

CREATE TABLE user_feed (
  user_id uuid,
  bucket int,               -- e.g. yearmonth or hour-bucket
  activity_ts timestamp,
  activity_id timeuuid,     -- ensures uniqueness and sortability
  verb text,
  payload blob,
  PRIMARY KEY ((user_id, bucket), activity_ts, activity_id)
) WITH CLUSTERING ORDER BY (activity_ts DESC, activity_id ASC)
  AND default_time_to_live = 0; -- TTL handled per-write or table-level as needed

TTL / retention

• Set TTL at write time based on business retention (e.g., 30 days). Avoid relying solely on manual deletes—use TTL to remove old rows automatically.
• Tune gc_grace_seconds appropriately (>= repair window) to allow tombstones to propagate before GC.

Anti-entropy and consistency

• Use regular repairs (nodetool repair) or automated incremental repair tools to prevent data divergence.
• Set appropriate consistency for writes: QUORUM or LOCAL_QUORUM for correctness; for extremely high-volume writes you may accept LOCAL_ONE for performance but handle potential data loss risk.
• Keep hinted handoff enabled and monitor write latencies; use retry policies.
• Monitor tombstone build-up (large TTL deletions) and tune compaction (TimeWindowCompactionStrategy for time-series) to reduce tombstones and read amplification.

Handling high write volume & hotspots

• Bucket by time (rolling buckets) to bound partition size and distribute writes across tokens.
• If a single user has extreme write throughput, further shard bucket (e.g., add shard_id mod N) and merge at read-time.
• Use asynchronous, batched writes in the ingestion pipeline.

Efficient latest-N reads

• Because clustering is DESC on activity_ts, a simple:
  SELECT * FROM user_feed WHERE user_id=? AND bucket=? LIMIT N;
  returns most recent items within that bucket. To get latest N across buckets, query current bucket then previous buckets until N collected (typically only a few).
• Optionally maintain a small "user_latest" table that stores a capped list (or top-K) of the most recent N activity_ids for O(1) reads if latency is critical.

Other considerations

• Use TimeWindowCompactionStrategy for time-series workloads.
• Monitor partition sizes (keep under ~100MB/100k rows depending on workload).
• Design for eventual consistency: idempotent writes, backpressure in producers.
• Consider materialized views or a denormalized “timeline” table if join-like reads or fan-out patterns are required.