MySQL проти PostgreSQL: повнотекстовий пошук — коли вбудованого достатньо, а коли це пастка

Лінія рішення: коли вбудованого пошуку достатньо

Чесно кажучи: вбудований повнотекстовий пошук достатній, коли ваш пошук — вбудована функція, а не продукт. Достатній, якщо ви можете описати вимоги в абзаці, і в цьому абзаці немає слів «мабуть ви мали на увазі», «синоніми», «нечіткий пошук», «персоналізований ранжир», «автодоповнення» або «аналітика майже в реальному часі».

Вбудованого достатньо, якщо…

• Обсяг даних помірний і зона пошуку обмежена. Наприклад: пошук заявок у допоміжній системі, статей бази знань, назв продуктів, внутрішніх документів.
• Потреби по затримці — «швидко для додатку», а не «швидко як пошуковий рушій в умовах хаосу». Якщо P95 може бути 200–500 мс і ніхто не фільтрує на кожен введений символ, часто можна залишатися в базі даних.
• Ранжування може бути «достатньо хорошим», і ви готові налаштовувати його шляхом зміни ваг і стоп‑слів — не тренуванням моделей.
• Послідовність важливіша за складність релевантності. Пошук у БД транзакційно послідовний, якщо ви так його спроєктуєте; зовнішні рушії додають лаг індексації і ще одну зону відмов.
• Ви готові миритися з обмеженнями, як‑от особливості токенізації, вибір стеммінгу для мов та поодинокі питання «чому це не співпадає з точною фразою?».

Вбудований пошук стає пасткою, коли…

• Ви створюєте продукт пошуку. Якщо релевантність — ваш диференціатор, не чекайте, що загального DB‑рушія вистачить назавжди.
• Ваші запити поєднують FTS з фільтрами високої кардинальності та сортуванням і ви очікуєте лінійного масштабування. Такого не буде. Воно масштабується як комітет.
• Мультиорендний пошук означає «пошук по всіх орендарях» і дизайн індексу явно не для цього. Ви заплатите CPU, пам’яттю та неприємним блокуванням.
• «Просто додати індекс» стає вашою операційною моделлю. Повнотекстові індекси — не звичайні B‑tree. Ви можете їх роздути, змусити до частих перезаписів і перетворити обслуговування на роботу на умовний неповний робочий день.
• Вам потрібні просунуті функції: нечітке зіставлення, синоніми на рівні орендаря, складне ранжування, аналізатори на поле, підсвічування, «подібні документи», підказки запитів, толерантність до помилок або гібрид лексичного та векторного пошуку.

Якщо ви вагаєтесь: почніть з вбудованого пошуку, щоб перевірити поведінку та потреби користувачів, але спроєктуйте потік даних так, щоб пізніше можна було дзеркалити документи в спеціалізований рушій без переписування всього застосунку. Іншими словами: не робіть базу даних своїм пошуковим кластером і не дивуйтесь, коли вона почне поводитися як такий.

Як насправді працює (і ламається) повнотекстовий пошук MySQL

Повнотекстовий пошук MySQL оманливо простий: додайте FULLTEXT‑індекс, використайте MATCH() AGAINST(), запустіть. Під капотом важливий ваш механізм збереження (сьогодні InnoDB, колись MyISAM), правила токенізації мають значення, а «релевантність» формуватиметься за замовчуваннями, про які ви могли й не знати, що погодились.

InnoDB FULLTEXT: що ви насправді отримуєте

InnoDB реалізує FULLTEXT, підтримуючи допоміжні таблиці для інвертованого індексу. Це інтегровано, транзакційно достатньо для більшості застосунків, але має власний профіль використання ресурсів: фонова підтримка індексу, потенційно велике IO під час великих оновлень та чутливість до стоп‑слів і мінімального розміру токена.

Натуральний режим vs булевий: дві різні істоти

Натуральний режим — це «просто шукати цей текст» і повернути оцінку релевантності. Булевий режим додає оператори й дає змогу змусити включення/виключення та префіксне матчинг. У продакшені булевий режим стає популярним, бо користувачі хочуть «обов’язково містить X», але булевий режим також полегшує випадкове написання запитів, що повертають або надто багато, або надто мало результатів, або сканують більше, ніж ви думаєте.

Режими відмов, які ви побачите в MySQL

• «Чому пошук ‘to be’ нічого не повертає?» Стоп‑слова і мінімальна довжина слова. Система «працює як задумано», що рідко заспокоює.
• Релевантність виглядає випадковою, бо модель оцінювання груба і сильно залежить від частоти терміну та довжини документа, що може не відповідати вашому домену.
• Витрати на побудову/оновлення індексу дивують. Масові оновлення або часті редагування великих текстових полів можуть створити write amplification і реплікаційний лаг.
• Проблеми з кодуванням і калацією впливають на токенізацію й порівняння. Ви можете отримати «одно виглядає, але різні токени» при неконсистентній нормалізації Unicode.
• Плани виконання погіршуються, коли ви комбінуєте повнотекстові предикати з іншими фільтрами й сортуванням. MySQL може вибрати неоптимальний план або змусити тимчасові таблиці.

