PostgreSQL проти SQLite: повнотекстовий пошук — коли SQLite дивує (а коли ні)

Каркас рішення: що ви насправді обираєте

«PostgreSQL vs SQLite» — неправильне питання. Правильне питання:
куди ви хочете помістити складність?
SQLite переносить складність у процес додатку й у файлову систему. PostgreSQL тримає її в сервері з ручками, інструментами інспекції й чіткими межами.

Для повнотекстового пошуку (FTS) реальне рішення залежить від:

• моделі конкурентності: SQLite чудовий, поки конкуренція записів не стане вашою реальністю. PostgreSQL створений для конкурентних записів.
• ранжування й лінвістика: обидва можуть бути «достатньо хорошими», але PostgreSQL дає більше структурного контролю; SQLite дає швидкість і портативність.
• операційна видимість: Postgres має зрілі статистики, плани запитів і серверну інструментацію. SQLite вимагає, щоб ви були власним DBA.
• життєвий цикл даних: бекапи, реплікація, обробка корупції й еволюція схеми виглядають інакше, коли база — це файл.
• утримання відмов: завислий writer в SQLite може зупинити ваш додаток. Висячий запит у Postgres можна вбити без вбивства всього процесу.

Оперіонована порада:
Якщо ваш додаток однозв’язковий, з малою кількістю записів і ви хочете вбудованої простоти — SQLite FTS5 — реальний варіант.
Якщо вам потрібна мульти-реплікація записів, онлайн-обслуговування та надійна інспекція — використовуйте PostgreSQL і не вибачайтеся за це.

Одна цитата, яка закарбувалася у людей з опсів десятиліттями і тут пасує:
«Надія — не стратегія.» — Внічe Ломбарді

Факти та історія, які важливіші за бенчмарки

Бенчмарки цікаві, поки не стануть закупівлею. Ось конкретні контекстні пункти, що впливають на реальні системи.
(Це та частина, де хтось каже «але у мене на ноуті…» і ви тихо закриваєте вкладку.)

1. SQLite старше багатьох «ентерпрайзних» стеків. Він почався близько 2000 року, створений для надійності та вбудованості без адміністрування.
2. SQLite FTS — це не одна річ. FTS3/4 з’явилися раніше; FTS5 новіший і має кращу розширюваність та функції, як-от вбудований BM25 для ранжування.
3. PostgreSQL повнотекстовий пошук з’явився задовго до тренду. tsearch2 існував як зовнішній модуль на початку 2000-х; пізніше його інтегрували в ядро.
4. Обмеження конкурентності SQLite — архітектурне. Це не «повільно»; це те, що один файл бази має семантику блокувань, яка може створювати вузькі місця для записів.
5. Режим WAL був важливою зміною для SQLite. Він покращив читання/запис за рахунок відокремлення журнального файлу від основної бази.
6. GIN-індекс у PostgreSQL зробив текстовий пошук практичним у масштабі. GIN створено для типів даних «містять багато ключів», як tsvector-терміни.
7. Обидві системи токенізують; жодна не «Google». Вам доведеться керувати стеммінгом, стопсловами та синонімами самому, і в вас усе одно питатимуть за це.
8. SQLite відомий своїм тестуванням. Його тестовий набір інтенсивний і довготривалий; це одна з причин, чому йому довіряють у вбудованих системах.
9. PostgreSQL — це платформа, а не просто база даних. Розширення, кастомні словники та серверні джоби змінюють вигляд «повнотекстового пошуку».

Жарт №1 (коротко, по темі): Повнотекстовий пошук — це місце, де продукт виявляє, що мова неоднозначна, а інженерія виявляє, що продукт теж неоднозначний.

Як насправді працюють SQLite FTS5 і PostgreSQL tsearch

SQLite FTS5: інвертований індекс у файлі

FTS5 зберігає інвертований індекс: терміни → списки ID рядків (і позиції, залежно від опцій).
«Таблиця», яку ви запитуєте, не є звичайною таблицею; це віртуальна таблиця, що підкріплена структурами індексу FTS.
Ви записуєте рядки, вона токенізує текст за допомогою токенайзера (наприклад unicode61) і оновлює індекс.

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

