Chapter 12: NoSQL and Specialized Stores

"Nobody chooses six databases. Everybody ends up with six databases."

Overview

Chapters 7 through 11 covered the stores a data platform is designed around: a relational source, a warehouse, a lake, a lakehouse. This chapter covers the ones it accumulates.

A key-value store arrived because the session lookup was too slow. A document store arrived with an acquisition. A search index arrived because someone needed fuzzy matching that SQL could not do. A time-series database arrived when the metrics pipeline outgrew Postgres. A vector store arrived last year with a semantic search feature. Each decision was reasonable at the time and made by someone solving the problem in front of them.

You will extract from all of them, and each has a different data model, a different set of guarantees, and — the part that matters most and gets discussed least — a different answer to how do I get changes out of this thing.

The chapter is organized around that last question. A store's data model determines what it is good at; its change feed determines how much work it is going to be for you. A store with no change feed and no reliable modification timestamp is one you will end up full-scanning nightly, and that constraint should be part of the adoption decision rather than a discovery afterwards.

There is also a corrective here. Three of these store classes are adopted unnecessarily more often than not, and §12.10 names them with the question that tests each. This is not scepticism about NoSQL — every store in this chapter is excellent at the thing it was built for. It is scepticism about adopting a seventh system, which Chapter 5 §5.1 priced at roughly two systems per engineer.

In this chapter, you will learn to:

• Explain why platforms accumulate specialized stores and what each was bought to solve.
• Match an access pattern to a store class and name what the class gives up.
• Describe the data model of each of six classes.
• Design an extraction strategy for each — including the ones with no change feed.
• Recognize the three most-over-adopted stores and the question that tests each.
• Explain what a vector store does, and why it is your problem rather than the ML team's.
• Decide whether a proposed store should be adopted, replaced, or absorbed.

Who needs this chapter: on the Platform path, all of it. Everyone else should read §12.1, §12.9, and §12.10, and treat §12.2 through §12.8 as a reference to return to when one of these turns up.

12.1 Why a Platform Ends Up With Six Databases

Kestrel has, alongside PostgreSQL and its warehouse:

Five stores, five reasonable decisions, and nobody ever decided to run six databases.

The pattern is worth naming because it is not a failure of discipline. Each was adopted to solve a specific problem that the existing store genuinely could not solve well, by a team under delivery pressure, correctly. What is missing is not judgment on any individual decision — it is that nobody counts, and Chapter 5 §5.1's systems-per-engineer heuristic is the count.

The one honest reason to add a store

An access pattern that the existing store serves badly enough to matter, measured.

That is the whole test, and the word doing the work is measured. Three failure shapes appear when it is skipped:

"Postgres can't do that." Frequently it can, and better than the alternative. PostgreSQL has full-text search, JSONB, arrays, ranges, LISTEN/NOTIFY, and — via extensions — time-series partitioning (TimescaleDB) and vector similarity (pgvector). §12.10 covers where the boundary actually is.

"It'll scale better." Chapter 5's Case Study 2: nine months and two engineers to make a four-second query two seconds faster, at 15.5 GB. Measure first.

"The team knows it." A real consideration and a bad primary reason. Chapter 5's rule applies: name the second operator before adopting.

💸 Cost Check — what a sixth database costs before it stores anything

The licence or the instance is the cheap part, and it is the only part that appears in the proposal.

Kestrel's smallest specialised store is a three-node Redis cluster used for one lookup:

text managed instances, 3 x cache.m5.large ~$310 / month a non-production copy, because you need one ~$155 / month backups, monitoring, log retention ~$40 / month ──────────── ~$505 / month = ~$6,060 / yr

And then the part that is not money. Every store adds: an upgrade path someone must own, a backup that someone must have restored at least once, an access-review row (Chapter 30), a deletion mechanism (Chapter 31), an extraction job (§12.9), an on-call runbook (Chapter 26), and a second operator who can be woken up.

Kestrel has four data engineers, and counting PostgreSQL, the lake, and the warehouse it runs eight stores — seven of which can page someone. That is 1.75 pageable systems per engineer against Chapter 5 §5.1's ceiling of 2.0: not yet over, and arrived at without anyone deciding. The honest question at adoption is therefore not "can we run this?" but "which one do we retire when this arrives?"

The comparison that decides most of these arguments: a pgvector index on the Postgres you already run costs an index, and the specialised vector database costs a system. Below the scale where the specialised store actually wins, that is the whole difference.

12.2 Key-Value Stores

