SQL vs NoSQL

The 30-second version

You'll hear "NoSQL scales, SQL doesn't" a lot. It's mostly hype. Big SQL databases serve millions of users, and plenty of NoSQL projects failed because the data didn't fit the model. The real question is simpler: what will your app ask the database, over and over? Those questions are your access patternsAccess patternOne specific way your app reads or writes data, written as a sentence — “get a customer's last 10 orders, newest first”. Listing them is the first step of choosing and designing a database., and this whole topic is about matching them to a model.

Neither is better. The cabinet is great when you ask lots of different questions ("which products sold best in October?" pulls from every drawer at once). The box is great when you ask the same question over and over ("show me this customer's account") and want it in one trip.

Here is the same tiny shop, stored both ways. Click between them and notice where "Blue Mug" appears:

The five families

"NoSQL" isn't one thing. It's everything that isn't relational. In this topic you'll meet five data modelsData modelThe shape a database lets you store and ask for data in: tables of rows, JSON-like documents, key → value pairs, wide sorted rows, or a graph of nodes and links., all storing the same shop:

Tables, keys and joins

The shop, as tables

A tableTable / row / columnHow a relational database stores data: a table (users) is like a spreadsheet; each row is one record (one user); each column is one field (name, email) with a fixed type. is like a spreadsheet with strict rules: every row has the same columns, and every column has a type. Our shop needs four tables:

Each table has a primary keyPrimary keyThe column (or columns) that uniquely identifies each row, like users.id. The database refuses two rows with the same primary key and indexes it automatically.: a column whose value is unique per row, like customers.id. Other tables point at it with a foreign keyForeign keyA column that points at another table's primary key — orders.user_id → users.id — and lets the database refuse rows that point at nothing.: orders.customer_id holds a customer's id. That's the "customer #1" note on the order slip from the filing-cabinet analogy.

Why is there an order_items table at all? An order holds many products, and a product shows up in many orders. That's a many-to-manyMany-to-many relationshipEach A relates to many Bs and each B to many As — orders contain many products, and a product appears in many orders. relationship. A single column can't hold "a list of products", so each (order, product) pair gets its own row in a join tableJoin tableA table whose rows just connect two other tables, one row per link — order_items(order_id, product_id, qty). It's how relational databases store many-to-many relationships..

Here is how you declare those tables in SQL. This is the schemaSchema (database)The declared shape of your data: which tables exist, their columns and types, keys and constraints. Changing it on a live system is a migration.: the rules every row must follow.

CREATE TABLE customers (
  id    bigint PRIMARY KEY,           -- unique per row, indexed automatically
  name  text   NOT NULL,
  email text   NOT NULL UNIQUE        -- no two customers share an email
);

CREATE TABLE products (
  id          bigint PRIMARY KEY,
  name        text NOT NULL,
  price_cents int  NOT NULL CHECK (price_cents >= 0)
);

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),  -- ← foreign key
  placed_at   date   NOT NULL
);

CREATE TABLE order_items (               -- the join table
  order_id   bigint REFERENCES orders(id),
  product_id bigint REFERENCES products(id),
  qty        int NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, product_id)     -- one row per (order, product) pair
);

Asking a question: SELECT with JOIN

"Show me everything Ada ordered" needs all four tables. A JOINJOINSQL that combines rows from two tables by matching columns — e.g. each order with its user's name: … FROM orders JOIN users ON users.id = orders.user_id. glues rows from two tables together wherever a condition matches:

SELECT o.id AS order_id, c.name AS customer, p.name AS product, i.qty
FROM orders o
JOIN customers   c ON c.id = o.customer_id   -- each order + its customer
JOIN order_items i ON i.order_id = o.id      -- + its line items
JOIN products    p ON p.id = i.product_id    -- + each item's product
WHERE c.name = 'Ada'
ORDER BY o.id, p.name;

This is the real output from Postgres 16:

 order_id | customer |   product   | qty
----------+----------+-------------+-----
      100 | Ada      | Blue Mug    |   2
      100 | Ada      | Tea (100 g) |   1
      101 | Ada      | Notebook    |   3
(3 rows)

Step through what that query means, one clause at a time:

1SELECT o.id AS order_id, c.name AS customer,
2       p.name AS product, i.qty
3FROM orders o
4JOIN customers   c ON c.id = o.customer_id
5JOIN order_items i ON i.order_id = o.id
6JOIN products    p ON p.id = i.product_id
7WHERE c.name = 'Ada'
8ORDER BY o.id, p.name;

You say what, the database decides how