Одна операційна реальність: коли MySQL FULLTEXT йде вбік, симптоми виглядають як «база даних повільна». Пошук стає галасливим сусідом, що краде CPU у решти застосунку. І оскільки він всередині БД, його важче ізолювати без обмеження швидкості функції або виносу її з бази.

Як насправді працює (і ламається) повнотекстовий пошук PostgreSQL

Повнотекстовий пошук PostgreSQL (FTS) — це набір інструментів. Ви будуєте документи tsvector, шукаєте їх за допомогою tsquery, індексуєте GIN (зазвичай) або GiST (іноді), і ранжируєте результати за допомогою ts_rank або схожих функцій. Це більш явний підхід, ніж у MySQL. Добре, бо можна налаштовувати; погано, бо можна налаштувати неправильно.

tsvector, tsquery і чому нормалізація не опціональна

Postgres не «шукає сирий текст» так само, як MySQL. Він парсить текст у лексеми (нормалізовані токени), зазвичай застосовуючи стеммінг згідно з конфігурацією текстового пошуку (наприклад english). Це означає, що ви мусите вирішити, яку конфігурацію мови використовуєте і чи потрібен стеммінг. Це чудово для «running» → «run». Це погано, коли коди продуктів або юридичні посилання спотворюються.

Вибір індексу: GIN швидкий для читання, але не безкоштовний у підтримці

GIN‑індекси — робоча конячка для FTS, бо вони добре підходять під інвертований індекс. Читання швидке. Записи можуть бути дорогими. Якщо ви часто оновлюєте документи, списки очікування GIN і поведінка vacuum стануть вашою новою хобі‑темою. (Ніхто не вибирає це хобі навмисно.)

Режими відмов, які ви побачите в PostgreSQL

• Роздування GIN‑індексу і уповільнення, якщо великий churn і недостатній vacuum, або якщо індексуєте великі документи без розуміння.
• Суперечності в ранжуванні, бо функції ранжування за замовчуванням не завжди відповідатимуть уявленням продуктового менеджера. Потрібні ваги, нормалізація і іноді окремі поля.
• Невідповідність конфігурації мови призводить до «чому ‘analysis’ не збігається з ‘analyses’?» або «чому пошук за артикулом повертає нісенітницю?»
• Неправильне побудування запитів (використання to_tsquery напряму на введенні користувача) перетворює пунктуацію на синтаксичні помилки і робить запити причиною інцидентів.
• Комбінування FTS з фільтрацією та сортуванням може викликати великі bitmap scans і тиск на пам’ять, якщо ви не структуруєте запит і індекси обдумано.

Postgres винагороджує явність: окремі вектори на поле, зважене ранжування, згенеровані стовпці, часткові індекси на орендаря і чисте побудування запитів через plainto_tsquery або websearch_to_tsquery. Він також карає за імпровізацію у продакшені.

Цікаві факти та історія, які можна використати

1. MyISAM першим отримав повнотекстовий пошук, задовго до того, як InnoDB його підтримав; багато «думок про FTS у MySQL» — релікти епохи MyISAM.
2. InnoDB FULLTEXT зберігає індекс у допоміжних таблицях, через що великі перебудови й масивні оновлення виглядають як таємничі внутрішні IO‑штормі.
3. tsearch у PostgreSQL передував сучасній тенденції «пошук скрізь»; він був частиною Postgres багато релізів, еволюціонуючи з ранніх contrib‑модулів у ядро.
4. GIN‑індекси спроєктовані для складних значень (масиви, JSON‑подібні структури, набори лексем). Повнотекстовий пошук — один із найкращих варіантів їхнього застосування.
5. FTS у Postgres має кілька словників і конфігурацій (стеммінг, стоп‑слова, проста парсинг). Така гнучкість дозволяє адаптуватися під різні мови — якщо ви її справді налаштуєте.
6. У MySQL є правила мінімального розміру токена, що історично викликало пропуски коротких слів; про це згадують, коли CEO шукає дволітерну лінійку продуктів.
7. Обидві системи роблять компроміси щодо стоп‑слів: їхнє видалення зменшує розмір індексу та шум, але може знищити релевантність у доменах, де поширені слова важливі (юридичні тексти, заголовки, пісенні тексти).
8. Релевантність FTS — не універсальна істина. MySQL і Postgres оцінюють по‑різному через різні моделі; міграція рушія змінює результати, навіть якщо дані ідентичні.
9. Реплікація підсилює біль: FTS‑навантаження може збільшити обсяг binlog/WAL і посилити відставання під час масових реіндексів або оновлень.

Одна перефразована ідея варта липкої нотатки, приписана відомому інженеру з надійності: paraphrased idea — Werner Vogels (про побудову систем із врахуванням відмов і про проєктування для того, що ламається). Трактуйте пошук як окрему зону відмов, навіть коли він живе в базі.

Жарт #1: Повнотекстовий пошук як офісна кава: коли він хороший — ніхто не згадує, коли поганий — усі одночасно пишуть тікети.

