PostgreSQL: питання на співбесіді рівня Middle

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

WHERE lower(email) = 'ivan@x.com'        -- індекс на email не використається
WHERE DATE(created_at) = '2026-10-04'    -- те саме
WHERE name LIKE '%олег%'                 -- невідомий початок рядка

Як виправити:

• Переписати умову, щоб колонка лишалася «голою»:

WHERE created_at >= '2026-10-04' AND created_at < '2026-10-05'

• Індекс за виразом (PostgreSQL; функціональний індекс у MySQL 8.0.13+):

CREATE INDEX users_email_lower_idx ON users (lower(email));

• LIKE 'олег%' (відомий початок) працює з B-деревом. У PostgreSQL з не-C локаллю для цього потрібен індекс з text_pattern_ops.
• LIKE '%олег%' - потрібен інший тип індексу: триграмний (pg_trgm + GIN у PostgreSQL) або повнотекстовий пошук (tsvector, FULLTEXT у MySQL), або окремий пошуковий рушій - Meilisearch, Elasticsearch.

Ще одна пастка - неявне приведення типів: порівняння рядкової колонки з числом (WHERE phone = 380501234567) змушує MySQL перетворювати кожне значення колонки, і індекс не працює.

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

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

-- Запит
SELECT status, total FROM orders WHERE user_id = 5;

-- Покривний індекс: user_id для пошуку, status і total - щоб не йти в таблицю
CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (status, total);

INCLUDE (PostgreSQL 11+) додає колонки лише в листки індексу: за ними не шукають, зате їх можна прочитати. У MySQL INCLUDE немає - колонки просто додають у кінець складеного індексу.

Нюанси:

• PostgreSQL через MVCC мусить перевірити, чи рядок видимий транзакції. Index-only scan справді не читає таблицю лише для сторінок, позначених у visibility map як «усі рядки видимі». Її оновлює VACUUM, тож на таблиці з частими змінами без вакууму виграш менший. У EXPLAIN ANALYZE це видно як Heap Fetches.
• InnoDB усі вторинні індекси вже містять первинний ключ, тож SELECT id ... WHERE email = ? за індексом на email - покривний автоматично.
• Широкий покривний індекс - це копія частини таблиці: більше місця, повільніший запис. Його роблять під конкретний частий запит, а не «про всяк випадок».

Докладніше в документації: Index-only scan і покривні індекси

Стандарт SQL визначає чотири рівні - від найслабшого до найсуворішого. Кожен забороняє більше аномалій:

• Брудне читання - бачимо незакомічені зміни іншої транзакції.
• Неповторюване читання - той самий рядок, прочитаний двічі, змінився між читаннями.
• Фантом - повторний запит з тією ж умовою повертає нові рядки.

На практиці:

• PostgreSQL за замовчуванням Read Committed. Read Uncommitted поводиться як Read Committed. Його Repeatable Read фантомів не допускає (снапшот на всю транзакцію), а Serializable реалізований через SSI.
• MySQL InnoDB за замовчуванням Repeatable Read. Звичайні SELECT читають снапшот, але блокувальні читання (FOR UPDATE) і UPDATE бачать найсвіжіші дані.

Що це означає для коду: класична гонка «прочитав баланс → перевірив → записав» не захищена на Read Committed. Рішення - атомарний UPDATE ... SET balance = balance - 100 WHERE balance >= 100, блокування FOR UPDATE або Serializable.

На Repeatable Read і Serializable база може відхилити транзакцію з помилкою серіалізації. Застосунок мусить бути готовий повторити таку транзакцію - це частина контракту, а не збій.

Докладніше в документації: Ізоляція транзакцій

SELECT ... FOR UPDATE читає рядки й блокує їх до кінця транзакції. Інші транзакції, які захочуть змінити ці рядки чи теж взяти FOR UPDATE, чекатимуть.

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- тепер ніхто не змінить баланс, поки ми рахуємо
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

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

FOR UPDATE SKIP LOCKED (PostgreSQL 9.5+, MySQL 8.0+) - не чекати, а пропустити вже заблоковані рядки. Ідеально для черги завдань у таблиці: кілька воркерів беруть різні завдання без конфліктів.

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Саме так працює драйвер черги database у Laravel.

NOWAIT - не чекати, а одразу отримати помилку, якщо рядок зайнятий.

