Time-series & search stores
Time-series stores are write-optimized (LSM, downsampling, retention) for append-only metrics; search engines invert text into a term index so full-text is fast where SQL LIKE collapses. A specialized store means owning the dual-write problem — add it only when the primary can't.
The product manager asked for two “small” features: a dashboard showing each server’s CPU over the last 90 days, and a search box that finds products by any word in the title or description. Both went onto the existing Postgres primary because “it’s just more tables.” The metrics table grew by 50 million rows a day and its indexes thrashed the disk; the search ran WHERE description LIKE '%word%', which the database can’t use an index for, so every search became a full table scan that locked up under load. Neither feature was hard — they were just being asked of an engine built for a different shape of work. A relational primary is a brilliant transactional store and a terrible metrics firehose and a terrible search engine, and no amount of indexing changes that.
Time-series: a different write shape
By the end of this lesson you’ll know why those two features were expensive, which storage property each violates, and what the correct decision looks like the next time you see the same requirements.
Time-series data is a flood of timestamped, append-only measurements — server metrics, IoT sensor readings, application events, financial ticks. Its shape is unusual: writes vastly outnumber reads, almost nothing is ever updated or deleted individually, and queries are nearly always “aggregate this metric over this time window.” A time-series database (Prometheus, InfluxDB, TimescaleDB, AWS Timestream) is built around that shape, and three properties define it:
- Write-optimized storage (LSM-trees). Instead of updating data in place (which means a random disk seek per write — slow), a log-structured merge tree buffers writes in memory and flushes them to disk as sorted, immutable files, merging them in the background. The result is that writes are sequential appends — the single thing spinning and solid-state disks both do fastest — which is why an LSM store ingests far more write throughput than a traditional update-in-place (B-tree) store. The trade is more work at read time and during background compaction, which is fine because time-series is write-heavy.
- Downsampling. You don’t need per-second resolution for data from last year — you need it for the last hour. Downsampling pre-aggregates old data into coarser buckets (per-second → per-minute → per-hour), shrinking it by orders of magnitude while keeping the trend.
- Retention. Old raw data is automatically dropped after a window (keep 7 days raw, 90 days at 1-minute, 2 years at 1-hour). Retention is a first-class, declarative feature — the store deletes aged data for you, so storage stays bounded instead of growing forever.
Together these three properties mean a time-series store keeps ingesting at full speed while the data stays bounded and queryable over any time window — without any of that burden falling on your transactional primary. Without retention, the table grows without bound; without downsampling, old data at per-second resolution wastes storage and slows range queries; without LSM writes, the disk becomes the bottleneck before the data even lands.
A relational primary can store metrics, but it’s the wrong shape: B-tree indexes degrade as billions of rows churn, there’s no built-in downsampling or retention, and the write firehose competes with your transactional traffic for the same buffer cache and WAL — the exact poisoning you saw with blobs in 02-blob-and-object.
Search: why LIKE is not search
Ask yourself: when a user types a word into a search box, what should the engine be doing — and why can a B-tree index not help? The answer reveals why full-text search is a fundamentally different problem.
A search box that finds documents by any word looks like a database query, but it’s a fundamentally different access pattern. SQL’s WHERE description LIKE '%word%' forces a full scan: the leading wildcard means no B-tree index can help, so the engine reads and substring-matches every row. It also gives you no relevance ranking, no stemming (“running” matching “run”), no typo tolerance, no multi-field scoring. It works on a thousand rows and collapses on a million.
A search engine (Elasticsearch/OpenSearch, Lucene, Postgres’ own full-text via tsvector) solves this by building an inverted index. Where a normal index maps row → values, an inverted index maps the other way: for each term (word), it stores the list of documents that contain it. So “find documents containing payment and failed” becomes two cheap list lookups intersected — no scan. On top of the inverted index the engine layers the things LIKE can never do: tokenization and stemming (so “payments” finds “payment”), relevance scoring (which document is the best match, not just a match), fuzzy matching for typos, and aggregations (facets/filters). That’s why you reach for a search engine the moment “search” means more than “exact prefix match on one column.”
▸Why this works
Why is an inverted index fast for text where a B-tree is hopeless? A B-tree indexes a column’s value left-to-right, so it can find rows where a column starts with “pay” in log time — but '%word%' has the wildcard on the left, so there’s no prefix to seek on and the tree is useless; you scan everything. An inverted index sidesteps this by indexing content, not values: at write time the engine tokenizes each document into terms and, for every term, appends the document’s id to that term’s posting list. A query for “payment failed” then fetches two pre-built posting lists and intersects them — work proportional to how many documents contain those words, not to the size of the whole corpus. The cost is paid at write time (every document must be analyzed and the index updated) and in storage (the index can rival the data in size), which is precisely why search lives in a separate, write-amortized engine rather than as a feature bolted onto your transactional primary.
The senior decision: specialize, or overload the primary?
Every specialized store is a real cost — another system to run, monitor, back up, secure, and (the hard part) keep in sync. So the question is never “is a time-series DB better at metrics?” (obviously yes) but “is my primary actually failing at this workload, and is the failure worth a second system?” The honest progression:
- Start on the primary. Postgres has surprisingly capable full-text search (
tsvector) and time-series extensions (TimescaleDB). For modest scale, one store is far cheaper than the consistency headache of two. - Specialize when a measured limit hits — the metrics firehose is crowding out transactional traffic, or
LIKEsearches are scanning millions of rows and timing out. The limit, not the buzzword, justifies the second store. - Treat the specialized store as a derived index, not a source of truth. Your relational primary stays the system of record; the search engine and time-series store are read-optimized projections of it. This is the key framing: if the search index is lost, you rebuild it from the primary — you don’t lose data, only availability of that query path.
The dual-write consistency problem
The moment a write must land in two stores — the row in Postgres and the document in Elasticsearch — you have the dual-write problem, and it’s harder than it looks. Naive code writes to both in sequence: db.save(order); search.index(order). But these are two systems with no shared transaction. If the process crashes between the two calls, or the search write fails and the DB write succeeded, the stores diverge — the order exists but isn’t searchable, silently, forever. Retrying blindly can double-write; wrapping them in a “transaction” is impossible across two systems (no distributed transaction here). The robust patterns make the primary the single source of truth and derive the second store from its write log:
- Change Data Capture (CDC) — tail the database’s replication log (its WAL/binlog) and stream every committed change to the search/time-series store. The DB commit is the single atomic event; the index update is a downstream consumer that can retry and replay.
- Transactional outbox — in the same DB transaction that writes the order, also write an “index this order” event to an
outboxtable; a separate relay reads the outbox and pushes to the search engine. Because the order row and the outbox row commit atomically, the event can’t be lost.
Both share one principle: never trust two independent writes to stay consistent — make one store authoritative and propagate from its commit. Accepting that the derived store is eventually consistent (a new order is searchable a second later) is usually fine; pretending the dual write is atomic is the bug.
▸Common mistake
The classic dual-write failure is the “best-effort” index update buried in a request handler: write the row, then fire the search index call and ignore (or merely log) its failure to “keep the request fast.” It works in every test and demo, because tests don’t crash mid-handler and the network doesn’t drop in CI. In production it drifts slowly — a few documents fail to index during each deploy, a queue hiccup here, a timeout there — and three months later “search is missing some results” is an un-debuggable mystery because nothing recorded which writes were lost. The fix isn’t more retries in the handler; it’s removing the dual write entirely by deriving the index from the DB’s commit log (CDC or an outbox), so the act of committing the data is the act that guarantees it will be indexed.
Your metrics table on the relational primary takes 50M append-only rows/day, and its B-tree indexes now thrash the disk while competing with transactional traffic. Why is a time-series store the right move, mechanically?
You write each new order to Postgres, then call the search index in the same handler and log-and-ignore any failure 'to keep the request fast.' Months later, search is missing some orders unpredictably. What's the root cause and the fix?
A search engine finds documents by any word without scanning the corpus because it builds an _______ index: for each term it stores the list of documents that contain it, so a query intersects pre-built lists instead of substring-matching every row the way SQL LIKE must.
- 01What three properties make a time-series store fit metrics, and why does a relational primary fit badly?
- 02Why is SQL LIKE not search, and how does an inverted index fix it?
- 03State the dual-write problem and the two robust patterns that solve it.
A relational primary is a brilliant transactional store and a poor metrics firehose and a poor search engine. Time-series stores (Prometheus, InfluxDB, Timescale, Timestream) match the append-only metrics shape with three properties: LSM-tree storage that turns writes into fast sequential appends, downsampling that shrinks old data to coarser buckets, and retention that bounds storage by auto-dropping aged data. Search engines (Elasticsearch/OpenSearch, Lucene, Postgres tsvector) replace SQL’s scan-forcing LIKE with an inverted index — term → posting list of documents — so full-text queries intersect pre-built lists and gain stemming, relevance scoring, and fuzzy matching, paying the cost at write time. The senior decision isn’t “is the specialized store better” (it is) but “is my primary measurably failing this workload?” — start on the primary, specialize on a real limit, and treat the new store as a derived index with the primary as the source of truth. That framing exposes the real hazard: the dual-write problem. Two independent writes with no shared transaction silently diverge on any crash or failure, so you never best-effort the second write — you derive it from the DB’s commit log via CDC or a transactional outbox, making the act of committing the data the act that guarantees it gets indexed. Now when you see a handler that writes to the DB and then fires a search index call and logs any failure, you know exactly what the bug is, why it will surface in production months later, and which two patterns eliminate it at the architecture level.
Practice
Start at the top. Tasks go easiest → hardest: recall a fact, apply it to a case, then a senior-level stretch. Open one, attempt it, then reveal.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.