Choosing the Right Database for the Job

You now know the main database families, so the practical question is how to pick one. The common mistake is to ask which database is best. No database is best; each is best for a particular way of reading and writing data. A designer who answers Postgres or Mongo without a reason sounds like someone choosing a vehicle by brand. The good habit is to describe the job first and let the job choose the tool.

You would not ask whether a truck is better than a bicycle. You would ask what you are carrying, how far, and how often. A bicycle is perfect for one person across town and hopeless for a sofa, a truck is the reverse, and a city that needs both builds both rather than insisting on one vehicle. Databases work the same way, and the four questions below describe the load and the journey.

• -Access pattern: how will the data be read? By key (get user 42), by range (orders from last week), by joining several entities, by searching text, or by walking relationships? This is the single biggest signal.
• -Consistency: what happens if a reader sees slightly old data? For a bank balance that causes real harm; for a like count it does not. Strong guarantees cost speed and scale, a trade-off a later module explores.
• -Write volume: hundreds of writes a second, or hundreds of thousands? One well-sized relational database handles thousands of simple writes per second, so reach for a specialized store when the numbers demand it, not before.
• -Schema: does every record look the same and stay that way, or does it vary and change often?

• -Several related things must change together (orders, stock, payments)? Choose SQL.
• -A very high rate of simple writes, looked up by key? Choose a wide-column store.
• -Records that vary in shape, with a schema you will change often? Choose a document store.
• -One key, very fast, perhaps temporary (a session or a cart)? Choose a key-value store.
• -Relationships many hops deep? Choose a graph database.
• -Sub-millisecond reads in front of a slower store? That is not a database choice at all, it is caching, the next module.

Do not pick one database for a whole system. Break it into pieces and ask the four questions of each piece. An online shop might end up like this:

• -Orders and payments: SQL. Several tables must change together and a wrong answer costs money, so transactions and constraints matter most.
• -Product catalog: a document store. A laptop has RAM and a CPU, a shirt has a size and a color, and a document holds either shape without a table per product type.
• -Shopping carts and sessions: a key-value store such as Redis. Always accessed by a single key, needs to be very fast, and can expire on its own.
• -Product search: a search engine built on an inverted index, covered later in the course.
• -Click and activity events: a wide-column store or a log, because it is a huge volume of writes that are rarely updated.
• -Customers who bought this also bought: a graph, if that traversal is a core feature.

Using several stores, each for the job it does best, is called polyglot persistence, and it is how most large systems actually look. It has a price, though.

When you are asked what database you would use, a strong answer has four beats: say what the data is and how it is accessed, name the guarantee you need, pick the store, and state what you are giving up. For example: orders are related records that must change together and must never be lost, so I would use a relational database for its transactions, and I accept that spreading writes across machines will need sharding later. The last beat is the one that separates a memorized answer from a reasoned one.