PostgreSQL Full-Text Search: The Complete Guide to tsvector

Why Full-Text Search Beats LIKE

Most applications start with LIKE '%foo%' as their search implementation. It works for the first hundred rows. It falls apart around ten thousand for three specific reasons: it is slow (sequential scan on every query), it is dumb (searches for literal substrings, missing 'running' when the user searches 'run'), and it is noisy (matches inside words, so 'cat' finds 'category' and 'concatenate'). None of these matter on a demo dataset. All of them matter on a real one.

PostgreSQL's full-text search understands text the way people actually think about it. It stems words (so 'running' and 'ran' both match a search for 'run'), tokenizes properly (so 'cat' does not match 'category'), ranks results by relevance (better matches first), and can be accelerated with a GIN index to make sub-100ms queries realistic on tables with millions of rows. Best of all, it is built into every PostgreSQL distribution — no extensions to enable, no external services to run.

Understanding tsvector — The Core Concept

The heart of PostgreSQL full-text search is the tsvector type. A tsvector is a sorted list of lexemes (normalized word forms) with their positions in the original text. Converting text to a tsvector strips stop words ('the', 'and', 'or'), stems each word to its root form, and records where each lexeme appeared. The transformation is one-way: you cannot get the original text back from a tsvector, but you can search it very fast.

-- Convert English text to a tsvector
SELECT to_tsvector('english', 'The quick brown foxes are running fast');
-- Result: 'brown':3 'fast':7 'fox':4 'quick':2 'run':6

Notice what happened: 'The' and 'are' disappeared (stop words), 'foxes' became 'fox', 'running' became 'run', and each surviving word got a position number. Now any query that stems to 'fox', 'run', 'quick', 'brown', or 'fast' will match this tsvector — regardless of the original grammatical form.

Setting Up Your First Full-Text Search Column

The two-column pattern is the standard: one text column for the original content, one tsvector column pre-computed for fast searching. Postgres generated columns keep the tsvector in sync automatically — no triggers, no manual updates.

-- Products table with title, description, and a computed search column
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B')
  ) STORED
);

Two things are happening in that computed column. First, we combine two source columns (title and description) into a single searchable tsvector. Second, we weight them with setweight — titles get weight A (highest), descriptions get weight B (medium). When we rank results later, matches in the title will score higher than matches in the description. The four weight levels (A, B, C, D) let you express relative importance across as many source columns as you want.

Querying with tsquery — Matching Words to Vectors

The counterpart to tsvector is tsquery — a search expression that gets matched against the vector. The @@ operator returns true when the query matches the vector. tsquery supports AND (&), OR (|), NOT (!), phrase search (<->), and prefix matching (:*).

-- Simple word search — natural-language style
SELECT id, title
FROM products
WHERE search_vector @@ plainto_tsquery('english', 'wireless headphones');

-- Boolean search with prefix matching
SELECT id, title
FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & headphone:*');

-- Phrase search (words must appear in order)
SELECT id, title
FROM products
WHERE search_vector @@ phraseto_tsquery('english', 'noise cancelling headphones');

Three functions to remember. plainto_tsquery is the safe default — treats user input as words to AND together, ignoring operators. to_tsquery is the powerful one — parses operators (& | ! :*) but requires validated input (otherwise errors on unbalanced parens). phraseto_tsquery matches words in order, useful for exact-phrase searches. For unfiltered user input, use websearch_to_tsquery — it accepts Google-style syntax (quotes for phrases, OR for alternatives) and never errors on malformed input.

Ranking with ts_rank — Ordering by Relevance

Matching is half the problem. The other half is putting the best matches first. ts_rank scores how well a vector matches a query — higher scores mean better matches. Order by the rank and you get relevance-ranked results.

SELECT
  id,
  title,
  ts_rank(search_vector, query) AS rank
FROM
  products,
  websearch_to_tsquery('english', 'wireless headphones') query
WHERE
  search_vector @@ query
ORDER BY
  rank DESC
LIMIT 20;

The rank score considers three things: how many query terms matched, how frequently they appear in the document (higher = better), and how close they appear together. For most search interfaces this default ranking is enough. If you need to boost recent content, multiply the rank by a freshness factor: ts_rank(search_vector, query) * (1.0 / (1 + EXTRACT(days FROM (NOW() - created_at)))) will surface recent items faster than older equally-relevant ones.