Три корпоративні міні‑історії з передової

Міні‑історія 1: Інцидент через хибне припущення

Середня SaaS‑компанія додала «пошук по всіх нотатках клієнта» в адмінпанель. Відправили швидко: одна таблиця MySQL, один FULLTEXT‑індекс, новий endpoint, що виконував MATCH(notes) AGAINST(?). На стаджингу працювало. У продакшені теж працювало — поки не перестало.

Підтримка повідомила «пошук не працює» під час робочого дня, але весь інший застосунок теж відчувався повільним. На виклику побачили CPU на максимумі і черги запитів. Команда припустила, що повнотекстовий індекс робить пошуки «фактично O(1)». Хибне припущення було тоншим: вважали, що додавання повнотекстового предикату завжди рано і дешево звужує результати.

Насправді запит також включав фільтри по орендарю, діапазон дат і сортування за «остання зміна». Оптимізатор обрав план, що робив набагато більше роботи, а сортування за датою вимагало тимчасових таблиць для великих наборів кандидатів. Для деяких пошукових термінів набір кандидатів був масивним.

Інцидент завершився обмеженням швидкості endpoint, зміною UI із вимогою принаймні 3 не‑стоп‑символів, та редизайном: вони розділили «пошук» і «сортування», спочатку фільтрували за орендарем і недавньою активністю, а потім застосовували повнотекстове співпадіння. Пізніше додали окремий сервіс пошуку. Урок не в тому, що MySQL FTS поганий. Урок: оптимізатор — не ваш продуктовий менеджер, а «індексовано» не означає «дешево».

Міні‑історія 2: Оптимізація, що зіграла зі зворотнім ефектом

Команда маркетплейсу на PostgreSQL хотіла пришвидшити пошук оголошень, створивши один великий tsvector, куди конкатенували title, description і seller notes. Ваги виставили однакові. Зберегли як згенерований стовпець і проіндексували GIN. Запити стали швидшими і команда оголосила перемогу.

Потім записи уповільнилися. Не «трохи». Почалися періодичні сплески WAL і відставання autovacuum. У застосунку була функція, що дозволяла продавцям часто змінювати описи, часто пачками (уявіть сезонні оновлення). Кожен правка кришила GIN‑індекс. Список очікування GIN зростав, vacuum не встигав, і затримки запитів стали нестабільними. «Оптимізація» перемістила витрати з читання на запис, а їхній трафік був write‑heavy.

Вони налаштовували autovacuum, що допомогло, але знову нашкодило, коли змагалося з нормальним навантаженням і спричиняло IO‑конфлікт. Зрештою розбили вектор на менші поля, зменшили індексований контент (seller notes перестали індексуватись) і винесли «notes search» в асинхронний індекс, оновлюваний поза основним шляхом. Релевантність теж покращилася, бо ваги стали значущими замість «все однаково важливо».

Урок: найбільший вектор рідко буває найкращим. Індексуйте тільки те, що потрібно, і пам’ятайте: GIN — прискорювач запитів, а не безкоштовний десерт.

Міні‑історія 3: Нудна, але правильна практика, що врятувала ситуацію

Ентерпрайз‑застосунок запускав Postgres FTS для внутрішньої документації різних відділів. Без нюансів: tsvector на документ, GIN‑індекс і кілька фільтрів. Що було вишуканим — їхня операційна дисципліна: у моніторингу був канарковий запит пошуку, що виконувався щохвилини з відомим набором термінів.

Одної вівторка вони побачили, як латентність канарки повільно зростає протягом години. Ще не інцидент, але тренд. На виклику перевірили статистику vacuum і виявили, що autovacuum відстає для таблиці документів. Також помітили зростання dead tuples через нову фічу, що частіше оновлювала документи.

Оскільки у них були базові показники, вони відреагували до того, як користувачі помітили: налаштували пороги autovacuum для цієї таблиці, запланували цілеспрямований VACUUM (ANALYZE) у тихий період і тимчасово обмежили пакетну задачу оновлення документів. Жодних розборів інцидентів, жодних сердитих внутрішніх листів — просто тихе повернення до норми.

Це та нудна практика: один синтетичний запит, відслідковуваний у часі, прив’язаний до конкретної підсистеми. Це не гламурно. Це дешевше за героїчні вчинки.

Жарт #2: Найшвидший спосіб покращити очікування від пошуку — перейменувати функцію на «фільтр за ключовими словами» і спостерігати, як очікування падуть до реальності.

Практичні завдання: команди, вивід, значення, рішення

Це реальні завдання, які можна виконати під час rollout, інциденту або розбору після нього. Кожне містить команду, приклад виводу, що це означає, і яке рішення прийняти.

Завдання 1 (MySQL): Підтвердити наявність FULLTEXT‑індексів і що вони покривають

cr0x@server:~$ mysql -e "SHOW INDEX FROM articles WHERE Index_type='FULLTEXT'\G"
*************************** 1. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: ft_title_body
 Seq_in_index: 1
  Column_name: title
   Index_type: FULLTEXT
