Full-Text Search Database with SereneDB
Full-text search finds the rows whose text contains a query's words, ignoring case and endings such as -s and -ing, and ranks them by relevance. In SereneDB that is one SQL statement: a @@ match on an inverted index, ordered by BM25.
An open-source database with full-text search built in, not a search cluster beside it: the index covers tables or files on S3, queried over the PostgreSQL wire protocol.
What Is Full-Text Search?
Full-text search — also called lexical or keyword search — matches the words of a search query against the words stored in documents and returns the documents that contain them, most relevant first. It is not LIKE '%shoe%': text is split into tokens and normalized before it is indexed, so Shoes and SHOES are one term and a stemmer maps running and runs to run.
A full-text search example: a shopper types running shoes. The catalog's dictionary stems it, an inverted index returns the rows holding run or shoe, and a relevance score, most often BM25, puts the rows with both first.
Why Full-Text Search Needs an Inverted Index
An inverted index maps each token back to the rows that contain it.
What LIKE cannot do
A substring predicate compares the pattern with every row, knows no word boundaries or inflections, and returns its matches unranked.
Look up, then jump
Per token, the index stores the rows that hold it, so a query reads only its own tokens' lists: the cost follows the matches, not the table.
The same analysis on both sides
A column's dictionary runs at index time and again on each query, so FOX in a query and Fox in a row reduce to one token.
How SereneDB Runs Full-Text Search in SQL
A dictionary decides how a column is tokenized, CREATE INDEX … USING inverted builds the postings, and a query selects from the index by name with @@. The match runs as an IRESEARCH_SCAN, so it composes with JOIN and GROUP BY.
Analyzers: tokenizers, stemmers and dictionaries
A text search dictionary chains templates with |: tokenizers split natural-language text, normalizers rewrite the tokens. Feature flags decide what the index records: frequency for scoring, position for phrase and proximity search, offset for highlighting, norm for length normalization.
Phrase, proximity and prefix queries
ts_phrase matches tokens in order; ## allows a gap, as in 'quick' ## 1 ## 'fox'. ts_starts_with, ts_like and ts_regexp match token patterns: see the function reference.
SELECT id, name FROM products_idx
WHERE name @@ ts_phrase('running shoes'); -- 1, 2
SELECT id, name FROM products_idx
WHERE name @@ ts_starts_with('ear'); -- 5Fuzzy search and boolean queries
ts_levenshtein matches tokens within an edit distance; &&, || and !! combine queries; websearch_to_tsquery parses search-box text. There is no tsvector type: see Postgres full text search for how the two differ.
SELECT id, name FROM products_idx
WHERE name @@ ts_levenshtein('lether', 1); -- 3
SELECT id, name FROM products_idx
WHERE description @@
websearch_to_tsquery('shoes -leather'); -- 1, 2Full-text search over files you never loaded
Index a view over external data: Parquet, CSV, JSON or Iceberg on disk or S3, or a table in an attached PostgreSQL or ClickHouse. Only the postings are built; REINDEX picks up new files. See search over a data lake.
CREATE VIEW logs AS
SELECT * FROM read_parquet('s3://my-bucket/logs/*.parquet');
CREATE INDEX logs_idx ON logs
USING inverted (level, message english);
REINDEX INDEX logs_idx;What Is BM25 Search?
BM25 search is full-text search ranked by Okapi BM25, the relevance model behind Lucene and Elasticsearch. A term counts for more when frequent in the row and rare in the collection; extra occurrences add less (k1, default 1.2) and long fields are scaled down (b, default 0.75).
Nine scorers, one per scan
BM25, TFIDF, three language models, dfi and three raw signals. A score is a FLOAT to project, filter on or blend.
Boosting and top-k
The ^ operator multiplies a clause's share of the score. With optimize_top_k, WAND pruning skips candidates that cannot reach the top k. See ranking.
IResearch underneath
The Search Benchmark Game runs IResearch, the open-source C++ library under the index, against Lucene and Tantivy; the docs report it ahead in every query type and mode. It measures the library, not SereneDB end to end.
BM25 vs TF-IDF on the same five rows
TF-IDF has no term-frequency saturation and, by default, ignores length, so the three rows holding run once tie. BM25 normalizes length: Running socks, five tokens long, beats two seven-token rows. Both are scorer functions.
Step-by-Step: Full-Text Search in SQL in 5 Minutes
Create a text search dictionary
Lower-case, then stem. ts_lexize shows the tokens a string becomes.
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 the table and the inverted index
Both text columns use the dictionary; id, an INTEGER, is indexed for exact and range matches.
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 rows and refresh
The index is eventually consistent: rows are searchable after the next refresh, every second by default, or at once after VACUUM (REFRESH_TABLE).
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;Match with @@: stems and phrases
Rows 1, 2, 4 and 5: running, runs and run all index as run. The second query returns row 1 alone.
SELECT id, name FROM products_idx
WHERE description @@ 'runs';
SELECT id, name FROM products_idx
WHERE name @@ ts_phrase('running shoes')
AND description @@ 'trails';Rank with BM25 and highlight the matches
Road running shoes first, with two matches, then Running socks, the shortest one-match row.
SELECT id, name, ts_highlight(description) AS snippet
FROM products_idx
WHERE description @@ 'running'
ORDER BY BM25(products_idx.tableoid) DESC, id
LIMIT 3;Use Cases for Full-Text Search
Product and catalog search
Stemmed names, typo tolerance, category and price filters in one index, facet counts from the term dictionary. When shoppers describe rather than name, add hybrid search.
Log and event search
A phrase on the message, exact matches on level and service, a GROUP BY over the matches: one statement. Parquet logs on S3 are indexed in place.
Documentation search
Serene Docs Search indexes a Git repository, website or S3 bucket into SereneDB and answers with BM25, stemming and synonyms: see documentation search.
When a Dedicated Search Engine Is Still the Better Choice
You need a cluster
Elasticsearch splits an index into shards across nodes and keeps replicas. SereneDB deploys as a single binary on one node, and its docs describe no replication or managed cloud.
You rely on multi-field queries
multi_match and the full percolate query have no SereneDB twin: @@ takes one column, so several fields are several predicates joined with OR.
You run on the platform around it
Elasticsearch runs ingest pipelines before indexing, Kibana sits on top, and Elastic Cloud hosts the Elastic Stack. SereneDB is the database alone.
Frequently Asked Questions
Search over the words inside text fields: text is split into tokens and normalized, an inverted index maps each token to its rows, and search results are ranked by relevance, usually with BM25.
Searching a catalog for running shoes: WHERE name @@ 'running shoes' returns products holding the stems run or shoe, and ordering by BM25 puts those with both first.
A map from each token to the rows that contain it, like the index at the back of a book: a query jumps to the matching rows instead of reading all of them.
Yes. It reads per-term statistics the index keeps beside the postings: term frequency, document frequency, field length. In SereneDB a scorer needs the frequency flag.
No, but it starts there. BM25 adds saturation, so the tenth occurrence counts far less than the first, and length normalization. SereneDB ships both scorers.
It is lexical: notebook computer misses laptop unless a synonym list links them. It ignores word order, and scores are relative to one collection, so two indexes' scores do not compare.
Full-text search is lexical search: it matches tokens, so exact identifiers and error codes are found. Semantic search compares embeddings and finds the same meaning in other words, but can miss the exact term. Hybrid search runs both.
Get Started with SereneDB
One dictionary, one table, one inverted index: five steps from an empty database to ranked, highlighted matches.