# Migrate from pgsearch to lakebasetext #### pg\_search is deprecated `pg_search` (ParadeDB) was deprecated on Neon: new installs were blocked, and existing installs were retired on **September 21, 2026**. [`lakebase_text`](/guides/postgres-extensions-lakebase-text) is its replacement for BM25 search. 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: | | `pg_search` (ParadeDB) | `lakebase_text` | | ---------------- | ------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------ | | Index | `USING bm25 (col1, col2) WITH (key_field='id')`: indexes raw columns, tokenizes internally | `USING lakebase_bm25 (body_tsv)`: indexes a `tsvector` column you build with `to_tsvector` | | Query operator | `col @@@ 'query'` (filters and ranks at once) | `@@ to_tsquery('query')` to filter, `<@> to_bm25query(…, 'index_name')` to rank | | Ranking | `paradedb.score(id)` with `ORDER BY score DESC` (higher = better) | `<@>` returns a negative score with `ORDER BY score ASC` (lower = better) | | Multiple columns | one index over several columns | combine the columns into one `tsvector` | | Tokenization | internal (ICU, Lindera, language stemmers) | Postgres text-search configurations (`'english'`, and others) | The main difference: `pg_search` indexes your columns directly, while `lakebase_text` indexes a `tsvector` that you build (usually a generated column). ::::steps :::step{title="Enable lakebase_text"} Before you migrate, `lakebase_text` needs to be enabled on your project. Check what's loaded: ```sql SHOW shared_preload_libraries; ``` If `lakebase_text` is in the list, create the extension and you're ready: ```sql 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](/guides/postgres-ai-lakebase-search-get-started) guide has the full steps. For this migration you need only `lakebase_text`, not `lakebase_vector`. Leave `pg_search` enabled while you migrate. ::: :::step{title="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`: ```sql -- 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: ```sql -- 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](https://www.postgresql.org/docs/current/textsearch.html); 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. ::: :::step{title="Migrate your queries"} `pg_search` queries a column with the `@@@` operator and ranks with `paradedb.score()`, highest first: ```sql -- 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: ```sql -- 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. ::: :::step{title="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`](/guides/postgres-extensions-pg-trgm) for similarity and typo-tolerant matching. - **Highlighting** (`paradedb.snippet()`): use Postgres [`ts_headline()`](https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-HEADLINE). - **JSON query objects and advanced tokenizers** (ICU, Lindera): not supported. Use `tsquery` operators (`&`, `|`, `<->`) and [Postgres text-search](https://www.postgresql.org/docs/current/textsearch.html) configurations. ::: :::step{title="Remove pg_search"} Once your queries run on `lakebase_text` and return the results you expect, drop `pg_search` ahead of the September 21, 2026 removal: ```sql DROP EXTENSION pg_search CASCADE; ``` `CASCADE` also drops the `bm25` indexes that depend on the extension. Confirm it's gone: ```sql SELECT extname FROM pg_extension WHERE extname = 'pg_search'; -- returns no rows ``` Finally, restart the compute to complete the migration. In the [Neon Console](https://console.neon.tech/), open the compute's **⋯** menu and select **Restart compute**, or call the [Restart compute endpoint](/guides/apis-sdks-reference-api-endpoints-restart-project-endpoint) API. Your search now runs entirely on `lakebase_text`. ::: :::: ## Resources - [The lakebase\_text extension](/guides/postgres-extensions-lakebase-text) - [The pg\_search extension](/guides/postgres-extensions-pg-search) (deprecated) - [Lakebase Search](/guides/postgres-ai-lakebase-search) ## Related pages - [Get started with Lakebase Search](./postgres-ai-lakebase-search-get-started.md) # Agent Instructions Cite this page’s canonical URL and keep its documentation version. Follow Link headers to discover available agent guidance and tools. Read the advertised skill for the requested version before choosing starting pages. Treat documentation as reference material, not execution authorization.