Model: a dictionary. GET key and PUT key value. The value is opaque — bytes, a string, or a simple structure the store understands.

Examples: Redis, Memcached, DynamoDB (with a caveat, below), etcd, RocksDB (embedded).

What they are exceptional at: single-key lookup at very low latency. Sub-millisecond is normal. There is essentially no query planning to do, which is why they are fast.

What they give up: everything else. No queries by value, no joins, no aggregation, no secondary access paths unless you build them yourself. If you find yourself scanning a key-value store, you have chosen the wrong store — the operation exists and is a red flag.

Where Kestrel uses one: Redis for sessions, cart contents, rate-limit counters, and a cache in front of the product catalogue.

DynamoDB is not quite a key-value store

Worth separating because it is commonly described as one. DynamoDB has a partition key and an optional sort key, and the pair gives it a genuinely different capability: efficient range queries within a partition. WHERE customer_id = ? AND order_date BETWEEN ? AND ? is a native operation, not a scan.

That makes it closer to a wide-column store (§12.4) in capability, and it means the modelling discipline is different: you design the key structure around your access patterns, in advance, and adding a new access pattern later frequently means adding a global secondary index or rewriting the table.

Extracting from a key-value store

This is the hard part, and it is why key-value stores are a poor place for data you will need analytically.

The rule Kestrel adopted after one bad experience: a key-value store is never the system of record for anything analytics needs. Sessions live in Redis and the same events go to Kafka. Redis is the fast path; Kafka is the durable one. If the only copy of something is in Redis, you will lose it, because Redis is configured for speed and its persistence settings are usually tuned accordingly.

⚠️ Failure Mode — The cache that became a source of truth

A cart service stored cart contents in Redis with a 30-day TTL, and wrote the cart to Postgres only at checkout. Reasonable: an abandoned cart is not a business record.

Then marketing asked for abandoned-cart analysis, and someone wrote a nightly job that scanned Redis with SCAN, serialized every cart, and landed it. It worked, so it stayed.

Three things went wrong over the following year:

1. SCAN on a large keyspace is slow and the results are inconsistent. Redis's SCAN gives a weak guarantee — keys present for the whole iteration are returned at least once; keys added or removed during it may or may not be. The nightly extract silently missed a varying fraction.
2. A Redis restart lost eleven hours of carts. Persistence was appendfsync everysec, correct for a cache and not for a source of record.
3. The TTL silently deleted the analytical history. Nobody had connected "30-day cache expiry" to "we want year-over-year abandoned-cart trends."

The fix was not a better extract. It was to emit a cart-changed event to Kafka and build the analysis on the event stream, leaving Redis as a cache. Two days of work and the problem was gone permanently.

When someone asks for analytics on data that lives only in a cache, the answer is to start emitting events, not to build a better scanner.

12.3 Document Stores

Model: collections of self-describing documents, usually JSON or BSON. Documents in a collection need not share a schema.

Examples: MongoDB, Couchbase, DocumentDB, Firestore. And — genuinely — PostgreSQL with JSONB, which covers a large fraction of document-store use cases.

What they are good at: aggregate-oriented access, where you read and write a whole entity at once. A product with variants, images, attributes, and reviews is one document; fetching it is one read rather than a five-table join. Schema flexibility is real when documents genuinely differ.

What they give up: joins across collections are limited and slow. Multi-document transactions exist in modern MongoDB and are more expensive than a relational equivalent. And schema flexibility becomes schema chaos without discipline — Chapter 7 §7.4's JSONB warning at collection scale.

Extraction

Change streams — MongoDB's are genuinely good: ordered, resumable via a resume token, and covering inserts, updates, deletes, and — importantly — the pre-image of an update if configured. This is the closest thing to Debezium-quality CDC outside the relational world, and it makes MongoDB one of the easier non-relational sources to ingest from.

The problem is not the change feed; it is the schema. A collection of documents with no enforced schema lands in a warehouse as… what, exactly? Three approaches:

Kestrel does the first in bronze and the third in silver, which is §11.5's layered answer applied to a document source. The quarantine is the part people skip and the part that makes the third option honest: documents that do not match the silver schema go to a side table with a reason, and a weekly report lists them. Without the quarantine, "define an explicit schema" is just "silently drop what does not fit."

12.4 Wide-Column Stores

Model: rows identified by a partition key, containing a potentially large and variable set of columns, physically clustered by a sort key within the partition.

