Full Text Search in PostgreSQL: What It Is and How It Works

Full text search in PostgreSQL is a feature that allows you to search natural language text efficiently by breaking it into searchable words and ranking results by relevance. It uses special data types and functions like tsvector and to_tsvector to index and query text content quickly.

How It Works

Full text search in PostgreSQL works by turning text into a special format called a tsvector, which is like a list of important words from the text. This process removes common words like "the" or "and" and reduces words to their root form, so searching is faster and smarter.

When you search, PostgreSQL converts your search phrase into a tsquery format and compares it against the stored tsvector. It then finds matches and ranks them by how relevant they are, similar to how a search engine works.

Think of it like making a summary of a book’s key words and then quickly checking if your search words appear in that summary instead of reading the whole book every time.

Example

This example shows how to create a table with a text column, add a tsvector column for full text search, and query it.

sql

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  body TEXT,
  document_with_weights tsvector
);

-- Update the tsvector column with weighted title and body
UPDATE articles SET document_with_weights =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B');

-- Create an index to speed up full text search
CREATE INDEX idx_fts ON articles USING GIN(document_with_weights);

-- Search for articles matching 'database'
SELECT id, title FROM articles
WHERE document_with_weights @@ to_tsquery('database')
ORDER BY ts_rank(document_with_weights, to_tsquery('database')) DESC;

Output

 id |       title       
----+-------------------
  1 | Introduction to PostgreSQL
  3 | Advanced Database Techniques

When to Use

Use full text search in PostgreSQL when you need to search large amounts of text quickly and with relevance ranking. It is perfect for applications like blogs, forums, document management systems, or any place where users search through articles, comments, or descriptions.

It is better than simple text matching because it understands word forms and ignores common words, making searches more accurate and faster.

Key Points

• Full text search uses tsvector and tsquery types to index and search text.
• It removes common words and reduces words to their root form for better matching.
• Indexes like GIN speed up search queries significantly.
• It ranks results by relevance, not just presence of words.
• Supports multiple languages with built-in dictionaries.

Key Takeaways

PostgreSQL full text search indexes text for fast, relevant searching using tsvector and tsquery.

It improves search accuracy by ignoring common words and using word roots.

GIN indexes make full text search queries efficient on large datasets.

Use it when you need to search and rank large text collections like articles or comments.

Full text search supports multiple languages and weighted search for better results.