How to Use Full-Text Search in PostgreSQL

For a long time, whenever a project needed search functionality, my first instinct was to reach for a dedicated search engine like Elasticsearch or Algolia. Then, on a project with a tight budget and a small team, I didn’t have the luxury of running and maintaining a separate search cluster. That’s when I actually sat down and learned PostgreSQL’s built-in full-text search, and honestly — I was surprised at how capable it is. For a huge number of real-world applications, you don’t need a separate search engine at all. In this article, I’ll walk through how full-text search works in PostgreSQL, the syntax you need to know, real examples, and the practical lessons I’ve picked up along the way.

Why Not Just Use LIKE or ILIKE?

Before I get into full-text search, it’s worth explaining why simple pattern matching isn’t enough. A query like:

SELECT * FROM articles WHERE title ILIKE '%postgres%';

works fine for tiny datasets and exact substring matches, but it falls apart quickly. It doesn’t understand word boundaries, it can’t rank results by relevance, it doesn’t handle stemming (so “running” won’t match “run”), and it can’t ignore stop words like “the” or “and.” It’s also slow on large tables because it typically can’t use a standard B-tree index efficiently for a leading wildcard search. Full-text search solves all of these problems.

The Core Concept: tsvector and tsquery

PostgreSQL’s full-text search revolves around two special data types:

• tsvector — a preprocessed, normalized representation of a document’s text, broken into lexemes (normalized word forms) with position information.
• tsquery — a preprocessed, normalized representation of a search query, also broken into lexemes, combined with boolean operators.

You search by matching a tsvector against a tsquery using the @@ operator.

Let’s see this in action:

SELECT to_tsvector('english', 'Running a PostgreSQL database requires careful tuning');

This returns something like:

'careful':5 'databas':3 'postgresql':2 'requir':4 'run':1 'tune':6

Notice how “Running” became run, and “database” became databas. This is stemming — PostgreSQL reduces words to their root form so that searches for “run” also match “running,” “runs,” and “ran.” It also removed common stop words that add no search value.

Now let’s create a matching query:

SELECT to_tsvector('english', 'Running a PostgreSQL database requires careful tuning')
   @@ to_tsquery('english', 'run & postgresql');

This returns true because both lexemes are present in the document.

Setting Up Full-Text Search on a Table

Let’s walk through a realistic example — a blog articles table.

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    published_at TIMESTAMPTZ DEFAULT now()
);

I could compute the tsvector on the fly for every query, but that’s wasteful — it means reprocessing the text on every single search. Instead, I add a dedicated tsvector column and keep it updated automatically.

ALTER TABLE articles ADD COLUMN search_vector tsvector;

Populating the Search Vector

For existing rows:

UPDATE articles
SET search_vector = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

I give title and body different weights later, but for a simple version, concatenating them works fine.

Keeping It Updated Automatically with a Trigger

I don’t want to remember to update search_vector manually every time a row changes, so I use a trigger. PostgreSQL even provides a convenience function for this.

CREATE TRIGGER articles_search_vector_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

This automatically regenerates search_vector whenever title or body changes, so I never have to think about it again in application code.

Weighting Fields by Importance

Not all text is equally important. A match in the title probably matters more than a match somewhere in the body. PostgreSQL lets you assign weights (A, B, C, D, with A being the highest) to different parts of a document.

UPDATE articles
SET search_vector =
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B');

If I want the trigger to reflect this weighting instead of the simple version above, I write a custom function rather than relying on the built-in trigger helper:

CREATE OR REPLACE FUNCTION articles_search_vector_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B');
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_vector_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_vector_trigger();

This weighting matters most when it comes to ranking results, which I’ll cover shortly.

Indexing for Performance

Just like with JSONB, full-text search really shines once you add a GIN index.

CREATE INDEX idx_articles_search_vector ON articles USING GIN (search_vector);

Without this index, PostgreSQL has to scan and evaluate every row for every search. With it, searches on large tables stay fast even as the table grows into the millions of rows.

Running Searches

Now for the actual searching. Let’s find articles about database performance:

SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & performance');

Using plainto_tsquery for User Input

Writing raw tsquery syntax with & and | isn’t something you want to expose to end users typing into a search box. That’s what plainto_tsquery is for — it takes plain text and converts it into a query automatically, ANDing the terms together.

SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'database performance tuning');

Using websearch_to_tsquery for Search-Engine-Style Input

If you want to support things like quoted phrases and OR in a way similar to how people type into Google, websearch_to_tsquery (available since PostgreSQL 11) is the better option.

