Full-Text Search with PostgreSQL

The Search Engine Already in Your Database

When someone says “we need search”, the conversation almost always jumps to Elasticsearch or Algolia. It’s a reflex at this point. But here’s the thing: PostgreSQL has had a full-text search engine baked into its core since version 8.3, released in 2008. That’s 18 years of battle-tested search sitting right inside the database you’re probably already running.

In this post, we’ll explore how PostgreSQL full-text search works, when it’s the right choice, and how to set it up with practical code examples. By the end, you’ll have everything you need to build a solid search feature without spinning up a single extra service.

How PostgreSQL Full-Text Search Works

At its core, Postgres full-text search revolves around two data types: tsvector and tsquery.

A tsvector is a sorted list of distinct lexemes (think of them as normalised words). When you convert text into a tsvector, Postgres applies language-specific processing: it strips stop words like “the” and “is”, and it stems words to their root form. “Running”, “runs”, and “ran” all become “run”.

SELECT to_tsvector(’english’, ‘The quick brown foxes were jumping’);

-- Result: ‘brown’:3 ‘fox’:4 ‘jump’:5 ‘quick’:2

Notice how “foxes” became “fox” and “jumping” became “jump”. The numbers represent positions in the original text.

A tsquery represents your search query. It supports Boolean operators like `&` (AND), `|` (OR), `!` (NOT), and `<->` (FOLLOWED BY) for phrase matching.

SELECT to_tsquery(’english’, ‘quick & fox’);

-- Result: ‘quick’ & ‘fox’

To check whether a document matches a query, you use the `@@` operator:

SELECT to_tsvector(’english’, ‘The quick brown fox’)

@@ to_tsquery(’english’, ‘quick & fox’);

-- Result: true

Simple enough. But this alone would be slow on a real table. That’s where indexes come in.

Setting Up Search: The Three-Step Pattern

In my experience, you need exactly three things to get production-ready full-text search in Postgres: a tsvector column, a GIN index, and a ranking query. Let’s walk through each.

Step 1: Add a Generated tsvector Column

Since PostgreSQL 12, you can use a generated column that automatically computes the tsvector whenever the row changes. This is the cleanest approach because there are no triggers to maintain.

CREATE TABLE articles (

id SERIAL PRIMARY KEY,

title VARCHAR(255) NOT NULL,

body TEXT NOT NULL,

author VARCHAR(100),

search_vector tsvector GENERATED ALWAYS AS (

setweight(to_tsvector(’english’, coalesce(title, ‘’)), ‘A’) ||

setweight(to_tsvector(’english’, coalesce(body, ‘’)), ‘B’) ||

setweight(to_tsvector(’english’, coalesce(author, ‘’)), ‘C’)

) STORED
);

The `setweight()` function assigns importance levels. Weight ‘A’ is the highest, ‘D’ the lowest. Here, title matches will rank higher than body matches, which rank higher than author matches. This is exactly what users expect from search results.

Step 2: Create a GIN Index

A GIN (Generalised Inverted Index) works like the index at the back of a textbook. It maps each word to a list of rows containing that word. Without this index, Postgres would need to scan every row and compute tsvectors on the fly.

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

GIN indexes are about 3x faster for lookups than the alternative GiST index. The trade-off is that they’re slower to build and larger on disk, but for read-heavy search workloads (which is most search workloads), GIN is the right choice.

Step 3: Query with Ranking

SELECT id, 
  title,
  ts_rank(search_vector, query) AS rank

FROM articles, websearch_to_tsquery(’english’, ‘database performance’) AS query

WHERE search_vector @@ query

ORDER BY rank DESC

LIMIT 20;

`websearch_to_tsquery` is a gem that was introduced in PostgreSQL 11. It accepts Google-like search syntax: quoted phrases for exact match, `-` for exclusion, and implicit AND between words. It handles messy user input gracefully, which makes it perfect for user-facing search boxes.

Want to show highlighted snippets? Use `ts_headline`:

SELECT id,
     title,
     ts_headline(’english’, 
     body,
     websearch_to_tsquery(’english’, ‘database performance’),
    ‘StartSel=<mark>, StopSel=</mark>, MaxFragments=3’
) AS snippet
FROM articles
WHERE search_vector @@ websearch_to_tsquery(’english’, ‘database performance’);

