Питання на співбесіді: JSON і повнотекстовий пошук

-- settings = '{"theme": "dark", "notify": {"email": true}, "tags": ["php", "sql"]}'

settings -> 'notify'                 -- {"email": true}   (результат - jsonb)
settings ->> 'theme'                 -- dark              (результат - text)
settings -> 'notify' ->> 'email'     -- true
settings #>> '{notify,email}'        -- true (шлях масивом)
settings -> 'tags' -> 0              -- "php" (елемент масиву за індексом з нуля)

settings @> '{"theme": "dark"}'      -- містить цю пару
settings ? 'theme'                   -- є ключ верхнього рівня
settings ?| array['theme', 'lang']   -- є хоча б один з ключів
settings ?& array['theme', 'lang']   -- є всі ключі

Головна різниця, на якій помиляються: -> повертає jsonb, ->> - text. Для порівняння з рядком чи числом потрібен ->> і приведення типу:

WHERE (data ->> 'price')::numeric > 100
WHERE data ->> 'status' = 'active'

@> (містить) - найкорисніший оператор для фільтрів: його прискорює GIN-індекс, на відміну від порівняння через ->>.

Пастка в PHP: оператори ?, ?|, ?& конфліктують із заповнювачами параметрів PDO - драйвер прийме ? за параметр запиту. Варіанти:

• функції-аналоги: jsonb_exists(settings, 'theme'), jsonb_exists_any(), jsonb_exists_all();
• подвоєний знак ?? (PDO з PHP 7.4 розуміє його як буквальний ?);
• оператор @> там, де він підходить за змістом.

У Laravel запити до JSON-полів пишуть через стрілки в імені колонки - Query Builder перетворює їх на оператори PostgreSQL:

User::where('settings->theme', 'dark')->get();
User::whereJsonContains('settings->tags', 'php')->get();

Докладніше в документації: Оператори jsonb

Значення jsonb - одне ціле: «змінити поле на місці» неможливо, будь-яка зміна створює новий документ. Але PostgreSQL має функції й оператори, які збирають новий документ за вас:

-- встановити чи замінити значення за шляхом
UPDATE users SET settings = jsonb_set(settings, '{notify,email}', 'false')
WHERE id = 1;

-- jsonb_set створює лише останній ключ шляху: якщо об'єкта ui ще немає,
-- документ повернеться без змін. Проміжний об'єкт збирають явно:
UPDATE users SET settings = jsonb_set(
    settings, '{ui}', coalesce(settings -> 'ui', '{}') || '{"sidebar": "collapsed"}'
);

-- злити об'єкти: ключі правого перезапишуть лівий
UPDATE users SET settings = settings || '{"theme": "light", "lang": "uk"}';

-- видалити ключ / шлях / елемент масиву
UPDATE users SET settings = settings - 'beta_features';
UPDATE users SET settings = settings #- '{notify,sms}';

-- вставити в масив
UPDATE users SET settings = jsonb_insert(settings, '{tags,0}', '"laravel"');

Нюанси:

• Значення в jsonb_set - теж JSON: рядок пишуть з подвійними лапками всередині ('"collapsed"'), число й true/false - без.
• jsonb_set з NULL як новим значенням (не JSON-null, а SQL NULL) поверне NULL для всього документа - і стерте налаштування. Для безпечної роботи з NULL є jsonb_set_lax() (PostgreSQL 13+): за замовчуванням він запише JSON-null.
• || зливає лише верхній рівень: вкладений об'єкт праворуч повністю замінить вкладений об'єкт ліворуч, а не зіллється з ним.
• Конкурентні оновлення: два запити, що одночасно змінюють різні ключі одного документа через jsonb_set, не конфліктують - кожен перечитує актуальне значення в UPDATE. А от «прочитати весь JSON у застосунок → змінити → записати назад» дає загублене оновлення.

У Laravel:

$user->update(['settings->notify->email' => false]);   // перетвориться на jsonb_set

Сигнал до зміни схеми: якщо якесь поле JSON постійно оновлюється й фільтрується окремо, - йому, ймовірно, місце в окремій колонці.

Докладніше в документації: Функції обробки JSON

Залежить від того, які запити робляться до JSON.

1. GIN на весь документ - для пошуку за довільними ключами й значеннями:

CREATE INDEX products_attrs_gin ON products USING gin (attributes);

