A Practical Guide to Postgres Full-Text Search

The mirror image of tsvector is tsquery, the parsed representation of what a user is looking for. You rarely write tsquery by hand. Instead you use websearch_to_tsquery, which accepts the kind of input people actually type — quoted phrases, or, and a leading - to exclude a term — and turns it into a safe query. That function alone removes a surprising amount of custom parsing code.

Store the vector, don't compute it every time

The naive approach recomputes to_tsvector on every request. That works for a demo and falls over in production, because the database has to scan and tokenize every row for every search. The fix is to store the vector once, in a column, and let Postgres keep it up to date.

A generated column is the cleanest option:

ALTER TABLE post
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(excerpt, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'C')
  ) STORED;

Two things are happening here. First, the column is derived automatically, so it can never drift from the source columns. Second, setweight tags each part of the document with an importance class from A (highest) to D. A match in the title should rank higher than a match buried in the body, and these weights are how you express that later during ranking.

Make it fast with a GIN index

A stored vector is not enough on its own; without an index, Postgres still reads every row. The right index for full-text search is GIN (Generalized Inverted Index), which maps each lexeme to the rows that contain it — exactly the lookup a search needs.

CREATE INDEX post_search_idx ON post USING GIN (search_vector);

With that index in place, a query filters to just the candidate rows instead of scanning the table:

SELECT id, title
FROM post
WHERE search_vector @@ websearch_to_tsquery('english', 'postgres search')
LIMIT 20;

The @@ operator is the match test: it returns true when the vector satisfies the query. On a table with millions of rows, this returns in a few milliseconds.

Ranking: the part users actually feel

Matching tells you whether a row is relevant; ranking tells you how relevant, and it is what makes results feel smart. ts_rank_cd scores each match using term frequency and the weights you assigned earlier, so a keyword in the title outranks the same keyword in the body.

SELECT id, title,
       ts_rank_cd(search_vector, query) AS rank
FROM post, websearch_to_tsquery('english', 'postgres search') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Order by that rank and your best results rise to the top. For content sites, a small tweak pays off: blend text rank with a freshness or popularity signal so a strong-but-ancient article does not always beat a timely one. A weighted sum of ts_rank_cd and a recency score is usually enough.

Highlighting the match

Users trust results more when they can see why something matched. ts_headline returns a snippet with the matching terms wrapped in markers you choose, ready to render in a results list:

SELECT ts_headline('english', body,
         websearch_to_tsquery('english', 'gin index'),
         'StartSel=<mark>, StopSel=</mark>')
FROM post
WHERE id = $1;

Because ts_headline reprocesses the original text, run it only on the handful of rows you actually display, never across the whole result set.

A realistic rollout plan

You do not need every feature on day one. A sensible order is: add the generated column, add the GIN index, switch your query to @@ with websearch_to_tsquery, then layer in ranking and highlighting once the basics are serving traffic. Each step is independently shippable and reversible.

If you want a managed Postgres that supports all of this out of the box, a provider like Neon's serverless Postgres gives you branching databases and generous full-text support without any extra search infrastructure. Whatever you host on, the techniques above are standard Postgres — they move with you.

When to graduate to a dedicated engine

Postgres search has real limits. It does not do fuzzy typo tolerance well, its synonym handling is manual, and very large faceted-search workloads are happier on a purpose-built engine. Those are good reasons to migrate later, with real usage data in hand. They are poor reasons to add a second system before you have a single paying user. Start with the database you already trust, measure, and graduate only when the numbers say so.

Sources: PostgreSQL — Full Text Search · PostgreSQL — Full Text Search index types (GIN and GiST)