How to Implement Full-Text Search in PostgreSQL

What is Full-Text Search in PostgreSQL?

Full-Text Search (FTS) in PostgreSQL is a built-in feature that enables efficient, sophisticated searching of text documents stored in your database. Unlike simple LIKE or ILIKE pattern matching, FTS understands language-specific stemming, stop words, and relevance ranking. It breaks text into searchable tokens, normalizes them, and allows you to query them with powerful boolean operators, phrase matching, and prefix matching — all backed by specialized indexes that make searches blazingly fast even on millions of documents.

Core Concepts: tsvector and tsquery

PostgreSQL FTS revolves around two primary data types:

• tsvector — A sorted list of lexemes (normalized words) extracted from the original text, each with a position and optional weight. Think of it as the indexed, searchable representation of a document.
• tsquery — A normalized query expression containing lexemes combined with boolean operators (& for AND, | for OR, ! for NOT) and phrase operators.

Here is a simple example that demonstrates the transformation:

SELECT to_tsvector('english', 'The quick brown foxes jumped over the lazy dogs.');
-- Output: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

SELECT to_tsquery('english', 'jumping & foxes');
-- Output: 'jump' & 'fox'

Notice how foxes became fox, jumped became jump, and dogs became dog — this is stemming (also called normalization). Stop words like the and over are removed entirely. The numbers after the colons represent the positions of each lexeme within the original text.

Why Full-Text Search Matters

Many applications need robust search functionality — blog platforms, e-commerce product catalogs, documentation systems, customer support ticket search, and more. While LIKE '%search_term%' works for trivial cases, it fails spectacularly at scale for several reasons:

• No linguistic intelligence — LIKE cannot match "running" to "run" or handle plural forms, conjugations, or misspellings.
• Terrible performance — Leading wildcards (%term) prevent index usage, forcing a full table scan that becomes unbearably slow as data grows.
• No relevance ranking — All rows match equally; there's no concept of one document being more relevant than another.
• Limited query expressiveness — No boolean logic, phrase matching, or proximity operators.

PostgreSQL's FTS solves all of these problems. It provides language-aware tokenization, supports GIN indexes for sub-millisecond searches, delivers built-in ranking functions, and offers a rich query language — all without leaving your database. This means no separate search infrastructure (like Elasticsearch or Solr) is required for many use cases, dramatically simplifying your architecture.

Getting Started: Your First Full-Text Search

Step 1: Creating a Table with Searchable Content

Let's create a sample articles table and populate it with some test data:

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

INSERT INTO articles (title, body) VALUES
('PostgreSQL Full-Text Search Guide', 
 'Full-text search in PostgreSQL is powerful and built right into the database. 
  You can search large volumes of text efficiently using tsvector and tsquery types.'),
('Getting Started with Docker', 
 'Docker containers provide isolated environments for running applications. 
  They are lightweight and portable across different systems.'),
('Advanced PostgreSQL Indexing', 
 'PostgreSQL supports many index types including B-tree, GIN, GiST, and BRIN. 
  Full-text search relies heavily on GIN indexes for fast token lookup.'),
('Introduction to Python Programming', 
 'Python is a versatile programming language used for web development, 
  data science, and automation tasks. It is known for its readability.');

Step 2: Performing Ad-Hoc Searches

You can perform full-text searches without any index by casting text to tsvector and comparing against a tsquery:

-- Simple search: find articles containing 'search' or 'database'
SELECT id, title, body
FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'search | database');

The @@ operator returns true if the tsquery matches the tsvector. This query finds articles that contain either "search" or "database" (after normalization). The || operator concatenates the title and body into a single text string before conversion.

-- Phrase search: find articles with the exact phrase "full text"
SELECT id, title
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'full <-> text');

The <-> operator means "immediately follows," enforcing that "full" must appear directly before "text" in the original document.

Step 3: Adding Relevance Ranking

Ranking transforms your search from a simple yes/no filter into a sorted list of results where the most relevant documents appear first:

SELECT id, title, ts_rank(
    to_tsvector('english', title || ' ' || body),
    to_tsquery('english', 'postgresql | database | search')
) AS rank
FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgresql | database | search')
ORDER BY rank DESC;

ts_rank computes a relevance score based on the frequency and density of matching lexemes. More matches and rarer words contribute to higher scores. The result might look like:

 id |              title               |    rank
----+-----------------------------------+-------------
  3 | Advanced PostgreSQL Indexing      | 0.075990885
  1 | PostgreSQL Full-Text Search Guide | 0.06079271
(2 rows)

Step 4: Highlighting Search Results

To show users where their search terms appear in the text, use ts_headline:

SELECT id, title,
       ts_headline('english', body, to_tsquery('english', 'postgresql | search'),
                   'StartSel=, StopSel=, MaxWords=30, MinWords=10') AS highlighted_body
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'postgresql | search');

This returns an HTML-ready excerpt with matching words wrapped in <mark> tags:

 id |              title              |                 highlighted_body
----+---------------------------------+--------------------------------------------------
  1 | PostgreSQL Full-Text Search Guide | Full-text search in PostgreSQL is powerful...
  3 | Advanced PostgreSQL Indexing     | PostgreSQL supports many index types including B-tree...

Building for Performance: GIN Indexes

The examples above compute tsvector on the fly, which forces PostgreSQL to process every row at query time — slow for large tables. The solution is to store the tsvector in a column and index it with a GIN (Generalized Inverted Index):

Adding a Dedicated tsvector Column

ALTER TABLE articles ADD COLUMN search_vector tsvector;

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

Here setweight assigns importance labels ('A' is highest, 'D' lowest). Title words get weight A, body words get weight B. These weights influence ranking — matches in titles will score higher than matches in body text.

Creating the GIN Index

CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);