*************************** 2. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: ft_title_body
 Seq_in_index: 2
  Column_name: body
   Index_type: FULLTEXT

Значення: У вас композитний FULLTEXT‑індекс на title і body. Якщо запит використовує лише body, але індекс композитний в іншому порядку, він все одно може використовуватись, але поведінка залежить від движка/версії і форми запиту.

Рішення: Переконайтесь, що у вашому запиті MATCH(title, body) відповідає списку колонок індексу. Якщо ні — змініть запит або індекс; не покладайтесь на магію оптимізатора.

Завдання 2 (MySQL): Перевірити стоп‑слова і налаштування розміру токена, що пояснюють «пропущені результати»

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'innodb_ft_%';"
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 3     |
| innodb_ft_max_token_size | 84    |
| innodb_ft_enable_stopword| ON    |
+--------------------------+-------+

Значення: Токени коротші за 3 символи не індексуються. Стоп‑слова увімкнені. Дво‑літерні пошуки тихо провалюються.

Рішення: Якщо у вашому домені важливі короткі токени (SKU, коди), плануйте зміну конфігу і перебудову індексу — або переробіть, зберігаючи окреме нормалізоване поле для кодів і шукайте його через B‑tree.

Завдання 3 (MySQL): Перевірити, чи оптимізатор використовує FULLTEXT або робить щось дороге

cr0x@server:~$ mysql -e "EXPLAIN SELECT id FROM articles WHERE MATCH(title, body) AGAINST('incident response' IN NATURAL LANGUAGE MODE) AND tenant_id=42 ORDER BY updated_at DESC LIMIT 20\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
         type: fulltext
possible_keys: ft_title_body,idx_tenant_updated
          key: ft_title_body
      key_len: 0
          ref:
         rows: 12000
        Extra: Using where; Using filesort

Значення: Використовується FULLTEXT (type: fulltext), але також виконано filesort. Саме там часто ховається латентність під навантаженням.

Рішення: Розгляньте зміну UX/запиту: спочатку отримуйте топ N результатів без ORDER BY updated_at, потім сортуйте в аплікації; або підтримуйте окрему стратегію «недавні по орендарю»; або погодьтеся на менш суворе сортування.

Завдання 4 (MySQL): Виявити ресурсоємні запити з повнотекстом у slow log

cr0x@server:~$ sudo awk '/MATCH\(|AGAINST\(/ {print}' /var/log/mysql/mysql-slow.log | head -n 5
# Query_time: 1.842  Lock_time: 0.000 Rows_sent: 20  Rows_examined: 250000
SELECT id,title FROM articles WHERE MATCH(title,body) AGAINST('status page' IN BOOLEAN MODE) AND tenant_id=42 ORDER BY updated_at DESC LIMIT 20;
# Query_time: 1.221  Lock_time: 0.000 Rows_sent: 0  Rows_examined: 180000
SELECT id FROM articles WHERE MATCH(body) AGAINST('to be' IN NATURAL LANGUAGE MODE) AND tenant_id=42 LIMIT 20;

Значення: Запити переглядають величезну кількість рядків відносно повернутих. Також видно, що «to be» повертає 0 (ймовірно стоп‑слова/мін. розмір токена).

Рішення: Додайте запобіжники: мінімальна довжина запиту, повідомлення UI про стоп‑слова, і перепис запитів, що застосовують селективні фільтри раніше.

Завдання 5 (MySQL): Перевірити реплікаційний лаг, спричинений churn індексації

cr0x@server:~$ mysql -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 143

Значення: Ви відстаєте на 143 секунди. Масивні оновлення з FULLTEXT можуть підсилювати це під час сплесків.

Рішення: Якщо оновлення пошуку викликають лаг, відокремте індексацію (асинхронно), пакетно виконуйте оновлення у непікові години або виносьте пошук з основного шляху БД.

Завдання 6 (PostgreSQL): Підтвердити тип FTS‑індексу і розмір (рання ознака блоуту)

cr0x@server:~$ psql -d appdb -c "\di+ public.*fts*"
                                           List of relations
 Schema |     Name      | Type  |  Owner   |   Table   | Persistence | Access method |  Size  | Description
--------+---------------+-------+----------+-----------+-------------+---------------+--------+-------------
 public | docs_fts_gin  | index | app_user | docs      | permanent   | gin           | 845 MB |
(1 row)

Значення: GIN‑індекс займає 845 MB. Це може бути нормально — або ознака блоуту, залежно від розміру таблиці та churn.

Рішення: Порівняйте зростання індексу в часі. Якщо індекс росте швидше за таблицю — дослідіть vacuum і патерни оновлень.

Завдання 7 (PostgreSQL): Перевірити здоров’я autovacuum/vacuum для таблиці FTS

cr0x@server:~$ psql -d appdb -c "SELECT relname,n_live_tup,n_dead_tup,last_autovacuum,last_vacuum FROM pg_stat_user_tables WHERE relname IN ('docs');"
 relname | n_live_tup | n_dead_tup |     last_autovacuum     |        last_vacuum
