Getting Started with VectorChord‑BM25

dnf install -y edb-as16-server-core
dnf install -y edb-as16-pg-tokenizer
dnf install -y edb-as16-vectorchord-bm25

/usr/edb/as16/bin/initdb -D ~/epas16d
# In postgresql.conf
# shared_preload_libraries = 'pg_tokenizer'
/usr/edb/as16/bin/pg_ctl -D ~/epas16d -l ~/epas16d/logfile start
/usr/edb/as16/bin/psql postgres -c 'SET search_path TO "$user", public, tokenizer_catalog, bm25_catalog;'

CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;

-- Create a tokenizer (example: pre‑trained BERT)
SELECT create_tokenizer('bert', $$
model = "bert_base_uncased"
$$);

-- Document table
CREATE TABLE documents (
  id serial primary key,
  passage text,
  embedding bm25vector
);

-- Load sample data
INSERT INTO documents (passage) VALUES
('PostgreSQL is a powerful, open-source object-relational database system. It has over 15 years of active development.'),
('Full-text search is a technique for searching in plain-text documents or textual database fields. PostgreSQL supports this with tsvector.'),
('BM25 is a ranking function used by search engines to estimate the relevance of documents to a given search query.'),
('PostgreSQL provides many advanced features like full-text search, window functions, and more.'),
('Search and ranking in databases are important in building effective information retrieval systems.'),
('The BM25 ranking algorithm is derived from the probabilistic retrieval framework.'),
('Full-text search indexes documents to allow fast text queries. PostgreSQL supports this through its GIN and GiST indexes.'),
('The PostgreSQL community is active and regularly improves the database system.'),
('Relational databases such as PostgreSQL can handle both structured and unstructured data.'),
('Effective search ranking algorithms, such as BM25, improve search results by understanding relevance.');

-- Tokenize (avoid generated columns; models can change)
UPDATE documents SET embedding = tokenize(passage, 'bert');

-- Build BM25 index
CREATE INDEX documents_embedding_bm25 ON documents USING bm25 (embedding bm25_ops);

SELECT id, passage,
       embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL', 'bert')) AS bm25_score
FROM documents
ORDER BY bm25_score
LIMIT 10;