PostgreSQL tsearch: лексеми, словники та індекси

Повнотекстовий пошук у PostgreSQL обертається навколо tsvector (документ як нормалізовані терміни з позиціями) і tsquery (запит).
Ви можете будувати tsvector з колонок «на льоту», але в продакшені зазвичай зберігають його (згенерована колонка або тригер) і індексують з GIN.

Postgres додає структуру: конфігурації, словники, стопслова та ваги.
Та структура робить його більш контрольованим для мультимовного чи предметно-специфічного пошуку, але також дає більше шляхів для неправильного налаштування.

Ранжування: BM25-подібне проти ts_rank

SQLite FTS5 включає функцію ранжування BM25; це не магія, але доволі узгоджено з тим, що люди очікують від пошуку.
PostgreSQL надає ts_rank та інші; з вагами й нормалізацією можна отримати хороші результати, але доведеться тюнити.

Переклад: якщо ви хочете «гідне ранжування з коробки», SQLite може приємно здивувати.
Якщо ви хочете «ранжування, яке можна пояснити юридичній команді», PostgreSQL дає більше важелів.

Коли SQLite дивує вас (в хорошому сенсі)

1) Вбудований пошук з низьким операційним навантаженням

Ви деплоїте один двійковий файл (або контейнер) і один файл бази даних.
Вам не потрібен DB-сервер, пулінг з’єднань, міграції між нодами чи service discovery.
Для внутрішніх інструментів, десктопних додатків, edge-пристроїв і single-tenant деплойментів SQLite FTS5 — дарунок.

2) Навантаження з переважно читаннями та локальними даними

Якщо ваш патерн доступу переважно читання і час від часу батчеві записи, SQLite у режимі WAL може бути дуже швидким.
Локальний I/O, без мережевого хопу, без серверної конкуренції і зрілий рушій запитів.

3) Невеликі та середні корпорації з реалістичними запитами

Для десятків тисяч до кількох мільйонів документів (залежно від розміру документу і заліза) SQLite FTS5 може здаватися «занадто швидким, щоб бути правдою».
Це реальність. Проте легко перерости, якщо додати:
часті оновлення, багато записувачів або високу конкуренцію запитів під одним файловим замком.

4) Портативне індексування і відтворювані тести

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

Коли SQLite не дивує (і заважає)

Конкурентність: вузьке місце записувача

SQLite може виконувати паралельні читання за допомогою WAL, але записи все ще серіалізуються.
Для FTS записи — це не «дешеві вставки»; вони також оновлюють індекс. При постійному навантаженні записів ви побачите очікування блокувань і хвостові спайки затримок.

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

Операційний контроль і інспекція

Postgres дає вам pg_stat_statements, логи повільних запитів, EXPLAIN (ANALYZE, BUFFERS), поведінку фонової vacuum та інше.
SQLite дає pragma і те, що ви підключите для трасування. Це працює — поки ваш інцидент-менеджер не спитає «що змінилося?»

Горячі оновлення і поведінка злиттів

Часті оновлення одного й того ж набору документів можуть фрагментувати структури індексу FTS і збільшувати роботу злиття.
Якщо ви не плануєте обслуговування (або плануєте його під піком), ви можете створити самостійний DoS.

Підводні камені коректності: токенізація й локаль

«Чому ‘résumé’ не знаходить ‘resume’?» — це не філософське питання; це питання налаштування токенайзера.
Токенайзери SQLite настроювані, але не безмежні. Конфіги текстового пошуку PostgreSQL можуть бути більш тонкими, особливо з кастомними словниками.

Жарт №2 (коротко, по темі): Конкурентність SQLite — як маленька кав’ярня: еспресо чудове, але лише один бариста може одночасно пінити молоко.

Що дає PostgreSQL, чого не буде в SQLite

Сильна мультикористувацька конкурентність та ізоляція

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

Опції індексування та передбачуване планування запитів

У PostgreSQL ви можете:

• Зберігати tsvector і індексувати його за допомогою GIN.
• Використовувати часткові індекси, щоб звузити пошук до «активних» документів.
• Використовувати згенеровані колонки, щоб уникнути тригерів.
• Тюнити GIN (наприклад fastupdate) і моніторити роздування індекса.

