Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 65 ยท Databases at Scale

SQL vs NoSQL and Storage Types

Choose between relational, document, key-value, wide-column and graph databases, and between block, object and file storage, with runnable Python examples.

Every system design eventually reaches the same question: where does the data live? The answer is rarely "one database for everything". Different data has different shapes, different access patterns and different consistency needs, and a good design picks the storage that fits each one.

This lesson covers two decisions. First, which database model fits your data: relational (SQL) or one of the NoSQL families. Second, which storage type holds raw bytes: block, file or object storage. You will also see why "SQL does not scale" and "NoSQL has no schema" are both half-truths.

The relational model

A relational database stores data in tables with a fixed schema. Rows are linked by keys, and you combine tables with joins. Its strengths are well understood:

  • ACID transactions: several changes succeed or fail together.
  • Flexible queries: you can ask questions you did not plan for, using SQL.
  • Integrity: foreign keys, unique constraints and types are enforced by the database.

MySQL, PostgreSQL and SQL Server are typical examples. A single well-tuned relational server handles far more traffic than most people expect, often thousands of queries per second with the right indexes. See SQL for the language itself.

The NoSQL families

"NoSQL" is an umbrella for stores that give up some relational features to gain something else, usually horizontal scale, a flexible shape or speed.

Four NoSQL families

Key-value Get and put by key. Redis, DynamoDB.
Document JSON-like records. MongoDB, Firestore.
Wide-column Rows with many sparse columns. Cassandra, HBase.
Graph Nodes and edges. Neo4j.

Each one is built around a single access pattern.

1 / 2

Key-value stores are the simplest: a giant dictionary. You can only look things up by key, which is exactly why they are so fast and easy to spread across machines. Document stores keep each record as a nested document, so an order and its line items can be one read. Wide-column stores such as Cassandra organize data by a partition key and are built for enormous write volumes. Graph databases make relationship queries ("friends of friends who like jazz") cheap, where SQL would need many self-joins.

Joins versus embedding

The real trade-off is where you pay for relationships. In SQL you store each fact once and join at read time. In a document store you usually embed related data inside the document, so a read is a single lookup, but the same fact may be copied in many places.

Python Normalized tables versus an embedded document

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Embedding wins when you always read the pieces together and the copied data rarely changes. Joins win when the same entity is referenced from many places and changes often.

Scaling: what is really different

SQL databases are not unable to scale. The difficulty is that joins and multi-row transactions are easy on one machine and hard across many. NoSQL stores often scale out easily because they only allow operations that touch one partition. Many "NoSQL" systems have since added SQL-like queries and transactions, and "NewSQL" databases such as CockroachDB and Spanner offer SQL with distributed scale. Start with the access pattern, then see how the options compare:

Question Leans toward SQL Leans toward NoSQL
Relationships between entities Many, queried in unplanned ways Few, or known in advance
Transactions across records Required Usually within one record
Schema Stable, enforced Changes often, varies by record
Scale One big server or read replicas Needs to spread writes across many nodes
Query shape Ad hoc, reporting Known lookups by key

When in doubt, start with PostgreSQL or MySQL. Move specific, high-volume data (sessions, feeds, events) to a specialized store when you have measured a reason.

Block, file and object storage

Databases are not the only place to keep bytes. Cloud platforms offer three storage types, and each fits a job.

Three kinds of storage

Block Raw fixed-size blocks, like a disk.
File Folders and paths, shared over a network.
Object Flat buckets of blobs with metadata.

The abstraction decides what you can do cheaply.

1 / 2

Block storage exposes raw blocks to a server, which formats them with a file system. It has the lowest latency and is what databases run on. File storage (NFS, SMB) gives many machines a shared directory tree. Object storage holds immutable blobs in a flat namespace, addressed by a key and an HTTP API. It is cheap, almost unlimited and replicated for durability, but you cannot edit part of an object in place: you replace it whole.

Python A tiny content-addressed object store

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Because objects are immutable, deduplication, versioning and caching by key are easy. This is why systems store video and images in object storage and keep only the object key in the database.

Polyglot persistence

Large systems usually mix stores: a relational database for accounts and payments, Redis for sessions and counters, object storage for media, a search engine for text queries, and a column store for analytics. This is called polyglot persistence. The cost is operational: every store is one more thing to run, back up, secure and keep in sync. Add a new one only when the benefit is clear.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which workload fits a key-value store best?

    Key-value stores excel at fast lookups by a known key. Reporting needs SQL, relationship queries suit graph databases, and big media belongs in object storage.
  2. 2. What is the main cost of embedding user data inside every order document?

    Embedding makes reads fast, but copies of the same fact drift unless you rewrite every document that contains it.
  3. 3. Which storage type is best for user-uploaded photos?

    Object storage is cheap, huge, durable and served over HTTP. The database keeps just the object key.
  4. 4. Why can you not edit the middle of an object in object storage?

    Object stores expose whole-object PUT and GET operations, which is what makes them simple to replicate and scale.

Summary

  • Relational databases give transactions, integrity and ad hoc queries; start there by default.
  • NoSQL families (key-value, document, wide-column, graph) each optimize one access pattern.
  • Embed data you read together; normalize data many records share and change often.
  • Block storage backs databases, file storage shares directories, object storage holds cheap durable blobs.
  • Real systems combine several stores (polyglot persistence) and pay the operational price.

Next: Database Replication