GIN Indexes — Making It Fast

Without an index, tsvector search still uses a sequential scan — every row gets its search_vector matched against the query. Fine on ten thousand rows, painful on a million. The GIN (Generalized Inverted Index) is the acceleration layer that makes full-text search actually fast on large tables.

-- Create the GIN index on the search vector column
CREATE INDEX products_search_idx
  ON products
  USING GIN (search_vector);

-- Run ANALYZE so the query planner picks up the index
ANALYZE products;

On a table of two million product records, a search query typically drops from 3-4 seconds (sequential scan) to under 20 milliseconds (index scan) after adding this GIN index. Verify the index is being used with EXPLAIN ANALYZE — look for 'Bitmap Index Scan on products_search_idx' in the query plan. If you see 'Seq Scan' instead, either the index is missing or the query planner has stale statistics — run ANALYZE and check again.

Highlighting Matched Terms

Users expect search results to show WHERE their query matched — the little bolded snippets in Google's search results. Postgres provides this out of the box via ts_headline, which wraps matched terms in configurable HTML tags.

SELECT
  id,
  ts_headline('english', description,
    websearch_to_tsquery('english', 'wireless bluetooth'),
    'StartSel=<mark>, StopSel=</mark>, MaxWords=30, MinWords=15'
  ) AS excerpt
FROM products
WHERE search_vector @@ websearch_to_tsquery('english', 'wireless bluetooth');

The MaxWords/MinWords options control snippet length. StartSel/StopSel wrap matches — <mark> is the semantic HTML tag for highlighted text and defaults to a yellow background in most browsers. For custom styling, pick a class name (StartSel=<span class="hl">) and style it in CSS.

Multi-Language Support

The language argument to to_tsvector ('english', 'spanish', 'french', etc.) determines stemming, stop words, and tokenization rules. Postgres ships with configurations for ~20 languages. If your data is monolingual, pick the appropriate config and forget about it. If your data mixes languages, store the language per row and use a computed column that switches configs.

-- Multilingual: derive config from a language column
ALTER TABLE articles ADD COLUMN language regconfig NOT NULL DEFAULT 'english';

ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector(language, body)) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

Now each row is indexed with its correct language configuration. A French article gets French stemming; a Spanish one gets Spanish. For languages Postgres does not ship with (Hindi, Chinese, Arabic), consider dedicated extensions like RUM or upgrade to a specialized search engine (Meilisearch supports 100+ languages out of the box).

Full-Text Search vs Fuzzy Search — When to Use What

Full-text search and fuzzy search solve different problems. Understanding which to reach for saves you from mixing them badly.

Common Pitfalls

Five mistakes that show up repeatedly when teams roll out Postgres FTS for the first time:

• Forgetting the GIN index — queries work without it, just slowly. EXPLAIN ANALYZE catches this quickly.
• Using to_tsquery with unfiltered user input — malformed queries throw errors that reach the user. Use websearch_to_tsquery or plainto_tsquery for untrusted input.
• Not weighting columns — every FTS project eventually needs setweight to make title matches beat description matches. Add it early.
• Trying to search in one language when your data is multilingual — creates weird stemming errors. Store language per row and let the computed column pick the right config.
• Reaching for Elasticsearch too early — Postgres FTS covers most workloads up to several million rows with sub-100ms latency. Prove you have outgrown it before adding a whole new service.

Final Thoughts

PostgreSQL full-text search is one of the most under-used features in the entire Postgres feature set. Teams reach for Elasticsearch, Algolia, or Meilisearch by default, then spend weeks on data sync pipelines, dashboards, and operational overhead — for capabilities Postgres could have delivered with two SQL statements and a GIN index. The tsvector + tsquery + GIN combination is enough for typo-free concept search up to several million rows, with sub-100ms latency, in the database you already run. Layer fuzzy search (pg_trgm) on top for typo tolerance, and you have most of what teams reach for external search engines to solve — with none of the operational complexity. Learn tsvector deeply before adopting anything else; you will likely find you did not need the other thing.

Frequently Asked Questions