Specialized Data

A specialized data system is a store built around one family of queries: ranked text search, time-window aggregation, relationship traversal, similarity search, or sub-millisecond key lookup. Choose one when the query semantics you need are expensive or impossible in your general-purpose database, when you can state how stale its copy of the data may be, and when you can afford to operate it. The central engineering question is not what the engine can do, but what it guarantees under concurrency, failure and scale, and how its copy of the data stays aligned with the system of record.

A transactional database stores orders reliably and enforces their invariants, and it may still struggle with relevance-ranked search, similarity search over hundreds of millions of vectors, high-cardinality telemetry, or traversals of variable depth. Each specialized engine wins by choosing a different physical layout: inverted indexes, time-partitioned compressed blocks, adjacency lists, proximity graphs, or plain hash tables in memory. The layout that makes one operation cheap makes others expensive. The question to ask is therefore never "which database is best", but three narrower ones: which operations are required, what guarantee does each need, and how are the copies kept in step.

The mental model that keeps the rest of this lesson simple: every specialized store is a derived index over facts that live somewhere else. A search cluster is an index shaped for text, a cache is an index shaped for key lookup, a vector store is an index shaped for distance. Derived data can be rebuilt, can lag, and can be wrong in ways the source cannot. The exception is a system that deliberately makes a specialized store the primary one, and that is a different decision with a different bar for durability, backup and consistency. Foundations for the source side are in Database Foundations, and the general indexing ideas that these engines specialize are in Indexing.

The running example is a store with an orders table in PostgreSQL plus a product catalog. Search serves the catalog, a cache serves hot order lookups, and an analytics store serves finance. The sections below cover search, time-series, graph, vector, cache and key-value, and document and wide-column stores; each explains the mechanism and then names the failure that production teaches you. Columnar analytics appears in the selection framework near the end. The multi-store section near the end ties them together.

The main database types are best told apart not by brand or by whether they use SQL, but by the access pattern each was built for. The categories overlap in practice (PostgreSQL has JSON columns, full-text search and a vector extension), so read them as the workload where each design is strongest, not as exclusive boxes.

• Relational (row-oriented OLTP). Normalized tables, joins, constraints and multi-row ACID transactions; the default system of record. PostgreSQL and MySQL are typical. Data Modeling covers how the schema is designed.
• Document. Self-contained JSON-like aggregates read and written by key or by indexed fields, with a flexible but still implicit schema (MongoDB, for example).
• Key-value. Lookup by exact key at very low latency, often in memory; used for caches, sessions and counters (Redis, Memcached, DynamoDB in its key-value use).
• Wide-column. Rows partitioned by key across many nodes, with queries designed per partition and write throughput scaled out (Cassandra, HBase, Bigtable). The partition key choice is the central design decision, as discussed in Distributed Data.
• Column-oriented analytical (OLAP). Data stored by column and compressed, so scans and aggregates over a few columns of billions of rows are cheap, while single-row updates are not (ClickHouse, BigQuery, Redshift, Snowflake).
• Search. Inverted indexes for full-text matching and relevance ranking (Elasticsearch, OpenSearch, Solr).
• Time-series. Append-mostly timestamped measurements with window aggregates, downsampling and retention (Prometheus, InfluxDB, TimescaleDB).
• Graph. Variable-depth traversal of relationships, such as paths, rings and recommendations (Neo4j).
• Vector. Approximate nearest-neighbor search over embeddings, for similarity and semantic retrieval.

Two things stay constant across all of them. Each type is fast at the access pattern its physical layout favours and slow at the opposite one, which is why the layout decision is an indexing decision at heart. And the more nodes a type spreads data over, the more it asks you to design queries around partitions and to accept weaker cross-node guarantees. The sections below take search, time-series, graph, vector, cache and key-value, and document and wide-column stores one at a time; columnar analytics is covered in the selection framework, and relational and the general mechanics are covered by the foundations, modeling and indexing lessons.

Full-text search tokenizes text, normalizes the tokens with an analyzer, and stores an inverted index: a map from each term to the list of documents that contain it (its posting list, usually with term frequency and positions). A query is analyzed with the same rules, the posting lists of its terms are intersected or unioned, and the matches are scored. A B-tree answers "which row has this exact value"; an inverted index answers "which documents contain these words", and that difference is why LIKE '%word%' does not scale to search.

Analyzers decide what "the same word" means, so they decide search quality. With the whitespace analyzer the query Headphones misses a document containing headphones; lowercasing fixes that but headphone and headphones remain different terms; stemming merges them. Stop-word removal makes the query for analyze to nothing at all, and an engine then returns no results by default. Language, synonyms, accent folding and tokenization of product codes such as XM-5 are the same kind of decision. The analyzer used at index time and at query time must agree, and changing it means reindexing, because the stored terms were produced by the old rules.