-- прискорює
WHERE attributes @> '{"color": "red"}'
WHERE attributes ? 'warranty'

2. GIN з jsonb_path_ops - менший і швидший, але підтримує лише @> (і jsonpath-оператори @?, @@):

CREATE INDEX products_attrs_path_gin ON products USING gin (attributes jsonb_path_ops);

Якщо запити лише «містить такі пари», - це кращий вибір: індекс у рази менший.

3. B-tree за виразом - коли фільтрують чи сортують за одним конкретним полем:

CREATE INDEX products_brand_idx ON products ((attributes ->> 'brand'));

-- прискорює
WHERE attributes ->> 'brand' = 'Bosch'
ORDER BY attributes ->> 'brand'

Зверніть увагу на подвійні дужки й на те, що вираз у запиті має точно збігатися з виразом в індексі. Для числових порівнянь - індекс за виразом з приведенням: ((attributes ->> 'price')::numeric).

Що не прискорює GIN: attributes ->> 'brand' = 'Bosch' - для нього потрібен B-tree за виразом (або переписати умову на @> '{"brand": "Bosch"}').

4. Згенерована колонка + звичайний індекс - коли поле використовується часто: воно стає звичайною колонкою з типом, статистикою й простими індексами, а JSON лишається джерелом.

Ціна GIN: повільніший запис і більший розмір. На таблицях з інтенсивними вставками це помітно; GIN збирає зміни у відкладений список (fastupdate), який вливається пізніше.

Перевірка: EXPLAIN має показувати Bitmap Index Scan по GIN-індексу, а не Seq Scan з фільтром.

Докладніше в документації: Індексування jsonb

SQL/JSON path (PostgreSQL 12+) - мова запитів до вмісту JSON за стандартом SQL, схожа на JSONPath: шляхи, фільтри, арифметика.

-- order.data = {"items": [{"sku": "A1", "qty": 2, "price": 100}, {"sku": "B2", "qty": 1, "price": 900}]}

SELECT jsonb_path_query_array(data, '$.items[*] ? (@.price > 500).sku') FROM orders;
-- ["B2"]

SELECT * FROM orders WHERE data @? '$.items[*] ? (@.qty > 1)';   -- є позиція з кількістю > 1
SELECT * FROM orders WHERE data @@ '$.items.size() > 3';         -- предикат

• $ - корінь документа, @ - поточний елемент у фільтрі ? (...).
• @? - чи повертає шлях хоч щось; @@ - результат предиката. Обидва прискорює GIN-індекс.
• Режими lax (за замовчуванням, прощає відсутні ключі) і strict (помилка на відсутніх).

JSON_TABLE (PostgreSQL 17+) перетворює JSON на звичайні рядки й колонки - без ручного jsonb_array_elements і приведень:

SELECT o.id, items.*
FROM orders o,
     JSON_TABLE(o.data, '$.items[*]'
         COLUMNS (
             sku   text    PATH '$.sku',
             qty   int     PATH '$.qty',
             price numeric PATH '$.price'
         )
     ) AS items
WHERE items.qty > 1;

Результат можна з'єднувати, групувати й фільтрувати, як будь-яку таблицю.

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

PostgreSQL 17 також додав функції стандарту JSON_EXISTS, JSON_QUERY, JSON_VALUE - їх зручно використовувати, якщо запити мають бути переносимими між СУБД.

Застереження: якщо такі запити - щоденна робота, а не виняток, дані, ймовірно, варто зберігати в таблицях, а не в JSON.

Докладніше в документації: Мова SQL/JSON path

PostgreSQL може зібрати вкладену JSON-структуру одним запитом - без N+1 і без складання масивів у застосунку.

SELECT jsonb_build_object(
    'id', u.id,
    'name', u.name,
    'orders', coalesce(
        (SELECT jsonb_agg(
                    jsonb_build_object('id', o.id, 'total', o.total, 'created_at', o.created_at)
                    ORDER BY o.created_at DESC
                )
         FROM orders o
         WHERE o.user_id = u.id),
        '[]'::jsonb
    )
) AS payload
FROM users u
WHERE u.id = 42;

Основні функції:

