Implementing Postgres Full-Text Search With tsvector and pg_trgm

By James Nguyen Updated September 24, 2026

Standing up Elasticsearch for a product search feature that needed to handle maybe a hundred thousand rows felt like bringing a shipping container to move a bookshelf, an entire separate service to keep in sync, monitor, and pay for, when Postgres was already sitting right there holding the data. Postgres's built-in full-text search, tsvector and tsquery, handled the actual requirement well, and layering pg_trgm on top covered the typo-tolerance gap that pure full-text search doesn't solve on its own.

What tsvector Actually Does to Your Text

A tsvector is a preprocessed, normalized representation of text: it lowercases, strips common stop words like "the" and "and," and reduces words to their linguistic stem via a process called stemming, so "running," "runs," and "ran" all normalize toward the same lexeme and match each other in a search. Converting a text column to tsvector isn't just an index type, it's an actual transformation of the text into a structure built specifically for this kind of matching, distinct from a raw string comparison.

SELECT to_tsvector('english', 'Running shoes for marathon training');
-- 'marathon':4 'run':1 'shoe':2 'train':5

Generated Columns Keep the Index Current Automatically

Recomputing to_tsvector on every search query against raw text works but is slow and can't be indexed efficiently. A generated column that Postgres maintains automatically whenever the source columns change means the tsvector stays current without application code remembering to update it on every write, which is the failure mode I've seen in hand-rolled trigger-based versions of this that inevitably drift out of sync eventually.

ALTER TABLE products ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B')
  ) STORED;

CREATE INDEX idx_products_search ON products USING GIN (search_vector);

Weighting Fields So Title Matches Rank Above Description Matches

The setweight calls above aren't decorative, they tag lexemes from the name field as weight 'A' and description as weight 'B', and ts_rank uses those weights when scoring how well a document matches a query, so a product whose name contains the search term ranks above one where the term only appears buried in a long description. Without explicit weighting, a search for "running shoes" would rank a product with that phrase mentioned once in a paragraph about return policy exactly the same as a product actually named that.

Querying With plainto_tsquery for User-Typed Input

tsquery has its own operators for AND, OR, and phrase proximity, but accepting that syntax directly from user input means a search box breaks the moment someone types an unescaped special character. plainto_tsquery takes plain natural-language input and safely converts it to a proper tsquery, handling the normalization the same way to_tsvector did on the stored side, so "Running Shoes" as typed correctly matches a stored vector containing the stemmed lexeme run.

SELECT id, name, ts_rank(search_vector, query) AS rank
FROM products, plainto_tsquery('english', 'running shoes') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Where Pure Full-Text Search Falls Short: Typos

Stemming handles "running" matching "run," but it does nothing for a user typing "runing" with a missing letter, tsvector's stemming operates on correctly spelled words and a misspelled one simply doesn't match. This is a real gap for a public-facing search box where typos are routine, and it's the specific problem pg_trgm solves, not full-text search's job at all.

pg_trgm for Fuzzy, Typo-Tolerant Matching

pg_trgm breaks text into overlapping three-character sequences (trigrams) and measures similarity by how many trigrams two strings share, which tolerates a misspelling gracefully since most of a misspelled word's trigrams still match the correctly spelled version. Combining it with a GIN index on the trigram-extracted column lets a similarity-based query run fast even on a fairly large table, rather than falling back to a full sequential scan doing string comparison on every row.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

SELECT name, similarity(name, 'runing shoez') AS sim
FROM products
WHERE name % 'runing shoez'  -- % is the pg_trgm similarity operator
ORDER BY sim DESC
LIMIT 10;

Combining Both: Full-Text First, Trigram Fallback

The pattern that actually shipped runs the tsvector-based full-text query first, since it's faster and ranks well for correctly spelled input, and only falls back to a pg_trgm similarity query against the product name if the full-text query returns zero results, treating the fuzzy match as a typo-recovery path rather than the primary search mechanism. This kept normal searches fast while still surfacing something reasonable for "runing shoez" instead of an empty results page.

Final Verdict

For a dataset in the tens or low hundreds of thousands of rows without a hard requirement for faceted search or distributed scale, Postgres's tsvector plus pg_trgm covers real full-text search, ranking, and typo tolerance without standing up a separate search service to keep in sync. It's not a replacement for Elasticsearch at genuine scale, but for the size of problem most of us are actually solving, reaching for a second service by default is very often solving a problem you don't have yet.