---------+------------+------------+-------------------------+-------------------------
 docs    |    2100342 |     482991 | 2025-12-28 09:42:11+00  |
(1 row)

Значення: Майже півмільйона dead tuples. Це ризик відставання vacuum, і для GIN‑підтримки FTS це може означати погіршення латентності запитів і більші індекси.

Рішення: Налаштуйте autovacuum для цієї таблиці (знизьте пороги) і розгляньте зменшення churn у текстах, що індексуються.

Завдання 8 (PostgreSQL): Побачити, чи запит використовує GIN і наскільки він дорогий

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM docs WHERE tenant_id=42 AND fts @@ websearch_to_tsquery('english','incident response') ORDER BY updated_at DESC LIMIT 20;"
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2312.44..2312.49 rows=20 width=16) (actual time=118.230..118.244 rows=20 loops=1)
   Buffers: shared hit=18543 read=412
   ->  Sort  (cost=2312.44..2320.12 rows=3071 width=16) (actual time=118.228..118.237 rows=20 loops=1)
         Sort Key: updated_at DESC
         Sort Method: top-N heapsort  Memory: 34kB
         ->  Bitmap Heap Scan on docs  (cost=122.50..2231.64 rows=3071 width=16) (actual time=24.911..113.775 rows=5208 loops=1)
               Recheck Cond: (fts @@ websearch_to_tsquery('english'::regconfig, 'incident response'::text))
               Filter: (tenant_id = 42)
               Buffers: shared hit=18512 read=412
               ->  Bitmap Index Scan on docs_fts_gin  (cost=0.00..121.73 rows=16347 width=0) (actual time=23.021..23.022 rows=16221 loops=1)
                     Index Cond: (fts @@ websearch_to_tsquery('english'::regconfig, 'incident response'::text))
 Planning Time: 1.112 ms
 Execution Time: 118.410 ms
(13 rows)

Значення: Використовується GIN, але фільтр по орендарю застосовується після bitmap heap scan, тобто ви вибираєте багато співпадінь, а потім відкидаєте більшість. Buffers показують значну роботу.

Рішення: Розгляньте комбіновану стратегію: додати частковий індекс на орендаря (якщо кількість орендарів невелика), або зберігати tenant_id в ідентифікаторі документа і партиціювати по орендарях, або підтримувати окремий fts на шард для орендаря.

Завдання 9 (PostgreSQL): Підтвердити конфіг текстового пошуку і поведінку стеммінгу

cr0x@server:~$ psql -d appdb -c "SHOW default_text_search_config;"
 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

Значення: Конфіг за замовчуванням — English. Якщо ви індексуєте мультимовний контент, це ризик для релевантності та коректності.

Рішення: Обирайте конфігурацію за мовою документу, або використовуйте simple для нелігвістичних токенів. Не прикидайтесь, що один стеммер підходить для всього.

Завдання 10 (PostgreSQL): Перевірити, які токени фактично індексуються

cr0x@server:~$ psql -d appdb -c "SELECT to_tsvector('english','Running runners ran easily') AS v;"
                     v
-------------------------------------------
 'easili':4 'ran':3 'run':1,2
(1 row)

Значення: «Running» і «runners» нормалізуються до «run», «easily» до «easili». Ось чому стеммінг може допомагати або шкодити.

Рішення: Якщо стеммінг псує доменні терміни, змініть конфіг або зберігайте окремі поля з simple‑словником для кодів/імен.

Завдання 11 (PostgreSQL): Впіймати «небезпечне» побудування tsquery до того, як воно стане великою проблемою

cr0x@server:~$ psql -d appdb -c "SELECT to_tsquery('english','foo:bar');"
ERROR:  syntax error in tsquery: "foo:bar"

Значення: Введення користувача може породжувати синтаксичні помилки, якщо ви використовуєте to_tsquery напряму.

Рішення: Використовуйте plainto_tsquery або websearch_to_tsquery для введення користувача. Розглядайте to_tsquery як внутрішнє API.

Завдання 12 (Система): Визначити, чи пошук зв’язаний з CPU або IO на хості БД

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 3  0      0  81244  52120 912340    0    0   210   180  790 1460 45 12 40  3  0
 6  1      0  65010  52080 910120    0    0  4520  1100 1200 2400 32 10 35 23  0
 7  2      0  64122  51990 905332    0    0  6120  1450 1350 2600 28 11 33 28  0
 5  1      0  63200  51920 900110    0    0  5900  1500 1300 2500 29 12 34 25  0
 4  0      0  62910  51860 899820    0    0  4800  1200 1150 2300 31 11 37 21  0

Значення: wa (IO wait) підскакує до 20–28%. Це натякає, що вузьке місце — сховище, а не лише CPU. Повнотекстові запити часто перетворюються на «прочитати багато сторінок» навантаження, коли селективність низька.

Рішення: Якщо IO wait високий, пріоритет — кеш‑хітрейт, селективність індексів і продуктивність сховища. Якщо CPU на максимумі при низькому IO wait — працюйте з планами запитів, вартістю токенізації і лімітуйте конкурентність.