Now queries against this index are extremely fast:

SELECT id, title, ts_rank(search_vector, to_tsquery('english', 'postgresql')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql')
ORDER BY rank DESC;

GIN indexes support all FTS operators efficiently, including @@, @> (contains), and <@ (contained by). For a table with millions of rows, this query completes in milliseconds rather than seconds or minutes.

Automatic Updates with Triggers

Manually updating the search_vector column whenever a row changes is error-prone. Instead, use a trigger:

CREATE OR REPLACE FUNCTION articles_search_vector_update() 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_trigger
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW
    EXECUTE FUNCTION articles_search_vector_update();

Now every INSERT or UPDATE automatically recalculates the search_vector. If you have existing data, run a one-time update first, then the trigger handles everything going forward.

Advanced Full-Text Search Techniques

Prefix Matching (Wildcard Searches)

To implement search-as-you-type or prefix matching, use the :* suffix operator:

-- Find articles containing words starting with "postgr"
SELECT id, title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgr:*');

This matches "postgres", "postgresql", "postgraduate" — any lexeme that begins with "postgr" after normalization. Note that prefix matching only works at the end of a query term, not the beginning.

Boolean Query Expressions

Combine multiple conditions with boolean operators:

-- AND: articles containing both 'postgresql' AND 'index'
SELECT id, title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & index');

-- OR: articles containing 'postgresql' OR 'python'
SELECT id, title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql | python');

-- NOT: articles containing 'database' but NOT 'postgresql'
SELECT id, title FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & !postgresql');

Phrase and Proximity Queries

The <->> operator (FOLLOWED BY) enforces adjacency:

-- Exact phrase: "full text search"
SELECT id, title FROM articles
WHERE search_vector @@ to_tsquery('english', 'full <-> text <-> search');

-- Words within N positions: "postgresql" within 3 words of "database"
SELECT id, title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql <3> database');

The number inside the operator specifies the maximum distance (number of intervening lexemes) allowed between the two terms.

Working with Multiple Languages

PostgreSQL ships with built-in dictionaries for many languages. You can specify the language at query time:

-- Spanish text search
SELECT to_tsvector('spanish', 'Los gatos corrieron rápidamente por el jardín.');
-- Output lexemes are Spanish-stemmed: 'gato', 'corr', 'rapid', 'jardin'

-- German text search with compound word handling
SELECT to_tsvector('german', 'Die Fahrzeugversicherung ist wichtig.');
-- German dictionary handles compounds: 'fahrzeug', 'versicherung'

For multi-language content, you might store separate tsvector columns per language or use a single column with a custom configuration that chains multiple dictionaries.

Custom Dictionary Configurations

You can fine-tune the text search configuration to add custom stop words, thesaurus files, or specialized dictionaries. Here's how to create a custom configuration based on the English configuration but with extra stop words:

-- Create a custom dictionary
CREATE TEXT SEARCH DICTIONARY my_english_dict (
    TEMPLATE = snowball,
    Language = 'english',
    StopWords = 'my_english_stop'
);

-- Create the stop words file (on the server filesystem)
-- In the sharedir/tsearch_data directory, create my_english_stop.stop:
-- Contents: additional stop words, one per line

-- Create a custom configuration
CREATE TEXT SEARCH CONFIGURATION my_english (COPY = english);

-- Map the custom dictionary to word types
ALTER TEXT SEARCH CONFIGURATION my_english
    ALTER MAPPING FOR word, asciiword
    WITH my_english_dict;

Comparing Strategies: tsvector Column vs. Expression Index

You have two primary approaches for indexing full-text searches:

1. Stored tsvector column (recommended for most cases):

-- Column + trigger + GIN index as shown above
ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE INDEX ON articles USING GIN(search_vector);

• Pros: Clean, explicit; easy to update weights; can include multiple fields with different weights; index is straightforward.
• Cons: Requires a trigger for maintenance; uses extra storage for the column.

2. Expression index (simpler alternative):

CREATE INDEX articles_expr_idx ON articles USING GIN(
    to_tsvector('english', title || ' ' || body)
);

• Pros: No extra column or trigger needed; always up-to-date.
• Cons: Cannot assign different weights to different fields; query must match the expression exactly for the index to be used.

Choose the stored column approach when you need fine-grained weight control or when the text comes from multiple columns with different importance levels. Choose the expression index for simplicity when weights don't matter.

Best Practices for PostgreSQL Full-Text Search

• Always use a GIN index. Without it, full-text searches degrade to full table scans. The performance difference on tables with 100k+ rows is dramatic — often 100x to 1000x faster.
• Assign weights strategically. Use setweight to give higher importance to title fields (weight 'A') and lower importance to body or footnote text (weight 'C' or 'D'). This makes ranking results feel natural to users.
• Use triggers for automatic maintenance. Don't rely on application code to update search_vector columns. A BEFORE INSERT OR UPDATE trigger guarantees consistency regardless of how data enters the database.
• Choose the right dictionary. Always specify the language explicitly — to_tsvector('english', ...) rather than relying on the default, which might be set to 'simple' (no stemming). The 'simple' dictionary performs no normalization, defeating much of FTS's value.
• Combine FTS with traditional filters. You can mix full-text search conditions with regular WHERE clauses. PostgreSQL's query planner will combine GIN index scans with B-tree index scans efficiently:

SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql')
  AND created_at > '2024-01-01'
ORDER BY rank DESC;

• Test your queries with EXPLAIN ANALYZE. Verify that the GIN index is actually being used. If you see "Seq Scan" instead of "Bitmap Index Scan" or "Index Scan," your query expression doesn't match the index expression.
• Consider covering indexes for ranking. For extremely high-volume searches where ranking is the bottleneck, you can include the tsvector in an index with weights pre-computed, though this is rarely necessary until you reach millions of documents.
• Use ts_headline sparingly. Generating headlines is CPU-intensive because it must access the original text. For high-traffic applications, generate headlines only for the top N results displayed to the user, not for all matching rows.
• Handle NULLs explicitly. When concatenating fields for tsvector generation, always wrap them in coalesce(..., ''). A NULL concatenated with any string yields NULL, which will silently produce an empty tsvector.
• Keep security in mind. When constructing tsquery values from user input, use plainto_tsquery() or phraseto_tsquery() instead of to_tsquery() to avoid syntax errors or potential injection of unintended operators. plainto_tsquery treats the entire input as a simple phrase with AND logic, which is safer for user-facing search boxes.

-- Safer for user input: plainto_tsquery ignores special characters
SELECT plainto_tsquery('english', 'find this text exactly');
-- Output: 'find' & 'text' & 'exact'

-- Versus to_tsquery which interprets punctuation as operators
SELECT to_tsquery('english', 'find & this | text');
-- Output: 'find' & 'text' | 'text'  (potentially confusing for raw user input)

Debugging and Troubleshooting

Inspecting Tokenization

To understand exactly how PostgreSQL tokenizes your text, use ts_debug:

SELECT * FROM ts_debug('english', 'The PostgreSQL database server version 16.3 is amazing!');

This returns a row for each token showing the token type, which dictionary recognized it, and the resulting lexeme. It's invaluable for understanding why certain words match or don't match your queries.

Checking Index Usage

EXPLAIN ANALYZE
SELECT id, title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql');

Look for Bitmap Index Scan on articles_search_idx in the output. If you see a sequential scan instead, verify that your query's tsvector expression exactly matches the expression used in the index definition.

Conclusion

PostgreSQL's full-text search is a remarkably capable, self-contained search solution that eliminates the need for external search infrastructure in a wide range of applications. By understanding tsvector and tsquery types, leveraging GIN indexes, applying proper weight assignments, and following the best practices outlined in this tutorial, you can deliver fast, linguistically intelligent search that scales to millions of documents — all within the database you already know and trust. Start with the stored column and trigger pattern for robustness, use plainto_tsquery for user-facing search, and always verify index usage with EXPLAIN ANALYZE. With these techniques, your PostgreSQL-powered search will be both powerful and maintainable.