Choosing a Datastore

The question people ask, and the question that helps

"SQL or NoSQL" sorts databases by their query language, which is close to the least interesting thing about them. The useful questions are about what you will do with the data.

How do you read it? By primary key, by range, by arbitrary filters, by full text?
How does it change? Append-only, frequent small updates, bulk rewrites?
What must be true across records? Nothing, or invariants spanning several?
How big is it, and how fast does it grow?

Concept

A defensible default

Start with a relational database. It gives transactions, constraints, joins, a mature query planner and a decade of operational knowledge. Move off it for a specific property it does not provide — not for a feeling about scale.

What you give up by leaving

The properties a relational store provides are not conveniences; they are things you will otherwise implement yourself, less well.

Property	If you leave, you now own
Transactions	Reasoning about partial writes by hand
Foreign keys	Orphaned records and the cleanup jobs to find them
Joins	Fan-out queries in application code, and their N+1s
Schema	Every historical shape of every record, forever
Query planner	Hand-tuned access paths that rot as data grows

The bill for 'schemaless'.

When a second store is genuinely right

Specialised stores earn their place when an access pattern is badly served by the primary one — full-text relevance ranking, very high write-rate time series, large binary objects, graph traversals many hops deep.