Пастки:

• Блокування тримається до кінця транзакції. Без BEGIN (в autocommit) воно знімається одразу після запиту й нічого не захищає.
• Довга транзакція з FOR UPDATE - це черга з інших запитів. Усередині не роблять HTTP-запитів чи інших повільних дій.
• Блокування рядків у різному порядку в різних транзакціях - прямий шлях до deadlock.

Докладніше в документації: Явні блокування рядків

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

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 20;

Limit  (actual time=812.4..812.5 rows=20 loops=1)
  ->  Sort  (actual time=812.4..812.4 rows=20 loops=1)
        Sort Method: top-N heapsort
        ->  Seq Scan on orders  (rows=1200 ... actual rows=950000 loops=1)
              Filter: (status = 'pending')
              Rows Removed by Filter: 4050000
Buffers: shared read=73000

На що дивитися:

• Оцінка проти факту: rows=1200 очікувалося, actual rows=950000 вийшло. Велика розбіжність означає застарілу статистику (ANALYZE orders) або корельовані колонки - і планувальник обирає поганий план.
• Rows Removed by Filter - база прочитала мільйони рядків, щоб відкинути більшість. Підказка до індексу.
• loops - скільки разів виконувався вузол. Час у вузлі вказано за один прохід: 0.5 мс × 20 000 loops у Nested Loop - це 10 секунд.
• BUFFERS - скільки сторінок прочитано з кешу (hit) і з диска (read).

Обережно: EXPLAIN ANALYZE справді виконує запит. Для UPDATE/DELETE загортайте його в транзакцію з ROLLBACK.

MySQL має EXPLAIN ANALYZE з версії 8.0.18 - вивід у вигляді дерева з фактичним часом.

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

LIMIT 20 OFFSET 100000 не «перескакує» на потрібне місце. База мусить знайти й відкинути перші 100 000 рядків і лише потім віддати 20. Чим далі сторінка, тим довший запит - навіть з індексом.

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

Keyset (cursor) пагінація - замість номера сторінки запам'ятовують значення останнього рядка й продовжують від нього:

-- Перша сторінка
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Наступна: від останнього показаного рядка
SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ('2026-10-01 12:00:00', 4821)
ORDER BY created_at DESC, id DESC
LIMIT 20;

З індексом (created_at, id) кожна сторінка - кілька кроків по індексу, хоч перша, хоч мільйонна. id додано для однозначності, коли created_at однаковий.

Обмеження keyset: немає переходу на довільну сторінку «47» і загальної кількості сторінок. Для стрічок, нескінченного скролу, API й експорту це не проблема.

У Laravel: cursorPaginate() реалізує саме keyset, paginate() - OFFSET плюс окремий COUNT(*), simplePaginate() - OFFSET без підрахунку.

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

Логічний бекап (pg_dump, mysqldump) зберігає дані як SQL-команди чи логічний формат: «створити таблицю, вставити ці рядки».

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

Фізичний бекап (pg_basebackup, Percona XtraBackup, знімок диска) - копія файлів даних як вони є.

• Плюси: швидке відновлення - файли просто кладуть на місце; основа для реплік.
• Мінуси: прив'язаний до мажорної версії й платформи; лише цілий кластер.

PITR (Point-In-Time Recovery) = фізичний базовий бекап + безперервний архів журналу змін (WAL у PostgreSQL, binlog у MySQL). Під час відновлення база розгортає базовий бекап і програє журнал до потрібного моменту:

# PostgreSQL: архівувати кожен заповнений сегмент WAL
archive_mode = on
archive_command = '...'   # або готовий інструмент: pgBackRest, WAL-G, Barman

# під час відновлення
recovery_target_time = '2026-10-04 14:31:00'

Навіщо це на практиці: о 14:32 хтось виконав руйнівну міграцію. Дамп учорашньої ночі втратить пів дня даних, а PITR поверне базу на 14:31.

На керованих базах (RDS, Cloud SQL, DigitalOcean) PITR зазвичай увімкнений за замовчуванням з вікном у кілька днів - варто знати, як ним скористатися, до аварії.

Докладніше в документації: Безперервне архівування і PITR

За асинхронної реплікації репліка застосовує зміни з запізненням - зазвичай мілісекунди, але під навантаженням, при великих транзакціях чи проблемах мережі це можуть бути секунди й хвилини.

