Search vs Database

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.

-- 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

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.

{
  "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

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".

-- 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.