Use Cases for Trigram Indexes (When not to use Full Text Search)

The blog post by Michael Goddard explores the use of trigram indexes in CockroachDB 22.2 for improving text search capabilities, particularly in scenarios where users may input misspelled or variably formatted text. It highlights the limitations of full-text search methods for certain use cases, such as handling typos, transliterations, and spelling variations, using the example of a restaurant's name that was misspelled due to a hyphen. Trigram indexes are described as a method to efficiently perform fuzzy matching by creating a sliding window of three-character sequences, which helps in matching text despite minor discrepancies. The post provides a detailed tutorial on implementing trigram indexes using SQL queries in CockroachDB, demonstrating how they enhance search performance by reducing the need for full-table scans. The author emphasizes the utility of trigram indexes for short text fields and illustrates their efficiency through a practical example, while also mentioning the new feature of index visibility in CockroachDB for performance tuning.