Full-text search

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

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

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.

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

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():

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;
$$;

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) 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:

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:

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:

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).

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 and Hybrid search.

Related

• Indexes
• Query performance and EXPLAIN
• Querying and filtering data
• Postgres reference: Full Text Search