Handling Typos with pg_trgm

One legitimate gap in Postgres FTS is its lack of typo tolerance. If a user searches for “postgressql” (note the typo), the full-text search won’t find “postgresql”. This is where the pg_trgm extension comes in.

Trigram matching breaks words into three-character sequences and compares them. It doesn’t care about stemming or language; it just measures how similar two strings look.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create a trigram index on the title

CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);

-- Fuzzy search

SELECT title, similarity(title, ‘postgressql’) AS sim

FROM articles

WHERE title % ‘postgressql’

ORDER BY sim DESC;

The real power comes from combining both approaches:

SELECT id, 
  title,
  ts_rank(search_vector,
  websearch_to_tsquery(’english’, ‘postgres’)) * 2.0 + similarity(title, ‘postgres’) AS combined_score

FROM articles

WHERE search_vector @@ websearch_to_tsquery(’english’, ‘postgres’)

OR title % ‘postgres’

ORDER BY combined_score DESC

LIMIT 20;

This gives you relevance-ranked results from full-text search, with a fallback to fuzzy matching for typos. It covers a surprising amount of ground.

Multi-Table Search with Materialised Views

Things get more interesting when you need to search across multiple tables. Imagine an e-commerce app where you want to search products by name, description, category, and tags. Joining four tables on every search query would be painfully slow.

The solution is a materialised view that precomputes the search vector:

CREATE MATERIALIZED VIEW product_search AS

SELECT p.id,
   p.name, 
   p.description, 
   c.name AS category_name,
   string_agg(t.name, ‘ ‘) AS tag_names,

setweight(to_tsvector(’english’, p.name), ‘A’) ||

setweight(to_tsvector(’english’, coalesce(p.description, ‘’)), ‘B’) ||

setweight(to_tsvector(’english’, coalesce(c.name, ‘’)), ‘C’) ||

setweight(to_tsvector(’english’, coalesce(string_agg(t.name, ‘ ‘), ‘’)), ‘D’)

AS search_vector

FROM products p

JOIN categories c ON p.category_id = c.id

LEFT JOIN product_tags pt ON p.id = pt.product_id

LEFT JOIN tags t ON pt.tag_id = t.id

GROUP BY p.id, p.name, p.description, c.name;

CREATE UNIQUE INDEX ON product_search (id);

CREATE INDEX ON product_search USING GIN (search_vector);

Now searches hit a single, pre-indexed table. Refresh it periodically with `REFRESH MATERIALIZED VIEW CONCURRENTLY` (the `CONCURRENTLY` keyword keeps the view available during refresh, so there’s no downtime).

The trade-off is that results can be slightly stale, usually by minutes. For most applications, however, that’s acceptable.

When Postgres FTS Is Enough (and When It Isn’t)

Here’s my rule of thumb: if search is a feature of your app, use Postgres. If search is your app, consider a dedicated engine.

Postgres FTS shines when:

• Your dataset is under a few million rows

• You want zero additional infrastructure

• ACID compliance matters (no stale search results from sync lag)

• Your team is small and can’t afford to maintain a separate search cluster

You might outgrow it when:

• Search relevancy is the core product experience (e-commerce, content discovery)

• You need built-in autocomplete suggestions, faceted navigation, or synonym handling

• Your dataset exceeds tens of millions of rows with sub-50ms latency requirements

It’s also worth mentioning the extension ecosystem. Projects like pg_textsearch (from Timescale) bring BM25 ranking (the same algorithm Elasticsearch uses) directly into Postgres, with 4x faster top-k queries and 41% smaller indexes. ParadeDB’s pg_search is another option, built on a Rust-based search engine, delivering 20x faster ranking than native tsvector.

The line between “need a dedicated engine” and “Postgres is fine” keeps moving in Postgres’s favour.

Conclusion

PostgreSQL full-text search is one of the most underutilised features in the database world. A generated tsvector column, a GIN index, and websearch_to_tsquery give you stemming, ranking, phrase search, and multi-language support with no extra infrastructure. Add pg_trgm for typo tolerance and materialised views for multi-table search, and you’ve covered what most applications need.

I have created a simple project that showcases how all this works. You can check it out on GitHub.