Skip to content

Scenario: search with facets

The index is a derived copy: feed it, rebuild it, and count each facet without its own tick.

Predict first

Before you read: predict

A streaming catalog's search page shows a Genre list with a count beside each genre. A viewer searches space and ticks Documentary. Across the catalog, space matches 4 documentaries, 3 dramas and 5 sci-fi films. The page builds the Genre list with this query:

SELECT genre, count(*) AS films
FROM films
WHERE search @@ websearch_to_tsquery('english', 'space')
  AND genre = 'Documentary'
GROUP BY genre
ORDER BY genre;

What does the Genre list show?

Pick one

The 30-second version

Search runs on an inverted index; Postgres full-text search is one in your own database. A separate engine is a derived copy: feed it from the outbox, expect lag, and rebuild it beside the live one. Count each facet under every other filter but its own. Pick an analyzer per language, and weight the title.

The index is a derived copy: feed it, rebuild it, and count each facet without its own tick.

How it works

Step 1 of 11

Numbers first. Documents, edits per minute, searches per second and the facet fields set the design.

Analogy: A coin dealer's catalogue booklet

At the coin dealer's In the design
the stock ledger in the safe is the truth; the booklet on the counter is copied from it the engine is a derived copy (steps 2–3)
each sale goes on a slip; the evening clerk updates the booklet from the slips the outbox feeds the indexer; the booklet lags by a day (steps 4–5)
a new grading scale: a fresh booklet is made up beside the one on the counter, then swapped in rebuild beside, then switch (step 6)
the tab strip shows Roman (40) and Victorian (25); turning to Roman still shows the Victorian count each facet counted without its own tick (step 7)
the dealer keeps an English booklet and an Arabic one, each indexed by its own language's rules one analyzer per language (step 9)

In real terms: the search index is a copy you keep feeding and can always rebuild.

Breaks down when: a dealer in doubt walks to the safe; a search engine never reads the database while it answers.

Contrasting case: an admin screen filtering 500 rows with WHERE and LIKE: one table, no facets, no copy.

Examples, run for real

4.1 ExamHall EXH-3920 → MR !4220: Arabic questions in the bank

Admins search a 200,000-question bank by subject, difficulty and language. Every row was indexed with english, so Arabic word forms stayed apart (EXH-3920). MR !4220 stores each row's configuration in a regconfig column and indexes to_tsvector(cfg, body) (step 9).

4.1 model: 6 Arabic question stems, the word for "student" written طالب, الطالب, للطالب, وطالب, بالطالب, الطلاب · the admin searches طالب · PostgreSQL 18.3 (PGlite)
4.1 EXH-3920, every row indexed with 'english': 1 of 6 found
4.1 MR !4220, each row indexed with its own configuration ('arabic' here): 5 of 6 found; الطلاب, a broken plural, stays apart

4.2 Proof domain, media streaming: two ticks, two lists

A viewer searches ocean and ticks Documentary and English. The results apply both ticks; each list applies only the other one (step 7).

4.2 model: the films catalog, 'ocean', ticked Documentary and English · PostgreSQL 18.3 (PGlite), a GIN index on the tsvector column
4.2 results: 2 films
4.2 Genre list, counted under English only: Documentary 2 · Drama 1 · Sci-fi 1
4.2 Language list, counted under Documentary only: Arabic 1 · English 2 · French 1

The Genre list still offers Drama and Sci-fi in English; the Language list still offers Arabic and French documentaries.

4.3 The board for "Design product search with filters and a count next to each" (worked, not executed)

Typical question Steps that answer it Mechanism owner
"Design search with filters like brand, price and rating, with counts." 1, 7, 8, 11 this lesson; GIN: db-specialized-indexes
"The index is 30 seconds behind. Acceptable, and how would you shrink it?" 4, 5, 6 this lesson; the relay: async-outbox-pattern
"When is Postgres full-text search enough?" 2, 3, 9, 10 this lesson

Try it

Answer first, then check. Nothing is saved.

Question 1 of 6

A streaming catalog holds five titles: The Office, Office Space, The Post Office Girl, Officer Down, The Lighthouse. Its search runs to_tsvector(cfg, title) @@ websearch_to_tsquery(cfg, 'The Office'). The team tries cfg = 'simple', then cfg = 'english'.

How many of the five titles match under each?

Pick one

Question 2 of 6