Кращі інструменти для «дня після релізу»

Повнотекстовий пошук стає політичним після релізу.
Хтось запитає «чому цей результат вище за той?» або «можемо виключити внутрішні документи?»
Видимість планів, статистик і фонового обслуговування у PostgreSQL робить дебаг без здогадок простішим.

Але: Postgres теж не пошуковий движок

Якщо вам потрібна нечітка пошуковість, толерантність до опечаток, синоніми в масштабі, аналітика під час запиту або розподілене індексування — ви прямуєте до виділеного пошукового сервера.
Використовуйте SQLite/Postgres FTS, коли потрібен вбудований у базу пошук, а не друга інфраструктура.

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

Це реальні «що робити о 2-й ночі» задачі. Кожна містить команду, типовий вихід, що це означає, і рішення.
Команди можна виконати; налаштуйте шляхи, імена баз і користувачів під своє середовище.

Завдання 1: Підтвердити режим журналювання SQLite (WAL чи ні)

cr0x@server:~$ sqlite3 app.db "PRAGMA journal_mode;"
wal

Що це означає: wal дає кращу читання/запис конкуренцію ніж delete або truncate.

Рішення: Якщо не wal і у вас є одночасні читання під час записів, переключіться на WAL і повторно перевірте хвостові затримки.

Завдання 2: Перевірити таймаут блокування SQLite (щоб уникнути миттєвого “database is locked”)

cr0x@server:~$ sqlite3 app.db "PRAGMA busy_timeout;"
0

Що це означає: 0 означає, що виклики зазнають помилки миттєво при контенції блокувань.

Рішення: Встановіть ненульовий таймаут у з’єднанні додатка (або PRAGMA), якщо очікується коротка контенція; інакше усуньте джерело конкуренції записувачів.

Завдання 3: Перевірити, що таблиця FTS5 існує і це справді FTS5

cr0x@server:~$ sqlite3 app.db ".schema docs_fts"
CREATE VIRTUAL TABLE docs_fts USING fts5(title, body, content='docs', content_rowid='id', tokenize = 'unicode61');

Що це означає: Це віртуальна таблиця FTS5 з зовнішнім контентом.

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

Завдання 4: Запустити перевірку цілісності FTS5

cr0x@server:~$ sqlite3 app.db "INSERT INTO docs_fts(docs_fts) VALUES('integrity-check');"

Що це означає: Зазвичай відсутність виходу означає успіх. Помилки вказують на корупцію індекса або невідповідність.

Рішення: Якщо тест невдалий — плануйте перестроєння (rebuild) і перевірте файлову систему/IO та випадки раптового завершення процесу.

Завдання 5: Перебудувати індекс FTS5 з зовнішнім контентом (планове обслуговування)

cr0x@server:~$ sqlite3 app.db "INSERT INTO docs_fts(docs_fts) VALUES('rebuild');"

Що це означає: FTS5 перебудовує індекс з таблиці контенту.

Рішення: Плануйте це в непіковий час; якщо час перебудови неприйнятний, ви ймовірно на тому етапі, коли Postgres (або пошуковий движок) стане розумнішим вибором.

Завдання 6: Подивитися, чи SQLite агресивно робить чекпоінти WAL (або зовсім ні)

cr0x@server:~$ sqlite3 app.db "PRAGMA wal_checkpoint(TRUNCATE);"
0|0|0

Що це означає: Вивід — busy|log|checkpointed. Всі нулі означають, що кадрів у черзі немає.

Рішення: Якщо log росте великий у продакшені, додайте контрольовані чекпоінти (на рівні додатка) і моніторьте IO-затримки.

Завдання 7: Переглянути розмір сторінки SQLite і налаштування кешу

cr0x@server:~$ sqlite3 app.db "PRAGMA page_size; PRAGMA cache_size;"
4096
-2000

Що це означає: 4KB сторінки; negative cache_size означає одиниці в KB (тут ~2000KB).

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

Завдання 8: Знайти помилки «database is locked» у логах додатка

