iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

13.1 Relational vs NoSQL

By · Reviewed & last updated · Part of Enterprise Architecture Tutorial

Relational databases and NoSQL stores serve different access patterns. RDBMS for ACID transactions and relational integrity; NoSQL for scale-out, flexible schema, or specific query shapes (document, key-value, graph, columnar).

13.1 Relational vs NoSQL

The NoSQL family at a glance

FamilyExamplesBest for
DocumentMongoDB, CouchDB, DynamoDBFlexible content; per-tenant
Key-valueRedis, DynamoDB, MemcachedCache, sessions, counters
Wide columnCassandra, ScyllaDB, HBaseTime-series at scale; IoT
GraphNeo4j, ArangoDB, NeptuneSocial, fraud, supply chain
Time seriesInfluxDB, TimescaleDB, ClickHouseMetrics, IoT, finance ticks
RelationalPostgreSQL, MySQL, Oracle, SQL ServerACID, complex joins, OLTP

CAP theorem - choose two during a partition

Consistency Availability Partition tolerance CP (CA) AP Figure 13.1 - During a partition you can keep two; CA is only possible in non-distributed systems.
ChoiceMeansExamples
CPConsistent + partition tolerant; may refuse writesMongoDB (CP-tuned), HBase
APAvailable + partition tolerant; eventual consistencyCassandra, Riak
CAConsistent + available; only non-distributedSingle-node RDBMS

When to choose what

NeedPick
Transactions across multiple rowsRelational
Flexible per-tenant contentDocument
Fast in-memory cache / countersKey-value
Time-series at high write volumeWide column or Time series
Relationship-first queriesGraph

Worked example - the bakery's data choices

WorkloadStore
Customers / orders / paymentsPostgreSQL (transactional)
Product cataloguePostgreSQL (small)
Session cacheRedis
SearchElasticsearch / OpenSearch
Analytics eventsClickHouse
Reviews + ratings (UGC)MongoDB

Common pitfalls

  • NoSQL because it is trendy; you really needed a JOIN.
  • Relational at scale you cannot afford; should have partitioned.
  • No schema discipline in document stores; soup over time.
  • Cache as primary data; lost on restart.
  • Cross-store consistency assumptions; eventual is not immediate.

Where this lives in EA

Database choice is Information + Technology Architecture. The data catalogue must record which entity lives where and which store is the system of record.

Mentor’s tip: Relational for ACID; NoSQL by access pattern. CAP forces a choice during partitions. System of record per entity is an EA decision; without it, every team picks their own truth.

Discussion

Loading…