Databases and data modeling

Most system design mistakes start with the database. Someone picks a store because it is fashionable, then spends years rebuilding joins and transactions in application code. Or someone runs every workload on one relational primary, and the analytics dashboard scans a huge table on the same machine that serves checkout.

The way out is to stop asking "which database is best?" and ask: what questions will this data answer, how often, and how fast? Once you can write that down, the store, the schema and the indexes mostly follow.

Concepts in this section

Read the pages in this order.

Choosing a family

Relational databases

A relational database such as PostgreSQL or MySQL stores tables with a fixed schema, and joins them at query time. It gives you three things:

1. Flexible queries. A new report is a new SELECT, not a data migration.
2. Constraints. Foreign keys, UNIQUE and NOT NULL refuse bad data.
3. Transactions. Several writes commit together. See transactions and isolation.

Normalize the write model, so each fact is stored once. Denormalize only where a measured read path needs it. Mark each copy as a snapshot or as something that must stay in sync. An invoice should keep the address from the time of the order, so that copy is a snapshot.

A single well-tuned Postgres node goes further than most people assume. Add replicas and a cache, and you cover most products. Writes are the ceiling, because they all go to one primary. That is when sharding or a distributed SQL database comes up.

Other families

• Key-value and document stores such as DynamoDB, Redis and MongoDB scale out easily, because each request names its key. Queries that do not use the key need planning ahead.
• Wide-column stores such as Cassandra suit very high write rates and time-ordered data.
• Specialists such as search, vector, graph, time-series and columnar stores each win one pattern. They are usually fed from the primary, not used as the source of truth.

Common mistakes

• Choosing for scale you do not have. A startup with 10,000 users pays the query-first tax for years.
• Using a cache or search index as the source of truth. Keep the truth in a durable primary.
• Analytics on the primary. One long GROUP BY can push the hot data out of memory and slow checkout.
• Storing blobs in the database. Put files in object storage and keep only the key.

Key takeaways

• Write the access patterns before you choose a store.
• Relational databases give flexible queries, constraints and transactions.
• Key-value, document and wide-column stores scale by requiring the key in every query.
• Specialized stores each win one measured pattern.
• Start with Postgres, and add a store only when you can point to a pattern it cannot serve.

Further learning

Rick Houlihan's re:Invent talk shows how access patterns become keys in DynamoDB.

Designing Data-Intensive Applications (Kleppmann)Designing Data-Intensive Applications (DDIA) — an O’Reilly book by Martin Kleppmann (The Wild Boar Book)dataintensive.net How Discord Stores Billions of MessagesWe decided early on to store all chat history forever so users can come back at any time and have their data available. This is a lot of data: how do we do it?discord.com