cr0x@server:~$ journalctl -u app.service --since "1 hour ago" | grep -i "database is locked" | tail
Dec 30 01:12:40 host app[2219]: sqlite error: database is locked (SQLITE_BUSY) on INSERT INTO docs_fts ...
Dec 30 01:12:41 host app[2219]: sqlite error: database is locked (SQLITE_BUSY) on UPDATE docs SET ...

Що це означає: У вас є контенція записувачів або довгі транзакції, що тримають блокування.

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

Завдання 9: Знайти великі WAL-файли (симптом чекпоінтингу або пікових записів)

cr0x@server:~$ ls -lh app.db app.db-wal app.db-shm
-rw-r--r-- 1 app app  12G Dec 30 01:10 app.db
-rw-r--r-- 1 app app 3.8G Dec 30 01:12 app.db-wal
-rw-r--r-- 1 app app  32K Dec 30 00:55 app.db-shm

Що це означає: WAL великий; вартість відновлення/чекпоінту й тиск на диск наближаються.

Рішення: Дослідіть довгих читачів, що заважають чекпоінтам, додайте періодичні чекпоінти і перевірте запас дискового простору; інакше ви «виявите» ENOSPC під піковим трафіком.

Завдання 10: PostgreSQL: підтвердити конфігурацію текстового пошуку

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

Що це означає: За замовчуванням парсинг/стеммінг налаштовані на англійську.

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

Завдання 11: PostgreSQL: перевірити, чи правильний тип індексу для tsvector

cr0x@server:~$ psql -d app -c "\d+ docs"
                                                   Table "public.docs"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | bigint  |           | not null |         | plain    |             |              |
 title  | text    |           |          |         | extended |             |              |
 body   | text    |           |          |         | extended |             |              |
 tsv    | tsvector|           |          |         | extended |             |              |
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id)
    "docs_tsv_gin" gin (tsv)

Що це означає: Ви маєте GIN-індекс на tsv. Добре.

Рішення: Якщо індекс відсутній — додайте його перш ніж звинувачувати Postgres у «повільності».

Завдання 12: PostgreSQL: переглянути реальний план для запиту пошуку

cr0x@server:~$ psql -d app -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM docs WHERE tsv @@ plainto_tsquery('english','refund policy') ORDER BY ts_rank(tsv, plainto_tsquery('english','refund policy')) DESC LIMIT 20;"
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..35.71 rows=20 width=8) (actual time=4.112..4.168 rows=20 loops=1)
   Buffers: shared hit=812
   ->  Index Scan using docs_tsv_gin on docs  (cost=0.42..271.08 rows=152 width=8) (actual time=4.110..4.156 rows=20 loops=1)
         Index Cond: (tsv @@ plainto_tsquery('english'::regconfig, 'refund policy'::text))
 Planning Time: 0.214 ms
 Execution Time: 4.242 ms
(6 rows)

Що це означає: Використано GIN-індекс; буфери — хіти (в кеші). 4ms — здорово.

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

Завдання 13: PostgreSQL: перевірити, чи autovacuum встигає (bloat впливає й на GIN)

cr0x@server:~$ psql -d app -c "SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
 relname | n_dead_tup |        last_autovacuum
---------+------------+-------------------------------
 docs    |     842113 | 2025-12-30 00:41:12+00
 events  |      12044 | 2025-12-29 23:58:06+00
(2 rows)

Що це означає: Багато мертвих кортежів у docs; оновлення/видалення накопичуються.

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

Завдання 14: PostgreSQL: знайти найповільніші запити за загальним часом (якщо увімкнено pg_stat_statements)

cr0x@server:~$ psql -d app -c "SELECT calls, total_exec_time::int AS total_ms, mean_exec_time::numeric(10,2) AS mean_ms, rows, left(query,80) AS q FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
 calls | total_ms | mean_ms | rows |                                        q
-------+----------+---------+------+--------------------------------------------------------------------------------
 10213 |   912345 |   89.33 |  0   | SELECT id FROM docs WHERE tsv @@ plainto_tsquery($1,$2) ORDER BY ts_rank...
  1400 |   221100 |  157.92 |  1   | UPDATE docs SET body = $1, tsv = to_tsvector($2, $1) WHERE id = $3
(2 rows)

