SQL versus NoSQL

“SQL versus NoSQL” is a bad question. The useful question is: what is the access pattern, and which invariants must the storage engine enforce?

Start here unless you have a reason

A relational database with real constraints is the right first drawing for most product systems. You get transactions, joins for the queries you did not predict, and a planner that uses indexes. Postgres in particular covers JSON when a slice of the row is schemaless, full text for a modest search, and even vectors. Reaching for a second database on minute ten is how designs become unexplainable.

You outgrow it for specific reasons, not for prestige:

• Write throughput that cannot be handled by one primary even after you have removed unnecessary indexes and batched.
• A data shape that is always fetched by one key and is enormous (events, time series).
• A query the relational engine is bad at (nearest vectors at scale, heavy full-text with custom ranking, multi-hop graph traversals).

What the other families are for

Key-value (Redis, DynamoDB used as a key lookup): the key is the query. Excellent for caches, sessions, idempotency records, and “give me this id.” Poor the moment you filter on a non-key field.

Wide-column (Cassandra, Scylla, DynamoDB with composite keys): you design the table around one query. The partition key decides where the row lives and what is hot. You can scale writes. You cannot casually add a where-clause. Many query shapes means many tables.

Document: a hierarchy that is stored and fetched together (a menu, a product with variants). Good when the document is the unit of consistency. Bad when you need to query inside every document and then join across them.

Graph: relationships are the data, and queries are “friends of friends” or “what depends on this package” with a depth you do not know up front. A single join is not a graph problem.

Search and vector engines are indexes, not sources of truth. They are allowed to be rebuilt.

The invariant test

Ask one question before you leave Postgres: which updates must be atomic? Seat inventory and a payment auth. A task claim and a lease. An account debit and a ledger line. If those sit in one database, a transaction is a couple of statements. If they sit in two, you have a saga, compensation, and a new class of bugs. Sometimes that split is worth it. It should be a sentence you say on purpose.

Schema-on-read does not delete the schema. It moves the pain to every reader. For data you will query six months from now, a constraint at write time is cheaper than a cleanup job.