Завдання 13 (PostgreSQL): Знайти топ‑витратні запити (включно з пошуком)

cr0x@server:~$ psql -d appdb -c "SELECT calls, total_exec_time::bigint AS ms, mean_exec_time::numeric(10,2) AS mean_ms, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;"
 calls |   ms   | mean_ms | rows |                              query
-------+--------+---------+------+-------------------------------------------------------------------
  8123 | 984221 | 121.21  |  0   | SELECT id FROM docs WHERE tenant_id=$1 AND fts @@ websearch_to_tsquery('english',$2) ORDER BY updated_at DESC LIMIT 20
  1102 | 312110 | 283.22  |  1   | UPDATE docs SET body=$1, fts=to_tsvector('english',$1) WHERE id=$2
   989 | 221004 | 223.45  |  1   | SELECT count(*) FROM docs WHERE fts @@ plainto_tsquery('english',$1)
(3 rows)

Значення: Ваші топ‑споживачі включають і читання пошуку, і записи оновлення FTS. Це класичний профіль «функція пошуку навантажує все».

Рішення: Вирішіть, чи (a) зменшити частоту оновлень індексованих полів, (b) винести оновлення вектра поза запитом, або (c) винести пошук в окрему систему.

Завдання 14 (MySQL): Підтвердити, чи ваш FULLTEXT‑запит повертає низьку селективність (занадто багато співпадінь)

cr0x@server:~$ mysql -e "SELECT COUNT(*) AS matches FROM articles WHERE MATCH(title, body) AGAINST('status' IN NATURAL LANGUAGE MODE);"
+---------+
| matches |
+---------+
| 182344  |
+---------+

Значення: Дуже поширений термін співпадає з величезним відсотком документів. Це низька селективність; вона породжує важку роботу, проблеми з сортуванням і промахи кешу.

Рішення: Додайте фільтри, вимагайте додаткові терміни, налаштуйте стоп‑слова або перейдіть на UX зі звуженням пошуку (наприклад у межах проєкту/орендаря/діапазону дат).

Плейбук швидкої діагностики

Коли пошук гальмує, часу на філософські дискусії немає. Потрібно знайти вузьке місце за лічені хвилини.

Перше: це план запиту, насичення ресурсів чи перекіс даних?

• Перевірте насичення системи: CPU проти IO wait. Якщо IO wait високий — ви читаєте забагато з диска або сховище заважає. Якщо CPU високий при низькому IO wait — ви робите дорогий ранжир/парсинг або маєте надмірну конкурентність.
• Перевірте план запиту: Чи використовується FTS‑індекс? Чи сортуєте ви великі набори кандидатів? Чи фільтри застосовуються рано чи пізно?
• Перевірте селективність: Пошуки за загальними словами повертають масивні набори? Це не «проблема продуктивності», це математика.

Друге: відокремте «болі читання» від «болей запису»

• Біль читання: повільні SELECT, промахи буферу, великі bitmap scans, filesorts, тимчасові таблиці, висока кількість перевірених рядків.
• Біль запису: повільні UPDATE/INSERT, реплікаційний лаг, сплески WAL/binlog, відставання vacuum/autovacuum, конкуренція через обслуговування індексу.

Третє: оберіть стратегію стримування

• Тримайте зараз: обмежте швидкість пошуку, додайте мінімальну довжину запиту, вимкніть дорогі сортування, обмежте результати, поверніть часткові результати.
• Виправте наступного: додайте або виправте індекси, змініть структуру запиту, налаштуйте vacuum/autovacuum, перепишіть стратегію токенізації.
• Реархітектура: якщо пошук — топ‑споживач ресурсів, ізолюйте його: репліки для читання пошуку, окрема БД або зовнішній пошуковий рушій.

Поширені помилки: симптом → корінь → фікс

1) «Пошук не повертає короткі терміни»

Симптом: Дво‑літерні коди продуктів або короткі назви ніколи не збігаються.

Корінь: MySQL innodb_ft_min_token_size занадто великий, стоп‑слова увімкнені; у Postgres конфігурація стеммінгу/токенізації не підходить для кодів.

Фікс: Для MySQL змініть розмір токена і перебудуйте індекси (плануйте downtime/обслуговування). Для Postgres індексуйте коди окремо B‑tree або використовуйте simple конфіг на спеціалізованих полях.

2) «Пошук став повільним після додавання ORDER BY updated_at»

Симптом: Запит використовує FTS‑індекс, але латентність і CPU/IO злітають.

Корінь: Сортування великих наборів кандидатів; MySQL filesort/тимчасові таблиці; у Postgres сортування після bitmap heap scan.

Фікс: Змініть UX (сортуйте за рангом замість часу), використовуйте двоетапне отримання, або попередньо обчислюйте «недавні документи» по орендарю і шукайте в цьому вікні.

3) «Postgres FTS раптово став спайковим і лишається поганим»

Симптом: Періодичні сплески латентності; розмір індексу росте; vacuum відстає.

