PostgreSQL Full-Text Search: How Far It Gets You Before Algolia or Meilisearch / Nov 13, 2026

If your data already lives in PostgreSQL, you already have a search engine. For an admin panel, a catalogue of a few thousand products, a blog or an internal tool, it is often all you need, and it saves you running a second system and keeping it in sync.

This post sets it up properly, fixes the user-input bug a lot of tutorials ship, adds typo tolerance where it helps, and lists the signs that it is time to move to a dedicated search service.

The three pieces

Postgres full-text search is built from three things:

• tsvector: a document reduced to normalised words (lexemes) and their positions. to_tsvector('english', 'Running quickly through the park') gives 'park':5 'quick':2 'run':1. "Running" was stemmed to "run", and "through" and "the" were dropped as stop words.
• tsquery: a search, such as run & park.
• A GIN index on the tsvector, so matching does not scan the whole table.

The @@ operator asks whether a tsvector matches a tsquery.

Set it up with a generated column

Store the tsvector as a generated column, so Postgres keeps it current on every insert and update. You don't need a trigger for this (Postgres 12 and later):

ALTER TABLE products
  ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B')
  ) STORED;
 
CREATE INDEX products_search_idx ON products USING GIN (search);

The weights make a match in the name count for more than a match in the description when you rank results.

Query user input safely

This is the part most examples get wrong. to_tsquery expects its own syntax, so passing what a user typed straight into it breaks the moment someone searches for c++, types a stray quote, or ends with &. The search returns a database error instead of results.

For a search box, use websearch_to_tsquery (Postgres 11 and later). It accepts the kind of text people type into search engines: quoted phrases, or, and -word to exclude, and it never raises a syntax error:

SELECT id, name, ts_rank(search, q) AS rank
FROM products, websearch_to_tsquery('english', $1) AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

Always pass the search text as a parameter ($1). Never build the SQL string from it.

Search as you type

websearch_to_tsquery matches whole words, so "nex" won't find "Next.js". For results while someone is still typing, you need prefix matching (nex:*), which only to_tsquery supports. Build that query from tokens you have cleaned yourself, so user input can never reach the query syntax:

// "nex js" -> "nex:* & js:*". Anything that is not a letter or a digit is dropped,
// so the result is always valid to_tsquery syntax.
export function prefixQuery(input) {
  return input
    .toLowerCase()
    .split(/[^\p{L}\p{N}]+/u)
    .filter(Boolean)
    .map((token) => `${token}:*`)
    .join(" & ");
}

const q = prefixQuery(userInput);
if (!q) return []; // nothing searchable was typed
 
const { rows } = await pool.query(
  `SELECT id, name
     FROM products
    WHERE search @@ to_tsquery('english', $1)
    ORDER BY ts_rank(search, to_tsquery('english', $1)) DESC
    LIMIT 10`,
  [q],
);

Snippets, without slowing everything down

ts_headline returns a snippet with the matching words marked. It works on the original text, not the index, so it is slow. Only run it on the rows you are actually returning:

WITH hits AS (
  SELECT id, name, description, ts_rank(search, q) AS rank, q
  FROM products, websearch_to_tsquery('english', $1) AS q
  WHERE search @@ q
  ORDER BY rank DESC
  LIMIT 20
)
SELECT id, name, rank,
       ts_headline('english', description, q, 'MaxWords=30, MinWords=10') AS snippet
FROM hits;

One security note. ts_headline does not escape anything. By default it wraps matches in <b> tags and returns the rest of the text as it is. If that column can ever contain text a user wrote, don't render the snippet as HTML. Use plain marker strings with StartSel and StopSel, escape the text, then turn the markers into <mark> elements in your UI.

Typos: what Postgres can and can't do

Full-text search does stemming, not spelling. "Running" finds "run", but "recipie" finds nothing.

The pg_trgm extension helps. It compares strings by their three-letter chunks, which makes it good at short fields like product names, and at "did you mean" suggestions:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm_idx ON products USING GIN (name gin_trgm_ops);
 
SELECT name, similarity(name, $1) AS score
FROM products
WHERE name % $1
ORDER BY score DESC
LIMIT 5;

% matches when the similarity is above a threshold (0.3 by default). A common pattern is to run the full-text query first, and fall back to the trigram query when it returns nothing.

Pick the language deliberately

'english' stems English words and drops English stop words. That is wrong for names, brands and other languages. A product called "Aso Oke" or a Yoruba or Pidgin phrase won't stem in any useful way. Use the 'simple' configuration for fields like that. It lowercases and splits, but doesn't stem or drop anything. Trigram matching works for them too.

Sometimes you don't need a server at all

For a few hundred static pages, a database may be more than you need. The resources section of this site builds a small search index at build time, about 25 KB for its pages, and searches it in the browser on every keystroke. There is no server and nothing to keep in sync, because the index is rebuilt with the site.

When it is time to move on

Postgres search stops being enough when one of these is true:

• Users expect typo tolerance and instant results across long text, not only names.
• You need faceted counts, like "Fabrics (120) · Candles (34)", across many filters at once.
• Relevance needs tuning per query, with synonyms, pinned results or business rules.
• Search traffic competes with your normal queries, and slows down checkout or saves.

That is when a dedicated engine earns its place. Meilisearch is open source, can be self-hosted, and is typo-tolerant out of the box. Algolia is a mature hosted service priced by usage, so check its current pricing against your real traffic before committing.

Moving has a cost that is easy to forget: a second system to keep in sync. Every insert, update and delete has to reach the search index. Records that should be private must never reach a public one. Plan the sync before you plan the UI.

The short version

• Store a weighted tsvector in a generated column, with a GIN index.
• Use websearch_to_tsquery for user input. For prefix search, build the query from cleaned tokens.
• Run ts_headline only on the rows you return, and never render its output as raw HTML.
• Add pg_trgm for names and "did you mean".
• Move to a dedicated engine when your users, not a benchmark, show you Postgres is no longer enough.

Further reading: the PostgreSQL documentation on full-text search and pg_trgm.