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.
- 1An inverted index maps each term to the documents that hold it. A query reads a few posting lists, not every document.
- 2BM25 scores a match by how rare the term is, how often it appears, and how long the field is.
- 3Postgres ts_rank has no IDF. On the lab catalog it ranked a dry bag second for "waterproof tent".
- 4Feed the index from the change log, with versions. A dual write leaves the index stale after a crash.
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.
- 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.
| method | where | status |
|---|---|---|
| LIKE '%word%' with no index | Postgres | Not approved |
| B-tree on the column | Postgres | Whole-value prefix |
| pg_trgm GIN index | Postgres | Substrings, typos |
| tsvector, GIN, ts_rank | Postgres | Rank without IDF |
| Search engine with BM25 | Search engine | Approved |
| Dual write from the service | Service | Not approved |
| Change log, indexer with versions | CDC | Approved |
| Query the primary on each keystroke | Postgres | Not 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.
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- 1Terms only meet if both sides apply the same steps.
- 2Stop words are in nearly every document. Their IDF is near 0 and their posting lists are the longest.
- 3Positions count stop words too, so a phrase keeps its gaps.
- 4A stem need not be a word: the lab stems insulated to insulat. It only has to match itself.
Tested source Go: analyze
// 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.
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- 1IDF: a term in every document scores about 0; a term in 1 of N scores about ln N.
- 2Length norm: the same count in a field longer than average scores lower. b sets how much.
- 3Saturation: the score approaches idf × (k1 + 1) and never passes it.
- 4A field weight: a match in the title can count 3 times a match in the body.
- 5The Lucene and Elasticsearch defaults.
Tested source Go: idf and bm25
// 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.
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.
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- 1The posting list holds positions, so the check runs inside the index. No document is read.
- 2All terms with one prefix sit next to each other: one binary search finds the first.
- 3An edit is an insert, a delete, a change or a swap of two neighbours.
Tested source Go: phrase · Go: prefix · Go: fuzzy
// 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] })
}
// 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
}
// 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
}
| query | in Postgres | in a search engine |
|---|---|---|
| phrase | phraseto_tsquery | match_phrase |
| prefix | to_tsquery('wat:*') | prefix, edge n-grams |
| typo | pg_trgm, word % 'jaket' | fuzziness AUTO |
| user syntax | websearch_to_tsquery | simple_query_string |
Phrases use positions, prefixes use the sorted term dictionary, and typos use edit distance against the dictionary.
| query | BM25 engine | Postgres |
|---|---|---|
| waterproof tent | 3 tents first | Dry bag second |
| running shoes | run, shoe | run, shoe |
| "rain jacket" | 1 match | 1 match |
| wat… | 3 terms | :* prefix |
| jaket | 1 edit | Trigrams only |
| jakcet | 1 swap | No match |
-- 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;- 1True when trigram similarity is at least 0.3, the default threshold.
- 2Search for any look-alike word, then rank as usual.
Tested source Go: the claims checked on the recorded results
// 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.
- 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.
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);- 1Postgres computes the vector on every insert and update. No application code can forget it.
- 2Field weights: a title match ranks above a body match.
- 3The inverted index: lexeme to the rows that contain it.
- 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.
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.
| tool | capability | what it gives this design | also used for |
|---|---|---|---|
| Postgres | to_tsvector with a language configuration | Split, stop words and a Snowball stemmer inside the database. | Search on small corpora |
| Postgres | GIN index on tsvector | An inverted index: lexeme to rows. | JSONB, arrays |
| Postgres | websearch_to_tsquery, phraseto_tsquery, :* prefix | User query syntax, phrases with <->, prefixes. | Admin search screens |
| Postgres | ts_rank and ts_rank_cd, setweight A to D | Rank by count, proximity and field weight. | |
| Postgres | ts_rank limits | Limit No global statistics, so no IDF. It reads the vector of every match. | |
| Postgres | pg_trgm with GIN | Typo-tolerant similarity and indexed LIKE '%word%'. | Did you mean, name search |
| Postgres | Triggers and logical decoding | A change log written with the row, or read from the WAL. | Outbox, audit, replication |
| Search engine | Analyzers per field: tokenizer, filters, synonyms | Different text rules for titles, codes and prose. | Multi-language search |
| Search engine | BM25 by default (k1 1.2, b 0.75), field boosts | Relevance with IDF; tuning without code. | Logs, observability |
| Search engine | Fuzzy queries, fuzziness AUTO | 0, 1 or 2 edits by term length. | Name matching |
| Search engine | Edge n-grams, completion suggester | Typeahead served from the index. | Autocomplete |
| Search engine | Shards, replicas, query then fetch | Parallel ranking per shard; replicas add read capacity. | Aggregations, facets |
| Search engine | Refresh interval, 1 second by default | Near real time: a write is searchable after the next refresh. | Bulk loads with refresh off |
| Search engine | Index aliases | Build a new index, then move the alias in one step. Reads never stop. | Schema changes, rollbacks |
| CDC | Log-based change data capture | Reads 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.
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- 1The change exists if and only if the write committed. No crash can split them.
- 2The indexer reads the latest row, so it never applies stale content from an old change.
- 3Replays and reordered changes become no-ops. A delete is remembered with its version too.
- 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
// 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
}
// 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
}
-- 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();- 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.
GET "wa"5 of 5
- 3,000
- 600
- 545
- 500
- 375
220 prefixes, 320 stored completions, for 30 distinct queries.
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- 1Longer prefixes add entries and rarely change the answer.
- 2Storage is k entries per prefix, whatever the log size.
- 3Serve it from memory, Redis or a CDN. The table rebuilds every few minutes from the log.
Tested source Go: build the table
// 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.
| term | documents | raw bytes | gap bytes |
|---|---|---|---|
| t1 | 36,717 | 146,868 | 36,717 |
| t2 | 26,691 | 106,764 | 26,691 |
| t100 | 892 | 3,568 | 975 |
| t40000 | 2 | 8 | 4 |
| all | 936,836 | 3,747,344 | 1,405,239 |
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- 1t1 is in 36,717 of 50,000 documents, so almost every gap is 1 or 2.
Tested source Go: encode gaps
// 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.
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- 1A distributed frequency phase. Without it, each shard computes IDF from its own documents.
- 2Latency is the slowest shard. Replicas let the coordinator pick a fast copy.
Tested source Go: sharded search
// 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))]
}
| query | one index | 3 shards, local IDF |
|---|---|---|
| waterproof tent | p06 p21 p07 p08 p09 | p06 p09* p21* p07* p08* |
| rain jacket | p04 p05 p17 p19 p16 | p04 p05 p19* p17* p24* |
| hiking boots | p03 p20 p23 p19 p24 | p03 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.
| event | result | why it is safe, or the fix | saved by |
|---|---|---|---|
| The indexer falls behind | Search shows stale results. | Postgres stays correct. Alert on lag; take price and stock from Postgres. | Hydrate |
| Crash between a database write and an index write | The index never gets the change. | No dual write. The change log commits with the row. | Change log |
| The indexer crashes before it saves its position | Changes are applied twice. | Versions make the second apply a no-op. | Version check |
| A slow transaction commits after a later sequence number | A 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 reindex | Rebuilding 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 term | Ranking reads every match: 458 ms in Postgres. | Stop words; engines skip postings that cannot reach the top 10. | Search engine |
| Small shards with local statistics | The same query ranks differently by shard. | Fewer, larger shards, or a distributed frequency phase. | Search engine |
| Many deletes and updates | Deleted copies stay in segments until a merge. | Merges remove them. Index size and document counts lag until then. | Segment merge |
| step | add | it handles | move up when you see |
|---|---|---|---|
| 1 | LIKE 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. |
| 2 | A 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. |
| 3 | pg_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. |
| 4 | A 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. |
| 5 | Shard 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.
0 of 9 known
For "waterproof tent", why does BM25 rank three tents above a dry bag that says waterproof 4 times?
Postgres ts_rank puts that dry bag second. Why?
A user types "jakcet". Edit distance finds jacket; trigrams do not. Why?
Why do posting lists store positions, not only document ids?
Why not save to Postgres and then update the index in the same request?
The indexer applies 3 changes and crashes before it saves its position. What happens on restart?
Shard a search index by document or by term?
The same query ranks differently on a small sharded index. Why?
When is Postgres full text search enough?
- 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.