beginnerintermediateadvanced

System Design · Section 15

Database Fundamentals for System Design

Level: intermediate
Read: 18 min
Concepts: 3

The generic relational vs. NoSQL vocabulary, SQL mechanics, indexing and ACID transactions each already have an owning topic (PostgreSQL, MySQL, MongoDB) — this section covers only what is genuinely system-design-specific: choosing a data model per workload rather than by reputation, using more than one storage technology deliberately rather than by accident, and recognizing the moment a logical transaction crosses a service boundary no single database can cover.

This section

Database Replication Explained | System Design Interview BasicsBig Tech Coach

What is true here

Relational databases buy strong consistency and joins at the cost of harder write-sharding; non-relational buys the reverse.
The choice is made per workload — a single system commonly uses both, deliberately, for different parts.
Polyglot persistence (multiple storage technologies in one system) is normal, but each store needs a stated, specific reason.
An ACID transaction is scoped to one database and cannot span multiple services.
A logical operation spanning services needs an explicit strategy (a saga, a transactional outbox) instead of an assumed transaction.

What you will be able to do

Choose between a relational and non-relational model based on a workload's actual access pattern and consistency requirement
Justify each storage technology in a system by a stated requirement rather than reputation or habit
Recognize when a logical operation is crossing a transaction boundary that no single database transaction covers

System-design-level decisions

The judgment calls this topic actually owns — everything else about databases lives in their dedicated roadmap.

Choosing a data model at a system-design level

coreintermediate

The choice between a relational database and a non-relational one is not "which is better" — it is which trade-offs a specific workload can afford. Relational buys strong consistency, joins and constraints, at the cost of harder horizontal scaling for writes. Non-relational buys easier horizontal scaling and schema flexibility, at the cost of weaker built-in consistency guarantees and no cross-collection joins.

Think of it as

A relational database is like a well-organized library with a strict card catalog — every book's location is guaranteed correct and cross-referenced, but reorganizing the whole library to add a new wing is a serious undertaking. A non-relational store is like many independent reading rooms that can each grow on their own — easy to add more rooms, but nothing guarantees a book you expect to find cross-referenced in another room actually is, and you look it up differently in each.

text

relational:     strong consistency + joins, harder write-sharding
non-relational: flexible schema + easier write-sharding, weaker built-in joins

What we're doing: Show the same system using both models deliberately, for different reasons, rather than picking one for everything.

mixed-model-system.txttext

A ride-sharing system:

Trip/payment/driver-account data → relational (PostgreSQL)
  - a completed trip must atomically update the fare,
    the driver's balance and the rider's receipt —
    exactly the multi-row transaction relational
    databases are built for.

Live driver location updates (thousands/sec) →
non-relational (a key-value or wide-column store)
  - each update is independent, extremely high write
    volume, no cross-entity transaction needed —
    exactly where a relational database's
    write-scaling ceiling would bite first.

3: This is the relational case: a transaction has to touch multiple entities atomically.
9: This is the non-relational case: independent writes at very high volume, no transaction needed.

Why this works: The decision is made per workload, not once for the whole system — a design that forces every workload into one model pays for a trade-off it did not need to accept.

Picking a data model based on general reputation rather than the workload's actual requirement

Wrong

text

"NoSQL scales better, so use it for everything,
including the payments ledger."

Better

text

"The payments ledger needs atomic multi-row
transactions — use a relational database for it,
even though a different, independent-write-heavy
part of the system uses a non-relational store."

What you see: A payments or inventory system built on a store with weak cross-entity transaction guarantees develops subtle correctness bugs — double charges, negative inventory — that a relational database's ACID guarantees would have prevented by construction.

Why: "Scales better" is true for a specific access pattern (independent, high-volume writes), not universally. A workload that genuinely needs atomic multi-row transactions gives that guarantee up by choosing a store that doesn't provide it, regardless of that store's other strengths.

Relational vs. non-relational, by workload

Relational

+Strong consistency, native joins
+Multi-row transactions across entities
+Harder to shard writes

Non-relational

−Flexible, per-record schema
−Easier horizontal write scaling
−Weaker built-in joins and consistency

The trade-off, at a system-design level