Look at what the query doesn't say. It doesn't say "loop over orders", "use this index", or "read customers first". SQL is a declarativeDeclarative languageYou describe the result you want (“Ada's orders”), not the steps to compute it. The database's query planner picks the steps. SQL is declarative; a for-loop over records is not. language: you describe the result, and the database's query plannerQuery planner (optimizer)The part of the database that decides HOW to run your SQL — which index, which join order, scan or lookup — by estimating the cost of each option and picking the cheapest. picks the steps. Ask Postgres how it would run a smaller join with EXPLAIN:

shop=# EXPLAIN SELECT o.id, c.name FROM orders o
         JOIN customers c ON c.id = o.customer_id WHERE c.name = 'Ada';
 Hash Join  (cost=20.18..51.66 rows=8 width=40)
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o  (cost=0.00..27.00 rows=1700 width=16)
   ->  Hash  (cost=20.12..20.12 rows=4 width=40)
         ->  Seq Scan on customers c  (cost=0.00..20.12 rows=4 width=40)
               Filter: (name = 'Ada'::text)

The numbers in brackets are the planner's estimates (cost in its own units, guessed row counts and sizes). They're guesses for a freshly created, nearly empty table, which is why it imagines 1,700 orders; ignore them for now. Read the plan bottom-up: Postgres decided to find Ada in customers first, put her into an in-memory lookup table (a hash), then walk orders once and match each order against it. With different table sizes or indexes, it would pick a different plan for the same SQL. That freedom is the relational model's superpower: you can ask questions nobody planned for when the schema was designed. (Reading plans properly is taught in Database indexes.)

The database guards the rules

The schema isn't just documentation; the database enforces it on every write. Real Postgres output:

shop=# INSERT INTO order_items VALUES (102, 99, 1);   -- product 99 doesn't exist
ERROR:  insert or update on table "order_items" violates foreign key constraint "order_items_product_id_fkey"
DETAIL:  Key (product_id)=(99) is not present in table "products".

shop=# INSERT INTO customers VALUES (3, 'Cy', 'ada@example.com');
ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(ada@example.com) already exists.

Bad data can't get in, whatever bug your app has. Keep that in mind: in later sections, several NoSQL models hand this job back to your code.

From Go and JavaScript

Your app sends the same SQL as a string. The $1 placeholder is filled in by the driver, never pasted into the string yourself, which is what prevents SQL injectionSQL injectionAn attack where user input pasted into a SQL string changes the query itself (e.g. a name like ' OR 1=1 --). Prevent it by sending values separately as placeholders (?)..

import (
    "context"
    "database/sql"
    "fmt"

    _ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver
)

func printOrders(ctx context.Context, db *sql.DB, name string) error {
    rows, err := db.QueryContext(ctx, `
        SELECT o.id, p.name, i.qty
        FROM orders o
        JOIN customers   c ON c.id = o.customer_id
        JOIN order_items i ON i.order_id = o.id
        JOIN products    p ON p.id = i.product_id
        WHERE c.name = $1
        ORDER BY o.id, p.name`, name) // ← $1 = name, sent separately
    if err != nil {
        return err
    }
    defer rows.Close()
    for rows.Next() {
        var orderID int64
        var product string
        var qty int
        if err := rows.Scan(&orderID, &product, &qty); err != nil {
            return err
        }
        fmt.Println(orderID, product, qty) // 100 Blue Mug 2 …
    }
    return rows.Err()
}
// db comes from: db, err := sql.Open("pgx", "postgres://user@localhost:5432/shop")

Normalization: every fact in one place

Why not one big table?

The four tables in §02 might look like over-engineering. Why not one spreadsheet with a row per line item and every detail on it?

It reads nicely, and you never need a join. But "Ada's email" is stored three times, and "Blue Mug costs 12.00" twice. Duplicated facts cause three kinds of anomalyUpdate anomalyA bug caused by duplicated data: you change one copy of a fact but not the others, and now the database contradicts itself.: bugs where the data ends up contradicting itself.

Splitting the data into customers, products, orders and order_items removes all three: each fact has one home, and rows point at each other by id. Try renaming a product both ways:

The price of normalization: joins

Nothing is free. Because the facts are spread across tables, reading a whole order means putting them back together with joins at query time. On one machine, with the right indexesIndex (database)An extra, sorted data structure the database keeps next to a table so it can find rows by a column without reading the whole table — like the index at the back of a book., that takes well under a millisecond for a single order, and relational databases are extremely good at it.

It gets harder when the tables are so big they're split across many machines: then a join may have to fetch rows from several machines over the network. Several NoSQL models were designed around avoiding exactly that. We'll come back to it in §11.

Copying on purpose: denormalization

DenormalizationDenormalizationDeliberately copying data into several places (e.g. the product name into every order) so reads need fewer lookups — at the price of having to update every copy. means copying data on purpose so reads get cheaper. It isn't a sin. It's a trade you make with your eyes open:

• Historical facts should be copied. If the mug costs 12.00 today and 15.00 next year, old orders must still say 12.00. So real schemas store unit_price_cents on order_items. That isn't a duplicate: it's a different fact ("the price at the time of this order"). The same goes for the shipping address on an order.
• Read copies for speed. A precomputed "order count per customer", a search index, a cacheCacheA small, fast store of answers you already computed or fetched, so the next request for the same thing skips the slow work. The hard part is knowing when a cached answer is out of date.: all copies of facts that live elsewhere. That's fine as long as one place is the source of truth and the copies are rebuilt from it.

Documents: one box per thing

The shop, as documents

In MongoDBMongoDBA popular document database: it stores JSON-like documents in collections instead of rows in tables., a record is a documentDocument (database)One self-contained record stored as a JSON-like object, with nested objects and arrays inside it. Document databases like MongoDB store and fetch whole documents by id. and documents live in a collectionCollectionA named group of documents in a document database — the rough equivalent of a table, but documents in it don't have to share the same fields. (roughly, a table). Each document has a unique _id. Here is order 100 as one document:

{
  _id: 100,
  customerId: 1,                 // ← a reference: just the id, like a foreign key
  placedAt: ISODate("2026-10-01"),
  status: "shipped",
  items: [                       // ← embedded: the line items live INSIDE the order
    { productId: 10, name: "Blue Mug",    priceCents: 1200, qty: 2 },
    { productId: 11, name: "Tea (100 g)", priceCents: 800,  qty: 1 }
  ]
}

Two techniques appear in that one document:

• EmbeddingEmbeddingStoring related data inside the parent document — an order's line items as an array inside the order — so one read returns all of it.: items is an array inside the order. The four-table join from §02 collapses into one read, a single round tripRound-trip time (RTT)How long it takes for a message to go from one machine to another and for the reply to come back. Roughly 1 ms inside a data center, 70–150 ms across an ocean. to the database, because the data is already assembled.
• ReferencingReferencingStoring only the id of related data (productId: 10) and fetching it separately — the document-database version of a foreign key, but nothing checks it points at anything.: customerId: 1 just names the customer, who lives in a separate customers collection. The order also copies the product's name and priceCents. That's the "price at the time of the order" from §03: a deliberate snapshot, not an accident.

Compare it with the tables. Switch between the two views and follow "Blue Mug":

{ _id: 100, customerId: 1,
  items: [
    { productId: 10, name: "Blue Mug", qty: 2 },
    { productId: 11, name: "Tea (100 g)", qty: 1 } ] }
{ _id: 101, customerId: 1,
  items: [ { productId: 12, name: "Notebook", qty: 3 } ] }
{ _id: 102, customerId: 2,
  items: [ { productId: 10, name: "Blue Mug", qty: 1 } ] }

{ _id: 1, name: "Ada" }
{ _id: 2, name: "Bob" }

{ _id: 10, name: "Blue Mug", priceCents: 1200,
  description: "…", stock: 40 }
…

Talking to MongoDB

MongoDB isn't installed in this site's sandbox, so the outputs below are illustrative: written to match what the real mongosh shell prints, but not captured from a live server. The query syntax is real.

// One read returns the whole order, items included.
db.orders.findOne({ _id: 100 })

// Query INSIDE the array: every order containing product 10.
db.orders.find({ "items.productId": 10 }, { _id: 1, placedAt: 1 })
// → [ { _id: 100, placedAt: ISODate('2026-10-01T00:00:00.000Z') },
//     { _id: 102, placedAt: ISODate('2026-10-21T00:00:00.000Z') } ]      (illustrative)

// Make that query fast: an index on a field inside the array.
db.orders.createIndex({ "items.productId": 1 })

From application code it looks like this. The Go program compiles against the official v2 driver; the result shown is illustrative:

import (
    "context"

    "go.mongodb.org/mongo-driver/v2/bson"
    "go.mongodb.org/mongo-driver/v2/mongo"
)

type Item struct {
    ProductID  int    `bson:"productId"`
    Name       string `bson:"name"`
    PriceCents int    `bson:"priceCents"`
    Qty        int    `bson:"qty"`
}

type Order struct {
    ID         int    `bson:"_id"`
    CustomerID int    `bson:"customerId"`
    Items      []Item `bson:"items"` // ← embedded array decodes into a slice
}

func loadOrder(ctx context.Context, orders *mongo.Collection, id int) (Order, error) {
    var o Order
    err := orders.FindOne(ctx, bson.M{"_id": id}).Decode(&o) // ← one round trip: one request, one reply
    return o, err
}
// orders comes from: client.Database("shop").Collection("orders")

Referencing, and joins that aren't free

When you need the customer's name next to the order, you have two choices: a second query by customerId, or a $lookup stage, which is MongoDB's join:

db.orders.aggregate([
  { $match: { _id: 100 } },
  { $lookup: {
      from: "customers",          // the other collection
      localField: "customerId",   // field in orders
      foreignField: "_id",        // field in customers
      as: "customer"              // put matches in this new array field
  } }
])

aggregate takes a list of stages, and each stage's output feeds the next (a pipeline). $lookup works, but document databases are built and tuned around not needing it in code that runs on every request. If you find yourself joining on every request, the data probably wants to be embedded, or relational. (When the "join" is done in your app with one query per row, you get the N+1 problem, see N+1 Queries.)

Schema-on-write vs schema-on-read

Postgres rejected bad rows in §02 because the table has a schema checked on every write: schema-on-writeSchema-on-writeThe database checks every write against a declared schema and rejects data with the wrong shape — how SQL tables work.. By default MongoDB accepts any document into any collection:

db.products.insertOne({ _id: 13, name: "Teapot", colour: "blue" })  // "colour"!
db.products.insertOne({ _id: 14, name: "Kettle", color: "red", priceCents: "39.99" })
// both succeed: { acknowledged: true, insertedId: … }   (illustrative)

One document spells colour, the other color, and one price is a string. Nothing stopped them. This is schema-on-readSchema-on-readThe database accepts any shape; the code that reads the data decides how to interpret it (and must cope with old or missing fields).: the shape is decided by the code that reads the data, and that code must cope with every shape that was ever written.

// Every reader must handle every historical shape.
// a ?? b means "a, unless a is null/undefined — then b"
const color = p.color ?? p.colour ?? "unknown";
const cents = typeof p.priceCents === "string"
  ? Math.round(parseFloat(p.priceCents) * 100)
  : p.priceCents;

-- The database refuses bad shapes at the door.
CREATE TABLE products (
  id          bigint PRIMARY KEY,
  name        text NOT NULL,
  color       text,
  price_cents int  NOT NULL
);

Embed or reference?

Every document design comes down to this question, asked once per relationship. Here's the checklist, then the three classic ways embedding goes wrong.

Order line items pass every test: always shown with the order, owned by it, a handful per order, frozen once the order is placed. That's why §04 embedded them.

Failure 1: the array that never stops growing

A blog post with its comments embedded looks lovely, until a post goes viral. Every new comment makes the document bigger. MongoDB caps a single document at 16 MB, and long before that, every read of the post (even "just show the title") drags the whole thing off disk and over the network. Every new comment also rewrites a larger document.

Drag the slider to see what happens as comments pile up:

The fix is to reference: comments go in their own collection, each with a postId, plus an index on { postId: 1, createdAt: -1 } so "latest 20 comments of this post" is a quick lookup. A common middle ground is to embed only the latest few comments in the post (for the first screen) and keep the full list separate.

Failure 2: many-to-many

Students take many courses; courses have many students. Embed courses in each student and every course's details are copied into hundreds of student documents. Rename a course and you're back to the update anomaly from §03, now across hundreds of documents with no join to save you.

The usual document answer is to reference in one or both directions (courseIds: [7, 9] on the student) and fetch the courses in a second query. That works. But if your data is mostly many-to-many relationships, that's a strong hint the relational model (a join tableJoin tableA table whose rows just connect two other tables, one row per link — order_items(order_id, product_id, qty). It's how relational databases store many-to-many relationships.) fits better.

Failure 3: data that's shared and changes

Suppose each order embeds the customer's current email "for convenience". Ada changes her email: now you must find and update every order she ever placed. Ask yourself: is this copy a snapshot or a live value?

• Snapshot (price at purchase, shipping address used): copy it and never update it. Good embedding.
• Live value (current email, product's current stock): reference it by id.

The middle path: copy just a few fields

The order in §04 does both at once. It references the product by productId, and copies the two fields the order page needs (name, priceCents). MongoDB's docs call this the extended reference pattern. The page renders from one read; the product's full details (description, stock, images) stay in products, the single source of truth.

Key-value: design the key from the question

The simplest model: Redis

RedisRedisA popular in-memory data server: a fast key–value store and cache that also offers counters, sorted sets, pub/sub channels and streams (an append-only log for messages). keeps everything in memory, so a GET usually answers in well under a millisecond. Values can be plain strings or small structures such as hashes (a map inside one key). Real session from Redis 7.0:

127.0.0.1:6379> SET session:9f2c '{"userId":1}' EX 3600
OK
127.0.0.1:6379> GET session:9f2c
"{\"userId\":1}"
127.0.0.1:6379> HSET cart:1 product:10 2 product:11 1
(integer) 2
127.0.0.1:6379> HINCRBY cart:1 product:10 1
(integer) 3
127.0.0.1:6379> HGETALL cart:1
1) "product:10"
2) "3"
3) "product:11"
4) "1"
127.0.0.1:6379> GET session:nope
(nil)