Your design feeds the search index from the outbox relay. The interviewer's follow-up: "You must change the title analyzer for all 120,000 films, so every film must be indexed again. The backfill reads the films table for 40 minutes, and editors make 30 edits a minute while it runs." Searches keep running throughout.

Which plan ends with every film at its latest version in the index searches use, without any search hitting a half-built index?

Pick one

Question 3 of 6

An editor fixes a film's title in the streaming catalog. The change commits at 12:00:01. The outbox relay polls every 5 s from 12:00:00; the indexer sends what it holds to the engine every 15 s from 12:00:12; the engine refreshes the index every 10 s from 12:00:00. A poll picks up rows committed before it.

When does a search first return the new title?

Pick one

Question 4 of 6

The streaming catalog's search engine splits its films across 3 shards. The Genre list is a terms aggregation with shard_size 2: each shard sends the 2 genres with the most matches among its own films. A viewer searches moon. The shards hold these matches: shard 1 Drama 8, Horror 5, Comedy 4, Thriller 1; shard 2 Horror 6, Thriller 5, Comedy 4, Drama 2; shard 3 Thriller 7, Drama 5, Comedy 4, Horror 3.

What does the Genre list show?

Pick one

Question 5 of 6

A streaming catalog's search column is setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', description), 'D'). It holds three films (id · title · description): 1 · "The Vault Job" · "A safecracker plans one last heist with an old crew in a quiet coastal town."; 2 · "Night Train" · "A heist on a sleeper train: the heist crew rehearses the heist between stations."; 3 · "Heist of the Century" · "Bank robbers dig a tunnel under the city." A viewer searches heist:

SELECT title FROM films
WHERE search @@ websearch_to_tsquery('english', 'heist')
ORDER BY ts_rank(search, websearch_to_tsquery('english', 'heist')) DESC, id;

Which list does the viewer see?

Pick one

Question 6 of 6

A streaming catalog holds six titles: Planet Documentary, Desert Documentaries, Making a Documentary, The Document, Night Train, Office Space. Its search runs to_tsvector('english', title) @@ websearch_to_tsquery('english', :typed). A viewer types documentry, then documentaries.

How many titles does each return?

Pick one

Go deeper

Pro tip
  1. Name the lag budget and its stages before choosing the engine. If search may trail edits by a few seconds, say which stage takes them: poll, flush, refresh. When: the interviewer asks whether search 30 seconds behind is acceptable. Status: RAN on the model.
Source

No item is keyed on a source; every key is executed in PostgreSQL (PGlite) or on a model whose rules these pages state. Behaviour of the named products is valid as of 2026-10-13.

  • PostgreSQL 18 docs, Chapter 12 Full Text Search (the curriculum row's source; pages 12.1, 12.3, 12.4, 12.6, 12.11 saved, and 12.1, 12.3, 12.4, 12.6 also from the 17 docs, CL-069 (1)): https://www.postgresql.org/docs/18/textsearch.html (pg18-textsearch*.txt, pg17-textsearch*.txt). "often involves removal of suffixes (such as s or es in English)" (steps 9–10); "The function setweight can be used to label the entries of a tsvector with a given weight"; "If no weights are provided, then these defaults are used: {0.1, 0.2, 0.4, 1.0}" (step 11).
  • OpenSearch docs, Aggregations (the curriculum row's source) and its Terms page: https://docs.opensearch.org/latest/aggregations/ (opensearch-aggregations.txt), https://docs.opensearch.org/latest/aggregations/bucket/terms/ (opensearch-terms-aggregation.txt). "the coordinating node responsible for the aggregation will prompt each shard for its top unique terms"; "The number of buckets returned by each shard is controlled by the shard_size parameter." (step 8; the rule termsAgg follows).
  • OpenSearch docs, Refresh index: https://docs.opensearch.org/latest/api-reference/index-apis/refresh/ (opensearch-refresh.txt). "The document is not searchable until a refresh operation converts these in-memory structures into searchable segments on disk." (step 5; the rule lagTrace follows).
  • OpenSearch docs, Index aliases: https://docs.opensearch.org/latest/im-plugin/index-alias/ (opensearch-index-alias.txt). "Alias changes are atomic. An alias never points to an unintended set of indexes, even for a moment." (step 6; the switch reindex models).

Related lessons

Checked against the tools and docs as of 2026-10-13.