Full-Text Search and Extensions
PostgreSQL full-text search converts documents into searchable lexemes and supports language-aware matching and ranking. to_tsvector prepares document text, while plainto_tsquery or websearch_to_tsquery builds a query from user text. A GIN index on a text-search vector can speed up searches.
Extensions add capabilities to PostgreSQL. CREATE EXTENSION enables an installed extension within a database, subject to permissions and server availability. PostGIS is a popular geospatial extension, and pg_trgm supports trigram-based similarity and certain pattern searches. Extensions should be evaluated for compatibility, security, and managed-service support.
Full-text search query
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
@@ websearch_to_tsquery('english', 'postgresql indexing');💡 The text is converted to a searchable vector and compared with a query vector. For larger tables, store or index the search vector rather than recomputing it for every row.
Key Takeaways
- Full-text search supports lexeme-based matching and ranking.
- GIN indexes can accelerate text-search queries.
- Extensions add specialized capabilities but must be supported by the server.
Knowledge Check
Which operator matches a tsvector against a tsquery?