Chapter 12 · Data layer

Choosing a Database: A Scenario-Based Guide

Not a feature comparison. Every section here exists to answer one question at a time — what's the access pattern, what shape is the data, does it need to be the source of truth — until the choice of Postgres, BigQuery, DuckDB, MongoDB, Redis, or a graph database stops being a matter of taste and starts being a consequence of the answers.

23 sections DuckDB 1.6 · PyMongo 4.17 Walmart catalog + batch inference 10 interview drills Reading time ~2.5 hours

[!] The concrete scenario this chapter keeps coming back to

Walmart's product catalog lives in BigQuery — tens of millions of rows, with every attribute a category needs. A batch job needs to run LLM inference over that catalog on a single GCP VM with 256GB of RAM, and for each product it needs to look up full details, including images, before calling the model. Nothing about that sentence tells you which database to use — it takes the whole chapter's framework to get there, and 12.20 works the answer out in full once that framework exists.

Three more scenarios close the chapter the same way: a full e-commerce platform's data layer, a fraud-detection system that needs to traverse relationships, and a catalog admin tool deciding between Postgres and MongoDB. If you only read one part of this chapter, Part F is where the earlier framework gets used for real.

12.1 Why "which database" is asked backwards

"Should we use Postgres or MongoDB" is almost never the right first question, because it names two products before establishing what either of them would actually need to do. The right first question has no product names in it at all.

[def] A database choice is downstream of four answers, not a preference

Every database decision in this chapter reduces to the same four questions, asked in order: what's the dominant access pattern (12.2), what shape is the data (12.3), does this store need to be the system of record or just a fast copy (12.4), and what does scale and latency actually require. Get the first two right and most of the remaining choice is close to mechanical — get them wrong and no amount of tuning the wrong database fixes it.

[!] "What does the team already know" is a legitimate fifth factor, not a weakness

Chapter 11 made the identical argument about frontend frameworks: a team fluent in Postgres that reaches for it on a workload MongoDB would fit slightly better is usually making the right call, not a lazy one, if the alternative is a team learning a new database's operational quirks under production pressure. This chapter still teaches the underlying reasoning, so that familiarity is a deliberate choice and not an excuse to skip the analysis.

12.2 Access pattern: five shapes of "get me data"

Every database in this chapter was built to make one of these patterns fast, usually at some cost to the others. Naming which one dominates your workload is the single most useful thing you can do before comparing products.

Five access patterns and the question each one asks
Pattern	The question being asked	Example
Transactional (OLTP)	Read or write one record, or a few related ones, correctly and immediately	Placing an order; updating a user's profile
Analytical (OLAP)	Scan and aggregate millions of rows to answer one question	Revenue by category last quarter
Key-based lookup	Given an exact key, return its value as fast as possible	Session data; a cached product page
Full-text or vector search	Given a query, rank the most relevant matches from a large corpus	Product search; semantic document retrieval (chapter 4)
Graph traversal	Follow relationships an unknown number of hops deep	Are these two accounts connected through shared devices?

[+] OLTP and OLAP are optimised for opposite physical layouts

A transactional workload touches a handful of full rows at a time, so row-oriented storage — everything about one record stored together — is fast for it. An analytical workload touches one or two columns across millions of rows, so column-oriented storage — everything about one field stored together — is fast for that instead. This single physical difference is the real reason Postgres and BigQuery are different products rather than one product with different settings; 12.5 through 12.9 build directly on it.

12.3 Data shape: rows, documents, vectors, graphs

Separately from how data is accessed, its natural shape pushes toward some databases and away from others — forcing the wrong shape into the wrong tool is where a lot of avoidable schema pain comes from.

Four shapes and the store built around each
Shape	Looks like	Native store
Tabular, fixed schema	Rows with the same columns, relationships expressed as foreign keys	Postgres, BigQuery, DuckDB
Semi-structured, per-record schema	Records that vary in shape from one another, nested fields	MongoDB; Postgres with JSONB (12.6)
High-dimensional vectors	An embedding representing meaning, compared by similarity, not equality	Qdrant and similar (chapter 4); pgvector inside Postgres
Graphs	Entities where the relationships matter as much as the entities	Neo4j and similar graph-native databases

[retail] A single product record can genuinely need three of these shapes at once

A Walmart catalog item has fixed tabular fields (SKU, price, department), a variable set of category-specific attributes (screen size for a TV, thread count for sheets), and — if you're building semantic search over it — an embedding vector. That's not a sign one database is missing a feature; it's a sign the record has three different shapes glued together, which is exactly the setup 12.17's polyglot persistence section addresses directly.

12.4 System of record vs. derived copy

The fourth question decides how much correctness a store actually needs to guarantee, and it is asked far less often than it should be.

[def] The system of record is the copy that's allowed to be wrong nowhere

Exactly one store should hold the authoritative version of any given fact — the order total, the current inventory count, the user's email address. Every other copy of that fact, anywhere else in the architecture, is a derived copy: built for speed or for a different access pattern, allowed to lag the system of record by some bounded amount of time, and always rebuildable from it if it's lost.

[!] Confusing the two is how "eventually consistent" becomes "silently wrong"

A derived copy being briefly stale is an accepted, designed-for trade-off. A derived copy being treated as if it were the system of record — written to directly, trusted for a decision that needs the current truth — is a bug waiting for a race condition to expose it. Every scenario in Part F names its system of record explicitly for exactly this reason: it is the one design decision every other choice in an architecture has to respect.

[+] This is the same distinction chapter 9 drew for API responses

9.7 warned against overloading response shape with information the client hadn't asked for; the discipline here is a cousin of that instinct — know precisely what a piece of data is before deciding where it lives, rather than letting convenience decide it for you. A cache that's occasionally treated as authoritative is the data-layer version of an API returning fields nobody agreed it owned.

12.5 Postgres: the transactional default

For a workload that reads and writes individual records and needs those writes to be correct even when several happen at once, Postgres is the default for good reasons, not just familiarity.

[def] ACID is the guarantee a transactional store exists to provide

