SQL vs NoSQL: What Actually Differs, and How to Choose

The comparison is almost always made on scale, and scale is the axis that matters least for the application you are probably building. It is also made between two things that are not comparable: one is a query language, the other is a category defined by not being the first one.

A badly framed question

“NoSQL” groups together stores that have almost nothing in common with each other. A document store, a key-value cache, a wide-column store, a graph database and a time-series database differ from each other far more than any of them differs from PostgreSQL. Their only shared property is a negative one.

So the real question is never “SQL or NoSQL.” It is which data model matches the shape of your data, and which guarantees you need around it. Once you ask it that way, most of the arguments evaporate, because the honest answer for the large majority of applications is a relational database, and the interesting part is knowing precisely when it is not.

What actually differs

Four things, and scale is the last of them.

The data model. Relational stores normalise: each fact lives in exactly one place and relationships are expressed by keys. Document stores nest: a record carries its children inside it. That is the whole difference, and everything else follows. Nesting is fast when you always want the whole document and painful when the same nested fact appears in a thousand documents and changes.

Query flexibility. A relational database will answer a question you did not anticipate. You can join, aggregate and filter on anything, at some cost, without having planned for it. Most non-relational stores answer the questions you designed for and punish you for the others — often by making you scan everything, or by making you maintain a second copy of the data organised differently.

Consistency guarantees. A relational database can enforce invariants across rows and tables inside a transaction: this balance never goes negative, this order always has at least one line item, this email is unique. Most distributed stores offer atomicity within a single document or key and nothing across them, which means those invariants move into your application code, where they are checked rather than enforced.

Scaling shape. Relational databases scale vertically and by read replicas very well, and horizontally for writes only with real effort. Distributed stores are built to shard writes across nodes from the start. This is a genuine difference — it is just not the one that should decide your choice, for reasons below.

The question that decides it

If you take one thing: do you already know how the data will be queried?

Non-relational stores mostly require you to know your access patterns before you design the schema, because the schema is the access pattern. Design a DynamoDB table around fetching orders by customer, and fetching them by status next quarter is not a slower query, it is a new table plus a backfill. That is a fine trade when the patterns are known and stable — an authentication service, a session store, a feed by user id.

Early-stage products are the opposite. The whole activity is discovering which questions matter, and the answers change monthly. A relational database is the one that lets you ask a question you did not plan for, which is worth more in that phase than any throughput number.

The corollary is that the same product can want different answers at different times, and that is not a contradiction. It is why the migration direction matters, which I come back to at the end.

Consistency, and what CAP actually says

CAP gets quoted as “pick two of three,” which is not what it says. It describes what happens during a network partition: with the network split, you either refuse some requests or serve possibly-stale data. When the network is healthy — which is nearly always — you are not choosing between consistency and availability at all, and the real trade-off is consistency against latency. PACELC states this properly: else, latency or consistency.

The practical question is what your domain does when it reads something stale. A like counter reading 41 instead of 42 is nothing. An inventory count reading 1 instead of 0 is an oversold item, a refund, and an apology. A permissions check reading a revoked role is a security incident. Those are not the same risk, and “eventually consistent” is only a cost you can price per use case.

The point people miss: strong consistency is not just about correctness, it is about how much logic you have to write. Every invariant the database will not enforce becomes application code, and application code runs in several instances at once, which is exactly the situation transactions exist to handle.

The scale argument, honestly

Most teams that choose a distributed store for scale are buying for a scale they do not have and may never reach. A single well-indexed PostgreSQL instance on decent hardware handles tens of thousands of transactions per second and tables in the hundreds of millions of rows. Nearly every B2B product, internal tool, marketplace and content site ever built fits inside that comfortably.

What you pay for scale you do not need is query flexibility, transaction guarantees, and a large body of tooling that assumes SQL. That is an expensive premium on insurance against an event that probably will not occur, sold at the moment you can least afford it.

The legitimate version of the argument is specific rather than aspirational: write volume that genuinely exceeds one primary, multi-region writes with local latency requirements, or ingest rates where the workload is append-heavy and the queries are narrow. Those are real, and when you are in one you know it from measurements rather than from a diagram of what might happen after Series B.

Postgres ate most of the middle ground

A lot of the original NoSQL argument was about rigidity: schema migrations hurt, and some data genuinely is heterogeneous. Much of that has been absorbed.

-- schemaless where you need it, relational where you don't
CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  tenant_id   uuid NOT NULL REFERENCES tenants(id),
  occurred_at timestamptz NOT NULL,
  payload     jsonb NOT NULL          -- shape varies by event type
);

CREATE INDEX ON events USING gin (payload jsonb_path_ops);

You get document flexibility for the part that is genuinely variable, foreign keys and transactions for the part that is not, and one system to operate. The same applies to full-text search, geospatial queries, arrays, and time-series through extensions — all things that used to justify a second database.

The caveat is real, though: JSONB is not a substitute for a schema. It is an escape hatch for the fields you cannot model yet. Put your whole domain in a JSONB column and you have built a document store with worse ergonomics and no validation, and you will find out which keys were misspelled by reading production data.

When to choose which

Relational (PostgreSQL by default) — when entities have relationships you will query across, when invariants must hold across records, when reporting and ad-hoc analysis matter, or when you do not yet know all the questions. This is the default, and it should be argued away from rather than argued for.

Document store — when records are genuinely self-contained and read whole, when the shape varies meaningfully per record or per tenant, and when there is little cross-record querying. Content management, product catalogues with wildly varying attributes, per-customer configuration blobs.

Key-value — when access is exclusively by known key and latency matters more than anything: sessions, caches, rate limiters, feature flags. Almost always a complement to a primary database rather than a replacement for one.

Wide-column — when write volume is genuinely enormous, the access pattern is narrow and known, and the data is naturally partitioned by a key you always have. Event logs, sensor data, per-user activity histories at large scale.

Graph — when relationships are the data and traversals are deep and variable in length. Recursive queries in SQL cover “who reports to whom” perfectly well; a graph database earns its place at shortest-path, recommendation and fraud-ring shapes where the traversal depth is unbounded.

Time-series — when everything is append-only, timestamped, queried in windows, and downsampled as it ages. Metrics and telemetry. The retention and rollup behaviour is what you are buying, more than raw speed.

The asymmetry worth planning around

Migrations between these are not equally hard in both directions, and this is the argument that should carry the most weight when you are genuinely unsure.

Relational to document is mostly mechanical: you already know the relationships, so you can denormalise into whatever shape the access pattern wants. Document to relational is archaeology. Relationships that were never enforced are only conventions, and conventions drift — you will find orphaned references, three spellings of the same field, and records whose shape encodes which year they were written in. Nothing rejected any of it at write time, so all of it is in there.

So the default preserves optionality. Start relational unless you have a specific, measured reason not to, and add a specialised store when a specific workload demands it. That order lets you discover you were wrong cheaply.

A word on adding that second store: each one is another thing to provision, back up, monitor, secure, upgrade and staff. Worse, the moment the same fact lives in two systems, keeping them agreeing is your problem, permanently, and it is a harder problem than the one you added the second store to solve. Polyglot persistence is a real strategy, but the bar is a workload the primary store genuinely cannot serve — not a preference for a query language.

The short version

Do not compare SQL to NoSQL; compare data models and guarantees. Ask whether you know your access patterns — if not, you want the store that answers unplanned questions. Price staleness per use case instead of treating consistency as a philosophy. Assume you will not need horizontal write scaling until you have measured that you do. Default to relational because it is the cheapest position to be wrong from, and add a specialised store when a specific workload earns it.