Як знаходити схожі рядки й опечатки за допомогою pg_trgm?

pg_trgm розбиває рядок на триграми - послідовності з трьох символів: «laravel» → l, la, lar, ara, rav, ave, vel, el . Схожість двох рядків - частка спільних триграм.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT similarity('laravel', 'laravle');     -- ~0.45
SELECT 'laravle' % 'laravel';                -- true, якщо схожість вища за поріг (0.3)
SELECT word_similarity('ларав', 'Laravel Україна');

Що вміє:

• Пошук з опечатками: знайти «Хмельницький» за запитом «Хмельницкий».
• LIKE '%підрядок%' та ILIKE з індексом - головна практична причина ставити розширення.
• Найближчі за схожістю: ORDER BY name <-> 'запит' LIMIT 10 з GiST-індексом.

CREATE INDEX companies_name_trgm ON companies USING gin (name gin_trgm_ops);

SELECT * FROM companies WHERE name ILIKE '%софт%';       -- тепер з індексом
SELECT * FROM companies WHERE name % 'Епам' ORDER BY similarity(name, 'Епам') DESC;

GIN проти GiST для триграм: GIN швидший на пошук і LIKE, GiST підтримує сортування за відстанню (<->) для «найближчих».

Обмеження:

• Короткі запити (1-2 символи) майже не мають триграм - індекс не допомагає, а результатів забагато.
• Розмір індексу великий - кілька триграм на кожен символ тексту. Для довгих текстів (статті) підходить погано; для назв, імен, адрес - добре.
• Поріг схожості (pg_trgm.similarity_threshold) треба підбирати під дані: занизький дає сміття, зависокий пропускає опечатки.
• Регістр і локаль: порівняння без урахування регістру залежить від LC_CTYPE бази - з локаллю C кирилиця не зводиться до малих літер.

Типове поєднання: повнотекстовий пошук для документів + pg_trgm для назв, автодоповнення й підказок «можливо, ви мали на увазі».

Докладніше в документації: pg_trgm