Питання на співбесіду з SQL

SQL

Чим HAVING відрізняється від WHERE і як працює GROUP BY? ЧАСТО ПИТАЮТЬ

SQL·Junior ·GROUP BY ·HAVING ·агрегати

WHERE фільтрує рядки до групування й не бачить агрегатів, GROUP BY згортає рядки в групи, HAVING фільтрує вже готові групи за значенням агрегату.

Як питають

Чому запит із WHERE COUNT(*) > 3 падає з помилкою?

У якому порядку база виконує WHERE, GROUP BY і HAVING?

Чи можна писати HAVING без GROUP BY і що тоді буде?

Чому MySQL лається на колонку, якої немає в GROUP BY?

Пояснення

Щоб зрозуміти різницю, треба тримати в голові логічний порядок виконання запиту: FROM і JOIN збирають рядки, WHERE фільтрує їх по одному, GROUP BY згортає рядки в групи за однаковими значеннями ключа групування, всередині груп обчислюються агрегати (COUNT, SUM, AVG, MIN, MAX), HAVING відкидає непотрібні групи, і тільки потім працюють SELECT, ORDER BY та LIMIT. Це не порядок написання, а порядок сенсу — фізично планувальник може робити по-своєму, але результат зобовʼязаний збігатися з цією моделлю.

Звідси головна відповідь: WHERE не бачить COUNT(*), бо на момент його роботи груп ще не існує — є лише окремі рядки, і запит WHERE COUNT(*) >= 3 падає з помилкою (Invalid use of group function у MySQL, aggregate functions are not allowed in WHERE у PostgreSQL). HAVING, навпаки, виконується після агрегації, тому оперує вже порахованими значеннями. Симетрично HAVING майже не має сенсу для звичайної колонки: після групування окремих рядків уже немає, є ключ групування й агрегати.

Другий бік — продуктивність. Технічно можна винести умову на звичайну колонку в HAVING (MySQL це проковтне, бо дозволяє в HAVING посилатися на колонки й аліаси SELECT; PostgreSQL стандартно вимагатиме агрегат або колонку з GROUP BY). Але тоді база згрупує зайві рядки й лише потім їх викине, а індекс по цій колонці не спрацює для звуження пошуку. Правило просте: умова про значення рядка — у WHERE, умова про результат агрегації — у HAVING.

Після GROUP BY у SELECT дозволені тільки колонки з ключа групування та агрегати. MySQL до 5.7.5 із вимкненим ONLY_FULL_GROUP_BY мовчки повертав довільне значення з групи, і це давало нестабільні результати між запусками; з 5.7.5 режим увімкнений за замовчуванням, а PostgreSQL поводився так завжди. Виняток — функційна залежність: якщо групувати за первинним ключем (GROUP BY u.id), інші колонки цієї таблиці брати можна, бо вони визначені однозначно (PostgreSQL з 9.1, MySQL 8).

Дві деталі, на яких найчастіше помиляються джуни. Перша: COUNT(*) рахує рядки групи, а COUNT(column), SUM, AVG ігнорують NULL — тому COUNT(o.coupon) менший за COUNT(*), і це не баг. Друга: GROUP BY не гарантує сортування. У старих MySQL воно було побічним ефектом реалізації, у 8.0 неявне сортування й модифікатори ASC/DESC для GROUP BY прибрані, тож потрібний порядок задають явним ORDER BY.

КодSQL

-- Питання: користувачі, у яких від 3 оплачених замовлень за 2026 рік,
-- на суму понад 10000, найбільші зверху.
SELECT
    u.id,
    u.email,
    COUNT(*)        AS paid_orders,   -- рядків у групі
    SUM(o.total)    AS revenue,       -- SUM ігнорує NULL
    COUNT(o.coupon) AS with_coupon    -- рахує лише не-NULL купони
FROM users u
JOIN orders o ON o.user_id = u.id
-- WHERE працює до групування: відсікає рядки, може використати індекс
WHERE o.status = 'paid'
  AND o.created_at >= '2026-01-01'
-- GROUP BY згортає рядки в групи; u.email можна брати,
-- бо u.id — первинний ключ (функційна залежність)
GROUP BY u.id, u.email
-- HAVING працює після агрегації: тут і тільки тут доступні COUNT/SUM
HAVING COUNT(*) >= 3 AND SUM(o.total) > 10000
-- GROUP BY не сортує сам: у MySQL 8.0 неявне сортування прибрано
ORDER BY revenue DESC
LIMIT 20;

-- Помилка: агрегата у WHENE ще не існує, груп немає
-- WHERE COUNT(*) >= 3        -- ERROR: Invalid use of group function / aggregate not allowed

-- Кілька лічильників за один прохід (PostgreSQL)
SELECT user_id,
       COUNT(*)                                   AS all_orders,
       COUNT(*) FILTER (WHERE status = 'paid')    AS paid_orders
FROM orders
GROUP BY user_id;

Що хоче почути інтервʼюер

Логічний порядок виконання: FROM/JOIN → WHERE → GROUP BY → агрегати → HAVING → SELECT → ORDER BY → LIMIT; звідси все інше випливає.

Що WHERE не бачить COUNT/SUM, бо на момент його роботи груп ще немає, а HAVING бачить, бо працює вже після агрегації.

Що умову на звичайну колонку треба ставити у WHERE, а не в HAVING: менше рядків для групування і доступний індекс.

Що після GROUP BY у SELECT можна брати лише колонки з GROUP BY або агрегати — ONLY_FULL_GROUP_BY у MySQL з 5.7.5 увімкнено за замовчуванням.

Що COUNT(*) рахує рядки, а COUNT(col) і SUM/AVG ігнорують NULL — це різні числа.

Типові помилки

Писати WHERE COUNT(*) > 3 — синтаксична помилка, агрегат у WHERE неможливий.

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

Плутати «фільтр до групування» і «фільтр після»: WHERE status = 'paid' і HAVING SUM(...) > 0 дають різні відповіді на різні питання.

Вважати, що GROUP BY сам сортує результат: у MySQL 8.0 неявне сортування прибрано, без ORDER BY порядок не визначений.

Використовувати COUNT(column) там, де треба порахувати всі рядки групи, і дивуватися меншому числу через NULL.

ПОРАДА

Скажіть одним реченням: «WHERE — про рядки, HAVING — про групи», і одразу назвіть порядок виконання. Далі додайте, що умову на звичайну колонку завжди тримають у WHERE, бо це менше роботи для GROUP BY.

Додаткові питанняЗ ВІДПОВІДЯМИ

Так. Без GROUP BY весь результат — це одна група, тому `SELECT COUNT(*) FROM orders HAVING COUNT(*) > 100` поверне один рядок, якщо замовлень більше ста, і жодного, якщо менше. На практиці так пишуть рідко; корисніше це як ілюстрація того, що HAVING завжди працює над групами, просто інколи група одна.

До MySQL 5.7.5 режим ONLY_FULL_GROUP_BY був вимкнений і база мовчки брала довільне значення з групи — звідси нестабільні результати. З 5.7.5 він увімкнений за замовчуванням і такий запит падає з помилкою, як у PostgreSQL. Виняток в обох базах — функційна залежність: якщо групувати за первинним ключем таблиці (`GROUP BY u.id`), решту колонок цієї таблиці брати можна, бо вони однозначно визначені ключем. Це вміє PostgreSQL з 9.1 і MySQL 8. Обхід через `ANY_VALUE()` у MySQL є, але це визнання, що запит неточний.

У WHERE. WHERE відсіє неоплачені рядки до групування, тож GROUP BY отримає менший набір, а індекс по `status` або складений індекс може взагалі спрацювати. Той самий фільтр у HAVING неможливий для звичайної колонки (її вже немає після агрегації) або дасть інший результат, якщо загорнути його в агрегат. Правило: умова на значення рядка — WHERE, умова на результат COUNT/SUM/AVG — HAVING.

Умовним агрегатом. У PostgreSQL це стандартний `COUNT(*) FILTER (WHERE status = 'paid')`, у MySQL — `SUM(status = 'paid')` або сумісний з обома `COUNT(CASE WHEN status = 'paid' THEN 1 END)`: CASE повертає NULL для решти рядків, а COUNT ігнорує NULL. Так один прохід по таблиці дає кілька різних лічильників без підзапитів і JOIN-ів.

Сторінка питання →

SQL

Чим відрізняються INNER JOIN і LEFT JOIN? ЧАСТО ПИТАЮТЬ

SQL·Junior ·JOIN ·SQL

INNER JOIN лишає лише ті рядки, для яких знайшлася пара в обох таблицях; LEFT JOIN лишає всі рядки лівої таблиці, підставляючи NULL там, де пари немає.

Як питають

Чому LEFT JOIN повернув менше рядків, ніж очікувалось?

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

У чому різниця між умовою в ON і в WHERE?

Пояснення

INNER JOIN повертає лише ті рядки, для яких умова звʼязку знайшла пару в обох таблицях: користувачі без замовлень у результат не потрапляють. LEFT JOIN бере всі рядки лівої таблиці й додає до них збіги з правої; якщо збігу немає, колонки правої таблиці заповнюються NULL. RIGHT JOIN є дзеркалом LEFT, а FULL OUTER JOIN обʼєднує обидва й у MySQL відсутній.

Найважливіше для практики — різниця між умовою в ON і у WHERE. ON описує, як шукати пару, і при LEFT JOIN відсутність пари дає NULL. WHERE застосовується вже до зібраного результату, і порівняння NULL з чим завгодно не є true, тому рядки без пари зникають. Так LEFT JOIN orders o ... WHERE o.status = 'paid' непомітно стає INNER JOIN. Якщо потрібні всі користувачі, а замовлення лише оплачені, умова на статус іде в ON.

Цей самий NULL є інструментом: WHERE o.id IS NULL після LEFT JOIN знаходить рядки без відповідника, і це стандартний anti-join поруч із NOT EXISTS. NOT IN з підзапитом, який може повернути NULL, дає порожній результат, тому в таких запитах його уникають.

Окрема пастка — множення рядків. JOIN один-до-багатьох повертає по рядку на кожен збіг, тому агрегати після нього подвоюються. Агрегувати праву таблицю варто до JOIN, у підзапиті або CTE.

КодSQL

-- INNER: лише користувачі, у яких є замовлення
SELECT u.id, o.id AS order_id
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT: усі користувачі; без замовлень order_id буде NULL
SELECT u.id, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Пастка: умова у WHERE відкидає NULL-рядки, це вже фактично INNER JOIN
SELECT u.id, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

-- Правильно: умова на праву таблицю в ON, користувачі без оплат лишаються
SELECT u.id, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';

-- Користувачі без жодного замовлення
SELECT u.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Те саме без JOIN, безпечно до NULL
SELECT u.id FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Що хоче почути інтервʼюер

Чітке визначення обох через результат: INNER це перетин, LEFT це вся ліва таблиця плюс збіги з правої або NULL.

Що умова на праву таблицю у WHERE відфільтровує NULL-рядки й перетворює LEFT JOIN на INNER, а в ON вона обмежує лише збіг.

Що IS NULL по колонці правої таблиці після LEFT JOIN є стандартним способом знайти рядки без відповідника, як і NOT EXISTS.

Що JOIN на звʼязок один-до-багатьох множить рядки, тому COUNT і SUM після нього потребують DISTINCT або підзапиту.

Що RIGHT JOIN це дзеркало LEFT, а FULL OUTER JOIN є у PostgreSQL і відсутній у MySQL.

Типові помилки

Казати, що різниця лише у швидкості.

Писати умову на праву таблицю у WHERE й отримувати INNER JOIN, не розуміючи чому.

Рахувати COUNT(*) після JOIN один-до-багатьох і отримувати кількість замовлень замість кількості користувачів.

Використовувати NOT IN з підзапитом, який може повернути NULL: результат порожній, бо порівняння з NULL не є true.

Не знати, що для LEFT JOIN потрібен індекс на колонці звʼязку правої таблиці, інакше кожен рядок лівої сканує праву.

ПОРАДА

Саме приклад із WHERE проти ON відрізняє того, хто розуміє JOIN, від того, хто вивчив визначення. Намалюйте два кола або наведіть таблицю з трьома рядками.

Додаткові питанняЗ ВІДПОВІДЯМИ

LEFT JOIN спершу будує результат: для користувачів без замовлень усі колонки orders дорівнюють NULL. Потім WHERE o.status = 'paid' перевіряє NULL = 'paid', що не є true, і такі рядки відкидаються. Лишаються лише користувачі зі збігом, тобто те саме, що INNER JOIN. Умова в ON застосовується під час пошуку пари, тому користувач без оплачених замовлень лишається з NULL.

LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL, або NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id). NOT EXISTS часто читається краще й безпечний до NULL, на відміну від NOT IN. Оптимізатори PostgreSQL і MySQL 8 виконують обидва варіанти як anti-join, тому продуктивність зазвичай однакова.

JOIN один-до-багатьох повертає по рядку на кожен збіг: користувач з трьома замовленнями зʼявиться тричі. SUM(u.balance) додасть баланс тричі. Рішення: агрегувати праву таблицю в підзапиті чи CTE до JOIN, або рахувати COUNT(DISTINCT u.id). Це найчастіша помилка у звітах.

Декартів добуток: кожен рядок лівої таблиці з кожним рядком правої, без умови. Потрібен для генерації комбінацій, наприклад сітка дат і категорій для звіту з нулями, або для JOIN з таблицею з одним рядком параметрів. Випадковий CROSS JOIN через забуту умову дає мільйони рядків.

Сторінка питання →

SQL

Як NULL поводиться в порівняннях, агрегатах і унікальних індексах? ЧАСТО ПИТАЮТЬ

SQL·Junior ·тризначна логіка ·UNIQUE ·агрегати

NULL — це «невідомо», а не значення: будь-яке порівняння з ним дає UNKNOWN і рядок не проходить WHERE, агрегати мовчки пропускають NULL замість того щоб рахувати їх нулями, а UNIQUE вважає різні NULL різними, тому дублікати проходять.

Як питають

Чому `WHERE bonus = NULL` не повертає жодного рядка?

Чому після додавання умови `status <> 'paid'` зі звіту зникли рядки, у яких статус узагалі не заповнений?

Скільки рядків з однаковим NULL можна вставити в колонку з UNIQUE індексом?

Чому AVG по колонці дає більше число, ніж очікує бухгалтерія?

Пояснення

NULL у SQL означає не «порожньо» і не «нуль», а «значення невідоме». Через це логіка стає тризначною: вираз може бути TRUE, FALSE або UNKNOWN. bonus = NULL — це питання «чи дорівнює невідоме число тисячі», відповідь на яке невідома, тому результат UNKNOWN. WHERE пропускає далі лише рядки з TRUE, тож і = NULL, і <> NULL завжди дають порожній результат — без помилки й без попередження. Заперечення теж не допомагає: NOT UNKNOWN — це знову UNKNOWN. Саме звідси найпоширеніший баг у звітах: після додавання умови status <> 'paid' із вибірки тихо зникають усі рядки, де status взагалі не заповнений. Перевіряти наявність значення можна лише через IS NULL та IS NOT NULL, а порівнювати дві потенційно порожні колонки — через IS NOT DISTINCT FROM у PostgreSQL або <=> у MySQL.

Той самий механізм ламає NOT IN з підзапитом. Якщо підзапит повернув хоч один NULL, вираз id NOT IN (1, NULL) розкладається в id <> 1 AND id <> NULL, друга частина назавжди UNKNOWN, і результат ніколи не стане TRUE. Запит повертає нуль рядків, хоча дані на місці. Безпечна заміна — NOT EXISTS, який працює через звичайне зіставлення рядків і на NULL не спотикається; альтернатива — прибрати NULL прямо в підзапиті через WHERE col IS NOT NULL. Це одна з тих помилок, які проходять код-рев'ю і виявляються вже на проді, бо запит синтаксично коректний.

В агрегатах правило інше: SUM, AVG, MIN, MAX і COUNT(col) просто пропускають NULL, а не підставляють нуль. Тому COUNT(*) рахує рядки, COUNT(bonus) — лише заповнені бонуси, а AVG(bonus) ділить суму на кількість непорожніх значень. Для трьох співробітників із бонусами 1000, NULL і 0 середнє буде 500, а не 333.33 — і саме тут звіт розходиться з очікуваннями бізнесу. Якщо порожнє значення за змістом дорівнює нулю, це треба сказати явно: AVG(COALESCE(bonus, 0)). Окремо варто памʼятати, що SUM по порожній вибірці повертає NULL, а не 0, тому в коді така сума теж потребує COALESCE.

А от GROUP BY, DISTINCT, UNION і віконні PARTITION BY користуються третім правилом: там усі NULL вважаються однаковими й потрапляють в одну групу. Виходить, що в одному запиті NULL може бути одночасно «не рівним самому собі» у WHERE і «рівним самому собі» у GROUP BY. Унікальний індекс належить до першої категорії: NULL для нього різні, тому UNIQUE (email) спокійно пропускає скільки завгодно рядків із порожнім email — і в MySQL, і в PostgreSQL (для контрасту, SQL Server дозволяє лише один NULL). З PostgreSQL 15 зʼявився явний перемикач UNIQUE NULLS NOT DISTINCT; у MySQL 8.4 такого немає, там або NOT NULL, або перевірка на рівні застосунку, або згенерована колонка з підстановкою.

Практичний висновок: NOT NULL має бути станом за замовчуванням, а nullable-колонка — свідомим рішенням для випадків, де «невідомо» справді окремий стан (deleted_at, closed_at). Для сум і лічильників краще NOT NULL DEFAULT 0, бо будь-яка арифметика з NULL дає NULL і помилка розповзається далі по обчисленнях. Компроміс у зворотний бік теж є: заміна NULL на «магічні» значення на кшталт 0, порожнього рядка чи '1970-01-01' робить дані брехливими та псує агрегати, тож замінювати NULL заглушками лише щоб уникнути IS NULL — гірше, ніж навчитися з ним працювати.

КодSQL

-- Дані: employees
-- id | name  | manager_id | bonus | email
--  1 | Оля   |       NULL |  1000 | [email protected]
--  2 | Іван  |          1 |  NULL | NULL
--  3 | Петро |       NULL |     0 | NULL

-- 1. Порівняння з NULL дає UNKNOWN, а WHERE пропускає лише TRUE
SELECT * FROM employees WHERE bonus = NULL;   -- 0 рядків завжди
SELECT * FROM employees WHERE bonus IS NULL;  -- Іван

-- 2. Заперечення не рятує: NOT(UNKNOWN) теж UNKNOWN
SELECT count(*) FROM employees WHERE bonus <> 1000;  -- 1 (Петро), Іван мовчки зник

-- 3. NOT IN по nullable-колонці не поверне нічого
SELECT * FROM employees WHERE id NOT IN (SELECT manager_id FROM employees);  -- 0 рядків
-- Правильно: NOT EXISTS коректно працює з NULL
SELECT e.* FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.id);

-- 4. Агрегати пропускають NULL, а не рахують його за 0
SELECT count(*)     AS rows_total,  -- 3
       count(bonus) AS with_bonus,  -- 2
       sum(bonus)   AS total,       -- 1000
       avg(bonus)   AS avg_bonus    -- 500, а не 333.33
FROM employees;

-- 5. GROUP BY і DISTINCT, навпаки, збирають усі NULL в одну групу
SELECT bonus, count(*) FROM employees GROUP BY bonus;  -- є рядок bonus = NULL

-- 6. UNIQUE вважає NULL різними: обидва рядки з email = NULL пройдуть
CREATE UNIQUE INDEX employees_email_uniq ON employees (email);
-- PostgreSQL 15+: заборонити повтори NULL явно
CREATE UNIQUE INDEX employees_email_strict ON employees (email) NULLS NOT DISTINCT;

Що хоче почути інтервʼюер

Що SQL має тризначну логіку: TRUE, FALSE, UNKNOWN, і WHERE пропускає лише TRUE, тому і `= NULL`, і `<> NULL` дають порожній результат.

Що перевіряти треба через `IS NULL` / `IS NOT NULL`, а для порівняння двох значень, з яких обидва можуть бути NULL, є `IS NOT DISTINCT FROM` у PostgreSQL і оператор `<=>` у MySQL.

Що `COUNT(*)` рахує рядки, а `COUNT(col)`, `SUM`, `AVG` пропускають NULL: AVG ділить на кількість непорожніх значень, а не на кількість рядків.

Що GROUP BY і DISTINCT, навпаки, вважають усі NULL однією групою — це інше правило, ніж у порівняннях.

Що UNIQUE індекс у MySQL і PostgreSQL дозволяє скільки завгодно NULL, і що в PostgreSQL 15+ це вимикається через `UNIQUE NULLS NOT DISTINCT`.

Що `NOT IN` з підзапитом, який може повернути NULL, не поверне нічого, і замінюється на `NOT EXISTS`.

Типові помилки

Писати `WHERE deleted_at = NULL` або `WHERE deleted_at != NULL` замість `IS NULL` / `IS NOT NULL`.

Вважати, що умова `status <> 'paid'` включає рядки з NULL у status: вони мовчки випадають зі звіту.

Думати, що `SUM(bonus)` рахує NULL як 0, а `AVG(bonus)` ділить на загальну кількість рядків.

Розраховувати, що UNIQUE індекс на nullable-колонці не пустить два порожні значення — пустить, і в MySQL, і в PostgreSQL.

Використовувати `NOT IN (SELECT ...)` по nullable-колонці й дивуватись порожньому результату замість `NOT EXISTS`.

Плутати NULL з порожнім рядком `''` або з 0: у SQL це три різні значення (в Oracle порожній рядок і є NULL, у MySQL і PostgreSQL — ні).

ПОРАДА

Скажіть одним реченням: «NULL — це не значення, а відсутність знання, тому порівняння з ним дає не FALSE, а UNKNOWN». І одразу назвіть три різні правила: у WHERE усі NULL різні, у GROUP BY і DISTINCT — однакові, в агрегатах — просто пропускаються.

Додаткові питанняЗ ВІДПОВІДЯМИ

У PostgreSQL є стандартний оператор `a IS NOT DISTINCT FROM b`: він дає TRUE, коли обидва NULL, і FALSE, коли NULL лише з одного боку. У MySQL цього синтаксису немає, натомість є null-safe оператор `a <=> b` з такою ж семантикою. Портативний, але повільніший варіант — `a = b OR (a IS NULL AND b IS NULL)`; він гірший тим, що заважає планувальнику використати індекс. Такі порівняння часто потрібні в UPDATE-тригерах і в синхронізації даних, де треба зрозуміти, чи значення справді змінилось.

Бо підзапит повертає серед іншого NULL. `id NOT IN (1, NULL)` розкладається в `id <> 1 AND id <> NULL`; друга частина завжди UNKNOWN, тому весь вираз ніколи не буде TRUE — у кращому разі UNKNOWN. Результат: жодного рядка, і жодної помилки. Лікується або `NOT EXISTS (SELECT 1 FROM employees e WHERE e.manager_id = t.id)`, який коректно працює з NULL, або явним `WHERE manager_id IS NOT NULL` у підзапиті. `IN` цієї проблеми не має: там NULL просто не додає збігів.

Стандарт залишає це на розсуд СУБД. PostgreSQL вважає NULL найбільшим значенням: при `ASC` вони в кінці, при `DESC` — на початку; змінюється через `ORDER BY col ASC NULLS FIRST`. MySQL вважає NULL найменшим: при `ASC` вони перші, і `NULLS FIRST/LAST` там не підтримується — обходять через `ORDER BY col IS NULL, col`. Тому запит із сортуванням по nullable-колонці на MySQL і PostgreSQL дає різний порядок сторінок, і це реальний баг при міграції між базами.

CHECK пропускає рядок, коли умова дає UNKNOWN: `CHECK (price > 0)` не завадить вставити рядок із `price = NULL`. Щоб закрити цю дірку, потрібен окремий `NOT NULL` на колонці або `CHECK (price IS NOT NULL AND price > 0)`. Зовнішній ключ поводиться так само: nullable FK з NULL не перевіряється на існування батьківського рядка. Практичний висновок — `NOT NULL` за замовчуванням, а nullable робити свідомо, коли «невідомо» справді є окремим станом.

NULL доречний, коли «невідомо» або «незастосовно» — це справжній стан: дата видалення для не видаленого запису, дата закриття для відкритої задачі. Для лічильників і сум NULL зазвичай шкідливий: `NOT NULL DEFAULT 0` прибирає з коду нескінченні `COALESCE(amount, 0)` і робить арифметику передбачуваною, бо `NULL + 100` дорівнює NULL. Для тексту варто обрати щось одне — або NULL, або `''` — інакше в базі зʼявляться два різні «порожньо» і фільтр по одному з них половину рядків не побачить.

Сторінка питання →

SQL

Що таке віконні функції і коли вони замінюють підзапити? ЧАСТО ПИТАЮТЬ

SQL·Middle ·віконні функції ·ROW_NUMBER ·LAG