Atomicity: A transaction's writes all happen, or none of them do — there is no partially applied order.
Consistency: A transaction can only move the database from one valid state to another, respecting every constraint declared on the schema.
Isolation: Concurrent transactions don't see each other's uncommitted changes, so two simultaneous writes don't corrupt each other.
Durability: Once a transaction commits, it survives a crash immediately afterward.

[retail] Placing an order is the canonical case ACID exists for

Deduct inventory, create the order record, and charge the payment method — three writes that must all succeed or all fail together. Without atomicity, a crash between steps two and three leaves an order with no successful charge, or inventory decremented twice for the same order under concurrent checkouts. This is precisely the workload row-oriented, transactional storage was built for, and precisely the workload an analytical warehouse in 12.7 is the wrong tool for.

[+] Postgres earned "the default," it didn't inherit it

Beyond ACID compliance, Postgres ships a mature query planner, genuine extensibility (JSONB in 12.6, pgvector for embeddings, PostGIS for geospatial data), foreign keys and check constraints that enforce correctness at the database layer rather than trusting application code to get it right every time, and decades of operational tooling. For a new transactional service with no unusual requirement, it remains the reasonable starting point specifically because the burden of proof sits with any alternative.

12.6 JSONB: flexible fields without leaving Postgres

Before reaching for a document database because "the schema varies by category," it's worth knowing how far Postgres's own flexible-column type goes on its own.

A products table with fixed columns and one flexible onesql

CREATE TABLE products (
    sku          TEXT PRIMARY KEY,
    department   TEXT NOT NULL,
    price_cents  INTEGER NOT NULL,
    attributes   JSONB NOT NULL DEFAULT '{}'
);

-- A TV and a bedsheet set, same table, different attribute shapes
INSERT INTO products VALUES
  ('TV-4471', 'electronics', 42900, '{"screen_size_in": 55, "resolution": "4K"}'),
  ('BED-9012', 'home', 3499, '{"thread_count": 400, "material": "cotton"}');

-- Indexed lookup on a field that only some rows even have
CREATE INDEX idx_products_screen_size
  ON products USING GIN (attributes);

SELECT sku FROM products WHERE attributes @> '{"screen_size_in": 55}';

[def] JSONB is binary, indexed JSON, not a text column that happens to hold JSON

Postgres stores JSONB in a decomposed binary format and can build a GIN index directly over its keys and values, so containment queries like the one above use an index rather than scanning and re-parsing text on every row. This is a meaningfully different capability from a plain TEXT column storing a JSON string, which offers no query-time structure at all.

[!] JSONB solves "flexible fields," not "we don't want to think about a schema"

JSONB is the right tool for a genuinely variable attribute bag on an otherwise well-structured row, exactly like the product example above. It's the wrong tool when it becomes a way to avoid deciding what a table's columns are at all — at that point you've built an unindexed, unvalidated key-value store inside a relational database, and lost the query planner's ability to reason about most of your data. 12.11 covers the document-database version of the same trap.

12.7 BigQuery: warehouse-shaped analytics

The moment the dominant question becomes "aggregate across millions or billions of rows to answer one thing," the row-oriented storage that makes Postgres fast for transactions becomes the reason it's slow for this instead.

[def] Columnar storage: BigQuery only reads the columns a query actually asks for

A query aggregating revenue by category only needs the category and revenue columns, not the other forty columns each row might have. Column-oriented storage keeps each column's values contiguous on disk, so a query like that reads only the data it needs — a large part of why BigQuery can scan billions of rows in seconds for the right kind of query, and a large part of why it's a poor fit for fetching one complete row by its primary key, which is scattered across every column's separate storage.

[+] Serverless means no cluster to size, at the cost of per-query pricing

BigQuery has no instances to provision or scale — a query submitted against a petabyte-scale table just runs, with Google managing the underlying compute allocation. The on-demand pricing model charges primarily for bytes scanned per query, which makes an unfiltered SELECT * across a huge table genuinely expensive in a way a fixed-cost Postgres instance never is — a real operational difference worth knowing before running an exploratory query against a multi-terabyte table.

[!] BigQuery is not built for one-row-at-a-time transactional traffic

Individual row updates and point lookups by primary key work, but they don't play to the engine's strengths and carry meaningfully higher per-operation overhead than a transactional database designed for exactly that pattern. A checkout flow issuing one BigQuery query per order is fighting the tool; BigQuery earns its keep on the aggregate query across the whole catalog, not the single-row lookup this chapter's 12.20 scenario also needs.

12.8 DuckDB: analytics without a server

BigQuery answers "how do I run analytical queries at warehouse scale, with no infrastructure to manage." DuckDB answers a related but genuinely different question: "how do I run analytical queries fast, locally, inside the same process as my code."

[def] DuckDB is an embedded, columnar, single-node analytical engine

There's no server to connect to — import duckdb loads the engine directly into your Python process, the same way SQLite does for transactional workloads. It uses the same column-oriented, vectorised execution strategy that makes BigQuery fast for aggregation, but scoped to whatever compute the single machine running it actually has, rather than a distributed cluster.

Querying a Parquet file directly, no import step, no serverpython

import duckdb

# Reads directly from Parquet on local disk or cloud storage (with the httpfs extension)
result = duckdb.sql("""
    SELECT department, COUNT(*) AS n, AVG(price_cents) AS avg_price
    FROM read_parquet('catalog_export/*.parquet')
    GROUP BY department
    ORDER BY n DESC
""").df()

[+] Reading Parquet and Arrow natively is the feature that matters most for an AI pipeline

DuckDB queries Parquet, CSV, and Arrow files directly, with no separate load step and no schema declared up front — it infers structure from the file itself. Paired with the httpfs extension, it can query Parquet sitting in S3 or GCS without downloading it first. For a batch job that already has data as Parquet — commonly the result of exporting from a warehouse, exactly as in 12.20 — this removes an entire load-and-index step a traditional database would need.

[!] DuckDB scales with one machine's RAM and cores, not with a cluster

