Databases at Scale
SQL vs NoSQL
Relational databases buy you joins, ad-hoc queries, and strong transactional guarantees at the cost of rigid schemas and harder horizontal scaling. NoSQL trades those guarantees for flexible schemas and near-linear write scaling — the choice is about your access patterns, not which one is "better."
- Relational (SQL): fixed schema, joins across tables, ACID transactions, vertical scaling is the path of least resistance
- NoSQL is not one thing: document stores (MongoDB), key-value (DynamoDB, Redis), wide-column (Cassandra), and graph (Neo4j) solve different problems
- NoSQL systems typically favor denormalization — data duplicated across documents so a single read hits one partition instead of joining across many
- "Schemaless" does not mean "no schema" — it means the schema lives in application code instead of the database, so migrations become a runtime concern
- NewSQL (CockroachDB, Spanner) blurs the line: relational semantics with horizontal scaling, at the cost of higher operational complexity
| Dimension | SQL (relational) | NoSQL |
|---|---|---|
| Schema | Fixed, enforced at write time | Flexible, enforced (or not) in application code |
| Relationships | Joins across normalized tables | Denormalized — related data embedded together |
| Consistency | ACID transactions by default | Often eventual; strong consistency is opt-in and costly |
| Scaling | Vertical first; horizontal needs sharding | Horizontal scaling is the default design point |
| Query flexibility | Ad-hoc queries via SQL | Access patterns often must be designed in upfront |
The deepest difference is not the query language, it is where the schema lives and when it is enforced. A relational database rejects a malformed row at insert time; a document store will happily store it, and the very first sign of trouble is an NPE deep in application code reading a field that some documents never had. This is a real cost, not a convenience — schema migrations that a relational database handles as a single ALTER TABLE become an application-level "handle both the old and new shape" problem that can live in the codebase for years.
// Relational: normalized, joined at query time
// orders(id, customer_id, total)
// order_items(id, order_id, sku, qty)
class OrderRepository {
List<OrderItem> findItems(long orderId, Connection conn) throws SQLException {
String sql = "SELECT sku, qty FROM order_items WHERE order_id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, orderId);
// ...map ResultSet rows to OrderItem
return List.of(); // illustrative
}
}
}
// Document store: denormalized, the whole order is one document
// { "_id": "order-123", "customerId": "c-9", "total": 42.50,
// "items": [{ "sku": "A1", "qty": 2 }, { "sku": "B7", "qty": 1 }] }
// One read returns everything; no join, but every order duplicates
// whatever customer/item data it embedded at write time.