beginnerintermediateadvanced

System Design · Section 97

Search vs Database

Level: intermediate
Read: 18 min
Concepts: 3

A database index is the right tool whenever the question is structured — exact values, ranges, ordering, and combinations of those — and it is the only tool that can answer inside the same transaction as a write, which matters whenever the answer drives a decision that must be consistent with what is stored. Beyond the ordinary B-tree, relational databases carry index types covering far more than people assume: inverted indexes for array and JSON containment, trigram indexes for substring and fuzzy matching, partial indexes for hot subsets, and built-in full-text search with stemming and ranking. That last one is the middle ground teams routinely skip, and skipping it means adopting a whole second system for capabilities the database already had. A search engine genuinely earns its place when the question changes from "which rows match" to "which rows are most relevant": relevance ordering computed from term rarity and frequency rather than from a column, typo tolerance without enumerating variants, faceted counts over the entire matching set in one pass, and business signals blended into a single tunable score. Those four capabilities are real, and so are the four costs that arrive with them permanently — a second system to run and secure, a synchronisation pipeline that lags and breaks, an index that is derived data and must remain rebuildable, and mappings that make a schema change a reindex-and-switch rather than an ALTER. Once the same fact lives in two stores, exactly one is the system of record and the rest are derived. That rule breaks in two specific ways. On the write side, a dual write — database first, index second, no shared transaction — leaves the two permanently disagreeing after any failure between them, with no error outstanding and nothing to detect it; writing an outbox row inside the business transaction, or driving the index from a change-data-capture stream, gives a single ordered, replayable source that turns a sync failure into a retry and a lost index into a rebuild. On the read side, a derived store answers "which items match" and the system of record answers "what is true about this item" — so prices, balances, permission decisions and any count something depends on are read from the source, because an index can lag, can have been reindexed with a bug, and carries no transactional guarantee.

System Design overview

System Design for Beginners CoursefreeCodeCamp.org

What is true here

Structured filtering and transactional reads belong in the database; composite, inverted, trigram, partial and built-in full-text indexes cover more than most teams use.
A search engine is justified by relevance, fuzziness, faceting and blended ranking — and charges four permanent operational costs for them.
A dual write has no shared transaction, so a failure between the two writes diverges silently and permanently.
An outbox row inside the business transaction, or change data capture, gives one ordered replayable change source every derived store is built from.
Derived stores narrow candidates; the system of record decides truth — never serve a price, balance or permission from a lagging index.

What you will be able to do

Choose the right index type for a given question shape, and recognise when built-in full-text search is sufficient
State the four capabilities and four costs of adopting a search engine before adopting one
Replace a dual write with an outbox or change-data-capture flow and explain what each failure now does
Split a request between a derived store and the system of record so that stale data cannot produce a wrong or unsafe answer

Choosing the tool

What a database index already answers, and what a search engine adds — priced against what it costs.

When a database index is the right tool

standardintermediate

A database index is the right tool whenever the question is structured: exact values, ranges, ordering, and combinations of those, over data you also need to write transactionally. "Orders for this customer, placed in the last 30 days, status pending, newest first" is a structured question, and a composite index answers it with a single range scan that stays fast as the table grows. The database is also the only place that can answer such a question in the same transaction as a write, which matters whenever the answer drives a decision that has to be consistent with what is stored — checking remaining stock, computing a balance, validating a constraint. Beyond the ordinary B-tree, most relational databases carry index types that cover more than people expect: an inverted index type for array containment and JSON keys, a trigram index for substring and fuzzy matching, a partial index that indexes only the rows a hot query actually touches, and built-in full-text search with stemming and ranking. That built-in full-text search is the important middle ground — for many products it is genuinely sufficient, and adopting a separate search engine before exhausting it buys an extra system to run, sync, monitor and reindex in exchange for capabilities the database already had. The rule is that structured filtering and transactional reads stay in the database, and the next concept covers what genuinely justifies leaving it.

Think of it as

