SQL vs NoSQL: Choosing a Database

Relational databases give schemas, joins and transactions; NoSQL stores (key-value, document, wide-column, graph) trade some of that for scale-out and flexible schemas. Choose by access pattern, consistency and scale.

There is no "best database". There is a database that fits how your data is read and written.

flowchart TB
  Q["What are the access patterns?"] --> J{"Joins, transactions, ad-hoc queries?"}
  J -->|yes| SQL["Relational: PostgreSQL, MySQL, Aurora"]
  J -->|no| K{"Lookup by key at huge scale?"}
  K -->|yes| KV["Key-value / wide-column: DynamoDB, Cassandra"]
  K -->|no| DOC["Document: MongoDB, Firestore"]
  Q --> G["Relationships are the data?"] --> GR["Graph: Neo4j, Neptune"]
  %% step Q: Start from the queries the product needs, not from the technology.
  %% step SQL: Relational databases fit structured data with relationships and multi-row transactions.
  %% step KV: Key-value and wide-column stores scale out horizontally for simple, predictable lookups.
  %% step DOC: Document stores keep nested, flexible records together.
  %% step GR: Graph databases answer friend-of-friend and path questions efficiently.

Comparison

	Relational (SQL)	NoSQL (key-value, document, wide-column)
Schema	Fixed, enforced	Flexible per item
Queries	SQL, joins, aggregations	Mostly by key or index; joins in the app
Transactions	ACID across rows and tables	Often per item; some offer limited multi-item transactions
Scaling	Vertical + read replicas; sharding is hard	Horizontal partitioning built in
Consistency	Strong by default	Often tunable: eventual or strong
Fits	Orders, payments, inventory, reporting	Sessions, carts, user profiles, events, IoT, very high scale

ACID and BASE

ACID — Atomic, Consistent, Isolated, Durable: a payment either fully happens or not at all.
BASE — Basically Available, Soft state, Eventually consistent: replicas may disagree for a moment, but the system stays up.

Design for the access pattern

In DynamoDB-style stores you design the key for the query: partition key customer_id, sort key order_date gives "all orders of a customer, newest first" in one fast query. A query the key was not designed for needs a secondary index or a full scan.

In SQL you design normalised tables and add indexes for the slow queries — EXPLAIN ANALYZE shows whether a query uses an index or scans the table.

Polyglot persistence

Large systems mix: PostgreSQL for orders, Redis for sessions, DynamoDB for carts, object storage for images, a search engine for product search. Each store does what it is good at — at the cost of more systems to run.

Traps: choosing NoSQL "for scale" for a 10 GB dataset that needs joins; missing indexes blamed on the database engine; a hot partition key (every write to date=today); scans in a key-value store.