SELECT id, title
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', '"database performance" OR tuning');

I generally default to websearch_to_tsquery for any user-facing search box these days, because it handles phrase matching and negation (-word) in a way that feels natural to users without me having to build my own query parser.

Ranking Results by Relevance

A search feature isn’t very useful if the most relevant results aren’t at the top. PostgreSQL gives you ts_rank and ts_rank_cd for this.

SELECT id, title,
       ts_rank(search_vector, query) AS rank
FROM articles, websearch_to_tsquery('english', 'database performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

ts_rank takes the weights you assigned earlier (A, B, C, D) into account, so a match in the title (weight A) contributes more to the score than a match buried in the body (weight B). ts_rank_cd additionally considers how close together the matching terms are in the document (“cover density”), which is useful when phrase proximity matters to relevance.

Highlighting Matches

Users like to see why a result matched — the classic “snippet with bolded search terms” you see in search engine results. PostgreSQL provides ts_headline for exactly this.

SELECT id, title,
       ts_headline('english', body, websearch_to_tsquery('english', 'database performance'),
                   'StartSel=<b>, StopSel=</b>, MaxWords=30, MinWords=15') AS snippet
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'database performance')
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', 'database performance')) DESC;

This returns a snippet of the body text with the matching words wrapped in <b> tags, which I can drop straight into a search results page.

Handling Multiple Languages

PostgreSQL ships with text search configurations for many languages (english, french, german, spanish, and more), each with its own stemming and stop word rules. If you’re building a multilingual application, you can pass the appropriate configuration per row, and even store the language as a column so the trigger picks the right one dynamically.

SELECT cfgname FROM pg_ts_config;

If your content is genuinely mixed-language and you don’t know the language ahead of time, this gets trickier — PostgreSQL doesn’t automatically detect language, so you’d need to determine it yourself (either at ingestion time or with an external library) and store it alongside the text.

Fuzzy Matching and Typo Tolerance

Full-text search handles stemming, but it doesn’t handle typos out of the box. For that, I usually pair it with the pg_trgm extension, which supports trigram-based similarity matching.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT title, similarity(title, 'postgres performnce') AS sim
FROM articles
ORDER BY sim DESC
LIMIT 5;

This isn’t a replacement for full-text search — it solves a different problem (fuzzy string similarity vs. linguistic search) — but combining the two gives you something close to a modern search experience: stemmed, ranked, typo-tolerant search, all inside PostgreSQL.

Common Use Cases

• Blog and documentation search — exactly the example I used above.
• Product search in an e-commerce catalog, often combined with pg_trgm for typo tolerance.
• Support ticket or knowledge base search, where relevance ranking really matters.
• Searching structured records like contacts or companies by name, notes, or tags.
• Log or audit search across free-text fields.

Troubleshooting Tips

Searches return nothing even though the word is clearly in the text. Check whether the word was stemmed differently than you expect. to_tsvector('english', 'analysis') might not match to_tsquery('english', 'analyses') unless they share a stem — test both sides with to_tsvector/to_tsquery directly to see what lexemes are actually produced.

Search is slow. Almost always means the GIN index is missing, unused, or the query isn’t hitting the indexed column (for example, computing to_tsvector() on the fly in the WHERE clause instead of querying the precomputed column). Run EXPLAIN ANALYZE and confirm you see a Bitmap Index Scan on your GIN index.

Trigger doesn’t fire on bulk imports. If you’re using COPY or bulk INSERT ... SELECT, make sure the trigger is still enabled — some import strategies temporarily disable triggers for performance and forget to backfill the search vector afterward. Run a manual UPDATE to backfill if needed.

Ranking feels off. Double check your weights. It’s a common mistake to weight everything the same, which makes ts_rank far less useful. Also remember ts_rank_cd is usually better when proximity of terms matters (e.g., someone searching a two or three word phrase).

Best Practices

1. Always store a precomputed tsvector column and index it with GIN. Don’t compute to_tsvector() inline in your WHERE clause on large tables.
2. Use triggers to keep the vector in sync, or maintain it in application code if you prefer more control — just be consistent.
3. Weight fields deliberately. Titles, tags, and headings should usually outrank body text.
4. Use websearch_to_tsquery for user-facing search boxes. It’s far more forgiving of how real people type queries.
5. Combine with pg_trgm if typo tolerance matters to your users.
6. Test your text search configuration against real content, especially if you’re working in a language other than English or with a lot of domain-specific jargon that the stemmer might mangle.
7. Don’t over-engineer early. Full-text search in PostgreSQL comfortably handles search across millions of rows for most applications. Only consider a dedicated search engine once you have requirements that genuinely exceed this — things like faceted search at massive scale, complex relevance tuning pipelines, or geographically distributed search infrastructure.

