Lesson 06 · Purpose-built engines

The specialists: time-series, search, vector

The final pattern in the field guide: databases that got faster by getting narrower. Each of these three engines takes one workload the general-purpose models handle awkwardly, assumes it's the only workload, and rebuilds storage around that assumption. The recurring move: exploit a structural property of the data that a general-purpose engine can't assume.

Time-series: data that only ever arrives in order

Metrics, sensor readings, price ticks: append-only, timestamp-ordered, mostly-recent queries, and: the exploitable property: adjacent values barely differ. CPU at 14:00:00 is 47.2%; at 14:00:10 it's 47.3%. General engines store each point as a full row; time-series engines store deltas of deltas:

Compress a metrics stream

One CPU gauge, 10-second resolution, one day = 8,640 points. Apply each layer and watch the bytes fall.

bytes / point
:
one day, one gauge
:
1000 gauges, 1 year
:
:

These are Facebook's Gorilla paper numbers (the basis of Prometheus's TSDB): timestamps at fixed intervals compress to ~1 bit each (delta-of-delta = 0), and near-identical floats XOR to mostly zeros. Add the lifecycle rules no general engine has: retention (drop raw data after 15 days: deletion by dropping whole time-chunks, free, versus the tombstone pain of Lesson 4) and rollups (keep 5-min averages for a year). Species: Prometheus (pull-based metrics + PromQL), InfluxDB, TimescaleDB (time-series as PostgreSQL extension: the incumbent absorbing again), Amazon Timestream: and your CloudWatch bills are this lesson, productized.

Search: the index turned inside out

Every index so far maps key → location. Text search needs the opposite: word → every document containing it: an inverted index, the structure that makes "find 'quorum' among a billion docs" a lookup instead of a crawl:

Query five documents both ways

A tiny corpus of incident reports. Search it with a LIKE-style scan, then through the inverted index. Try timeout, replica, or database timeout.

:

The index is built at write time: each document is analyzed: tokenized, lowercased, stemmed ("timeouts"→"timeout"), stop-words dropped: and its words posted to the index. That's also why search engines rank naturally: the posting lists carry frequencies, so scoring (TF-IDF, BM25: rare-in-corpus + frequent-in-doc = relevant) falls out of the same structure. The trade: writes become expensive analysis pipelines, index rebuilds loom over schema changes, and: critically: search engines relax durability and consistency (near-real-time visibility, no transactions), which is why Elasticsearch is almost always a secondary store, fed by CDC from the system of record (replication course, lesson 5), never the source of truth.

Vector: search by meaning

The newest specialist. An embedding model turns anything: text, image, user behavior: into a point in high-dimensional space where distance ≈ semantic similarity. The database's one job: given a query point, find its nearest neighbors, fast:

Nearest neighbors in embedding space

Support tickets embedded into 2-D (real systems: 768–3072 dims). Click anywhere to place a query: the store returns the 3 nearest. Notice "can't sign in" finding "login broken" with zero shared words.

Keyword search (previous lab) matches vocabulary; vector search matches meaning: and modern retrieval (including the RAG pattern behind LLM apps) runs both and merges ("hybrid search"). The engineering problem hiding inside: exact nearest-neighbor over a billion 1536-dim vectors is a full scan, so real engines use approximate indexes (HNSW's navigable small-world graphs: Lesson 5's data structure, repurposed; or IVF's cluster-then-probe) trading a point of recall for 1000× speed. Species: Pinecone, Weaviate, Qdrant, Milvus: and, on cue, the incumbent absorbing the challenger: pgvector puts HNSW inside PostgreSQL, next to your ACID tables.

The pattern across all three: each specialist deleted generality (transactions, ad-hoc queries, being the source of truth) to exploit one structural fact: time's monotonic order, text's finite vocabulary, similarity's geometry. Which is why they usually appear beside a general-purpose core rather than instead of it: system-of-record in Lesson 1/3, specialists fed downstream by CDC. That architecture has a name, and it's Lesson 7.

Check yourself

1. Metrics in a relational table hit write limits and disk bloat at ~50k points/sec. Why does a time-series engine handle the same stream comfortably?

Every advantage flows from one licensed assumption: data arrives in time order and is queried in time ranges. A general engine must support UPDATE of arbitrary rows and can't compress across rows it must access independently; the specialist bets the workload never needs that, and wins the bet by 10–20×.

2. Why do teams run Elasticsearch next to PostgreSQL instead of replacing it, syncing via CDC?

The division of labor follows the guarantees. The dual-writes-vs-CDC lesson from the replication course is exactly this architecture's plumbing: write to Postgres, let Debezium-style capture feed the index, and the search cluster becomes disposable/rebuildable: the safest kind of specialist.

3. A user searches your help center for "app keeps crashing on startup." The best article is titled "Application fails to launch." Which retrieval finds it, and how do real systems hedge?

Zero token overlap defeats any inverted index (stemming maps "crashing"→"crash", not →"fail"). Embeddings were trained on enough text to place synonymous sentences nearby. But pure vector search embarrassingly misses exact strings ("error 0x80070057") that keyword search nails: hence hybrid as the production default, including in RAG pipelines.