Що бачить користувач:

• Створив замовлення, його перенаправили на сторінку замовлення - а там «не знайдено», бо читання пішло на репліку, куди запис ще не дійшов.
• Змінив пароль чи налаштування - а сторінка показує старе значення.
• Воркер черги отримує завдання з ID щойно створеного запису, читає з репліки - і не знаходить його.

Як з цим жити:

• Читати свої записи з primary. У Laravel - опція sticky => true: після запису в межах того самого запиту всі читання йдуть на primary.
• Критичні читання - завжди з primary: перевірка балансу перед списанням, авторизація, все, що веде до запису.
• Передавати дані, а не лише ID, або відкладати завдання черги до коміту (afterCommit) і читати їх з primary.
• Моніторити затримку і прибирати відсталу репліку з ротації читання. У PostgreSQL - pg_stat_replication на primary та now() - pg_last_xact_replay_timestamp() на репліці.

Синхронна реплікація прибирає затримку для підтверджених транзакцій, але кожен COMMIT чекає репліку - запис повільніший, а падіння репліки може зупинити запис на primary. Тому її вмикають свідомо, для даних, втрата яких неприпустима.

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

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

Типові причини:

• Умова не відсіює більшість рядків. Якщо запит поверне 30% таблиці, послідовне читання сторінок підряд дешевше, ніж тисячі випадкових переходів індекс → таблиця. Так задумано.
• Маленька таблиця. Кілька сторінок простіше прочитати повністю.
• Застаріла статистика. Після масового імпорту чи видалення планувальник думає, що в таблиці 100 рядків, а їх мільйон. Рішення - ANALYZE orders (autovacuum робить це сам, але із запізненням).
• Умова не підходить до індексу: функція над колонкою (lower(email)), неявне приведення типів, LIKE '%текст', умова на другу колонку складеного індексу без першої.
• Параметризований запит з generic-планом, оптимальним «в середньому», але не для конкретного значення.
• Налаштування вартості: random_page_cost = 4 за замовчуванням розраховане на HDD. На SSD його зазвичай знижують до 1.1-1.5, і індекси стають «дешевшими» для планувальника.

Як розібратися:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';

Порівняти rows (оцінку) з actual rows. Якщо вони розходяться в рази - проблема в статистиці. Для перевірки гіпотези в сесії можна тимчасово заборонити послідовне читання: SET enable_seqscan = off; - і подивитися, наскільки «індексний» план насправді швидший. На проді так не залишають.

Нерівномірні дані: для колонок з перекосом (99% status = 'done') допомагає збільшити деталізацію статистики: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; і повторний ANALYZE.

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

Обмеження UNIQUE - логічне правило схеми. PostgreSQL реалізує його унікальним індексом, який створює автоматично. Тому для простого випадку різниці в поведінці немає:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
-- майже те саме, що:
CREATE UNIQUE INDEX users_email_unique ON users (email);

Коли потрібен саме індекс:

• Частковий: унікальність серед частини рядків - CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL. Обмеження з WHERE не буває.
• За виразом: унікальність без урахування регістру - ON users (lower(email)).
• CONCURRENTLY: створити без блокування запису на великій таблиці.

Коли потрібне саме обмеження:

• Зовнішні ключі посилаються на PRIMARY KEY чи UNIQUE-обмеження (або унікальний індекс без умови).
• Відкладена перевірка DEFERRABLE INITIALLY DEFERRED - перевіряти унікальність у кінці транзакції, а не після кожного рядка (наприклад, при перестановці позицій).
• ON CONFLICT ON CONSTRAINT за іменем.