A Real-World Example: Searchable Support Tickets

Let me walk through a concrete setup I’ve built for a support ticket system, since it shows several of these pieces working together. Tickets have a subject, a body, and a set of free-text tags, and support agents need to search across all of it with relevance ranking.

CREATE TABLE tickets (
    id SERIAL PRIMARY KEY,
    subject TEXT NOT NULL,
    body TEXT NOT NULL,
    tags TEXT[] DEFAULT '{}',
    search_vector tsvector,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE OR REPLACE FUNCTION tickets_search_vector_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.subject, '')), 'A') ||
        setweight(to_tsvector('english', array_to_string(coalesce(NEW.tags, '{}'), ' ')), 'B') ||
        setweight(to_tsvector('english', coalesce(NEW.body, '')), 'C');
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER tickets_search_vector_update
BEFORE INSERT OR UPDATE ON tickets
FOR EACH ROW EXECUTE FUNCTION tickets_search_vector_trigger();

CREATE INDEX idx_tickets_search_vector ON tickets USING GIN (search_vector);

Notice I’m folding the tags array into the vector with its own weight tier, between subject and body — this lets a tag match rank higher than a body match without being as dominant as a subject match. Searching, with ranking and a highlighted snippet, looks like this:

SELECT id, subject,
       ts_rank(search_vector, query) AS rank,
       ts_headline('english', body, query, 'MaxWords=25, MinWords=10') AS snippet
FROM tickets, websearch_to_tsquery('english', 'login failing after password reset') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

This one query gives a support dashboard everything it needs: ranked results and a preview snippet, without any application-side search logic at all.

Combining Full-Text Search with Filters

In practice, search is rarely just “match this text” — it’s usually combined with structured filters like status or date range. Because search_vector is just another column, this composes naturally with ordinary WHERE clauses:

SELECT id, subject, ts_rank(search_vector, query) AS rank
FROM tickets, websearch_to_tsquery('english', 'billing refund') query
WHERE search_vector @@ query
  AND created_at > now() - interval '90 days'
  AND 'urgent' = ANY(tags)
ORDER BY rank DESC
LIMIT 20;

PostgreSQL’s planner handles combining the GIN index scan with the other filters efficiently, so I don’t need to build any special infrastructure to support “search plus filters” — it’s just SQL.

Partial Word and Prefix Matching

Sometimes users want results as they type, before a full word is finished. tsquery supports prefix matching with a trailing :*:

SELECT id, subject
FROM tickets
WHERE search_vector @@ to_tsquery('english', 'passw:*');

This matches “password,” “passwords,” and any other lexeme starting with that stem, which is handy for autocomplete-style search boxes, though I usually pair it with a reasonable minimum character count in the application before firing the query, both for relevance and to avoid hammering the database on every keystroke.

Frequently Asked Questions

Can full-text search handle very large tables (tens of millions of rows)? Yes, comfortably, as long as the GIN index is in place and search is genuinely filtering the result set rather than matching nearly every row. I’ve run it against tables well into the tens of millions of rows without issue.

How is this different from pg_trgm? Full-text search is linguistically aware — it stems words, understands boolean and phrase queries, and ranks by relevance. pg_trgm is purely about character-level string similarity, which is what makes it good at typo tolerance but means it has no concept of word roots or language structure. They solve different problems and combine well together.

Does full-text search work well for non-English content? Yes, PostgreSQL ships text search configurations for many languages, though quality varies — stemming rules for some languages are more mature than others, and truly multilingual content (mixed languages within a single document) is the hardest case and usually needs some extra handling at the application layer.

Do I need to reindex after adding new documents? No — the GIN index updates automatically as rows are inserted or updated, the same way any other index does. There’s no separate “reindexing” step needed for ordinary data changes.

Wrapping Up

Full-text search in PostgreSQL isn’t just a toy feature bolted onto a relational database — it’s a genuinely capable search system that has saved me from standing up and maintaining extra infrastructure more times than I can count. Once you understand tsvector, tsquery, weighting, and GIN indexing, you have most of what you need to build a fast, relevant search experience without leaving your primary database. I’d encourage you to try it on your next project before assuming you need something heavier.