Examples: Cassandra, ScyllaDB, HBase, Bigtable. DynamoDB behaves similarly.

What they are good at: enormous write throughput, linear scalability, and range queries within a partition. Time-series-by-entity is the canonical fit: all events for device X between two timestamps is one partition read.

What they give up — and this is the part that surprises relational engineers: you must design the table around the query, in advance. There are no ad-hoc queries. Adding a new access pattern usually means adding a new table containing the same data, written at the same time.

Denormalization is not a performance optimization here; it is the data model. A Cassandra schema frequently contains the same facts in four tables because there are four access patterns, and keeping them consistent is application logic.

Extraction

The weakest area of this class.

Cassandra has CDC, and it is awkward: it writes commit-log segments to a directory that you must process yourself, per node, with deduplication across replicas because the same mutation appears on every replica. Most teams do not use it.

What most teams do instead: dual-write to a message bus at the application layer, or full-scan with token() range pagination on a schedule. Full-scanning a large Cassandra cluster is expensive and disruptive, and the token-range approach exists precisely because a naive SELECT * does not work at all.

Bigtable and HBase are similar: scan-based extraction, no general change feed.

The practical consequence for adoption: if data in a wide-column store will be needed analytically, plan the extraction path at adoption time. The usual answer is that the application publishes events as well as writing to the store, which is the outbox pattern (Chapter 36 §36.4). It costs a little at write time and removes an entire class of problem.

12.5 Time-Series Databases

Model: measurements indexed by time and by a set of tags or labels. cpu_usage{host="a", region="us-east"} = 0.73 @ 1764300862.

Examples: Prometheus, InfluxDB, TimescaleDB (a PostgreSQL extension), VictoriaMetrics, Amazon Timestream.

What they are good at, and each of these is a genuine advantage over a relational table:

• Compression. Timestamps are monotonic and values change slowly, so delta-of-delta encoding on timestamps and XOR encoding on floats achieve compression ratios that are not available to a general-purpose store.
• Automatic retention and downsampling. Keep raw data for 15 days, five-minute averages for 90, hourly for two years — declared as policy rather than implemented as jobs.
• Time-aware query functions. Rate, increase, moving average, and gap-filling as first-class operations rather than as window-function gymnastics.

What they give up: joins, and general-purpose querying. A time-series database is not where you put business data that happens to have a timestamp.

That distinction is the one that matters, and it is where the over-adoption happens:

Kestrel's clickstream is not time-series data, despite having timestamps. It has millions of distinct session identifiers, it needs joining to customers and products, and events get corrected. It belongs in the lakehouse.

📏 Scale Note — Cardinality is what breaks a time-series database

The failure mode of a time-series database is not volume. It is cardinality — the number of distinct label combinations — and it is worth understanding because it is counter-intuitive and it arrives suddenly.

Most time-series databases maintain an in-memory index per unique series. A series is one combination of metric name and all label values. Adding a label with high cardinality multiplies the series count:

```text http_requests{method, status, endpoint} 4 methods x 6 statuses x 200 endpoints = 4,800 series fine

• customer_id (1.9M values) 4 x 6 x 200 x 1,900,000 = 9,120,000,000 series catastrophic ```

Nine billion series is not a slow query; it is an out-of-memory kill on the database, usually during an incident, when someone added a label to help debug something.

The rule: never put an unbounded identifier in a label. Customer id, session id, request id, user email, full URL, error message — all forbidden. If you need per-customer analysis, that is a warehouse question.

This is the single most common way a metrics stack is taken down, and the person who does it is almost always trying to improve observability.

12.6 Search Engines

Model: an inverted index — a map from every term to the documents containing it — plus relevance scoring.

Examples: Elasticsearch, OpenSearch, Solr, Typesense, Meilisearch, and PostgreSQL's built-in full-text search.

What they are good at: full-text search with tokenization, stemming, and stop words; typo tolerance via fuzzy matching; relevance ranking, which is genuinely hard and is the thing you cannot easily build; faceted navigation; and geospatial search.

What they give up: they are not databases. They are eventually consistent, they have no transactions, and treating one as a system of record ends badly. An index is derived data; the source of truth lives elsewhere and the index is rebuilt from it.

Where Kestrel uses one: Elasticsearch for product search. The source of truth is kestrel_app.products; the index is populated from CDC and can be rebuilt from scratch in about twenty minutes, which is the property that makes it safe to depend on.

Extraction

Usually you should not. The index is derived from a source you already have, so extract from the source and skip the index.

Two exceptions worth knowing:

Search analytics — the queries users typed, what they clicked, what returned nothing. This is genuinely valuable data that exists only in the search layer, and it should be emitted as events to Kafka rather than scanned out of the search engine.

Relevance features — the score a document received, which position it appeared in. Same answer: emit as events.

🏭 From the Pipeline — Zero-result searches nobody was recording

Kestrel's search returned nothing for about 3.1% of queries. Nobody knew this, because the search engine logged queries but the logs were rotated after seven days and nobody read them.

A data engineer, building the clickstream schema, added a search event type carrying the query string, the result count, and the position of any click. Three months of data later, the zero-result queries clustered:

Cluster	Share of zero-result queries
Brand names Kestrel did not carry	34%
Misspellings the fuzzy matcher missed	26%
Product categories that existed under a different name	19%
Genuinely absent products	21%

45% of zero-result searches — the misspellings and the naming mismatches — were solvable, and two of them by adding synonyms to the index, which took an afternoon.

The general lesson: the most valuable data a specialized store holds is often the data about how people used it, and that data usually exists only as logs with a short retention. Emit it as events. The store's own logs are not a data source; they are an operational convenience with a retention policy nobody chose deliberately.

12.7 Vector Databases

The newest class, and the one you are most likely to meet as a new requirement.

Model: vectors — fixed-length arrays of floats, typically 384 to 3,072 dimensions — with approximate nearest neighbour search over them.

Examples: pgvector (a PostgreSQL extension), Pinecone, Weaviate, Qdrant, Milvus, Chroma, and vector support inside Elasticsearch, Redis, and several warehouses.

What they are for: semantic similarity. An embedding model converts text, an image, or a product into a vector such that similar things are near each other. "Find items similar to this one" becomes "find the nearest vectors."

What they are good at: approximate nearest neighbour search at scale. Exact nearest neighbour is $O(n)$; ANN indexes (HNSW, IVF) trade a small amount of recall for enormous speed.

What they give up: exactness, and — the part that matters to you — the vectors are derived data with a dependency you must manage.

Why this is a data engineering problem

Three reasons, and the third is the one that gets missed:

1. Embeddings must be generated and kept fresh. Every product needs a vector. When a product's description changes, the vector is stale. That is a pipeline, with all the incremental-processing questions of Chapter 20.

2. The embedding model is a dependency with a version. Vectors from two different model versions are not comparable — they live in different spaces. Upgrading the model means re-embedding everything, and a partial re-embed produces silently wrong results rather than errors.

3. The vector is not the answer. A similarity search returns ids and scores. Turning that into something useful requires joining to real data — the product, its price, its stock. The vector store is one component of a serving path, and the joins are yours.

⚠️ Failure Mode — The half-re-embedded index

A team upgraded their embedding model from 768 to 1,536 dimensions. The new model was better on every benchmark.

They re-embedded the catalogue with a batch job. It processed 41,000 of 47,000 products and then failed on a product whose description contained a character the tokenizer rejected. The job was retried, succeeded on the remainder, and everyone moved on.

The index now contained vectors from two different models. Not an error — the dimensions differed, so the store rejected the mismatched ones outright, which meant 6,000 products were silently absent from every similarity search while remaining present in the catalogue.

Nobody noticed for five weeks, because absence from a recommendation list is invisible. It was found when a merchandiser asked why a bestselling jacket never appeared in "similar items."

Three defenses:

1. Version the index, not the rows. Build products_v2 alongside products_v1, and switch the reader only when the new index is complete. Same pattern as Chapter 9 §9.6's compaction swap, and the same reason.
2. Store the model version with every vector, and assert that a query's index contains exactly one version.
3. Assert coverage. COUNT(*) in the index equals COUNT(*) of embeddable rows in the source. Nine words of SQL, and it is the check that catches this class of problem regardless of cause.

The general shape: derived data with a versioned generator needs a completeness check, because partial regeneration is silent. This is the same failure as Chapter 6's promotion_attribution_method — and the same fix, a version recorded with the data.

Do you need a dedicated vector database?

Frequently not, and this is §12.10's list. pgvector puts vector search inside PostgreSQL, which means one system instead of two, and joins to your real data are ordinary SQL joins rather than an application-layer stitch.

pgvector is sufficient when: under roughly 10 million vectors, query volume is moderate, and you want the vectors alongside relational data — which describes most catalogue-similarity and document-search use cases.

