What type of index should be used for searching in the middle or end of a string (LIKE '%text%')?

Searching for a pattern like '%text%' in the middle or end of a string with standard B-Tree indexes is inefficient because they cannot be used with a leading '%'. In such cases, the following are used:

• Full-Text Indexes — allow efficient searching of words or phrases within text.
• Trigram or N-gram indexes — break strings into fixed-length substrings and index them, speeding up substring searches.
• Extensions like pg_trgm in PostgreSQL — support indexing for LIKE '%text%'.

Example of creating a trigram index in PostgreSQL:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_trgm ON table_name USING gin (column_name gin_trgm_ops);

This enables efficient execution of queries with LIKE '%text%'.