It is exceptionally fast for datasets that fit in memory on a single beefy machine, and it can spill to disk for larger-than-memory workloads, but there is no distributed execution across multiple nodes the way BigQuery has. That's a deliberate trade, not an oversight: it's what makes it embeddable with zero operational overhead in the first place, and it's exactly the trade that makes it the right fit for 12.20's single-VM batch job rather than a shared multi-tenant warehouse.

12.9 Postgres vs. BigQuery vs. DuckDB, side by side

All three speak SQL. None of them are interchangeable, because each optimises for a different point in the access-pattern and deployment space from 11.2.

The same query language, three different jobs
	Postgres	BigQuery	DuckDB
Storage layout	Row-oriented	Column-oriented	Column-oriented
Deployment	Server, always running	Fully managed, serverless	Embedded, in-process
Scales by	Vertical, plus read replicas	Distributed cluster, elastic	One machine's RAM and cores
Best at	Many small transactional reads/writes	Aggregating huge tables, infrequently	Fast local analytics on files you already have
Pricing shape	Fixed cost per running instance	Primarily per byte scanned	No separate cost — runs on compute you already pay for

[retail] A genuinely common pattern uses two of the three, one to feed the other

Export a slice of a BigQuery table to Parquet once, then run repeated exploratory or batch-processing queries against that Parquet file with DuckDB on a single VM, instead of re-querying BigQuery — and paying for bytes scanned — on every pass. This isn't a workaround; it's the two engines doing what each is actually good at, and it's the exact shape 12.20 builds out for the Walmart catalog scenario.

12.10 MongoDB: what the document model solves

MongoDB's actual pitch is narrower than "no schema, infinite flexibility" — it solves a specific problem: reading and writing one aggregate object, whole, without reassembling it from several tables on every request.

[def] A document is the whole aggregate, stored and fetched as one unit

A product catalog entry with its variable attributes, its images, and its reviews can live as one BSON document, fetched with a single lookup by its ID. A relational equivalent means that same read is a join across a products table, an attributes table, and a reviews table — correct, but more query work for a shape that was always going to be read and written as one coherent object anyway.

[+] Where this genuinely wins: aggregates that vary in shape and are read whole

A content-management document, a user's activity feed, a product record where every category has different attributes — all cases where the natural unit of work is "the whole object," and where different instances of that object legitimately don't share the same fields. Modeling that in strict relational tables usually means either a wide table full of mostly-null columns, or the entity-attribute-value pattern that JSONB in 12.6 already handles inside Postgres without a second database at all.

[retail] MongoDB's aggregation pipeline is a real analytical capability, with a real ceiling

Stages like $match, $group, and $lookup let MongoDB run genuine multi-stage aggregations over its own collections, which covers a meaningful amount of reporting need without leaving the database. It's still a document store's aggregation engine, not a columnar warehouse's — a "revenue by category across two hundred million orders" query is 12.7's job, not this one.

12.11 The trap: relational data modeled as documents anyway

The most common MongoDB mistake isn't choosing it for the wrong workload — it's choosing it correctly, and then modeling genuinely relational data inside it as if documents were free of the trade-offs tables were built to manage.

Relational data forced into embedded documents

{
  "_id": "order_88213",
  "customer": { "name": "...", "email": "..." },
  "items": [
    { "sku": "TV-4471", "price_at_purchase": 42900 },
    { "sku": "BED-9012", "price_at_purchase": 3499 }
  ]
}
// Product price changes: which copy is correct,
// this order's embedded snapshot, or the catalog?

Embedding what's read together; referencing what changes independently

{
  "_id": "order_88213",
  "customer_id": "cust_5521",     // referenced, not embedded
  "items": [
    { "sku": "TV-4471", "price_at_purchase": 42900 }
    // price_at_purchase is a deliberate historical snapshot,
    // not an accidental duplicate of the live catalog price
  ]
}

[!] Every embedded copy is a decision about staleness, whether or not you meant to make one

The right card above still embeds price_at_purchase — correctly, because an order should show what the customer actually paid, not today's price. That's a deliberate, named snapshot. Embedding a customer's full profile inside every order, by contrast, means updating an email address requires finding and rewriting it in every order document that copied it — the exact multi-document update problem MongoDB's single-document atomicity doesn't cover, and transactions across documents cover only at a real performance cost.

[+] The actual design question is unchanged from relational modeling: what changes independently of what

Two pieces of data that always change together and are always read together are reasonable to embed. Two pieces of data that change on different schedules, or that need to be queried independently at scale, are reasonable to reference and fetch separately — which is the same normalization judgment call a relational schema requires, just made explicitly at write time instead of enforced structurally by foreign keys.

12.12 Redis and the cache-vs-source-of-truth line

A key-value store answers exactly one question fast: given this key, what's the value. Redis is the most common one, and it's used correctly and incorrectly in roughly equal measure.

[def] Redis keeps data in memory, which is the entire source of both its speed and its risk

In-memory storage is what makes sub-millisecond key lookups possible at very high throughput — and what makes data loss on a crash a real possibility unless persistence is explicitly configured. Redis 8 supports durable persistence modes, but the instinct worth keeping is that Redis's default character is "fast and disposable," and treating it otherwise requires an explicit choice, not an assumption.

[retail] Session data and a shopping cart are the textbook correct uses