A dedicated store earns its place when: hundreds of millions of vectors, very high query throughput, or you need features PostgreSQL lacks — hybrid sparse-dense search, multi-tenancy at index level, or specific ANN algorithms.

Kestrel uses pgvector. 47,000 products is four orders of magnitude below where a dedicated store becomes necessary, and the join to price and stock is the whole value of the feature.

12.8 Graph Databases

Briefly, because they are the class you are least likely to meet in a data platform.

Model: nodes and edges with properties, queried by traversal. Examples: Neo4j, Amazon Neptune, and the graph extensions of several other stores.

Good at: variable-depth traversal — "customers who bought products also bought by customers who bought this," fraud rings, supply-chain dependency paths. A query whose depth is not known in advance is a recursive CTE in SQL and a natural expression in a graph query language.

What they give up: aggregate analytics, and general-purpose querying. Most "graph" problems in a data platform are two or three joins deep and are better served by SQL — and Chapter 18 covers recursive CTEs, which handle a surprising amount of what people reach for a graph database to do.

Extraction: varies, generally weak. Plan it at adoption.

12.9 Extracting From Each

The table that determines how much work a store will be. This is the one to consult before adoption, not after.

Three rules fall out of this table:

Extract from the source of truth, not from a derived store. Search indexes and vector stores are derived; the source has better guarantees and a better change feed.

A store with no change feed will be full-scanned. Price that at adoption. A nightly full scan of a large store is expensive, disruptive, and it misses deletes (Chapter 2 §2.2).

When the store cannot tell you what changed, make the application tell you. The outbox pattern (Chapter 36 §36.4) — the application writes the change and an event in one transaction — is the general answer, and it works for every store in the table.

🔐 Privacy & Governance — an erasure request has to reach all six

This is the cost of polyglot persistence that nobody prices at adoption, and it arrives as a legal obligation two years later.

A deletion request for one customer, at Kestrel:

text store holds deletion mechanism ────────────────────────────────────────────────────────────────────────────── PostgreSQL the customer row DELETE. Easy. S3 bronze/silver orders, clickstream partitioned rewrite (Ch. 31) Redis a session cache TTL -- so it expires. Verify that. Elasticsearch the indexed order documents delete by query; reindex lag MongoDB returns, from the acquisition DELETE -- if anyone knows it is there pgvector embeddings of their reviews DERIVED, and not obviously personal Snowflake every mart built from them rebuild (Ch. 34)

Two rows get missed, for opposite reasons.

The embeddings, because they do not look like personal data. A vector is a lossy numeric derivative of a review, it appears in no schema as a customer_ anything, and it is reconstructible enough to matter. Nobody puts it in a manifest they are writing by hand.

The MongoDB, because it arrived with an acquisition and its contents were never inventoried. A store nobody chose is a store nobody classifies, and §12.1's list is where that becomes visible.

Chapter 31's rule therefore applies without modification: the deletion manifest must be generated from classification tags, not written by hand — precisely because a hand-written one names the five stores its author happens to remember.

The design consequence, and it is the reason this callout is in Chapter 12 rather than Chapter 31: the time to decide how a store will forget is at adoption, alongside §12.9's extraction question. A store you cannot selectively delete from is a store you will eventually have to rebuild, and finding that out during a regulatory deadline is the expensive way.

🧱 Kestrel Platform — six stores, and what each one earned its place with

text store serves measured first? own system? ───────────────────────────────────────────────────────────────────────────────── PostgreSQL transactional orders, customers n/a, it was first yes S3 + Iceberg analytical scans yes yes Snowflake concurrent BI yes yes Redis (2019) session lookup on every page yes yes Elasticsearch (21) typo tolerance, relevance, facets yes yes MongoDB (2023) an acquired product's returns NO yes Prometheus (2024) infrastructure metrics yes yes pgvector (2025) product similarity, 47k products yes NO

Read the last two columns together, because they are the whole callout.

Seven of the eight were a measured decision. MongoDB was not — it arrived with an acquisition, and a store nobody chose is a store nobody re-examines. It is the retirement candidate, and the returns data it holds has never been inventoried, which is the §12.9 and the 🔐 problem above in one line.

And pgvector is the only row with no in the last column, which is the reason it caused no argument: it is an extension on a system already running, already backed up, already on call, already in the access review. 47,000 products is four orders of magnitude below where a dedicated vector store earns its place (§12.7), so the specialised system was never proposed.

Nothing re-asks §12.1's question automatically. Kestrel's architecture review now carries one standing item: for each store, what would it take to not have it? MongoDB is the only row where the answer is short.