A well-organised filing cabinet with several kinds of tabs: alphabetical, by date, by status. Any question you can phrase as "find the range between these two tabs" is answered by walking straight to it. Questions of that shape are the overwhelming majority of what an application asks, and they are answered in the same place the records are actually kept — which is what lets you read and write in one consistent operation.

sql

-- equality columns first, then the sort column
CREATE INDEX orders_customer_status_created
    ON orders (customer_id, status, created_at DESC);

-- answered by one contiguous range scan
SELECT * FROM orders
 WHERE customer_id = $1
   AND status = 'pending'
 ORDER BY created_at DESC
 LIMIT 20;

-- and readable inside the same transaction as a
-- write, which no external index can offer

Question shapes a database index already answers

Exact match

B-tree

Range and order

B-tree, composite

Array / JSON containment

inverted index

Substring, fuzzy

trigram

Stemmed text with ranking

built-in full-text search

Index types and the question shape each one answers

Index types and the question shape each one answers
Index type	Answers	Example
B-tree	Equality, ranges, ordering	`status = 'pending' AND created_at > ...` ordered by date
Composite B-tree	Several predicates plus a sort, in one scan	`(customer_id, status, created_at DESC)`
Inverted (GIN-style)	Array containment, JSON key lookup	`tags @> ARRAY['urgent']`
Trigram	Substring and fuzzy matching	`name ILIKE '%anders%'`
Partial	A hot subset only, kept small	`WHERE status = 'pending'`
Built-in full-text	Stemmed text matching with ranking	`to_tsvector(body) @@ plainto_tsquery(...)`

Remember: A database index is the right tool for structured questions — exact values, ranges, ordering and their combinations — and it is the only tool that can answer inside the same transaction as a write. Composite indexes put equality columns before the range or sort column. Beyond B-trees, inverted, trigram, partial and built-in full-text indexes cover far more than people assume, and exhausting the built-in full-text search first avoids adopting a whole second system for capabilities the database already had.

When a search engine is the right tool

standardintermediate

A search engine earns its place when the question stops being "which rows match" and becomes "which rows are most relevant". Four capabilities mark that boundary. Text relevance means results are ordered by how well they match rather than by a column — a document mentioning a rare term three times ranks above one mentioning a common term ten times, which is what scoring functions like BM25 compute and what a database ORDER BY cannot express. Fuzzy matching tolerates typos and spelling variation, so a search for "recieve" finds "receive" without you enumerating misspellings. Faceting returns counts per category alongside the results — 412 in Electronics, 88 in Audio — computed over the whole matching set in one pass, which as separate database aggregates is one query per facet over the same filtered set. And specialised ranking lets you blend relevance with business signals — recency, stock level, margin, personalisation — into one score, tuned continuously without touching the data. Against that, four ongoing costs: a second system to run, secure and upgrade; a synchronisation pipeline that can lag and can break; the index being derived data that must stay rebuildable; and a schema that is largely immutable, so changes mean reindexing. The decision is worth making explicitly, because the capabilities are real and so is the cost, and the middle ground — the database's own full-text search — is frequently skipped.

Think of it as

A librarian versus a card catalogue. The catalogue answers "which books are by this author, published in this decade" perfectly and cheaply. The librarian answers "which of these books is most useful for my question", tolerates you half-remembering the title, tells you how many are in each section, and can be told to favour recent editions. That judgement is worth hiring someone for — and it is a hire, with a salary, not a filing tab you add for free.

json

{
  "query": { "multi_match": {
      "query": "wireles headphnes",
      "fields": ["title^3", "description"],
      "fuzziness": "AUTO"
  }},
  "aggs": { "by_category": {
      "terms": { "field": "category" }
  }}
}
// relevance-ordered results, typo tolerance and
// per-category counts, in one request

What you buy against what you pay

Capabilities gained

+Relevance ordering rather than column ordering
+Typo and variant tolerance without enumeration
+Per-category counts over the whole result set in one pass
+Business signals blended into a tunable score

