Postgres Full Text Search, Fuzzy Matching and Semantic Search

Postgres full text search is good enough for most app search boxes: it understands word stems, ranks results, handles phrases and exclusions, and runs off a GIN index in your existing database, with no separate search cluster to keep in sync. You add a tsvector column, index it and query it with websearch_to_tsquery. When users expect typo tolerance or "search by meaning", pg_trgm and embeddings close the gap, and on StitchDB a built-in search index combines words and meaning for you.

This article builds up in layers, so you can stop at the one that is enough for your app.

#Layer 1: ILIKE and why it runs out

Everyone starts here:

select id, title from posts where title ilike '%running shoes%';

It works for small tables and exact substrings, but it has three problems. It cannot use a normal B-tree index for a leading %, so it scans the table. It does not know that "run" and "running" are the same word. And it has no notion of relevance, so the best match is not first. Full text search fixes all three.

#Layer 2: Postgres full text search

#The two types

• A tsvector is a document turned into normalized words (lexemes) with positions: to_tsvector('english', 'Running shoes for trails') gives 'run':1 'shoe':2 'trail':4. Stop words like "for" are dropped.
• A tsquery is a search: websearch_to_tsquery('english', 'running shoes -sale') gives 'run' & 'shoe' & !'sale'.

The @@ operator asks whether a document matches a query.

#A searchable column

Store the vector in a generated column so it stays current on every insert and update, and weight the title above the body:

alter table public.posts
  add column fts tsvector generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) stored;

create index posts_fts_idx on public.posts using gin (fts);

The configuration ('english' here) decides stemming and stop words. Use 'simple' for names, product codes or mixed-language content, where you want exact words without stemming.

#Query and rank

select id, title, ts_rank(fts, q) as rank
from public.posts, websearch_to_tsquery('english', 'trail running -sale') as q
where fts @@ q
order by rank desc
limit 20;

websearch_to_tsquery accepts what users type into search boxes: plain words, "quoted phrases", or and -exclusions. It does not throw on stray punctuation the way to_tsquery does, which is why it is the right default for user input.

ts_rank scores matches by frequency and weight, so a hit in the title (weight A) ranks above one in the body. For short snippets around the match, add ts_headline('english', body, q) to the select list. It is slower, so call it only for the rows you return.

#From the REST API

On StitchDB, the generated REST API exposes full text operators directly, and the JavaScript SDK wraps them:

// Full-text search
await stitchdb.from('posts').select('*').textSearch('body', 'postgres & realtime', { type: 'websearch', config: 'english' })

Point textSearch at the generated fts column to use the index. The REST operators are fts, plfts, phfts and wfts, matching to_tsquery, plainto_tsquery, phraseto_tsquery and websearch_to_tsquery.

The REST filter returns matches but cannot order by ts_rank. For ranked results, wrap the query in a function and call it over RPC:

create function public.search_posts(term text)
returns setof public.posts
language sql stable as $$
  select p.*
  from public.posts p, websearch_to_tsquery('english', term) q
  where p.fts @@ q
  order by ts_rank(p.fts, q) desc
  limit 50
$$;

const { data } = await stitchdb.rpc('search_posts', { term: 'trail running -sale' })

The function runs as the caller (security invoker is the default), so Row Level Security still filters the rows. A user never finds a post they are not allowed to read. See Functions (RPC).

#Layer 3: typo tolerance with pg_trgm

Full text search matches words, not spellings. "Postgers" finds nothing. The pg_trgm extension compares strings by their three-letter chunks and handles typos well, especially on short fields like names and titles:

create extension if not exists pg_trgm with schema extensions;

create index posts_title_trgm_idx on public.posts using gin (title extensions.gin_trgm_ops);

select id, title, extensions.similarity(title, 'postgers') as sim
from public.posts
where title operator(extensions.%) 'postgers'
order by sim desc
limit 10;

The trigram GIN index also speeds up ILIKE '%…%' queries, so if layer 1 is all you need on a large table, this index alone may fix it. A common pattern is to run full text search first and fall back to trigram similarity when it returns nothing. unaccent helps when users type "cafe" for "café"; both extensions are available on StitchDB under Database › Extensions (extensions).

#Layer 4: search by meaning

Keyword search cannot know that "automobile" and "car" are related, or that "how do I cancel" should find the "Ending your subscription" article. Semantic search turns each row and the query into embeddings, lists of numbers where similar meanings are close together, and finds the nearest ones.

You can build this yourself with pgvector: an embedding column, an index, an embedding API call on write and on search. The pgvector guide shows that path. It is the right choice when you want control over models, chunking or retrieval for an AI feature.

For a search box, StitchDB has a shorter path.

#The built-in search index

In Studio open Database › Search, pick a table, up to 8 columns, a language, and whether to search by meaning. The table needs a primary key. Existing rows are indexed at once; after that, inserts, updates and deletes are picked up by a trigger in the same transaction. Embeddings for semantic search are made in the background, usually within seconds, and only for rows whose text changed.

Then query from the app:

const { data, error } = await stitchdb.search('posts', 'running shoes', { limit: 20 })

// words only, with phrase and exclusion, two columns back
const { data: exact } = await stitchdb.search('posts', '"trail running" -sale', { mode: 'keyword', select: 'id,title' })

Or over HTTP:

curl 'https://<ref>.p.stitchdb.com/search/v1/posts?q=running%20shoes&limit=20' \
  -H "apikey: $STITCHDB_ANON_KEY" \
  -H "Authorization: Bearer $USER_ACCESS_TOKEN"

The answer is { data, scores, mode }: rows best first, each with a score, keyword_rank and semantic_rank. Results are rows of your table filtered by its Row Level Security policies, exactly like the REST API. Column order matters: a match in the first column ranks above one in the second.

#Limits and costs

Values from the plans API as of November 2026; see search and pricing. One embedding is used per changed row and per new search text; repeated searches are cached. When the month runs out, hybrid searches continue with words only and say so in a note, semantic searches return 429, and new rows wait. You can add your own OpenAI-compatible embeddings key to remove the cap, in which case requests go from your project's server to that provider.

The index lives in your database, in schema _stitch_search, and counts toward its size: roughly the indexed text plus about 6 KB per row for meaning.

#Which layer do you need?

#Common mistakes

• Building the tsvector in the query. where to_tsvector('english', body) @@ q cannot use an index on a stored column. Use the generated column or an expression index with the same expression.
• Mixing configurations. A vector built with 'english' queried with 'simple' misses stemmed words. Use the same configuration on both sides.
• Using to_tsquery for user input. It throws on syntax like a lone &. Use websearch_to_tsquery.
• Forgetting RLS on search functions. A security definer search function returns every row. Keep search functions as security invoker.

#FAQ

#Is Postgres full text search as good as Elasticsearch?

For most app search boxes it is enough and it removes a sync pipeline. Dedicated search engines offer more tuning for large, search-heavy products, such as faceting at scale and advanced relevance controls.

#Does full text search work in languages other than English?

Yes. Postgres ships configurations for many languages, such as german and french. The built-in index offers the same languages, and Any (simple) for exact words.

#Can I combine search with other filters?

Yes. In SQL, add where conditions next to fts @@ q. The REST textSearch filter combines with any other filter, and RPC functions can take extra arguments.

#Does the built-in index respect my policies?

Yes. App searches always apply Row Level Security. Only Studio's Try box searches as the table owner.

Add search to your app without another service: create a free project.