Ranking with BM25

Matching says a document qualifies; ranking says how well. Lucene-based engines (Elasticsearch, OpenSearch, Solr) default to BM25, which replaced the classic TF-IDF formula as the default in Elasticsearch 5.0. BM25 combines two ideas. The inverse document frequency of a term rewards rarity: in a corpus of 1,000,000 documents, a term found in 100 of them has an IDF of about 9.2 under Lucene's formula ln(1 + (N − n + 0.5)/(n + 0.5)), while a term found in 500,000 has about 0.69. The term frequency part saturates and normalizes by length, controlled by k1 (default 1.2) and b (default 0.75).

The curves show why repeating a keyword ten times does not make a document ten times more relevant: the contribution approaches a ceiling, and a long document needs more occurrences to earn the same score as a short one. The plot uses the classic form with the (k1 + 1) numerator; recent Lucene versions drop that constant factor, which changes absolute scores and not the ordering. Scores are also relative to one query on one index, so comparing them across queries, or against a vector similarity, is meaningless.

Filters are not relevance

In the query below the must clause contributes to the score, while the filter clauses only include or exclude and are cacheable. Tenant isolation and ACLs belong in filters, where they are exact, not in ranking, where they would be a preference. Document-level security can be enforced at query time with filters like these, which is flexible but means every query path must remember to apply them, or at index time by routing each tenant to its own index or shard set, which is stronger isolation at a cost in index count. Whichever you pick, state the guarantee: a permission revoked in the database is still visible in search until the projection catches up, so decide whether that window is acceptable for the data.

