System Design
B8

Search: inverted indexes, ranking and sync

Find documents by the words in them, ranked by relevance. This sheet runs a small BM25 engine and Postgres full text search on the same catalog, and records where they differ.

Not startedSaved in this browser only.
  1. 1An inverted index maps each term to the documents that hold it. A query reads a few posting lists, not every document.
  2. 2BM25 scores a match by how rare the term is, how often it appears, and how long the field is.
  3. 3Postgres ts_rank has no IDF. On the lab catalog it ranked a dry bag second for "waterproof tent".
  4. 4Feed the index from the change log, with versions. A dual write leaves the index stale after a crash.
B8
    A

    The inverted index

    recorded from the lab analyzer
    1 · analyze: lowercase, split, drop stop words, stemwordstermswaterproof0waterproofrain1rainjacket2jacketwith3stoptaped4tapeseams5seamand6stopa7stophood8hoodBold terms were stemmed. Positions count every word, so phrase queries still work.2 · index: term → posting list (document, field and position)p04 Rain jacket · p05 Insulated jacket · p17 Running jacketjacketp04t1 b2p05t1 b2p17t1 b2df 3rainp04t0 b1p05b15df 2runp17t0 b4df 1waterproofp04b0p05b7df 2

    I analyze text into terms once at write time, and store for each term the documents and positions that hold it. A query reads only its terms' lists.

    B

    The problem: LIKE reads every row

    measured, 1 million documents
    Median query time, one core0.11101001kLIKE '%word%': 779 msLIKE '%word%'779GIN, rare word, count: 0.218 msGIN, rare word, count0.218GIN, rare word, top 10: 0.228 msGIN, rare word, top 100.228GIN, 2% of docs, top 10: 22.6 msGIN, 2% of docs, top 1022.6GIN, 53% of docs, top 10: 458 msGIN, 53% of docs, top 10458ms, log scale
    • LIKE cannot rank, stem or skip rows.
    • Ranking a word in 53% of documents reads every match: 458 ms.

    LIKE with a leading wildcard scans the table: 779 ms at 1 million documents. A GIN index answers the same word in 0.218 ms.

    C

    Options

    find text in a store
    methodwherestatus
    LIKE '%word%' with no indexPostgresNot approved
    B-tree on the columnPostgresWhole-value prefix
    pg_trgm GIN indexPostgresSubstrings, typos
    tsvector, GIN, ts_rankPostgresRank without IDF
    Search engine with BM25Search engineApproved
    Dual write from the serviceServiceNot approved
    Change log, indexer with versionsCDCApproved
    Query the primary on each keystrokePostgresNot approved

    A search engine here means a Lucene-based cluster such as Elasticsearch or OpenSearch. The lab models it with a Go engine.

    I start with Postgres full text search and a GIN index. I add a search engine when relevance, typos or facets become the product.

    D

    Analysis

    pseudo code
    text to termspseudo code
    analyze(text):        // at index time and query time1
      words = split lowercase(text) on non-letters
      FOR EACH word, position3 IN words:
        IF word is a stop word: skip2    // the, a, for
        emit (stem(word), position)     // running -> run4
    1. 1Terms only meet if both sides apply the same steps.
    2. 2Stop words are in nearly every document. Their IDF is near 0 and their posting lists are the longest.
    3. 3Positions count stop words too, so a phrase keeps its gaps.
    4. 4A stem need not be a word: the lab stems insulated to insulat. It only has to match itself.
    Tested source Go: analyze
    Go: analyzego
    // Analyze lowercases text, splits it on anything that is not a letter or a digit, drops stop
    // words and stems each word. Index time and query time use the same function, so a query term
    // and a document term meet in the same form.
    func Analyze(text string) []Token {
      words := strings.FieldsFunc(strings.ToLower(text), func(r rune) bool {
        return !unicode.IsLetter(r) && !unicode.IsDigit(r)
      })
      out := make([]Token, 0, len(words))
      for pos, w := range words {
        if stopWords[w] {
          continue
        }
        out = append(out, Token{Term: Stem(w), Pos: pos})
      }
      return out
    }
    

    Index and query go through the same analyzer. If they differ, a stemmed document term never meets an unstemmed query term.

    E

    Ranking: BM25

    pseudo code
    score one documentpseudo code
    score(doc, query):          // k1 = 1.2, b = 0.755
      FOR EACH term IN query, FOR EACH field:
        // rare term: high weight
        idf  = ln(1 + (N - df + 0.5) / (df + 0.5))1
        tf   = times the term is in the field
        // long field: lower score
        norm = k1 * (1 - b + b * len / avg_len)2
        score += boost4 * idf * tf * (k1 + 1) / (tf + norm)3
      RETURN score
    1. 1IDF: a term in every document scores about 0; a term in 1 of N scores about ln N.
    2. 2Length norm: the same count in a field longer than average scores lower. b sets how much.
    3. 3Saturation: the score approaches idf × (k1 + 1) and never passes it.
    4. 4A field weight: a match in the title can count 3 times a match in the body.
    5. 5The Lucene and Elasticsearch defaults.
    Tested source Go: idf and bm25
    Go: idf and bm25go
    // idf is the Lucene form of inverse document frequency. A term in every document scores near 0;
    // a term in one document out of N scores near ln(N).
    func idf(n, df int) float64 {
      return math.Log(1 + (float64(n)-float64(df)+0.5)/(float64(df)+0.5))
    }
    
    // bm25 scores one term in one field. Term frequency saturates: the fifth occurrence adds far less
    // than the first. A field longer than average gets a lower score for the same count.
    func bm25(tf int, idf float64, length int, avgLen, k1, b float64) float64 {
      norm := k1 * (1 - b + b*float64(length)/avgLen)
      return idf * float64(tf) * (k1 + 1) / (float64(tf) + norm)
    }
    

    BM25 multiplies IDF by a saturating term frequency, normalized by field length. A rare word counts most, and repeating a word stops paying.

    F

    Try it: one catalog, three rankers

    recorded from the Go engine and Postgres
    title boost

    Search engineterms: waterproof · tent

    In the body text, tent is in 5 of 24 products and waterproof is in 9. BM25 gives the rarer word more weight and ranks three tents first. ts_rank has no IDF, so the dry bag (waterproof 4 times, no tent) comes second.

    Click a result to see the score of each term.

    24 products, title and body. ts_rank weights title words A and body words B, and ORs the words as the engine does. Trigram mode replaces each word with catalog words of similarity 0.3 or more.

    On the same 24 products, BM25 and ts_rank agree on stemming and phrases, and disagree on rare words and typos. I can say why in each case.

    G

    Phrase, prefix and typo queries

    pseudo code
    query typespseudo code
    phrase(t1 t2):
      docs WHERE t2 is at position(t1) + 11
    prefix(p):
      terms that start with p, from the sorted dictionary2
      -> one OR query over those terms
    fuzzy(w):
      dictionary terms within AUTO edits3 of w
      // 0 edits up to 2 letters, 1 up to 5, else 2
    1. 1The posting list holds positions, so the check runs inside the index. No document is read.
    2. 2All terms with one prefix sit next to each other: one binary search finds the first.
    3. 3An edit is an insert, a delete, a change or a swap of two neighbours.
    Tested source Go: phrase · Go: prefix · Go: fuzzy
    Go: phrasego
    // Phrase ranks documents where the terms appear next to each other, in order, in one field. It
    // walks the position list of the first term and checks that term i sits at position p + i.
    func (ix *Index) Phrase(text string, p Params, k int) []Hit {
      toks := Analyze(text)
      if len(toks) == 0 {
        return nil
      }
      match := map[int32]bool{}
      for f := range numFields {
        for _, first := range ix.postings[f][toks[0].Term] {
          for _, start := range first.pos {
            if ix.hasPhraseAt(Field(f), first.doc, toks, start) {
              match[first.doc] = true
              break
            }
          }
        }
      }
      terms := make([]string, len(toks))
      for i, t := range toks {
        terms[i] = t.Term
      }
      return ix.scoreFiltered(terms, p, k, func(d int32) bool { return match[d] })
    }
    
    Go: prefixgo
    // PrefixTerms finds every term that starts with prefix. The dictionary is sorted, so the matches
    // are one contiguous run: two binary searches find it.
    func (ix *Index) PrefixTerms(prefix string, max int) []string {
      d := ix.Dictionary()
      lo := sort.SearchStrings(d, prefix)
      var out []string
      for i := lo; i < len(d) && strings.HasPrefix(d[i], prefix); i++ {
        if len(out) == max {
          break
        }
        out = append(out, d[i])
      }
      return out
    }
    
    Go: fuzzygo
    // AutoFuzziness is the edit budget by term length: 0 edits up to 2 characters, 1 edit for 3 to 5,
    // 2 edits above 5. Elasticsearch calls this AUTO.
    func AutoFuzziness(term string) int {
      switch n := len(term); {
      case n <= 2:
        return 0
      case n <= 5:
        return 1
      }
      return 2
    }
    
    // FuzzyTerms finds dictionary terms within the edit budget of term. An edit is an insert, a
    // delete, a substitution, or a swap of two neighbours.
    func (ix *Index) FuzzyTerms(term string) []string {
      budget := AutoFuzziness(term)
      var out []string
      for _, t := range ix.Dictionary() {
        if Distance(term, t) <= budget {
          out = append(out, t)
        }
      }
      return out
    }
    
    queryin Postgresin a search engine
    phrasephraseto_tsquerymatch_phrase
    prefixto_tsquery('wat:*')prefix, edge n-grams
    typopg_trgm, word % 'jaket'fuzziness AUTO
    user syntaxwebsearch_to_tsquerysimple_query_string

    Phrases use positions, prefixes use the sorted term dictionary, and typos use edit distance against the dictionary.

    H

    Where the two differ

    each row is asserted by a lab test
    queryBM25 enginePostgres
    waterproof tent3 tents firstDry bag second
    running shoesrun, shoerun, shoe
    "rain jacket"1 match1 match
    wat…3 terms:* prefix
    jaket1 editTrigrams only
    jakcet1 swapNo match
    typo fallback in Postgressql
    -- Swap each query word for look-alike catalog words, then rank.
    SELECT id, ts_rank(tsv, q) AS rank
    FROM products,
         (SELECT to_tsquery('english', string_agg(word, ' | ')2) AS q
          FROM regexp_split_to_table(lower($1), '\s+') AS qw, words
          WHERE word % qw1) AS query
    WHERE tsv @@ q
    ORDER BY rank DESC, id
    LIMIT 10;
    1. 1True when trigram similarity is at least 0.3, the default threshold.
    2. 2Search for any look-alike word, then rank as usual.
    Tested source Go: the claims checked on the recorded results
    Go: the claims checked on the recorded resultsgo
    // checkResultClaims asserts every difference the sheet states between the engine and Postgres.
    func checkResultClaims(t *testing.T, res map[string]QueryResult) {
      t.Helper()
      bm := func(q, boost string) []string { return ids(res[q].BM25[boost]) }
      pg := func(q string) []string { return ids(res[q].PGRank) }
      trgm := func(q string) []string { return ids(res[q].PGTrigram) }
    
      // IDF: "tent" is rare and "waterproof" is common. BM25 puts three tents above the dry bag,
      // which says waterproof 4 times but never tent. ts_rank has no IDF and puts the bag second.
      if b := bm("waterproof-tent", "1"); pos(b, "p08") < 3 || !slices.Equal(b[:3], []string{"p06", "p21", "p07"}) {
        t.Errorf("waterproof tent, BM25: %v; want p06 p21 p07 first, then the dry bag p08", b)
      }
      if p := pg("waterproof-tent"); pos(p, "p08") != 1 {
        t.Errorf("waterproof tent, ts_rank: %v; want the dry bag p08 second", p)
      }
      // Both stem running to run and shoes to shoe. Neither stems runner, so the vest is missed.
      if r := res["running-shoes"]; !slices.Equal(r.Terms, []string{"run", "shoe"}) || r.TSQuery != "'run' | 'shoe'" {
        t.Errorf("running shoes: terms %v, tsquery %s", r.Terms, r.TSQuery)
      }
      if slices.Contains(bm("running-shoes", "1"), "p18") || slices.Contains(pg("running-shoes"), "p18") {
        t.Error("running shoes: the runner vest p18 should match neither engine")
      }
      // Words match "jacket ... rain"; the phrase matches only "rain jacket".
      if !slices.Contains(bm("rain-jacket", "1"), "p05") || !slices.Contains(pg("rain-jacket"), "p05") {
        t.Error("rain jacket as words: both engines should match p05")
      }
      if !slices.Equal(bm("rain-jacket-phrase", "1"), []string{"p04"}) || !slices.Equal(pg("rain-jacket-phrase"), []string{"p04"}) {
        t.Errorf("rain jacket as a phrase: %v and %v, want only p04", bm("rain-jacket-phrase", "1"), pg("rain-jacket-phrase"))
      }
      // A prefix expands to every dictionary term that starts with it.
      if e := res["wat"].Expansions["wat"]; !slices.Equal(e, []string{"watch", "water", "waterproof"}) || top(bm("wat", "1")) != "p09" || top(pg("wat")) != "p09" {
        t.Errorf("wat: expansions %v, tops %s and %s", e, top(bm("wat", "1")), top(pg("wat")))
      }
      // Typos: full text search finds nothing. Edit distance finds both. Trigrams find the
      // missing letter (similarity 0.44) and miss the swapped pair (0.27, under 0.3).
      for _, q := range []string{"jaket", "jakcet"} {
        if len(pg(q)) != 0 || !slices.Equal(res[q].Expansions[q], []string{"jacket"}) || len(bm(q, "1")) != 3 {
          t.Errorf("%s: ts_rank %v, fuzzy %v, BM25 %v", q, pg(q), res[q].Expansions[q], bm(q, "1"))
        }
      }
      if res["jaket"].TrgmSim["jacket"] < 0.3 || res["jakcet"].TrgmSim["jacket"] >= 0.3 {
        t.Errorf("similarity to jacket: jaket %v, jakcet %v; want one above 0.3, one below", res["jaket"].TrgmSim, res["jakcet"].TrgmSim)
      }
      if len(trgm("jaket")) != 3 || len(trgm("jakcet")) != 0 {
        t.Errorf("trigram: jaket %v, jakcet %v; want 3 hits, then none", trgm("jaket"), trgm("jakcet"))
      }
      // Stop words leave the query; a title boost changes the order.
      if r := res["stop-words"]; !slices.Equal(r.Terms, []string{"tent", "rain"}) || r.TSQuery != "'tent' | 'rain'" {
        t.Errorf("stop words: terms %v, tsquery %s", r.Terms, r.TSQuery)
      }
      if top(bm("stop-words", "1")) != "p21" || top(bm("stop-words", "3")) != "p04" {
        t.Errorf("title boost: top %s at x1, %s at x3; want p21, then p04", top(bm("stop-words", "1")), top(bm("stop-words", "3")))
      }
    }
    

    Postgres full text search stems and parses phrases well. It has no IDF and no typo tolerance, and pg_trgm misses a swapped letter pair in a short word.

    I

    Schema

    Postgres full text search
    table
    1 million documents of 20 words: 370 MB with the stored vector.
    GIN index
    66 MB, about 18% of the table.
    trigrams
    A second GIN index, on the text, for typo fallback.
    log
    One change row per write, read in order by the indexer.
    • Weight A for the title, B for the body. ts_rank multiplies matches by 1.0 and 0.4 by default.
    • The english configuration stems with Snowball and drops stop words.
    productssql
    CREATE EXTENSION IF NOT EXISTS pg_trgm;
    
    CREATE TABLE products (
      id      text   PRIMARY KEY,
      title   text   NOT NULL,
      body    text   NOT NULL,
      version bigint NOT NULL DEFAULT 1,
      -- Title words weigh A, body words B.
      -- Postgres recomputes the vector on every write.
      tsv tsvector GENERATED ALWAYS AS1 (
        setweight(to_tsvector('english', title), 'A')2 ||
        setweight(to_tsvector('english', body), 'B')
      ) STORED
    );
    
    -- An inverted index: lexeme -> rows that contain it.
    CREATE INDEX products_tsv ON products USING gin (tsv)3;
    
    -- Trigrams of the whole text, for typo-tolerant matching.
    CREATE INDEX products_trgm ON products
      USING gin ((title || ' ' || body) gin_trgm_ops4);
    1. 1Postgres computes the vector on every insert and update. No application code can forget it.
    2. 2Field weights: a title match ranks above a body match.
    3. 3The inverted index: lexeme to the rows that contain it.
    4. 4Indexes 3-letter pieces, so LIKE, ILIKE and similarity can use an index.

    I keep a generated tsvector column with field weights and a GIN index on it. A trigger writes the change log in the same transaction.

    J

    The request path

    click a step; its path lights up
    Usersearch boxCatalog serviceN copiesPostgresrows, change logCapture, indexWAL, versionsSearch clustershards, replicas

    Step 1: Write

    • Save the product in one transaction.
    • The same transaction appends the change to a log. No call to the search cluster.

    If it fails

    The write fails: nothing is logged, and nothing reaches search.

    Postgres is the truth. Search is a copy fed from the change log, and the page takes current values such as price and stock from Postgres.

    K

    Capabilities used

    what each tool gives you
    toolcapabilitywhat it gives this designalso used for
    Postgresto_tsvector with a language configurationSplit, stop words and a Snowball stemmer inside the database.Search on small corpora
    PostgresGIN index on tsvectorAn inverted index: lexeme to rows.JSONB, arrays
    Postgreswebsearch_to_tsquery, phraseto_tsquery, :* prefixUser query syntax, phrases with <->, prefixes.Admin search screens
    Postgrests_rank and ts_rank_cd, setweight A to DRank by count, proximity and field weight.
    Postgrests_rank limitsLimit No global statistics, so no IDF. It reads the vector of every match.
    Postgrespg_trgm with GINTypo-tolerant similarity and indexed LIKE '%word%'.Did you mean, name search
    PostgresTriggers and logical decodingA change log written with the row, or read from the WAL.Outbox, audit, replication
    Search engineAnalyzers per field: tokenizer, filters, synonymsDifferent text rules for titles, codes and prose.Multi-language search
    Search engineBM25 by default (k1 1.2, b 0.75), field boostsRelevance with IDF; tuning without code.Logs, observability
    Search engineFuzzy queries, fuzziness AUTO0, 1 or 2 edits by term length.Name matching
    Search engineEdge n-grams, completion suggesterTypeahead served from the index.Autocomplete
    Search engineShards, replicas, query then fetchParallel ranking per shard; replicas add read capacity.Aggregations, facets
    Search engineRefresh interval, 1 second by defaultNear real time: a write is searchable after the next refresh.Bulk loads with refresh off
    Search engineIndex aliasesBuild a new index, then move the alias in one step. Reads never stop.Schema changes, rollbacks
    CDCLog-based change data captureReads committed changes in commit order and publishes them.Cache invalidation, analytics

    Postgres gives me stemming, a GIN inverted index, phrases, prefixes and trigrams. A search engine adds BM25, analyzers per field, fuzzy queries, shards and aliases.

    L

    Keep the index in sync

    pseudo code, then the race it avoids
    change log to indexpseudo code
    on every write, in the same transaction1:
      append (seq, id) to the change log        // a trigger, or WAL decoding
    indexer loop:
      from = saved position
      FOR EACH change after from, in order:
        row = current row for change.id2
        IF row is gone: delete id at its version
        ELSE: upsert row at row.version         // older versions are ignored3
      save position                             // crash before this: replay4
    1. 1The change exists if and only if the write committed. No crash can split them.
    2. 2The indexer reads the latest row, so it never applies stale content from an old change.
    3. 3Replays and reordered changes become no-ops. A delete is remembered with its version too.
    4. 4At-least-once delivery. The version check makes it effectively once.
    Tested source Go: catch up · Go: upsert with version · SQL: change log and trigger
    Go: catch upgo
    // CatchUp reads changes after the saved position, applies each one with the version of the
    // current row, then saves the new position. A crash before the save replays some changes; the
    // version check makes the replay harmless.
    func (w Indexer) CatchUp(ctx context.Context, crashBeforeSave bool) (int, error) {
      var from int64
      if err := w.DB.QueryRow(ctx, stmts["load_checkpoint"], w.Name).Scan(&from); err != nil {
        return 0, fmt.Errorf("load checkpoint: %w", err)
      }
      rows, err := w.DB.Query(ctx, stmts["read_changes"], from, 1000)
      if err != nil {
        return 0, fmt.Errorf("read changes after %d: %w", from, err)
      }
      type change struct {
        seq, version int64
        id, op       string
        title, body  *string
      }
      changes, err := pgx.CollectRows(rows, func(r pgx.CollectableRow) (change, error) {
        var c change
        err := r.Scan(&c.seq, &c.id, &c.op, &c.title, &c.body, &c.version)
        return c, err
      })
      if err != nil {
        return 0, fmt.Errorf("scan changes: %w", err)
      }
      applied := 0
      for _, c := range changes {
        if c.title == nil { // the row is gone: the latest state is "deleted"
          if w.Ix.Delete(c.id, c.version) {
            applied++
          }
        } else if w.Ix.Upsert(Doc{ID: c.id, Title: *c.title, Body: *c.body, Version: c.version}) {
          applied++
        }
        from = c.seq
      }
      if crashBeforeSave {
        return applied, ErrCrash
      }
      if _, err := w.DB.Exec(ctx, stmts["save_checkpoint"], w.Name, from); err != nil {
        return applied, fmt.Errorf("save checkpoint %d: %w", from, err)
      }
      return applied, nil
    }
    
    Go: upsert with versiongo
    // Upsert adds a document or replaces the live copy with the same id. A document carries a
    // version; a version at or below the last one applied is ignored, so a replayed or reordered
    // change is harmless. Version 0 means "unversioned" and always applies.
    func (ix *Index) Upsert(d Doc) bool {
      if d.Version != 0 && d.Version <= ix.versions[d.ID] {
        return false
      }
      ix.versions[d.ID] = d.Version
      if n, ok := ix.current[d.ID]; ok {
        ix.docs[n].live = false
      }
      n := int32(len(ix.docs))
      info := docInfo{doc: d, live: true}
      for f := range numFields {
        toks := Analyze(d.field(f))
        info.len[f] = len(toks)
        byTerm := map[string][]int32{}
        var order []string
        for _, t := range toks {
          if _, seen := byTerm[t.Term]; !seen {
            order = append(order, t.Term)
          }
          byTerm[t.Term] = append(byTerm[t.Term], int32(t.Pos))
        }
        for _, term := range order {
          ix.postings[f][term] = append(ix.postings[f][term], posting{doc: n, pos: byTerm[term]})
        }
      }
      ix.docs = append(ix.docs, info)
      ix.current[d.ID] = n
      ix.dict = nil
      return true
    }
    
    SQL: change log and triggersql
    -- Every write appends a change in the same transaction.
    -- An indexer reads this log in order.
    CREATE TABLE product_changes (
      seq        bigserial PRIMARY KEY,
      product_id text      NOT NULL,
      version    bigint    NOT NULL,
      op         text      NOT NULL CHECK (op IN ('upsert', 'delete'))
    );
    
    CREATE TABLE indexer_checkpoint (
      name text   PRIMARY KEY,
      seq  bigint NOT NULL
    );
    
    CREATE OR REPLACE FUNCTION log_product_change() RETURNS trigger AS $$
    BEGIN
      IF TG_OP = 'DELETE' THEN
        INSERT INTO product_changes (product_id, version, op)
        VALUES (OLD.id, OLD.version + 1, 'delete');
      ELSE
        INSERT INTO product_changes (product_id, version, op)
        VALUES (NEW.id, NEW.version, 'upsert');
      END IF;
      RETURN NULL;
    END $$ LANGUAGE plpgsql;
    
    CREATE TRIGGER products_log AFTER INSERT OR UPDATE OR DELETE ON products
      FOR EACH ROW EXECUTE FUNCTION log_product_change();
    Writer AWriter BPostgresSearch indextitle = "blue"t1title = "red"t2index "red"t3index "blue", latet4now "red"now "blue"✕ Postgres says red. The index says blue until the next write.A crash between the two writes is worse: the index never hears of the change.
    • The lab proves the race above, the crash, and convergence after a replay.
    • A sequence number is taken at insert, not at commit. WAL decoding reads in commit order, so it never skips a slow transaction.

    I never dual write. The write and its change record commit together, and an indexer applies changes in order, with versions, from a saved position.

    M

    Typeahead

    top 5 per prefix, precomputed

    GET "wa"5 of 5

    1. 3,000
    2. 600
    3. 545
    4. 500
    5. 375

    220 prefixes, 320 stored completions, for 30 distinct queries.

    prefix tablepseudo code
    build(log, k):
      FOR EACH query, count IN log:
        FOR EACH prefix of query, up to 12 characters1:
          table[prefix] += (query, count)
      keep the k most searched2 in each table[prefix]
    lookup(prefix):
      RETURN table[prefix]                      // one read per keystroke3
    1. 1Longer prefixes add entries and rarely change the answer.
    2. 2Storage is k entries per prefix, whatever the log size.
    3. 3Serve it from memory, Redis or a CDN. The table rebuilds every few minutes from the log.
    Tested source Go: build the table
    Go: build the tablego
    // BuildTypeahead precomputes, for every prefix of every logged query, the k most searched
    // completions. A keystroke then costs one lookup, with no ranking at query time.
    func BuildTypeahead(log []Suggestion, k, maxPrefix int) map[string][]Suggestion {
      table := map[string][]Suggestion{}
      for _, s := range log {
        q := strings.ToLower(s.Query)
        for n := 1; n <= len(q) && n <= maxPrefix; n++ {
          table[q[:n]] = append(table[q[:n]], s)
        }
      }
      for p, list := range table {
        slices.SortFunc(list, func(a, b Suggestion) int {
          if a.Count != b.Count {
            return b.Count - a.Count
          }
          return strings.Compare(a.Query, b.Query)
        })
        table[p] = list[:min(k, len(list))]
      }
      return table
    }
    

    Seeded log: 30 queries with Zipf counts. An engine does the same with edge n-grams: "jac" is indexed as j, ja, jac.

    I precompute the top completions for every prefix from the query log. A keystroke is one lookup, with no ranking at request time.

    N

    Posting lists compress

    recorded, 50,000 synthetic documents
    termdocumentsraw bytesgap bytes
    t136,717146,86836,717
    t226,691106,76426,691
    t1008923,568975
    t40000284
    all936,8363,747,3441,405,239
    gap encodingpseudo code
    encode(doc_ids):          // sorted: 3, 7, 8, 21
      gaps = 3, 4, 1, 13        // common term: small gaps1
      write each gap as a varint
                                // 7 bits a byte: a gap under 128 takes 1 byte
    1. 1t1 is in 36,717 of 50,000 documents, so almost every gap is 1 or 2.
    Tested source Go: encode gaps
    Go: encode gapsgo
    // EncodeGaps stores a sorted posting list as the gaps between document numbers, each gap as a
    // varint: 7 bits a byte. A common term has small gaps, so most gaps fit in one byte.
    func EncodeGaps(docs []int32) []byte {
      out := make([]byte, 0, len(docs))
      prev := int32(0)
      for _, d := range docs {
        out = binary.AppendUvarint(out, uint64(d-prev))
        prev = d
      }
      return out
    }
    

    50,000 documents of 20 words, 50,000-word vocabulary with Zipf frequencies: 1.5 bytes a posting against 4.

    Posting lists hold sorted ids, so I store the gaps as varints. A common term costs about 1 byte per document.

    O

    Sharding an index

    by document against by term
    by documentcoordinatorshard 0p01 p02 p04p10 p17 p22all termsshard 1p03 p05 p06p08 p13 p15p16 p19 p20p23all termsshard 2p07 p09 p11p12 p14 p18p21 p24all terms✓ each shard ranks its own documents△ every query visits all 3 shardsthe coordinator merges each top 10by termcoordinatorshard 0tent→ every docsome termsshard 1some termsshard 2waterproof→ every docsome terms✓ visits 2 of 3 shards✕ a common term ships its whole posting lista hot term loads one shard; updates touch many
    scatter and gatherpseudo code
    search(query, k):
      IF global statistics:
        stats = sum of N and df from every shard1    // one extra round trip
      FOR EACH shard, in parallel2:
        top k by BM25, using stats
      RETURN top k of the merged lists
    1. 1A distributed frequency phase. Without it, each shard computes IDF from its own documents.
    2. 2Latency is the slowest shard. Replicas let the coordinator pick a fast copy.
    Tested source Go: sharded search
    Go: sharded searchgo
    // Search sends the query to every shard, takes each shard's top k, and merges them by score. With
    // global set, it first sums each shard's document counts, so every shard scores with the same IDF.
    func (s Sharded) Search(terms []string, p Params, k int, global bool) []ShardHit {
      if global {
        parts := make([]Stats, len(s))
        for i, ix := range s {
          parts[i] = ix.LocalStats(dedupe(terms))
        }
        g := Global(parts)
        p.Stats = &g
      }
      var all []ShardHit
      for i, ix := range s {
        for _, h := range ix.Score(terms, p, k) {
          all = append(all, ShardHit{Hit: h, Shard: i})
        }
      }
      slices.SortFunc(all, func(a, b ShardHit) int {
        if a.Score != b.Score {
          if a.Score > b.Score {
            return -1
          }
          return 1
        }
        return strings.Compare(a.ID, b.ID)
      })
      return all[:min(k, len(all))]
    }
    
    queryone index3 shards, local IDF
    waterproof tentp06 p21 p07 p08 p09p06 p09* p21* p07* p08*
    rain jacketp04 p05 p17 p19 p16p04 p05 p19* p17* p24*
    hiking bootsp03 p20 p23 p19 p24p03 p24* p19* p20* p23*

    Top 5 on the 24-product catalog. * marks a changed rank: 3 of 3 queries change. With summed counts, all 3 match one index exactly.

    I shard by document. Every query visits every shard, but each ranks locally. I sum document counts first when shards are small, so IDF is the same everywhere.

    P

    Failure cases

    what breaks, and the fix
    eventresultwhy it is safe, or the fixsaved by
    The indexer falls behindSearch shows stale results.Postgres stays correct. Alert on lag; take price and stock from Postgres.Hydrate
    Crash between a database write and an index writeThe index never gets the change.No dual write. The change log commits with the row.Change log
    The indexer crashes before it saves its positionChanges are applied twice.Versions make the second apply a no-op.Version check
    A slow transaction commits after a later sequence numberA table-based reader can skip it.Read the WAL in commit order, or re-read a window behind the position.CDC
    A new analyzer or field needs a reindexRebuilding in place blocks or empties search.Build a new index, catch up from the log, then move the alias.Alias
    A query for a very common termRanking reads every match: 458 ms in Postgres.Stop words; engines skip postings that cannot reach the top 10.Search engine
    Small shards with local statisticsThe same query ranks differently by shard.Fewer, larger shards, or a distributed frequency phase.Search engine
    Many deletes and updatesDeleted copies stay in segments until a merge.Merges remove them. Index size and document counts lag until then.Segment merge
    Q

    Scale ladder

    start simple; climb only on a signal
    Each step adds one component1LIKE2+ GIN3+ trigrams4+ search, CDC5+ shardsmore load →
    Capacity against demand, 1 million documents1101001k10kDemand, average: 230 queries per second, one coreDemand, average230Demand, 10× peak: 2,300 queries per second, one coreDemand, 10× peak2,300LIKE '%word%': 1.3 queries per second, one coreLIKE '%word%'1.3GIN, 53% match, top 10: 2.2 queries per second, one coreGIN, 53% match, top 102.2GIN, 2% match, top 10: 44 queries per second, one coreGIN, 2% match, top 1044GIN, rare word, top 10: 4,400 queries per second, one coreGIN, rare word, top 104,400queries per second, one core, log scale
    stepaddit handlesmove up when you see
    1LIKE or ILIKE on one column.Tens of thousands of rows: 8.18 ms at 10,000, 779 ms at 1 million.Users need word matches, stemming or a ranked list.
    2A tsvector column and a GIN index, ranked with ts_rank.Rare words at about 4,400 queries a second per core at 1 million documents.Typos, or a typeahead that hits the primary on every keystroke.
    3pg_trgm and a prefix table in memory or Redis.Typos by similarity; completions in one lookup.Common words: 458 ms each. Relevance tuning, facets, synonyms.
    4A search cluster fed by change data capture. Postgres stays the truth.BM25, analyzers per field, fuzzy queries, aggregations. Replicas add read capacity.One shard's index nears the memory or disk of a node.
    5Shard by document, with replicas per shard.Index size grows with shards; each query fans out to all of them.Top of the ladder.

    Queries a second per core is 1,000 divided by the measured median in ms: 1,000 / 0.228 for a rare word. Demand example: 20 million searches a day is 230 a second.

    I start with Postgres full text search and a GIN index. I add a search cluster fed by change capture when ranking, typos or facets outgrow it.

    R

    Drill

    predict, then reveal

    0 of 9 known

    1. For "waterproof tent", why does BM25 rank three tents above a dry bag that says waterproof 4 times?

    2. Postgres ts_rank puts that dry bag second. Why?

    3. A user types "jakcet". Edit distance finds jacket; trigrams do not. Why?

    4. Why do posting lists store positions, not only document ids?

    5. Why not save to Postgres and then update the index in the same request?

    6. The indexer applies 3 changes and crashes before it saves its position. What happens on restart?

    7. Shard a search index by document or by term?

    8. The same query ranks differently on a small sharded index. Why?

    9. When is Postgres full text search enough?

    S

    Numbers to say

    measured in the lab, or cited
    GIN
    A rare word in 1 million documents: 0.218 ms.
    LIKE
    A leading-wildcard LIKE over 1 million rows: 779 ms.
    common
    Top 10 of a word in 53% of documents: 458 ms.
    index size
    GIN 66 MB for a 370 MB table.
    postings
    Gap encoded: 1.5 bytes a posting against 4.
    BM25
    k1 = 1.2, b = 0.75 by default.
    refresh
    A write is searchable in about 1 second.
    fuzzy
    AUTO: 0 edits to 2 letters, 1 to 5, then 2.

    Postgres 16.14 on a 16-thread laptop, one core per query (no parallel workers), median of 7 runs. Engine defaults are from the Elasticsearch documentation. Use as orders of magnitude.