PHASE 1Beginner ~22 min· topic 7 of 9

Topic 1.7

Real Search Engines

In one line

You rarely write an inverted index yourself. Lucene-based engines (Elasticsearch, OpenSearch), PostgreSQL full-text search and SQLite FTS5 all implement the ideas of this phase, with analysers, BM25 and fields built in. Know what each gives you, and when your database is enough.

Think of it like this

You can bake bread from grain you grind yourself, and now you know how. But for a bakery you buy flour and an oven. A search engine is the oven: it does the grinding (indexing), the timing (segments and merging) and the safety checks, so you spend your time on the recipe (analysis, fields, boosts, tests).

Words you'll meet

New words in this topic, in plain English. Come back here whenever one feels fuzzy.

Analyser: The pipeline of character filters, a tokenizer and token filters that turns text into terms.
Mapping / schema: The declaration of each field's type and analyser (text, keyword, number).
Lucene: The open-source search library inside Elasticsearch, OpenSearch and Solr.
tsvector / tsquery: PostgreSQL types for a document's terms and for a search query.
GIN index: PostgreSQL's index type for "many keys per row", used for full-text search.
FTS5: SQLite's full-text search module.
Shard: A piece of an index. Big indexes are split across shards so work can be shared.

Step by step

01Step 1: see the three stages

Run the example. Line 1 shows the character filter at work (the <p> tags are gone). Line 2 shows standard: tokenize and lower-case only. Line 3 shows english: stop words removed and riders stemmed to rider.

Step 1: see the three stagesdiagram

Rendering diagram…

02Step 2: use the same analyser at query time

The last two lines index the passage with english and query it two ways. With english on both sides the query shares rider must wear. With standard for the query, only must wear is shared, because riders is not rider.

03Step 3: PostgreSQL

The block below adds a generated tsvector column, a GIN index and a ranked query. It is shown, not run, because it needs a database.

04Step 4: SQLite FTS5

One virtual table, one MATCH and one bm25 call, with a weight for each column (titles worth five bodies).

05Step 5: Elasticsearch or OpenSearch

A mapping that analyses title and text with the built-in English analyser and stores code as an exact keyword, and a multi_match query with a title boost. The same JSON shapes work in OpenSearch.

06Step 6: choose

If you already run PostgreSQL and the data is modest, start there. If you need heavy tuning, many languages, or you will do hybrid search at scale, use a dedicated engine. Either way, run your Topic 1.6 harness against it.

Try it yourself

1
Add a character filter
Make strip_tags also turn - into a space. Which handbook word (hint: built-in) now gives different terms, and does it matter for search?
2
Mismatch on purpose
Make the query analyser standard and the index analyser english for a different query: Charges for late bikes. How many terms are shared? Then fix it by using the same analyser for both.
3
Read the SQL
In the PostgreSQL block, find where the title gets weight A and the body weight D. Which Topic 1.5 idea is this? Which line is the index that makes @@ fast?

Code & diagrams

An analyser pipeline, built from parts New tab

Real engines use the same three stages with ready-made, tested parts. The stemmer here is the crude one from Topic 1.1.

Expected output

html removed:       Riders under 16 must wear one.
standard:           riders under 16 must wear one
english:            rider under 16 must wear one
english / english:  rider must wear
english / standard: must wear

PostgreSQL: full-text searchsql

Shown, not run. Needs PostgreSQL 12 or newer for generated columns.

CREATE TABLE docs (
  id    text PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL,
  tsv   tsvector GENERATED ALWAYS AS (
          setweight(to_tsvector('english', title), 'A') ||
          setweight(to_tsvector('english', body),  'D')
        ) STORED
);

CREATE INDEX docs_tsv_idx ON docs USING GIN (tsv);

-- websearch_to_tsquery: plain words are ANDed, "quoted words" are a phrase, -word excludes.
SELECT id, title, ts_rank_cd(tsv, query) AS rank
FROM docs, websearch_to_tsquery('english', 'monthly pass cost') AS query
WHERE tsv @@ query
ORDER BY rank DESC
LIMIT 3;

SQLite: FTS5 with BM25sql

Shown, not run. bm25() returns negative numbers: smaller is better.

CREATE VIRTUAL TABLE docs USING fts5(title, body, tokenize = 'porter unicode61');

INSERT INTO docs (title, body) VALUES
  ('Monthly pass', 'The monthly pass costs 25 dollars and includes unlimited rides of up to 45 minutes.');

-- weights: title counts as 5 body hits. ORDER BY bm25 gives the best match first.
SELECT title, bm25(docs, 5.0, 1.0) AS score
FROM docs
WHERE docs MATCH 'monthly pass'
ORDER BY bm25(docs, 5.0, 1.0)
LIMIT 3;

Elasticsearch / OpenSearch: mapping and queryjson

Shown, not run. Two requests: create the index, then search it.

PUT /handbook
{
  "mappings": {
    "properties": {
      "title": { "type": "text", "analyzer": "english" },
      "text":  { "type": "text", "analyzer": "english" },
      "code":  { "type": "keyword" }
    }
  }
}

POST /handbook/_search
{
  "size": 3,
  "query": {
    "multi_match": { "query": "monthly pass cost", "fields": ["title^3", "text"] }
  }
}

Break it on purpose

Errors are the best teachers. Make each change, read the error, guess what went wrong, then reveal the answer.

Break #1

No character filter

In the Python tab, change strip_tags to just return text and run.

terminal

$ Run

── what you'll see ──

html removed: <p>Riders under 16 must wear one.</p>

standard: p riders under 16 must wear one p

english: p rider under 16 must wear one p

english / english: rider must wear

english / standard: must wear

Myth vs fact