The trade-off, at a system-design level
Requirement	Favors	Why
Multi-row transactions across entities	Relational	ACID transactions and joins are native
Schema changes often, per-record	Non-relational (document)	No migration needed for a new field on one record
Very high write throughput, simple access pattern	Non-relational	Easier to shard writes across many nodes
Strict referential integrity (foreign keys)	Relational	Constraints are enforced by the database itself

Together

text

E-commerce order system:
  orders/payments/inventory → relational (PostgreSQL)
    - needs a transaction across "reserve inventory" +
      "create order" + "charge payment" to all succeed
      or all roll back together.

  product-view event stream (billions of events/day) →
  non-relational (a wide-column or document store)
    - each event is independent, no cross-entity
      transaction needed, and write volume is the
      dominant constraint.

Remember: Relational buys strong consistency and joins at the cost of harder write-sharding; non-relational buys easier write-scaling and schema flexibility at the cost of weaker built-in joins and consistency — choose per workload, not once for the whole system.

Polyglot persistence: multiple stores, each for a stated reason

standardadvanced

Most real systems end up using more than one kind of database — a relational store for transactional data, a cache for hot lookups, maybe a search index for full-text queries, object storage for files. Polyglot persistence is choosing that mix deliberately, with a stated reason per store, rather than accumulating stores ad hoc.

Think of it as

A well-run kitchen does not store everything in one giant refrigerator. Perishables go in the fridge, dry goods in a pantry, wine in a cellar — each chosen because of what it actually preserves best. A kitchen that throws everything in one fridge "to keep it simple" is not simpler in any way that matters; it is just failing to use the right tool for each kind of ingredient.

text

for each store in the system, ask:
  what specific requirement does this store satisfy
  that the primary database does not?
  no answer → it probably shouldn't be a separate store

What we're doing: Show the concrete cost of adding a store without a stated reason, versus adding one that has one.

stated-reason-check.txttext

Proposal: "let's also use MongoDB for user profiles,
alongside the existing PostgreSQL."

Stated reason check:
  - Does profile data need flexible, per-user schema
    that keeps changing? If yes → real reason.
  - Or is it "MongoDB seemed easier for this one
    table"? → not a reason grounded in a requirement
    the current store actually fails to meet.

Without a stated reason, the team now runs, monitors,
backs up and keeps consistent a second database — for
a workload the first one could have handled — purely
for operational cost with no corresponding benefit.

4: This is the actual test — a genuine, specific requirement, not general reputation.
12: This is the cost being paid for no reason — every additional store is ongoing operational weight, not a one-time decision.

Why this works: Polyglot persistence is a legitimate, common pattern — but only when each store's presence is justified by a specific requirement. Adding a store without that justification is pure operational cost with nothing bought in return.

Adding a new storage technology because it is trendy or "everyone uses it," with no stated requirement

Wrong

text

"Let's add MongoDB / Elasticsearch / Redis to
the stack — it's what modern systems use."

Better

text

"This workload needs sub-millisecond reads on a
key we control and can tolerate losing on
restart — that's a stated reason for adding
Redis specifically. If a proposed store has no
such reason, keep the data in the primary store."

What you see: The architecture accumulates storage technologies over time, each added for a project-specific reason nobody wrote down, until on-call has to understand five different systems' failure modes for workloads that could mostly have lived in the primary database all along.

Why: Every additional store is a permanent operational cost — backups, monitoring, upgrades, a new failure mode to reason about. That cost is worth paying only when a specific, stated requirement justifies it, and reputation alone is not a requirement.

One e-commerce system, four deliberate stores

PostgreSQL

orders, payments — needs ACID

Redis

sessions, hot cache

Elasticsearch

full-text search

photos, invoices

A typical polyglot mix, and the stated reason for each

A typical polyglot mix, and the stated reason for each
Store	Holds	Why this store specifically
PostgreSQL	Orders, accounts, payments	Needs ACID transactions and joins
Redis	Sessions, hot lookup cache	Sub-millisecond reads, ephemeral is fine
Elasticsearch	Full-text product search	Relational full-text search does not scale to this query shape
S3-style object storage	Uploaded images, PDFs	Large binary blobs do not belong in a relational row

Together

text

