Question 1

Як індексувати jsonb-колонку?

Accepted Answer

Залежить від того, які запити робляться до 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

Question 2

Що таке SQL/JSON path і JSON_TABLE у PostgreSQL?

Accepted Answer

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

Question 3

Як зібрати відповідь API прямо в SQL через json_agg і jsonb_build_object?

Accepted Answer

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 - інструмент для гарячих місць, де виміряно, що звичайний підхід надто повільний.
Докладніше в документації: Агрегатні функції

Question 4

Як працює повнотекстовий пошук у PostgreSQL?

Accepted Answer

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:*'), складне ранжування з урахуванням популярності.
Для пошуку по сайту, документації, товарах середнього обсягу його часто достатньо - без окремого сервісу.
Докладніше в документації: Повнотекстовий пошук

Question 5

Як ранжувати результати пошуку й підсвічувати збіги?

Accepted Answer

Ранжування - 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=, StopSel=, 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). Докладніше в документації: Керування повнотекстовим пошуком

Middle: питання на співбесіді з теми «JSON і повнотекстовий пошук»