Віконна функція рахує агрегат або позицію по групі рядків (вікні), не згортаючи їх у один рядок: ROW_NUMBER і RANK дають нумерацію всередині PARTITION BY, LAG бере значення попереднього рядка, SUM() OVER (ORDER BY …) дає running total — усе це замінює корельовані підзапити й самоджойни одним проходом.

Як питають

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

Чим ROW_NUMBER відрізняється від RANK і DENSE_RANK?

Як порахувати наростаючий підсумок без коду на PHP і без корельованого підзапиту?

Чому запит із ROW_NUMBER() у WHERE падає з помилкою?

Пояснення

Віконна функція обчислює значення по набору рядків, повʼязаних із поточним, і при цьому не згортає їх: на виході стільки ж рядків, скільки на вході, просто зʼявляється додаткова колонка. Набір задає речення OVER: PARTITION BY ділить результат на незалежні розділи (нумерація починається заново для кожного user_id), ORDER BY задає порядок усередині розділу, а рамка (ROWS/RANGE) визначає, які саме сусідні рядки потрапляють у розрахунок. Це і є принципова відмінність від GROUP BY, який із десяти замовлень користувача робить один рядок: вікно лишає всі десять і кожному дописує ранг, суму або значення сусіда.

Три функції покривають більшість практичних задач. ROW_NUMBER() дає суцільні унікальні номери 1, 2, 3 — навіть якщо значення однакові, порядок між ними база обере довільно. RANK() при ничиїй ставить однаковий ранг і пропускає наступні (1, 1, 3), DENSE_RANK() не пропускає (1, 1, 2); різниця важлива, коли треба «топ-3 з урахуванням рівних результатів». LAG(col, 1, default) бере значення з попереднього рядка розділу, LEAD — з наступного, і третій аргумент рятує від NULL на межі розділу. Раніше те саме писали корельованим підзапитом (SELECT count(*) FROM orders o2 WHERE o2.user_id = o.user_id AND o2.total > o.total) або самоджойном по n - 1: обидва читають таблицю багато разів, вікно — один раз із сортуванням.

Ключовий момент, на якому валяться на співбесіді, — момент обчислення. Віконні функції рахуються після FROM, WHERE, GROUP BY і HAVING, але до ORDER BY і LIMIT. Тому у WHERE їх писати не можна: WHERE row_number() OVER (...) <= 3 дає помилку і в MySQL, і в PostgreSQL. Канонічний шаблон top-N у групі — CTE або похідна таблиця, де вікно живе в SELECT, і зовнішній запит із WHERE rn <= 3. Наслідок із того самого правила корисний і в інший бік: усе, що відсіює WHERE, до вікна взагалі не доходить, тому WHERE status = 'paid' всередині CTE звужує розділ, а не фільтрує вже пронумеровані рядки.

Running total — це агрегат із рамкою: sum(total) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Явний ROWS тут не прикраса. Якщо рамку не вказати, стандарт наказує RANGE UNBOUNDED PRECEDING AND CURRENT ROW, а RANGE працює зі значеннями ORDER BY: усі рядки з однаковим created_at вважаються однією позицією й отримують однакову суму, що включає їх усіх. На секундній точності часу це майже непомітно, на даті — ламає звіт. Без ORDER BY рамка охоплює весь розділ, і sum(total) OVER (PARTITION BY user_id) дає загальну суму користувача в кожному його рядку — зручно, щоб порахувати частку замовлення від обороту без другого запиту.

Межі теж варто назвати. Віконні функції є в PostgreSQL із 8.4, у MySQL з 8.0, MariaDB з 10.2, SQLite з 3.25 — на легасі MySQL 5.7 доведеться повертатись до підзапитів. Продуктивність не безкоштовна: PARTITION BY user_id ORDER BY total DESC без індексу (user_id, total) дає в плані Sort над повним скануванням, і на великій таблиці це дорожче за альтернативу. Для «одного найкращого рядка на групу» майже завжди виграє DISTINCT ON у PostgreSQL або LATERAL із LIMIT 1 (MySQL 8.0.14+), бо вони читають по кілька рядків з індексу замість сортування всього розділу. Тож правило просте: вікно — коли потрібні всі рядки з додатковим контекстом, GROUP BY — коли потрібен один рядок на групу, а вибір між вікном і LATERAL вирішує EXPLAIN.

КодSQL

