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.

Why lakebase_text?

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.

Enable the lakebase_text extension

Lakebase Search must be enabled on your Neon project before you can install this extension. Once it's enabled, run the following statement 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.

Quick start

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.

Configure default_limit

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.

Fallback parameters

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.

Prefilter

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.

Reference

Types

Operators

Operator classes

Functions

Index storage parameters

Search parameters (GUCs)