Card 07/ 11
SQL vs NoSQL: Choosing a Database by Its Data
Transactions and joins on one side, flexible shape and spread writes on the other.
Relational databases, queried with SQL, store rows in tables of a fixed shape and change several rows in one transaction. NoSQL databases relax the fixed shape or the transactions in exchange for spreading writes across many machines or reading records whole. The shape of your data and what must stay consistent decide between them more than size does.
| Axis | Relational (PostgreSQL, MySQL) | NoSQL (DynamoDB, MongoDB, Cassandra) |
|---|---|---|
| Shape of the data | Fixed columns per table, declared up front | Each record can carry its own fields |
| Transactions | Many rows change together or not at all | Usually within one record or key; wider transactions are limited |
| Scaling writes | One primary takes writes; spreading them needs extra work | Built to spread writes across many machines |
| Query flexibility | Joins and new queries on any column | Fast for the queries the keys were designed for, awkward for new ones |
A transaction is a group of changes that all happen or none do. A join combines rows from two tables in one query, such as orders with the customers who placed them.
Use it when
- Several rows must change together, as in moving money between two accounts
- The data has relationships you will query in ways not yet known
- The write volume fits on one primary machine, which covers most products
Avoid it when
- Every record has a different shape and nobody queries across them
- Writes run beyond what one primary can take and cannot be split
Use it when
- Records are read and written whole, by a known key
- Write volume is high and grows, such as a stream of click events
- Fields vary from record to record
Avoid it when
- Changes must span many records atomically
- The questions you will ask of the data are not known yet
So start from the data: rows that must change together point to a relational database, and records read whole by key at high volume point to NoSQL.
← → move through sections and cards