Full-Text Search Basics: Mastering tsvector and tsquery

Previously in this course, we covered working with patterns and LIKE to perform basic text filtering. While LIKE is useful for simple prefix or suffix matches, it fails to handle natural language, stemming, or relevance ranking. In this lesson, we level up to PostgreSQL's native full-text search capabilities, allowing your store application to perform complex, linguistic-aware searches on product descriptions.

Understanding Full-Text Search from First Principles

At its core, full-text search (FTS) is about moving from "does this string contain these characters?" to "does this document contain these concepts?"

Standard LIKE queries perform a sequential scan, comparing strings character by character. This is inefficient for large datasets and linguistically naive (e.g., it treats "running" and "run" as completely different words). PostgreSQL solves this with two primary data types:

1. tsvector: A pre-processed document representation. It strips out common "stop words" (like "the", "a", "is") and reduces words to their "lexemes" (their root form, so "running" becomes "run").
2. tsquery: The processed search query. It understands operators like AND (&), OR (|), and NOT (!).

Implementing Full-Text Search

To search our products table effectively, we need to transform the description column into a tsvector and match it against a tsquery.

1. The tsvector Transformation

The to_tsvector() function performs the heavy lifting. It takes a configuration (usually english) and the text to be processed.

SQL
-- View how PostgreSQL indexes the word "Running shoes for runners"
SELECT to_tsvector('english', 'Running shoes for runners');

-- Result: 'run':1,4 'shoe':2

Notice how "running" and "runners" are both normalized to the lexeme 'run', and the stop word "for" is discarded.

2. The tsquery Logic

Similarly, to_tsquery() handles our search input. It requires specific operator syntax to be valid.

SQL
-- Search for products containing "run" AND "shoe"
SELECT to_tsquery('english', 'run & shoe');

3. Putting it Together in a Query

To find products in our store, we use the @@ operator, which checks if a tsvector matches a tsquery.

SQL
SELECT product_name, description
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'running & shoe');

Worked Example: Enhancing Product Searches

Let's assume we want to search our products table for a "waterproof jacket". Using a standard LIKE query would require multiple wildcards and still might miss variations like "jackets". With FTS, we get better results.

SQL
-- Find products that mention waterproof and jacket
SELECT product_id, product_name
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'waterproof & jacket');

Hands-on Exercise

1. Identify a text column in your current store project (e.g., product_description).
2. Run a SELECT query using to_tsvector and to_tsquery to find all products matching the term "durable" combined with "outdoor".
3. Observe how the query handles different word endings if you insert a row containing the word "durability".

Common Pitfalls

• Performance overhead: Converting text to a tsvector on every query is slow. For production, you should create a GIN index on the tsvector expression so PostgreSQL doesn't have to re-process every row.
• Stop words: If you search for "the jacket", the tsquery might fail because "the" is a stop word and is ignored during the vectorization process. Always sanitize user input before passing it to to_tsquery.
• Case sensitivity: to_tsvector is generally case-insensitive regarding the lexemes it produces, but it is best practice to keep your inputs clean.

Frequently Asked Questions

Q: Can I use this for non-English languages? Yes, PostgreSQL supports many languages via the regconfig argument in to_tsvector. You can see the list using SELECT * FROM pg_catalog.pg_ts_config;.

Q: Is this the same as MySQL's full-text search? While both serve the same goal, the implementation differs significantly in indexing strategies and configuration options. See MySQL Full-Text Search vs PostgreSQL tsvector: Which to Choose? for a deep dive.

Q: How do I make this fast for thousands of products? By adding a GIN (Generalized Inverted Index): CREATE INDEX idx_fts_description ON products USING GIN(to_tsvector('english', description));

Recap

We've moved beyond simple string matching. By utilizing tsvector for pre-processed content and tsquery for structured search logic, you can provide professional-grade search functionality for your store application. This is a critical step in conducting the full-scale enterprise audit of your database performance.

Up next: We will discuss Database Maintenance Tasks, focusing on how to keep your indexes healthy and your table statistics accurate as your data grows.