• jsonb_build_object(k1, v1, ...) - об'єкт з пар ключ-значення;
• jsonb_build_array(...) - масив;
• jsonb_agg(expr ORDER BY ...) - агрегат: рядки групи → JSON-масив;
• jsonb_object_agg(key, value) - агрегат: рядки → JSON-об'єкт;
• row_to_json(t) / to_jsonb(t) - увесь рядок у JSON.

Пастки:

• jsonb_agg по порожній групі повертає NULL, а не [] - звідси coalesce(..., '[]').
• jsonb не зберігає порядок ключів (впорядковує їх), а json - зберігає. Якщо порядок полів у відповіді важливий, - json_build_object / json_agg.
• Числа numeric потрапляють у JSON як числа з повною точністю; клієнт на JavaScript може їх округлити - гроші краще віддавати рядками чи в копійках.

Коли це доречно:

• Ендпоінти з глибоко вкладеними даними, де збирання в застосунку дає багато запитів.
• Експорти й звіти, де формат стабільний.
• Сервіси на кшталт PostgREST і Hasura будують усі відповіді саме так.

Коли - ні: у звичайному Laravel-застосунку форматування відповіді належить API Resources: там логіка видимості полів, перетворень і версій. SQL-збирання JSON - інструмент для гарячих місць, де виміряно, що звичайний підхід надто повільний.

Докладніше в документації: Агрегатні функції

LIKE '%черги%' шукає підрядок: не знаходить інших форм слова, не ранжує результати й не використовує звичайні індекси. Повнотекстовий пошук працює зі словами.

Два типи:

• tsvector - документ, розібраний на нормалізовані слова (лексеми) з позиціями;
• tsquery - запит з операторами & (і), | (або), ! (не), <-> (наступне слово).

SELECT to_tsvector('english', 'Running background jobs with queues');
-- 'background':2 'job':3 'queue':5 'run':1

SELECT to_tsvector('english', 'Running background jobs with queues')
    @@ to_tsquery('english', 'queue & job');   -- true

Конфігурація (english) визначає словники: стемінг (jobs → job, running → run), стоп-слова (with викинуто), нижній регістр.

Зручні функції для запиту від користувача:

websearch_to_tsquery('english', 'laravel queues -redis "failed jobs"')   -- синтаксис як у пошуковиках
plainto_tsquery('english', 'laravel queues')                            -- просто слова через «і»

Індекс - GIN за tsvector, найкраще збереженим у згенерованій колонці:

ALTER TABLE posts ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED;

CREATE INDEX posts_search_idx ON posts USING gin (search);

SELECT * FROM posts WHERE search @@ websearch_to_tsquery('english', 'queue retries');

Чого повнотекстовий пошук PostgreSQL не вміє з коробки: виправляти опечатки (для цього pg_trgm), шукати синоніми без налаштування словників, «пошук під час набору» за частиною слова (лише префікси: 'que:*'), складне ранжування з урахуванням популярності.

Для пошуку по сайту, документації, товарах середнього обсягу його часто достатньо - без окремого сервісу.

Докладніше в документації: Повнотекстовий пошук

Ранжування - ts_rank (за частотою слів запиту) або ts_rank_cd (ще й за близькістю слів одне до одного):

SELECT id, title,
       ts_rank(search, query) AS rank
FROM posts, websearch_to_tsquery('english', 'queue retries') AS query
WHERE search @@ query
ORDER BY rank DESC
LIMIT 20;

Ваги частин документа. setweight позначає частини літерами A-D (за спаданням важливості). Збіг у заголовку (A) важить більше, ніж у тексті (D):

setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('english', summary), 'B') ||
setweight(to_tsvector('english', body), 'D')

Ваги можна змінити в самому ts_rank: ts_rank('{0.1, 0.2, 0.4, 1.0}', search, query) - масив для D, C, B, A.

Нормалізація за довжиною: без неї довгий документ має перевагу просто тому, що містить більше слів. Третій аргумент ts_rank задає нормалізацію: ts_rank(search, query, 32) - ділити на (1 + rank) - чи 1 - на логарифм довжини документа.

Підсвічування збігів - ts_headline повертає фрагмент тексту з позначеними словами:

SELECT ts_headline('english', body, query,
       'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15, MaxFragments=2')
FROM posts, websearch_to_tsquery('english', 'queue retries') AS query
WHERE search @@ query;

Нюанси продуктивності:

• ts_rank обчислюється для кожного збігу - на запиті з сотнями тисяч збігів сортування за рангом повільне. Індекс прискорює лише пошук, не ранжування.
• ts_headline повільна: вона розбирає сирий текст заново. Викликайте її лише для рядків поточної сторінки - у зовнішньому запиті після LIMIT.
• Екранування: ts_headline повертає текст разом з вашими мітками; якщо текст містить HTML від користувачів, його треба екранувати до підсвічування, інакше вийде XSS.

Коли ранжування PostgreSQL замало - урахування популярності, свіжості, персоналізації, «можливо, ви мали на увазі» - це вже задача пошукового рушія (Meilisearch, Elasticsearch, Typesense).

Докладніше в документації: Керування повнотекстовим пошуком

Вбудованої української конфігурації в PostgreSQL немає - серед стандартних (english, russian, german, ...) української мови немає й у PostgreSQL 18. Перевірити: SELECT cfgname FROM pg_ts_config;.

Варіанти:

1. Конфігурація simple - без стемінгу й стоп-слів, лише нижній регістр:

to_tsvector('simple', 'Черги в Laravel')   -- 'черги':1 'в':2 'laravel':3

Працює одразу, але «черга» не знайде «черги»: форми слова не зводяться до основи. Частково рятує префіксний пошук ('черг:*').

2. Hunspell-словник для української - справжня нормалізація форм. Файли словника (.dict, .affix, стоп-слова) кладуть у каталог tsearch_data сервера, а потім:

CREATE TEXT SEARCH DICTIONARY ukrainian_hunspell (
    TEMPLATE = ispell, DictFile = uk_ua, AffFile = uk_ua, StopWords = ukrainian
);

CREATE TEXT SEARCH CONFIGURATION ukrainian (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION ukrainian
    ALTER MAPPING FOR word, hword, hword_part
    WITH ukrainian_hunspell, simple;

Потрібен доступ до файлової системи сервера - на керованих базах (RDS, Cloud SQL) це зазвичай неможливо.

3. unaccent і триграми - доповнення: unaccent прибирає діакритику, а pg_trgm знаходить схожі слова й опечатки, частково компенсуючи відсутність стемінгу.

Пастка, на якій ламається пошук кирилицею, - локаль бази. Приведення до нижнього регістру в повнотекстовому пошуку й у lower() залежить від LC_CTYPE бази. З локаллю C (часто так створюють базу в Docker і для тестів) кирилиця не перетворюється на малі літери:

SELECT lower('Черги');                                      -- 'Черги' при LC_CTYPE = C
SELECT to_tsvector('simple', 'Черги') @@ to_tsquery('simple', 'черги');   -- false

Пошук «тихо» не знаходить слів з великої літери. Рішення - створювати базу з UTF-8 локаллю (uk_UA.UTF-8, ICU чи вбудований провайдер C.UTF-8 з PostgreSQL 17).

Реалістичний вибір: для серйозного пошуку українською часто беруть окремий рушій (Meilisearch, Typesense, Elasticsearch з українським аналізатором) - там морфологія, опечатки й ранжування вже налаштовані. PostgreSQL лишається для фільтрів і простого пошуку.

Докладніше в документації: Словники повнотекстового пошуку

Є три способи зберігати пошуковий вектор, і в кожного свої компроміси.

1. Індекс за виразом - нічого не зберігати:

CREATE INDEX posts_fts_idx ON posts USING gin (to_tsvector('english', title || ' ' || body));

SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'queue');

• Не займає місця в таблиці.
• Вираз у запиті має дослівно збігатися з виразом в індексі (разом з конфігурацією - функція з одним аргументом нестабільна й в індексі не допускається).
• ts_rank і перевірки збігу обчислюють вектор заново для кожного рядка - повільно.

2. Згенерована колонка - найпростіший надійний варіант:

ALTER TABLE posts ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED;

CREATE INDEX posts_search_idx ON posts USING gin (search);

• Завжди актуальна, без коду в застосунку.
• Вектор обчислено заздалегідь - ранжування швидке.
• Обмеження: лише колонки того самого рядка.

3. Тригер - коли вектор має включати дані з інших таблиць: назви тегів, ім'я автора, назву категорії.

