Migrate from pg_search to lakebase_text

This guide migrates your BM25 full-text search from pg_search to lakebase_text. You map your existing indexes and queries to their lakebase_text equivalents, verify the results, then remove pg_search. lakebase_text runs BM25 search through a lakebase_bm25 index built on standard Postgres tsvector types.

What changes

pg_search and lakebase_text both do BM25 search, but they model it differently:

The main difference: pg_search indexes your columns directly, while lakebase_text indexes a tsvector that you build (usually a generated column).

1. Enable lakebase_text

   Before you migrate, lakebase_text needs to be enabled on your project. Check what's loaded:

   SHOW shared_preload_libraries;

   If lakebase_text is in the list, create the extension and you're ready:

   CREATE EXTENSION IF NOT EXISTS lakebase_text;

   If lakebase_text isn't listed, enabling it is a one-time setup: add it to your project's preloaded libraries with the Neon API, restart the compute, then create the extension. The Get started with Lakebase Search guide has the full steps. For this migration you need only lakebase_text, not lakebase_vector. Leave pg_search enabled while you migrate.

2. Migrate the index

   The examples below use a mock_items(description, category) table as a stand-in for your existing table. Substitute your own table and column names.

   With pg_search, you create a BM25 index over your columns and designate a key_field:

   -- pg_search
   CREATE INDEX mock_items_search ON mock_items
     USING bm25 (id, description, category) WITH (key_field = 'id');

   With lakebase_text, you build a tsvector from the same columns and index that. A GENERATED ALWAYS column keeps it in sync as rows change:

   -- lakebase_text
   ALTER TABLE mock_items ADD COLUMN search_tsv tsvector
     GENERATED ALWAYS AS (to_tsvector('english', coalesce(description, '') || ' ' || coalesce(category, ''))) STORED;
   
   CREATE INDEX mock_items_bm25 ON mock_items USING lakebase_bm25 (search_tsv);

   To search a single column instead of several, point to_tsvector at just that column. 'english' is one of several built-in Postgres text-search configurations; list the ones available on your database with SELECT cfgname FROM pg_ts_config;. BM25 scores depend on corpus statistics gathered at build time, so create the index after your data is loaded, and run VACUUM after large bulk loads to keep scores accurate.

3. Migrate your queries

   pg_search queries a column with the @@@ operator and ranks with paradedb.score(), highest first:

   -- pg_search
   SELECT id, description, paradedb.score(id) AS score
   FROM mock_items
   WHERE description @@@ 'keyboard'
   ORDER BY score DESC;

   id |         description          |   score
   ----+------------------------------+-----------
     2 | Blue metal keyboard          | 0.8236319
     3 | Wireless ergonomic keyboard  | 0.8236319

   lakebase_text does the same work with two operators instead of one: @@ filters the matching rows, and <@> with to_bm25query ranks them. Scores are negative, so order ascending to put the best matches first:

   -- lakebase_text
   SELECT id, description,
     search_tsv <@> to_bm25query(to_tsvector('english', 'keyboard'), 'mock_items_bm25') AS score
   FROM mock_items
   WHERE search_tsv @@ to_tsquery('english', 'keyboard')
   ORDER BY score
   LIMIT 10;

   id |         description          |        score
   ----+------------------------------+---------------------
     2 | Blue metal keyboard          | -0.8374048792080783
     3 | Wireless ergonomic keyboard  | -0.8374048792080783

   When you rewrite a query, three things change:

   • Filter with @@. <@> only scores, it doesn't filter. Add a WHERE search_tsv @@ to_tsquery('english', 'query') clause, or every row comes back (non-matches at score 0).
   • Pass the index name to to_bm25query. BM25 scoring reads corpus statistics from the index.
   • Reverse the sort. pg_search orders DESC; lakebase_text orders ascending, because its scores are negative and a lower score is a better match.

   For multi-word or user-supplied input, use websearch_to_tsquery('english', 'your query') in place of to_tsquery in both the filter and the rank. to_tsquery expects pre-formatted query syntax and errors on plain phrases with spaces.

4. Check for feature gaps

   Before you drop pg_search, confirm the features you rely on carry over. A few don't map one-to-one:

   • Fuzzy / typo matching (paradedb.match(..., distance => 1)): no direct equivalent. Use pg_trgm for similarity and typo-tolerant matching.
   • Highlighting (paradedb.snippet()): use Postgres ts_headline().
   • JSON query objects and advanced tokenizers (ICU, Lindera): not supported. Use tsquery operators (&, |, <->) and Postgres text-search configurations.

5. Remove pg_search

   Once your queries run on lakebase_text and return the results you expect, drop pg_search ahead of the September 2026 removal:

   DROP EXTENSION pg_search CASCADE;

   CASCADE also drops the bm25 indexes that depend on the extension. Confirm it's gone:

   SELECT extname FROM pg_extension WHERE extname = 'pg_search';   -- returns no rows

   Finally, restart the compute to complete the migration. In the Neon Console, open the compute's ⋯ menu and select Restart compute, or call the Restart compute endpoint API.

   Your search now runs entirely on lakebase_text.

Resources

• The lakebase_text extension
• The pg_search extension (deprecated)
• Lakebase Search