Choosing a database from access patterns
How the reads and writes of an application pick its database, and what a second database costs. Builds on What one modern server can do.
5 minute lesson
Start from the reads and writes
The useful question is not which database is best. It is how this data will be read and written. Each such way is an access pattern. Write them down before you choose anything.
- How is it found? By one key, by several fields, or across relationships?
- What must never be wrong? Money, stock and uniqueness need transactions and rules.
- How much, and how fast? A million rows and a billion rows a day are different problems.
From pattern to store
- Many relations and open questionsRelational
- Always fetched by one keyKey-value
- Self-contained objects of varying shapeDocument
- Huge volume read by one partitionWide-column
- Measurements over timeTime-series
A small team starts an online shop: customers, products, orders and payments. It expects a few thousand orders a day, and the reports it will need are not known yet.
Which database is the sensible start?
- A relational database: related data, rules, open questions
- A wide-column store: it will scale when the shop is huge
- A key-value store: it has the fastest lookups of them all
- One store of each kind: every part gets its ideal match
Show the answer
- A relational database: related data, rules, open questions
The data is full of relationships, payments must never be wrong, and tomorrow's questions are unknown: all three point to a relational database. A few thousand orders a day are nothing for one server. The other options solve problems that the shop does not have, and take away joins and transactions that it needs.
Related data, strict rules and unknown future questions: start relational.
A second database is never free
Large systems use several stores, each for what it does best: a relational database for orders, a key-value store for sessions, a search index for the catalogue. Each one added has a price.
- Another system to run: backups, upgrades, monitoring and people who know it.
- Two copies of some data, which can disagree. One store must be the source of truth, and the others are filled from it.
- No transaction across them. A write to both can succeed in one and fail in the other.
A shop keeps its products in a relational database and copies them into a search engine for the search box. A price is changed in the database, and the copy step fails.
Which principle limits the damage?
- The database is the truth, and the index is rebuilt from it
- The search engine is the truth, as customers see it first
- Both are the truth, and the newer of the two values wins
- Neither is the truth, until an engineer compares the two
Show the answer
- The database is the truth, and the index is rebuilt from it
Two stores can always disagree, because no transaction spans both. If one of them is declared the source of truth, a disagreement has a clear answer and a clear repair: copy again from the truth. The search engine shows a stale price for a while, and checkout, which reads the database, still charges the right one.
With several stores, name one source of truth. The others are copies that can be rebuilt.
How do you choose a database for a new feature?
Show the answer
List how the data will be read and written: by one key, by several fields or across relationships, what must never be wrong, and how much there will be. Pick the store whose strengths match those patterns. If nothing points elsewhere, use the relational database you already run. Add a specialised store only for a measured need, and decide which store holds the truth.
Recap
- Choose a database from the access patterns, not from its reputation.
- A relational database is the default. Specialised stores answer specific, measured needs.
- Every extra store adds operations work and copies that can disagree. Name one source of truth.