lakebase_text

The lakebase_text extension adds BM25 full-text search to Lakebase via the lakebase_bm25 index type. It is compatible with PostgreSQL's standard tsvector type and query operators.

Install

First, enable Lakebase Search in your project settings. Then install the extension:

CREATE EXTENSION IF NOT EXISTS lakebase_text;

Upgrade the extension and indexes

A new Lakebase Search release can add features, fixes, and performance improvements. Although Lakebase Search is released as part of Lakebase 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.

Note

The latest available extension version is reported by SELECT default_version FROM pg_available_extensions WHERE name = 'lakebase_text'.

Why lakebase_text instead of standard GIN full-text search

PostgreSQL's built-in full-text search uses GIN indexes and ts_rank for relevance scoring. ts_rank does not use global corpus statistics, so scores degrade as data grows. lakebase_text improves on this in two ways:

  • BM25 ranking accounts for term frequency, document length, and corpus-wide statistics simultaneously, producing more accurate relevance scores than TF-IDF.
  • Top-K pushdown uses Block-Max WAND to return only the K most relevant results from the index, without scoring every match in the result set.

Quick start

Build the lakebase_bm25 index after inserting data. BM25 computes corpus-wide statistics at index build time, not incrementally, so the index must be created on a populated table.

-- Create a table with a generated tsvector column
CREATE TABLE documents (
  id      SERIAL PRIMARY KEY,
  passage TEXT,
  vector  TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', passage)) STORED
);

-- Insert data before building the BM25 index
INSERT INTO documents (passage) VALUES
  ('Postgres is a powerful open-source relational database.'),
  ('Vector search finds semantically similar results.'),
  ('BM25 ranking improves full-text search relevance scores.');

-- Create the BM25 index on the populated table
CREATE INDEX documents_passage_bm25 ON documents USING lakebase_bm25 (vector);

-- Query: lower score means more relevant
SELECT id, passage,
  vector <@> to_bm25query(to_tsvector('english', 'database'), 'documents_passage_bm25') AS score
FROM documents
ORDER BY score
LIMIT 5;

The <@> operator returns a negative BM25 score. Ordering by ascending score returns the most relevant results first.

Note

A lakebase_bm25 index scan can omit any number of rows whose <@> value is exactly 0.0. Do not rely on zero-distance rows being returned or on their order. To evaluate every row, set lakebase_bm25.enable_scan to off to use a sequential scan instead.

Populate from synced tables

If you're loading source text from Unity Catalog rather than inserting it directly, synced tables can generate a tsvector column during sync, ready to index with lakebase_bm25 as soon as the sync completes. See Custom type mapping for Lakebase Search.

Keep the index accurate

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, Databricks 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.

Warning

BM25 global statistics are not MVCC-versioned. If VACUUM updates the statistics while a transaction is using an older MVCC snapshot, the transaction can calculate scores using statistics newer than its row snapshot. Row visibility remains MVCC-compliant, but scores, rankings, and top-K results can change within a REPEATABLE READ transaction. Do not rely on snapshot-stable BM25 rankings across a concurrent VACUUM, including autovacuum.

Session-level GUCs

Parameter Type Default Description
lakebase_bm25.default_limit integer 1000 Maximum number of results returned from the index.
lakebase_bm25.prefilter boolean false When true, evaluates WHERE conditions before computing BM25 scores. Use when filters eliminate many rows and are cheap to evaluate.
lakebase_bm25.enable_scan boolean true Set to false to force a sequential scan, bypassing the index. Useful for testing. 
SET lakebase_bm25.default_limit TO 20;
SET lakebase_bm25.prefilter = on;

GUCs take precedence over index storage parameters when both are set.

Index storage parameters

Set these options at index creation time or with ALTER INDEX:

Parameter Type Default Range Description
k1 real 1.2 1.2 to 2.0 Term frequency saturation. Higher values give more weight to repeated terms.
b real 0.75 0.0 to 1.0 Document length normalization. 0.0 disables length normalization; 1.0 applies full normalization.
default_limit integer 1000 1 to 65535 Fallback limit when the session GUC is not set.
prefilter boolean false N/A Fallback prefilter setting when the session GUC is not set. 
-- Set parameters at index creation (use a new name — the Quick start already created documents_passage_bm25)
CREATE INDEX documents_passage_bm25_tuned ON documents USING lakebase_bm25 (vector)
  WITH (default_limit = 20, k1 = 1.5);

-- Update parameters on an existing index
ALTER INDEX documents_passage_bm25_tuned SET (default_limit = 50);

API reference

Types

bm25query_tsvector: combines a query tsvector with the target index identifier. Used as the right operand of <@>.

Operators

Operator Signature Returns Description
<@> tsvector <@> bm25query_tsvector double precision Returns a negative BM25 score. Order ascending to get the most relevant results first.

Functions

Function Returns Description
to_bm25query(query tsvector, index regclass) bm25query_tsvector Constructs a BM25 query object from a tsvector and the index's object identifier.

Operator classes

Class Default for Description
tsvector_bm25_ops tsvector Maps tsvector columns to the <@> operator for BM25 scoring. This is the default operator class for tsvector with lakebase_bm25; you do not need to specify it explicitly.

Next steps

  • Last updated on