Relational vs. NoSQL Databases in Practice

Beginner · 12 min

How a working backend developer actually decides between a relational database and a document, key-value, or wide-column store for a given app.

Before thisWeb Frameworks and the Request-Response Cycle

Be A Better Dev · 12 min

Relational vs. NoSQL Databases in Practice

The player loads from youtube-nocookie.com when you press play. Nothing reaches YouTube before that.

Taught by Be A Better DevReport a problem with this video
Not started
Set this lesson's status

What you'll learn

  • A relational database forces you to define your schema and the relationships between your data up front, which buys you consistency and flexible ad-hoc querying at the cost of upfront modeling work.
  • A document or key-value store lets you store data shaped the way your application actually reads it, which is fast for known access patterns but painful the moment you need to query it a different way.
  • Most real applications end up using more than one kind of database at once — a relational store for the data that needs integrity and joins, plus a document store, cache, or search index for the parts that don't.
  • The deep theory behind this choice — data modeling, normalization, and how either kind of database actually scales — is deliberately out of scope here and lives in the system-design roadmap.

Two different bets on how your data will be used

Every database is a bet on how you’re going to read and write your data later. A relational database bets that your data has real structure — entities that reference other entities — and that you don’t fully know yet what questions you’ll want to ask of it down the road. A document or key-value store bets the opposite: that you know your access pattern well enough right now to shape storage around it, and that flexibility to ask new kinds of questions later matters less than raw speed on the question you already know you’re asking.

That’s the whole decision, underneath the branding. Everything else — tables versus documents, joins versus embedding, SQL versus whatever query API a given NoSQL product ships — falls out of that one bet.

What a relational database actually buys you

A relational database (Postgres, MySQL, SQL Server) makes you define a schema before you store anything: tables, columns, types, and foreign keys that describe how rows in one table relate to rows in another. That’s real work up front. In exchange, you get two things a working backend developer relies on constantly. First, the database itself enforces your relationships — you can’t insert an order for a customer that doesn’t exist, because the foreign key constraint stops you. Second, SQL lets you ask questions you didn’t plan for when you designed the schema. If product asks “which customers who signed up in March bought more than two items in their first order,” you can write that query against a well-modeled relational schema even though nobody designed the schema anticipating that exact question.

That second property — flexible, ad-hoc querying across related data — is the main reason relational databases remain the default choice for most application data. Orders, users, payments, inventory: data where getting the relationships wrong is expensive, and where you genuinely don’t know every query you’ll need two years from now.

What a document or key-value store actually buys you

A document store (MongoDB-style) or key-value store (DynamoDB, or Redis used as a primary store rather than just a cache) flips the order of operations. Instead of modeling your entities and their relationships first, you model your access pattern first: “given a user ID, I need their full profile and their last ten orders in one read.” You design a document, or a key, that already contains everything that read needs — nested, embedded, denormalized — so that one lookup gets you everything, no joins required.

This is a genuinely good trade when your access pattern is well known and stable. A product catalog page, a user’s activity feed, a session store, a shopping cart — these are all cases where you know exactly how the data will be read, and embedding related data directly into one document avoids the overhead of stitching it together from multiple tables on every request. The cost shows up the moment your access pattern changes: if you embedded a user’s orders inside their profile document and now you need “all orders placed in the last hour across all users,” you’re stuck restructuring data that was shaped around a different question entirely.

Key-value and wide-column stores push this even further — DynamoDB in particular expects you to define your access patterns before you design your table, because its indexing model makes “just query it a different way later” genuinely difficult. That’s a deliberate design philosophy, not an oversight: you pay for that rigidity with very predictable, very fast performance on the patterns you did plan for.

Making the call for a given app

In practice, the decision usually comes down to a short checklist. Does this data have real relationships to other entities, where getting them wrong (an order with no valid customer, a payment with no valid order) would actually hurt you? Lean relational. Do you know the exact shape of the reads this data will need, and is that shape unlikely to change? A document or key-value store is a legitimate, often simpler choice. Do you need to run reports, ad-hoc analysis, or queries nobody has thought of yet? That’s relational’s home turf. Is this data more like a cache, a session, or a feed — disposable, access-pattern-specific, high-volume? That’s where Redis-as-primary-store or a document database earns its keep.

None of this means picking one and never touching the other. A typical backend service might use Postgres for its core transactional data, Redis for sessions and rate-limiting counters, and a document store or dedicated search index for a product catalog or full-text search. Polyglot persistence — using the right storage engine for each part of the system — is the normal, mature outcome of this decision, not a compromise.

What this lesson isn’t

This lesson is deliberately scoped to the application developer’s seat: which database do you reach for, given the shape of a particular feature’s data. It does not cover how to actually model relational data well, how normalization works, or what happens to either kind of database once you’re operating it under serious write or read load across multiple machines. That ground belongs to system design, and it’s covered in depth in SQL vs. NoSQL and data modeling, normalization and the ER model, and ACID transactions and the CAP theorem. Read this lesson to make the initial call on a new feature; read those three when you need to actually design the schema or reason about how either choice behaves at scale.

Check yourself

  1. 01What's the core tradeoff between defining your schema up front (relational) versus shaping storage around how you'll read the data (document/key-value)?

    A relational schema costs you upfront modeling effort but gives you consistency guarantees and the freedom to query your data in ways you didn't originally plan for, while a document or key-value store is fast and simple for the access pattern you designed it around but awkward or impossible to query any other way without redesigning the data.

  2. 02Why do most production systems end up using more than one kind of database rather than picking just one?

    Because different parts of an application have genuinely different data shapes and access patterns — order and payment data benefits from relational integrity and joins, while a session cache or a product catalog's search index is better served by a key-value store or search engine, so teams mix them rather than force one model to fit everything.

  3. 03If you're choosing a database for a new feature with a handful of entities that reference each other and need ad-hoc reporting later, which kind of database usually fits better, and why?

    A relational database usually fits better, because foreign keys enforce the relationships automatically, and SQL lets you ask new questions of the data later without having to restructure how it's stored first.

  4. 04What three system-design lessons does this lesson deliberately NOT cover, and why?

    Data modeling and normalization, the ER model, and the CAP theorem/scaling tradeoffs — because this lesson is about which database an application developer picks for a given app's day-to-day shape, not about how either kind of database is modeled in depth or how it behaves once you're operating it at serious scale.

Go deeper

Notes

Private to you

Esc
↑↓ navigate↵ open? shortcuts

Keyboard shortcuts

Press Esc to close

Anywhere

⌘K
Search and commands
/
Search
?
This sheet
Esc
Close

Reading a lesson

J
Next lesson
K
Previous lesson
B
Bookmark