Корінь: Роздування GIN‑індексу + відставання vacuum; часті оновлення індексованого тексту.

Фікс: Налаштуйте autovacuum для таблиці; зменшіть churn, батчуйте оновлення або перебудуйте індекси під час технічного вікна. Якщо churn — бізнес‑потреба, ізолюйте пошук.

4) «Деякі пошуки користувачів викликають помилки в Postgres»

Симптом: Частина запитів повертає 500 зі синтаксичними помилками tsquery.

Корінь: Використання to_tsquery напряму на введенні користувача.

Фікс: Замініть на websearch_to_tsquery (кращий UX) або plainto_tsquery. Логуйтесь відкинуті токени, не падайте на помилку.

5) «MySQL пошук поводиться по‑різному між середовищами»

Симптом: На staging результати є; у prod — ні, або відмінності в сортуванні.

Корінь: Різні стоп‑списки, розмір токена, collation/charset або різні версії/движки.

Фікс: Стандартизуйте конфіг MySQL між середовищами; явно задайте collations/charsets; розглядайте стоп‑список як конфіг, який тестується.

6) «Пошук уповільнює все інше в базі»

Симптом: Зі збільшенням трафіку пошуку повільнішають несуміжні ендпоїнти.

Корінь: Спільний CPU/IO/кеш; повнотекстові скани і ранжування конкурують з OLTP; ефекти чергування.

Фікс: Обмежте конкурентність пошуку; перенесіть читання пошуку на репліки; ізолюйте в окрему базу або сервіс, коли це стає значним внеском у навантаження.

7) «Результати дурні: загальні документи домінують»

Симптом: Довгі або спамні документи завжди на вершині.

Корінь: Ранжування не налаштовано; ваги рівні; нормалізація не відповідає домену; індексуються низькоякісні текстові поля.

Фікс: Вага title вище за body; виключіть boilerplate; зберігайте структуровані поля окремо; у Postgres використовуйте зважені вектори і нормалізацію рангу; у MySQL використовуйте булеві обмеження або додаткові фільтри.

8) «Мультиорендний пошук повільний, навіть з індексами»

Симптом: Є фільтр по орендарю, але запит все одно читає великі об’єми.

Корінь: FTS‑індекс не містить tenant_id, тому ви отримуєте збіги по всіх орендарях, а потім фільтруєте.

Фікс: Партиціюйте по орендарю, використовуйте окремі таблиці для орендарів (якщо можливо), або підтримуйте індекси/часткові індекси для кожного орендаря; принаймні, перекомпонувати пошук так, щоб спочатку шукати в межах орендаря.

Перевірочні списки / покроковий план

Покроково: запускати вбудований FTS так, щоб не ненавидіти свого майбутнього

1. Запишіть «контракт пошуку»: підтримувані оператори, мінімальна довжина запиту, підтримувані мови, правила ранжування і що означає «фраза». Якщо не можете це описати — користувачі виявлять це важко.
2. Визначте межі області: пошук тільки по орендарю, вікна по датах, типи документів. Додавайте фільтри, що зменшують кандидати на ранньому етапі.
3. Вирішіть налаштування токенізації і мови (Postgres) або стоп‑слова/розмір токена (MySQL). Занотуйте це в конфігу управління.
4. Індексуйте тільки те, що матиме значення: не засовуйте кожен текстовий blob у вектор/індекс. Розглядайте індексований текст як центр витрат.
5. Визначте «безпечне побудування запитів»: ніколи не парсити введення користувача як синтаксис без екранування/перетворення. Postgres: віддавайте перевагу websearch_to_tsquery. MySQL: санітизувати булеві оператори, якщо їх дозволяєте.
6. Тестуйте агресивними запитами: загальні слова, пусті результати, пунктуація, Unicode, наддовгі рядки, «копіювати/вставити з Word».
7. Встановіть запобіжники: ліміти запитів, timeouts, circuit breakers і «вибачте, звузьте пошук» відповіді.
8. Моніторте одну канаркову перевірку і відстежуйте P95, читання буферів і реплікаційний лаг. Проблеми з пошуком часто проявляються поступово.
9. Плануйте вихід: задокументуйте, як дзеркалити документи в спеціалізований пошуковий рушій. Навіть якщо ніколи не використаєте — дисципліна дизайну корисна.

Чекліст: вибір між MySQL і Postgres для вбудованого FTS

• Якщо потрібна гнучкість і налаштовувана релевантність: перевага на боці Postgres. Можна зважувати поля, вибирати словники і будувати структуровану поведінку пошуку.
• Якщо хочете мінімум рухомих частин і готові до простішого скорінгу: MySQL FULLTEXT підходить для простого keyword‑пошуку з обмеженнями.
• Якщо у вас великий write churn по індексованому тексту: обережніше з обома; обслуговування GIN у Postgres і оновлення допоміжних таблиць у MySQL б’ють по всьому. Розгляньте асинхронні патерни індексації.
• Якщо важлива мультимовність: Postgres зазвичай простіше налаштувати правильно — якщо ви дійсно це зробите.
• Якщо вам потрібна «семантика веб‑пошуку» для введення користувачів: перевага Postgres із websearch_to_tsquery.

