In this lesson, you will learn how to manipulate tsvector and tsquery structures to move beyond simple keyword searches and achieve sophisticated, performant full-text retrieval.
The Anatomy of Search Data
In PostgreSQL, full-text search is optimized by splitting text into two distinct roles. The tsvector is an optimized, sorted list of distinct lexemes (normalized tokens), while the tsquery represents the search criteria, including logical operators like AND (&), OR (|), and NOT (!).
Because generating these structures on the fly during a query is expensive, we manage them as persistent data structures. Instead of constantly casting text to tsvector, we store them in a column or compute them via functional indexes.
Manipulating TSVector and TSQuery
You can manually construct or modify these structures to refine your search logic. For instance, you might want to combine vectors from multiple columns or weigh specific terms differently.
-- Creating a tsvector from text with explicit weights
SELECT setweight(to_tsvector('english', 'PostgreSQL internals are fascinating'), 'A') ||
setweight(to_tsvector('english', 'database management'), 'B');
-- Constructing a complex tsquery programmatically
SELECT to_tsquery('english', 'PostgreSQL & (internals | performance)');
The power lies in the || operator, which allows you to concatenate vectors, and the setweight function, which tells the search engine which parts of your document are more important (A, B, C, or D). When you use ts_rank to calculate relevance, the engine uses these weights to prioritize matches in "A-weighted" areas (like a title) over "D-weighted" areas (like a footer).
Best Practices for Management
When managing these structures, always ensure your tsquery is normalized to the same dictionary/language as your tsvector. If you store a tsvector in a column, you can create a GIN (Generalized Inverted Index) on it. This turns a slow, sequential scan into a lightning-fast index lookup, as the database only traverses the pre-computed tree of lexemes.
Key Takeaway: By pre-computing tsvector data and utilizing setweight to assign document importance, you transform raw text into a structured, indexable format that enables high-performance, ranked search results.