Що це означає: Ваш запит пошуку домінує за загальним часом виконання; шлях оновлення теж важкий.

Рішення: Вирішіть, чи оптимізувати запит/ранжування, кешувати результати, попередньо обчислювати вектори або зменшити частоту оновлень.

Завдання 15: Перевірити насичення I/O на Linux (зазвичай це диск, не токенайзер)

cr0x@server:~$ iostat -xz 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.41    0.00    6.02   21.15    0.00   60.42

Device            r/s     w/s   rkB/s   wkB/s  await  %util
nvme0n1        112.0   980.0  8200.0 65200.0  18.40  97.50

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

Рішення: Перестаньте тюнити ранжування і почніть виправляти I/O: швидше сховище, зменшення write amplification, батчеві записи або перенесення пошуку з гарячого диска.

Завдання 16: Перевірити тиск на дескриптори файлів (додатки в процесі SQLite можуть вдарятися об ліміт)

cr0x@server:~$ cat /proc/$(pgrep -n app)/limits | grep "Max open files"
Max open files            1024                 1048576              files

Що це означає: Soft-ліміт 1024; ваш додаток може стикатися з ним через логи, сокети та файли БД.

Рішення: Підніміть ліміти, якщо бачите періодичні I/O помилки; це нудно, але запобігає фейковим «проблемам з базою».

Швидка схема діагностики

У вас повільний пошук або таймаути. Немає часу на дисертацію.
Робіть це в такому порядку. Зупиняйтесь, коли знайдете димляче місце.

По-перше: вирішіть, чи ви CPU-bound, IO-bound чи lock-bound

• IO-bound: високий %iowait, використання диска %util біля 100%, зростаючий WAL, повільні fsync.
• CPU-bound: високий user CPU, стабільне IO, важка токенізація/ранжування, великі набори результатів для сортування.
• Lock-bound: «database is locked» (SQLite), довгі транзакції (Postgres), блоковані записувачі.

По-друге: перевірте, що індекс використовується

• SQLite: підтвердіть, що ви використовуєте docs_fts MATCH ?, а не LIKE '%term%' на таблиці контенту.
• Postgres: EXPLAIN (ANALYZE, BUFFERS); шукайте використання GIN, а не seq scan.

По-третє: перевірте write amplification і фонове обслуговування

• SQLite: розмір WAL, частота чекпоінтів, поведінка злиття/перебудови FTS.
• Postgres: відставання autovacuum, мертві кортежі, поведінка черги GIN, роздування індекса.

По-четверте: проаналізуйте форму запиту та ранжування

• Чи сортуєте ви тисячі збігів, щоб повернути 20?
• Чи скрізь у вас префіксні запити?
• Чи обчислюєте to_tsvector під час запиту замість збереженого вектора?

П’яте: вирішіть, чи архітектура неправильна

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

Типові помилки: симптом → корінна причина → виправлення

1) Симптом: спайки «database is locked» під час піку

Корінна причина: Багато записувачів або довгі транзакції в SQLite; оновлення індексу FTS підсилюють навантаження записів.

Виправлення: Патерн одного записувача (черга), скоротіть транзакції, встановіть busy_timeout, увімкніть WAL, або переходьте на Postgres, якщо мульти-запис реальний.

2) Симптом: результати пошуку відсутні для нещодавно оновлених документів

Корінна причина: Зовнішня FTS-таблиця не синхронізована (тригери відсутні, зламалися або були вимкнені під час масового завантаження).

Виправлення: Відновіть тригери, запустіть FTS5 rebuild, додайте інваріантні перевірки в CI (порахувати невідповідності) і алерт на дрейф.

3) Симптом: запит Postgres використовує послідовне сканування

Корінна причина: Немає GIN-індексу на tsvector, або запит обертає tsvector функцією, або терміни мають низьку селективність.

Виправлення: Збережіть tsvector, додайте GIN-індекс, використовуйте правильні оператори (@@), розгляньте часткові індекси, налаштуйте конфіг/стопслова.

4) Симптом: ранжування виглядає випадковим або «гірше, ніж було»

Корінна причина: Зміни токенайзера/словника, зміни списку стоп-слів, змішання мов або перехід від фразових запитів до bag-of-words.