// go-redis v9: github.com/redis/go-redis/v9
err := rdb.Set(ctx, "session:9f2c", `{"userId":1}`, time.Hour).Err()
if err != nil {
    return err
}

raw, err := rdb.Get(ctx, "session:9f2c").Result()
if errors.Is(err, redis.Nil) {      // ← redis.Nil = "no such key"
    return ErrLoggedOut
} else if err != nil {
    return err
}
fmt.Println(raw)                     // {"userId":1}

if err := rdb.HIncrBy(ctx, "cart:1", "product:10", 1).Err(); err != nil {
    return err
}
// rdb comes from: rdb := redis.NewClient(&redis.Options{Addr: "localhost:6379"})

What you can't ask Redis is "which carts contain product 10?". There is no index on values, so you'd have to read every key. Key-value stores are perfect for sessions, carts, rate-limit counters, feature flags and caches (see Caching): anything where the app always knows the exact key.

Keys with structure: DynamoDB

DynamoDBDynamoDBAmazon's managed key-value and document database. Every item has a partition key (and optionally a sort key); you query by those keys, and AWS spreads the data across machines for you. extends the idea. Each itemItem (DynamoDB)DynamoDB's word for one record: a set of named attributes, identified by its partition key (and sort key, if the table has one). At most 400 KB. (DynamoDB's word for one record) has a key with two parts:

• a partition keyPartition keyThe part of a record's key that decides which partition (and so which machine) stores it. All records with the same partition key live together and can be read in one request. (PK), which picks the machine the item is stored on. Items with the same PK are kept together.
• an optional sort keySort key / clustering columnThe part of the key that orders records inside one partition. Because they're stored sorted, “all of Ada's orders from October, newest first” is one quick slice. (SK), which keeps those items sorted within their partition.

A Query names one PK and can ask for a range of SKs ("starts with ORDER#2026-10"). It reads only that slice. Anything that doesn't name a partition key needs a ScanFull scanReading every record in a table to find the few you want. Fine for 1,000 rows; at a billion rows it is slow, expensive, and hurts every other query., which reads the entire table.

Single-table design: keys from access patterns

Here's the twist that confuses people coming from SQL. With single-table designSingle-table designA DynamoDB technique: put several kinds of item (customers, orders) in one table, with keys like CUSTOMER#1 / ORDER#100, so items read together share a partition key., you put customers, orders and order items in one table and choose the keys so that each access pattern is one Query. You start from a list of questions, not from the entities:

1. Get a customer's profile.
2. Get a customer and their orders, newest first.
3. Get a customer's orders in one month.
4. Get one order with its line items.

Then you pick keys that put each answer in one partition, sorted the way you'll read it. Pick an access pattern below and watch which items get read:

The same Query from code. The Go program compiles against the AWS SDK for Go v2; DynamoDB isn't available in the sandbox, so no output is shown:

out, err := ddb.Query(ctx, &dynamodb.QueryInput{
    TableName:              aws.String("shop"),
    KeyConditionExpression: aws.String("PK = :pk"), // ← one partition only
    ExpressionAttributeValues: map[string]types.AttributeValue{
        ":pk": &types.AttributeValueMemberS{Value: "CUSTOMER#1"},
    },
    ScanIndexForward: aws.Bool(false), // sort key descending → newest first
})
if err != nil {
    return err
}
// out.Items: the PROFILE item and every ORDER# item for customer 1

The new question nobody planned for

Now the product team asks: "which orders contain the Blue Mug?" No key starts with the product, so the only option is a Scan of every item. Try the last pattern in the widget. The fix is a Global Secondary Index (GSI)Secondary indexAny index other than the one the table is stored in. Its entries point back to the row: by row address in Postgres, by primary key value in InnoDB.: DynamoDB keeps a second copy of the items keyed by another attribute (say GSI1PK = PRODUCT#10). It works, but every new question means new keys, an extra copy of the data, and extra write cost.

That's the core trade of key-value stores, made sharp:

Wide-column and graph

Wide-column: Cassandra

A wide-column storeWide-column storeA database (Cassandra, ScyllaDB, Bigtable) that stores rows grouped into partitions by a partition key and kept sorted inside each partition. Built for huge write volumes across many machines. like CassandraCassandraA distributed database built for huge write volumes spread across many machines. Data is grouped by a partition key and sorted within each partition, which suits “latest messages in this chat” queries. Internally it stores data in LSM trees. spreads data over many machines (often dozens), any of which can accept writes. Its query language, CQLCQLCassandra Query Language: looks like SQL (SELECT … FROM … WHERE), but only allows queries that the table's key layout can answer efficiently — no joins., looks almost exactly like SQL, which is misleading, because the rules underneath are DynamoDB's: you can only ask what the key layout answers.

Cassandra isn't installed in the sandbox; the statements are real CQL and the outputs are illustrative.

CREATE TABLE orders_by_customer (
  customer_id bigint,
  placed_at   date,
  order_id    bigint,
  total_cents int,
  item_names  list<text>,
  PRIMARY KEY ((customer_id), placed_at, order_id)
) WITH CLUSTERING ORDER BY (placed_at DESC, order_id DESC);

-- ✓ one partition, already sorted newest-first on disk
SELECT placed_at, order_id, total_cents FROM orders_by_customer
WHERE customer_id = 1 LIMIT 10;

So far, it's DynamoDB with SQL-looking syntax. The difference shows up when you ask something the key doesn't cover:

cqlsh:shop> SELECT * FROM orders_by_customer WHERE total_cents > 5000;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute
this query as it might involve data filtering and thus may have unpredictable
performance. If you want to execute this query despite the performance
unpredictability, use ALLOW FILTERING"                                  (illustrative)

Cassandra refuses rather than quietly reading every partition on every machine. And there are no joins at all. The answer is query-first modeling: one table per question, each keyed for its query, with the application writing every new order into all of them.

SELECT * FROM orders_by_customer WHERE customer_id = 1;
-- ✓ runs

Graph: Neo4j

Some questions are all relationships: "customers who bought what Ada bought", "people you may know", "is this payment linked to a known fraud ring within 4 hops?". A graph databaseGraph databaseA database (Neo4j, Amazon Neptune) that stores nodes (things) and relationships (links between things) directly, so following links many hops deep is cheap. stores nodes (Ada, an order, the Blue Mug) and relationships (PLACED, CONTAINS) as first-class data. Following a relationship is a direct pointer hop, not an index lookup.

In CypherCypherNeo4j's query language. You draw the pattern you're looking for with ASCII art: (a:Customer)-[:PLACED]->(o:Order)., Neo4j's query language, you draw the pattern you're looking for (illustrative output; Neo4j isn't in the sandbox):

MATCH (a:Customer {name: "Ada"})-[:PLACED]->(:Order)-[:CONTAINS]->(p:Product)
      <-[:CONTAINS]-(:Order)<-[:PLACED]-(other:Customer)
WHERE other <> a
RETURN DISTINCT other.name
// → "Bob"     (illustrative)

The same question in SQL works fine at this size. Real Postgres output:

SELECT DISTINCT other.name
FROM customers a
JOIN orders      ao    ON ao.customer_id = a.id
JOIN order_items ai    ON ai.order_id    = ao.id
JOIN order_items bi    ON bi.product_id  = ai.product_id   -- same product…
JOIN orders      bo    ON bo.id          = bi.order_id     -- …in another order
JOIN customers   other ON other.id       = bo.customer_id
WHERE a.name = 'Ada' AND other.id <> a.id;
--  name
-- ------
--  Bob

Five joins (six tables) for two hops, and it grows from there. Each extra hop adds more joins, and the number of rows in between can multiply at every step. Cypher handles variable depth directly: (a)-[:FOLLOWS*1..4]->(b) means "1 to 4 FOLLOWS hops". SQL needs a recursive query (WITH RECURSIVE) for that, which works but gets slow and awkward on large, highly connected data.

All five, side by side

You've now seen every model storing the same shop. Flip through them and notice how the same facts get arranged differently, and which question each layout makes easy:

Model this: one app, five databases

Pick an app, then a database model. You'll see how that model would store the app's data and what each of three typical questions would cost. The badges mean:

What the patterns tell you

Click through all five apps and a few patterns jump out:

• Relational is almost never ✕ until scale or graph depth. That's the value of joins plus a query plannerQuery planner (optimizer)The part of the database that decides HOW to run your SQL — which index, which join order, scan or lookup — by estimating the cost of each option and picking the cheapest.: new questions are usually just new SQL, maybe plus an index. Its ✕s appear at extreme write volumes and many-hop traversals.
• Key-value and wide-column are ✓ or ✕, rarely ~. They're superb at the questions you designed keys for and helpless at the rest. They reward knowing your access patterns in advance.
• Document sits in between. It's great when one document matches one screen, and it degrades when the data is shared between many documents.
• Graph wins one column decisively (multi-hop relationships) and is an odd fit elsewhere.

Transactions and consistency guarantees

The checkout problem

Checkout must do two things together: create the order and take the items out of stock. If only one happens, you either sell a mug you don't have or lose an order. A transactionDatabase transactionA group of database changes that happen all together or not at all (COMMIT keeps them, ROLLBACK undoes them). How much other users can see or interfere with the half-done state depends on the isolation level. makes several changes atomic: either all of them happen or none do. In Postgres it's BEGIN … COMMIT. First give products a stock column with a rule that it can never go negative, and leave just 1 Blue Mug. Then try to sell 2 of them. Real output:

shop=# ALTER TABLE products ADD COLUMN stock int NOT NULL DEFAULT 0 CHECK (stock >= 0);
ALTER TABLE
shop=# UPDATE products SET stock = 1 WHERE id = 10;
UPDATE 1
shop=# BEGIN;
BEGIN
shop=*# INSERT INTO orders VALUES (103, 2, '2026-10-30');
INSERT 0 1
shop=*# INSERT INTO order_items VALUES (103, 10, 2);
INSERT 0 1
shop=*# UPDATE products SET stock = stock - 2 WHERE id = 10;
ERROR:  new row for relation "products" violates check constraint "products_stock_check"
DETAIL:  Failing row contains (10, Blue Mug, 1200, -1).
shop=!# COMMIT;
ROLLBACK
shop=# SELECT id FROM orders WHERE id = 103;
 id
----
(0 rows)

The stock rule failed, so the order rows inserted a moment earlier vanished too. That's the "A" in ACIDACIDThe four guarantees of a database transaction: Atomic (all or nothing), Consistent (rules hold), Isolated (concurrent transactions don't see each other's half-done work), Durable (once committed, it survives a crash).. Any number of rows, in any tables, one all-or-nothing unit. How isolation between concurrent transactions works (and where it leaks) is the subject of Transactions & Isolation. The classic lost-update bug is in Race Conditions.

Two buyers, one mug

Atomicity matters most when two requests arrive at the same moment. Here is the same race (two buyers, 1 mug left) handled three ways. Step through each:

MongoDB: design so one document is enough

In MongoDB, a write to a single document is always atomic, including every nested field and array inside it. That's a big reason to embed: if an invariantInvariantA rule that must always hold true about your data — “an account balance is never negative”, “area equals width × height”. Code (and transactions) must keep it true before and after every change. (a rule that must always hold, like "stock is never negative") lives in one document, you don't need a transaction at all. "Take 2 mugs, but only if at least 2 are left" is one conditional update:

db.products.updateOne(
  { _id: 10, stock: { $gte: 2 } },   // match only if stock >= 2
  { $inc: { stock: -2 } }            // then subtract 2, atomically
)
// → { matchedCount: 0, modifiedCount: 0 }   when there isn't enough   (illustrative)

When the invariant spans documents (stock in products, the new order in orders), MongoDB offers multi-document transactionsMulti-document transactionA transaction that changes several documents all-or-nothing. MongoDB has them (since 4.0), but they cost more than single-document writes, so designs try to need them rarely. (since version 4.0; 4.2 when the data is split across several machines). They work, but cost more than single-document writes and have time limits, so MongoDB's own advice is to model data so most operations don't need them.

sess, err := client.StartSession()
if err != nil {
    return err
}
defer sess.EndSession(ctx)

_, err = sess.WithTransaction(ctx, func(ctx context.Context) (any, error) {
    res, err := products.UpdateOne(ctx,
        bson.M{"_id": 10, "stock": bson.M{"$gte": 2}}, // only if enough stock
        bson.M{"$inc": bson.M{"stock": -2}})
    if err != nil {
        return nil, err
    }
    if res.ModifiedCount == 0 {
        return nil, ErrOutOfStock // ← returning an error aborts everything
    }
    _, err = orders.InsertOne(ctx, order)
    return nil, err
})
return err

DynamoDB: conditions and small transactions

DynamoDB writes to one item are atomic and can carry a condition (stock >= :n, or attribute_not_exists(PK) for "only create if new"), which gives you safe compare-and-setCompare-and-swap (CAS)An atomic operation: “if the value is still X, change it to Y — and tell me whether that worked.” If someone changed it in the meantime, nothing is written and you can retry.: "write this only if the current value is still what I expect". If another request changed it first, the write is refused instead of silently overwriting. For several items there's TransactWriteItems: up to 100 actions, all or nothing, at about twice the cost of normal writes.

Reads have a twist: by default they're eventually consistent. A read right after a write may return the old value for a moment, because DynamoDB keeps several copies and may answer from one that hasn't caught up (eventual consistencyEventual consistencyA promise that if writes stop, every copy or derived view of the data (replicas, caches, read models, other services) will end up agreeing — but for a while, different readers may see different (stale) versions.). Pass ConsistentRead: true to get the latest value, at twice the read cost.

Cassandra: choose per query

Cassandra keeps each row on several machines, typically 3. On every query you choose how many of those copies must answer: ONE, QUORUM (a majority: 2 of 3) or ALL. This is tunable consistencyTunable consistencyChoosing per query how many replicas must answer (ONE, QUORUM, ALL) — trading speed and availability against reading the latest data.. Call the total number of copies N, the number a write waits for W, and the number a read asks R. The key rule: if R + W > N, every read overlaps with the latest write. Try it:

For "only one user may claim this email" you need compare-and-set ("insert only if nobody has claimed it yet"), which Cassandra offers as lightweight transactionsLightweight transaction (LWT)Cassandra's compare-and-set: INSERT … IF NOT EXISTS or UPDATE … IF col = x. Correct even under concurrency, but several times slower, and limited to one partition.:

INSERT INTO users_by_email (email, user_id) VALUES ('ada@example.com', 1) IF NOT EXISTS;
--  [applied]
-- -----------
--       True          ← a second attempt would print False   (illustrative)

They're correct under concurrency, but several times slower than normal writes, because the replicas must first agree with each other. They're also limited to one partition. Cassandra has no general multi-partition transactions (newer releases are adding them, so check your version).

Summary

Postgres as both: JSONB

A table that's half relational, half document

Our catalog sells mugs, tea and books. They share name and price, but their other attributes have nothing in common. A mug has a color and capacity, tea has an origin, a book has an author. Instead of 30 mostly-empty columns, keep the shared fields as columns and the varying ones in JSONB:

CREATE TABLE catalog (
  id          bigint PRIMARY KEY,
  name        text   NOT NULL,
  price_cents int    NOT NULL,
  attrs       jsonb  NOT NULL DEFAULT '{}'   -- ← the document part
);
INSERT INTO catalog VALUES
  (0, 'Ocean Mug', 1200, '{"kind":"mug","color":"teal","capacity_ml":350,"dishwasher_safe":true}');

To have something to measure, I filled the table with 300,000 generated products (one statement; generate_series(1, 300000) produces the numbers 1 to 300,000, one per row) plus the Ocean Mug:

INSERT INTO catalog
SELECT g, 'Item ' || g, 100 + (g % 5000),
  CASE g % 3
    WHEN 0 THEN jsonb_build_object('kind','mug','color',(ARRAY['blue','red','green','white'])[1 + g % 4],'capacity_ml', 250 + (g % 4) * 50)
    WHEN 1 THEN jsonb_build_object('kind','tea','origin',(ARRAY['Assam','Darjeeling','Uji','Yunnan'])[1 + g % 4],'tags', jsonb_build_array('loose','organic'))
    ELSE        jsonb_build_object('kind','book','author','Author ' || (g % 1000),'pages', 100 + g % 400)
  END
FROM generate_series(1, 300000) g;
ANALYZE catalog;   -- recount rows and values so the planner's estimates are right

Real output from that table (300,001 rows):

shop=# SELECT id, name, attrs FROM catalog WHERE id IN (0, 1, 2);
 id |   name    |                                     attrs
----+-----------+-------------------------------------------------------------------------------
  0 | Ocean Mug | {"kind": "mug", "color": "teal", "capacity_ml": 350, "dishwasher_safe": true}
  1 | Item 1    | {"kind": "tea", "tags": ["loose", "organic"], "origin": "Darjeeling"}
  2 | Item 2    | {"kind": "book", "pages": 102, "author": "Author 2"}

shop=# SELECT name, attrs->>'color' AS color, (attrs->>'capacity_ml')::int AS ml
       FROM catalog WHERE id = 0;
   name    | color | ml
-----------+-------+-----
 Ocean Mug | teal  | 350

Indexing inside the JSON: real EXPLAIN output

Without an index, finding the teal mug means reading every row. EXPLAIN ANALYZE runs the query and reports what Postgres actually did:

shop=# EXPLAIN ANALYZE SELECT id, name FROM catalog WHERE attrs @> '{"color":"teal"}';
 Gather  (cost=1000.00..7251.51 rows=10 width=19) (actual time=23.771..29.845 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on catalog  (cost=0.00..6250.51 rows=4 width=19) (actual time=19.931..19.932 rows=0 loops=3)
         Filter: (attrs @> '{"color": "teal"}'::jsonb)
         Rows Removed by Filter: 100000
 Execution Time: 29.859 ms

"Seq Scan… Rows Removed by Filter: 100000" (in each of 3 parallel workers) means it read all 300,000 rows to return one: about 30 ms. Now add a GIN indexGIN indexGeneralized Inverted Index: maps each element inside a value (a word, an array item, a JSON key) to the rows containing it. Used for full-text search, arrays and JSONB., which maps every key/value inside every document to the rows that contain it. It works like the index at the back of a book, but for JSON:

Here it is on the real table:

shop=# CREATE INDEX catalog_attrs_gin ON catalog USING gin (attrs jsonb_path_ops);
shop=# EXPLAIN ANALYZE SELECT id, name FROM catalog WHERE attrs @> '{"color":"teal"}';
 Bitmap Heap Scan on catalog  (cost=12.86..51.60 rows=10 width=19) (actual time=0.018..0.018 rows=1 loops=1)
   Recheck Cond: (attrs @> '{"color": "teal"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on catalog_attrs_gin  (cost=0.00..12.86 rows=10 width=0) (actual time=0.010..0.010 rows=1 loops=1)
         Index Cond: (attrs @> '{"color": "teal"}'::jsonb)
 Execution Time: 0.037 ms

From 29.9 ms to 0.037 ms, about 800× faster on this machine. "Bitmap Index Scan on catalog_attrs_gin" is the index finding the matching row; "Heap Blocks: exact=1" says it then read just one pagePage (storage)The fixed-size block (often 4–16 KB) a database or OS reads and writes as one unit. Reading one byte means reading its whole page. of the table.

One catch: the GIN index only helps the operators it was built for. The same question written with ->> ignores it:

shop=# EXPLAIN ANALYZE SELECT id, name FROM catalog WHERE attrs->>'color' = 'teal';
 Gather  (cost=1000.00..7713.01 rows=1500 width=19) (actual time=22.000..29.704 rows=1 loops=1)
   ->  Parallel Seq Scan on catalog  (… rows=0 loops=3)
         Filter: ((attrs ->> 'color'::text) = 'teal'::text)
 Execution Time: 29.714 ms

shop=# CREATE INDEX catalog_color ON catalog ((attrs->>'color'));   -- expression index
shop=# EXPLAIN ANALYZE SELECT id, name FROM catalog WHERE attrs->>'color' = 'teal';
 Index Scan using catalog_color on catalog  (cost=0.42..8.44 rows=1 width=19) (actual time=0.026..0.027 rows=1 loops=1)
   Index Cond: ((attrs ->> 'color'::text) = 'teal'::text)
 Execution Time: 0.039 ms

You can also put rules on the document part, so schema-on-write comes back for the fields you care about (real output):

shop=# ALTER TABLE catalog ADD CONSTRAINT attrs_has_kind CHECK (attrs ? 'kind');
shop=# INSERT INTO catalog VALUES (999999, 'Mystery', 100, '{"colour":"blue"}');
ERROR:  new row for relation "catalog" violates check constraint "attrs_has_kind"
DETAIL:  Failing row contains (999999, Mystery, 100, {"colour": "blue"}).

From Go and JavaScript

Both drivers convert JSONB to native values for you. Real runs against the table above:

// pgx v5, using its own API (pgx.Connect) rather than database/sql,
// because it can decode JSONB straight into a struct.
type MugAttrs struct {
    Kind       string `json:"kind"`
    Color      string `json:"color"`
    CapacityML int    `json:"capacity_ml"`
    Dishwasher bool   `json:"dishwasher_safe,omitempty"` // missing key → false
}

var name string
var attrs MugAttrs
err := conn.QueryRow(ctx,
    `SELECT name, attrs FROM catalog WHERE attrs @> $1`,
    map[string]any{"color": "teal"}, // ← encoded as JSON for you
).Scan(&name, &attrs)                // ← JSONB decoded into the struct
if err != nil {
    return err
}
fmt.Printf("%s: %+v\n", name, attrs)
// Ocean Mug: {Kind:mug Color:teal CapacityML:350 Dishwasher:true}

// Writing: pass a struct; pgx turns it into JSON.
_, err = conn.Exec(ctx,
    `INSERT INTO catalog (id, name, price_cents, attrs) VALUES ($1, $2, $3, $4)`,
    500001, "Red Mug", 1100, MugAttrs{Kind: "mug", Color: "red", CapacityML: 300})
// stored as: {"kind": "mug", "color": "red", "capacity_ml": 300}

When "just use Postgres" is right, and when it isn't

- your data is huge and append-heavy and must be
  spread over many machines from day one
- you need MongoDB-specific features your team
  already relies on (change streams, its sharding,
  Atlas search) and know how to run them
- documents are large and updated piece by piece
  very often (Postgres rewrites the whole JSONB
  value on every update)

- some attributes vary per row (product specs,
  settings, form answers, webhook payloads)
- you also need joins, constraints or multi-row
  transactions with the rest of your data
- one machine (plus replicas) handles your load,
  which covers far more apps than people think
- you'd rather run one database than two

Scaling: what "NoSQL scales" really means

The usual path

Scaling a database usually goes through the same stages. Each one buys a lot of headroom, and each later stage costs more complexity than the one before. Step through them:

1. Scale up (vertical scalingScaling up (vertical scaling)Handling more load by moving to a bigger machine: more CPU cores, more RAM, faster disks. Simple, but there's a largest machine you can buy.). Move to a bigger machine. Today's large cloud machines have 100+ CPU cores and terabytes of RAM (approximate, and growing every year). A well-indexed Postgres or MySQL on one such machine can handle tens of thousands of simple queries per second. That's enough for the vast majority of companies. No code changes.
2. Add read replicasReplicaA full copy of a database kept on another machine and updated as the original changes, for reading load and for taking over if the original dies.. Copies of the database on other machines take the reads; the primary takes the writes. Most apps read far more than they write, so this multiplies capacity. The catch is that replicas lag slightly behind, so a user might not see their own change straight away (see Replication). A cacheCacheA small, fast store of answers you already computed or fetched, so the next request for the same thing skips the slow work. The hard part is knowing when a cached answer is out of date. in front helps in the same way.
3. Split the data (partitioning / shardingPartitioning / shardingSplitting one big dataset into pieces (by key range or hash) stored on different machines or files, so no single one has to hold or serve everything.). When writes or data size outgrow one machine, each machine holds only a slice: customers 1–1M here, 1M–2M there. This is the step that's hard for a relational database, for reasons the next part explains. The schemes themselves (range vs hash, hot keys, resharding) are in Sharding.

Why splitting hurts relational databases

Once data is spread across machines, two everyday relational features suddenly need the network:

• Joins across machines. If Ada's orders live on machine A and the Blue Mug's product row on machine B, the join from §02 now fetches rows from both, over the network, for every query. A local join takes microseconds; a network round tripRound-trip time (RTT)How long it takes for a message to go from one machine to another and for the reply to come back. Roughly 1 ms inside a data center, 70–150 ms across an ocean. within a data center takes around half a millisecond (approximate). Repeat that per row and it adds up fast.
• Transactions across machines. The checkout from §09 might touch the order on machine A and the stock on machine B. Making both commit or neither needs a coordination protocol (such as two-phase commit) with extra round trips, and a crashed machine can leave the others waiting.

Run the join case and count the round trips:

Why NoSQL databases didn't have that problem

Look back at the rules DynamoDB and Cassandra impose. Every query must name a partition keyPartition keyThe part of a record's key that decides which partition (and so which machine) stores it. All records with the same partition key live together and can be read in one request.. There are no joins. Atomicity stops at one item or partition. Those aren't arbitrary restrictions: they're exactly the rules that guarantee every operation touches one machine. That's what made it easy for them to grow by adding machines.

The price moved to the data model. You design your keys around your access patterns (§06), copy data into several tables (§07), and handle the rest in application code.

The line is blurring

The "SQL can't scale out" story is a decade out of date:

• Distributed SQLDistributed SQLDatabases (Google Spanner, CockroachDB, YugabyteDB, TiDB) that speak SQL with transactions but automatically split data across many machines. databases (Google Spanner, CockroachDB, YugabyteDB, TiDB) partition data automatically and still offer SQL with multi-row transactions. They use replication protocols (Paxos or Raft) to keep the copies agreed. The cost: transactions that span machines, and especially regions, are slower than on one machine.
• Sharding layers for existing databases: Vitess for MySQL (used at YouTube and Slack), Citus for Postgres.
• NoSQL moving the other way: MongoDB added multi-document transactions, DynamoDB added TransactWriteItems and a SQL-like language (PartiQL), and Cassandra has long offered a SQL-like language.

Changing the shape over time

The example: customers have a single name ("Ada Lovelace"), and the product team now wants first_name and last_name.

SQL: migrations, in safe steps

A database migrationDatabase migrationA versioned script that changes a database's structure — add a column, rename a table — so every environment's database can be updated the same way. is a versioned script that changes the schema. The SQL isn't the tricky part. The tricky part is that old code is still running while you deploy new code: for a few minutes, some servers run the old version and some the new. So no single step may break the version that's still running.

The safe recipe is expand / contractExpand / contractChanging a schema in safe steps: add the new shape alongside the old (expand), move code and data over, then remove the old shape (contract). No step breaks running code.: six steps, alternating between database changes and code deploys. The rule behind every step: whatever the newest code writes, the oldest code still running must be able to read. Step through it, and try skipping ahead to see what breaks:

Here is the whole run on Postgres 16, with the customers table from §02. Between the SQL steps, the statements each code version would send show that nothing breaks (real output):

-- 1. EXPAND: add the new columns, nullable. Old code (v1) doesn't know
--    they exist, and its inserts still work:
ALTER TABLE customers ADD COLUMN first_name text, ADD COLUMN last_name text;
INSERT INTO customers (id, name, email) VALUES (4, 'Cy Young', 'cy@example.com');
-- INSERT 0 1

-- 2. DEPLOY v2: writes BOTH shapes, still reads name.
INSERT INTO customers (id, name, first_name, last_name, email)
VALUES (5, 'Dee Okafor', 'Dee', 'Okafor', 'dee@example.com');
-- INSERT 0 1
--    Meanwhile a v1 server, not yet replaced, renames Dee. v1 only
--    knows name, so first_name/last_name still say Dee Okafor:
UPDATE customers SET name = 'Dee Smith' WHERE id = 5;

-- 3. Once no v1 server runs: BACKFILL in small batches; repeat until it
--    says UPDATE 0. Fix rows with no first_name AND rows where name no
--    longer matches (Dee).
UPDATE customers
SET first_name = split_part(trim(name), ' ', 1),
    last_name  = substr(trim(name), length(split_part(trim(name), ' ', 1)) + 2)
WHERE id IN (
  SELECT id FROM customers
  WHERE first_name IS NULL
     OR trim(name) IS DISTINCT FROM trim(first_name || ' ' || last_name)
  LIMIT 1000);
-- UPDATE 5      (then, run again: UPDATE 0)

-- 4. DEPLOY v3: READS the new columns, still WRITES both. v2 servers
--    still running read name, and v3 keeps filling it in for them.
INSERT INTO customers (id, name, first_name, last_name, email)
VALUES (6, 'Eve Moss', 'Eve', 'Moss', 'eve@example.com');
-- INSERT 0 1

-- 5. Once no v2 runs: let name be empty, then DEPLOY v4, which stops
--    writing name. v3 servers still running don't read name, so a NULL
--    there can't hurt them.
ALTER TABLE customers ALTER COLUMN name DROP NOT NULL;
INSERT INTO customers (id, first_name, last_name, email)
VALUES (7, 'Fay', 'Lin', 'fay@example.com');
-- INSERT 0 1

-- 6. CONTRACT: once no code anywhere reads or writes name,
--    enforce the new shape and remove the old one.
ALTER TABLE customers ALTER COLUMN first_name SET NOT NULL;
ALTER TABLE customers DROP COLUMN name;

After the last step, SELECT * FROM customers prints (note Dee's corrected last name):

 id |       email       | first_name |    last_name
----+-------------------+------------+-----------------
  1 | ada@example.com   | Ada        | Lovelace
  2 | bob@example.com   | Bob        | Ng
  3 | grace@example.com | Grace      | Brewster Hopper
  4 | cy@example.com    | Cy         | Young
  5 | dee@example.com   | Dee        | Smith
  6 | eve@example.com   | Eve        | Moss
  7 | fay@example.com   | Fay        | Lin

Why the backfill also compares name with the new columns: during step 2, old v1 servers can still edit name after v2 has filled in the new columns, as with Dee. Checking only for NULLs would skip her, and after step 6 she'd be "Dee Okafor" forever.

And here is what went wrong when I ran the steps out of order. The first three are real Postgres errors; the last is a real error from a Go v2 server that reads name into a string with rows.Scan, as in §02:

-- Backfilled BEFORE deploying v2, so v1 inserted Cy with first_name NULL; then step 6:
ERROR:  column "first_name" of relation "customers" contains null values

-- Dropped name while v2 servers were still writing it:
ERROR:  column "name" of relation "customers" does not exist

-- Deployed v4 (no name) without first dropping NOT NULL on name:
ERROR:  null value in column "name" of relation "customers" violates not-null constraint

-- Skipped v3: deployed "stop writing name" while v2 servers still read it. v2 loads Fay:
sql: Scan error on column index 0, name "name": converting NULL to string is unsupported

After step 6, every row in the table has the new shape, and the database guarantees it. There's no "version 1 customer" anywhere for a reader to trip over. The cost is three code deploys (v2, v3, v4), four database steps and some patience. A plain ADD COLUMN (even with a constant default) is instant in Postgres 11+; the batched backfill is the slow part on big tables.

Documents: versions and lazy migration

A document store could do the same thing with a script that rewrites every document. Often teams don't. With millions of documents spread over many machines, they choose lazy migrationLazy migrationUpgrading old documents to the new shape only when the app next reads or writes them, instead of all at once. Old and new shapes coexist for a while. instead: each document carries a schemaVersion, and the app upgrades old documents when it next touches them.

type Customer struct {
    ID            int    `bson:"_id"`
    SchemaVersion int    `bson:"schemaVersion"`       // missing in v1 docs → 0
    Name          string `bson:"name,omitempty"`      // v1 only
    FirstName     string `bson:"firstName,omitempty"` // v2
    LastName      string `bson:"lastName,omitempty"`  // v2
}

// upgrade turns any older shape into the current one.
// The bool reports whether anything changed, so the caller can save it.
func upgrade(c Customer) (Customer, bool) {
    if c.SchemaVersion >= 2 {
        return c, false
    }
    first, last, _ := strings.Cut(c.Name, " ") // "Ada Lovelace" → "Ada", "Lovelace"
    c.FirstName, c.LastName, c.Name = first, last, ""
    c.SchemaVersion = 2
    return c, true
}
// upgrade(Customer{ID: 1, Name: "Ada Lovelace"})
// → {ID:1 SchemaVersion:2 Name: FirstName:Ada LastName:Lovelace}, true

Here's how a lazy migration plays out over time:

Where did the integrity checks go?

Count what moved out of the database and into your code as we went from tables to documents:

Choosing like a staff engineer

Four questions, in order

The fourth question is scale, and it comes last on purpose. Put real numbers on it (§11). Most systems never outgrow one well-run relational database plus replicas and a cache. The ones that do usually know it early, because the numbers are obvious (IoT fleets, chat at consumer scale, ad tracking).

Walk through the same questions interactively:

Polyglot persistence

Real systems often use several stores, each for what it's best at. That's polyglot persistencePolyglot persistenceUsing several kinds of database in one system, each for what it's best at — Postgres for orders, Redis for sessions, a search engine for search.:

The column that matters is the last one. Exactly one store owns each fact. Everything else is a read modelRead modelA copy of the data shaped for fast reading (a table, a search index, a cache), built from the events or main records. It can be thrown away and rebuilt.: a copy shaped for a particular kind of read, which you can throw away and rebuild from the source of truth.

The trap: dual writes

The obvious way to keep the search index in sync is to write to both stores from your code:

await db.query("UPDATE products SET name = $1 WHERE id = $2", [name, id]); // ✓ committed
await search.index({ index: "products", id, document: { name } });         // ✗ times out
// Postgres says "Ocean Mug", the search index still says "Blue Mug"
// — and nothing will ever notice or fix it.

That's a dual writeDual writesDuring a migration, writing every change to both the old and the new location so the new one stays up to date.. Between the two calls, the process can crash, the network can fail, or a second request can update the same product in the opposite order. Then the two stores disagree, silently and permanently:

Wrapping both calls in a database transaction doesn't help: the search engine isn't part of it. The fixes all follow one idea: write only to the source of truth, and let the copies follow it.

• Outbox patternOutbox patternWrite an outgoing event into an outbox table in the same database transaction as the change itself; a separate process reads that table and publishes the events. Either both happen or neither does.: in the same transaction as the update, insert a row into an outbox table. A separate worker reads the outbox and updates the index, retrying until it succeeds. See Behavioral patterns.
• Change data captureChange data capture (CDC)Reading a database's own change log (in Postgres, the write-ahead log) as a stream of every insert, update and delete, so another system can copy the changes without the app doing anything.: a tool (Debezium is the best-known) reads Postgres's own write-ahead logWrite-ahead log (WAL)An append-only file where a database records each change before applying it. After a crash it replays the log to recover; replicas can follow it to copy changes. and streams every committed change to the index, in commit order.
• Rebuildable copies: because the index is derived, you can always rebuild it from scratch from Postgres. Schedule regular rebuilds or comparisons to catch drift.

Pitfalls gallery

Check yourself

Decide before you click. Each explanation points back to the section that covers it: Q1 in §03, Q2 in §07, Q3 in §06, Q4 in §10, Q5 in §13, Q6 in §09, Q7 in §05.

CREATE TABLE readings (
  sensor_id text, day date, ts timestamp, value double,
  PRIMARY KEY ((sensor_id, day), ts)
);

await db.query("UPDATE products SET name = $1 WHERE id = $2", [name, id]);
await search.index({ index: "products", id, document: { name } });

Cheat sheet