# Full-text search

> Add keyword search to a SupaHost project with Postgres tsvector columns, GIN indexes and websearch queries, then call it from supabase-js with textSearch or an RPC.

Source: https://supahost.app/docs/database/full-text-search · Updated 2026-11-11

Postgres has a full search engine built in. It splits text into normalised words (lexemes), drops common words like "the", reduces words to their stems ("baking" and "baked" both become "bake"), and matches them against a query. You don't need a separate search service: the index lives next to your data, respects Row Level Security, and is backed up with the rest of your database.

Everything on this page is standard Postgres 17, so it works the same on every SupaHost plan.

## The two building blocks

| Type | What it holds | Made with |
|---|---|---|
| `tsvector` | The searchable form of a document: sorted lexemes with positions | `to_tsvector('english', text)` |
| `tsquery` | A search expression: words joined by AND, OR, NOT and phrase operators | `websearch_to_tsquery`, `plainto_tsquery`, `to_tsquery` |

The `@@` operator returns true when a `tsvector` matches a `tsquery`:

```sql
select to_tsvector('english', 'Slow-baked sourdough with rosemary')
       @@ websearch_to_tsquery('english', 'baking rosemary');
-- true
```

## Add a search column to a table

For a recipe app, store a generated `tsvector` column so Postgres keeps it up to date on every insert and update. Use `setweight` to rank title matches above body matches.

```sql
create table public.recipes (
  id bigint generated always as identity primary key,
  user_id uuid not null references auth.users (id) default auth.uid(),
  title text not null,
  ingredients text not null default '',
  method text not null default '',
  fts tsvector generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(ingredients, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(method, '')), 'C')
  ) stored
);

create index recipes_fts_idx on public.recipes using gin (fts);
```

The GIN index is what makes search fast. Without it, Postgres has to compute and compare every row. See [Indexes](/docs/database/indexes) for more on index types.

The language name (`'english'`) must be a literal in a generated column, because the expression has to be immutable. Run `select cfgname from pg_ts_config;` to see the configurations available, including `simple`, which does no stemming and suits names, codes and mixed-language text.

## Search from supabase-js

The client's `textSearch` filter maps to the `fts` (and friends) operators in the Data API. The `websearch` type accepts the syntax people already type into search boxes: quoted phrases, `or`, and `-` to exclude a word.

```ts
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  'https://app-one.acme.supahost.space',
  process.env.SUPABASE_ANON_KEY!
)

const { data, error } = await supabase
  .from('recipes')
  .select('id, title')
  .textSearch('fts', '"brown butter" cookies -walnut', {
    type: 'websearch',
    config: 'english',
  })
  .limit(20)
```

Row Level Security still applies, so a signed-in user only finds recipes your policies let them read. The Data API returns at most 1,000 rows per request on SupaHost, so paginate with `.range()` for long result lists.

## Rank results with an RPC

`textSearch` filters rows but can't sort by relevance. For ranked results, wrap the query in a database function and call it with `rpc()`:

```sql
create or replace function public.search_recipes(q text)
returns table (id bigint, title text, rank real)
language sql
stable
security invoker
set search_path = ''
as $$
  select r.id, r.title, ts_rank_cd(r.fts, query) as rank
  from public.recipes r,
       websearch_to_tsquery('english', q) as query
  where r.fts @@ query
  order by rank desc
  limit 50;
$$;
```

```ts
const { data } = await supabase.rpc('search_recipes', { q: 'lemon tart' })
```

`security invoker` means the function runs with the caller's permissions, so your RLS policies still filter the rows. See [Calling database functions (RPC)](/docs/api/rpc) for how arguments and return types map to JSON.

## Search as you type

Stemming works on whole words, so a half-typed word like `sourd` won't match `sourdough`. Add a prefix match with `:*` using `to_tsquery`. Clean the input first, because `to_tsquery` rejects stray punctuation:

```sql
create or replace function public.suggest_recipes(prefix text)
returns setof public.recipes
language sql
stable
security invoker
set search_path = ''
as $$
  select *
  from public.recipes
  where fts @@ to_tsquery(
    'english',
    replace(trim(regexp_replace(prefix, '[^[:alnum:]]+', ' ', 'g')), ' ', ' & ') || ':*'
  )
  limit 10;
$$;
```

Call it only once the user has typed at least one letter or digit; an empty string produces an invalid query.

## Show matching snippets

`ts_headline` returns a fragment of the original text with the matched words wrapped in tags. It reads the raw text, not the index, so call it only on the rows you return:

```sql
select title,
       ts_headline('english', method, websearch_to_tsquery('english', 'caramel'),
                   'StartSel=<mark>, StopSel=</mark>, MaxFragments=2')
from public.recipes
where fts @@ websearch_to_tsquery('english', 'caramel')
limit 10;
```

## Typos and fuzzy matching

Full-text search doesn't forgive spelling mistakes. For that, the `pg_trgm` extension compares strings by three-letter chunks. Enable it in the `extensions` schema and add a trigram index on the column you want to match loosely:

```sql
create extension if not exists pg_trgm with schema extensions;

create index recipes_title_trgm_idx
  on public.recipes using gin (title extensions.gin_trgm_ops);

select title
from public.recipes
where title operator(extensions.%) 'lasagna'   -- similar to, e.g. "Lasagne"
order by extensions.similarity(title, 'lasagna') desc
limit 5;
```

Check which extensions the Postgres image provides with `select name from pg_available_extensions order by name;` (more in [Postgres extensions](/docs/database/extensions)).

## When to reach for vectors

Keyword search finds the words people type. If you want "quick weeknight dinner" to match a recipe that never uses those words, you need semantic search with embeddings. You can also combine both: see [Semantic search](/docs/ai/semantic-search) and [Hybrid search](/docs/ai/hybrid-search).

## Related

- [Indexes](/docs/database/indexes)
- [Query performance and EXPLAIN](/docs/database/query-performance)
- [Querying and filtering data](/docs/api/querying)
- Postgres reference: [Full Text Search](https://www.postgresql.org/docs/17/textsearch.html)