NULL і унікальність. За стандартом SQL NULL не дорівнює NULL, тож у колонці з UNIQUE може бути скільки завгодно рядків з NULL. Часто це і потрібно (необов'язковий email). Але інколи - ні: «у користувача може бути лише одна адреса без типу».

NULLS NOT DISTINCT (PostgreSQL 15+) змінює правило - NULL вважаються однаковими:

CREATE UNIQUE INDEX one_default_address ON addresses (user_id, type) NULLS NOT DISTINCT;

Тепер для одного user_id дозволено лише один рядок з type IS NULL.

Докладніше в документації: Унікальні індекси

Роздування (bloat) - індекс займає значно більше місця, ніж потрібно для живих даних. Причина - MVCC: UPDATE і DELETE залишають мертві версії рядків, а записи індексу, що на них вказували, звільняються вакуумом не завжди повністю. Сторінки індексу стають напівпорожніми.

Наслідки: більший індекс гірше вміщається в пам'ять, більше читань з диска, повільніші запити й бекапи.

Особливо схильні:

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

Як оцінити: розширення pgstattuple (pgstatindex('orders_created_idx') показує щільність листків) або запити-оцінки з PostgreSQL Wiki. Простий сигнал - індекс помітно більший за свіжостворений аналог.

Як виправити без простою:

REINDEX INDEX CONCURRENTLY orders_created_idx;   -- PostgreSQL 12+
REINDEX TABLE CONCURRENTLY orders;

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

Підводні камені: як і з CREATE INDEX CONCURRENTLY, команду не можна виконати в транзакції, а при збої може лишитися недійсний індекс (_ccnew), який треба видалити вручну.

Профілактика:

• Налаштований autovacuum для таблиць з частими змінами.
• fillfactor менше 100 для таблиць з частими оновленнями - щоб нові версії рядків вміщалися на ту саму сторінку (HOT-оновлення не чіпають індекси взагалі).
• Не індексувати колонки, які часто змінюються, без потреби.

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

B-tree зберігає значення відсортованими. Тому запит «перші N за порядком» може взяти рядки прямо з індексу й зупинитися, не сортуючи всю таблицю:

CREATE INDEX posts_published_idx ON posts (published_at);

SELECT * FROM posts ORDER BY published_at DESC LIMIT 20;
-- Index Scan Backward: 20 рядків з кінця індексу, без сортування

B-tree можна читати в обох напрямках, тож для однієї колонки DESC в індексі не обов'язковий.

Коли напрямок у індексі важливий - змішане сортування кількох колонок:

SELECT * FROM products ORDER BY category_id ASC, price DESC LIMIT 50;

CREATE INDEX products_cat_price_idx ON products (category_id ASC, price DESC);

Індекс (category_id, price) для такого запиту не підійде: обхід у будь-якому напрямку дасть price в тому ж напрямку, що й category_id.

NULLS FIRST / NULLS LAST. За замовчуванням у PostgreSQL NULL більші за всі значення: при ASC вони в кінці, при DESC - на початку. Якщо запит пише ORDER BY published_at DESC NULLS LAST, індекс має збігатися:

CREATE INDEX posts_pub_idx ON posts (published_at DESC NULLS LAST);

Інакше планувальник змушений сортувати.

Разом з фільтром: WHERE user_id = ? ORDER BY created_at DESC LIMIT 10 найкраще обслуговує індекс (user_id, created_at): спершу рівність, потім колонка сортування.

Як перевірити: у EXPLAIN не має бути вузла Sort над великою кількістю рядків - лише Index Scan з Limit. Саме на цьому тримається keyset-пагінація.

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

Справжніх вкладених транзакцій у PostgreSQL немає. Натомість є точки збереження (savepoints) - мітки всередині транзакції, до яких можна відкотити частину змін, не скасовуючи все.

BEGIN;
INSERT INTO orders (id, total) VALUES (1, 500);

SAVEPOINT before_bonus;
INSERT INTO bonuses (order_id, amount) VALUES (1, 50);   -- помилка: порушено обмеження
ROLLBACK TO SAVEPOINT before_bonus;                       -- скасовано лише бонус

COMMIT;   -- замовлення збережено

Навіщо:

• Обробити помилку й продовжити. У PostgreSQL будь-яка помилка переводить транзакцію в стан «aborted» - наступні команди відхиляються до ROLLBACK. Savepoint дозволяє відкотитися до точки й піти далі.
• Масовий імпорт з пропуском поганих рядків - savepoint перед кожним рядком (або порцією).
• Вкладені виклики коду, кожен з яких «хоче транзакцію».

Як це використовує Laravel: вкладений DB::transaction() усередині іншої транзакції створює savepoint, а не новий BEGIN:

DB::transaction(function () {
    Order::create([...]);

    try {
        DB::transaction(fn () => Bonus::create([...]));   // SAVEPOINT trans2
    } catch (QueryException $e) {
        // відкотилося лише створення бонусу
    }
});

Підводні камені:

• COMMIT savepoint'а нічого не фіксує: зміни всередині стануть постійними лише з COMMIT зовнішньої транзакції. Якщо вона відкотиться, відкотиться все.
• Ціна: кожен savepoint споживає ресурси. Тисячі savepoint'ів в одній транзакції (по одному на рядок) помітно сповільнюють роботу - для масового імпорту краще перевіряти дані до вставки чи використовувати ON CONFLICT.
• Побічні ефекти поза базою (листи, HTTP-запити) відкат savepoint'а не скасує.

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

Serializable гарантує, що результат паралельних транзакцій буде таким, ніби вони виконувалися по черзі в якомусь порядку. Жодних аномалій паралельності - навіть тих, від яких не рятує Repeatable Read.

Класична аномалія, яку ловить лише Serializable - write skew:

-- У лікарні має чергувати хоча б один лікар. Чергують двоє.
-- Транзакція A:                          -- Транзакція B:
SELECT count(*) FROM doctors              SELECT count(*) FROM doctors
WHERE on_call;          -- 2              WHERE on_call;          -- 2
UPDATE doctors SET on_call = false        UPDATE doctors SET on_call = false
WHERE id = 1;                             WHERE id = 2;
COMMIT;                                   COMMIT;
-- Результат: не чергує ніхто

Кожна транзакція окремо коректна, а разом вони порушили правило. На Repeatable Read обидві закомітяться.

Як PostgreSQL це реалізує - SSI (Serializable Snapshot Isolation): транзакції не блокують одна одну, а база відстежує залежності між тим, що кожна прочитала й записала. Якщо виявлено небезпечний цикл залежностей, одна з транзакцій отримує помилку:

ERROR: could not serialize access due to read/write dependencies among transactions
SQLSTATE 40001

Помилка серіалізації - не збій, а частина контракту. Застосунок мусить повторити всю транзакцію з початку:

DB::transaction(function () {
    // ...
}, attempts: 3);   // Laravel повторить при deadlock чи serialization failure

Ціна Serializable:

• Повтори транзакцій - логіка має бути ідемпотентною до коміту (без листів і HTTP-запитів усередині).
• Додаткові накладні витрати на відстеження залежностей.
• Більше відкатів під високою конкуренцією.

Коли обирати: складні бізнес-правила, що залежать від кількох рядків (бронювання, ліміти, інваріанти «хоча б один / не більше N»), і коли розставляти блокування вручну складно. Для простих випадків часто достатньо атомарного UPDATE чи SELECT ... FOR UPDATE.

Докладніше в документації: Рівень ізоляції Serializable

У PostgreSQL кожна команда бере на таблицю блокування певного режиму. Режими конфліктують між собою за таблицею сумісності:

• SELECT - ACCESS SHARE (найслабший);
• INSERT/UPDATE/DELETE - ROW EXCLUSIVE;
• CREATE INDEX - SHARE (блокує запис);
• більшість ALTER TABLE, DROP, TRUNCATE, VACUUM FULL - ACCESS EXCLUSIVE (конфліктує з усім, навіть із SELECT).

Ланцюжок, що кладе прод:

1. Довгий запит (звіт, забута транзакція в idle in transaction) тримає ACCESS SHARE на таблиці.
2. Міграція виконує ALTER TABLE orders ADD COLUMN note text - їй потрібен ACCESS EXCLUSIVE, і вона стає в чергу за звітом.
3. Усі нові запити до orders, навіть прості SELECT, стають у чергу за ALTER TABLE, бо черга блокувань упорядкована.
4. Таблиця фактично недоступна, доки не завершиться звіт - хвилини чи години. Пул з'єднань застосунку вичерпується, сайт лягає.

Сама зміна після отримання блокування займає мілісекунди - проблема саме в очікуванні.

Захист:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

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

Ще правила:

• Перед міграцією перевіряти pg_stat_activity на довгі запити й idle in transaction.
• Налаштувати idle_in_transaction_session_timeout, щоб забуті транзакції не жили вічно.
• Робити кожну міграцію короткою: одна зміна схеми - одна транзакція, без масового оновлення даних в тій самій транзакції.

Докладніше в документації: Блокування таблиць