Concept
What a Database Does & SQL vs NoSQL
The Six Core Jobs of a Database
- Durable storage — Data persists across restarts and crashes.
- Efficient retrieval — Indexes and query optimizers make lookups fast.
- Concurrent access — Multiple clients read and write without conflict.
- Transactional integrity — Related changes succeed or fail together.
- Security and access control — Read/write permissions enforced at the data layer.
- Scalability — Replication, indexing, and sharding allow growth beyond one server.
SQL vs NoSQL Philosophies
| SQL (Relational) | NoSQL (Non-Relational) | |
|---|---|---|
| Schema | Rigid, predefined (schema-on-write) | Flexible or none (schema-on-read) |
| Consistency | ACID transactions | BASE / eventual consistency |
| Scaling | Vertical (scale-up) | Horizontal (scale-out) |
| Examples | PostgreSQL, MySQL, SQL Server | Redis, DynamoDB, Cassandra, MongoDB |
ACID (SQL guarantee): Atomicity (all-or-nothing), Consistency (valid state always), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes). Non-negotiable for financial systems.
BASE (NoSQL trade-off): Basically Available, Soft state, Eventual consistency — data propagates to all nodes eventually, but a window exists where different nodes hold different versions.