Costs incurred, permanently

−A second system to run, secure and upgrade
−A sync pipeline that lags and breaks
−A derived index that must stay rebuildable
−Schema changes become reindex-and-switch operations

Four capabilities, and what each replaces

Four capabilities, and what each replaces
Capability	What it does	The database alternative, and why it falls short
Text relevance	Orders results by match quality (term rarity, frequency, field weights)	`ORDER BY` a column — expresses no notion of "better match"
Fuzzy matching	Tolerates typos and spelling variants	Trigram similarity works, but degrades on large corpora and complex queries
Faceting	Per-category counts over the whole matching set, in one pass	One aggregate query per facet, each re-filtering the same set
Specialised ranking	Blends relevance with business signals into one tunable score	Possible in SQL, but every tuning change is a query rewrite

Four ongoing costs, stated before adopting

Four ongoing costs, stated before adopting
Cost	Concretely
A second system	Run, secure, upgrade, capacity-plan, and be paged for
A sync pipeline	Lags, breaks, and needs its own monitoring and backfill path
Derived-data discipline	The index must always be rebuildable from the source
Immutable mappings	A schema change is a new index plus an alias switch, not an ALTER

Remember: A search engine is justified when the question becomes "most relevant" rather than "which match": relevance ordering, typo tolerance, faceting over the whole result set, and business signals blended into a tunable score. All four are real, and so are the four permanent costs — a second system, a sync pipeline that lags and breaks, an index that must stay rebuildable, and mappings that make a schema change a reindex. Exhaust the database's own full-text search before paying them.

Keeping the copies honest

One system of record, no dual writes, and the reads that may never come from a derived store.

One system of record per fact, and how the copies stay in step

coreadvanced

Once a system holds the same fact in more than one store — a database and a search index, a cache, a warehouse, a materialised feed — exactly one of them has to be the system of record, and every other copy has to be derived, rebuildable, and never authoritative. That rule sounds obvious and is broken in two specific ways. The first is the dual write: application code writes to the database and then writes to the search index, as two separate operations with no shared transaction, so any failure between them leaves the two permanently disagreeing with nothing to detect or repair it. The fix is to write once and derive the rest: record the change in the same transaction as the data — an outbox row — or read the database's own replication stream with change data capture, and drive the index from that. Both give a single ordered source of changes, both survive a crash, and both make the index reconstructible by replaying from the beginning. The second failure is on the read side: querying the derived store for a value that has to be correct. Search indexes are the usual offender, because they are convenient and already hold most of the record. Serving a price, a balance, a permission decision or a count that anything depends on from an index that lags, that may have been reindexed with a bug, and that carries no transactional guarantee, is how a system ends up charging the wrong amount or showing one customer another customer's document. The read rule is simple: derived stores answer "which items", the system of record answers "what is true about this item".

Think of it as

A card catalogue in a library. It tells you which shelves to walk to, and nobody would settle an argument about a book's contents by reading the card. The card is derived from the book, it can be out of date, and if the whole drawer burned you would retype it from the shelves rather than the other way round. Every derived store in a system is a card catalogue, and the mistakes are the same two: updating the card without updating the book, and quoting the card as though it were the book.

sql

-- one transaction, two effects, no dual write
BEGIN;
  UPDATE products SET price_cents = 4200
   WHERE id = $1;
  INSERT INTO outbox (aggregate, aggregate_id,
                      event, payload)
  VALUES ('product', $1, 'product.updated', ...);
COMMIT;
-- a relay reads outbox in order and updates the
-- search index, the cache and the warehouse.
-- Replaying outbox from the start rebuilds them.

What we're doing: Watch a dual write diverge, then watch the same failure under an outbox.

divergence.txttext

A price changes from 52.00 to 42.00.

