How does full-text search work in a relational database, and when do you move to Elasticsearch?

Answer preview

Full-text search converts documents into normalised tokens (a tsvector) and queries them with a tsquery, backed by a GIN inverted index. It gives you stemming, stop-word removal, and ranking — things LIKE '%word%' can never do, because that predicate is non-sargable and scans the…