SQL or NoSQL

Relational, key-value, document, wide-column

Relational databases (PostgreSQL, MySQL) offer transactions, joins and constraints, and scale further than many expect with read replicas and partitioning. Key-value stores (Redis, DynamoDB) give fast lookups by key; document stores (MongoDB) suit flexible nested records; wide-column stores (Cassandra) handle very high write throughput with partition keys designed around queries. Justify the choice by access patterns, consistency needs and scale, not fashion.

Choosing, splitting and copying data

Database choice, sharding, replication and ID generation decide how far a design can scale.

Matching storage to the problem

Typical choices, each with alternatives.

payments, orders, inventory      -> relational (ACID transactions, constraints)
URL code -> long URL lookups      -> key-value (simple get by key, huge scale)
user profiles with nested data    -> document or relational with JSON columns
chat messages, activity events    -> wide-column (partition by conversation/user, time-sorted)
full-text product search          -> search index (Elasticsearch/OpenSearch) next to the main DB
analytics over billions of rows   -> columnar warehouse (BigQuery, ClickHouse, Snowflake)

Mention the access pattern first

Say "we always look up by code" before saying "so a key-value store fits".

Quick check: Which store best fits money transfers between accounts?

• A relational database with ACID transactions
• A cache without persistence
• A search index
• A CDN