Myth

Elasticsearch is a database, so it is a safe place for the only copy of the data.

Fact

Treat a search index as a derived copy that can be rebuilt from your source of truth. Engines are tuned for search, not for being the system of record.

Myth

PostgreSQL full-text search is the same as BM25.

Fact

It ranks by frequency and proximity and has no collection-wide idf by default. It is good enough for many products, and different from what the labs show.

Myth

Scores from two engines can be compared.

Fact

Different formulas, constants and per-shard statistics make raw scores incomparable. Compare rankings on your test set.

Pro corner

Extra depth for experienced readers. New to this? Skip it for now and come back later.

▸
Index time and query time can use different analysers on purpose (for example synonyms applied only at query time). If you do this, document it and test it, because it is the same mismatch the example shows when done by accident.
▸
Changing an analyser means re-indexing every document. Engines let you build a new index and switch an alias to it, with no downtime. Plan the migration before you need it (Topic 11.8 does the same for embeddings).
▸
BM25 statistics in a sharded engine are normally computed per shard, so the same document can score slightly differently depending on shard layout. On small indexes this shows up as odd rankings; engines offer a mode that gathers global statistics first.
▸
Use filters for exact conditions (tenant, language, date range) and queries for relevance. Filters are cached and do not affect scores, and they are the basis of access control at retrieval time (Topic 10.3).

Remember this

1
Why this exists. Everything in Topics 1.1 to 1.6 is real, but a production index also needs compression, concurrent updates, crash safety, sharding and many languages. Engines have solved that for years. Your job shifts from writing the index to configuring it correctly and checking the results against what you learned by hand.
2
The analyser pipeline. Every engine splits analysis into the same three stages, which the example builds: character filters (clean raw text, for example strip HTML tags), a tokenizer (cut into terms), and token filters (lower-case, remove accents, drop stop words, stem). You choose them per field. The example makes two analysers, standard (no stop words, no stemming) and english, and the last two lines show the Topic 1.1 golden rule failing: if the index uses english and the query uses standard, riders does not match the stored rider.
3
Lucene-based engines: Elasticsearch and OpenSearch. They store documents as JSON, build one inverted index per field, and score with BM25 by default (the same k1 and b knobs, usually 1.2 and 0.75; check your version's documentation). You declare a mapping: text fields are analysed and searched by words, keyword fields are stored whole for exact matches and filters. A multi_match query searches several fields with boosts such as title^3. They are separate servers, scale by sharding, and also store vectors (Phase 5).
4
PostgreSQL full-text search. If your data already lives in PostgreSQL, it can search without another system. to_tsvector('english', text) turns text into stemmed terms with positions, websearch_to_tsquery turns a user's string into a query (quotes become phrases), a GIN index makes it fast, and ts_rank_cd orders results. Its ranking is based on term frequency and proximity and is not BM25: there is no collection-wide idf or length normalisation by default, so rankings can differ from what you built here.
5
SQLite FTS5. A virtual table that gives an app or a small service full-text search inside one file. tokenize = 'porter unicode61' stems and handles Unicode, and bm25(table, weights...) returns a BM25 score. Careful: FTS5 returns it negated, so smaller is better and ORDER BY bm25(...) already puts the best first.
6
When is the database's own search enough? Often. As a rough rule of thumb rather than a hard limit (measure on your own data), up to a few million passages, with one or two languages, plain keyword needs and a team that already runs PostgreSQL, built-in search avoids a new system to deploy, secure, back up and keep in sync. A dedicated engine pays off for relevance tuning, many languages, heavy query load, faceting, or when you will add hybrid vector search at scale. Phase 5 returns to this choice for vectors.
7
Verify against your own data. An engine's defaults are not magic. Index the handbook, ask the harness questions of Topic 1.6, and look at the scores: engines can explain a score (Elasticsearch has an explain option) and you now know how to read one. Expect rankings, not raw numbers, to match your hand-built BM25: engines count statistics per shard or segment and may drop constant factors from the formula.

Explain it without notes

Name the three stages of an analyser and give an example of each.

When is PostgreSQL's built-in full-text search enough, and when would you choose a dedicated engine?

Why do two engines give different BM25 scores for the same query and data?

Practice

Write pipeline(*steps) that returns a function applying each step to a text in order. Build clean from str.lower, str.strip and a step that replaces - with a space, and print clean(" Built-In Chain ").

Write same_terms(a, b, analyze) that is true when analyze(a) == analyze(b), with analyze lower-casing, splitting into letters and digits and dropping one final s from words longer than 3 letters. Print it for ("Riders", "rider") and ("Riders", "ride").

Trade-offs

↔
Database full-text search: no new system and transactional with your data, with simpler ranking and fewer language options.
↔
A dedicated engine: strong relevance tools and scale, another service to operate and keep in sync.
↔
Your own in-memory index: perfect for learning, tests and tiny data sets, not for production corpora.

Done when you can

Done when you can name the three analyser stages and build a mini one.
Done when you can say what a text field and a keyword field are for.
Done when you can explain how PostgreSQL, FTS5 and Elasticsearch each rank results and which one is BM25.
Done when you can decide between a built-in database search and a dedicated engine for a given project.

Command Palette

Words you'll meet

Step by step

01Step 1: see the three stages

02Step 2: use the same analyser at query time

03Step 3: PostgreSQL

04Step 4: SQLite FTS5

05Step 5: Elasticsearch or OpenSearch

06Step 6: choose

Try it yourself

Add a character filter

Mismatch on purpose

Read the SQL

Code & diagrams

Break it on purpose

No character filter

Myth vs fact

Pro corner

Remember this

Explain it without notes

Practice

Trade-offs

Done when you can