// Elasticsearch / OpenSearch REST body for: PUT /products
{
  "settings": {
    "analysis": {
      "filter": {
        "english_stop":    { "type": "stop",    "stopwords": "_english_" },
        "english_stemmer": { "type": "stemmer", "language": "english" }
      },
      "analyzer": {
        "product_text": {
          "type": "custom",
          "tokenizer": "standard",
          "filter": ["lowercase", "english_stop", "english_stemmer"]
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "tenant_id":   { "type": "keyword" },
      "acl_groups":  { "type": "keyword" },
      "brand":       { "type": "keyword" },
      "title":       { "type": "text", "analyzer": "product_text" },
      "row_version": { "type": "long" }
    }
  }
}

// POST /products/_search  -- relevance from "must", exact restrictions from "filter"
{
  "query": {
    "bool": {
      "must":   [ { "match": { "title": "wireless noise cancelling headphones" } } ],
      "filter": [
        { "term":  { "tenant_id": "t_42" } },
        { "terms": { "acl_groups": ["support", "emea-sales"] } },
        { "term":  { "brand": "Acme" } }
      ]
    }
  },
  "size": 20
}

Near-real-time visibility, deletes and reindexing

Lucene writes immutable segments. A new document first sits in an in-memory buffer; a refresh makes buffered documents visible to search, and in Elasticsearch it runs every second by default on indices that are being searched. A fetch of a single document by id can see a document before the refresh, but a search cannot. Stack the pipeline lag in front of that and the committed order is invisible to search for noticeably longer than the refresh interval, and that is why search is a projection, not a source.

Segments are immutable, so an update is a delete plus a new document, and a delete only marks the old copy until a background merge rewrites segments. Disk usage and query cost therefore include dead documents until merges catch up, and a heavily updated index behaves differently from an append-only one. Mapping evolution follows from the same design: you cannot change an existing field's type in place. The usual procedure is to create a new index with the new mapping, backfill it from the source of truth, and move an alias to it. Build that capability before you need it, because the day a mapping is wrong is the day you need it. Replace the whole search index from the database at least once in a rehearsal, and measure how long it takes.

One more caveat belongs here. The search engine is not where you enforce uniqueness, balances or any invariant: it has no multi-document transactions and a node loss can lose recent writes, depending on replica and translog settings. If you ever need to confirm that the search result is acceptable (the price on the page, whether an item is purchasable), check the source of truth at the point of action.

A time series DB is a database built for timestamped measurements that are appended, rarely updated, and queried as aggregates over time windows.

Telemetry workloads append timestamped measurements, rarely update them, and query them as aggregates over time windows: p99 latency per route over six hours, CPU per host over a week. Time-series engines exploit that shape. They partition data by time, so recent data is hot and old data is a cold block; they compress timestamps and values column-wise (delta-of-delta timestamps and XOR-style float encodings are common), which is why a sample can cost only a couple of bytes on disk; they apply retention by dropping whole blocks instead of deleting rows; and they downsample old data into coarser rollups, such as raw samples for 15 days and hourly aggregates for 13 months.

The cost model has one dominant term: series cardinality. A series is one unique combination of metric name and label values. Storing samples is cheap, but each active series carries an index entry, in-memory state and query-time work. The number of series is the product of the label value counts, so adding one label multiplies the total instead of adding to it.

With 100 hosts, 50 routes and 5 status codes there are 25,000 series, which any system handles. Add a user_id label with a million values and the worst case is 25 billion, and the system fails long before that because memory and the index are exhausted by the combinations that actually appear. The figure's memory estimate assumes a few kilobytes per active series, an order of magnitude that depends on the engine and version; measure your own. The rule that follows: labels must come from a bounded set you control. Request ids, user ids, session ids, raw URLs and error messages belong in logs and traces, where high cardinality is the expected shape, and any of them can be added to a metric only through a bounded bucket such as a user tier or a route template.

# Prometheus exposition format. Bounded labels, one series per combination.
http_requests_total{route="/orders/:id", method="GET", status="200"} 18234

# Anti-pattern: an unbounded label creates a new series per user (or per request id).
http_requests_total{route="/orders/:id", user_id="u_8841207"} 3
# Put user_id in logs or traces, where high cardinality is the expected shape.

Not every timestamped record is a metric. Orders, payments and audit events carry timestamps and also need transactions, joins and updates, and that points to the relational store with a time-ordered index or partitioning. For moderate volumes plain PostgreSQL does a respectable job, and the SQL below is a baseline to benchmark against before introducing a dedicated engine: range partitions, a scheduled rollup, and retention by dropping a partition. Dedicated time-series databases and extensions such as TimescaleDB automate exactly these chores and add compression and continuous aggregates. The Scaling lesson covers partitioning in general.

-- PostgreSQL declarative partitioning as a plain-SQL time-series baseline.
CREATE TABLE metrics (
  ts      timestamptz      NOT NULL,
  host_id integer          NOT NULL,
  name    text             NOT NULL,
  value   double precision NOT NULL
) PARTITION BY RANGE (ts);

CREATE TABLE metrics_2026_11_02 PARTITION OF metrics
  FOR VALUES FROM ('2026-11-02 00:00:00+00') TO ('2026-11-03 00:00:00+00');

CREATE INDEX ON metrics_2026_11_02 (host_id, ts);

-- Downsample one day into hourly rows (run from a scheduled job once the day is closed).
CREATE TABLE metrics_hourly (
  hour    timestamptz      NOT NULL,
  host_id integer          NOT NULL,
  name    text             NOT NULL,
  avg_v   double precision NOT NULL,
  max_v   double precision NOT NULL,
  PRIMARY KEY (hour, host_id, name)
);

INSERT INTO metrics_hourly
SELECT date_trunc('hour', ts), host_id, name, avg(value), max(value)
  FROM metrics
 WHERE ts >= '2026-11-02 00:00:00+00' AND ts < '2026-11-03 00:00:00+00'
 GROUP BY 1, 2, 3
ON CONFLICT (hour, host_id, name) DO UPDATE
  SET avg_v = EXCLUDED.avg_v, max_v = EXCLUDED.max_v;

-- Retention: dropping a partition is a metadata operation, unlike DELETE over millions of rows.
-- DROP TABLE metrics_2026_11_02;   -- once raw data older than the retention window is no longer needed

Failure modes to monitor: active series count and its growth rate (alert on a step change after a deploy), ingestion rejections, the lag between sample timestamp and visibility, late or out-of-order samples that the engine drops or rewrites, and rollup jobs that quietly stop so that dashboards for old ranges go empty. Rollups are lossy: an hourly average cannot answer a question about a 30-second spike, so keep max and count next to averages, or compute percentiles from histograms, since averaged percentiles are wrong.

A graph database stores data as vertices (entities) and edges (relationships), each with properties, and answers queries by traversing those edges. Because a native graph store follows direct references between neighbors, it suits questions that repeatedly walk a variable number of hops, such as fraud rings or dependency impact analysis.

A graph model stores vertices (accounts, services, people) and edges (transferred-to, depends-on, follows), each with properties. In a native graph store each vertex keeps direct references to its neighbours, so following an edge does not require an index lookup or a join, and the cost of a query depends on the part of the graph it touches, not on the total size. That makes graphs a good fit for queries that repeatedly traverse a variable number of hops: fraud rings, dependency impact analysis, access-path questions ("can this user reach this resource through any group chain"), recommendations from friends of friends.

A graph database does not make every join faster. A fixed two-table join is exactly what a relational database does well, and a graph store gives you nothing there. The advantage appears when the depth is not known in advance, which in SQL becomes a recursive query or repeated self-joins. And even then the cost is governed by fan-out, as the calculator shows: the number of edges followed grows as fan-out to the power of the depth.

Three hops at an average fan-out of 50 is already 125,000 edges at the last hop, and six hops at the same fan-out is more than the entire 10-million-vertex graph, so the query degenerates into a scan of most of the data. Real graphs are not uniform: a supernode such as a payment hub, a celebrity account or a shared IP address can have millions of edges, and one hop through it dominates the query. Defences are bounded depth, a limit on results, filtering on edge type and properties as early as possible, excluding or special-casing known hubs, and precomputing the answers that are asked often.

// Neo4j Cypher. Accounts that can reach themselves through 2 to 4 transfers (a possible ring).
// A path never reuses the same relationship, but it may revisit a node, so review the results.
MATCH p = (a:Account {id: $accountId})-[:TRANSFERRED_TO*2..4]->(a)
RETURN p
LIMIT 25;

Before adopting a graph database, test the same question as a recursive CTE. If the depth is small and the fan-out modest, PostgreSQL with an index on the source column answers it, keeps the data transactional, and costs you no extra system.

-- PostgreSQL: the same ring question as a recursive CTE (no graph database). Rings of 2 to 4 transfers
-- through account 17; unlike the Cypher above, intermediate accounts are never repeated.
WITH RECURSIVE reach(account_id, depth, path) AS (
  SELECT t.to_account, 1, ARRAY[t.from_account, t.to_account]
    FROM transfers t
   WHERE t.from_account = 17
  UNION ALL
  SELECT t.to_account, r.depth + 1, r.path || t.to_account
    FROM reach r
    JOIN transfers t ON t.from_account = r.account_id
   WHERE r.depth < 4
     AND r.account_id <> 17                   -- a path that is back at the origin is complete
     AND NOT t.to_account = ANY (r.path[2:])  -- no repeated intermediates; the origin may close the ring
)
SELECT path FROM reach WHERE account_id = 17 AND depth >= 2 LIMIT 25;
-- Needs an index on transfers (from_account). Fan-out, not the CTE syntax, decides whether this is fast.

Modeling decisions that are expensive to change later: give edges a direction and a type (a FOLLOWS edge is not a BLOCKS edge); decide which edges must be unique (one MEMBER_OF per pair) and enforce it, because most graph stores will happily create duplicates; and model temporal validity explicitly with valid_from and valid_to properties when the relationship changes over time, since a traversal that ignores validity answers about a graph that never existed. Consistency of relationship updates is a real concern too: if the graph is a projection of a relational source, an edge created from a late event can appear after the edge that should have replaced it, which is the replay and ordering issue covered in the multi-store section.

An embedding model maps content (text, images, products) to a vector of a few hundred or thousand numbers so that similar items land near each other. Vector search retrieves the stored vectors closest to a query vector under a distance or similarity metric. Exact search compares the query with every vector, which is fine for thousands or low millions of vectors and too slow beyond that, so large collections use approximate nearest-neighbor (ANN) indexes that visit a small fraction of the data and accept that they may miss a true neighbour. The fraction of true top-k results the index actually returns is its recall.

Metrics and normalization

Cosine similarity compares direction only. Inner product also rewards length, and Euclidean distance measures the straight-line gap. They agree when the vectors are normalized to unit length: then the inner product equals cosine similarity and Euclidean distance is a monotone function of it, so the ranking is identical. They disagree when the vectors are not normalized, and which metric is correct is defined by the embedding model, not by preference. Use the metric the model was trained for, and normalize consistently at index time and query time.

Document A points exactly the same way as the query, so cosine ranks it first at every length. Under inner product its rank depends only on how long its vector happens to be. If your embedding pipeline produces vectors of varying length and you rank by inner product, you are partly ranking by vector norm, which is usually not what you intended.

How HNSW works and what it costs

HNSW (Hierarchical Navigable Small World) is the most widely used ANN index. It builds a layered proximity graph: sparse upper layers with long links, a dense bottom layer holding every vector. A search enters at the top, greedily moves towards the query, drops a layer, and at the bottom layer explores a candidate list whose size is the query-time parameter ef_search. Larger values visit more nodes: better recall, higher latency. Build-time parameters M (links per node) and ef_construction (candidate list during insertion) trade index quality against build time and memory. In pgvector the defaults are m = 16, ef_construction = 64 and hnsw.ef_search = 40.

Memory is the cost that surprises teams. 10 million vectors of 768 dimensions stored as 4-byte floats are 10,000,000 × 768 × 4 bytes = 30.72 GB before the graph; with 32 links per node at the bottom layer (twice M) and 4-byte ids, links add roughly another 1.3 GB, and a graph index wants to be resident in memory to be fast. Quantization (storing 8-bit or binary codes, then reranking with full vectors), lower dimensions, and disk-oriented index types all trade recall or latency for that footprint. Only a benchmark on your vectors and your queries, measuring recall against exact search, tells you where an acceptable point is. Build cost is also real: bulk loading and then indexing is generally cheaper than inserting row by row into a live index.

-- PostgreSQL with the pgvector extension (HNSW needs pgvector 0.5.0+).
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE doc_chunk (
  id              bigserial PRIMARY KEY,
  tenant_id       text         NOT NULL,
  body            text         NOT NULL,
  embedding       vector(768)  NOT NULL,
  embedding_model text         NOT NULL        -- e.g. 'embed-v3', vectors from different models are not comparable
);

CREATE INDEX doc_chunk_hnsw ON doc_chunk
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);          -- these are the pgvector defaults

-- Query-time recall/latency knob (default 40). Higher is slower and usually more accurate.
SET hnsw.ef_search = 100;

SELECT id, 1 - (embedding <=> $1) AS cosine_similarity
  FROM doc_chunk
 WHERE tenant_id = 't_42'
   AND embedding_model = 'embed-v3'
 ORDER BY embedding <=> $1          -- <=> is cosine distance; <#> is negative inner product; <-> is L2
 LIMIT 10;
-- With an HNSW index the WHERE clause is applied to the candidates the index returns, so a selective
-- filter can return fewer than 10 rows. Check your pgvector version's iterative-scan and partial-index options.

Hybrid retrieval and reranking

A vector score is not a truth or relevance guarantee. Embeddings blur exact identifiers: a part number, a person's name or a rare error code is often found better by BM25, while paraphrases and intent are found better by vectors. Hybrid retrieval runs both and merges the lists. Raw scores from the two systems are on unrelated scales, so a rank-based merge such as reciprocal rank fusion (score = Σ 1/(k + rank)) is the sturdy default. A reranker, typically a cross-encoder that reads the query and each candidate together, then reorders a short list. It is too slow for the full corpus, which is why it comes last.

// Reciprocal rank fusion: merge ranked id lists without comparing their raw scores.
// k = 60 is the constant from the original paper and a common default.
function reciprocalRankFusion(rankedLists, k = 60) {
  const scores = new Map();
  for (const list of rankedLists) {
    list.forEach((id, index) => {
      scores.set(id, (scores.get(id) ?? 0) + 1 / (k + index + 1));
    });
  }
  return [...scores.entries()].sort((a, b) => b[1] - a[1]).map(([id]) => id);
}

const lexical = ['d7', 'd2', 'd9', 'd4'];
const semantic = ['d2', 'd5', 'd7', 'd1'];
console.log(reciprocalRankFusion([lexical, semantic]));
// d2 and d7 appear in both lists, so they outrank d9, d5, d4, d1.

Filters, tenancy and lifecycle

Filters are where vector search is hardest to get right. If the index returns the nearest 50 candidates and the tenant or ACL filter is applied afterwards (post-filtering), a selective filter can leave few or no results, even though matching vectors exist.

The calculator assumes the filter is independent of similarity, which is the optimistic case. Tenant filters are usually correlated with content, so the real result can be better or much worse. The remedies are filtering inside the index traversal (supported by many engines), partitioning or a separate index per tenant, partial indexes for the hot filters, retrieving more candidates, or exact search within a small pre-filtered subset. Whichever you choose, ACL enforcement must be tested as a correctness property, not a relevance one: a result the caller may not see is a data leak, however good its similarity.

Lifecycle problems follow. Store the embedding_model and version next to every vector: vectors from different models, or different versions of one model, live in different spaces and cannot be compared, so a model upgrade means a backfill into a new index and a cutover, with both running in the meantime. Deletions must propagate from the source of truth, including right-to-erasure requests, and many ANN implementations mark deleted vectors and reclaim them lazily, so verify that a deleted item can no longer be returned and plan periodic index maintenance. Chunking, the units that get embedded, determines what can be retrieved at all, so version it too.

A cache keeps a copy of data nearer to the reader so repeated reads do not reach the authoritative store. It buys latency and takes load off the database, and it adds a second copy whose freshness you must now define. Treat the cache as disposable: the system should stay correct, if slower, when the cache is empty. Only a store deliberately operated as a primary database, with persistence, replication and backups chosen for that role, is an exception, and then it is not being used as a cache.

The calculator shows why the hit ratio matters non-linearly. Going from 95% to 99% at 10,000 reads per second cuts database reads from 500 to 100 per second, a fivefold reduction, so the last few points of hit ratio are what protect the database. It also shows the hazard: the database may be sized for the post-cache load, so an empty cache after a restart, a flush or a key-format change sends the full read rate to it. Warm-up, request coalescing, and load shedding (see Load Shedding) are part of the cache design, not an afterthought.

Cache-aside and its race

In cache-aside the application reads the cache, and on a miss reads the database and populates the cache. It is the default because it is simple and failure-tolerant. It is also racy: a reader can read the old row, lose the CPU, and write it into the cache after a writer has already updated the database and invalidated the key.

Deleting the key after the write narrows the window and does not close it, and a TTL bounds how long the stale entry survives without making the cache fresh. If staleness within the TTL is acceptable, accept it explicitly. If not, give every row a monotonic version, as the example below does with row_version, and make cache writes conditional on a newer version. The guard runs as a Lua script because the compare and the set must be atomic on the Redis side. A hard delete needs a short-lived tombstone carrying the deletion version, since deleting the key also erases the evidence the guard relies on. And the guard cannot defend against a populate that stalls for longer than the TTL, after which the key has expired and no version remains; for that, keep TTLs longer than any realistic request.

// Node.js, node-redis v4, pg. Versioned cache-aside: the cache only ever moves forward in row_version.
import { createClient } from 'redis';
import pg from 'pg';

const redis = createClient({ url: process.env.REDIS_URL });
await redis.connect();
const pool = new pg.Pool();

// Runs atomically inside Redis: ignore the write if the cache already holds this version or a newer one.
const GUARDED_SET = [
  "local cur = redis.call('HGET', KEYS[1], 'v')",
  "if cur and tonumber(cur) >= tonumber(ARGV[1]) then return 0 end",
  "redis.call('HSET', KEYS[1], 'v', ARGV[1], 'body', ARGV[2])",
  "redis.call('EXPIRE', KEYS[1], ARGV[3])",
  "return 1",
].join('\n');

const guardedSet = (row, ttlSeconds = 300) =>
  redis.eval(GUARDED_SET, {
    keys: ['order:' + row.id],
    arguments: [String(row.row_version), JSON.stringify(row), String(ttlSeconds)],
  });

export async function getOrder(id) {
  const cached = await redis.hGetAll('order:' + id);
  if (cached.body) return JSON.parse(cached.body);          // hit

  const { rows } = await pool.query(
    'SELECT id, status, total_cents, row_version FROM orders WHERE id = $1', [id]);
  if (!rows[0]) return null;
  await guardedSet(rows[0]);                                // populate; loses to a newer version
  return rows[0];
}

export async function updateStatus(id, status) {
  const { rows } = await pool.query(
    'UPDATE orders SET status = $2, row_version = row_version + 1 WHERE id = $1 ' +
    'RETURNING id, status, total_cents, row_version', [id, status]);
  if (!rows[0]) return null;
  await guardedSet(rows[0]);          // runs after the commit; if it fails, the TTL still bounds staleness
  return rows[0];
}

Write policies, stampedes and eviction

Write-through updates the cache and the database in the same operation: reads stay fresh and every write pays both latencies, with a partial failure to handle when one succeeds and the other does not. Write-behind acknowledges after writing the cache and flushes to the database later: writes are fast, but anything not yet flushed is lost if the cache node dies, so the cache has effectively become the primary for that window. Use it only for data you can afford to lose or rebuild.

A stampede happens when a hot key expires and hundreds of readers miss together and all query the database. Counter it with per-key request coalescing (one loader, others wait), jittered TTLs so related keys do not expire together, and serving stale data while a refresh runs. Cache negative results ("this id does not exist") briefly, or a stream of lookups for missing ids bypasses the cache entirely. Beyond that, set a memory limit and an eviction policy deliberately; a Redis instance with maxmemory and an LRU or LFU policy drops keys under pressure by design, and one without a limit eventually fails in a less graceful way. Persistence (RDB snapshots, AOF) reduces data loss on restart, and replication to a replica is asynchronous by default, so a failover can still lose recently acknowledged writes. Those features make Redis durable-ish; they are not a substitute for a system of record. For keys that must spread across nodes, see Consistent Hashing. A related correctness rule: if a request must not be processed twice, a cache entry is a poor place to record that, and the Idempotency lesson covers durable keys.

A document store (MongoDB, Firestore, CouchDB) keeps a JSON-like aggregate as one unit, so data that is read together, such as an order with its line items and shipping address, is also stored and fetched together. When the aggregate is the unit of access this removes joins and often removes the need for a multi-row transaction. Two failure modes appear when the aggregate is wrong. A document that grows without bound (every event appended to an array) hits size limits (MongoDB documents are capped at 16 MiB) and slows each update, because a small change to a large document can rewrite a large part of it. And a document that many writers update becomes a contention point, since one document is the unit of atomicity and serialization. Bucketing bounds the growth.

// MongoDB shell. One document per device per hour instead of one ever-growing document per device.
// No unique index on (device_id, hour): when a bucket reaches 3600 samples the filter no longer
// matches and the upsert starts an overflow bucket.
db.readings.updateOne(
  { device_id: 'd-42', hour: ISODate('2026-11-02T10:00:00Z'), count: { $lt: 3600 } },
  {
    $push: { samples: { t: ISODate('2026-11-02T10:00:07Z'), v: 21.4 } },
    $inc:  { count: 1 }
  },
  { upsert: true }
);

A wide-column store (Cassandra, ScyllaDB, HBase, Bigtable-style systems) is organized around a partition key that decides which nodes hold a row, and within a partition rows are kept sorted by clustering columns. These systems are good at write-heavy workloads and at fetching a known partition or a slice of it, and they are deliberately poor at what they cannot do cheaply: cross-partition joins, ad-hoc filtering on columns outside the key, and multi-partition transactions are absent or restricted. The design process is therefore inverted from relational: list the queries first, and create one table per query shape, denormalizing on purpose.

-- Cassandra CQL. The partition key (device_id, day) bounds each partition; ts clusters rows inside it.
CREATE TABLE readings_by_device_day (
  device_id text,
  day       date,
  ts        timestamp,
  value     double,
  PRIMARY KEY ((device_id, day), ts)
) WITH CLUSTERING ORDER BY (ts DESC);

-- Served by one partition: the newest readings of one device on one day.
SELECT ts, value FROM readings_by_device_day
 WHERE device_id = 'd-42' AND day = '2026-11-02' LIMIT 100;

Partition design is the whole game. A key such as device_id alone makes a partition grow forever, and one device emitting a reading per second adds 86,400 rows a day to the same partition, which eventually hurts reads, compaction and repair. Adding the day to the partition key bounds it. The opposite error is a key with too few distinct values, such as a country code, which sends all the traffic for a popular value to the same few nodes: a hot partition. Deletes are also writes in these systems (tombstones that must be retained for a grace period), so queue-like delete-heavy tables are a classic trap. Consistency is tunable per request, and the quorum arithmetic is covered in Distributed Data.

"Schema-less" means the database does not enforce a schema, not that one does not exist. Every reader of a document store has expectations about field names and types, and those are now enforced in application code, in migrations you write yourself, and in whichever service happens to read old documents last. Use schema validation where the engine offers it, version the documents, and plan the migration of old shapes. How to decide what to embed and what to reference is the subject of Data Modeling. The access-pattern and invariant analysis there applies unchanged.

Running several stores side by side, each chosen for its workload, is often called polyglot persistence. Its cost is that the stores no longer share a transaction, so one of them must be the source of truth and the others must be kept aligned by explicit mechanisms.

When an order lives in PostgreSQL, is indexed in search, cached in Redis and aggregated in an analytics store, there are four copies with four freshness profiles, and no transaction spans them. Dual writes from application code (write the database, then call the search client) fail in the gap between the two calls: the process dies after the first, the second times out, or two requests interleave, and the copies diverge with nothing recording that they did. The fix is to write once, to the source of truth, and derive the rest.

Two mechanisms do the deriving. A transactional outbox writes an event row in the same transaction as the business change, and a relay publishes unsent rows; the Outbox Pattern lesson covers it in depth. Change data capture (CDC) reads the database's own transaction log (in PostgreSQL, logical decoding) and emits row changes without application involvement; it captures every change, including ones made by scripts and migrations, at the price of coupling consumers to table shapes unless you map them. Both give at-least-once delivery and ordering that is guaranteed only within a scope (a partition, a key, a log), not globally.

-- PostgreSQL. The order change and its outbox row commit atomically.
CREATE TABLE orders (
  id          bigint      PRIMARY KEY,
  status      text        NOT NULL,
  total_cents bigint      NOT NULL,
  row_version integer     NOT NULL DEFAULT 1,
  updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE outbox (
  id           bigserial   PRIMARY KEY,
  aggregate_id bigint      NOT NULL,
  row_version  integer     NOT NULL,
  event_type   text        NOT NULL,
  payload      jsonb       NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  sent_at      timestamptz
);

BEGIN;
UPDATE orders SET status = 'PAID', row_version = row_version + 1, updated_at = now() WHERE id = 817;
INSERT INTO outbox (aggregate_id, row_version, event_type, payload)
SELECT id, row_version, 'OrderUpdated',
       jsonb_build_object('id', id, 'status', status, 'total_cents', total_cents)
  FROM orders WHERE id = 817;
COMMIT;

That delivery guarantee is what consumers must be built for. The same event can arrive twice, two events for one entity can arrive out of order, a consumer can replay history after a bug fix, and the payload shape will change over time. The robust design is for projections to be idempotent, version-aware upserts, not event-by-event commands: carry the row version, and refuse to move a record backwards.

// Index the projection with the database row version as an external version.
// A write with a version <= the stored one is rejected with 409, so replays and
// out-of-order deliveries cannot move the document backwards.
PUT /products/_doc/817?version=6&version_type=external
{ "tenant_id": "t_42", "acl_groups": ["support"], "brand": "Acme",
  "title": "Wireless noise cancelling headphones", "row_version": 6 }

Deletes need care: an external-version delete leaves a tombstone that the engine keeps for a limited time (index.gc_deletes, 60 seconds by default in Elasticsearch), after which a very late older write could resurrect the document, so keep a soft-delete flag or reconcile. For consumer retries, back off as in Retry & Backoff; events that keep failing belong in a dead-letter queue with an alert, not in an infinite loop, and schema changes should be additive so old and new consumers coexist.

Define authority, lag behavior and reconciliation

Write down which store is authoritative for each fact (the order status is in PostgreSQL, the search index only reflects it) and what the user sees while a projection lags. Usually the answer is a rule such as: the order detail page reads the database or a version-guarded cache, the order list is allowed to be seconds stale, and the checkout path never depends on a projection. Read-your-writes for the user who just acted can be handled by reading the source, or by returning the written row from the write response and letting the client show it. This split of write model and read models is exactly what CQRS formalizes, and when the event log itself is the source of truth you are in Event Sourcing territory.

Even a correct pipeline drifts through bugs, manual fixes and lost events, so give every projection a reconciliation path. A cheap approach compares compact fingerprints per id range (count, sum of versions, newest timestamp) between source and projection, then re-syncs only the ranges that differ.

-- Reconciliation input: compact per-bucket fingerprints from the source of truth.
-- Compute the same fingerprint from the projection and compare bucket by bucket; re-sync only buckets that differ.
SELECT id / 10000                AS bucket,
       count(*)                  AS row_count,
       sum(row_version)          AS version_sum,
       max(updated_at)           AS newest
  FROM orders
 GROUP BY 1
 ORDER BY 1;

What to measure in production: projection lag per consumer (the age of the oldest unprocessed outbox row or the CDC offset behind the log head), the failed-event and dead-letter counts, the duplicate and version-conflict rates (a sudden rise points to a relay problem), reconciliation mismatch counts, and search or vector index document counts against the source. A projection whose lag is not on a dashboard is a projection that will be wrong for a long time before anyone knows.

Before naming a product, write down the requirements the product has to meet. The list is short and each item changes the answer:

• The queries you must serve and their shape: key lookup, filtered range, text relevance, aggregate over a window, traversal of variable depth, nearest neighbour, and how many of each.
• Write and read rates, data size now and in a year, and how fast it grows.
• Freshness tolerance for each read path, in seconds, not adjectives.
• Transactional invariants and which store enforces them.
• Tenant isolation and access control requirements, including deletion and erasure.
• Availability targets, the backup and restore procedure, and the recovery time you have actually tested.
• The team that will carry the pager for the new system, and what it costs to run.

Then map requirements to engines with a few if-then rules. If the answer must be exact and transactional, stay relational. If it is ranked text with facets and typo tolerance, search. If it is aggregates over timestamped samples with retention, time series; if the aggregates run over billions of business rows, a columnar analytics engine such as ClickHouse or a warehouse, because they read only the columns the query touches. If the question is distance between embeddings, vectors, and plan the filter and lifecycle story from the start. If it is repeated variable-depth traversal, a graph. If it is a nearby-points query, geospatial indexing, which PostGIS provides in PostgreSQL. If it is a hot read that tolerates bounded staleness, a cache. If the access is by one partition key at very high write volume, a wide-column store.

Benchmark with representative data and failure scenarios, not demo data: the p99 under concurrent writes, behaviour when a node dies mid-load, how long a full rebuild takes, and what happens when a consumer is stopped for an hour. And resist the pull to add a system: a well-indexed relational table frequently meets the requirement, and PostgreSQL ships full-text search and, with an extension, vector search. Starting there with a measured plan to migrate later is cheaper than operating a cluster you did not need. A typical outcome is that the first specialized store is a cache or a search index, and the rest are added when a measured requirement forces them, since every additional store adds a pipeline, a failure mode, a runbook and a rebuild procedure.

-- PostgreSQL 12+ (generated columns). Built-in full-text search before adding a search cluster.
ALTER TABLE products ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
  ) STORED;

CREATE INDEX products_search_gin ON products USING gin (search);

SELECT id, name, ts_rank_cd(search, q) AS rank
  FROM products, websearch_to_tsquery('english', 'wireless noise cancelling headphones') AS q
 WHERE search @@ q
 ORDER BY rank DESC
 LIMIT 20;
-- ts_rank_cd is not BM25: it has no document-length normalization by default and no corpus-wide IDF.
-- Whether that is good enough is a measurable question, not an assumption.

The same discipline applies to the claims you rely on. Statements about how fast an engine is, or how a large company arranges its stores, describe their data, their hardware and their requirements, and say little about yours. Use them to generate hypotheses, and settle them with a test of your own workload.

The index mechanics that search, vector and time-series engines specialize are in Indexing, and the guarantees the source of truth provides are in Transactions & Concurrency. Replication lag and quorum behaviour in the stores above follow the rules of Distributed Data, and sharding and partitioning decisions are in Scaling. For the delivery side of projections, continue with the Outbox Pattern, Queues & Workers and Idempotency.