A logged-in session, a rate-limit counter (chapter 9's 9.22), an in-progress cart before checkout — all data with a natural expiry, all cheap to lose or rebuild, and all needing lookups fast enough that a full database round-trip on every page load would be a genuine user-facing delay. Redis's TTL support fits this shape directly: set an expiry once, and stale session data cleans itself up with no separate job required.

[!] The moment cached data is the only copy, it has quietly become the system of record

If the completed order total only lives in Redis, with no durable database write behind it, then 12.4's derived-copy guarantee has silently broken — a cache eviction or a restart without persistence configured now means real, unrecoverable data loss. The fix isn't "never use Redis for anything important"; it's writing the authoritative copy to Postgres (or wherever the true system of record lives) first, and treating the Redis copy as exactly what it's named: a cache.

12.13 DynamoDB and Cosmos DB, honestly

Both are managed, cloud-native NoSQL databases with a document-and-key-value hybrid model, and both deserve more depth than a decision-framework chapter can give them without turning into a second chapter.

[def] What they're for, in one sentence each

DynamoDB: AWS's fully managed key-value and document store, built around a partition key you design around up front, with provisioned or on-demand throughput and single-digit-millisecond latency at very large scale.
Cosmos DB: Azure's globally distributed, multi-model database, offering a MongoDB-compatible API among others, with tunable consistency levels and throughput billed in Request Units.

[retail] The one design decision both force early and both punish for getting wrong

DynamoDB's partition key and Cosmos DB's partition key both determine how data is physically distributed, and both create a "hot partition" problem if one key value receives disproportionate traffic — unlike Postgres, where a bad index can be added after the fact, a bad partition key strategy in either of these is a much more disruptive fix once real data and traffic already exist against it.

[!] This chapter treats them as a landscape entry, not a deep dive

A full treatment of DynamoDB's single-table design patterns or Cosmos DB's Request Unit optimization deserves its own chapter, in the same way chapter 9 flagged Node.js internals as real material chapter 9 didn't have room for. What matters here is recognising the shape: if a workload needs MongoDB's document flexibility with a specific cloud provider's managed operational model and global distribution story, these are the two names to know exist, evaluated on that provider's own terms.

12.14 What traversal buys you that joins don't

A relational join connects two tables through a key you name in the query. A graph traversal follows relationships whose length you don't know in advance — and that difference, not "graphs are more powerful," is the actual reason a graph database exists as a separate category.

[def] Index-free adjacency: a graph database stores the relationship, not just the keys that imply it

In a graph-native store, each node holds direct pointers to its connected nodes, so following a relationship is a pointer dereference regardless of how large the overall graph is. A relational join, by contrast, looks up matching keys through an index every time — fast for one join, but each additional hop in a chain is another full join, and the cost compounds with depth in a way pointer-following does not.

[retail] "How are these two accounts connected" is the shape traversal was built for

Account A shares a device with Account B, which shares a payment method with Account C, which shares an address with Account D — and the fraud question is whether any path exists between A and D at all, at an unknown depth. A graph query (in Cypher, Neo4j's query language) expresses "find any path up to N hops" directly; the same question in SQL means writing a join for every possible depth up front, or reaching for the recursive query 12.15 covers next.

[+] The tell: does the query need to name a specific depth, or discover it

"Get this order's line items" is always exactly one join deep — a relational join names that depth and is the right tool. "Are these accounts connected somehow" has no depth named in the question at all; discovering however many hops it takes is precisely graph traversal's job, and precisely the case where forcing a fixed number of relational joins means guessing a depth that might be wrong.

12.15 The recursive CTE ceiling, worked

Postgres can do variable-depth traversal too, with a recursive common table expression. Seeing where it starts to strain is more useful than being told graph databases are simply faster.

Finding a connection between two accounts, up to 4 hops, in Postgressql

WITH RECURSIVE connections AS (
    SELECT account_id, connected_account_id, 1 AS depth
    FROM account_links
    WHERE account_id = 'acct_A'

    UNION ALL

    SELECT c.account_id, al.connected_account_id, c.depth + 1
    FROM connections c
    JOIN account_links al ON al.account_id = c.connected_account_id
    WHERE c.depth < 4
)
SELECT * FROM connections WHERE connected_account_id = 'acct_D';

[def] This works. The question is what happens as depth and graph density grow

Each additional level of recursion re-joins account_links against every row found at the previous depth, so the number of rows under consideration can grow multiplicatively with each hop — a graph where each account links to a handful of others turns four hops into a genuinely large intermediate result set. A graph-native store doing the equivalent traversal follows existing pointers instead of re-running a join at every level, which is why the gap between the two widens specifically as depth and connectivity increase.

[+] For shallow, infrequent traversal, the recursive CTE is the right call

A two- or three-hop traversal, run occasionally, against a moderately sized table, is genuinely fine in Postgres — adding a graph database for that is adding an operational dependency to solve a problem that doesn't yet exist. The recursive CTE becomes the wrong tool specifically when depth is unbounded, the graph is dense, or the traversal needs to run frequently enough that its cost compounds into a real bottleneck — the threshold 12.16 and 12.22's fraud scenario both use to decide.

[!] An unbounded recursive CTE with no depth limit is a real production hazard

Leaving out the WHERE c.depth < 4 guard, or having it on a graph with a cycle, means the recursion can run far longer than intended or never terminate at all. Every recursive CTE against a graph-shaped table needs an explicit depth bound, a cycle check, or ideally both — not because the syntax requires it, but because nothing else will stop it from running away.

12.16 When a graph database is overkill

A graph database is a genuine specialist tool, and specialist tools get reached for more often than their actual workload justifies — usually because the data happens to have relationships in it, which is true of almost all data everywhere.

[!] "The data has relationships" is not the same test as "the query needs unbounded traversal"

Orders relate to customers, customers relate to addresses, products relate to categories — virtually every relational schema is, in some sense, a graph. That observation alone justifies nothing. The actual test from 12.14 still applies: does a real query need to discover a path of unknown length, or does every query in the system name a fixed, known number of hops. Most CRUD applications are entirely the second case, foreign keys and joins included.

[+] Adding a graph database means adding a second database to keep in sync

A graph database is very rarely a system's only store — it typically holds relationship data derived from a system of record that lives elsewhere, which means 12.19's synchronization cost applies here directly. That operational overhead is worth paying when traversal is a core, frequent part of the product; it's a real and avoidable cost when it's solving a problem three-hop recursive CTEs, run occasionally, would have handled without a second system at all.

[retail] A recommendation engine is the honest middle case worth knowing

"Customers who bought this also bought" can be built as a graph traversal (co-purchase relationships, walked at query time) or as a precomputed similarity table refreshed on a schedule and served from Postgres or Redis. Real production systems commonly do the second: cheaper to operate, and "occasionally slightly stale recommendations" is a trade most product teams accept happily in exchange for one fewer specialist database to run — a graph database earns its place specifically when the traversal must happen live, at request time, not on a batch schedule.

12.17 Polyglot persistence, stated as a principle

Every section so far has picked one database for one job. A real system almost always needs several databases at once — not as a compromise, but because no single product is built to be simultaneously excellent at all five access patterns from 11.2.

[def] Polyglot persistence: one system of record, plus purpose-built derived stores

The pattern that recurs across every scenario in Part F is the same: exactly one store holds the authoritative data (12.4), and every other database in the architecture holds a derived copy, reshaped for the one access pattern it's good at — a search index for full-text queries, a cache for key lookups, a warehouse for aggregation, a graph for traversal. None of those derived copies compete with the system of record; they each do one job it isn't built to do well.

[!] This is not the same argument as "use every database you've heard of"

Every additional store in an architecture is an additional thing to operate, monitor, back up, and keep in sync (12.19) — a real, ongoing cost that has to be justified by a genuinely distinct access pattern the existing stores don't serve well. A team running Postgres, MongoDB, Redis, and a search engine for a workload Postgres alone could have handled isn't practicing polyglot persistence; it's paying operational tax for no benefit. 12.23 works through exactly this judgment call.

[+] The test: would a dedicated store change the outcome, not just the vocabulary

Before adding a database to an architecture, the honest question is whether the existing system of record, queried directly, is actually failing at the new requirement — too slow, too expensive, or structurally the wrong shape — or whether it would work fine and the new store is being added out of habit. Every scenario in Part F answers that question explicitly before naming which stores to use.

12.18 Vector databases as a fifth category

Chapter 4 covered this access pattern in full depth against a 1.3-billion-vector Walmart catalog; this section places it inside the broader framework this chapter has been building, rather than repeating that chapter.

[def] Similarity search is a genuinely different question from every pattern in 12.2

"Find the rows most similar in meaning to this embedding" has no exact-match key to look up and no fixed join path to follow — it's a nearest-neighbor search over a high-dimensional space, which is precisely what chapter 4's 4.3 and 4.4 covered: approximate search and HNSW indexing exist because exact nearest-neighbor search doesn't scale, the same way B-tree indexes exist because full table scans don't.

[+] pgvector extends Postgres; a dedicated vector database is a different scale decision

For a modest number of vectors alongside otherwise relational data, the pgvector extension adds similarity search directly inside Postgres — one fewer store to run, at the cost of less specialised indexing and scaling ceilings than a purpose-built engine. Chapter 4's decision to run Qdrant at 1.3 billion vectors across four markets is 12.9's "each engine has a scale it's built for" argument applied to vectors specifically: the same shape of trade-off, a different specific threshold.

[retail] A vector database is almost always a derived copy, and 12.4 still applies to it

The embedding index is built from product titles, descriptions, or images that live in some other system of record — a catalog database or warehouse. If that source catalog changes and the vector index isn't re-embedded and re-upserted, search results silently drift out of date against the products they're supposed to represent, which is exactly the staleness risk 12.19 covers next, just with an embedding model in the sync pipeline instead of a plain copy.

12.19 The real cost of a copy: CDC and staleness

Every derived copy from 12.4 needs a mechanism to stay reasonably in sync with the system of record, and that mechanism is a real, ongoing piece of infrastructure, not a detail to wave away.

[def] Change Data Capture: streaming a database's own write-ahead log to other systems

Rather than a separate job periodically re-querying the system of record for changes, CDC tools like Debezium read the transactional database's internal replication log directly — the same stream Postgres uses for its own replicas — and turn each row-level change into an event that downstream systems (a search index, a cache invalidation, a warehouse loader) can consume. This is how a change to an order in Postgres propagates to Elasticsearch or BigQuery within seconds, without the source database's write path knowing or caring that any of those downstream systems exist.

[!] Staleness is not a bug in this design; it's a number you have to actually pick

A derived copy fed by CDC lags the system of record by some real amount — milliseconds under normal load, potentially much longer if the pipeline backs up. "Eventually consistent" is a legitimate design choice for a search index or an analytics table; it stops being legitimate the moment a part of the product silently assumes the derived copy is current when a decision actually needs the true, live value — the exact failure mode 12.4 named directly.

[+] The batch alternative is simpler to reason about, and often good enough

Not every derived copy needs sub-second freshness. A nightly export from Postgres or BigQuery to Parquet, loaded into DuckDB or re-embedded into a vector index the next morning, is a far simpler pipeline than streaming CDC and is entirely sufficient when the product's actual tolerance for staleness is hours, not seconds — exactly the case in 12.20's batch inference scenario, where the catalog snapshot only needs to be as fresh as the last time the batch job ran.

12.20 Scenario: batch LLM inference over a BigQuery catalog

The scenario from this chapter's opening box, worked through the four questions from Part A in order, rather than jumped to as a conclusion.

[!] The setup, restated precisely

The full Walmart product catalog — tens of millions of rows, every category's attributes, image references — lives in BigQuery, the system of record for this scenario. A batch job on a single GCP VM with 256GB of RAM needs to run LLM inference over every product, and for each one it needs the product's attributes and its images available before the model call.

[def] Question 1: what's the access pattern

Every row in the catalog needs to be read exactly once, in bulk, by a single process running on one machine — not looked up by key on demand, not queried interactively. That's a full-scan analytical read, not a transactional or key-based one, and it immediately rules out modeling this as repeated individual queries against any database, BigQuery included.

[def] Question 2: what shape is the data

Tabular, fixed core fields (SKU, department, price) plus per-category attributes — exactly 12.3's three-shapes-glued-together product record, minus the vector, since this job is running inference, not searching embeddings. The images are a fourth shape entirely: large binary objects that don't belong inside a row-oriented or column-oriented table at all, regardless of which database holds the rest of the record.

[+] The resulting architecture: BigQuery, exported once, read locally with DuckDB, images from GCS

Querying BigQuery directly for every one of tens of millions of rows, from a job running on the VM, means paying for bytes scanned on every run and adding network round-trips the VM's own 256GB of RAM doesn't need. The efficient shape: export the needed columns from BigQuery to Parquet in Cloud Storage once, then have the batch job on the VM read that Parquet with DuckDB — fast, columnar, local, and, per 12.8, capable of scanning it directly with no separate load step. Images were never going to live in BigQuery or DuckDB as blobs; they stay in Cloud Storage, with the catalog record holding a URI, fetched only when a specific product's inference call actually needs it.

The resulting pipeline, one store per job
Store	Role in this scenario	Why not something else
BigQuery	System of record for the catalog; queried once to produce the export	Already there; re-querying it per-product would mean paying per-byte-scanned on every inference run
Parquet on GCS	The exported snapshot the batch job actually reads	Columnar, splits cleanly by partition, and readable directly by DuckDB with no load step
DuckDB (on the VM)	Local, in-process querying and joining of the Parquet snapshot during inference	Embedded means zero extra infrastructure on a job that already has 256GB of RAM to use
Cloud Storage (images)	Holds the actual image bytes, referenced by URI from the catalog data	Object storage is what large binaries are for; no analytical or transactional database should hold them directly

[retail] If the export doesn't fit comfortably in 256GB, DuckDB still doesn't require it to

DuckDB can scan Parquet lazily and process it in a streaming fashion rather than fully materialising the entire dataset in memory at once, and a large export can be partitioned by department or date so the job processes one partition's worth at a time. This is 12.4's system-of-record discipline paying off directly: because BigQuery remains the source of truth, the batch job never needs to fully trust or fully load its local Parquet copy as anything more than a working snapshot for this one run.

12.21 Scenario: a full e-commerce platform's polyglot stack

Zoom out from one batch job to an entire platform, and 12.17's principle has to hold across five or six different access patterns at once, each with its own store and its own relationship to the system of record.

One platform, each store earning its place
Need	Store	System of record, or derived copy
Orders, inventory, payments	Postgres	System of record — needs ACID (12.5)
Product search by keyword	A search index (e.g. Elasticsearch)	Derived from the product catalog, kept current via CDC (12.19)
Sessions and cart	Redis	Derived, TTL-expiring (12.12)
Semantic product search / recommendations	A vector database (chapter 4)	Derived, re-embedded on a schedule (12.18)
Company-wide reporting and dashboards	BigQuery	Derived, loaded via batch or CDC pipeline (12.7, 12.19)

[def] Exactly one arrow points away from Postgres for every fact; none point back into it casually

Every derived store in the table above is fed by data flowing out of Postgres, never the other way around for the facts Postgres owns — the search index doesn't decide what the current price is, the warehouse doesn't process a refund. The one common failure mode across platforms shaped like this is a shortcut where an application writes directly to a derived store to "save a round trip," quietly creating a second, competing system of record for that fact in violation of 11.4.

[+] Five databases is not five times the operational cost, if each one is genuinely earning its place

Each store in this table exists because a specific, real access pattern would have been slow, expensive, or structurally awkward against Postgres alone: full-text ranking, sub-millisecond key lookups, similarity search, and petabyte-scale aggregation are all things Postgres can technically attempt and none of them are what it was built to do best. The discipline from 12.17 — would a dedicated store change the outcome, not just the vocabulary — is what keeps this table from growing a sixth row for no real reason.

[!] This is also the moment "which database" quietly becomes "which four or five"

A platform at this stage rarely gets to pick one winner the way a single new service might. The realistic question is almost always which combination, with which store holding the truth for which fact — which is exactly why this chapter opened with four questions instead of a single flowchart ending in one product name.

12.22 Scenario: real-time fraud detection needing traversal

A checkout flow needs to flag an order as high-risk before it completes if the purchasing account is connected — at any depth — to accounts already known to be fraudulent, through shared devices, payment methods, or shipping addresses.

[def] Applying 12.14's test directly: this query does not name its own depth

A fraud ring rarely shares a device directly with every other account in it — the connections chain through several intermediate accounts, and the whole point of the check is discovering how many hops that chain actually takes, not assuming a fixed number in advance. That's precisely the shape 12.14 and 12.15 identified as graph traversal's actual justification, not a borderline case.

[+] The resulting architecture: Postgres stays the system of record; a graph store is a purpose-built derived copy

Orders, accounts, and payment methods still live in Postgres — nothing about needing fast traversal changes what should hold the authoritative transactional data. A graph database is fed the relationship edges (account-to-device, account-to-payment-method, account-to-address) via a CDC pipeline (12.19) from that same Postgres data, and the checkout flow queries the graph store specifically for the traversal check, in real time, before the order completes.

Two stores, two very different jobs, one source of truth
Store	Role	Latency requirement
Postgres	System of record: orders, accounts, payment methods, addresses	Standard transactional latency
Graph database	Derived relationship graph, queried for connectivity at checkout	Must return before the order can complete — typically well under a second

[!] The graph store's freshness requirement is tighter here than in most derived-copy cases

Unlike 12.20's overnight catalog snapshot, a fraud graph that lags real account activity by hours defeats the purpose — a device shared five minutes ago by a newly flagged fraudulent account needs to show up in the traversal check before the next order from a connected account clears. This pushes the CDC pipeline from 12.19 toward near-real-time streaming rather than batch, which is a genuine added cost specifically justified by what's actually being prevented: fraudulent orders completing before the connection is detected.

12.23 Scenario: catalog admin tool — Postgres+JSONB vs. MongoDB

An internal tool lets category managers create and edit product records, where every department has different attributes, and needs real transactional guarantees when inventory counts change. This is the scenario built to force 12.6 and 12.10 to actually compete.

[def] Restating both requirements without picking a product yet

Requirement one: attributes vary by department, which both JSONB and MongoDB's document model handle natively — this alone doesn't decide anything, since 12.6 established that Postgres doesn't need a second database just for flexible fields. Requirement two: inventory adjustments need real transactional guarantees, potentially across more than one record at a time — a stock transfer between two warehouse locations is a multi-row write that must succeed or fail together.

[+] The second requirement is the one that actually decides it

Postgres's multi-row ACID transactions are exactly what a stock transfer needs, with no extra design work. MongoDB has transactions too, but they were added onto a document-oriented model rather than being the foundation it was built around, and reaching for them regularly is a sign the data might genuinely fit a relational shape better than a document one. Since this tool needs multi-record transactional correctness as a core requirement, not an edge case, Postgres with JSONB for the variable attributes is the better-fitting answer here.

[retail] What would have flipped the answer

If the tool only ever created or fully replaced one product document at a time, with no multi-record transactional requirement anywhere in it, MongoDB's document model would have been a perfectly reasonable, arguably more natural fit for "one form, saved as one object." The deciding factor was never the varying attributes — both stores handle that — it was the multi-row transaction requirement 12.5 described as ACID's actual justification.

[!] This is 12.1's whole argument, played out on a single realistic example

Neither database was wrong in the abstract, and a comparison of their feature lists alone wouldn't have settled anything — both support flexible schemas, both support some form of transactions. The answer came from naming the actual dominant requirement precisely enough that one option clearly served it better, which is the entire method this chapter has been applying since 12.1, not a coincidence specific to this example.

12.24 Key takeaways

The twelve things worth remembering

"Which database" is the wrong first question. Access pattern, data shape, and system-of-record status decide the answer; naming a product first just guesses at those answers instead of working them out.
OLTP and OLAP need opposite physical storage layouts. Row-oriented storage is fast for touching whole records; column-oriented storage is fast for aggregating one field across millions of rows. This is why Postgres and BigQuery are different products, not different settings on one product.
A system of record and a derived copy are not interchangeable, even when they hold the same data. Exactly one store should be allowed to be wrong nowhere; every other copy is allowed to lag, and treating a derived copy as authoritative is how "eventually consistent" quietly becomes "silently wrong."
Postgres remains the reasonable transactional default — ACID guarantees, a mature planner, and genuine extensibility (JSONB, pgvector) put the burden of proof on any alternative, not on choosing it.
JSONB solves flexible fields inside an otherwise well-structured row. It stops being the right tool the moment it becomes a way to avoid deciding what a table's columns are at all.
BigQuery and DuckDB share a columnar engine but solve different deployment problems. BigQuery is serverless analytics at warehouse scale, priced per byte scanned; DuckDB is the same columnar speed embedded in-process on one machine, with no separate cost or cluster at all.
MongoDB's real justification is reading and writing one aggregate object as a whole. The common failure mode isn't choosing it wrongly; it's embedding data that changes on independent schedules inside a document anyway, recreating the normalization problem relational schemas already solved.
Redis is fast because it's in memory, which is also its risk. The moment a piece of data only lives in the cache, the cache has quietly become the system of record, whether or not anyone decided that on purpose.
Graph traversal earns its place when a query can't name its own depth in advance. A relational join names a fixed number of hops; a graph traversal discovers however many it takes. Most "the data has relationships" cases are still the first kind.
A recursive CTE in Postgres handles shallow, infrequent traversal fine. It strains specifically as depth and graph density grow, because each hop re-joins the table instead of following an existing pointer the way a graph-native store does.
Polyglot persistence means one system of record plus purpose-built derived stores, not "use every database available." Each additional store needs a genuinely distinct access pattern the existing ones don't serve well, or it's pure operational cost.
Every derived copy needs a real synchronization mechanism, whether CDC or batch export, and that mechanism has a genuine cost. Batch is simpler and often sufficient; near-real-time CDC is justified specifically when the product's actual tolerance for staleness is short, as in fraud detection, not by default.

[def] The one-sentence version

Name the access pattern, the data shape, and the system of record before naming a product; pick the database built for that combination, not the one you've heard of most; and treat every additional store in an architecture as a cost that has to be earned by a genuinely distinct job the existing stores can't do well, not a default to reach for because the data happens to have that shape somewhere in it.

12.25 Interview drills

Database questions in interviews are rarely "list the differences between X and Y." They're almost always a scenario, with the real assessment being whether you ask about access pattern and consistency requirements before naming a product.

1. We're starting a new service. Should we use Postgres or MongoDB?

I'd want to know the dominant access pattern and whether anything needs multi-record transactional guarantees before answering. If the service reads and writes individual records and ever needs two or more writes to succeed or fail together, Postgres is the safer default — ACID transactions are what it was built around, not added onto.

If the honest answer is "the schema varies a lot per record," that alone doesn't decide it, because Postgres handles variable attributes with an indexed JSONB column. MongoDB genuinely wins when the natural unit of work is one whole aggregate object, read and written together, with no multi-record transactional requirement in sight. Absent that, I'd start with Postgres and put the burden of proof on moving away from it.

2. Our catalog is in BigQuery and we need to run batch LLM inference over every row on a single large VM. How would you set that up?

I wouldn't query BigQuery per-product from the job. That's a full-scan read dressed up as millions of point lookups, and on-demand BigQuery pricing charges primarily for bytes scanned, so every run pays repeatedly for data that hasn't changed. Instead: export the columns the job actually needs to Parquet in Cloud Storage once, then read that Parquet locally with DuckDB inside the inference process.

DuckDB fits because it's embedded, columnar, and reads Parquet directly with no load step or server to run — it uses the RAM and cores the VM already has. Images stay in object storage, referenced by URI and fetched only when a specific product's inference call needs them; large binaries don't belong in a warehouse table or a DuckDB file. BigQuery stays the system of record throughout, and the Parquet export is explicitly a disposable working snapshot for that run.

3. When would you add a graph database rather than writing a recursive CTE in Postgres?

The test I'd apply is whether the query can name its own depth. "Get this order's line items" is always one hop and belongs in a join. "Are these two accounts connected somehow" has no depth in the question at all, and that's the case traversal exists for.

Even then, a recursive CTE with an explicit depth bound is genuinely fine for shallow, infrequent traversal on a moderate table — I wouldn't add a second database to avoid one. It breaks down as depth and graph density increase, because each additional hop re-joins the edge table against everything found at the previous level, whereas a graph-native store follows existing pointers. So: unbounded depth, dense graph, or traversal on the hot path at request time. That combination justifies it; "our data has relationships" doesn't.

4. What's the difference between a system of record and a derived copy, and why does it matter operationally?

The system of record holds the authoritative version of a fact — exactly one store per fact. A derived copy is a reshaped version of that fact living somewhere else to serve a different access pattern: a search index, a cache, a warehouse table. Derived copies are allowed to lag, and must always be rebuildable from the source.

It matters because it tells you what's safe to lose and what isn't. Losing a derived store is a rebuild; losing the system of record is real data loss. It also tells you which writes are legal — an application writing directly into a derived store to save a round trip has quietly created a second competing source of truth, which is where "eventually consistent" turns into genuinely wrong data nobody can reconcile afterward.

5. Our product attributes vary by category. Does that mean we need a document database?

No, and this is the most common reason teams reach for one unnecessarily. Postgres stores JSONB as decomposed binary rather than text, and a GIN index over that column supports indexed containment queries — so a table with fixed columns for SKU, department, and price plus one JSONB attributes column handles category-varying attributes without a second database.

What would actually justify a document database is a different requirement: the natural unit of work being one whole nested aggregate, read and written together, without multi-record transactional needs. I'd also flag the JSONB failure mode — it's the right tool for a variable attribute bag on a structured row, and the wrong one if it becomes an excuse to put everything in an untyped blob the query planner can't reason about.

6. Why not just use BigQuery for everything, since it scales and there's no cluster to manage?

Because its storage layout and pricing model are both built around a specific pattern: scanning and aggregating large numbers of rows over a few columns. Column-oriented storage means fetching one complete row by primary key is assembling it from many separately stored columns, which is exactly what row-oriented transactional databases avoid.

On-demand pricing charges primarily for bytes scanned, so a workload of many small frequent queries has a cost profile that scales with traffic in a way a fixed-cost Postgres instance doesn't. And there's no meaningful multi-row ACID transaction story for something like a checkout flow. It's excellent at the aggregate query across the whole catalog and the wrong tool for the single-row lookups an application serves constantly.

7. When is Redis the wrong choice, given how fast it is?

When the data it holds is the only copy. Redis is in-memory first, which is precisely why it's fast, and while it supports durable persistence modes, its default character is fast and disposable. If a completed order total lives only in Redis with no durable write behind it, an eviction or a restart is unrecoverable data loss, and the cache has silently become the system of record.

It's the right choice for data with a natural expiry that's cheap to rebuild: sessions, carts before checkout, rate-limit counters, cached read-heavy responses. TTL support means that data expires itself with no separate cleanup job. The rule I'd apply is that the authoritative write goes to the durable store first, and Redis holds exactly what its name says — a cache.

8. Walk me through the data layer for a full e-commerce platform.

Postgres as the system of record for orders, inventory, and payments, because those need ACID guarantees. Then derived stores, each justified by an access pattern Postgres doesn't serve well: a search index for keyword product search, a vector database for semantic search and recommendations, Redis for sessions and carts, and BigQuery for company-wide reporting.

The important part is the direction of flow — every derived store is fed from Postgres, via CDC for the ones needing freshness and batch export for the ones that don't, and none of them decide facts Postgres owns. The failure mode I'd watch for is an application writing directly to a derived store as a shortcut, because that creates a second competing source of truth. I'd also want each store to justify itself: five databases is fine if each solves a real problem, and pure operational tax if one of them is there out of habit.

9. How do you keep a search index or vector index in sync with the source database?

Two realistic options, chosen by how much staleness the product actually tolerates. Change Data Capture reads the source database's replication log directly and turns each row change into an event downstream systems consume, which gets propagation down to seconds without the source's write path knowing those consumers exist. Batch export on a schedule is far simpler to operate and entirely sufficient when tolerance is measured in hours.

For a vector index specifically there's an extra step: changed records have to be re-embedded before upserting, so the pipeline includes a model call, not just a copy. Either way I'd pick the staleness budget explicitly rather than discovering it in production — a catalog search index lagging an hour is usually fine, while a fraud-detection graph lagging an hour defeats its own purpose.

10. A team wants to add a fourth database to a system that already has three. How do you evaluate that?

I'd ask whether the existing stores, queried directly, are actually failing at the new requirement — measurably too slow, too expensive, or structurally the wrong shape — or whether the new store is being added because the data superficially resembles what that product is known for. "Our data has relationships" or "our schema varies" are the two most common versions of the second case, and neither justifies a new database on its own.

If it does clear that bar, the follow-up questions are what its system-of-record relationship is, what feeds it and how stale it's allowed to be, and who operates and monitors it. Every store is an ongoing cost in backups, monitoring, and sync machinery, so it needs to be earning that. I'd also weigh what the team already runs competently — a slightly better-fitting database nobody has operated before frequently loses to a well-understood one in practice.

Where this leaves you

You can now answer "which database" without guessing: name the access pattern, name the data shape, name which store owns the truth, and most of the choice follows from those three answers rather than from which product is most familiar or most discussed. The four scenarios in Part F are the method applied end to end — including the one this chapter opened with, where the right answer turned out to be four stores, each doing one job, rather than a single database doing all of them adequately.

The thread running through this chapter was the same one chapters 9 and 11 kept returning to: understand what a tool is actually built for before adopting it, and treat every additional moving part in an architecture as a cost that has to be earned. A derived copy with no clear owner, a cache quietly holding the only version of a fact, or a specialist database solving a problem that never existed are all versions of the same mistake — adding machinery faster than adding clarity about what it's for.

Chapters 1 through 8 built the AI stack, chapter 9 built the API in front of it, chapter 10 built the interface a person actually uses, and this chapter built the layer all of them ultimately read from and write to. What remains planned — the JavaScript language and event loop underneath chapter 11, deeper Node.js and backend material, and the platform and delivery track — picks up from here.