Виправлення: Версіонуйте конфіг пошуку, напишіть регресійні тести з золотими запитами і ставте ранжування як контракт API.

5) Симптом: використання диска росте й продуктивність погіршується

Корінна причина: Роздування Postgres (мертві кортежі), роздування GIN, WAL SQLite не чекпоінтиться, часті оновлення великих текстових полів.

Виправлення: Налаштуйте autovacuum; зменшіть churn оновлень; в SQLite керуйте чекпоінтами; у Postgres — періодичний REINDEX/вікна обслуговування при потребі.

6) Симптом: «працює на моєму комп’ютері», але в проді падає з текстом не ASCII

Корінна причина: Різні правила токенізації, колація або конфіги текстового пошуку між середовищами.

Виправлення: Зафіксуйте токенайзер/конфіг явно; створіть тест-кейси для Unicode і пунктуації; припиніть покладатися на значення за замовчуванням.

7) Симптом: спайки затримок кожні кілька хвилин

Корінна причина: Чекпоінтинг SQLite або сплески обслуговування FTS; autovacuum Postgres або IO-тиск від сторонніх процесів.

Виправлення: Корелюйте з логами/метриками; плануйте обслуговування; ізолюйте сховище; додайте джиттер/бекафф для батчевих записувачів.

8) Симптом: пошук повертає занадто багато нерелевантних збігів

Корінна причина: Конструкція запиту надто дозволяюча (занадто багато OR), немає зважування полів, відсутні фразові матчі, стопслова занадто агресивно вилучені.

Виправлення: Зважте title > body (Postgres — через ваги; SQLite — через окремі колонки та налаштування ранжування), додайте фразові запити для типових патернів, налаштуйте стопслова.

Чек-листи / покроковий план

Вибір SQLite FTS5: чек-лист «щоб не отримати виклик уночі»

1. Увімкніть режим WAL і підтвердьте, що він зберігається між деплойментами.
2. Встановіть busy_timeout (або реалізуйте повторні спроби з джиттером на рівні додатка).
3. Використовуйте патерн одного записувача, якщо у вас часті записи; розглядайте оновлення FTS як write-heavy.
4. Визначте політику чекпоінтів; моніторте розмір WAL і запас дискового простору.
5. Якщо використовуєте зовнішній контент — підтримуйте тригери і додавайте періодичну перевірку цілісності.
6. Зафіксуйте конфіг токенайзера і тестуйте Unicode-кейси.
7. Майте план перестроювання (прогнозуйте час); не виявляйте, що перестроювання займає години під час інциденту.
8. Бекапи: робіть безпечні знімки файлу БД (і розумійте, що «безпечне» означає у WAL-режимі).

Вибір PostgreSQL tsearch: чек-лист «зробити нудним»

1. Збережіть tsvector (згенерована колонка або тригер) і додайте GIN-індекс.
2. Оберіть default_text_search_config навмисно; не залишайте мультимовний контент на випадок.
3. Використовуйте EXPLAIN (ANALYZE, BUFFERS), щоб підтвердити використання індекса перед будь-яким тюнінгом.
4. Увімкніть pg_stat_statements, щоб знаходити справжні дорогі запити.
5. Налаштуйте autovacuum для таблиць з великим churn; слідкуйте за мертвими кортежами й роздуванням.
6. Визначте функцію ранжування і зафіксуйте її за стабільним інтерфейсом.
7. Плануйте міграції (зміни конфігів, словників) як версіоновані релізи.

Міграція з SQLite FTS до PostgreSQL tsearch: покроково

1. Зробіть інвентаризацію поведінки токенізації (що зараз матчиться) і напишіть регресійну суїту запитів та очікуваних топ-результатів.
2. Експортуйте документи й ID; збережіть стабільні ідентифікатори, щоб порівнювати результати між системами.
3. У Postgres створіть tsvector з найближчою конфігурацією; індексуйте його GIN.
4. Запустіть офлайн-порівняння: recall/precision для ваших регресійних запитів, а не синтетичні бенчмарки.
5. Реалізуйте dual-write або асинхронне індексування; не блокуйте записи користувачів під час індексування під час міграції.
6. Поступово переключайте читання; моніторьте латентність і скарги на релевантність окремо.
7. Виведіть SQLite-пошук лише після того, як зможете швидко відновлювати індекси Postgres і рятуватися від збоїв.

