Full-text search: match and rank by BM25 in SQL
SereneDB runs full-text search inside the database. A text search dictionary tokenizes and stems text, an inverted index maps each token to its rows, @@ matches words, phrases, prefixes and typos, and BM25 ranks the results, over tables or Parquet files, through the PostgreSQL wire protocol.
curl https://install.serenedb.com | shWhat is full-text search? Matching tokens, not substrings
Full-text search finds rows by the words inside their text. A text search dictionary splits each value into tokens and normalizes them, lower-cased and stemmed, the same way when it indexes and when you query, so Shoes, SHOES and shoe are one term. On a plain column, LIKE '%runs%' compares a substring with every row and returns the rows unranked. Pick an operator to see which of five catalog rows it returns; every result here ran on SereneDB 26.11.1.
- 1Trail running shoesLight shoes for running on rocky trails
- ✓2Road running shoesCushioned shoes for road running and daily runsmatch
- 3Leather dress shoesFormal leather shoes for the office
- ✓4Running socksBreathable socks for long runsmatch
- 5Wireless earbudsEarbuds that stay in while you run
SELECT id, name FROM products WHERE description ILIKE '%runs%';
SELECT id, name FROM products_idx WHERE description @@ 'runs';
SELECT id, name FROM products_idx WHERE name @@ ts_phrase('running shoes');
SELECT id, name FROM products_idx WHERE name @@ ts_starts_with('ear');
SELECT id, name FROM products_idx WHERE name @@ ts_levenshtein('lether', 1);
SELECT id, name FROM products_idx WHERE description @@ websearch_to_tsquery('shoes -leather');
SELECT id, name FROM products_idx WHERE description @@ 'running' ORDER BY BM25(products_idx.tableoid) DESC;
SELECT id, name FROM products_idx WHERE description @@ 'running' ORDER BY TFIDF(products_idx.tableoid) DESC;
How full-text indexing works: one dictionary, one inverted index, one score
A text search dictionary decides how each column becomes tokens; CREATE INDEX … USING inverted stores, for every token, the rows that hold it and the statistics BM25 needs; and a query selects from the index by name: @@ filters, a scorer in ORDER BY ranks, ts_highlight marks the matches. Hover a stage, or a column inside the index, to find its line.
A plain SQL table
Rows live in an ordinary table, and the index needs no primary key. New rows become searchable at the next refresh: every 1000 ms by default (refresh_interval), or at once after VACUUM (REFRESH_TABLE).
Tokenize and normalize, the same way twice
A text search dictionary chains templates with |: split_text splits the text and lower-cases it, and stem_words reduces each word to its stem. The same pipeline runs on the indexed text and on every query, so Shoes in a row and shoe in a query meet as one token. WITH (…) picks what the index records per token.
Columns without a dictionary
A column listed without a dictionary is indexed as stored, one token per value: exact, case-sensitive matches for ids, codes and categories. id is an INTEGER, so a range such as id <= 2 is answered inside the same index scan; EXPLAIN shows it as a Granular Range.
Each token maps back to its rows
Per token, the inverted index keeps the rows that contain it, with the frequencies, positions, lengths and offsets the dictionary’s flags ask for. A query reads only its own tokens’ posting lists, so the cost follows the matches, not the table. Field order carries no meaning: a query may constrain any of the fields.
Select from the index with @@
A bare string matches any of its tokens. ts_phrase keeps their order, and slop := N allows gaps. ts_starts_with, ts_like and ts_regexp match token patterns, ts_levenshtein tolerates typos, and websearch_to_tsquery reads a search box with quotes and minus signs. @@ runs as an IRESEARCH_SCAN, so it composes with WHERE, JOIN and GROUP BY.
BM25 and ten other scorers
BM25 counts a term for more when it is frequent in the row and rare in the collection; repeats add less (k1, default 1.2) and long fields count less (b, default 0.75). The other scorers are TFIDF, three language models (lm_jm, lm_dirichlet, indri_dirichlet), dfi, idf, constant and three raw signals (raw_tf, raw_boost, raw_dl). A query uses one scorer per index. With optimize_top_k on the index, ORDER BY <scorer> DESC LIMIT k skips rows that cannot reach the top k (WAND), and EXPLAIN reports “Top: 2, optimized”.
Ranked, highlighted rows
ts_highlight wraps each match in <b>…</b> from the offsets the index recorded. The score is a FLOAT you can select, filter on or fuse with a vector ranking in hybrid search.
Feature flags decide what the index records
WITH (frequency, position, norm, offset)Term frequencies for BM25 and the other scorers. Without the flag, every match of a term scores the same.
Token positions, so ts_phrase and slop are answered by the index alone. Needs frequency.
Character offsets for ts_highlight. Without the flag they are recomputed for each matching row. Needs frequency and position.
Field lengths, so BM25 scales long fields down. Without norm, rows 1, 4 and 5 above tie at 0.200. Needs frequency.
How to do full-text search in SQL, in five statements
One dictionary, one table, one inverted index: from an empty database to ranked, highlighted matches. Every statement and result below ran on SereneDB 26.11.1 on 2026-11-03.
CREATE TEXT SEARCH DICTIONARY english AS split_text(case := 'lower') | stem_words('en') WITH (frequency, position, norm, offset); SELECT ts_lexize('english', 'Running shoes for long runs'); -- {run,shoe,for,long,run}
CREATE TABLE products ( id INTEGER PRIMARY KEY, name VARCHAR, description VARCHAR ); CREATE INDEX products_idx ON products USING inverted (id, name english, description english);
INSERT INTO products VALUES (1, 'Trail running shoes', 'Light shoes for running on rocky trails'), (2, 'Road running shoes', 'Cushioned shoes for road running and daily runs'), (3, 'Leather dress shoes', 'Formal leather shoes for the office'), (4, 'Running socks', 'Breathable socks for long runs'), (5, 'Wireless earbuds', 'Earbuds that stay in while you run'); VACUUM (REFRESH_TABLE) products;
SELECT id, name FROM products_idx WHERE description @@ 'runs'; SELECT id, name FROM products_idx WHERE name @@ ts_phrase('running shoes') AND description @@ 'trails';
SELECT id, name, ts_highlight(description) AS snippet FROM products_idx WHERE description @@ 'running' ORDER BY BM25(products_idx.tableoid) DESC, id LIMIT 3;
What teams build on full-text search
Product and catalog search
Stemmed names, typo tolerance and an in-stock filter in one statement: the match runs in the index, the join to inventory is plain SQL, and facet counts come from the index’s term dictionary with ts_dict_agg. When shoppers describe what they want instead of naming it, add vectors with hybrid search.
Hybrid search →SELECT p.id, p.name, s.warehouse, s.qty FROM products_idx p JOIN stock s ON s.product_id = p.id WHERE p.name @@ 'running' AND s.qty > 0 ORDER BY p.id, s.warehouse;
| id | name | warehouse | qty |
|---|---|---|---|
| 1 | Trail running shoes | berlin | 4 |
| 2 | Road running shoes | lisbon | 9 |
| 4 | Running socks | lisbon | 12 |
Log search over Parquet files
A phrase on the message, an exact level, and a GROUP BY over the matches, in one statement. Point a view at Parquet, CSV or JSON files on disk or S3 and index the view: the rows stay in the files, only the postings are built, and REINDEX INDEX picks up new files.
Data lake search →CREATE VIEW logs AS SELECT * FROM read_parquet('s3://my-bucket/logs/*.parquet'); CREATE INDEX logs_idx ON logs USING inverted (level, service, message english); SELECT level, count(*) FROM logs_idx WHERE message @@ ts_phrase('connection reset') GROUP BY level ORDER BY level;
| level | count |
|---|---|
| ERROR | 2 |
| WARN | 1 |
Documentation and help-center search
A search box that understands quotes and minus signs, ranked by BM25, with highlighted snippets. Serene Docs Search packages this: it indexes a Git repository, folder, website or S3-compatible bucket into SereneDB, with optional vector search.
Docs search →SELECT title, ts_highlight(body) AS snippet FROM docs_idx WHERE body @@ websearch_to_tsquery('refresh index -reindex') ORDER BY BM25(docs_idx.tableoid) DESC, id LIMIT 3;
| title | snippet |
|---|---|
| Session settings | The <b>refresh</b> interval of a new <b>index</b> defaults to one second |
| Index maintenance | VACUUM (REFRESH_TABLE) makes new rows searchable at once; a background task <b>refreshes</b> the <b>index</b> every second |
Full-text search is search over the words inside text. Each value is split into tokens and normalized (lower-cased, stemmed), an inverted index maps every token to the rows that contain it, and the matches are ranked by relevance, usually with BM25. In SereneDB it is a WHERE col @@ query predicate on an inverted index, ordered by BM25().
At index time a text search dictionary tokenizes and normalizes each column, and the inverted index stores, per token, the rows with their frequencies, positions and lengths. At query time the same dictionary analyzes the query, the engine reads only those tokens' posting lists, and a scorer ranks the matches. In SereneDB that is CREATE TEXT SEARCH DICTIONARY, then CREATE INDEX … USING inverted.
Create a dictionary and an inverted index on the text columns, then query the index by name: SELECT id, name FROM products_idx WHERE description @@ 'runs' ORDER BY BM25(products_idx.tableoid) DESC LIMIT 10; The five statements above ran as written on SereneDB 26.11.1. Coming from PostgreSQL's tsvector and ts_rank? See Postgres full-text search.
On a plain column, LIKE '%runs%' compares a substring against every row: it misses running and run, matches inside other words, and returns rows unranked. Full-text search matches normalized tokens through an index and ranks them, so runs finds running, runs and run, best match first.
"Text search" is the loose term for any search over text, including LIKE and regular expressions. Full-text search means the indexed, tokenized kind: words are normalized, looked up in an inverted index and ranked.
No. BM25 builds on TF-IDF: both weigh a term by its frequency in the row and its rarity in the collection. BM25 adds saturation, so repeats count for less (k1), and length normalization, so long fields count for less (b). SereneDB ships both and nine more scorers, chosen in ORDER BY.
Full-text search matches the words people type, so exact names, identifiers and error codes are found, and every match can be explained. Semantic search compares embeddings to find the same meaning in other words. Hybrid search runs both in one query and fuses the rankings; see also vector search.
Full-text search is lexical: notebook does not match laptop, and a typo misses unless the query allows it. Scores compare only within one index. SereneDB covers the first two with synonym dictionaries and ts_levenshtein, and pairs words with meaning through hybrid search.
If the data already sits in SQL tables or Parquet files, full-text search can run where it is: SereneDB indexes tables, views and files in place and answers BM25-ranked queries in SQL over the PostgreSQL wire protocol. On SearchBench (1 billion OpenTelemetry log rows, 92 search and analytics queries, 11 engines in 17 configurations, hot runs), SereneDB 26.09.1 was the only one to finish all 92 within the 60 s cap, with the lowest geometric mean: 1.11× the fastest result per query. Code written for Elasticsearch can keep its client for creating indices, writing documents and searching: SereneDB answers a subset of the Elasticsearch REST API. SereneDB vs Elasticsearch and SereneDB vs OpenSearch compare SereneDB with each, feature by feature.
Get started with SereneDB
Start SereneDB with one command, create one dictionary and one inverted index, and your tables and Parquet files answer @@ queries ranked by BM25, over the PostgreSQL wire protocol.