Relational (SQL) strengths
When SQL wins
- Complex joins — reports spanning users, orders, and products.
- ACID transactions — multi-row updates must succeed or fail together.
- Ad hoc queries — analysts run SQL without schema migrations.
- Mature tooling — Postgres, MySQL, backups, ORMs, decades of ops knowledge.
-- Natural fit for SQL: relational integrity
CREATE TABLE follows (
follower_id UUID NOT NULL REFERENCES users(id),
following_id UUID NOT NULL REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT now(),
PRIMARY KEY (follower_id, following_id)
);
SELECT u.username, COUNT(f.following_id) AS following_count
FROM users u
LEFT JOIN follows f ON f.follower_id = u.id
WHERE u.id = $1
GROUP BY u.username;
StreamHub stores accounts, billing, and follow relationships in Postgres — relationships and constraints matter.
Database selection on AWS
DATABASERDS / AuroraACID · joins
DATABASEDynamoDBkey-value scale
ANALYTICSOpenSearchfull-text · autocomplete
DATABASEElastiCachesessions · rate limits
NoSQL categories
| Type | Model | Examples | Sweet spot |
|---|---|---|---|
| Document | JSON documents | MongoDB, DynamoDB | Flexible schema, nested objects |
| Key-value | Opaque key → blob | Redis, DynamoDB | Sessions, cache, simple lookups |
| Wide-column | Row key + column families | Cassandra, HBase | High write throughput, time-series |
| Graph | Nodes and edges | Neo4j | Social graphs, recommendations |
Document
ModelJSON documentsExamplesMongoDB, DynamoDBSweet spotFlexible schema, nested objectsKey-value
ModelOpaque key → blobExamplesRedis, DynamoDBSweet spotSessions, cache, simple lookupsWide-column
ModelRow key + column familiesExamplesCassandra, HBaseSweet spotHigh write throughput, time-seriesGraph
ModelNodes and edgesExamplesNeo4jSweet spotSocial graphs, recommendations
// Document store: video metadata as one document
{
"video_id": "v_9182",
"creator_id": "u_42",
"title": "Sunset timelapse",
"tags": ["nature", "4k"],
"stats": { "views": 128400, "likes": 9201 }
}
The rest of this guide is part of Pro
Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.
Still to come · 5 sections
- Side-by-side comparison
- Polyglot persistence
- Migration and schema evolution
- Decision checklist
- Common mistakes
Test yourself
Answer these before moving on — recall is what makes it stick.