Три корпоративні міні-історії з практики

Інцидент: неправильне припущення («SQLite — це просто файл, отже буде швидше»)

Середня SaaS-команда запустила пошук документів у вбудованому процесі основного додатка, використовуючи SQLite FTS5.
Мета — зменшити залежності: ніяких кластерів Postgres, ніяких сервісів пошуку, ніякого нового чергування on-call.
Перший місяць пройшов нормально. Набір даних був помірний, і більшість записів були нічним імпортом.

Потім вони додали нотатки користувачів з автозбереженням. Записи стали постійними.
Вони припустили, що режим WAL означає «майже конкурентність», і горизонтально масштабували додаток. Кожен інстанс писав до одного й того ж файлу бази на шаред-стореджі.
Симптоми були класичні: випадкові таймаути, іноді «database is locked», і CPU-графіки виглядали спокійними, поки користувачі кричали.

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

Виправлення не було хитрим. Вони перемістили пошук на виділений інстанс Postgres, поставили індексування в чергу і зберегли SQLite для локальної розробки й тестів.
Найцінніша зміна була соціальною: вони записали припущення про конкурентність у дизайн-док, щоб це більше не повторилось.

Оптимізація, що повернулася бумерангом: «Перебудуємо індекс щонічно, щоб він був швидким»

Інша команда мала великий індекс SQLite FTS5 і помітила, що латентність запитів трохи повільніша з часом.
Хтось запропонував нічну повну перебудову таблиці FTS. Це спрацювало в стейджингу, було легко автоматизувати і зробило графіки гарними рівно на один день.
Вони впровадили це з впевненістю і календарним нагадуванням.

Перебудова запускалася на тому ж хості, що й додаток. Вона насичувала диск I/O на годину, потім на дві.
Читання під час перебудови сповільнилися, тому користувачі робили повторні запити, що створило додаткове навантаження. Робота наклалася на ранковий трафік через різні часові пояси.
Support відкрив тікети. Інженери звинувачували мережу. Мережна команда звинувачувала DNS, як того вимагає традиція.

Бумеранг полягав не в тому, що перебудова — «погана». Вона була I/O-подією.
Команда ставила файл бази як внутрішній кеш, але він став первинною користувацькою функцією.
Потрібне було контрольоване обслуговування, а не грубий перезапуск.

Остаточне виправлення: вони перестали щонічно перебудовувати і натомість керували патернами записів, чекпоінтингом і здоров’ям індексу.
Для великого churn вони перейшли на Postgres, де могли робити REINDEX у вікнах і краще ізолювати I/O.
Урок: не влаштовуйте бурю на диску поруч із вашими клієнтами.

Нудна, але правильна практика: «Ми тестували релевантність так само, як API»

Третя компанія використовувала Postgres FTS для внутрішньої бази знань.
Нічого надзвичайного: tsvector, GIN-індекс і кілька тонких налаштувань ранжування.
Їхня секретна зброя була не в технології. Вона була в дисципліні.

Вони тримали невеликий корпус «золотих запитів» з очікуваними топ-результатами.
Кожна зміна словників, конфігів або ваг ранжування проходила через CI і давала звіт про диф.
Інженери швидко дізналися, що «дрібне налаштування» може означати «хвилю звернень в support».

Одного дня рутинне оновлення пакету ОС змінило поведінку, пов’язану з колацією, у стейджингу, що тонко змінило результати токенізації.
Суїта регресії це впіймала. Вони заблокували реліз і розслідували.
Це не був драматичний інцидент. Це був тихий не-інцидент — найкращий вид.

Коли настав час додати ще одну мову, вони не сперечалися про відчуття. Додали вектори на мову і розширили золотий набір.
Це коштувало їм трохи місця й часу.
Але врятувало від тижнів нарад типу «чому пошук погіршився», де всі праві, а ніхто не корисний.

Питання та відповіді

1) Чи підходить SQLite FTS5 для продакшену?