An e-commerce system, deliberately polyglot:
  PostgreSQL:     orders, inventory counts, payments
  Redis:          session data, product-page cache
  Elasticsearch:  "search for a product by keyword"
  S3:             product photos, invoices (PDF)

Each store earns its place by a stated requirement
the others do not satisfy well — not by habit or
by "this is what we always use."

Remember: Using more than one storage technology is normal and often correct — but each store in the mix should have a stated, specific reason it exists, or it is pure operational cost with nothing bought in return.

When a transaction must span multiple services

standardadvanced

A single database's ACID transaction only protects writes inside that one database. The moment "one logical operation" touches more than one service — each owning its own database — there is no single transaction that can wrap all of it, and the design has to decide explicitly how to keep those writes consistent instead.

Think of it as

A single-database transaction is like one clerk completing an entire form in one sitting — either the whole form is filed, or none of it is. A cross-service operation is like that same form needing signatures from clerks in three different buildings — there is no single "all or nothing" moment across all three, so the process needs its own explicit plan for what happens if the second building never signs.

text

one database, one service:  normal ACID transaction
multiple services:          saga / outbox — designed explicitly,
                             not provided by any single database

What we're doing: Show the exact moment a design needs to notice a transaction boundary is being crossed.

transaction-boundary.txttext

Naive design (assumes one transaction covers it all):
  BEGIN
    reserve_inventory(sku, qty)   -- service A's database
    charge_card(amount)           -- service B's database  ← different DB!
    create_order(...)             -- service C's database  ← different DB!
  COMMIT

This COMMIT cannot exist — three different databases,
three different connections, no shared transaction
coordinator by default.

System-design-level fix: recognize the boundary, then
choose an explicit strategy — e.g. a saga:
  1. Reserve inventory (service A, commits locally)
  2. Charge card (service B, commits locally)
     - if this fails, run a compensating action:
       release the inventory reservation from step 1
  3. Create order (service C, commits locally)

6: This single BEGIN/COMMIT block spanning three databases is exactly the mistake — it cannot actually work this way.
14: The saga makes the boundary explicit: each step is its own local transaction, with a named compensating action if a later step fails.

Why this works: The system-design skill is noticing the boundary exists at all — once noticed, the actual saga/outbox mechanics are a separate, deeper topic (distributed transactions), but a design that never notices the boundary will quietly assume atomicity it does not have.

Assuming a multi-service operation is atomic because each individual step "succeeds"

Wrong

text

reserveInventory(sku, qty);
chargeCard(amount);       // fails here
createOrder(...);         // never runs

// inventory is now reserved for an order
// that was never created — no rollback happened

Better

text

try {
  reserveInventory(sku, qty);
  chargeCard(amount);
  createOrder(...);
} catch (err) {
  releaseInventoryReservation(sku, qty); // explicit
  throw err;                             // compensation
}

What you see: Inventory silently drifts — stock stays "reserved" for orders that were never actually created, because a later step in the sequence failed and nothing explicitly undid the earlier step.

Why: Without an explicit compensating action, a partial failure across services leaves the system in a state no single step intended — the boundary-crossing has to be designed for directly, since no database transaction is protecting it automatically.

Checkout across 3 services — no single ACID transaction

Reserve inventory

service A, own DB

Charge card

service B, own DB

Create order

service C, own DB

Release reservation

compensating action

Same-database vs cross-service writes

Same-database vs cross-service writes
Writes involved	Fits in one ACID transaction?	What is needed instead
Two rows, one database, one service	Yes	Nothing extra — a normal transaction
Inventory (service A) + payment (service B)	No	A saga, or an outbox-based event flow
Order (service A) + notification (service B, best-effort)	No, and often does not need to be	Fire-and-forget event; notification failure should not roll back the order

Together

text

Checkout flow, three services, three databases:
  1. Inventory service reserves stock (its own DB txn)
  2. Payment service charges the card (its own DB txn)
  3. Orders service records the order (its own DB txn)

No single ACID transaction can wrap all three — each
step commits to its own database independently. The
design has to decide: if step 2 fails after step 1
succeeded, what un-reserves the stock?

Remember: An ACID transaction is scoped to one database — the moment an operation spans services, there is no built-in "all or nothing," and the design needs an explicit strategy (a saga, an outbox) rather than assuming atomicity it does not have.