Чекліст: ознаки, що пора переходити на спеціалізований пошуковий рушій

• Пошук у топ‑3 споживачів CPU або IO у БД.
• Потрібні нечітке зіставлення, синоніми, автодоповнення, підсвічування, аналізатори на поле або гібрид вектор+лексика.
• Додаєте правила релевантності на рівні орендаря і ваша схема починає виглядати як конфіг пошукового рушія.
• Інциденти регулярно пов’язані з «трафік пошуку зробив базу сумною».
• Ваша організація може експлуатувати ще одну stateful систему без ставлення до неї як до унікальної «пета».

FAQ

1) Чи «поганий» MySQL FULLTEXT?

Ні. Він радше упереджений і обмежений. Чудово підходить для простого keyword‑пошуку з обмеженнями. Стає болісним, коли потрібна складна релевантність або коли навантаження пошуку конкурує з OLTP.

2) Чи замінює PostgreSQL FTS Elasticsearch/OpenSearch?

Іноді — для внутрішніх або помірних функцій пошуку. Якщо потрібна толерантність до опечаток, багаті аналізатори, швидкі фасети в масштабі або складні пайплайни ранжування, ви швидко відчуєте нестачу спеціалізованого рушія.

3) Чому мої Postgres‑пошуки дивно поводяться з пунктуацією і спецсимволами?

Бо tsquery має синтаксис. Якщо ви будуєте tsquery напряму з введення користувача, пунктуація може стати операторами або викликати синтаксичні помилки. Використовуйте websearch_to_tsquery або plainto_tsquery.

4) Чому «ORDER BY updated_at» такий дорогий з FTS?

Бо FTS повертає набір співпадінь; сортування за іншим атрибутом часто вимагає сортування великого набору кандидатів. Якщо набір великий — ви сортуєте дуже багато. Розгляньте сортування за рангом, попередню фільтрацію або двоетапне отримання.

5) Як покращити ранжування в Postgres?

Розділіть поля (title, body, tags), призначте ваги і використовуйте зважені вектори. Також видаліть boilerplate з індексованого поля. Ранжування — це стільки ж гігієна даних, скільки математика.

6) Як найкраще обробляти мультиорендний повнотекстовий пошук?

Спочатку звужуйте пошук до орендаря. У Postgres подумайте про партиціювання по орендарю або часткові індекси, якщо кількість орендарів керована. У MySQL забезпечте селективність фільтру по орендарю і уникайте глобальних пошуків, які потім фільтруються.

7) Чи зберігати tsvector як згенерований стовпець?

Згенеровані стовпці зручні, але додають вартість індексування на шляху запису. Якщо у вас великий churn, розгляньте асинхронні оновлення або пакетні оновлення в залежності від вимог до консистентності.

8) Як зрозуміти, проблема в індексі чи в запиті?

Дивіться план і buffers/rows examined. Якщо FTS‑індекс використовується, але ви все одно читаєте багато — ймовірно низька селективність або дороге сортування/фільтрація. Якщо індекс не використовується — питання у побудові запиту або статистиках планувальника.

9) Чи можна робити пошук фрази у вбудованому FTS?

Postgres підтримує пошук фраз через phraseto_tsquery і пов’язані інструменти, але це не те саме, що повний «exact substring match». У MySQL булевий режим має оператори, але поведінка фраз не еквівалентна рушіям з позиційними індексами, оптимізованими для підсвічування.

10) Який найбільший операційний ризик з вбудованим повнотекстовим пошуком?

Пошук має спайковий, керований користувачами характер навантаження. Він перетворює вашу primary DB у спільний пул обчислень для непередбачуваних запитів, і саме так несуміжні ендпоїнти отримують колатеральні ушкодження.

Практичні наступні кроки

Якщо ви вже використовуєте вбудований повнотекстовий пошук, ваше завдання — не допустити, щоб він тихо став головним споживачем вашої бази.

1. Додайте канарку пошуку (один запит, одна панель, один алерт). Відстежуйте латентність, перевірені рядки і читання буферів у часі.
2. Впровадьте запобіжники: мінімальна довжина запиту, ліміти швидкості, timeouts і розумне максимальне вікно результатів.
3. Запустіть діагностичні завдання вище у тихий період і зафіксуйте базові показники: розміри індексів, статистику vacuum, slow queries, форми планів.
4. Прийміть стратегію стримування: репліки для читання пошуку або ізоляція пошуку в окрему службу, коли він стане великим споживачем ресурсів.
5. Зробіть релевантність продуктовим рішенням, а не випадковим результатом налаштувань за замовчуванням. Якщо вам потрібно більше, ніж ваги і токенізація — припиніть торгуватися з базою і закладіть бюджет на спеціалізований пошуковий рушій.

Вбудований повнотекстовий пошук може бути гострим інструментом. Просто не використовуйте його як молоток на проблемі, яка очевидно потребує пневматичного цвяхозабивача.