Full-text search with tsvector and tsquery — SQL & Relational Databases

Full-text search (FTS) in PostgreSQL enables natural language searching of text columns — finding documents that contain specified words regardless of their exact form, position, or surrounding words. Unlike LIKE '%keyword%' (exact substring matching, no index support for leading wildcards), FTS applies linguistic preprocessing: stemming reduces inflected word forms to their root (bowling, bowled, bowler all become the stem 'bowl'), stop words are removed (the, a, is, of are discarded as too common to be meaningful), and remaining terms are stored as a sorted lexeme list. A search for 'bowling' automatically matches 'bowled' and 'bowler' through stemming.

PostgreSQL's FTS revolves around two data types. tsvector is a sorted list of lexemes with their position numbers in the original text — the indexed document representation. tsquery is a boolean search expression combining lexemes with operators (& for AND, | for OR, ! for NOT, <-> for phrase/sequence). The @@ operator checks whether a tsvector matches a tsquery. A GIN index on a stored tsvector column makes @@ queries fast on millions of documents, enabling millisecond-scale search over large text collections.

For data engineers, FTS is correct for: player name and profile search interfaces, match commentary and article search, venue and product description search in retail pipelines, and log message analysis. PostgreSQL FTS eliminates the need for a separate search service (Elasticsearch, Solr) for moderate-scale requirements — keeping the architecture simpler and the search results in the same transactional database as the application data. For very large document collections (hundreds of millions of documents) or requirements for relevance ranking algorithms beyond PostgreSQL's ts_rank, a dedicated search engine may still be appropriate.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.