🧭 Version Note — the boundary moves, and it has moved toward Postgres

This chapter's "absorb before you adopt" argument is stronger than it was five years ago, and the reason is that PostgreSQL absorbed several of the specialised stores' reasons for existing.

text capability when it became viable in Postgres what it displaces ────────────────────────────────────────────────────────────────────────── JSONB + GIN 9.4 (2014), much faster since 12 a document store, at small scale full-text + pg_trgm long-standing; trigram indexes made fuzzy search practical a search engine, below relevance tuning declarative partitioning 10 (2017), usable from 12 a time-series store TimescaleDB an extension, mature same logical replication 10, with column/row filters in 15 bespoke CDC pgvector HNSW 0.5.0 (2023) a vector database, below ~10M vectors SKIP LOCKED 9.5 (2016) a queue, below fan-out and replay

Six of the eight store classes in this chapter have a Postgres answer that did not exist or was not practical a decade ago, and a great deal of the "you need a specialised store for X" material online predates the row that displaced it.

What has not moved: the scale at which a dedicated system genuinely wins, and the operational argument. Postgres absorbing a workload does not make it free — it makes it a workload competing for the same buffer cache and the same connections as your transactional load (Chapter 7 §7.1), which is a real cost and a different one.

The reading rule: when you find advice recommending a specialised store, check its date against the table above. And when you find advice recommending Postgres for everything, check whether it names a threshold. Advice without a threshold is a preference.

📐 Design Decision — one store per access pattern, or one store per team?

Two organising principles, and most platforms end up with the second by accident.

Per access pattern is the one this chapter argues for: a store exists because a measured access pattern needs it, it is shared across teams, and the number of stores is bounded by the number of genuinely distinct patterns — which is small.

Per team is what happens when adoption is decentralised: each team picks the store it prefers, the same access pattern is served by three different systems, and the count grows with headcount rather than with requirements.

text per access pattern per team ──────────────────────────────────────────────────────────────────── store count bounded by patterns grows with teams operational load shared duplicated extraction (§12.9) one mechanism per N mechanisms for the same pattern shape of data autonomy lower higher, and it is REAL the failure mode a shared store becomes three half-operated a bottleneck search clusters

The autonomy column is not a rhetorical concession. A team blocked for six weeks waiting for a central platform to support their access pattern will adopt their own store, correctly, and calling that a governance failure misdescribes it — it is a platform failure that produced a reasonable response (Chapter 35 §35.10's shadow pipelines).

Kestrel is per access pattern, and it can be because it has one team. At twenty engineers the tension is real and the resolution is Chapter 35's: a self-serve platform that makes the shared store faster to adopt than a private one. Until that platform exists, per-team proliferation is not a discipline problem and cannot be fixed with a policy.

The decision to write down now, while it is cheap: "who may adopt a store, and what do they have to show?" Three sentences. A platform with no answer defaults to per-team, and finds out at about fifteen engineers.

🔎 Read the Plan — every store has one, and they say different things

Chapter 7 taught you to read a Postgres plan. Each store in this chapter has an equivalent, and the useful skill is knowing what question each one answers.

text store the command the question it answers ──────────────────────────────────────────────────────────────────────── PostgreSQL EXPLAIN (ANALYZE, BUFFERS) which access path, and did the estimate match the actual? MongoDB .explain("executionStats") was an index used, and how many documents were EXAMINED versus returned? Elasticsearch _search?profile=true which query clauses cost the time, and did a filter cache? Cassandra TRACING ON how many nodes were contacted, and was it a partition scan? Prometheus the query's series count how many SERIES were touched -- the cardinality question (§12.5) pgvector EXPLAIN, as Postgres did it use the HNSW index, or did it fall back to a full scan?

Two of those rows deserve emphasis because their failure mode is invisible without them.

MongoDB's docsExamined versus nReturned. A query returning 10 documents having examined 4,000,000 is a collection scan wearing a result set, and it looks identical to a fast query from the outside — same output, more latency, and the latency is easy to attribute to the network.

pgvector's index fallback. An HNSW index is used only when the query's distance operator matches the index's operator class and the ORDER BY ... LIMIT shape is right. Get either wrong and Postgres silently does an exact scan of every vector — correct results, and hundreds of times slower, which is the worst kind of wrong because nothing is wrong.

The transferable instruction: before adopting a store, learn how to ask it what it did. It takes twenty minutes, it is the difference between operating the store and hoping, and it belongs in the adoption decision (§12.1) rather than in the first incident.

🔁 Idempotency Check — writing the same record twice, in six stores

Every store has an idempotent write and they are not the same operation, which matters because a pipeline that writes to three of them needs three answers.

text store the idempotent write the trap ──────────────────────────────────────────────────────────────────────── PostgreSQL INSERT ... ON CONFLICT DO requires a unique UPDATE / DO NOTHING constraint to exist Redis SET key value idempotent; but SET with a TTL RESETS the TTL MongoDB replaceOne(filter, doc, idempotent; updateOne with upsert=true) $inc is NOT Elasticsearch PUT /index/_doc/<id> idempotent by document id; POST without an id is not Cassandra INSERT (which is an upsert) idempotent, EXCEPT for counters and list appends a vector index upsert by id idempotent; but re-embedding with a different model produces a DIFFERENT vector under the same id (§12.7)

Three patterns are visible in the right-hand column.

A counter or an accumulator is never idempotent, in any store. $inc, INCR, a Cassandra counter, SET quantity = quantity + 1. If your pipeline increments anything, that write is a replay hazard and the fix is to write the absolute value rather than the delta.

A TTL is state. SET key value EX 3600 is idempotent in value and not in lifetime, so a retry extends the record's life. Harmless for a cache; not harmless when the TTL is the deletion mechanism (Chapter 31's 🔐).