Так, якщо ви однозв’язкові (або з одним записувачем), переважно читання і ви ставите обслуговування (WAL, чекпоінти, перебудови) як реальну операційну турботу.
Вона передбачувано ламається, коли ви порушуєте припущення щодо конкурентності.

2) Чи повільніший PostgreSQL повнотекстовий пошук за SQLite?

Не обов’язково. SQLite може здаватися швидшим, бо вбудований і уникає мережевих затримок.
Postgres може бути надзвичайно швидким з збереженим tsvector + GIN, але треба підтвердити використання індекса і слідкувати за autovacuum.

3) Чи варто обчислювати to_tsvector під час запиту в Postgres?

Майже ніколи для реального навантаження. Зберігайте вектор (згенерована колонка або тригер), індексуйте його і тримайте запити простими, щоб планувальник міг використати індекс.

4) Чи може SQLite обробляти кілька інстансів додатка, що читають і пишуть?

Читання: так, особливо з WAL. Записи: вони серіалізуються. Якщо у вас часті записи з кількох інстансів — ви зіштовхнетеся з контенцією блокувань і спайками затримок.
Використовуйте чергу одного записувача або переходьте на серверну БД.

5) Чому я отримую різні результати між середовищами?

Дрейф токенайзера/конфігів. Токенайзери SQLite і конфігури текстового пошуку Postgres — це значення за замовчуванням, поки ви їх явно не зафіксуєте.
Зафіксуйте конфіги, версіонуйте їх і тестуйте Unicode та пунктуацію явно.

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

Почніть із зважування полів (title > body), фразових запитів для типових патернів і налаштування стоп-слів.
Далі додайте регресійну суїту золотих запитів, щоб релевантність не деградувала непомітно.

7) Коли припинити тюнити і переключитись з SQLite на Postgres?

Коли вузьке місце — конкурентність (записувачі), операційна видимість або коли вікна обслуговування/перебудови стають неприйнятними.
Якщо рішення вимагає «зробити користувачам чекання меншим через надію» — час змін.

8) Коли припинити використовувати Postgres FTS і взяти пошуковий движок?

Коли вам потрібна толерантність до помилок вводу, синоніми, складне скоринґування, фасети/агрегації в масштабі або розподілене індексування.
Також коли пошук стає продуктовою функцією зі своїми SLA і потрібними спеціалізованими інструментами.

9) Чи робить режим WAL у SQLite записи конкурентними?

Він покращує читання/запис конкурентність, але не write/write конкурентність. Один записувач усе одно виграє одночасно.
WAL у першу чергу запобігає блокуванню читачів під час записів.

10) Чи завжди GIN — правильний індекс для Postgres текстового пошуку?

Зазвичай так. GiST існує і може бути корисний у деяких випадках, але GIN — стандартний вибір для запитів про containment tsvector.
Перевіряйте з EXPLAIN, а не за вірою.

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

Якщо вирішуєте сьогодні:

• Оберіть SQLite FTS5, якщо ви можете дотримуватися моделі одного записувача, WAL, явного чекпоінтінгу і дисципліни бекапів файлів.
• Оберіть PostgreSQL tsearch, якщо вам потрібна конкурентність, інспекція, передбачувані операції і ви готові керувати сервером бази даних.

Якщо у вас вже болить:

1. Пройдіть швидку схему діагностики. Визначте, чи ви lock-, IO- чи CPU-bound.
2. Доведіть використання індекса (SQLite: MATCH; Postgres: EXPLAIN (ANALYZE, BUFFERS)).
3. Стабілізуйте обслуговування: чекпоінти WAL (SQLite), autovacuum/роздування (Postgres).
4. Закріпіть конфіги і додайте регресійні тести золотих запитів. Релевантність — це контракт, а не настрій.
5. Якщо конкурентність — обмеження, припиніть вести переговори з фізикою файлових блокувань і перенесіть навантаження на Postgres.

Доросла версія «вибору бази даних» — це вибір, які інциденти ви хочете дебажити.
SQLite дивує тим, наскільки далеко може зайти. PostgreSQL дивує тим, скільки контролю ви отримуєте.
Обирайте, виходячи з сюрпризів, які ви можете собі дозволити.