SQL or not: the honest version
The tribal argument, minus the tribe. What actually differs and when it matters to you.
You will be told this is a huge decision. For most applications being built today it is not, and the honest default is a relational database. But the differences are real and worth understanding so you can recognise when you have hit one.
Relational (Postgres, MySQL, SQLite)
- Strict shape enforced by the database
- Joins across tables are cheap and normal
- Transactions across many rows are standard
- Changing shape requires a migration
Document (MongoDB, Firestore, DynamoDB)
- Each record can have its own shape
- Related data is often nested rather than joined
- Enormous scale is easier to reach
- Nothing stops two records disagreeing about shape
A practical filter: if your data is full of relationships (users have orders which have items which reference products) a relational database is doing that work for you, and a document store is asking you to do it by hand. If your records are genuinely independent, like event logs or cached documents, the flexibility costs you less.
Am I picking this because of my data, or because of a blog post?
Scale arguments for exotic databases usually assume a scale you do not have. Postgres will comfortably carry you further than almost any first product ever gets.
What to remember
- Relational is the sensible default for apps with related data.
- Schemaless does not remove the schema, it relocates it into your code.
- Choose from the shape of your data, not from the popularity of the tool.
Terms in this lesson
Field notes
Loaded from a deliberately slow source. The lesson above was already readable while this was still travelling. That is streaming, and it is the same trick a chat interface uses.
Fine at 500 rows, dead at 500,000
A list page loaded posts, then fetched each author separately. Development had eleven posts. The launch had four thousand, and every visitor issued four thousand queries. The database saturated eight minutes after the announcement.
The N+1 problem, at scale
The rename that took the site down
A column was renamed in a single migration during a deploy. For forty seconds the old code was still running and querying a column that no longer existed. Expand-then-contract exists precisely for those forty seconds.
Postmortem, e-commerce team
resolved in 900ms · region iad1
Hide field notes toggles a search param the loader reads. With it off, the slow promise is never created, so nothing streams.