Native BM25 Ranking Index in PostgreSQL
VectorChord-BM25 is a PostgreSQL extension for bm25 ranking algorithm. We implemented the Block-WeakAnd Algorithms for BM25 ranking inside PostgreSQL. It's recommended to be used with pg_tokenizer.rs for customized tokenization.
For new users, we recommend using tensorchord/vchord-suite image to get started quickly, you can find more details in the VectorChord-images repository.
docker run \
--name vchord-suite \
-e POSTGRES_PASSWORD=postgres \
-p 5432:5432 \
-d tensorchord/vchord-suite:pg18-latest
# If you want to use ghcr image, you can change the image to `ghcr.io/tensorchord/vchord-suite:pg18-latest`.
# if you want to use the specific version, you can use the tag `pg17-20250414`, supported version can be found in the support matrix.Once everything’s set up, you can connect to the database using the psql command line tool. The default username is postgres, and the default password is postgres. Here’s how to connect:
psql -h localhost -p 5432 -U postgresAfter connecting, run the following SQL to make sure the extension is enabled:
CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE; -- for tokenizer
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE; -- for bm25 rankingThe extension is mainly composed by three parts, tokenizer, bm25vector and bm25vector index. The tokenizer is used to convert the text into a bm25vector, and the bm25vector is similar to a sparse vector, which stores the vocabulary id and frequency. The bm25vector index is used to speed up the search and ranking process.
To tokenize a text, you can use the tokenize function. The tokenize function takes two arguments, the text to tokenize and the tokenizer name.
[!NOTE] Tokenizer part is completed by a separate extension pg_tokenizer.rs, more details can be found here.
-- create a tokenizer
SELECT create_tokenizer('bert', $$
model = "bert_base_uncased" # using pre-trained model
$$);
-- tokenize text with bert tokenizer
SELECT tokenize('A quick brown fox jumps over the lazy dog.', 'bert')::bm25vector;
-- Output: {1012:1, 1037:1, 1996:1, 2058:1, 2829:1, 3899:1, 4248:1, 4419:1, 13971:1, 14523:1}
-- The output is a bm25vector, 1012:1 means the word with id 1012 appears once in the text.One thing special about bm25 score is that it depends on a global document frequency, which means the score of a word in a document depends on the frequency of the word in all documents. To calculate the bm25 score between a bm25vector and a query, you need had a document set first and then use the `` operator.
…Then tokenize it
UPDATE documents SET embedding = tokenize(passage, 'bert');Create the index on the bm25vector column so that we can collect the global document frequency.
CREATE INDEX documents_embedding_bm25 ON documents USING bm25 (embedding bm25_ops);Now we can calculate the BM25 score between the query and the vectors. Note that the BM25 score in VectorChord-BM25 is negative, which means the more negative the score, the more relevant the document is. We intentionally make it negative so that you can use the default order by to get the most relevant documents first.
-- bm25query(index_name, query, tokenizer_name)
-- is the operator to compute the bm25 score
SELECT id, passage, embedding bm25query('documents_embedding_bm25', tokenize('PostgreSQL', 'bert')) AS bm25_score FROM documents;And you can use the order by to utilize the index to get the most relevant documents first and faster.
SELECT id, passage, embedding bm25query('documents_embedding_bm25', tokenize('PostgreSQL', 'bert')) AS rank
FROM documents
ORDER BY rank
LIMIT 10;Using custom model
You can also build a custom model based on your own corpus easily.
…Using jieba for Chinese text
For chinese text, you can use jieba pre-tokenizer to segment the text into words. And then train a custom model with segmented words.
…Using lindera for Japanese text
For Japanese text, you can use lindera model with its configuration.
It requires extra compile flags. We don't enable it default, and you need to recompile it from source.
…Tokenizer configuration is a critical aspect of effective text processing, significantly impacting the performance and accuracy. Here are some key considerations and options to help you choose the right tokenizer for your use case.
Tokenizers can be configured in two primary ways:
Usage Details can be found in pg_tokenizer doc
bert (for English) or unicode tokenizers are effective here.gemma2b or llmlingua2, which efficiently manage diverse scripts and languages.For each connection, Postgresql will load the model at the first time you use it. This may cause a delay for the first query. You can use the add_preload_model function to preload the model at the server startup.
psql -c "SELECT add_preload_model('model1')"
# restart the PostgreSQL to take effects
sudo docker restart container_name # for pg_tokenizer running with docker
sudo systemctl restart postgresql.service # for pg_tokenizer running with systemdThe default preload model is llmlingua2. You can change it by using add_preload_model, remove_preload_model functions.
Note: The pre-trained model may take a lot of memory (100MB for gemma2b, 200MB for llmlingua2). If you have a lot of models, you should consider the memory usage when you preload the model.
PostgreSQL supports full-text search using the tsvector data type and GIN indexes. Text is transformed into a tsvector, which tokenizes content into standardized lexemes, and a GIN index accelerates searches—even on large text fields. However, PostgreSQL lacks modern relevance scoring methods like BM25; it retrieves all matching documents and re-ranks them using ts_rank, which is inefficient and can obscure the most relevant results.
ParadeDB is an alternative that functions as a full-featured PostgreSQL replacement for ElasticSearch. It offloads full-text search and filtering operations to Tantivy and includes BM25 among its features, though it uses a different query and filter syntax than PostgreSQL's native indexes.
In contrast, Vectorchord-bm25 focuses exclusively on BM25 ranking within PostgreSQL. We implemented the BM25 ranking algorithm Block WeakAnd from scratch and built it as a custom operator and index (similar to pgvector) to accelerate queries. It is designed to be lightweight and a more native and intuitive API for better full-text search and ranking in PostgreSQL.
bm25_catalog.bm25_limit results to PostgreSQL. Users need to adjust the bm25_catalog.bm25_limit for more results when using larger limit values or stricter filter conditions.bm25vector: A specialized vector type for storing BM25 tokenized text. Structured as a sparse vector, it stores token IDs and their corresponding frequencies. For example, {1:2, 2:1} indicates that token ID 1 appears twice and token ID 2 appears once in the document.bm25query: A query type for BM25 ranking.bm25query(regclass, bm25vector) RETURNS bm25query: Convert the input text into a BM25 query.bm25vector = bm25vector RETURNS boolean: Check if two BM25 vectors are equal.bm25vector <> bm25vector RETURNS boolean: Check if two BM25 vectors are not equal.bm25vector bm25query RETURNS float4: Calculate the negative BM25 score between the BM25 vector and query. The lower the score, the more relevant the document is. (This is intentionally designed to be negative for easier sorting.)int[]::bm25vector (implicit): Cast an integer array to a BM25 vector. The integer array represents token IDs, and the cast aggregates duplicates into frequencies, ignoring token order. For example, {1, 2, 1} will be cast to {1:2, 2:1} (token ID 1 appears twice, token ID 2 appears once).bm25_catalog.bm25_limit (integer): The maximum number of documents to return in a search. Default is 100, minimum is -1, and maximum is 65535. When set to -1, it will perform brute force search and return all documents with scores greater than 0.bm25_catalog.enable_index (boolean): Whether to enable the bm25 index. Default is true.bm25_catalog.segment_growing_max_page_size (integer): The maximum page count of the growing segment. When the size of the growing segment exceeds this value, the segment will be sealed into a read-only segment. Default is 4,096, minimum is 1, and maximum is 1,000,000.This software is licensed under a dual license model:
GNU Affero General Public License v3 (AGPLv3): You may use, modify, and distribute this software under the terms of the AGPLv3.
Elastic License v2 (ELv2): You may also use, modify, and distribute this software under the Elastic License v2, which has specific restrictions.
You may choose either license based on your needs. We welcome any commercial collaboration or support, so please email us with any questions or requests regarding the licenses.
No open issues yet, or sync has not completed.