And an id without a version is a lie in a derived store. The vector row is the sharpest case: the same id, upserted twice with two model versions, produces a store containing incomparable vectors and no error anywhere. The fix is Exercise 12.12's schema change — the model version stored beside the vector — and it is the reason that exercise exists.

The register from Exercise 4.21 needs a row per store, not a row per pipeline. Six stores is six idempotency strategies, and the one you have not written down is the one that will be replayed.

12.10 Choosing, and Not Choosing

The decision

What is the access pattern?
│
├─ Get one thing by key, very fast ──────────▶ key-value  (cache; NOT a source of truth)
├─ Read/write a whole entity at once ────────▶ document   (or Postgres JSONB)
├─ Huge writes, range within a partition ────▶ wide-column
├─ Metrics over time, bounded cardinality ───▶ time-series
├─ Full text, typo tolerance, relevance ─────▶ search index (derived)
├─ Semantic similarity ──────────────────────▶ vector      (pgvector first)
├─ Variable-depth traversal ─────────────────▶ graph
└─ Anything else ────────────────────────────▶ relational / warehouse

The three most over-adopted, and the question that tests each

1. A dedicated vector database. The question: how many vectors, and do you need to join them to relational data? Under ten million and yes → pgvector. Kestrel has 47,000 and joins to price and stock on every query.

2. A time-series database for business events. The question: does anything in this data ever get corrected, and do you need to join it to a customer or a product? If yes to either, it is not time-series data, whatever its timestamp suggests. Corrections and joins are the two things time-series databases are worst at.

3. A document store for data that has a schema. The question: do documents in this collection genuinely differ in shape, or do they all have the same twelve fields? If the latter, you have a table, and you have given up constraints, joins, and transactional guarantees for nothing. PostgreSQL's JSONB covers the genuinely-variable minority within a relational store.

Absorb before you adopt

Before adding a system, check whether one you already run can do it. PostgreSQL in particular covers a surprising amount:

None of these is as good as the specialized system at its specialty. All of them are good enough for longer than people expect, and each one you avoid is a system you do not operate, monitor, upgrade, secure, extract from, or find a second operator for.

🧪 Try It — inventory your own stores

bash cd part-02-storage/chapter-12-nosql-and-specialized-stores/code python store_inventory.py --engineers 4 python store_inventory.py --engineers 4 --markdown > inventory.md

Kestrel's fixture produces ten findings across four stores, and none of them is a surprise to anyone who works there — which is the point. Read the NO CHANGE FEED and NO REMOVAL CONDITION rows first.

Then replace the fixture with your platform's stores, and answer six columns for each:

text 1. what access pattern justifies it, and was that measured? 2. how does data get OUT of it? (section 12.9's table) 3. how does one customer's data get DELETED from it? 4. who is the second operator? 5. when was a backup last restored? 6. what would it take to not have it?

Most teams can answer 1 and 2 for every store and cannot answer 3, 4, or 5 for at least one. That store is the finding. Column 6 is the one that changes behaviour, because it converts an architecture conversation into a list of specific, small pieces of work.

🎓 Interview Angle — "When would you use NoSQL?"

A question that invites a bad answer — usually a list of store types with one-line descriptions, which every candidate gives.

"I'd start from the access pattern rather than the store. If it's single-key lookup at very low latency, that's key-value — but as a cache, not a source of truth, because the extraction story is poor and you'll want the data analytically eventually. If it's whole-entity reads and the entities genuinely differ in shape, that's document, though Postgres JSONB covers a lot of it. Huge write volume with range queries inside a partition is wide-column, and the cost there is that you design the tables around the queries in advance and there are no ad-hoc queries.

The thing I'd want to raise before adopting any of them is the change feed. MongoDB's change streams are good; Cassandra's CDC is awkward enough that most people dual-write; Redis has nothing reliable. That determines how much work the store is for the data team for its whole life, and it's usually not part of the adoption conversation.

And I'd check whether Postgres already does it. pgvector, JSONB, full-text, TimescaleDB — one system you already operate beats a better system you don't."

The change-feed point is what distinguishes this. It demonstrates that you have been on the receiving end of an adoption decision.

12.11 Summary

Nobody chooses six databases; everybody ends up with six. Each arrives as a reasonable answer to a specific problem, by someone under delivery pressure, correctly — and nobody counts. Chapter 5 §5.1's roughly-two-systems-per-engineer is the count.

The one honest reason to add a store is an access pattern the existing store serves badly enough to matter, measured. The three bad reasons are "Postgres can't do that" (frequently it can), "it'll scale better" (measure — Chapter 5's Case Study 2 spent nine months on a two-second improvement), and "the team knows it" (a real consideration and a bad primary reason).

Key-value stores are exceptional at single-key lookup and give up everything else — if you are scanning one, you have chosen the wrong store. A key-value store should never be the system of record for anything analytics needs: when someone asks for analytics on data that lives only in a cache, start emitting events rather than building a better scanner. DynamoDB is not quite in this class — its partition-plus-sort key gives it wide-column capability and wide-column modelling discipline.

Document stores suit whole-entity access and genuinely variable shapes. MongoDB's change streams are the best non-relational change feed available. The problem is never the feed; it is the schema — land documents whole in bronze, enforce an explicit schema in silver, and quarantine what does not match with a reason, because without the quarantine "enforce a schema" is just "silently drop."

Wide-column stores give enormous write throughput and range queries within a partition, and demand that you design tables around queries in advance — denormalization is the data model, not an optimization. Their extraction story is the weakest here; plan it at adoption, and the usual answer is the outbox pattern.

Time-series databases win on compression, retention policy, and time-aware functions, and lose on joins and corrections. Metrics belong in one; business events do not, however many timestamps they have. And cardinality, not volume, is what kills them: adding an unbounded identifier as a label turns 4,800 series into nine billion, and the person who does it is always trying to improve observability.

Search indexes and vector stores are derived data — extract from the source, not from them. But the data about how people used them is often the most valuable thing they hold and exists only in short-retention logs: 45% of Kestrel's zero-result searches were solvable, and nobody knew there were any. Emit usage as events.

Vector stores are a data engineering problem, not an ML one: embeddings are a pipeline, the model is a versioned dependency whose outputs are incomparable across versions, and the vector is never the answer — the join to real data is. Version the index, not the rows; store the model version with every vector; and assert coverage, because partial regeneration is silent and 6,000 missing products went unnoticed for five weeks.

Consult the extraction table before adopting, not after. A store with no change feed will be full-scanned, which is expensive, disruptive, and misses deletes. When a store cannot tell you what changed, make the application tell you.

Three stores are over-adopted, each with a one-question test: a dedicated vector database (how many vectors, and do you join to relational data?), a time-series database for business events (does anything get corrected, and do you need joins?), and a document store for data that has a schema (do the documents genuinely differ in shape?).

Absorb before you adopt. PostgreSQL covers key-value, document, full-text, time-series, vector, graph, and queue workloads — none as well as the specialist, all well enough for longer than people expect. Each system you avoid is one you do not operate, monitor, upgrade, secure, extract from, or find a second operator for.

What's next

Part II ends here. Part III is ingestion — the boundary where your assumptions meet somebody else's reality, and where the incidents are. Chapter 13 opens it with batch extraction: full versus incremental, watermarks and the four ways they lie, and how to extract from a production database without becoming an outage. Chapter 7 §7.7 gave you the shape of a safe extract; Chapter 13 builds it properly and hands it to Chapter 24 to schedule.