Database Selection and Trade-offs Questions

Situation & objective

Compare relational DB vs document NoSQL for a user-profile service with flexible attributes, 100M low-latency reads, and occasional complex analytics — from a systems-engineer perspective.

Data modeling

• Relational: fixed schema, normalized tables (users, attributes). Good for strong schema constraints and joins; adding flexible attributes needs EAV tables or JSON columns (adds complexity).
• NoSQL (document): store profile as a document per user (dynamic fields). Simpler reads for the API (single document fetch).

Consistency

• Relational: ACID by default — easier for updates that must be globally consistent.
• Document stores: often eventual consistency; choose a strongly-consistent mode or single-region writes for critical fields.

Read/write patterns

• Reads: 100M users => optimize for point reads. NoSQL excels with single-key lookups; RDBMS requires sharding/partitioning.
• Writes: profile updates are low volume; both handle it if scaled. For multi-field transactions, RDBMS simpler.

Caching needs

• Use a global edge cache (CDN + Redis/Memcached) for sub-10ms reads. Cache-aside pattern; invalidate on profile update. Cache reduces DB load regardless of chosen store.

Operational cost

• Relational: higher operational overhead when sharding, complex joins, and scaling reads (read replicas). Licensing costs possible.
• NoSQL: lower per-node cost for horizontal scaling, simpler operational model for read-heavy workloads.

Analytics

• Relational: good for complex SQL queries; but scaling analytics on OLTP DB is expensive.
• NoSQL: export to data warehouse (e.g., Kafka -> S3 -> Redshift/BigQuery) or use change-data-capture to feed analytics store.

Migration path (hybrid)

• Primary serving: document store optimized for single-key low-latency reads with strong read replicas.
• CDC pipeline: stream updates (Debezium / DynamoDB Streams) into a data lake / analytical DB.
• For transactional needs: keep a relational store for fields requiring strong ACID, or use a relational metadata store while documents keep flexible attrs.
• Add Redis fronting for hot reads; use feature flags to progressively route reads to document store while validating analytics ingest.

Trade-offs

• Pick document-first + CDC to analytics when low-latency scale and flexible schema matter. Choose relational-first when strict transactions and complex joins dominate.