CREATE FUNCTION posts_search_update() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.search := setweight(to_tsvector('english', NEW.title), 'A')
               || setweight(to_tsvector('english',
                      coalesce((SELECT string_agg(name, ' ') FROM tags t
                                JOIN post_tag pt ON pt.tag_id = t.id
                                WHERE pt.post_id = NEW.id), '')), 'B');
    RETURN NEW;
END $$;

Мінус - зміна в пов'язаній таблиці (перейменували тег) сама вектор не оновить: потрібні тригери й там або фонове перерахування.

coalesce обов'язковий: to_tsvector(NULL) дає NULL, а NULL || вектор - теж NULL. Один порожній опис - і весь документ випадає з пошуку.

Рекомендація: згенерована колонка за замовчуванням; тригер чи перерахування в черзі - коли потрібні дані з інших таблиць; індекс за виразом - для простих випадків без ранжування.

Докладніше в документації: Таблиці й індекси для пошуку

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

Пошуку PostgreSQL зазвичай достатньо, коли:

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

Окремий рушій (Meilisearch, Typesense, Elasticsearch/OpenSearch) потрібен, коли:

• Якість пошуку - частина продукту: опечатки, синоніми, морфологія мов без готових словників (як українська), пошук під час набору, підказки, «можливо, ви мали на увазі».
• Фасетний пошук з підрахунком кількості для кожного значення фільтра на великих обсягах.
• Складне ранжування: популярність, свіжість, персоналізація, бусти полів, налаштування релевантності без зміни коду.
• Обсяг і навантаження: пошукові запити конкурують з основним навантаженням бази, і їх хочеться винести на окремі ресурси.
• Аналітика логів і агрегати по величезних обсягах тексту (Elasticsearch/OpenSearch).

Ціна окремого рушія:

• Синхронізація: дані потрапляють в індекс із затримкою; потрібна черга оновлень і переіндексація при зміні схеми. У Laravel це бере на себе Scout, але проблеми розсинхронізації все одно треба відстежувати.
• Дві системи правди для пошуку й даних: фільтр за правами доступу треба дублювати в індексі.
• Інфраструктура: пам'ять, бекапи, оновлення, моніторинг.

Поширений проміжний шлях: почати з PostgreSQL (повнотекстовий пошук + pg_trgm) і перейти на рушій, коли з'явиться конкретна вимога, якої база не закриває. Scout з драйвером database дозволяє почати з бази й пізніше змінити драйвер майже без змін у коді.

Докладніше в документації: Повнотекстовий пошук

Семантичний пошук знаходить документи за змістом, а не за збігом слів: запит «як не втратити завдання при падінні воркера» знайде статтю про повторні спроби в чергах, навіть без спільних слів.

Текст перетворюють на вектор-ембединг (масив з сотень чи тисяч чисел) за допомогою моделі (OpenAI, Voyage, локальні моделі). Схожі за змістом тексти мають близькі вектори.

Розширення pgvector додає тип vector і оператори відстані:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content text NOT NULL,
    embedding vector(1536)
);

-- 5 найближчих за косинусною відстанню
SELECT id, content
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

Оператори: <-> - евклідова відстань, <=> - косинусна, <#> - від'ємний скалярний добуток.

Індекси для наближеного пошуку (ANN) - точний пошук перебирає всі вектори, на мільйонах це повільно:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

• HNSW - швидкий і точний пошук, але повільніша побудова й більше пам'яті.
• IVFFlat - швидша побудова, менше пам'яті, але потрібні дані для навчання (створювати після наповнення таблиці) і нижча точність.

Наближені індекси можуть пропустити частину справжніх сусідів - точність регулюється параметрами (hnsw.ef_search).

Переваги вектора в PostgreSQL:

• Векторний пошук в одному запиті з фільтрами (WHERE tenant_id = ? AND published) і транзакційними даними.
• Немає окремої векторної бази, її синхронізації й бекапів.

Практичні нюанси:

• Гібридний пошук: поєднання векторного й повнотекстового пошуку (наприклад, через Reciprocal Rank Fusion) зазвичай дає кращі результати, ніж кожен окремо.
• Фільтри з HNSW: при дуже вибірковому фільтрі індекс може повернути замало результатів - нові версії pgvector мають ітеративне сканування для цього випадку.
• Ембединги прив'язані до моделі: змінили модель - перераховуйте всі вектори.
• Розмір: вектор на 1536 значень - ~6 КБ на рядок, плюс індекс.

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