Relational vs non-relational databases

Relational databases like Postgres and MySQL keep data in tables with a set schema, and you can query it however you want with SQL. Non-relational covers a lot of ground: key-value, document, wide-column and graph stores. They're usually built for specific kinds of lookups or for spreading across lots of machines.

Relational databases are known for transactions, which make a group of changes all succeed or all fail. If you're moving money between two accounts, you never want one balance to change without the other. You also get joins, so you can ask questions you didn't plan for.

With a lot of NoSQL stores, you work backwards. Decide on your queries first, then shape the data so each one is a quick lookup. That often means storing the same data in more than one place.

• Start with your queries and how many reads and writes you've got. Don't lead with a product name.
• If you need transactions or flexible queries, relational is a good default.
• You're allowed to use both, like one store for money and another for high-volume events.

Picking storage for a ride app

A ride app has riders, drivers, trips, payments and a stream of GPS pings. Instead of picking one database, look at what each kind of data needs.

1. Payments and trip assignment need transactions. A trip being booked and a driver being marked busy have to happen together. That's a relational database like Postgres.
2. Rider and driver profiles are read by ID constantly and rarely change. Postgres handles them fine, and a cache in front takes care of the reads.
3. Trip history is written once and read by rider or driver, newest first, forever. At billions of rows, a wide-column store like Cassandra keyed by user ID and sorted by time fits it well.
4. GPS pings come in at hundreds of thousands a second and are only useful for a few seconds. They go in memory, like Redis, and maybe into a data lake for analytics later.
5. Analytics, like rides per city per day, goes to a data warehouse, which is built for big scans and aggregations rather than single-row lookups.

Things interviewers tend to ask once you've brought this up.

Why not use one database for everything?

You can start that way, and it's often right. Split when one kind of data's volume or access pattern starts hurting the rest, like GPS writes slowing down payment queries.

Can Postgres scale?

Further than most people think: read replicas, partitioning tables, and bigger machines get you a long way. Past that you shard, either yourself or with something like Citus.

What do you lose with a NoSQL store?

Usually joins, flexible queries and multi-row transactions. If you later need a query you didn't plan for, you may have to store the data a second way.

When is a document database a good fit?

When you read and write whole records that have nested, varied fields, like a product with different attributes per category, and you rarely need to join across them.

• Naming a database before describing the queries.
• Picking NoSQL 'for scale' when the numbers fit on one Postgres box.
• Putting money in a store without the transactions it needs.
• Not saying what your partition key or primary key is.
• Treating 'SQL vs NoSQL' as one decision for the whole system.

• Sharding and consistent hashingSplit data across machines and keep it balanced.
• Consistency and the CAP theoremWhat happens when copies of your data can't agree.
• Caching strategiesMake reads faster, and figure out what to do when data changes.