Full-Text Index

A full-text index is a specialized index built for searching words and phrases inside large text columns — supporting relevance ranking, stemming, and "contains this word" queries that a normal B-tree index can't do efficiently.

A regular index can efficiently answer WHERE title = 'exact value', but not "find rows where the description contains the word 'database' anywhere." A full-text index pre-processes text into searchable tokens (lexemes), so that kind of query becomes fast instead of requiring a full scan with LIKE '%word%'.

PostgreSQL implements this with tsvector/tsquery and a GIN index; SQL Server and Oracle both ship dedicated full-text index types with their own query syntax.