The lakebase_text extension

The lakebase_text extension adds a lakebase_bm25 index type to Postgres for BM25 full-text search. It is a native upgrade to PostgreSQL's built-in full-text search: standard tsvector type and query operators work unchanged; only the index type changes.

See Lakebase Search for the architecture and the companion lakebase_vector extension.

PostgreSQL's built-in full-text search uses GIN indexes with tsvector. GIN works well for boolean filtering, but it has two limitations for search relevance:

• No BM25 ranking. GIN uses ts_rank, which does not use global corpus statistics, so scores degrade as data grows. BM25 is more accurate, accounting for term frequency, document length, and corpus-wide statistics together.
• No top-K pushdown. GIN must score all matching documents even when you only need the top 10. For large tables, this means significant unnecessary work on every query.

lakebase_bm25 adds a first-class BM25 index with Block-Max WAND top-K pushdown: the index returns only the K most relevant results directly, without scoring the entire match set. It fully preserves standard tsvector types and existing query operators. No application logic changes are required.

Install the extension in the Neon SQL Editor or from a client such as psql:

CREATE EXTENSION IF NOT EXISTS lakebase_text;

lakebase_text requires Postgres 16 or later. It has no extension dependencies; unlike lakebase_vector, it does not require pgvector.

lakebase_text relies on a preloaded library that Neon enables by default. If you've customized your project's preloaded libraries, make sure lakebase_text is in the list.

A new Lakebase Search release can add features, fixes, and performance improvements. Although Lakebase Search is released as part of Neon updates, it does not upgrade everything automatically. In lakebase_text, two things upgrade separately and carry version numbers that are unrelated to each other:

• The extension version is the version of the SQL objects that CREATE EXTENSION lakebase_text creates, including its data types, functions, operators, and the lakebase_bm25 index access method. This version is reported by SELECT installed_version FROM pg_available_extensions WHERE name = 'lakebase_text'. ALTER EXTENSION lakebase_text UPDATE updates this version.
• The index storage format is the on-disk layout of a lakebase_bm25 index. The extension may introduce updated index storage formats in an update, unlocking more features and delivering better performance. All newly created indexes automatically use the latest storage format, while existing indexes can be upgraded to the new format using REINDEX INDEX CONCURRENTLY after a newer storage format is available.

Upgrading is not urgent. The extension is compatible with SQL objects and index storage formats from older versions, but staying current keeps you on the supported, best-performing path and avoids a larger migration later, so upgrade when convenient rather than deferring indefinitely.

Create a table with a tsvector column and insert data:

CREATE TABLE documents (
    id serial PRIMARY KEY,
    passage text,
    vector tsvector
);

INSERT INTO documents (passage, vector) VALUES
('PostgreSQL is a powerful, open-source object-relational database system.', to_tsvector('english', 'PostgreSQL is a powerful, open-source object-relational database system.')),
('Full-text search is a technique for searching in plain-text documents.', to_tsvector('english', 'Full-text search is a technique for searching in plain-text documents.')),
('BM25 is a ranking function used by search engines to estimate document relevance.', to_tsvector('english', 'BM25 is a ranking function used by search engines to estimate document relevance.')),
('PostgreSQL provides advanced features like full-text search and window functions.', to_tsvector('english', 'PostgreSQL provides advanced features like full-text search and window functions.')),
('Effective ranking algorithms like BM25 improve information retrieval results.', to_tsvector('english', 'Effective ranking algorithms like BM25 improve information retrieval results.'));

Create a lakebase_bm25 index on the tsvector column:

CREATE INDEX documents_passage_bm25 ON documents USING lakebase_bm25 (vector);

Set how many results the index returns and run a BM25 search:

SET lakebase_bm25.default_limit TO 5;

SELECT
  id,
  vector <@> to_bm25query(to_tsvector('english', 'PostgreSQL'), 'documents_passage_bm25') AS score
FROM documents
ORDER BY score
LIMIT 5;

The <@> operator calculates the negative BM25 score of a document against a query. Ordering by score ascending returns the most relevant documents first (lower negative score = higher relevance).

to_bm25query constructs a bm25query_tsvector value by combining the query tsvector with the object identifier of the BM25 index. The index identifier is required because BM25 scoring depends on corpus-wide statistics stored in the index.

BM25 statistics are computed at index build time and updated by VACUUM. For most workloads, regular VACUUM keeps scores accurate. After bulk-loading a large amount of new data, run VACUUM manually:

VACUUM documents;

To maintain query and update performance, VACUUM must clean the index promptly. For a table dedicated to text search, Neon recommends setting autovacuum_vacuum_insert_scale_factor to 0 so the insert-triggered autovacuum threshold does not grow with the table:

ALTER TABLE documents SET (
  autovacuum_vacuum_insert_scale_factor = 0
);

With the scale factor set to 0, autovacuum_vacuum_insert_threshold determines the fixed number of inserted tuples that triggers autovacuum. Tune that threshold based on your workload.

The lakebase_bm25.default_limit GUC controls how many results the index returns before PostgreSQL applies any LIMIT clause from your query. The default is 1000.

-- Return at most 10 results from the index
SET lakebase_bm25.default_limit TO 10;

Setting this value to match your query's LIMIT avoids unnecessary work when you only need a small top-K result set.

You can store search parameters directly in an index as storage parameters, rather than setting them per session or transaction with GUCs. This is useful when you have multiple indexes, prefer not to set GUCs, or want to configure search behavior offline.

Set default_limit at index creation:

CREATE INDEX documents_passage_bm25 ON documents USING lakebase_bm25 (vector)
WITH (default_limit = 5);

Queries against this index use default_limit = 5 without requiring a SET command:

SELECT
  id,
  vector <@> to_bm25query(to_tsvector('english', 'PostgreSQL'), 'documents_passage_bm25') AS score
FROM documents
ORDER BY score
LIMIT 5;

Update a storage parameter on an existing index:

ALTER INDEX documents_passage_bm25 SET (default_limit = 10);

GUCs take precedence over index storage parameters when both are set. To avoid hard-to-diagnose behavior, use only one method at a time.

In a filtered query, PostgreSQL applies WHERE conditions after the index scan returns results. If your filter eliminates many rows, the index may return far more results than your LIMIT requires.

The lakebase_bm25.prefilter GUC enables the index to evaluate filter conditions before computing BM25 scores, pruning the search space early:

SET lakebase_bm25.default_limit TO 5;
SET lakebase_bm25.prefilter = on;

SELECT
  id,
  vector <@> to_bm25query(to_tsvector('english', 'PostgreSQL'), 'documents_passage_bm25') AS score
FROM documents
WHERE id % 1000 = 0
ORDER BY score
LIMIT 5;

Prefilter is recommended when the filter is strict (eliminates many rows) or unpredictable (eliminates an unknown number of rows), and cheap to evaluate (much cheaper than computing BM25 scores). Enabling it for a loose or expensive filter may be slower than the default.

Join our Discord Server to ask questions or see what others are doing with Neon. For paid plan support options, see Support.