Search Engines

The inverted index. For every term, a posting list of the documents containing it, with positions and frequencies. A query intersects posting lists and scores the results by relevance (BM25 or similar: rarer terms count more, term frequency helps, long documents are penalised). The analysis chain in front of it — tokenisation, lowercasing, stemming, stop words, synonyms, language-specific rules — determines what actually matches, and it must be identical at index time and query time or queries silently miss.

Start with Postgres. tsvector with a GIN index handles full-text search on a few million rows perfectly well, and it keeps your search transactionally consistent with your data. Saying this first is the senior move; reaching for Elasticsearch by reflex is not.

Reach for a dedicated engine when you need relevance tuning and boosting, faceted aggregations over large result sets, typo tolerance and fuzzy matching, multi-language analysis, or per-query latency across tens of millions of documents. Those are real needs, and Postgres does them poorly or not at all.

The cost you take on is synchronisation. The index is a second copy of your data, and it will drift. The options, in ascending order of robustness: dual-write from the application (simplest, and wrong the first time a write partially fails), a periodic reindex job (safe, stale, expensive at volume), or change data capture from the database's replication log into the indexer (correct, more moving parts). Whatever you choose, you also need a full reindex path, because mappings change and you will need to rebuild — usually by indexing into a new index and atomically swapping an alias.

Near-real-time, not real-time. Documents become searchable after a refresh interval, typically around a second. Any design that requires a write to be searchable immediately in the same request needs to read that record from the primary database instead.

Sharding and replicas work the usual way: shard count is fixed at index creation, queries fan out to all shards and merge, so over-sharding a small index costs latency rather than saving it.

Search appears inside many design problems — a marketplace, a feed, a docs site, log search — and the interesting question is never "how does an inverted index work" but "how does the index stay in sync with the database, and what do users see when it doesn't." It's also the direct precursor to retrieval in AI systems: hybrid search in RAG is a BM25 engine and a vector index answering the same query.

• Inverted index plus relevance scoring is what a search engine adds over a database LIKE.
• Analysis must match at index and query time, or matches silently disappear.
• Postgres full-text with a GIN index is the correct default at small-to-mid scale and stays consistent with your data.
• Add a dedicated engine for relevance tuning, facets, fuzziness, or many millions of documents.
• The real cost of a search engine is keeping it in sync — CDC beats dual writes, which break on partial failure.
• Always have a full reindex path: build into a new index and swap an alias atomically.
• Search is near-real-time; read-your-own-write must be served from the primary database.
• Hybrid retrieval in RAG is this system plus a vector index over the same corpus.