DUAL WRITE
  14:02:01  UPDATE products ... COMMIT   ok
  14:02:01  index.update(product, 4200)
            -> the search cluster is briefly
               unreachable; the call raises
  14:02:01  the exception is logged and
            swallowed, because failing the
            request would be worse

  The database says 42.00. The index says
  52.00. Forever. Nothing will notice: no
  error is outstanding, no job reconciles
  them, and the next price change overwrites
  the index with a value that happens to be
  right again -- so the bug appears and
  disappears with no pattern.

OUTBOX
  14:02:01  UPDATE products ...
            INSERT INTO outbox ...   COMMIT
  14:02:01  relay tries to update the index
            -> unreachable; the outbox row is
               NOT marked processed
  14:02:04  retry
  14:02:09  retry -- succeeds
  Index now says 42.00.

  And if the index is lost entirely, replaying
  outbox from the beginning rebuilds it, because
  the change history lives with the data rather
  than in a call that already failed.

9: Swallowing the exception is the correct local decision — failing a price update because a search cluster blinked would be worse — which is exactly why the dual write is a design problem rather than an error-handling one.
16: This is what makes divergence so hard to find: the next unrelated change can silently repair it, so reports of stale prices are intermittent, unreproducible, and usually closed as user error.
26: The outbox row is the difference. The change is durable in the same transaction as the data, so the retry has something to retry from, and the relay's failure delays the update rather than losing it.

Why this works: Both designs face the same unreachable search cluster; only one of them can recover, because only one recorded the change somewhere durable before attempting to propagate it. That is the whole content of the rule: a derived store stays correct when there is a single ordered, replayable source of changes it can be rebuilt from.

Serving an authorization decision from the search index

Wrong

python

hits = index.search(query, filter={
    "visible_to": user.groups})   # ACLs copied
return hits                       # into the index
                                  # at index time

Better

python

hits = index.search(query, filter={
    "visible_to": user.groups})   # narrows, cheaply
ids  = [h.id for h in hits]
docs = db.fetch_authorized(ids, user)  # the
return docs                            # decision

What you see: A user sees a document in search results after their access was revoked, in the window before the index catches up — or indefinitely, if the reindex that would have removed it failed. Opening the document works, because the same stale filter authorised the fetch.

Why: An index copy of an ACL is as old as the last successful sync, and a permission decision is exactly the kind of value that cannot be slightly stale. Using the index filter to narrow the candidate set and the system of record to make the decision keeps the search fast while making the authorisation correct — and it fails safe, because a document the database will not authorise is dropped regardless of what the index believed.

Write once, derive everything else

Application

System of record

the one authoritative copy

Outbox / change stream

one ordered source of changes

Search index

derived — answers "which items"

Cache

derived

Warehouse

derived

Direct second write

no shared transaction — permanent divergence

Three ways to keep a derived store in step

Three ways to keep a derived store in step
Approach	How	Failure behaviour
Dual write	Application writes the database, then writes the index	A crash between the two disagrees permanently, silently, with no repair path
Transactional outbox	Change row written in the business transaction; a relay publishes it	The change is durable with the data; a relay outage delays, never loses
Change data capture	A connector reads the database replication stream	Same guarantee with no application change; adds an operational component

What may and may not be read from a derived store

What may and may not be read from a derived store
Read	From the derived store?	Why
"Which documents match this query?"	Yes — this is what it is for	The result set is the derived store's purpose
Ordering and relevance	Yes	Computed by the index; nothing authoritative depends on it
Approximate result counts	Yes, if displayed as approximate	Presented at the precision actually offered
Price, balance, quota remaining	No	Can be stale; drives money and cannot be wrong
Permission decisions	No	A stale ACL in an index is a data leak
Counts anything depends on	No	Index lag makes the number wrong in a way nothing detects

Remember: One store is the system of record for a fact; every other copy is derived, rebuildable and never authoritative. Do not dual-write — record the change in the same transaction as the data (an outbox) or read the replication stream (change data capture), so there is one ordered, replayable source every derived store is built from. And on the read side, let derived stores answer "which items match" while the system of record answers "what is true about this item": prices, balances, permissions and any count something depends on are read from the source, not from an index that lags.