-- Топ-3 замовлення кожного користувача + наростаючий підсумок + дельта до попереднього
WITH ranked AS (
    SELECT
        o.id,
        o.user_id,
        o.total,
        o.created_at,
        -- унікальні номери 1,2,3… всередині кожного user_id
        row_number() OVER (PARTITION BY o.user_id ORDER BY o.total DESC) AS rn,
        -- при однакових сумах дає однаковий ранг і розрив: 1,1,3
        rank()       OVER (PARTITION BY o.user_id ORDER BY o.total DESC) AS rnk,
        -- сума всіх попередніх замовлень користувача; ROWS, а не дефолтний RANGE,
        -- інакше рядки з однаковим created_at отримають однакову суму
        sum(o.total) OVER (
            PARTITION BY o.user_id ORDER BY o.created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_total,
        -- сума попереднього замовлення; 0 для першого рядка користувача
        lag(o.total, 1, 0) OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS prev_total
    FROM orders o
    WHERE o.status = 'paid'          -- WHERE відпрацьовує ДО обчислення вікна
)
SELECT id, user_id, total, running_total, total - prev_total AS diff
FROM ranked
WHERE rn <= 3                        -- фільтр по вікну можливий лише в зовнішньому запиті
ORDER BY user_id, rn;

-- Індекс, який прибирає Sort із плану WindowAgg
CREATE INDEX orders_user_total ON orders (user_id, total DESC);

-- Для топ-1 у групі дешевше: PostgreSQL читає по одному рядку на користувача
SELECT DISTINCT ON (user_id) user_id, id, total
FROM orders
WHERE status = 'paid'
ORDER BY user_id, total DESC;

Що хоче почути інтервʼюер

Що віконна функція не зменшує кількість рядків, на відміну від GROUP BY: кожен вхідний рядок лишається і отримує додаткову колонку.

Різницю ROW_NUMBER (завжди унікальні 1, 2, 3), RANK (при ничиїй однакове число і розрив: 1, 1, 3) і DENSE_RANK (1, 1, 2).

Що вікно рахується після WHERE, GROUP BY і HAVING, тому фільтрувати по ROW_NUMBER можна лише в зовнішньому запиті чи CTE — це і є типовий шаблон top-N у групі.

Що SUM() OVER (ORDER BY …) дає running total, і що за замовчуванням рамка RANGE UNBOUNDED PRECEDING включає всі рядки-однолітки з тим самим значенням ORDER BY, тому при дублікатах треба явно писати ROWS.

Що LAG/LEAD зі значеннями попереднього й наступного рядка замінюють самоджойн по `n - 1` і мають третій аргумент — значення за замовчуванням.

Версії: MySQL 8.0, MariaDB 10.2, PostgreSQL 8.4+, SQLite 3.25 — у MySQL 5.7 віконних функцій немає, і це впливає на вибір рішення для легасі-проєкту.

Типові помилки

Писати `WHERE row_number() OVER (...) <= 3` — віконні функції у WHERE і HAVING заборонені, потрібен підзапит або CTE.

Плутати ROW_NUMBER і RANK: брати RANK для пагінації й отримувати дірки в нумерації, або ROW_NUMBER для «топ-3 з урахуванням ничиїх» і випадково відрізати один із рівних результатів.

Забувати PARTITION BY і отримувати наскрізну нумерацію по всій таблиці замість нумерації всередині користувача.

Писати `SUM(amount) OVER (ORDER BY created_at)` для running total на даних із однаковими датами й дивуватися, що кілька рядків мають однакову суму: дефолтна рамка RANGE, а не ROWS.

Вважати, що віконна функція завжди швидша: PARTITION BY і ORDER BY без відповідного індексу дають Sort у плані, і для top-1 у групі LATERAL або DISTINCT ON часто дешевші.

Замінювати GROUP BY віконним агрегатом і дивуватися дублікатам: вікно рядки не згортає, потрібен ще DISTINCT або зовнішній фільтр.

ПОРАДА

Скажіть одним реченням: «GROUP BY згортає рядки, вікно лишає їх на місці й додає колонку». Далі назвіть три робочі шаблони — ROW_NUMBER + PARTITION BY для top-N, SUM() OVER для наростаючого підсумку, LAG для дельти між сусідніми рядками — і одразу згадайте, що фільтрувати по вікну можна тільки зовні.

Додаткові питанняЗ ВІДПОВІДЯМИ

Логічний порядок обробки: FROM і JOIN, потім WHERE, GROUP BY, HAVING, і лише після цього обчислюються віконні функції, а вже за ними SELECT DISTINCT, ORDER BY, LIMIT. На момент WHERE номера ще не існує, тому і MySQL, і PostgreSQL повертають помилку («Window function is allowed only in SELECT list and ORDER BY» / «window functions are not allowed in WHERE»). Робочий шаблон: CTE або похідна таблиця з ROW_NUMBER() у SELECT, а фільтр `WHERE rn <= 3` у зовнішньому запиті. Якщо потрібні всі рядки при ничиїй — беріть RANK() замість ROW_NUMBER.

ROWS рахує фізичні рядки: `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` — це всі рядки до поточного включно, і два рядки з однаковою датою отримають різні суми. RANGE рахує за значенням ORDER BY: усі рядки з тим самим значенням (peers) вважаються однією позицією, тому вони отримають однакову суму, що включає їх усіх. Коли є ORDER BY і немає явної рамки, за замовчуванням береться саме `RANGE UNBOUNDED PRECEDING AND CURRENT ROW` — звідси більшість сюрпризів із running total. Без ORDER BY рамка — весь розділ, тому `SUM(x) OVER (PARTITION BY user_id)` дає загальну суму по користувачу в кожному рядку. PostgreSQL 11+ додав ще GROUPS і RANGE зі зміщенням (`RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW`), MySQL 8 підтримує RANGE зі зміщенням, але не GROUPS.

Найчастіше на top-1 у групі по великій таблиці: ROW_NUMBER змушує прочитати й відсортувати всі рядки розділу, навіть якщо потрібен один. У PostgreSQL для цього є `DISTINCT ON (user_id) … ORDER BY user_id, total DESC`, а універсально — `LATERAL` (у MySQL 8.0.14+ теж є LATERAL) з підзапитом `ORDER BY total DESC LIMIT 1` по індексу `(user_id, total DESC)`: база робить index seek на кожного користувача замість повного сортування. Також вікно нічого не дає, якщо результат усе одно згортається в один рядок на групу — там дешевший звичайний GROUP BY. Перевіряти треба EXPLAIN: ознака проблеми — великий Sort або WindowAgg над Seq Scan.

Через іменоване вікно: `SELECT row_number() OVER w, sum(total) OVER w FROM orders WINDOW w AS (PARTITION BY user_id ORDER BY created_at)`. Речення WINDOW стоїть після HAVING і перед ORDER BY, підтримується в PostgreSQL і в MySQL 8.0. Можна й успадковувати: `WINDOW w2 AS (w ROWS UNBOUNDED PRECEDING)` — та сама розбивка й сортування, інша рамка. Це не лише коротше: план стає читабельнішим, бо база бачить одне вікно й робить одне сортування замість кількох WindowAgg.

Три варіанти. Перший — корельований підзапит `WHERE (SELECT count(*) FROM orders o2 WHERE o2.user_id = o.user_id AND o2.total > o.total) < 3`, простий, але квадратичний по розміру групи. Другий — самоджойн із агрегатом або трюк зі змінними користувача (`@rn := IF(@u = user_id, @rn + 1, 1)`), який працює, але офіційно не має гарантованого порядку обчислення і в 8.0 оголошений застарілим. Третій і найчастіше правильний — оновити MySQL: 5.7 без розширеної підтримки з жовтня 2023, а 8.0 дає і віконні функції, і CTE. Для звітів альтернатива — рахувати наростаючі підсумки в матеріалізованій таблиці, а не на льоту.

Сторінка питання →

SQL

Як читати EXPLAIN і що в ньому найважливіше? ЧАСТО ПИТАЮТЬ

SQL·Middle ·EXPLAIN ·план запиту

Дивіться на тип доступу до таблиці, оцінку рядків проти фактичної кількості і найдорожчі вузли плану; розбіжність оцінки й факту вказує на застарілу статистику.

Як питають

Чим EXPLAIN ANALYZE відрізняється від EXPLAIN?

Що означає Seq Scan на великій таблиці?

Запит швидкий локально й повільний у продакшені: як зрозуміти чому?

Пояснення

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

План читається від найглибших вузлів до кореня, і шукати треба той вузол, де витрачається час, а не загальний cost. Перше, на що дивляться, — тип доступу. Seq Scan або ALL у MySQL означає повне читання таблиці; на малій таблиці це нормально, на великій із селективною умовою сигнал, що індекс відсутній або незастосовний через функцію в умові. Index Scan читає індекс і таблицю, Index Only Scan лише індекс. Далі шукають сортування без індексу: Sort із великою кількістю рядків або Using filesort і Using temporary у MySQL. У PostgreSQL важливо множити на loops: вузол із rows=1 і loops=100000 виконався сто тисяч разів.

План залежить від даних. На порожній локальній базі оптимізатор обирає Seq Scan і Nested Loop, які в продакшені стають катастрофою, тому аналізувати треба на реалістичному обсязі, а повільні запити в продакшені ловити через auto_explain або slow query log.

КодSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid' AND o.created_at > now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

-- Проблемний план (PostgreSQL): читаємо всю таблицю й сортуємо в памʼяті
-- Limit (actual time=812.4..812.5 rows=50 loops=1)
--   -> Sort (actual time=812.3..812.4 rows=50 loops=1)
--        Sort Method: top-N heapsort
--        -> Hash Join (actual rows=184213 loops=1)
--             -> Seq Scan on orders o (rows=184213 loops=1)        <- повний скан
--                  Filter: (status = 'paid' AND created_at > ...)
--                  Rows Removed by Filter: 4815787                  <- 96 % рядків відкинуто
--             -> Hash -> Seq Scan on users u

-- Ознаки проблеми: Seq Scan з великим Rows Removed by Filter, Sort без індексу,
-- estimated rows=1200 проти actual rows=184213 (стара статистика)

CREATE INDEX orders_status_created ON orders (status, created_at DESC);
ANALYZE orders;

-- Після: Index Scan по orders_status_created, Sort зник, actual time ~2 ms

Що хоче почути інтервʼюер

Що EXPLAIN показує план оптимізатора з оцінками, а EXPLAIN ANALYZE реально виконує запит і додає фактичний час і кількість рядків на кожному вузлі.

Що план читається від найглибших вузлів до кореня, і шукати треба вузол, де витрачається час, а не загальний cost.

Типи доступу: Seq Scan або ALL це повне читання таблиці, Index Scan пошук по індексу з читанням таблиці, Index Only Scan без таблиці, Bitmap Scan для великих діапазонів.

Що велика різниця між rows estimated і rows actual означає застарілу статистику або скорельовані колонки, і тоді оптимізатор обирає неправильний JOIN або порядок.

Що план залежить від даних: на порожній локальній базі Seq Scan нормальний, тому аналізувати треба на копії продакшн-обсягів.

Типові помилки

Дивитись лише на загальний cost і вважати, що EXPLAIN показує час.

Панікувати від Seq Scan на таблиці з тисячею рядків: для малих таблиць це найшвидший спосіб.

Запускати EXPLAIN ANALYZE на UPDATE або DELETE у продакшені без транзакції з ROLLBACK.

Не знати, що loops у PostgreSQL множить час і рядки вузла: actual rows=1 loops=100000 це сто тисяч виконань.

Ігнорувати Using filesort і Using temporary у MySQL або Sort з Disk у PostgreSQL, які показують сортування без індексу.

ПОРАДА

Скажіть, що читаєте EXPLAIN ANALYZE на копії продакшн-даних: на порожній локальній базі план буде іншим. Згадайте BUFFERS і auto_explain для пошуку повільних запитів у продакшені.

Додаткові питанняЗ ВІДПОВІДЯМИ

EXPLAIN лише будує план і показує оцінки оптимізатора: cost і очікувану кількість рядків. EXPLAIN ANALYZE виконує запит і додає фактичний час та рядки на кожному вузлі, тому саме він виявляє неправильні оцінки. Побічний ефект: DML-запити реально змінюють дані, тому їх аналізують у транзакції з ROLLBACK. Опція BUFFERS додає кількість прочитаних сторінок з кешу й з диска.

База читає таблицю цілком, бо або немає підходящого індексу, або оптимізатор вирішив, що умова поверне значну частину таблиці й індекс не окупиться, або функція чи приведення типу в умові робить індекс незастосовним, наприклад WHERE lower(email) = ? без індексу по lower(email). На великій таблиці з селективною умовою це сигнал додати індекс або переписати умову.

План залежить від статистики й обсягу. На малих даних оптимізатор обирає Seq Scan і Nested Loop, які на мільйонах рядків стають катастрофою. Також у продакшені інші налаштування памʼяті work_mem, інший кеш і паралельне навантаження. Тому план перевіряють на копії продакшн-даних або хоча б на базі, заповненій реалістичним обсягом, і після ANALYZE.

Спочатку ANALYZE таблиці, бо статистика могла застаріти після масового імпорту. Якщо не допомагає, збільшити default_statistics_target для колонки з нерівномірним розподілом або створити розширену статистику CREATE STATISTICS для скорельованих колонок, наприклад country і city. У MySQL це ANALYZE TABLE і гістограми через ANALYZE TABLE ... UPDATE HISTOGRAM.

Сторінка питання →

SQL

Чому OFFSET-пагінація повільна на великих таблицях і що таке keyset?

SQL·Middle ·пагінація ·keyset ·OFFSET

OFFSET не вміє «стрибати»: база читає й відкидає всі пропущені рядки, тому час зростає лінійно з номером сторінки. Keyset (seek method) замість номера сторінки передає ключ останнього прочитаного рядка й читає рівно LIMIT записів за постійний час.

Як питають

Чому 1-ша сторінка списку відкривається миттєво, а 5000-та — три секунди?

Що таке seek method або курсорна пагінація і чим вона краща за LIMIT/OFFSET?

Чому користувач бачить той самий запис двічі, гортаючи сторінки?

Індекс на created_at є, EXPLAIN показує Index Scan — чому запит із OFFSET 200000 усе одно повільний?

Пояснення

OFFSET — це не пошук, а відлік. Індекс дає базі впорядкований список записів, але не дає адресації «дай мені сотий тисячний елемент»: щоб дійти до потрібної позиції, планувальник читає й викидає всі рядки до неї. Тому ORDER BY created_at DESC LIMIT 20 OFFSET 100000 коштує 100 020 прочитаних записів заради двадцяти віддданих, і час зростає лінійно з номером сторінки. У плані це видно як Limit над Index Scan із великим rows removed, і саме тому перша сторінка списку відкривається за одиниці мілісекунд, а п'ятитисячна — за секунди. Якщо ще й індексу під сортування немає, до відліку додається повне сортування всієї вибірки.

Keyset-пагінація (вона ж seek method, вона ж курсорна) прибирає відлік: замість номера сторінки клієнт повертає значення ключа останнього прочитаного рядка, а запит формулюється як діапазон — «усе, що йде строго після цієї позиції». Для сортування created_at DESC, id DESC умова записується рядковим порівнянням (created_at, id) < (?, ?), і база через індекс (user_id, created_at, id) одразу стрибає в потрібну точку дерева та читає рівно двадцять записів. Вартість сторінки перестає залежати від її глибини. Ключовий момент — унікальність ключа сортування: created_at сам по собі не унікальний, тож без tie-breaker у вигляді id рядки з однаковою міткою часу розподіляються між сторінками недетерміновано. Так само не можна розписувати умову як created_at <= ? AND id < ?: це відкидає всі рядки з меншим created_at, але більшим id. Або рядкове порівняння, або явне a < ? OR (a = ? AND b < ?) — у MySQL 8.0 обидві форми оптимізуються в range scan, у старіших версіях надійніша друга.

Другий, часто важливіший за швидкість, аргумент — консистентність. Між запитом сторінки 3 і сторінки 4 хтось вставив запис на початок списку: вікно OFFSET зсувається, і користувач бачить той самий елемент двічі; при видаленні — навпаки, пропускає. Keyset прив'язаний до значення, а не до позиції, тому вставки та видалення на попередніх сторінках його не зсувають. Той самий принцип лежить в основі chunkById() і lazyById() в Laravel — вони обходять таблицю через WHERE id > ? саме тому, що chunk() з OFFSET ламається, коли обробка змінює рядки. Для HTTP-пагінації Laravel з версії 8.27 дає cursorPaginate(): курсор — це base64-JSON зі значеннями колонок з orderBy, який фреймворк розгортає в keyset-умову.

Ціна в keyset теж є, і на співбесіді її треба назвати першим. Немає переходу на довільну сторінку — позиція описується ключем, а не номером. Немає загальної кількості сторінок (COUNT(*) на великій таблиці й сам по собі коштує сканування, тому його або кешують, або замінюють на оцінку з reltuples у PostgreSQL чи rows з EXPLAIN у MySQL). Сортування має бути детермінованим, а колонки сортування — без NULL або з явним NULLS LAST і окремою гілкою курсора. Зміна сортування користувачем означає новий індекс під кожен варіант. Звідси практичний поділ: стрічки, нескінченний скрол, експорт і API з next_cursor — keyset; адмінка з номерами сторінок і глибиною в десятки сторінок — звичайний paginate(), він там ніколи не стане вузьким місцем. І в обох випадках рішення підтверджують через EXPLAIN (ANALYZE, BUFFERS), а не на око.

КодSQL

-- OFFSET: щоб віддати 20 рядків, база читає й відкидає 100 000
SELECT id, created_at, total
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- Keyset: передаємо не номер сторінки, а ключ останнього прочитаного рядка.
-- Рядкове порівняння читається як «усе, що йде строго після цієї позиції»
SELECT id, created_at, total
FROM orders
WHERE user_id = 42
  AND (created_at, id) < ('2026-02-01 10:15:00', 918273)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Індекс під keyset: рівність попереду, обидві колонки сортування далі
CREATE INDEX orders_user_created_id
    ON orders (user_id, created_at DESC, id DESC);

-- ПОМИЛКА: так пропадуть рядки з іншим created_at, але меншим id
-- WHERE created_at <= '2026-02-01 10:15:00' AND id < 918273

-- Еквівалент рядкового порівняння вручну:
-- для MySQL до 8.0 і для змішаних напрямків сортування
SELECT id, created_at, total
FROM orders
WHERE user_id = 42
  AND (created_at < '2026-02-01 10:15:00'
       OR (created_at = '2026-02-01 10:15:00' AND id < 918273))
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Перевірка: у плані має бути range/Index Scan і ~20 прочитаних рядків,
-- а не сотні тисяч, як у варіанті з OFFSET
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders
WHERE user_id = 42 AND (created_at, id) < ('2026-02-01 10:15:00', 918273)
ORDER BY created_at DESC, id DESC LIMIT 20;

Що хоче почути інтервʼюер

Що OFFSET не є операцією пошуку: індекс дає впорядкованість, але позицію N база отримує лише прочитавши N рядків, тому OFFSET 100000 LIMIT 20 коштує 100020 прочитаних рядків замість 20.

Що keyset формулює умову через значення останнього рядка попередньої сторінки, а не через його порядковий номер, і завдяки цьому кожна сторінка коштує однаково.

Що ключ сортування має бути унікальним: до created_at додають id як tie-breaker, інакше рядки з однаковим часом губляться або дублюються на межі сторінок.

Що умову треба писати рядковим порівнянням `(created_at, id) < (?, ?)`, а не `created_at <= ? AND id < ?` — друге відкидає валідні рядки.

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

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

Типові помилки

Вважати, що індекс на колонці сортування «лікує» OFFSET: індекс прибирає Sort, але пропущені рядки все одно читаються один за одним.

Писати keyset-умову як `created_at <= ? AND id < ?` — рядки з іншим created_at і більшим id зникнуть зі стрічки.

Сортувати лише за created_at без унікального tie-breaker і дивуватися дублям на межі сторінок при однакових мітках часу.

Плутати `simplePaginate()` з keyset: він лише прибирає `COUNT(*)`, але запит лишається `LIMIT ... OFFSET ...`.

Робити keyset по колонці, яка може бути NULL, без явного `NULLS LAST` і окремої гілки умови — порівняння з NULL дає UNKNOWN і рядок не потрапляє в жодну сторінку.

Лишати `SELECT COUNT(*)` на кожен запит: на великій таблиці він сам по собі коштує повного сканування й з'їдає весь виграш.

ПОРАДА

Скажіть коротко: «OFFSET — це не пошук, це відлік; keyset перетворює пагінацію на звичайний range scan по індексу». І одразу назвіть ціну: немає стрибка на сторінку 500 і немає загальної кількості — тому в адмінці лишаємо OFFSET, у стрічці й API ставимо keyset.

Додаткові питанняЗ ВІДПОВІДЯМИ

Неунікальну колонку доповнюють унікальним tie-breaker — зазвичай первинним ключем: `ORDER BY created_at DESC, id DESC` і курсор із двох значень. Без нього рядки з однаковим created_at розподіляються між сторінками недетерміновано. З NULL складніше: `(rating, id) < (?, ?)` для NULL дає UNKNOWN, тому такі рядки випадають. Рішення — або оголосити колонку NOT NULL для сортування, або явно вказати `ORDER BY rating DESC NULLS LAST` (PostgreSQL) і вести дві гілки курсора: поки rating не NULL — звичайна умова, після переходу в NULL-хвіст — `WHERE rating IS NULL AND id < ?`. Альтернатива — сортувати за виразом `COALESCE(rating, 0)` і побудувати індекс саме на цьому виразі.

Keyset їх принципово не дає — курсор описує позицію, а не номер. Варіанти: гібрид (keyset для «далі/назад», OFFSET лише коли користувач справді ткнув у номер сторінки), або взагалі відмова від номерів на користь нескінченного скролу. Для лічильника беруть приблизну оцінку: у PostgreSQL `reltuples` з `pg_class` або estimate з `EXPLAIN`, у MySQL — `rows` із EXPLAIN; точний `COUNT(*)` кешують на хвилини. Якщо точне число потрібне під фільтром — рахують його один раз на початок сесії пагінації й передають далі, а не на кожному запиті.

`paginate()` робить `LIMIT/OFFSET` плюс окремий `COUNT(*)` для загальної кількості сторінок. `simplePaginate()` прибирає `COUNT(*)` і бере на один рядок більше, щоб дізнатись, чи є наступна сторінка, але це все ще OFFSET — на глибині він так само повільний. `cursorPaginate()` (з Laravel 8.27) — це саме keyset: курсор кодує значення колонок з `orderBy` останнього рядка в base64-JSON і перетворюється на умову типу `where(...)`. Вимоги: обов'язковий детермінований `orderBy` з унікальним завершальним стовпцем, немає `total()` і немає переходу на довільну сторінку. Для фонового обходу таблиці той самий принцип дають `chunkById()` і `lazyById()` — вони йдуть по `WHERE id > ?`, а не по OFFSET, і тому безпечні при одночасних змінах.

Той самий принцип, що й для будь-якого `ORDER BY ... LIMIT`: колонки з рівністю попереду, потім увесь ключ сортування в тому ж порядку — для `WHERE user_id = ? ... ORDER BY created_at DESC, id DESC` це `(user_id, created_at, id)`. Обидві колонки сортування мають бути в індексі, інакше tie-breaker доведеться перевіряти на прочитаних рядках. Напрямки мають бути або однакові (тоді підходить звичайний ASC-індекс, база просто сканує його назад), або зафіксовані в самому індексі через `DESC` на потрібних колонках. Змішані напрямки (`created_at DESC, id ASC`) ламають і рядкове порівняння, і зворотний скан — там доведеться писати умову вручну через `OR` і будувати індекс `(user_id, created_at DESC, id ASC)`.

У MySQL 8.0 оптимізатор уміє перетворювати порівняння рядкових конструкторів на range scan по складеному індексу — це роботa Range Optimization of Row Constructor Expressions, і план виглядає як звичайний `range` з `Using index condition`. У старіших версіях таке порівняння часто вироджувалось у повний скан, тому умову розписували вручну: `a < ? OR (a = ? AND b < ?)`. PostgreSQL підтримує рядкове порівняння за стандартом і використовує індекс без застережень. Практичне правило: написали keyset — подивіться `EXPLAIN` і переконайтесь, що бачите range/Index Scan із малою кількістю прочитаних рядків, а не Seq Scan.

Сторінка питання →

SQL

Що таке складений індекс і чому важливий порядок колонок?

SQL·Middle ·індекси ·EXPLAIN

Складений індекс працює за принципом лівого префікса: індекс (a, b, c) допомагає умовам по a, по a і b, по a, b і c, але не по b чи c окремо.

Як питають

Чи спрацює індекс (a, b) для умови лише по b?

Яку колонку ставити першою в індексі?

Коли краще два окремих індекси замість одного складеного?

Пояснення

Складений індекс — це B-tree, у якому записи відсортовані спершу по першій колонці, всередині рівних значень по другій, і так далі. Звідси правило лівого префікса: індекс (a, b, c) дає швидкий пошук по a, по a, b і по a, b, c, але не по b окремо, бо значення b розкидані по всьому дереву. Телефонний довідник відсортований за прізвищем, потім за імʼям; знайти всіх Олен у ньому неможливо без повного перегляду.

Порядок колонок визначається запитами, які індекс має обслуговувати. Колонки з умовою рівності йдуть першими, колонка з діапазоном або та, по якій потрібне сортування, останньою. Після діапазону решта індексу для звуження пошуку вже не працює. Для типового WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20 правильний індекс (user_id, status, created_at): база стрибає в потрібне місце й читає двадцять уже відсортованих записів.

Індекс, який містить усі колонки запиту, стає покривним: таблиця не читається взагалі, у PostgreSQL це Index Only Scan із INCLUDE для додаткових колонок. Ціна кожного індексу — місце й повільніший запис, тому один складений індекс, спроєктований під кілька запитів, зазвичай кращий за набір вузьких. Після створення індексу його використання перевіряють через EXPLAIN, а не припускають.

КодSQL

-- Запит, під який проєктуємо індекс
SELECT id, total
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

-- Правильно: рівності попереду, сортування останнім
CREATE INDEX orders_user_status_created ON orders (user_id, status, created_at DESC);

-- Неправильно: після діапазону або сортування решта індексу не використовується для пошуку
CREATE INDEX orders_created_user ON orders (created_at, user_id, status);

-- Покривний: total у INCLUDE, таблицю читати не треба (PostgreSQL)
CREATE INDEX orders_user_status_created_cov
    ON orders (user_id, status, created_at DESC) INCLUDE (total);

-- Перевірка: має бути Index Scan / Index Only Scan, а не Seq Scan + Sort
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

Що хоче почути інтервʼюер

Правило лівого префікса з поясненням через структуру B-tree: записи відсортовані спершу по a, всередині рівних a по b, тому без a стрибнути до потрібного b неможливо.

Що колонки з умовою рівності йдуть першими, а колонка з діапазоном або сортуванням останньою: після діапазону решта індексу для пошуку не використовується.

Що селективність важлива, але порядок визначає перш за все набір запитів, які індекс має покривати.

Що покривний індекс, який містить усі потрібні колонки, дозволяє не читати таблицю взагалі: Index Only Scan у PostgreSQL, Using index у MySQL.

Що кожен індекс коштує на запис і памʼять, тому один складений індекс під кілька запитів часто кращий за кілька вузьких.

Типові помилки

Казати, що порядок не має значення й індекс працює для будь-якої комбінації.

Створювати індекс (created_at, user_id) для запиту WHERE user_id = ? ORDER BY created_at: діапазон або сортування мають бути в кінці.

Створювати окремі індекси по кожній колонці й очікувати, що база обʼєднає їх так само ефективно, як складений.

Ставити першою колонку з двома значеннями на кшталт is_active лише тому, що вона є в кожному запиті, не перевіривши альтернативи.

Не дивитись EXPLAIN після створення індексу й вірити, що він використовується.

ПОРАДА

Хороша ілюстрація — телефонний довідник: за прізвищем шукати легко, за одним лише імʼям — ні. І назвіть правило: рівність, потім діапазон або сортування.

Додаткові питанняЗ ВІДПОВІДЯМИ

Для пошуку ні: записи відсортовані по a, тому значення b розкидані по всьому індексу. PostgreSQL може зробити повний скан індексу замість таблиці, якщо індекс менший, а MySQL 8 має skip scan для дуже малої кардинальності a, але це не пошук по індексу. Якщо запити по b окремо часті, потрібен окремий індекс (b) або (b, a).

Коли запити фільтрують по колонках незалежно і рідко разом, або коли комбінацій умов багато і покрити їх усі складеними індексами дорого. PostgreSQL уміє обʼєднувати окремі індекси через Bitmap And, MySQL через index_merge, але це повільніше за один складений індекс під конкретний запит. Для одночасних умов складений індекс завжди виграє.

(user_id, status, created_at): дві рівності попереду в будь-якому порядку, сортування останнім. Тоді база знаходить діапазон записів потрібного користувача зі статусом, і вони вже відсортовані по created_at, тож LIMIT 20 читає рівно 20 записів без сортування в памʼяті. Якщо status має два значення і не завжди в запиті, можна (user_id, created_at) і фільтрувати status на прочитаних рядках.

Індекс, який містить усі колонки, потрібні запиту, і в SELECT, і в WHERE. База читає лише індекс і не звертається до таблиці: у PostgreSQL це Index Only Scan, якому допомагає INCLUDE для колонок, що не беруть участі в пошуку; у MySQL Using index в EXPLAIN, а в InnoDB вторинний індекс завжди неявно містить первинний ключ.

Сторінка питання →

SQL

Як зберігати й індексувати JSON у PostgreSQL і MySQL, і коли це помилка?

SQL·Middle ·JSON ·jsonb ·GIN

У PostgreSQL зберігайте jsonb і індексуйте його GIN для пошуку по довільних ключах або B-tree на виразі `(data->>'key')` для конкретного поля; у MySQL індекс на JSON можливий лише через згенеровану колонку, функціональний індекс (8.0.13+) або multi-valued index (8.0.17+). Помилка — тримати в JSON поля, які є в кожному рядку і за якими фільтрують чи джойнять: там потрібні звичайні колонки з типом, NOT NULL і зовнішнім ключем.

Як питають

Чим json відрізняється від jsonb і що брати за замовчуванням?

Як прискорити `WHERE data->>'status' = 'paid'`, якщо в таблиці мільйон рядків?

Чому в MySQL не можна просто повісити індекс на колонку типу JSON?

Ми тримаємо всі атрибути товару в JSON-полі — що з цим не так?

Пояснення

У PostgreSQL є два типи: json зберігає текст документа дослівно — з пробілами, порядком і навіть дублікатами ключів, — і парсить його наново при кожному зверненні; jsonb розбирає документ один раз на запис у бінарне подання, де ключі відсортовані й унікальні. Практично завжди потрібен jsonb: тільки він підтримує оператори @>, ?, ?|, ?& і тільки його можна проіндексувати GIN. Тип json виправданий хіба що для сирого логування, де важливо зберегти байти як прийшли. У MySQL тип один — JSON (з 5.7.8), він теж бінарний і теж нормалізує документ, а перевірка валідності відбувається на вставці.

Індексація йде двома різними шляхами, і сильна відповідь називає обидва. GIN по jsonb індексує вміст документа й відповідає на питання «чи містить документ ось цей фрагмент» (meta @> '{"source":"webhook"}') та «чи є такий ключ» (meta ? 'source'); з PG 12 туди ж потрапляють jsonpath-оператори @? і @@. Клас операторів jsonb_path_ops індексує хеші повних шляхів: індекс менший і швидший, але вміє лише containment. Якщо ж запит завжди звертається до одного відомого поля, GIN зайвий — потрібен звичайний B-tree на виразі: CREATE INDEX ON orders ((meta->>'utm_source')). Такий індекс, на відміну від GIN, дає ще й статистику по виразу після ANALYZE, тому планувальник перестає вгадувати кардинальність.

MySQL прямий індекс на JSON-колонці забороняє взагалі. Класичний шлях — згенерована колонка: ADD COLUMN utm_source VARCHAR(64) AS (meta->>'$.utm_source') STORED плюс індекс на ній; VIRTUAL теж індексується і не займає місця в рядку. З 8.0.13 те саме можна записати функціональним індексом з обовʼязковим CAST, але всередині MySQL усе одно створює приховану віртуальну колонку. Для масивів з 8.0.17 є multi-valued index — єдиний випадок, коли один рядок дає кілька записів в індексі; він працює з MEMBER OF, JSON_CONTAINS і JSON_OVERLAPS, але не годиться для сортування, унікальності й первинного ключа. Головна пастка в обох варіантах — типи й колація: вираз в індексі має збігатися з виразом у WHERE посимвольно, інакше EXPLAIN мовчки покаже ALL.

Ціна JSON платиться на записі й на читанні великих документів. У PostgreSQL будь-який UPDATE через MVCC створює нову версію рядка цілком — часткового оновлення поля всередині jsonb не існує; документ більший за пару кілобайтів їде в TOAST у стиснутому вигляді, і щоб дістати один ключ, його треба прочитати й розтиснути повністю. GIN додає помітну вартість вставки й має pending list, через який щойно записані рядки шукаються повільніше, доки не відпрацює чистка. У MySQL оновлення на місці можливе, але лише для JSON_SET, JSON_REPLACE і JSON_REMOVE і лише якщо документ не зростає; будь-яка інша зміна переписує значення повністю.

Помилка починається там, де JSON заміняє схему. Якщо поле є в кожному рядку, має тип, за ним фільтрують, сортують чи джойнять — це колонка, а не ключ у документі: інакше ви втрачаєте NOT NULL, зовнішній ключ, нормальну статистику, а помилка в назві ключа не викликає помилки взагалі, meta->>'statuss' тихо повертає NULL. Обмеження частково рятують — CHECK (jsonb_typeof(meta->'items') = 'array'), CHECK (meta ? 'version'), унікальний індекс на виразі, у MySQL CHECK на JSON-функціях з 8.0.16, — але вони не замінять зовнішній ключ. Розумна межа проста: у колонки виносимо все обовʼязкове й запитуване, у JSON лишаємо розріджені атрибути, payload зовнішніх систем, снапшоти й налаштування, форма яких змінюється швидше, ніж ви готові писати міграції.

КодSQL

-- PostgreSQL: тільки jsonb, json індексувати не можна
CREATE TABLE orders (
    id      bigserial PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users(id),  -- завжди є → звичайна колонка
    status  text   NOT NULL,                       -- фільтруємо → звичайна колонка
    meta    jsonb  NOT NULL DEFAULT '{}'           -- сюди тільки змінне
);

-- GIN: пошук по довільному ключу через containment @> і наявність ключа ?
CREATE INDEX orders_meta_gin ON orders USING gin (meta);
SELECT id FROM orders WHERE meta @> '{"source":"webhook"}';

-- jsonb_path_ops: менший і швидший, але лише @> (без оператора ?)
CREATE INDEX orders_meta_path ON orders USING gin (meta jsonb_path_ops);

-- B-tree на виразі: під рівність/діапазон/сортування по одному полю
-- (дає ще й статистику для планувальника після ANALYZE)
CREATE INDEX orders_meta_utm ON orders ((meta->>'utm_source'));
SELECT id FROM orders WHERE meta->>'utm_source' = 'google';

-- Число з JSON порівнюємо після приведення, не як рядок
CREATE INDEX orders_meta_amount ON orders (((meta->>'amount')::numeric));

-- MySQL 8: прямий індекс на JSON заборонений, потрібна згенерована колонка
ALTER TABLE orders
    ADD COLUMN utm_source VARCHAR(64)
        AS (meta->>'$.utm_source') STORED,
    ADD INDEX idx_utm (utm_source);

-- Або функціональний індекс (8.0.13+): CAST обовʼязковий
CREATE INDEX idx_utm_fn ON orders ((CAST(meta->>'$.utm_source' AS CHAR(64))));

-- Multi-valued index для масиву (8.0.17+): працює з MEMBER OF / JSON_CONTAINS
ALTER TABLE orders ADD INDEX idx_tags ((CAST(meta->'$.tags' AS CHAR(32) ARRAY)));
SELECT id FROM orders WHERE 'urgent' MEMBER OF(meta->'$.tags');

Що хоче почути інтервʼюер

Різницю json і jsonb: json зберігає текст як є (пробіли, порядок і дублікати ключів), jsonb — розібране бінарне подання, ключі відсортовані й унікальні, парсинг на запис, а не на читання; індексувати можна лише jsonb.

Що GIN індексує вміст документа й обслуговує оператори `@>`, `?`, `?|`, `?&` (і `@?`/`@@` з jsonpath у PG 12+), а B-tree на виразі `(data->>'key')` — це звичайний індекс під рівність, діапазон і сортування по одному полю.

Що в MySQL індекс на колонці JSON створити не можна: потрібна STORED/VIRTUAL generated column з індексом, функціональний індекс з обовʼязковим CAST (8.0.13+) або multi-valued index для масивів під `MEMBER OF`, `JSON_CONTAINS`, `JSON_OVERLAPS` (8.0.17+).

Що JSON коштує на запис: у PostgreSQL UPDATE переписує весь рядок через MVCC, великий jsonb їде в TOAST і читання одного ключа розтискає весь документ; у MySQL часткове оновлення на місці працює лише для JSON_SET/JSON_REPLACE/JSON_REMOVE і лише якщо документ не зростає.

Критерій вибору: JSON — для розріджених, різнорідних або зовнішніх даних (payload вебхука, налаштування, снапшот); звичайні колонки — для того, що є завжди, має тип, обмеження, зовнішній ключ і бере участь у фільтрах та джойнах.

Що планувальник погано оцінює селективність по JSON: для виразу `data->>'key'` статистики немає, поки не створено індекс на цьому ж виразі (PG) або згенеровану колонку (MySQL), тому оцінка кардинальності буває на порядки хибною.

Типові помилки

Обрати тип `json` замість `jsonb` у PostgreSQL «бо коротша назва»: по `json` не можна побудувати GIN і кожне читання ключа заново парсить текст.

У MySQL написати `CREATE INDEX ... ON t (data)` для JSON-колонки й здивуватися помилці: прямий індекс на JSON заборонений (як і на BLOB/TEXT без довжини префікса).

Створити GIN-індекс і чекати, що він прискорить `data->>'status' = 'paid'`: GIN обслуговує `@>` і `?`, а не `->>`; або запит переписують на `data @> '{"status":"paid"}'`, або будують B-tree на виразі.

У MySQL зробити функціональний індекс без CAST і без узгодженої колації: `((data->>'$.email'))` не приймається, потрібен `CAST(data->>'$.email' AS CHAR(191))` і той самий COLLATE, що й у запиті, інакше індекс мовчки не використається.

Класти в JSON `user_id`, `status`, `created_at` — поля, які є в кожному рядку: втрачаються NOT NULL, зовнішній ключ, тип і нормальна статистика, а кожен запит обростає кастами.

Порівнювати числа з JSON як рядки: `data->>'price' > '100'` — це лексикографічне порівняння, потрібен явний `(data->>'price')::numeric` у PG або CAST у MySQL.

ПОРАДА

Сформулюйте правило одним реченням: «JSON — для того, чого ми не знаємо заздалегідь; колонки — для того, за чим фільтруємо». А далі покажіть, що знаєте обидва шляхи індексації: GIN для пошуку по довільному ключу, B-tree на виразі — для конкретного, і що в MySQL це завжди generated column під капотом.

Додаткові питанняЗ ВІДПОВІДЯМИ

`jsonb_ops` (за замовчуванням) кладе в індекс окремо кожен ключ і кожне значення, тому підтримує `@>`, `?`, `?|`, `?&` — тобто й перевірку «чи є такий ключ». `jsonb_path_ops` індексує хеші цілих шляхів «ключ → значення», підтримує лише `@>` (і `@?`/`@@`), зате індекс помітно менший і швидший, бо на популярний ключ немає роздутого списку. Беріть `jsonb_path_ops`, якщо всі запити — це containment по конкретному шляху; залишайте типовий клас, якщо потрібен пошук за наявністю ключа. GIN у будь-якому разі дорожчий на запис, ніж B-tree, і має pending list (`fastupdate`), через який щойно вставлені рядки шукаються повільніше до чистки.

Два способи. Перший — явна згенерована колонка: `ALTER TABLE orders ADD COLUMN status VARCHAR(32) AS (data->>'$.status') STORED, ADD INDEX idx_status (status)`; VIRTUAL теж індексується (значення обчислюється при читанні, місце в рядку не займає), STORED займає місце, але дешевший на читання. Другий — функціональний індекс з 8.0.13: `CREATE INDEX idx_status ON orders ((CAST(data->>'$.status' AS CHAR(32))))`; усередині MySQL усе одно створює приховану віртуальну колонку. Головна пастка — типи й колація: вираз в індексі має збігатися з виразом у WHERE до символа, включно з `COLLATE`. Перевірка одна — `EXPLAIN`: має бути `type: ref` і ваш ключ у `key`, а не `ALL`.

З MySQL 8.0.17 індекс може мати кілька записів на один рядок таблиці — саме те, чого бракує для масивів у JSON: `ALTER TABLE products ADD INDEX idx_tags ((CAST(data->'$.tags' AS CHAR(32) ARRAY)))`. Оптимізатор використовує його для `'sale' MEMBER OF(data->'$.tags')`, `JSON_CONTAINS` і `JSON_OVERLAPS`. Обмеження реальні: така колонка може бути лише одна в індексі (решта частин — звичайні), її не можна робити первинним чи унікальним ключем і не можна використати для сортування. У PostgreSQL цю ж задачу закриває GIN по jsonb з `data @> '{"tags":["sale"]}'` або GIN по `(data->'tags')`.

Найчастіше через оцінку селективності: для containment PostgreSQL бере грубу оцінку, і якщо планувальник вважає, що умова поверне більшу частину таблиці, повний скан справді дешевший за bitmap-скан з перевіркою кожного рядка. Другий типовий випадок — оператор не з набору GIN (`->>` замість `@>`) або приведення типу у виразі, який не збігається з виразом індексу. Лікується або переписуванням умови під `@>`, або B-tree на виразі `((data->>'key'))` — він дає планувальнику власну статистику по цьому виразу після `ANALYZE`, і оцінка стає нормальною. Перевіряйте `EXPLAIN (ANALYZE, BUFFERS)` і дивіться на розбіжність rows та actual rows.

У PostgreSQL — CHECK-обмеження: `CHECK (jsonb_typeof(data->'items') = 'array')`, `CHECK (data ? 'version')`, у PG 12+ jsonpath-предикат `CHECK (data @@ '$.price > 0')`. Плюс унікальний індекс на виразі `((data->>'sku'))`. У MySQL — CHECK на функції JSON (8.0.16+) або унікальний індекс на згенерованій колонці. Але це все одно слабше за нормальні колонки: немає зовнішніх ключів на поле всередині JSON, помилка в назві ключа не ловиться (`data->>'statuss'` тихо дає NULL), і схема живе лише в голові розробника. Тому мінімальна дисципліна — те, що обовʼязкове й запитуване, витягувати в колонки, а в JSON лишати справді змінну частину.

Сторінка питання →

SQL

Коли потрібне партиціонування таблиць і як архівувати старі дані без простою?

SQL·Senior ·партиціонування ·PostgreSQL ·MySQL

Партиціонування потрібне тоді, коли дані природно ріжуться по ключу (найчастіше по часу) і старі шматки треба видаляти чи архівувати цілком: DROP/DETACH PARTITION — це операція над метаданими замість мільйонів DELETE. Прискорення читання — побічний ефект, і лише коли ключ партиціонування є в WHERE.

Як питають

Таблиця подій виросла до 400 млн рядків, DELETE старих даних не встигає за ніч — що робите?

Чи прискорить партиціонування наші SELECT-и?

Як перевести робочу таблицю на партиції без вікна простою?

Скільки партицій — це забагато?

Пояснення

Партиціонування розбиває одну логічну таблицю на набір фізичних таблиць за значенням ключа: у PostgreSQL це декларативні PARTITION BY RANGE | LIST | HASH (з версії 10), у MySQL — RANGE, RANGE COLUMNS, LIST, HASH, KEY на нативній реалізації InnoDB (з 8.0 інші рушії партиціонування не підтримують). Планувальник під час планування й виконання відкидає партиції, які не можуть містити потрібних рядків — це partition pruning, і саме тут головне непорозуміння: прунінг вимагає, щоб ключ партиціонування був у WHERE чи в умові з’єднання. Запит WHERE user_id = 42 на таблиці, партиціонованій по created_at, читає всі партиції і працює повільніше, ніж на непартиційованій таблиці з нормальним індексом.

Тому справжній мотив партиціонування — не читання, а життєвий цикл даних і обслуговування. Видалення місяця подій через ALTER TABLE ... DETACH PARTITION у PostgreSQL або ALTER TABLE ... DROP PARTITION у MySQL — це операція над метаданими й табличним простором, вона не породжує мільйони версій рядків, не роздуває WAL/undo, не створює лагу реплік і не залишає по собі bloat, який доводиться прибирати pg_repack. Побічно менші стають і VACUUM, ANALYZE, перебудова індексів: вони йдуть по одній партиції, а не по терабайтній таблиці. Плюс холодні партиції можна тримати на дешевшому табличному просторі або взагалі винести з бази.

Плата за це — обмеження й обовʼязкова автоматизація. У PostgreSQL ключ партиціонування має входити в первинний ключ і в кожен UNIQUE, глобальних унікальних індексів немає; зовнішні ключі на партиційовану таблицю зʼявилися лише в PG 12. У MySQL ключ теж мусить входити в усі унікальні ключі, а зовнішніх ключів на партиційованих InnoDB-таблицях немає взагалі. Партиції на майбутнє треба створювати наперед — планувальником, командою Artisan або pg_partman; інакше вставка або впаде, або осяде в DEFAULT-партиції, після чого підключення нової партиції з тим самим діапазоном вимагатиме сканування дефолтної під блокуванням.

Операційна частина архівації виглядає так. Відчепити партицію потрібно неблокуючим способом: DETACH PARTITION ... CONCURRENTLY (PG 14+, поза транзакційним блоком; при перерві залишає стан detach pending, який довершує FINALIZE). Далі відчеплена таблиця — звичайна таблиця: COPY у Parquet/CSV на обʼєктне сховище, перевірка контрольних сум і DROP TABLE. У MySQL аналог — EXCHANGE PARTITION WITH TABLE, щоб вийняти дані в окрему таблицю без копіювання. Обидві операції все одно чекають на метадані-блокування за довгими читаннями, тож нічний скрипт запускають з lock_wait_timeout (MySQL) або lock_timeout (PostgreSQL) і ретраями, а не «наосліп».

Межа застосовності проста: якщо немає ключа, по якому дані і фільтруються, і застарівають, партиціонування додасть складності без вигоди — краще індекси, keyset-пагінація і чистка пачками через pt-archiver. Партиціонування також не є шардингом: воно живе в межах одного сервера і не рятує від нестачі IOPS чи обʼєму, а лише робить обслуговування великої таблиці передбачуваним.

КодSQL

-- PostgreSQL 14+: розбиття по діапазону часу
CREATE TABLE events (
    id          bigserial,
    occurred_at timestamptz NOT NULL,
    payload     jsonb NOT NULL,
    PRIMARY KEY (id, occurred_at)   -- ключ партиціонування має бути в PK
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- Прунінг спрацює лише за наявності occurred_at у WHERE
EXPLAIN SELECT count(*) FROM events WHERE occurred_at >= '2026-09-01';

-- Архівація місяця: відчіпляємо без довгого блокування (не в транзакції)
ALTER TABLE events DETACH PARTITION events_2026_03 CONCURRENTLY;
-- відчеплена таблиця живе окремо: вивантажити в холодне сховище і прибрати
DROP TABLE events_2026_03;          -- метадані, а не мільйони DELETE

-- Підключення історичної таблиці без повного сканування під блокуванням
ALTER TABLE events_2026_02 ADD CONSTRAINT events_2026_02_rng
    CHECK (occurred_at >= '2026-02-01'
       AND occurred_at <  '2026-03-01') NOT VALID;
ALTER TABLE events_2026_02 VALIDATE CONSTRAINT events_2026_02_rng;
ALTER TABLE events ATTACH PARTITION events_2026_02
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- MySQL 8.4: та сама ідея, ключ мусить входити в кожен унікальний ключ
-- ALTER TABLE events DROP PARTITION p2026_03;
-- SET SESSION lock_wait_timeout = 5;  -- щоб не висіти за довгим читанням

Що хоче почути інтервʼюер

Що головний мотив — дешеве видалення й обслуговування (DROP PARTITION замість DELETE, VACUUM та REINDEX по одній партиції, бекап лише активної), а не магічне прискорення запитів.

Що прунінг партицій працює лише коли ключ партиціонування є в WHERE або JOIN; без нього план читає всі партиції, і стає гірше, ніж було.

Обмеження на ключі: у PostgreSQL ключ партиціонування має входити в PRIMARY KEY і будь-який UNIQUE; у MySQL — у кожен унікальний ключ, а зовнішні ключі на партиційованих InnoDB-таблицях не підтримуються.

Операційний план архівації: у PostgreSQL 14+ `DETACH PARTITION ... CONCURRENTLY`, вивантаження відчепленої таблиці й `DROP TABLE`; у MySQL — `ALTER TABLE ... DROP PARTITION` або `EXCHANGE PARTITION`.

Що партиції треба створювати наперед (планувальник, pg_partman), інакше вставка в неіснуючий діапазон падає або втрапляє в DEFAULT-партицію, з якої її потім важко дістати.

Якщо партиціонування не варіант — видалення пачками по keyset-діапазону з паузами, з оглядом на реплікаційний лаг, а не один гігантський DELETE.

Типові помилки

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

Партиціонувати по `created_at`, а фільтрувати в запитах по `user_id`: прунінгу немає, планувальник обходить сотні партицій, латентність зростає.

Робити партицію на день на п’ять років уперед: тисячі партицій роздувають планування, `pg_class` і памʼять під час prepared statements.

Чистити історію одним `DELETE FROM events WHERE created_at < now() - interval '1 year'`: довга транзакція, розпухання undo/WAL, лаг реплік, а в PostgreSQL місце все одно не повертається без VACUUM FULL чи pg_repack.

Викликати `ATTACH PARTITION` на великій таблиці без заздалегідь провалідованого CHECK — база сканує її цілком під блокуванням.

Забути, що `DROP PARTITION` у MySQL і `DETACH` у PostgreSQL чекають на завершення довгих читань: без `lock_wait_timeout`/`statement_timeout` нічний скрипт стає інцидентом.

ПОРАДА

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

Додаткові питанняЗ ВІДПОВІДЯМИ

Два шляхи. Перший, у PostgreSQL: створити нову партиційовану таблицю, писати в неї (dual-write у застосунку або тригер на старій), фонову історію переливати пачками, потім перейменувати таблиці в одній транзакції. Другий, дешевший: створити порожню партиційовану таблицю, додати на стару таблицю CHECK на її діапазон дат через `NOT VALID` + `VALIDATE CONSTRAINT` (обидва без ACCESS EXCLUSIVE), і `ATTACH PARTITION` — тоді підключення не сканує дані, а стара таблиця стає партицією «все до сьогодні». У MySQL `ALTER TABLE ... PARTITION BY` перебудовує таблицю копіюванням, тому в проді це роблять через gh-ost або pt-online-schema-change.

Видаляти пачками по діапазону первинного ключа, а не по OFFSET: у циклі беремо `WHERE id > :last AND created_at < :cutoff ORDER BY id LIMIT 5000`, запамʼятовуємо останній id, кожна пачка — окрема коротка транзакція, між пачками пауза й перевірка лагу реплік (`pg_stat_replication` / `SHOW REPLICA STATUS`). Готова реалізація — `pt-archiver` з Percona Toolkit, який уміє одночасно писати рядки у файл чи архівну таблицю. У PostgreSQL після масового видалення місце всередині файлів звільнить autovacuum, а щоб повернути його ОС — `pg_repack` без ексклюзивного блокування.

Орієнтир — десятки-сотні, а не тисячі. Кожна партиція має власні файли, статистику й рядки в системних каталогах; планувальник у PostgreSQL до версії 12 обробляв усі партиції на етапі планування, і хоч у 12+ це набагато дешевше, а виконавчий прунінг (з 11) відсікає партиції вже під час виконання, витрати на планування та памʼять під prepared statements ростуть лінійно. Практичний вибір: партиція на місяць для довгої історії, на день — лише коли ретеншн короткий (наприклад, 30–90 днів логів). У MySQL ліміт жорсткий: 8192 партиції на таблицю, але задовго до нього стає боляче через відкриті файлові дескриптори.

Партиціонування — всередині одного сервера й однієї логічної таблиці: планувальник сам обирає партиції, транзакції і бекапи звичайні. Шардинг розкладає дані по різних інстансах, і за це платять розподіленими транзакціями, крос-шардовими JOIN-ами й ребалансуванням. Партиціонування вирішує проблему обслуговування та життєвого циклу даних, шардинг — проблему обсягу запису й обʼєму, який не вміщається в один вузол. Порядок дій майже завжди такий: індекси → партиціонування → репліки на читання → і лише потім шардинг.

Сторінка питання →

SQL

Як виникають deadlock-и і як ви їх діагностуєте та уникаєте?

SQL·Senior ·deadlock ·блокування ·InnoDB

Deadlock — це цикл очікувань блокувань: база сама його виявляє й відкочує одну транзакцію (MySQL 1213/40001, PostgreSQL 40P01), тому застосунок зобовʼязаний повторити її, а зменшують частоту єдиним порядком блокування, короткими транзакціями та індексами під UPDATE.

Як питають

Раз на добу в логах «Deadlock found when trying to get lock; try restarting transaction» — що робити?

Чим deadlock відрізняється від lock wait timeout?

Два запити оновлюють різні рядки — звідки взагалі взявся deadlock?

Чи можна позбутися deadlock-ів повністю, а не ретраїти їх?

Пояснення

Deadlock виникає, коли транзакція A тримає блокування, потрібне B, а B тримає блокування, потрібне A: у графі очікувань зʼявляється цикл, який не розірветься сам ніколи. Тому бази не чекають, а виявляють його. InnoDB перевіряє граф щоразу, коли транзакція стає в чергу за блокуванням (innodb_deadlock_detect увімкнено за замовчуванням), і відкочує жертву — транзакцію, яка змінила найменше рядків, тобто найдешевшу для скасування. PostgreSQL робить це ліниво: сесія, яка прочекала довше за deadlock_timeout (1 секунда), запускає перевірку, і якщо цикл знайдено, саме вона отримує помилку 40P01 deadlock detected із DETAIL про те, який процес на що чекав.

Це принципово інша подія, ніж lock wait timeout, і плутанина тут коштує найдорожче. Lock wait timeout exceeded (MySQL 1205) означає лінійне очікування без циклу, яке впʼялося в innodb_lock_wait_timeout — 50 секунд за замовчуванням; при дефолтному innodb_rollback_on_timeout=OFF відкочується лише останній оператор, і транзакція лишається відкритою з половиною змін, тож застосунок мусить явно зробити ROLLBACK. Deadlock натомість відкочує транзакцію-жертву повністю: повторювати треба весь блок від BEGIN, а не оператор, що впав. Звідси вимога до архітектури — блок транзакції має бути ідемпотентним, а листи, платіжні виклики та HTTP до зовнішніх систем живуть за межами COMMIT.

Друга поширена хиба — вважати, що без явного FOR UPDATE deadlock неможливий. Блокування бере кожен UPDATE, DELETE та INSERT, і причини найчастіше буденні: два сценарії оновлюють ті самі рядки в різному порядку; пакетний upsert з несортованим набором ключів; INSERT, який на конфлікті унікального індексу спершу бере shared-лок на існуючому записі; вставка в дочірню таблицю, що тримає лок на батьківському рядку за зовнішнім ключем. Окремо варто памʼятати, що блокування беруться по індексних записах: UPDATE ... WHERE status = 'pending' без індексу на status заблокує все, що просканував, а в MySQL на REPEATABLE READ ще й проміжки між записами через gap- і next-key-локи.

Діагностика зводиться до трьох кроків. У MySQL: SHOW ENGINE INNODB STATUS показує секцію LATEST DETECTED DEADLOCK з обома операторами, іменами індексів і режимами блокувань — але лише останній випадок, тому на проді вмикають innodb_print_all_deadlocks, і всі події падають в error log; поточні очікування видно в performance_schema.data_lock_waits і data_locks. У PostgreSQL текст помилки вже містить обидва процеси, а живу картину дають pg_blocking_pids() разом із pg_stat_activity, плюс log_lock_waits для очікувань довших за deadlock_timeout. Мета читання — знайти два місця в коді, які беруть ті самі обʼєкти в різному порядку.

Профілактика працює в такому порядку. Єдиний порядок блокування (найпростіше — завжди за зростанням первинного ключа: ORDER BY id ... FOR UPDATE, сортований і дедуплікований масив у пакетних операціях). Коротші транзакції: усе, що можна порахувати до BEGIN, рахується до нього, мережеві виклики виносяться назовні. Індекси під умови UPDATE, щоб площа блокування збігалася з набором рядків, які справді змінюються. Там, де вистачає одного оператора, блокування не потрібне взагалі — UPDATE ... WHERE balance >= :amount із перевіркою affected rows атомарний за визначенням, а для черг є FOR UPDATE SKIP LOCKED. І межа чесності: повністю прибрати deadlock-и в конкурентному застосунку неможливо, тому ретрай із невеликим backoff лишається обовʼязковим — правильна ціль не «нуль deadlock-ів», а рідкісні й непомітні для користувача.

КодSQL

-- Класичний цикл: сесії беруть ті самі рядки у зворотному порядку
-- Сесія A                                   | Сесія B
BEGIN;                                    -- | BEGIN;
UPDATE accounts SET balance = balance - 10
  WHERE id = 1;   -- X-лок на рядку 1     -- | UPDATE ... WHERE id = 2;  -- X-лок на 2
UPDATE accounts SET balance = balance + 10
  WHERE id = 2;   -- чекає на B           -- | UPDATE ... WHERE id = 1;  -- чекає на A
-- MySQL: ERROR 1213 (40001) Deadlock found; вся транзакція-жертва відкочена
-- PostgreSQL: ERROR 40P01 deadlock detected (детектор спрацював за deadlock_timeout)

-- Виправлення: єдиний порядок блокування в усіх сценаріях
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;  -- завжди за зростанням
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

-- Діагностика MySQL 8.0
SHOW ENGINE INNODB STATUS;                    -- секція LATEST DETECTED DEADLOCK, лише останній
SET GLOBAL innodb_print_all_deadlocks = ON;   -- усі випадки — в error log
SELECT * FROM performance_schema.data_lock_waits;  -- хто кого чекає прямо зараз

-- Діагностика PostgreSQL
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, left(query, 60)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
-- ALTER SYSTEM SET log_lock_waits = on;  -- очікування довші за deadlock_timeout (1 с) — у лог

-- Часто блокувань узагалі не треба: один атомарний оператор замість read-modify-write
UPDATE accounts SET balance = balance - 10 WHERE id = 1 AND balance >= 10;

Що хоче почути інтервʼюер

Що deadlock — це виявлений цикл у графі очікувань, а не таймаут: InnoDB перевіряє граф одразу і відкочує транзакцію, яка змінила менше рядків, PostgreSQL запускає детектор після deadlock_timeout (1 с) і вбиває ту сесію, чиє очікування спричинило перевірку.

Різницю з lock wait timeout: у MySQL deadlock (1213) відкочує всю транзакцію-жертву, а «Lock wait timeout exceeded» (1205) при дефолтному innodb_rollback_on_timeout=OFF відкочує лише оператор, лишаючи транзакцію відкритою з половиною змін.

Що deadlock можливий без жодного FOR UPDATE: звичайні UPDATE, INSERT з унікальним ключем, ON DUPLICATE KEY UPDATE, вставка в дочірню таблицю з FK і навіть один пакетний UPDATE конфліктують блокуваннями.

Конкретні інструменти діагностики: SHOW ENGINE INNODB STATUS і innodb_print_all_deadlocks, performance_schema.data_lock_waits; у PostgreSQL pg_blocking_pids(), pg_locks, log_lock_waits і DETAIL у тексті помилки.

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

Типові помилки

Плутати deadlock із lock wait timeout і «лікувати» його підняттям innodb_lock_wait_timeout: цикл не розсмокчеться за жодний час очікування.

Вважати deadlock багом бази або наслідком неправильного рівня ізоляції й шукати рішення в SERIALIZABLE, який навпаки додає конфліктів.

Ретраїти транзакцію, всередині якої є Mail::send або запит до платіжного шлюзу: після повтору клієнт отримає два листи і два списання.

Ловити 40001 і повторювати лише останній оператор: транзакція-жертва вже відкочена цілком, повторювати треба її всю з самого BEGIN.

Робити пакетний UPDATE або upsert з масиву id у довільному порядку: два воркери з перетинними наборами блокують рядки в різній послідовності й регулярно зустрічаються в циклі.

Дивитись лише SHOW ENGINE INNODB STATUS і робити висновок про частоту: там зберігається тільки останній deadlock, без innodb_print_all_deadlocks історії немає.

ПОРАДА

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

Додаткові питанняЗ ВІДПОВІДЯМИ

InnoDB відкочує транзакцію з найменшою кількістю змін (за обсягом undo), тобто найдешевшу для скасування — і робить це повністю, включно з уже виконаними операторами. PostgreSQL простіший: жертвою стає сесія, чиє очікування довше за deadlock_timeout запустило перевірку графа, і вона отримує 40P01. Для коду висновок один: жертва — це не завжди «неважливий» запит, тому повторюваним має бути весь блок транзакції, а всі неідемпотентні дії (листи, HTTP до зовнішніх систем, платежі) виносяться за COMMIT. У Laravel це другий аргумент DB::transaction($cb, 3) плюс ->afterCommit() для job-ів.

Блокування бере кожен UPDATE, DELETE та INSERT. Типові джерела: два UPDATE, що зачіпають ті самі рядки в різному порядку; INSERT з унікальним індексом, який при конфлікті бере S-лок на існуючому записі, а потім хоче X-лок — класика для ON DUPLICATE KEY UPDATE та INSERT ... ON CONFLICT з перетинними пакетами; вставка в дочірню таблицю, яка бере shared-лок на батьківський рядок за зовнішнім ключем; UPDATE без індексу на колонці умови, який блокує весь просканований діапазон. У MySQL на REPEATABLE READ додаються gap- і next-key-локи, тож конфліктувати можуть навіть вставки неіснуючих ще рядків у той самий проміжок індексу.

SHOW ENGINE INNODB STATUS показує дві транзакції: для кожної — активний оператор, а також рядки WAITING FOR THIS LOCK і HOLDS THE LOCK(S) із назвою індексу, номером сторінки й типом (RECORD LOCKS ... lock_mode X locks rec but not gap / gap before rec). Практично беруть три речі: які саме два індекси задіяні, який режим блокування, і в якому порядку кожна транзакція їх брала — цього достатньо, щоб знайти в коді два сценарії з різним порядком. Показано лише останній випадок, тому на проді вмикають innodb_print_all_deadlocks=ON, і всі deadlock-и пишуться в error log. Поточні очікування видно в performance_schema.data_lock_waits разом із data_locks (INFORMATION_SCHEMA.INNODB_LOCKS прибрано в 8.0).

Не боротися за ті самі рядки: SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED — заблоковані рядки пропускаються, і кожен воркер бере свій. Для пакетних оновлень — сортувати ключі перед виконанням (ORDER BY id у SELECT ... FOR UPDATE або сортований масив в upsert) і прибирати дублікати з пакета. Плюс загальне: тримати транзакцію короткою, рахувати все, що можна, до BEGIN, і не тримати блокування на час зовнішнього HTTP-виклику.

Сторінка питання →

SQL

Які рівні ізоляції транзакцій ви використовуєте і чому?

SQL·Senior ·транзакції ·ізоляція ·PostgreSQL

MySQL за замовчуванням REPEATABLE READ, PostgreSQL — READ COMMITTED; рівень визначає, які аномалії можливі, але втрачене оновлення закривають не рівнем, а атомарним UPDATE або SELECT FOR UPDATE.

Як питають

Що таке phantom read і на якому рівні він можливий?

Чому два паралельні запити списали з балансу більше, ніж там було?

Чому SERIALIZABLE вимагає повторювати транзакцію?

Пояснення

Рівень ізоляції визначає, що транзакція бачить із паралельних змін. READ UNCOMMITTED допускає читання незафіксованих даних і в PostgreSQL фактично дорівнює READ COMMITTED. READ COMMITTED бачить лише закомічені дані, але між двома своїми запитами може побачити різні значення. REPEATABLE READ фіксує знімок на початку транзакції; у PostgreSQL це також закриває фантоми, у MySQL InnoDB звичайні читання йдуть зі знімка, а locking reads захищені gap-локами. SERIALIZABLE гарантує результат, еквівалентний послідовному виконанню. За замовчуванням MySQL працює на REPEATABLE READ, PostgreSQL на READ COMMITTED, і для більшості застосунків дефолт лишають.

Практична проблема, яку рівень ізоляції не вирішує, — втрачене оновлення. Два запити читають баланс 100, обидва рахують у PHP і обидва записують результат: одне списання зникає. На READ COMMITTED і REPEATABLE READ це відбувається мовчки. Рішення — не читати й записувати окремо: UPDATE ... SET balance = balance - :amount WHERE balance >= :amount виконує обидві дії атомарно під блокуванням рядка, а нуль affected rows означає недостатньо коштів. Коли між читанням і записом потрібна логіка, застосовують SELECT ... FOR UPDATE у короткій транзакції.

SERIALIZABLE у PostgreSQL реалізований без блокувань: база відстежує залежності й відкидає одну з конфліктних транзакцій із помилкою serialization_failure. Це штатна ситуація, і код має повторити транзакцію цілком. Так само треба обробляти deadlock. У відповіді найкраще працює реальний випадок: подвійне списання, двічі використаний промокод, і те, як саме гонку закрили.

КодSQL

-- Lost update: обидві транзакції читають 100, обидві пишуть 100 - 70 = 30.
-- Рівень ізоляції нижче SERIALIZABLE цього не ловить.
SELECT balance FROM accounts WHERE id = 1;        -- 100 в обох сесіях
UPDATE accounts SET balance = 30 WHERE id = 1;    -- списали 140, баланс 30

-- Рішення 1: атомарний UPDATE з умовою, перевіряємо affected rows
UPDATE accounts
SET balance = balance - 70
WHERE id = 1 AND balance >= 70;                   -- друга сесія отримає 0 rows

-- Рішення 2: блокування рядка на час короткої логіки
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- друга сесія чекає
-- бізнес-перевірки в PHP
UPDATE accounts SET balance = balance - 70 WHERE id = 1;
INSERT INTO ledger (account_id, amount, operation_key) VALUES (1, -70, 'op-9f1c'); -- UNIQUE(operation_key)
COMMIT;

-- Рішення 3: SERIALIZABLE, готові повторити при 40001
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... на serialization_failure застосунок повторює всю транзакцію

-- Черга на базі: воркери беруть різні рядки без очікування
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

Що хоче почути інтервʼюер

Чотири рівні й аномалії, які кожен допускає: dirty read, non-repeatable read, phantom read, і lost update як окрема практична проблема.

Що READ COMMITTED бачить зміни інших транзакцій між своїми запитами, а REPEATABLE READ фіксує знімок на початку і в PostgreSQL також закриває фантоми.

Що патерн прочитати в PHP, порахувати, записати ламається на будь-якому рівні нижче SERIALIZABLE, а рішення це UPDATE з виразом і умовою або SELECT FOR UPDATE.

Що SERIALIZABLE у PostgreSQL це SSI без блокувань, який відкидає транзакцію з помилкою serialization_failure, тому код мусить бути готовий повторити її.

Досвід реальної гонки: подвійне списання, дублікат промокоду, перевищення ліміту, і як саме її закрили.

Типові помилки

Вважати, що REPEATABLE READ гарантує відсутність будь-яких аномалій, зокрема lost update.

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

Використовувати SELECT FOR UPDATE без транзакції або з довгою логікою всередині, тримаючи блокування на час HTTP-запиту.

Не обробляти deadlock і serialization failure: замість повтору транзакції показувати користувачу 500.

Не знати, що в MySQL REPEATABLE READ фантоми частково закриті gap-локами, а в PostgreSQL знімком, і поведінка при UPDATE конфліктних рядків відрізняється.

ПОРАДА

Наведіть реальний випадок гонки, який ловили в продакшені — це переконує краще за перелік рівнів. І покажіть, що знаєте різницю дефолтів MySQL і PostgreSQL.

Додаткові питанняЗ ВІДПОВІДЯМИ

Повторний SELECT з тією ж умовою повертає нові рядки, які вставила інша транзакція. За стандартом можливий на READ COMMITTED і REPEATABLE READ. У PostgreSQL REPEATABLE READ працює на знімку й фантомів не показує; у MySQL InnoDB звичайні SELECT читають знімок, але locking reads і UPDATE можуть побачити нові рядки, від чого захищають gap-локи.

PostgreSQL реалізує SERIALIZABLE через SSI: транзакції не блокують одна одну, а база відстежує залежності читань і записів. Якщо виявляється цикл, який неможливо впорядкувати послідовно, одна з транзакцій відкидається з помилкою 40001 serialization_failure. Це не помилка коду, а нормальна ситуація, тому застосунок мусить повторити транзакцію цілком, у Laravel це другий аргумент DB::transaction.

Атомарний UPDATE accounts SET balance = balance - :amount WHERE id = :id AND balance >= :amount і перевірка affected rows: нуль означає недостатньо коштів. База виконує читання й запис одним кроком під блокуванням рядка. Якщо потрібна складна логіка між читанням і записом, SELECT ... FOR UPDATE у транзакції, коротка секція і запис. Плюс унікальний ключ операції для ідемпотентності.

FOR UPDATE блокує рядки на запис і читання іншими locking reads, FOR SHARE дозволяє іншим читати з FOR SHARE, але не змінювати. SKIP LOCKED пропускає вже заблоковані рядки замість очікування, що робить його ідеальним для черг на базі: кілька воркерів беруть різні завдання без конфліктів. NOWAIT замість очікування одразу повертає помилку.

Сторінка питання →