Advanced Full-Text Search in PostgreSQL

When building search functionality for your application, you'll likely start with PostgreSQL's ILIKE operator. While reliable and straightforward, it quickly becomes apparent that exact string matching falls short of user expectations. Consider this scenario: a user searches for "black dress" but your database contains "black beautiful dress." The ILIKE operator will miss this match entirely, leading to frustrated users and missed opportunities.

In this comprehensive guide, we'll explore advanced PostgreSQL search techniques that deliver the intelligent, flexible search experience your users expect.

The Limitations of Basic Pattern Matching

The ILIKE operator works well for exact substring matches:

SELECT * FROM products WHERE title ILIKE '%black dress%';

However, this approach fails when word order differs ("black beautiful dress" vs "beautiful black dress"), there are variations in spacing or punctuation, users employ synonyms or related terms, or you need to search across multiple columns with different priorities.

Introduction to Trigram Search

Trigram search addresses word order issues by analysing string similarity rather than exact matches. A trigram is a sequence of three consecutive characters, and PostgreSQL's pg_trgm extension uses these to calculate similarity scores between strings.

##### How Trigrams Work

Let's examine how trigrams compare "search" and "research":

• "search" generates: sea, ear, arc, rch
• "research" generates: res, ese, sea, ear, arc, rch

The overlap of four common trigrams (sea, ear, arc, rch) out of five total unique trigrams yields a high similarity score.

##### Setting Up Trigram Search

First, enable the extension:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Then implement similarity-based searching:

SELECT
    title,
    similarity(title, 'black dress') as score
FROM products
WHERE similarity(title, 'black dress') > 0.2
ORDER BY score DESC;

##### Trigram Search Limitations

While excellent for short strings like product titles, trigram search becomes less effective with longer content. The similarity score decreases as string length increases, making it unsuitable for searching product descriptions or lengthy text fields.

Full-Text Search with tsvector

PostgreSQL's tsvector data type is specifically designed for full-text search operations. It transforms text into a structured format optimized for search performance and relevance ranking.

##### Understanding tsvector

Consider this input text:

"a fat cat sat on a mat and ate a fat rat"

The tsvector representation becomes:

'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4

Notice how stop words ("a", "on", "and") are removed, words are normalized to their base form (lexemes), position information is preserved, and duplicate words show multiple positions.

##### Weighted Multi-Column Search

Real-world applications often need to search across multiple fields with different priorities. PostgreSQL's setweight function allows you to assign importance levels:

SELECT
    title,
    description,
    ts_rank_cd(
        setweight(to_tsvector('english', title), 'A') ||
        setweight(to_tsvector('english', description), 'B'),
        to_tsquery('english', 'black dress')
    ) as rank
FROM products
WHERE
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', description), 'B')
    @@ to_tsquery('english', 'black dress')
ORDER BY rank DESC;

###### Weight Classifications

• 'A': Highest priority (typically titles)
• 'B': High priority (typically descriptions)
• 'C': Medium priority
• 'D': Lowest priority

Implementation Best Practices

##### Managing NULL Values

Use COALESCE to handle NULL values gracefully:

setweight(to_tsvector('english', COALESCE(title, '')), 'A') || setweight(to_tsvector('english', COALESCE(description, '')), 'B')

##### Query Optimization

For production applications, consider:

1. Pre-computed tsvector columns: Store tsvector data in dedicated columns updated via triggers
2. GIN indexes: Create indexes on tsvector columns for faster search performance
3. Language-specific configurations: Use appropriate language dictionaries for better stemming and stop word handling

Performance Considerations

Create appropriate indexes for your search patterns:

-- For trigram similarity
CREATE INDEX idx_products_title_trigram ON products
USING gin (title gin_trgm_ops);

-- For full-text search
CREATE INDEX idx_products_search_vector ON products
USING gin (search_vector);

Conclusion

Advanced PostgreSQL search capabilities can transform your application's user experience. By understanding when to use trigram similarity versus full-text search, and how to combine both approaches effectively, you can build search functionality that rivals dedicated search engines for many use cases.

The key is matching the technique to the specific requirement: trigram search for flexible matching and suggestions, tsvector for comprehensive full-text search with relevance ranking. Together, they provide a robust foundation for intelligent search functionality that grows with your application's needs.