Postgres Full Text Search and BM25 Ranking with SereneDB
Postgres full text search is a tsvector column, a GIN index and ts_rank. SereneDB keeps the PostgreSQL wire protocol, the @@ operator and the *_tsquery parser names, and ranks matches with BM25 in ORDER BY.
A separate open-source engine, not an extension: Postgres drivers connect to it, and it can index tables of the Postgres you already run.
What Is Postgres Full Text Search?
PostgreSQL text search finds documents that match a query and can sort them by relevance. to_tsvector reduces a document to a tsvector of normalized lexemes, websearch_to_tsquery turns the search string into a tsquery, and @@ tests one against the other. A GIN index, the preferred text search index type, maps each lexeme to its rows.
Ranking is a separate step: ts_rank weighs how often the query terms occur, ts_rank_cd adds how close they sit, and both use no global information. A word in every row weighs as much as a word in one: in the aside all four matches tie, though only one contains the rare word authenticator.
ALTER TABLE articles ADD COLUMN tsv tsvector
GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;
CREATE INDEX articles_tsv ON articles USING GIN (tsv);
SELECT id, title, ts_rank_cd(tsv, q) AS rank
FROM articles,
websearch_to_tsquery('english', 'password OR authenticator') q
WHERE tsv @@ q
ORDER BY rank DESC LIMIT 10;Why Postgres Full Text Search Hits a Ceiling
No BM25 in core
Neither built-in ranking function reads corpus statistics, so there is no inverse document frequency — the part of BM25 that makes a rare term count. Postgres BM25 comes from extensions: pg_search, pg_textsearch.
Postgres fuzzy search sits outside the index
A tsquery matches lexemes, prefixes and phrases, not misspellings. Typos need a contrib module: pg_trgm with its own trigram index, or levenshtein() between two strings.
Ranking reads every match
Ranking consults the tsvector of every match, which the Postgres docs call potentially I/O bound.
How SereneDB Runs Postgres Full Text Search
SereneDB shares PostgreSQL's parser and wire protocol, not its engine: full-text search runs on an inverted index, and the compatibility matrix lists what carries over.
Your Postgres driver, over wire protocol v3.0
psycopg, node-postgres, Npgsql, libpqxx, tokio-postgres and psql have full client support; JDBC, DBeaver and Grafana partial. Pass the search box text as a bound parameter: websearch_to_tsquery never raises on malformed input.
import psycopg
conn = psycopg.connect("host=localhost port=7890 dbname=_system")
rows = conn.execute(
"SELECT id, title FROM articles_idx"
" WHERE body @@ websearch_to_tsquery(%s)"
" ORDER BY BM25(articles_idx.tableoid) DESC LIMIT 10",
("password OR authenticator",),
).fetchall()Index the Postgres you already run
CREATE SERVER stores a connection that survives restarts; ATTACH lasts a session. Index a view over a remote table: postings live in SereneDB, rows stay in Postgres. The index is a snapshot, refreshed by REINDEX or reindex_interval as a full rebuild per pass. More in zero-ETL search.
CREATE SERVER app FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'pg.internal', port '5432', database 'shop',
user 'reader', password 'secret');
CREATE VIEW tickets_v AS
SELECT id, subject, body FROM app.public.tickets;
CREATE INDEX tickets_idx ON tickets_v
USING inverted (subject english_dict, body english_dict);
-- after Postgres changes:
REINDEX INDEX tickets_idx;Step-by-Step: Postgres Text Search in 5 Minutes
Start SereneDB and connect with psql
One container; no credentials are required by default.
docker run -d --name serenedb -p 7890:7890 serenedb/serenedb
psql -h localhost -p 7890Create the dictionaries and the index
The two text search dictionaries stand in for Postgres's simple and english configurations; english_dict stems but keeps stop words. frequency feeds BM25, position phrase queries.
CREATE TEXT SEARCH DICTIONARY simple_dict AS
split_text(case := 'lower')
WITH (frequency, position);
CREATE TEXT SEARCH DICTIONARY english_dict AS
split_text(case := 'lower') | stem_words('en_US.UTF-8')
WITH (frequency, position);
CREATE TABLE articles (id INTEGER PRIMARY KEY, title VARCHAR, body VARCHAR);
CREATE INDEX articles_idx ON articles
USING inverted (id, title simple_dict, body english_dict);Insert the rows
The index refreshes every second by default; publish the load now instead.
INSERT INTO articles VALUES
(1, 'Reset a forgotten password', 'Request a password reset link from the sign-in page.'),
(2, 'Rotate database credentials', 'Rotate the database password without downtime.'),
(3, 'Password rules', 'Passwords need twelve characters and one digit.'),
(4, 'Reset two-factor sign-in', 'Resetting a lost authenticator starts on the account page.'),
(5, 'Fix a dropped connection', 'Retry with backoff when the server resets the connection.');
VACUUM (REFRESH_TABLE) articles;Rank with BM25, no extension needed
Article 4 comes first: authenticator is in one body of five, password in three, and BM25 weighs the rarer term higher. Native Postgres ranked all four the same.
SELECT id, title FROM articles_idx
WHERE body @@ websearch_to_tsquery('password OR authenticator')
ORDER BY BM25(articles_idx.tableoid) DESC, id
LIMIT 10;Fuzzy search: a typo in the query
pasword is one edit from password: articles 1 and 3, matched on the unstemmed title inside the same index — fuzzy search.
SELECT id, title FROM articles_idx
WHERE title @@ ts_levenshtein('pasword', 1)
ORDER BY id;Postgres Text Search at a Billion Logs: SearchBench
One machine, a billion OpenTelemetry logs, 92 search and analytics queries run one at a time, a 60-second cap, hot median latency. Postgres 18 ran tsvector with GIN, ParadeDB pg_search, TigerData pg_textsearch for BM25 top-k and GIN for the rest, on a hypertable.
The table is latency only; concurrency and updates were not measured. Postgres ranks with ts_rank_cd, not BM25, so its top rows differ. SereneDB measured 32.5 ms and 31.0 ms in two later posts; this is the Postgres post's run, with every adapter in the SearchBench repo.
Postgres Full Text Search vs Elasticsearch vs SereneDB
Stay native when match-and-sort is enough
Search stays beside your rows. For BM25 inside Postgres itself, pg_search and pg_textsearch install into it; SereneDB is a separate server.
A search cluster beside Postgres
Elasticsearch ranks with BM25 by default and scales out across nodes and shards. Postgres stays the system of record, and a sync pipeline copies each change into a second system.
BM25 behind the Postgres protocol
BM25, phrase and fuzzy terms from Postgres drivers. One server, not a cluster. The index is eventually consistent, and FTS SQL is ported, not reused: no tsvector, ts_rank or ts_headline.
Use Cases for Postgres Full Text Search
A search box for a Postgres app
The app keeps its driver and sends what the user typed to websearch_to_tsquery: quoted phrases, OR, a leading - to exclude, ranked by BM25.
Search over tables you keep in Postgres
SereneDB indexes a view over the tables Postgres keeps as the system of record and joins matches with local data; reindex_interval refreshes the snapshot on a schedule.
Keyword and vector search together
The same index takes a FLOAT[N] embedding column, filled by ai_embed(), so BM25 and vector distance meet in one statement: hybrid search.
Frequently Asked Questions
Yes: tsvector and tsquery types, the @@ operator, GIN and GiST indexes, and two ranking functions. Core lacks BM25: its ranking uses no statistics about the rest of the table.
to_tsvector turns documents into lexemes, a *_tsquery function parses the search, @@ matches through a GIN index, and ts_rank scores each match.
Not in core. Extensions add it — ParadeDB's pg_search, TigerData's pg_textsearch — or a separate engine does: SereneDB orders by BM25(), k1 1.2 and b 0.75 by default.
Through contrib modules: pg_trgm for trigram similarity, fuzzystrmatch for levenshtein(). SereneDB matches typos in the full-text index with ts_levenshtein on a word column, or ts_ngram on a column indexed with an n-gram dictionary.
For match-and-sort search, it can. For BM25 or typo tolerance the options are an extension (pg_search, pg_textsearch, pg_trgm) or Elasticsearch beside Postgres, fed by a sync pipeline. SereneDB puts BM25 and fuzzy terms behind the Postgres protocol instead.
No. It is its own engine with PostgreSQL's SQL parser and wire protocol v3.0, so most Postgres clients connect without code changes.
Yes, as a snapshot: index a view over a table reached with CREATE SERVER (ATTACH lasts one session). Postings are stored in SereneDB, rows stay in Postgres, and each REINDEX pass rebuilds the index.
Get Started with SereneDB
One container, two dictionaries, one index: five steps to BM25-ranked, typo-tolerant results.